dao层写法
public ArrayList<User> selectAll() throws Exception {
String sql = "select id,name,password,image from mall_user";
ArrayList<User> list = new ArrayList<User>();
// 如果执行了begin()函数，这里获取的是线程独有的连接，需要手动回滚提交
// 否则要那一条单独的连接用于自动
Connection conn = Connector.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet res = ps.executeQuery();
while (res.next()) {
int id = res.getInt("id");
String name = res.getString("name");
String password = res.getString("password");
String image = res.getString("image");
list.add(new User(id, name, password, image));
}
// 如果执行了begin()函数，这里什么都不做
// 否则直接在这里释放连接到连接池
Connector.autoFreeConnection(conn);
return list;
}
自动事务：
try {
userdao.selectByName(username);
} catch (Exception e) {
// log...
}
手动事务：
try {
Connector.begin();// 开启事务
userdao.insert(user1);
productdao.update(p1);
Connector.commit();// 关闭事务
} catch (Exception e) {
Connector.rollback(); // 事务回滚
//log...
}
封装：
import java.sql.*;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.BlockingQueue;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.logging.Logger;
import com.hour.mall.config.SqlConfig;
public class Connector {
static Logger log = Logger.getLogger(Connector.class.getName());
static final String JDBC_DRIVER = SqlConfig.MySql_JDBC_DRIVER;
static final String DB_URL = SqlConfig.DB_URL;
static final String USER = SqlConfig.USER;
static final String PASS = SqlConfig.PASS;
static final int maxNum = 128;
static AtomicInteger nowActive = new AtomicInteger(0);
static BlockingQueue<Connection> freeConnectionPool = new ArrayBlockingQueue<Connection>(128);
// 用于手动提交事务
static ThreadLocal<Connection> nowConnectionLocal = new ThreadLocal<>();
// 当前是手动还是自动
static ThreadLocal<Boolean> autoLocal = new ThreadLocal<>();
// 获取连接
public static Connection getConnection() throws Exception {
if (autoLocal.get() == null) {
// 自动提交就不用ThreadLocal了
log.info("当前事务：自动提交");
return doConnection();
} else {
// 手动提交用ThreadLocal，但是需要提前begin声明
log.info("当前事务：手动提交");
return nowConnectionLocal.get();
}
}
// 声明手动提交事务
public static void begin() throws Exception {
log.info("手动提交事务已开启");
nowConnectionLocal.set(doConnection());
nowConnectionLocal.get().setAutoCommit(false);
autoLocal.set(false);
}
// 手动提交
public static void commit() throws Exception {
if (nowConnectionLocal.get() != null) {
nowConnectionLocal.get().commit();
nowConnectionLocal.get().setAutoCommit(true);
log.info("手动提交事务已提交");
freeConnection(nowConnectionLocal.get());
nowConnectionLocal.remove();
autoLocal.set(true);
} else {
log.info("手动提交事务提交失败，当前无事务");
}
}
// 手动回滚
public static void rollback() throws Exception {
if (nowConnectionLocal.get() != null) {
try {
nowConnectionLocal.get().rollback();
nowConnectionLocal.get().setAutoCommit(true);
log.info("手动提交事务已回滚");
freeConnection(nowConnectionLocal.get());
nowConnectionLocal.remove();
autoLocal.set(true);
} catch (Exception e) {
log.info("手动提交事务回滚失败");
}
} else {
log.info("手动提交事务回滚失败，当前无事务");
}
}
/**
* 连接放入空闲区
*/
public static void autoFreeConnection(Connection connection) {
if (autoLocal.get()) {
freeConnectionPool.add(connection);
nowActive.getAndDecrement();
log.info("自动事务连接已自动释放");
}
}
/**
* 断开连接
*/
public static void closeConnection() {
for (Connection c : freeConnectionPool) {
try {
c.close();
} catch (SQLException e) {
System.out.println(c + "关闭失败");
}
}
}
/**
* 放入空闲区连接
*/
private static void freeConnection(Connection connection) {
if (connection != null) {
freeConnectionPool.add(connection);
nowActive.getAndDecrement();
}
}
/**
*
*
* @throws ClassNotFoundException
* @throws SQLException
* @throws InterruptedException
*/
public static Connection doConnection()
throws ClassNotFoundException, SQLException, InterruptedException {
Connection connection = null;
if(nowActive.get() < maxNum) {
// 注册 JDBC 驱动
Class.forName(JDBC_DRIVER);
// 打开链接
connection = DriverManager.getConnection(DB_URL, USER, PASS);
nowActive.getAndIncrement();
return connection;
}
while((connection = freeConnectionPool.poll()) == null){
}
return connection;
}
