jdbctemplate.java

云网论坛CWBBS 源码,内容丰富,学习,参考,教学的好资料,具体见内说明,

package com.cloudwebsoft.framework.db;

import java.util.Vector;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import org.apache.log4j.Logger;
import java.util.*;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.sql.Clob;
import java.sql.Blob;
import cn.js.fan.db.ResultIterator;
import cn.js.fan.db.ResultWrapper;
import cn.js.fan.db.SQLFilter;
import java.sql.Types;
import cn.js.fan.web.Global;

/**
 * <p>Title: 参考Spring的类名称及其含义，制作本类，用以实现无须关闭数据库连接的数据库操作</p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2004</p>
 *
 * <p>Company: Cloud Web Soft</p>
 *
 * @author not attributable
 * @version 1.0
 */

public class JdbcTemplate {
    int rowCount = 0; // 实际取得的记录行数
    int colCount = 0;
    int pageSize = 10;
    public int curPage = 1;
    public long total = 0; // 由sql语句得到的总记录条数

    Logger logger;
    HashMap mapIndex;
    Connection connection = null;

    public JdbcTemplate() {
        this.connection = new Connection(Global.defaultDB);
        logger = Logger.getLogger(JdbcTemplate.class.getName());
        mapIndex = new HashMap();
    }

    public JdbcTemplate(Connection conn) {
        this.connection = conn;
        logger = Logger.getLogger(JdbcTemplate.class.getName());
        mapIndex = new HashMap();
    }

    public JdbcTemplate(DataSource ds) {
        this.connection = ds.getConnection();
        logger = Logger.getLogger(JdbcTemplate.class.getName());
        mapIndex = new HashMap();
    }

    public JdbcTemplate(DataSource ds, int curPage, int pageSize) {
        this.connection = ds.getConnection();
        logger = Logger.getLogger(JdbcTemplate.class.getName());
        mapIndex = new HashMap();
        this.curPage = curPage;
        this.pageSize = pageSize;
    }

    public Connection getConnection() {
        return this.connection;
    }

    public long getTotal() {
        return total;
    }

    public int getColumnCount() {
        return colCount;
    }

    public int getRowCount() {
        return rowCount;
    }

    /**
     * 执行查询，结果集置于result中
     * @param sql String
     * @return Vector
     */
    public ResultIterator executeQuery(String sql) throws SQLException {
        ResultIterator ri = new ResultIterator();

        rowCount = 0;
        colCount = 0;
        ResultSet rs = null;
        Vector result = null;
        boolean isException = false;
        try {
            rs = connection.executeQuery(sql);
            if (rs == null) {
                return ri;
            } else {
                // 取得列名信息
                ResultSetMetaData rm = rs.getMetaData();
                colCount = rm.getColumnCount();
                for (int i = 1; i <= colCount; i++) {
                    mapIndex.put(rm.getColumnName(i).toUpperCase(), new Integer(i));
                }
                result = new Vector();
                ResultWrapper rsw = new ResultWrapper(rs);
                while (rsw.next()) {
                    Vector row = new Vector();
                    for (int i = 0; i < colCount; i++)
                        row.addElement(rsw.getObject(i + 1));
                    result.addElement(row);
                    rowCount++;
                }
            }
        } catch (SQLException e) {
            isException = true;
            throw e;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e) {}
                rs = null;
            }
            if (connection.getAutoCommit()) {
                connection.close();
                connection = null;
            }
        }
        return new ResultIterator(result, mapIndex);
    }

    public void addBatch(String sql) throws SQLException {
        connection.addBatch(sql);
    }

    public int[] executeBatch() throws SQLException {
        int[] r = null;
        boolean isException = false;
        try {
            r = connection.executeBatch();
        }
        catch (SQLException e) {
            isException = true;
            throw e;
        }
        finally {
            if (connection.getAutoCommit()) {
                connection.close();
                connection = null;
            }
        }
        return r;
    }

    /**
     * 填充PreparedStatement
     * @param ps PreparedStatement
     * @param objectParams Object[]
     * @throws SQLException
     */
    public void fillPreparedStatement(PreparedStatement ps,
                                      Object[] objectParams) throws
            SQLException {
        if (objectParams == null)
            return;
        int len = objectParams.length;
        for (int i = 1; i <= len; i++) {
            Object obj = objectParams[i - 1];
            // logger.info("fillPreparedStatement: obj=" + obj);
            if (obj == null) {
                // mysql orcale下支持但SQLSERVER下不支持Types.NULL，需得变为Types.VARCHAR或其它才能通过，但是能不能彻底解决问题无相关资料
                // Date字段在三种数据库下都测试通过
                ps.setNull(i, Types.VARCHAR);
            }
            else if (obj instanceof String) {
                ps.setString(i, (String) obj);
            } else if (obj instanceof Integer) {
                ps.setInt(i, ((Integer) obj).intValue());
            } else if (obj instanceof java.util.Date) {
                ps.setTimestamp(i, new Timestamp(((java.util.Date)obj).getTime()));
            } else if (obj instanceof Timestamp) {
                ps.setTimestamp(i, (Timestamp) obj);
            } else if (obj instanceof Long) {
                ps.setLong(i, ((Long) obj).longValue());
            } else if (obj instanceof Short) {
                ps.setShort(i, ((Short) obj).shortValue());
            } else if (obj instanceof Double) {
                ps.setDouble(i, ((Double) obj).doubleValue());
            } else if (obj instanceof Float) {
                ps.setFloat(i, ((Float) obj).floatValue());
            } else if (obj instanceof Clob) {
                ps.setClob(i, (Clob) obj);
            } else if (obj instanceof Blob) {
                ps.setBlob(i, (Blob) obj);
            } else if (obj instanceof Boolean) {
                ps.setBoolean(i, ((Boolean) obj).booleanValue());
            } else if (obj instanceof Byte) {
                ps.setByte(i, ((Byte) obj).byteValue());
            }
            else
                throw new SQLException("fillPreparedStatement: Object " + obj + " type is not supported.");
        }
    }

    /**
     * 分页操作，将ResultSet的信息保存在Vector中，以利用Iterator模式
     * @param sql String　sql查询语句
     * @param curPage int　当前页
     * @param pageSize int　页的记录条数
     * @return ResultIterator
     */
    public ResultIterator executeQuery(String sql, Object[] objectParams, int curPage, int pageSize) throws
            SQLException {
        ResultIterator ri = new ResultIterator();

        this.curPage = curPage;
        this.pageSize = pageSize;

        rowCount = 0;
        colCount = 0;

        ResultSet rs = null;
        Vector result = null;
        PreparedStatement ps = null;
        try {
            // 取得总记录条数
            String countsql = SQLFilter.getCountSql(sql);
            ps = connection.prepareStatement(countsql);
            fillPreparedStatement(ps, objectParams);

            rs = connection.executePreQuery();
            if (rs != null && rs.next()) {
                total = rs.getLong(1);
            }
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (ps!=null) {
                ps.close();
                ps = null;
            }

            // 防止受到攻击时，curPage被置为很大，或者很小
            int totalpages = (int) Math.ceil((double) total / pageSize);
            if (curPage > totalpages)
                curPage = totalpages;
            if (curPage <= 0)
                curPage = 1;

            if (total != 0)
                connection.setMaxRows(curPage * pageSize); //尽量减少内存的使用
            ps = connection.prepareStatement(sql);
            fillPreparedStatement(ps, objectParams);
            rs = connection.executePreQuery();