package com.news.dao;
import java.sql.*;
import java.util.SortedMap;
import java.util.ResourceBundle;
import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;
import org.apache.tomcat.dbcp.dbcp.BasicDataSource;
import com.news.po.PageInfo;
public final class DbHelper {
static BasicDataSource ds = new BasicDataSource();
static{
ResourceBundle res = ResourceBundle.getBundle("jdbc");
ds.setDriverClassName(res.getString("driver"));
ds.setUsername(res.getString("username"));
ds.setPassword(res.getString("password"));
ds.setUrl(res.getString("url"));
}
/**
* 打开连接
* @return --打开成功返回连接,否则返回null
*/
private static Connection getConnection(){
try {
return ds.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
/**
* 执行增删改
* @param sql --delete from Users where UserId=?
* @param params
* @return
*/
public static int executeNonQuery(String sql,Object...params){
Connection c = null;
PreparedStatement ps = null;
try {
c = getConnection();
ps = c.prepareStatement(sql);
if(params!=null && params.length>0){
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1, params[i]);
}
}
return ps.executeUpdate();
} catch (Exception e) {
throw new RuntimeException(e);
} finally{
try {
ps.close();
c.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 执行查询(适合返回多行多列的情况)
* @param sql
* @param params
* @return
*/
public static Result executeQuery(String sql,Object...params){
Connection c = null;
PreparedStatement ps = null;
ResultSet rs = null;
Result r = null;
try {
c = getConnection();
ps = c.prepareStatement(sql);
if(params!=null && params.length>0){
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1, params[i]);
}
}
rs = ps.executeQuery();
r = ResultSupport.toResult(rs);//将ResultSet封装成Result
return r;
} catch (Exception e) {
throw new RuntimeException(e);
} finally{
try {
rs.close();
ps.close();
c.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 执行查询(适合返回单行单列的情况)
* @param sql
* @param params
* @return
*/
public static Object executeScaler(String sql,Object...params){
Connection c = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
c = getConnection();
ps = c.prepareStatement(sql);
if(params!=null && params.length>0){
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1, params[i]);
}
}
rs = ps.executeQuery();
if(rs!=null && rs.next()){
return rs.getObject(1);
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally{
try {
rs.close();
ps.close();
c.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}
public static void pager(PageInfo pi){
Connection c = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
c = getConnection();
//查询总条数
String sql = "select count(*) from "+pi.getTableName();
if(null!=pi.getWhere() && !"".equals(pi.getWhere())){
sql+=" where "+pi.getWhere();
}
ps = c.prepareStatement(sql);
rs = ps.executeQuery();
if(rs!=null && rs.next()){
pi.setRecordCount(rs.getInt(1));
}
sql = "select * from "+pi.getTableName();
if(null!=pi.getWhere() && !"".equals(pi.getWhere())){
sql+=" where "+pi.getWhere();
}
sql+=" limit "+(pi.getPageIndex()*pi.getPageSize());
sql+=","+pi.getPageSize();
rs.close();
ps.close();
ps = c.prepareStatement(sql);
rs = ps.executeQuery();
Result r = ResultSupport.toResult(rs);
pi.setResult(r);
} catch (Exception e) {
throw new RuntimeException(e);
} finally{
try {
rs.close();
ps.close();
c.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
DBHelper高级封装
最新推荐文章于 2021-07-01 12:24:01 发布