包含分页的JDBC工具类

 包含分页的JDBC工具类
包含分页的JDBC工具类
package com.shxt.tool;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
/**
 * @Author：何云龙
 * @Version:JDBC封装1.1 2012-11-29 下午06:38:55
 * @Description:jdbc的封装
 */
public class DBUtil {
    private String url = "jdbc:mysql://localhost:3306/sduentdb";
    private String userName = "root";
    private String passWord = "root";
    private Connection conn = null;
    private Statement st = null;
    private PreparedStatement ps = null;
    private ResultSet rs=null;
    
    
    
    
    
    
    
    
    // 加载驱动，只加载一次即可
    static {
        try {
            // System.out.println("加载驱动正在进行");
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("加载驱动遇到异常");
            e.printStackTrace();
        }
    }
    public Connection getConnection() {
        // 创建连接
        try {
            conn = DriverManager.getConnection(url, userName, passWord);
            return conn;
        } catch (SQLException e) {
            System.out.println("创建连接出现异常！！");
            e.printStackTrace();
        }
        return null;
    }
    public int update(String sql) {
        // row是指受影响的行数
        int row = -1;
        try {
            // 当前连接如果是空或者被关闭，需要重新创建一个连接
            if (conn == null || conn.isClosed()) {
                conn = getConnection();
            }
            st = conn.createStatement();
            row = st.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            
            //关闭资源
            release();
        }
        return row;
    }
    public int update(String sql, Object[] obj) {
        int row = -1;
        // 当前连接如果是空或者被关闭，需要重新创建一个连接
        try {
            if (conn == null || conn.isClosed()) {
                conn = getConnection();
            }
            ps = conn.prepareStatement(sql);
            // 参数结构数据对象
            ParameterMetaData pmd = ps.getParameterMetaData();
            int varCount = pmd.getParameterCount();
            // 给sql语句中的问号？附上值
            for (int i = 0; i < varCount; i++) {
                ps.setObject(i + 1, obj[i]);
            }
            row = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            
            //关闭资源
            release();
        }
        return row;
    }
    public ArrayList<Map<String, Object>> queryToList(String sql) {
        ArrayList<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        // 当前连接如果是空或者被关闭，需要重新创建一个连接
        try {
            if (conn == null || conn.isClosed()) {
                conn = getConnection();
            }
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int col = rsmd.getColumnCount();
 
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= col; i++) {
                    map.put(rsmd.getColumnName(i),
                            rs.getObject(rsmd.getColumnName(i)));
                }
                list.add(map);
                
            }
        //    System.out.println(list);
            return list;
            
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            
            //关闭资源
            release();
         
        }
        return null;
    }
    
    
    public ArrayList<Map<String, Object>> queryToList(String sql,String[] str) {
        ArrayList<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        // 当前连接如果是空或者被关闭，需要重新创建一个连接
        try {
            if (conn == null || conn.isClosed()) {
                conn = getConnection();
            }
            ps = conn.prepareStatement(sql);
            // 参数结构数据对象
            ParameterMetaData pmd = ps.getParameterMetaData();
            int varCount = pmd.getParameterCount();
            // 给sql语句中的问号？附上值
            for (int i = 0; i < varCount; i++) {
                ps.setString(i + 1, str[i]);
            }
            
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int col = rsmd.getColumnCount();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= col; i++) {
                    map.put(rsmd.getColumnName(i),
                            rs.getObject(rsmd.getColumnName(i)));
                }
                list.add(map);
                
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            
            //关闭资源
            release();
        }
        return null;
    }
    
    
    private int pageSize;//页容量
    private int rowsCount;//总记录数
    private int start;//开始位置
    private int end;//结束位置
    private int pageNow;//当前页
    public static int pageCount;//总页数
    
    
    public  ArrayList<Map<String, Object>>  getPage(int pageSize,int pageNow,String sql){
        rowsCount=queryToList(sql).size();//获取到总记录数
        pageCount=rowsCount%pageSize==0?rowsCount/pageSize:(rowsCount/pageSize+1);//获取到总页数
        start=pageNow*pageSize-pageSize;//开始位置
        
        String sqlPage="select * from  ("+sql+") as t limit "+start+" , "+pageSize;
        
         ArrayList<Map<String, Object>> list=queryToList(sqlPage);
        
        return list;
        
    }
 
    
    
    
    
    
    
    
    
    //关闭资源  释放资源
    public void release(){
        
        try {
            
            if(rs!=null){
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        try {
            if(st!=null){
                st.close();
            } 
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(ps!=null){
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        
     
        
    }
    
     
}