package com.ftpsearch.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import com.ftpsearch.exception.DBException;
public class DBUtil {
/**
*Logger for this class
*/
private static final Logger logger = Logger.getLogger(DBUtil.class);
private static ThreadLocal<Connection> connectionThreadLocal = new ThreadLocal<Connection>();
/**
* 使用threadLocal,打开一个数据库连接
* @return
*/
public static synchronized Connection currentConnection() {
Connection conn = connectionThreadLocal.get();
if(conn == null) {
conn = ConnectionManager.getConnection();
connectionThreadLocal.set(conn);
}
return conn;
}
/**
* 使用threadLocal,关闭数据库连接
*
*/
public synchronized static void closeConnection() {
Connection conn = connectionThreadLocal.get();
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
logger.warn("JDBC CLOSE Connection EXCEPTION: message = " +e.getMessage());
}
}
connectionThreadLocal.set(null);
}
private static void closeStatement(Statement stmt) {
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
logger.warn("JDBC CLOSE Statement EXCEPTION: message = " +e.getMessage());
}
}
}
private static void closeResultSet(ResultSet rs) {
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
logger.warn("JDBC CLOSE ResultSet EXCEPTION: message = " +e.getMessage());
}
}
}
// public static <E>List executeQuery(DBQueryCallback<E> callback) {
// return executeQuery(callback.getQuerySQL(),callback);
// }
/**
* 执行查询,查询结果为空返回为null
* @param <E>
* @param sql
* @param callback
* @return
*/
public static <E>List executeQuery(String sql,DBQueryCallback<E> callback) {
List<E> result = new ArrayList<E>();
Connection conn = currentConnection();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
//设置参数
callback.setParameters(ps);
//执行查询
rs = ps.executeQuery();
while(rs.next()) {
E o = callback.getBusinessObject(rs);
result.add(o);
}
if(result.isEmpty()) {
return null;
}
else {
return result;
}
} catch (SQLException e) {
closeConnection();
throw new DBException("executeUpdate()发生异常",e.getMessage(),e);
}
finally {
closeResultSet(rs);
closeStatement(ps);
}
}
/**
* 查询得到单个实体
* 查询不到返回为null
* @param <E>
* @param sql
* @param callback
* @return
*/
@SuppressWarnings("unchecked")
public static <E>E get(String sql,DBQueryCallback<E> callback) {
List<E> result = executeQuery(sql,callback);
if(result != null && result.size() > 0)
return result.get(0);
else
return null;
}
// public static <E>E get(DBQueryCallback<E> callback) {
// return get(callback.getQuerySQL(),callback);
// }
// public static int executeUpdate(DBCallback callback) {
// return executeUpdate(callback.getQuerySQL(),callback);
// }
/**
* 执行更新,返回受影响的数据库数据行数
* @param sql
* @param callback
* @return
*/
public static int executeUpdate(String sql,DBCallback callback) {
Connection conn = currentConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
callback.setParameters(ps);
return ps.executeUpdate();
} catch (SQLException e) {
// closeStatement(ps);
closeConnection();
throw new DBException("executeUpdate()发生异常",e.getMessage(),e);
}
finally {
closeStatement(ps);
}
}
}