java mysql分页工具类翻页_包含分页的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> queryToList(String sql) {

ArrayList> list = new ArrayList>();

// 当前连接如果是空或者被关闭，需要重新创建一个连接

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 map = new HashMap();

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> queryToList(String sql,String[] str) {

ArrayList> list = new ArrayList>();

// 当前连接如果是空或者被关闭，需要重新创建一个连接

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 map = new HashMap();

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>  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> 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();

}

}

}

posted on 2013-02-28 22:20 何云隆 阅读(202) 评论(0)  编辑  收藏 所属分类: java