java 高性能dbutil_java DBUtil的简单封装（dbutil+tomcat jdbcpool)

package org.home.util;

import java.sql.Connection;

import java.sql.SQLException;

import java.util.List;

import org.apache.commons.dbutils.QueryRunner;

import org.apache.commons.dbutils.ResultSetHandler;

import org.apache.commons.dbutils.handlers.BeanListHandler;

import org.apache.tomcat.jdbc.pool.DataSource;

import org.apache.tomcat.jdbc.pool.PoolProperties;

public class DBUtil {

private static DataSource ds;

private static QueryRunner runner;

static {

PoolProperties p = new PoolProperties();

p.setUrl("jdbc:mysql://localhost:3306/home");

p.setDriverClassName("com.mysql.jdbc.Driver");

p.setUsername("root");

p.setPassword("sa");

p.setJmxEnabled(true);

p.setTestWhileIdle(false);

p.setTestOnBorrow(true);

p.setValidationQuery("SELECT 1");

p.setTestOnReturn(false);

p.setValidationInterval(30000);

p.setTimeBetweenEvictionRunsMillis(30000);

p.setMaxActive(100);

p.setInitialSize(10);

p.setMaxWait(10000);

p.setRemoveAbandonedTimeout(60);

p.setMinEvictableIdleTimeMillis(30000);

p.setMinIdle(10);

p.setLogAbandoned(true);

p.setRemoveAbandoned(true);

p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"

+ "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");

ds = new DataSource();

ds.setPoolProperties(p);

runner = new QueryRunner();

}

private static Connection getConnection() throws SQLException {

return ds.getConnection();

}

/**

* 删除

* @param tableName

* @param id

* @return

*/

public static boolean delete(String tableName, long id) {

String sql = "delete from " + tableName + " where id=?";

int i = 0;

boolean flag = false;

Connection con = null;

try {

con = getConnection();

i = runner.update(con, sql, id);

if (i >= 0) {

flag = true;

}

} catch (SQLException e) {

System.out.println(e.getMessage());

} finally {

if (con != null) {

try {

con.close();

} catch (SQLException e) {

System.out.println(e.getMessage());

}

}

}

return flag;

}

/**

* 修改/添加

* @param sql

* @param pring

* @return

*/

public static boolean update(String sql, Object pring[]) {

int i = 0;

boolean flag = false;

Connection con = null;

try {

con = getConnection();

i = runner.update(con, sql, pring);

if (i >= 0) {

flag = true;

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

if (con != null) {

try {

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

return flag;

}

/**

* 查询

* @param sql

* @param rsh

* @return

*/

public static List> query(String sql, ResultSetHandler> rsh) {

List> result = null;

Connection con = null;

try {

con = getConnection();

result = (List>) runner.query(con, sql, rsh);

} catch (SQLException e) {

e.printStackTrace();

} finally {

if (con!=null) {

try {

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

return result;

}

/**

* 分页查询

* @param sql

* @param rsh

* @param page

* @param pageSize

* @return

*/

public static List> query(String sql, ResultSetHandler> rsh, int page, int pageSize) {

List> result = null;

Connection con = null;

try {

con = getConnection();

result = (List>) runner.query(con, sql + " limit " + page*pageSize + "," + pageSize, rsh);

} catch (SQLException e) {

e.printStackTrace();

} finally {

if (con!=null) {

try {

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

return result;

}

/**

* 根据id查询

* @param clasz

* @param id

* @return

*/

public static Object get(Class> clasz, int id) {

Connection con = null;

Object obj = null;

try {

con = getConnection();

@SuppressWarnings({ "unchecked", "rawtypes" })

List>result = (List>) runner.query(con,

"select * from " + clasz.getSimpleName().toLowerCase() + " where id=" + id,

new BeanListHandler(clasz));

if (result!=null && result.size()>0) {

obj = result.get(0);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

if (con!=null) {

try {

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

return obj;

}

}