包含分页的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();
}
}
}