jsf学习总结（数据库连接类）

 1.与连接池相关连

    /**
     * getCon
     * 功能：取得连接
     * @return Connection         连接
     */
    public static synchronized Connection getCon() throws wlglException {
     Connection con = null;
        Context initCtx  = null;
        Context envCtx =null;
        DataSource ds = null;
         
        String DBConPool ="jdbc/DBConPool";
       
        //读取连接池信息,及异常处理.
        try {
         initCtx  = new InitialContext();
         envCtx  = (Context) initCtx.lookup("java:comp/env");
            ds = (DataSource) envCtx.lookup(DBConPool);
        } catch (javax.naming.NamingException ex) {
            wlglException.ProcessMainWebExceptionMessage("201",
                    "查找数据Tomcat连接池jndi失败,jndi=" + DBConPool + ".", ex);
        }
       
        //取得连接
        try {
            con = ds.getConnection();
        }

        catch (java.sql.SQLException ex) {
            wlglException.ProcessMainWebExceptionMessage("202", "取数据连接池失败:", ex);
        }
        return con;
    }

2.   执行sql查询，同时打印相关sql语句有助于调试，通过ArrayList传递参数。

public ResultSet exeQuery(String sqlStr, ArrayList params,
                              int resultSetConcurrency) throws
            wlglException {

        try {
            mypstmt = myCon.prepareStatement(sqlStr,
                                             java.sql.ResultSet.
                                             TYPE_SCROLL_INSENSITIVE,
                                             resultSetConcurrency);
            Iterator itx = params.iterator();
            int i = 1;
            while (itx.hasNext()) {
                Object param = itx.next();
                if (param != null) {
                    if (Integer.class.isInstance(param)) {
                        mypstmt.setInt(i, ((Integer) param).intValue());
                    }
                    else if (String.class.isInstance(param)) {
                        mypstmt.setString(i, ((String) param));
                    }
                    else if (Double.class.isInstance(param)) {
                        mypstmt.setDouble(i, ((Double) param).doubleValue());
                    }
                    else if (Boolean.class.isInstance(param)) {
                        mypstmt.setBoolean(i, ((Boolean) param).booleanValue());
                    }
                } else {
                    mypstmt.setString(i, "");
                }
                i = i + 1;
            }
            myRs = mypstmt.executeQuery();//  mypstmt.executeUpdate();
        }

        catch (SQLException ex) {
            wlglException.ProcessMainWebExceptionMessage("204",
                    "execute SQL失败:SQLStr:" + sqlStr + "/nParams:" + params +
                    ".", ex);
        }
        getCount();
        System.out.println("SQLStr是:" + sqlStr);
        System.out.println("params是:" + params);
        return myRs;
    }

3.得到一行数据及标题

   public void exeQueryOneRowTitlesAndDatas(String sqlStr, ArrayList params,
                                             ArrayList titles, ArrayList datas) throws
            wlglException {

        try {
            mypstmt = myCon.prepareStatement(sqlStr);
            Iterator itx = params.iterator();
            int i = 1;
            while (itx.hasNext()) {
                Object param = itx.next();
                if (Integer.class.isInstance(param)) {
                    mypstmt.setInt(i, ((Integer) param).intValue());
                }
                else if (String.class.isInstance(param)) {
                    mypstmt.setString(i, ((String) param));
                }
                else if (Double.class.isInstance(param)) {
                    mypstmt.setDouble(i, ((Double) param).doubleValue());
                }
                else if (Boolean.class.isInstance(param)) {
                    mypstmt.setBoolean(i, ((Boolean) param).booleanValue());
                }
                i = i + 1;
            }
            myRs = mypstmt.executeQuery();
            ResultSetMetaData md = myRs.getMetaData();
            boolean hasNext = false;
            if (myRs.next()) {
                hasNext = true;
            } else {
                hasNext = false;
            }
            for (i = 1; i <= md.getColumnCount(); i++) {
                if (hasNext) {
                 //临时增加处理数据类型为bit类型的.0,1
                 //Date： 2008-6-15
                 //author: szj
                 int tmp=md.getColumnType(i);
                 
                 if(tmp==java.sql.Types.BIT)
                 {
                  if(myRs.getByte(i)==1)
                   datas.add("1"); 
                  else
                      datas.add("0");
                 }
                 else
                 {
                  datas.add(myRs.getString(i));
                 }
                    titles.add(md.getColumnName(i));
                } else {
                    datas.add("");
                }
            }
        } catch (SQLException ex) {
            wlglException.ProcessMainWebExceptionMessage("208",
                    "执行单行返回结果SQL失败:SQLStr:" + sqlStr + "/nParams:" + params +
                    ".", ex);
        }
        System.err.println("SQLStr是:" + sqlStr);
        System.err.println("params是:" + params);
    }