import java.sql.PreparedStatement;
/**
* PreparedStatement回调接口
*
* @author 姚磊
*
*/
public interface PreparedStatementCallback {
/**
*
* @param pstm
* @return
* @throws Exception
*/
Object doInPreparedStatement(PreparedStatement pstm) throws Exception;
}
public interface ResultSetCallback{
/**
*
* @param rs
* @return
* @throws Exception
*/
Object doInResultSet(ResultSet rs) throws Exception;
}
public class JdbcTemplate {
/**
* 执行数据库查询操作,不考虑数据库事务问题.
* 这里基于java.sql.Statement接口实现有关操作
* @param callback
* @param sql
* @return
* @throws Exception
*/
public Object executeQuery(ResultSetCallback callback,String sql) {
Connection conn = null;
Object obj = null;
Statement stm =null;
ResultSet rs = null;
try{
conn = DBUtil.getConnection();
stm = conn.createStatement();
rs = stm.executeQuery(sql);
obj = callback.doInResultSet(rs);
}
catch(Exception e){
e.printStackTrace();
}
finally{
//close ResultSet object
if(rs!=null)
try{
rs.close();
}
catch(Exception e){
e.printStackTrace();
}
//close Statement object
if(stm!=null)
try{
stm.close();
}
catch(Exception e){
e.printStackTrace();
}
//close Connection object
DBUtil.closeConnection(conn);
}
return obj;
}
/**
* 执行数据库更新操作,需要考虑事务问题
* 这里基于java.sql.PreparedStatement接口实现有关操作
* @param callback
* @param sql
* @return
* @throws Exception
*/
public Object executeUpdate(PreparedStatementCallback callback,String sql){
Connection conn = null;
Object obj = null;
PreparedStatement pstm =null;
try{
conn = DBUtil.getConnection();
conn.setAutoCommit(false); //设置非自动提交事务
pstm = conn.prepareStatement(sql);
obj = callback.doInPreparedStatement(pstm);
conn.commit(); //提交事务
}
catch(Exception e){
e.printStackTrace();
try{
conn.rollback();//回滚事务
}catch(Exception ex){
ex.printStackTrace();
}
}
finally{
//close PreparedStatement object
if(pstm!=null)
try{
pstm.close();
}
catch(Exception e){
e.printStackTrace();
}
//close Connection object
DBUtil.closeConnection(conn);
}
return obj;
}
}
public User findUser(String name, String passwd) {
final String sql = "select * from admin where name ='"+name+"' and passwd='"+passwd+"'";
LogUtil.log(sql);
return (User) new JdbcTemplate().executeQuery(new ResultSetCallback(){
public Object doInResultSet(ResultSet rs) throws Exception {
User admin = null;
if(rs.next()){
admin = new User();
admin.setName(rs.getString("name"));
admin.setPasswd(rs.getString("passwd"));
}
return admin;
}},sql);
}