利用反射封装的JDBC工具类

/**
* JDBC工具类
*/
public class JdbcUtils {

private static final Logger logger = Logger.getLogger(JdbcUtils.class);//这种情况下默认使用logger打印，如是只打印到自定义的logger就获取自定义的logger的名称

//这里使用的是c3p0数据源
private static DataSource dataSource;

//数据源通过静态块进行初始化，保证在字节码加载时就初始话，且只初始化一次
static {
dataSource = new ComboPooledDataSource();
}

// 获得数据源连接池
public static DataSource getDateSource() {
return dataSource;
}

// 获得与指定数据库的连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}

// 统一的释放资源方法
public static void release(ResultSet rs, Statement stmt, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}

// 通用的增删改方法
public static boolean update(String sql, Object[] params) throws DaoException{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = getConnection();
conn.setAutoCommit(false);//将事物默认提交设置为flase
pstmt = conn.prepareStatement(sql);
for (int i = 0; params != null && i < params.length; i++)
pstmt.setObject(i + 1,params[i]);//通过setObject设置参数所对应的值。sql语句的参数用？占位
int num = pstmt.executeUpdate();//executeUpdate会返回影响结果的条数
conn.commit();
conn.setAutoCommit(false);
if (num > 0)
return true;
return false;
} catch (SQLException e) {
if(conn != null){
try {
conn.rollback();
} catch (SQLException e1) {
throw new DaoException(e1);
}
}
throw new DaoException(e);
} finally {
release(rs, pstmt, conn);
}
}

/**
* @author
* @since
* @throws
* @description 对数据进行批量添加，当数据量大于100时，则整除1000时提交一次，wbs里存放的是批量数值数组的集合
*/
public static boolean updateBatch(String sql,List wbs) throws DaoException{
logger.info("批量添加开始");
Calendar calendar = Calendar.getInstance();
long startTime = calendar.getTimeInMillis();
calendar = null;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int count = 0;
boolean flag = false;
try {
conn = getConnection();
conn.setAutoCommit(false);
pstmt = conn.prepareStatement(sql);
for(Iterator it = wbs.iterator();it.hasNext();){
Object[] params = (Object[])it.next();
count++;
for (int i = 0; params != null && i < params.length; i++){
pstmt.setObject(i + 1, params[i]);
}
pstmt.addBatch();//将一批参数添加到pstmt对象的批处理命令
if(count % 100 == 0){
pstmt.executeBatch();
conn.commit();//当数量到100时提交
flag = true;
}
}
pstmt.executeBatch();
conn.commit();
conn.setAutoCommit(true);
flag = true;
if(flag){
logger.info("批量添加结束");
Calendar calendarOld = Calendar.getInstance();
long endTime = calendarOld.getTimeInMillis();
//logger.info("批量添加共耗时:"+(endTime-startTime)+"ms");
logger.info("批量添加共耗时:"+(endTime-startTime)+"ms");
return true;
}
logger.info("批量添加失败");
return false;
} catch (SQLException e) {
if(conn != null){
try {
conn.rollback();
} catch (SQLException e1) {
throw new DaoException(e);
}
}
throw new DaoException(e);
} finally {
release(rs, pstmt, conn);
}
}

// 通用的查询方法
public static Object query(String sql, Object[] params, ResultSetHandler handler) throws DaoException{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
for (int i = 0; params != null && i < params.length; i++){
pstmt.setObject(i + 1, params[i]);
}
rs = pstmt.executeQuery();
Object result = handler.handle(rs);//此处的handler分为BeanHandler和ListHandler,对应的Handler通过反射将查询到值映射到对象或是集合里并返回。对应handler在最下方。
return result;
} catch (SQLException e) {
throw new DaoException(e);
} finally {
release(rs, pstmt, conn);
}
}

/**
* 此处返回的是插入数据后生成的主键
* @param sql
* @param params
* @return
*/
public static int insert(String sql,Object[] params) throws DaoException{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
conn = getConnection();
pstmt = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
for (int i = 0; params != null && i < params.length; i++)
pstmt.setObject(i + 1, params[i]);
pstmt.executeUpdate();
rs =pstmt.getGeneratedKeys();//获取刚刚插入的主键rs
rs.next();//获取刚刚获取的主键
int key = rs.getInt(1);
if(key > 0){
return key;
}else{
return 0;
}
}catch(SQLException e){
throw new DaoException(e);
}finally{
release(rs,pstmt,conn);
}
}

}
对应的BeanHandler和ListHandler如下:
public class BeanHandler implements ResultSetHandler {
private Class clazz;

public BeanHandler(Class clazz) {
this.clazz = clazz;
}

// 将结果集的第一行数据封装到bean返回
public Object handle(ResultSet rs) throws DaoException{
try {
if (rs.next()) {
Object bean = this.clazz.newInstance();
ResultSetMetaData metaData = rs.getMetaData();//获取rs的元数据,该元数据可以获得列的数量和对应列索引处的列的名称值
int columnCount = metaData.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnName(i);
Object value = rs.getObject(columnName);//根据列的名称获取对应的值
if(value instanceof Integer){
BeanUtils.setProperty(bean, columnName, (Integer)value);//将值转化为对应的类型然后反射到对应对象属性中。
}else if(value instanceof String){
BeanUtils.setProperty(bean, columnName, (String)value);
}else if(value instanceof Date){
BeanUtils.setProperty(bean, columnName, (Date)value);
}else if(value instanceof Boolean){
BeanUtils.setProperty(bean, columnName, (Boolean)value);
}else if(value instanceof Float){
BeanUtils.setProperty(bean, columnName, (Float)value);
}
}
return bean;
}
return null;
} catch (Exception e) {
throw new DaoException(e);
}
}

}

public class BeanListHandler implements ResultSetHandler {
private Class clazz;

public BeanListHandler(Class clazz) {
this.clazz = clazz;
}

// 将结果集的每一行封装到bean，将bean加入一个list返回
public Object handle(ResultSet rs) throws DaoException{
try {
List list = new ArrayList();
while (rs.next()) {
Object bean = this.clazz.newInstance();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
for (int i = 1; i <= columnCount; i++){
String columnName = metaData.getColumnName(i);
Object value = rs.getObject(columnName);

if(value instanceof Integer){
BeanUtils.setProperty(bean, columnName, (Integer)value);
}else if(value instanceof String){
BeanUtils.setProperty(bean, columnName, (String)value);
}else if(value instanceof Date){
BeanUtils.setProperty(bean, columnName, (Date)value);
}else if(value instanceof Boolean){
BeanUtils.setProperty(bean, columnName, (Boolean)value);
}
}
list.add(bean);
}
return list;
} catch (Exception e) {
e.printStackTrace();
throw new DaoException(e);
}
}