DBUtil的简单封装 - 黑马程序员技术交流社区

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;
}

复制代码