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