dblink.java~44~

一个完整的门户网站,基于JSP环境下开发的,使用SQL数据库.

package com.bwm.database;

import java.sql.*;
import java.util.*;
import javax.servlet.http.HttpServletRequest;
import com.bwm.model.Link;
import com.bwm.core.*;

/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2004</p>
 * <p>Company: MR</p>
 * @author BWM
 * @version 1.0
 */

public class Dblink extends Link {
    private Link l=new Link();
    public Dblink() {
    }
    public Dblink(String sql) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = Database.getConnection();
            ps = con.prepareStatement(FinalConstants.SELECT_AFFICHE + sql,
                                      ResultSet.TYPE_SCROLL_INSENSITIVE,
                                      ResultSet.CONCUR_READ_ONLY);
            rs = ps.executeQuery();
            if (rs.next()) {
                l.setLid(rs.getInt("lid"));
                l.setName(ParamUtils.getSqlString(rs.getString("name")));
                l.setLtime(ParamUtils.getSqlString(rs.getString("ltime")));
                l.setUrl(ParamUtils.getSqlString(rs.getString("url")));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        } finally {
            System.out.println(ps + " Dblink(String sql) " + l.getLid());
            try {
                if (ps != null) {
                    ps.close();
                    ps = null;
                }
            } catch (SQLException e) {
                ps = null;
            }
            try {
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (SQLException e) {
                con = null;
            }
        }
    }
    public Dblink(HttpServletRequest request) {
        l.setName(ParamUtils.getRequestString(request, "name"));
        l.setUrl(ParamUtils.getRequestString(request, "url"));
    }
    public boolean Insert() {
        boolean b = false;
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = Database.getConnection();
            ps = con.prepareStatement(FinalConstants.INSERT_AFFICHE);
            ps.setString(1, l.getName());
            ps.setString(2, l.getUrl());
            if (ps.executeUpdate() > 0) {
                b = true;
            } else {
                b = false;
            }
        } catch (SQLException e) {
            b = false;
            System.out.println(e.getMessage());
            e.printStackTrace();
        } finally {
            System.out.println(ps + " Dblink.Insert()");
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                ps = null;
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                con = null;
            }
        }
        return b;
    }

    public Collection Select(String sql) {
        Collection coll = new ArrayList();
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            con = Database.getConnection();
            ps = con.prepareStatement(FinalConstants.SELECT_AFFICHE + sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Link link = new Link();
                link.setLid(rs.getInt("lid"));
                link.setName(ParamUtils.getSqlString(rs.getString("name")));
                link.setLtime(ParamUtils.getSqlString(rs.getString("ltime")));
                link.setUrl(ParamUtils.getSqlString(rs.getString("url")));
                coll.add(link);
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        } finally {
            System.out.println(ps + " Dbaffiche.Select(String sql) ");
            try {
                if (ps != null) {
                    ps.close();
                    ps = null;
                }
            } catch (SQLException e) {
                ps = null;
            }
            try {
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (SQLException e) {
                con = null;
            }
        }
        return coll;
    }

    //页数+SQL条件
    public Collection Select(int page, String sql) {
        Collection coll = new ArrayList();
        Connection con = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        int tip = FinalConstants.STEP * (page - 1);
        try {
            con = Database.getConnection();
            ps = con.prepareStatement(FinalConstants.SELECT_AFFICHE + sql);
            rs = ps.executeQuery();
            if (tip <= 0) {
                rs.beforeFirst();
            } else {
                if (!rs.absolute(tip)) {
                    rs.beforeFirst();
                }
            }
            for (int i = 1; rs.next() && i <= FinalConstants.STEP; i++) {
                Affiche affiche = new Affiche();
                affiche.setAid(rs.getInt("aid"));
                affiche.setUid(ParamUtils.getSqlString(rs.getString("uid")));
                affiche.setTitle(ParamUtils.getSqlString(rs.getString("title")));
                affiche.setAtime(ParamUtils.getSqlString(rs.getString("atime")));
                affiche.setContent(ParamUtils.getSqlString(rs.getString("content")));
                coll.add(affiche);
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        } finally {
            System.out.println(ps + " Dbaffiche.Select(int page,String sql) ");
            try {
                if (ps != null) {
                    ps.close();
                    ps = null;
                }
            } catch (SQLException e) {
                ps = null;
            }
            try {
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (SQLException e) {
                con = null;
            }
        }
        return coll;
    }
    public int getLid() {
        return l.getLid();
    }
    public void setLid(int lid) {
        l.setLid(lid);
    }
    public String getName() {
        return l.getName();
    }
    public void setName(String name) {
        l.setName(name);
    }
    public String getLtime() {
        return l.getLtime();
    }
    public void setLtime(String ltime) {
        l.setLtime(ltime);
    }
    public String getUrl() {
        return l.getUrl();
    }
    public void setUrl(String url) {
        l.setUrl(url);
    }

}