java数据库工具包_Java常用工具类之DBUtil.java（数据库操作辅助类）

package com.qushida.util;

import java.beans.BeanInfo;

import java.beans.Introspector;

import java.beans.PropertyDescriptor;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.text.DateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**

* 数据库操作辅助类

*

* @version 3.0

* @author xiaocaiji

*/

public class DBUtil {

// 设置数据源(使用C3P0数据库连接池)

private static DataSource dataSource = new ComboPooledDataSource("mysql-config");

private static Logger logger = Logger.getLogger("DBUtil");

private static ThreadLocal tl = new ThreadLocal();

public static DataSource getDataSource() {

return dataSource;

}

// private static Connection conn;

/**

* 该语句必须是 SQL INSERT、UPDATE 、DELETE 语句

*

* @param sql

* @return

* @throws Exception

*/

public int execute(String sql) throws Exception {

return execute(sql, new Object[] {});

}

/**

* insert语句使用，返回新增数据的主键。

*

* @param sql

* @return

*/

public Object execute(String sql, Object[] paramList, boolean falg) throws Exception {

Connection conn = null;

Object o = new Object();

try {

conn = getConnection();

o = this.execute(conn, sql, paramList, falg);

} catch (Exception e) {

logger.info(e.getMessage());

throw new Exception(e);

} finally {

closeConn(conn);

}

return o;

}

/**

* insert语句使用，返回新增数据的主键。

*

* @param sql

* @return

*/

public Object execute(Connection conn, String sql, Object[] paramList, boolean falg) throws Exception {

if (sql == null || sql.trim().equals("")) {

logger.info("parameter is valid!");

}

PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

Object id = null;

try {

// 指定返回生成的主键

// 如果使用静态的SQL，则不需要动态插入参数

setPreparedStatementParam(pstmt, paramList);

if (pstmt == null) {

return -1;

}

pstmt.executeUpdate();

// 检索由于执行此 Statement 对象而创建的所有自动生成的键

ResultSet rs = pstmt.getGeneratedKeys();

if (rs.next()) {

id = rs.getObject(1);

System.out.println("数据主键地址：" + id);

}

} catch (Exception e) {

logger.info(e.getMessage());

throw new Exception(e);

} finally {

closeStatement(pstmt);

}

return id;

}

/**

* 该语句必须是 SQL INSERT、UPDATE 、DELETE 语句 insert into table values(?,?,?,?)

*

* @param sql

* @param paramList：参数，与SQL语句中的占位符一

* @return

* @throws Exception

*/

public int execute(String sql, Object[] paramList) throws Exception {

if (sql == null || sql.trim().equals("")) {

logger.info("parameter is valid!");

}

Connection conn = null;

PreparedStatement pstmt = null;

int result = 0;

try {

conn = getConnection();

pstmt = DBUtil.getPreparedStatement(conn, sql);

setPreparedStatementParam(pstmt, paramList);

if (pstmt == null) {

return -1;

}

result = pstmt.executeUpdate();

} catch (Exception e) {

logger.info(e.getMessage());

throw new Exception(e);

} finally {

closeStatement(pstmt);

closeConn(conn);

}

return result;

}

/**

* 事物处理类

*

* @param connection

* @param sql

* @param paramList：参数，与SQL语句中的占位符一

* @return

* @throws Exception

*/

public int execute(Connection conn, String sql, Object[] paramList) throws Exception {

if (sql == null || sql.trim().equals("")) {

logger.info("parameter is valid!");

}

PreparedStatement pstmt = null;

int result = 0;

try {

pstmt = DBUtil.getPreparedStatement(conn, sql);

setPreparedStatementParam(pstmt, paramList);

if (pstmt == null) {

return -1;

}

result = pstmt.executeUpdate();

} catch (Exception e) {

logger.info(e.getMessage());

throw new Exception(e);

} finally {

closeStatement(pstmt);

}

return result;

}

/**

* 获取实体类型的方法，type为实体类类型。

*

* @param type

* @param sql

* @param paramList

* @return

* @throws Exception

*/

public Object getObject(Class> type, String sql, Object[] paramList) throws Exception {

BeanInfo beanInfo = Introspector.getBeanInfo(type);

Object obj = type.newInstance();

PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();

Map map = getObject(sql, paramList);

if (map != null) {

for (int i = 0; i < propertyDescriptors.length; i++) {

PropertyDescriptor descriptor = propertyDescriptors[i];

String propertyName = descriptor.getName();

if (map != null && map.containsKey(propertyName)) {

Object value = map.get(propertyName);

Object[] args = new Object[1];

args[0] = value;

try {

descriptor.getWriteMethod().invoke(obj, args);

} catch (Exception e) {

logger.info("检测一下Table列，和实体类属性：" + propertyName + "" + "是否一致，并且是否是" + value.getClass() + "类型");

throw new Exception(

"检测一下Table列，和实体类属性：" + propertyName + "" + "是否一致，并且是否是" + value.getClass() + "类型");

}

}

}

} else {

obj = null;

}

return obj;

}

public List> getQueryList(Class> type, String sql, Object[] paramList) throws Exception {

BeanInfo beanInfo = Introspector.getBeanInfo(type);

PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();

List> list = getQueryList(sql, paramList);

List beanList = new ArrayList();

for (Iterator iterator = list.iterator(); iterator.hasNext();) {

Map map = (Map) iterator.next();

Object obj = type.newInstance();

for (int i = 0; i < propertyDescriptors.length; i++) {

PropertyDescriptor descriptor = propertyDescriptors[i];

String propertyName = descriptor.getName();

if (map != null && map.containsKey(propertyName)) {

Object value = map.get(propertyName);

Object[] args = new Object[1];

args[0] = value;

try {

descriptor.getWriteMethod().invoke(obj, args);

} catch (Exception e) {

logger.info("检测一下Table列，和实体类属性：" + propertyName + "" + "是否一致，并且是否是" + value.getClass() + "类型");

throw new Exception(

"检测一下Table列，和实体类属性：" + propertyName + "" + "是否一致，并且是否是" + value.getClass() + "类型");

}

}

}

beanList.add(obj);

}

return beanList;

}

/**

* 将查询数据库获得的结果集转换为Map对象

*

* @param sql：查询

* @return

*/

public List> getQueryList(String sql) throws Exception {

return getQueryList(sql, new Object[] {});

}

/**

* 将查询数据库获得的结果集转换为Map对象

*

* @param sql：查询

* @param paramList：参数

* @return

*/

public List> getQueryList(String sql, Object[] paramList) throws Exception {

if (sql == null || sql.trim().equals("")) {

logger.info("parameter is valid!");

return null;

}

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

List> queryList = null;

try {

conn = getConnection();

pstmt = DBUtil.getPreparedStatement(conn, sql);

setPreparedStatementParam(pstmt, paramList);

if (pstmt == null) {

return null;

}

rs = getResultSet(pstmt);

queryList = getQueryList(rs);

} catch (RuntimeException e) {

logger.info(e.getMessage());

System.out.println("parameter is valid!");

throw new Exception(e);

} finally {

closeResultSet(rs);

closeStatement(pstmt);

closeConn(conn);

}

return queryList;

}

/**

* 分页查询

*

* @param sql

* @param params

* 查询条件参数

* @param page

* 分页信息

* @return

*/

public Page getQueryPage(Class> type, String sql, Object[] params, Page page) {

int totalPages = 0; // 页数

Long rows = 0l;// 数据记录数

// 分页工具类

List> list = null;

Map countMap = null;

try {

list = this.getQueryList(type,

sql + " limit " + (page.getCurPage() - 1) * page.getPageNumber() + " , " + page.getPageNumber(),

params);

countMap = this.getObject(" " + "select count(*) c from (" + sql + ") as t ", params);

rows = (Long) countMap.get("c");

// 求余数

if (rows % page.getPageNumber() == 0) {

totalPages = rows.intValue() / page.getPageNumber();

} else {

totalPages = rows.intValue() / page.getPageNumber() + 1;

}

page.setRows(rows.intValue());

page.setData(list);

page.setTotalPage(totalPages);

} catch (Exception e) {

e.printStackTrace();

}

return page;

}

/**

* 分页查询

*

* @param sql

* @param params

* 查询条件参数

* @param page

* 分页信息

* @return

*/

public Page getQueryPage(String sql, Object[] params, Page page) {

int totalPages = 0; // 页数

Long rows = 0l;// 数据记录数

// 分页工具类

List> list = null;

Map countMap = null;

try {

list = this.getQueryList(

sql + " limit " + (page.getCurPage() - 1) * page.getPageNumber() + " , " + page.getPageNumber(),

params);

countMap = this.getObject(" " + "select count(*) c from (" + sql + ") as t ", params);

rows = (Long) countMap.get("c");

// 求余数

if (rows % page.getPageNumber() == 0) {

totalPages = rows.intValue() / page.getPageNumber();

} else {

totalPages = rows.intValue() / page.getPageNumber() + 1;

}

page.setRows(rows.intValue());

page.setData(list);

page.setTotalPage(totalPages);

} catch (Exception e) {

e.printStackTrace();

}

return page;

}

/**

* 将查询数据库获得的结果集转换为Map对象

*

* @param sql：查询

* @return

*/

public Map getObject(String sql) throws Exception {

return getObject(sql, new Object[] {});

}

/**

* 将查询数据库获得的结果集转换为Map对象

*

* @param sql：查询

* @param paramList：参数

* @return

*/

public Map getObject(String sql, Object[] paramList) throws Exception {

if (sql == null || sql.trim().equals("")) {

logger.info("parameter is valid!");

return null;

}

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

Map map = new HashMap();

try {

conn = getConnection();

pstmt = DBUtil.getPreparedStatement(conn, sql);

setPreparedStatementParam(pstmt, paramList);

if (pstmt == null) {

return null;

}

rs = getResultSet(pstmt);

List list = getQueryList(rs);

if (list.isEmpty()) {

return null;

}

map = (HashMap) list.get(0);

} catch (RuntimeException e) {

logger.info(e.getMessage());

logger.info("parameter is valid!");

throw new Exception(e);

} finally {

closeResultSet(rs);

closeStatement(pstmt);

closeConn(conn);

}

return map;

}

private static PreparedStatement getPreparedStatement(Connection conn, String sql) throws Exception {

if (conn == null || sql == null || sql.trim().equals("")) {

return null;

}

PreparedStatement pstmt = conn.prepareStatement(sql.trim());

return pstmt;

}

private void setPreparedStatementParam(PreparedStatement pstmt, Object[] paramList) throws Exception {

if (pstmt == null || paramList == null) {

return;

}

DateFormat df = DateFormat.getDateTimeInstance();

for (int i = 0; i < paramList.length; i++) {

// -

if (paramList[i] instanceof Integer) {

int paramValue = ((Integer) paramList[i]).intValue();

pstmt.setInt(i + 1, paramValue);

} else if (paramList[i] instanceof Float) {

float paramValue = ((Float) paramList[i]).floatValue();

pstmt.setFloat(i + 1, paramValue);

} else if (paramList[i] instanceof Double) {

double paramValue = ((Double) paramList[i]).doubleValue();

pstmt.setDouble(i + 1, paramValue);

} else if (paramList[i] instanceof Date) {

pstmt.setString(i + 1, df.format((Date) paramList[i]));

} else if (paramList[i] instanceof Long) {

long paramValue = ((Long) paramList[i]).longValue();

pstmt.setLong(i + 1, paramValue);

} else if (paramList[i] instanceof String) {

pstmt.setString(i + 1, (String) paramList[i]);

}

// = pstmt.setObject(i + 1, paramList[i]);

}

return;

}

/**

* 获得数据库查询结果集

*

* @param pstmt

* @return

* @throws Exception

*/

private ResultSet getResultSet(PreparedStatement pstmt) throws Exception {

if (pstmt == null) {

return null;

}

ResultSet rs = pstmt.executeQuery();

return rs;

}

/**

* @param rs

* @return

* @throws Exception

*/

private List> getQueryList(ResultSet rs) throws Exception {

if (rs == null) {

return null;

}

ResultSetMetaData rsMetaData = rs.getMetaData();

int columnCount = rsMetaData.getColumnCount();

List> dataList = new ArrayList>();

while (rs.next()) {

Map dataMap = new HashMap();

for (int i = 0; i < columnCount; i++) {

dataMap.put(rsMetaData.getColumnLabel(i + 1), rs.getObject(i + 1));

}

dataList.add(dataMap);

}

return dataList;

}

/**

* 关闭数据库

*

* @param conn

*/

private void closeConn(Connection conn) {

if (conn == null) {

return;

}

try {

conn.close();

} catch (SQLException e) {

logger.info(e.getMessage());

}

}

/**

* 关闭

*

* @param stmt

*/

private void closeStatement(Statement stmt) {

if (stmt == null) {

return;

}

try {

stmt.close();

} catch (SQLException e) {

logger.info(e.getMessage());

}

}

/**

* 关闭

*

* @param rs

*/

private void closeResultSet(ResultSet rs) {

if (rs == null) {

return;

}

try {

rs.close();

} catch (SQLException e) {

logger.info(e.getMessage());

}

}

/**

* 可以选择三个不同的数据库连接

*

* @param JDBC

* ,JNDI(依赖web容器 DBCP

* @return

* @throws Exception

*/

public static Connection getConnection() throws Exception {

Connection conn = tl.get();

if (conn == null) {

conn = dataSource.getConnection();

}

return conn;

}

/*********** 事务处理方法 ************/

/**

* 开启事务

*/

public static void beginTranscation() throws Exception {

Connection conn = tl.get();

if (conn != null) {

logger.info("事务已经开始！");

throw new SQLException("事务已经开始！");

}

conn = dataSource.getConnection();

conn.setAutoCommit(false);

tl.set(conn);

}

/**

* 结束事务

*

* @throws SQLException

*/

public static void endTranscation() throws SQLException {

Connection conn = tl.get();

if (conn == null) {

logger.info("当前没有事务！");

throw new SQLException("当前没有事务！");

}

conn.commit();

}

/**

* 回滚

*

* @throws SQLException

*/

public static void rollback() throws SQLException {

Connection conn = tl.get();

if (conn == null) {

logger.info("当前没有事务,不能回滚！");

throw new SQLException("当前没有事务,不能回滚！");

}

conn.rollback();

}

/**

* 事务处理，关闭资源

*

* @throws SQLException

*/

public static void closeConn() throws SQLException {

Connection conn = tl.get();

if (conn == null) {

logger.info("当前没有连接，不需要关闭Connection。");

throw new SQLException("当前没有连接，不需要关闭Connection。");

}

conn.close();

tl.remove();

}

}