此工具类使用了德鲁伊数据库连接池,需导入jar文件包
db.properties文件里的登录连接信息设置如下
import com.alibaba.druid.pool.DruidDataSource;
import java.sql.*;
import java.util.List;
import java.util.ResourceBundle;
/**
* 数据库连接工具类
*/
public class BaseDao {
//数据库连接对象
private Connection connection;
//预状态通道
private PreparedStatement pps;
//查询的sql得到的结果集合
private ResultSet resultSet;
//执行sql的影响行数
private int rows;
//连接地址
private static String url;
//驱动
private static String driver;
//登入信息
//用户名
private static String userName;
//密码
private static String passWord;
//德鲁伊连接池对象
private static DruidDataSource druidDataSource = new DruidDataSource();
//加载驱动
static {
//读取db.properties文件里的登录连接信息
ResourceBundle bundle = ResourceBundle.getBundle("db");
driver = bundle.getString("driverClassName");
url = bundle.getString("url");
userName = bundle.getString("userName");
passWord = bundle.getString("passWord");
//将驱动、连接地址、数据库登录信息等参数给德鲁伊连接池
druidDataSource.setUsername(userName);
druidDataSource.setPassword(passWord);
druidDataSource.setUrl(url);
druidDataSource.setDriverClassName(driver);
druidDataSource.setInitialSize(4);
}
//获得连接
protected Connection getConnection(){
try {
connection = druidDataSource.getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
//得到预状态通道
protected PreparedStatement getPps(String sql){
try {
pps = getConnection().prepareStatement(sql);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return pps;
}
//绑定参数 list保存的是给占位符所赋的值
protected void param(List list){
if (list!=null && list.size()>0){
for(int i=0;i<list.size();i++){
try {
pps.setObject(i+1,list.get(i));
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
//执行(增、删、改)操作,获得影响行数
protected int update(String sql,List list){
getPps(sql);
param(list);
try {
rows = pps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return rows;
}
//执行查询操作,获得结果集合
protected ResultSet query(String sql,List list){
getPps(sql);
param(list);
try {
resultSet = pps.executeQuery();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return resultSet;
}
//执行全表查询操作,不传参
protected ResultSet queryNoParm(String sql){
getPps(sql);
try {
resultSet = pps.executeQuery();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return resultSet;
}
//关闭资源
protected void closeAll(){
try {
if (resultSet != null){
//关闭查询语句
resultSet.close();
}
if (pps != null){
//关闭sql通道
pps.close();
}
if (connection != null){
//关闭数据库连接
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}