刚开始有点不习惯,现在用惯了觉得爱上了?
发出来希望能需要的朋友有个帮助!!
private Connection con = null;
private PreparedStatement pst = null;
private CallableStatement call = null; //这个用于防问数据库存储过程
/**
* connection database
*
* @return connection
*/
private Connection getCon() {//返回数据库连接字符串
Context co;
try {
co = new InitialContext();
DataSource daSource = (DataSource) co
.lookup("java:comp/env/my/salary"); //我用的是tomcat数据库连接池
con = daSource.getConnection();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
/**
* close database operate
*
* @return
*/
public void close(ResultSet rSet, PreparedStatement pst, Connection con) {//关闭数据库连接
try {
if (rSet != null) {
rSet.close();
rSet = null;
}
if (pst != null) {
pst.close();
pst = null;
}
if (con != null) {
con.close();
con = null;
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
/**
* omnipotence class insert update delete
* @param sql
* @param lst
* @return
*/
public int getproc(String sql,List lst){ //调用存储过程 返回数据库操作执行数量
int count=0;
con = this .getCon();
if(con!=null){
try {
call=con.prepareCall(sql);
if(lst.size()>0){
for(int i=0;i<lst.size();i++){
call.setObject(i+1, lst.get(i));
}
}
count=call.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
if(call!=null){
call.close();
call=null;
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally{
this.close(null, pst, con);
}
}
}
return count;
}
/**
* omnipotence class Page
*
* @param sql
* @param lst
* @return
*/
public Result getpage(String sql, List lst) { //调用存储过程 返回集合类。
Result result = null;
ResultSet rSet = null;
con = this.getCon();
if (con != null) {
try {
call = con.prepareCall(sql);
if (lst.size() > 0) {
for (int i = 0; i < lst.size(); i++) {
call.setObject(i + 1, lst.get(i));
}
}
rSet = call.executeQuery();
result = ResultSupport.toResult(rSet);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if (call != null) {
call.close();
call = null;
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
this.close(rSet, pst, con);
}
}
}
return result;
}
/**
* omnipotence class select
*
* @return result
*
*/
public Result getList(String sql, List lst) { // 数据库查询操作
Result result = null;
con = this.getCon();
ResultSet rSet = null;
if (con != null) {
try {
pst = con.prepareStatement(sql);
if (lst.size() > 0) {
for (int i = 0; i < lst.size(); i++) {
pst.setObject(i + 1, lst.get(i));
}
}
rSet = pst.executeQuery();
result = ResultSupport.toResult(rSet);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
this.close(rSet, pst, con);
}
}
return result;
}
/**
* omnipotence class update delete insert
*
* @param list &
* sql
* @return int
*/
public int runMode(String sql, List lst) {//数据库 添 删 改 操作
int count = 0;
con = this.getCon();
if (con != null) {
try {
pst = con.prepareStatement(sql);
if (lst.size() > 0) {
for (int i = 0; i < lst.size(); i++) {
pst.setObject(i + 1, lst.get(i));
}
}
count = pst.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
this.close(null, pst, con);
}
}
return count;
}