1.首先下载两个jar包,一个是用于数据库连接池druid的jar包,一个是用于数据库的jar包。
2.为了简化JDBC连库的操作进行了数据库的封装封装代码如下。
工具类添加了事务的处理,通过数据访问层(dao)中QueryRunner,手动处理事务调用query或者update方法出现错误时可以进行事务的回滚。
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class DataDruidUtil{
//创建数据源对象
private static DruidDataSource dataSource;
private static ThreadLocal<Connection> threadLocal=new ThreadLocal<Connection>();
static {
try {
Properties properties=new Properties();
InputStream is = DataDruidUtil.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(is);
is.close();
dataSource= (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
System.out.println("初始化连接失败"+e.getMessage());
}
}
public static DruidDataSource getDataSource() {
return dataSource;
}
public static void closeAll(ResultSet rs, Statement stmt,Connection conn){
try {
if(rs!=null){
rs.close();
}
if(stmt!=null){
stmt.close();
}
if(conn!=null){
if(conn.getAutoCommit()){
conn.close();
threadLocal.remove();
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//手动处理事务获取连接
public static Connection getConnection(){
Connection connection = threadLocal.get();
try {
if (connection == null) {
connection = dataSource.getConnection();
threadLocal.set(connection);
}
return connection;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//与事务相关的方法
public static void begin()throws SQLException{
Connection connection = getConnection();
if (connection != null) {
connection.setAutoCommit(false);
}
}
public static void commit()throws SQLException{
Connection connection = getConnection();
if (connection != null) {
connection.commit();
}
}
public static void rollback()throws SQLException{
Connection connection = getConnection();
if (connection != null) {
connection.rollback();
}
}
public static void close()throws SQLException{
Connection connection = getConnection();
if (connection != null) {
connection.close();
threadLocal.remove();
}
}
}
3.druid.properties配置代码如下
druid.driverClassName=com.mysql.cj.jdbc.Driver druid.url=jdbc:mysql://localhost:3306/booksystem1?useSSL=false&characterEncoding=utf8 druid.username=root druid.password=123456 druid.initialSize=10 druid.maxActive=50 druid.minIdle=5 druid.maxWait=5000