bss系统管理的片段
package bbs.xzit.dbc;
import java.sql.*;
import java.util.Iterator;
import java.util.LinkedHashMap;
public class DatabaseUtil {
private DatabaseConnection dbc;
public DatabaseUtil(){
dbc = new DatabaseConnection();
}
// DML DDL ---insert update delete
//insert into table(f1,f2...) values(?,?...?)
public int executeUpdate(String sql,Object[] param) throws Exception {
Connection connect = dbc.getConn();
int num = 0;
PreparedStatement pstmt = null;
try {
pstmt = connect.prepareStatement(sql);
for(int i=0;i<param.length;i++){
pstmt.setObject(i+1, param[i]);
}
num = pstmt.executeUpdate();
} catch (SQLException ex) {
throw new Exception("错误提示：请检查SQL语法是否有误");
//throw ex ;
} finally{
close(connect,pstmt,null);
}
return num;
}
//select
public ResultSet executeQuery(String sql,Object[] param) throws Exception {
ResultSet rs = null;
Connection connect = dbc.getConn();
PreparedStatement pstmt = null;
try {
pstmt = connect.prepareStatement(sql);
for(int i=0;i<param.length;i++){
pstmt.setObject(i+1, param[i]);
}
rs = pstmt.executeQuery();
} catch (SQLException ex) {
throw new Exception("错误提示：请检查SQL语法是否有误,"+ex.getMessage());
}
return rs;
}
// oder by f1 asc,f2 dsc
public ResultSet executeQuery(String sql,Object[] param,LinkedHashMap order) throws Exception {
ResultSet rs = null;
Connection connect = dbc.getConn();
PreparedStatement pstmt = null;
String orderStr = "";
Iterator keys = order.keySet().iterator();
if(order.size()>0) orderStr = "order by ";
while(keys.hasNext()){
String key =(String)keys.next();
String keyValue = (String)order.get(key);
orderStr = orderStr + key +" " + keyValue +",";
}
orderStr.substring(0, orderStr.length()-2);
sql = sql + orderStr;
try {
pstmt = connect.prepareStatement(sql);
for(int i=0;i<param.length;i++){
pstmt.setObject(i+1, param[i]);
}
rs = pstmt.executeQuery();
} catch (SQLException ex) {
throw new Exception("错误提示：请检查SQL语法是否有误,"+ex.getMessage());
}
return rs;
}
public int executeCount(String sql,Object[] param) throws Exception {
ResultSet rs = null;
Connection connect = dbc.getConn();
PreparedStatement pstmt = null;
int cnt = 0;
try {
pstmt = connect.prepareStatement(sql);
for(int i=0;i<param.length;i++){
pstmt.setObject(i+1, param[i]);
}
rs = pstmt.executeQuery();
if(rs!=null){
cnt =rs.getInt(1);
}
} catch (SQLException ex) {
throw new Exception("错误提示："+ex.getMessage());
}finally{
close(connect,pstmt,rs);
}
return cnt;
}
public void close(Connection conn,PreparedStatement pstmt,ResultSet rs){
try{
if(rs!=null ) rs.close();
if(pstmt!=null) pstmt.close();
if(conn!=null) conn.close();
}catch(Exception ex){
}
}
}
