pubdb.java

这是我上次报java培训时

package jinLing.pub;

import java.sql.*;
import java.util.*;
import javax.naming.*;
import javax.sql.*;

public class PubDb {
    private Context ctx;
    private Connection conn; //数据库连接
    private int recordcount = 0; //查询结果记录总数
    static String sjndi; //从环境中获取数据库连接的字符串

    public PubDb() {
        sjndi = "java:comp/env/DataSourceACS"; // tomcat for ms  access
        //sjndi="java:/DataSource";       // jboss
    }

    protected void finalize() throws Throwable {
        if (null != conn) {
            conn.close();
        }
        if (null != ctx) {
            ctx.close();
        }
        conn = null;
        ctx = null;
        super.finalize();
    }

    /**
     * 功能：申请 connection
     * @return 1：申请成功 ，0：申请失败
     */
    protected int setConnection() throws SQLException {
        int stat = 1;
        try {
            if (conn == null || conn.isClosed()) {
                ctx = new InitialContext();
                DataSource ds = (DataSource) ctx.lookup(sjndi);
                conn = ds.getConnection();
            }
        } catch (Exception e) {
            e.printStackTrace();
            //System.out.println("connect database be defeated!");
            stat = 0;
        }
        Util.debug("------- beg conn:" + conn);
        return stat;
    }

    /**
     * 功能：关闭 connection
     */
    protected void releaseConnection() {
        try {
            if (null != conn) {
                conn.close();
            }
            if (null != ctx) {
                ctx.close();
            }
            conn = null;
            ctx = null;
        } catch (Exception ex) {
            conn = null;
            ctx = null;
            ex.printStackTrace();
        }
        Util.debug("++++++++ end conn:" + conn);
    }

    /**
     * 功能：执行分页查询语句
     * @param sql 查询语句
     * @return 数据集
     */
    public Collection execSel(String sql, String pageno, String count) {
        int stat = 1;
        Vector vVector = new Vector();
        Statement stmt = null;
        ResultSet rs = null;
        String sql_cont = "select count(*) as SL from ( " + sql + " ) z";

        try {
            stat = setConnection();
            Util.debug("stat:" + stat);
            if (stat == 1) {
                // 分页处理 得到总记录数
                stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_READ_ONLY);
                // Util.debug("sql_cont:" + sql_cont);
                rs = stmt.executeQuery(sql_cont);
                if (rs.next())
                    recordcount = rs.getInt("SL"); //总记录数
                else
                    recordcount = 0;
                if (recordcount > 0) {
                    // 查询当前页的数据
                    int bgn_rm = (Integer.parseInt(pageno) - 1) *
                                 Integer.parseInt(count) + 1; //当前记录数
                    rs = stmt.executeQuery(sql);
                    Util.debug("sql:" + sql);
                    rs.absolute(bgn_rm);
                    ResultSetMetaData r = rs.getMetaData();
                    String vFieldName = "";
                    String vFieldValue = "";
                    //循环把所有记录存入 Vector 中
                    for (int page_beg = 1; page_beg <= Integer.parseInt(count);
                                        page_beg++) {
                        Hashtable vTmp = new Hashtable();
                        //循环把某条记录的所有字段及其值存入 hashtable 中
                        for (int i = 1; i <= r.getColumnCount(); i++) {
                            //读取字段名
                            vFieldName = r.getColumnName(i).toUpperCase();
                            //读取字段名相应值
                            vFieldValue = rs.getString(i);
                            if (vFieldValue == null)
                                vFieldValue = "";
                            vTmp.put(vFieldName, vFieldValue);
                        }
                        vVector.add(vTmp);
                        if (!rs.next())
                            break;
                    }
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs = null;
                stmt = null;
                releaseConnection();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vVector;
    }

    /**
     * 功能：读取全部数据，不分页
     * @param sql 查询语句
     * @return 数据集
     */
    public Collection execSel(String sql) {
        return execSel(sql, "1", "1000000");
    }

    /**
     * 功能：执行查询语句--只适合结果集只有一条的情况
     * @param sql 查询语句
     * @return 数据集
     */
    public Hashtable execSelSingle(String sql) {
        int stat = 1;
        Hashtable hs = new Hashtable();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stat = setConnection();
            if (stat == 1) {
                stmt = conn.createStatement();
                rs = stmt.executeQuery(sql);
                ResultSetMetaData r = rs.getMetaData();
                String vFieldName = "";
                String vFieldValue = "";
                while (rs.next()) {
                    for (int i = 1; i <= r.getColumnCount(); i++) {
                        //读取字段名
                        vFieldName = r.getColumnName(i).toUpperCase();
                        //读取字段名相应值
                        vFieldValue = rs.getString(i);
                        if (vFieldValue == null)
                            vFieldValue = "";
                        hs.put(vFieldName, vFieldValue);
                    }
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs = null;
                stmt = null;
                releaseConnection();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return hs;
    }
    /**
     * 功能 ：获取查询结果中第一条记录的某个字段的值
     * @param   sql  用于查询的SQL语句
     * @param   field_name   查询结果中的某个字段的名称
     * @return  字段field_name对应的记录值
     */
    public String execSelSingle(String sql, String field_name) {
        String field_value = "";
        int stat = 0;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stat = setConnection();
            if (stat == 1) {
                stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_READ_ONLY);
                rs = stmt.executeQuery(sql);
                if (rs.next())
                    field_value = rs.getString(field_name); //
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs = null;
                stmt = null;
                releaseConnection();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return field_value;
    }

    /**
     * 功能：执行数据更新
     * @param sql
     * @return 更新记录条数
     */
    public int execUpd(String sql) {
        int st = 1;
        int stat = 1;
        Statement stmt = null;

        try {
            stat = setConnection();
            if (stat == 1) {
                stmt = conn.createStatement();
                stmt.executeUpdate(sql);
            }
        } catch (Exception ex) {
            st = 0;
            ex.printStackTrace();
        } finally {
            try {
                stmt = null;
                releaseConnection();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return st;
    }

    /**
     * 功能：获取最大ID值，用于插入记录
     * @param sql 获取最大ID值的查询语句
     * @return 最大ID值
     */
    public synchronized int getMax(String sql) {
        int max = 0;
        int stat = 0;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stat = setConnection();
            if (stat == 1) {
                // 分页处理 得到总记录数
                stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_READ_ONLY);
                rs = stmt.executeQuery(sql);
                if (rs.next())
                    max = rs.getInt(1); //
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs = null;
                stmt = null;
                releaseConnection();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return max;
    }

    /**
     * 功能：得到总记录数，使用前必须先执行execSel（）方法
     * @return execSel（）方法中查询的记录数
     */
    public int getRecordcount() {
        return recordcount;
    }

    /**
     * 功能：得到总记录数
     * @param sql
     * @return sql语句中查询的记录总数
     */
    public int getRecordcount(String sql) {
        int count = -1;
        int stat = 1;
        Statement stmt = null;
        ResultSet rs = null;
        try {

            stat = setConnection();
            if (stat == 1) {
                stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_READ_ONLY);
                rs = stmt.executeQuery(sql);
                if (rs.next())
                    count = rs.getInt(1);
                else
                    count = 0;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs = null;
                stmt = null;
                releaseConnection();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return count;
    }

    /**
     * 功能 ：获取初始化下拉框
     * @param   name  用于页面显示的字段
     * @param   val   值的字段
     * @param         stitle    标题  如'---请选择---'
     * @param locate 定位下拉框值
     * @return  下拉框内的选项组成的字符串
     */
    public String getInitData(String name, String val, String sql,
                              String stitle,
                              String locate) {
        Collection coll = null;
        String strbf = "";
        String sName = "";
        String sVal = "";
        try {
            coll = execSel(sql); //得到所有符合的记录
            Iterator it = coll.iterator();
            if (!stitle.equals(""))
                strbf = "<option value='' selected>" + stitle + "</option>";
            //循环生成下拉框的内容
            while (it.hasNext()) {
                Hashtable rs = (Hashtable) it.next();
                sVal = String.valueOf(rs.get(val));
                sName = String.valueOf(rs.get(name));
                if (sVal.equals(locate))
                    strbf += "<option value='" + sVal + "' selected>" + sName +
                            "</option>";
                else
                    strbf += "<option value='" + sVal + "'>" + sName +
                            "</option>";
            } //end while
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return strbf;
    }
}