数据池:
1.配置c3p0-cofig.xml(默认是这个文件)
2.修改JdbcUtils
3.jar包
配置文件里面的参数
1. 四大参数
2.初始化链接
3.最大连接数
4.最小连接数
5.自增长数
Baseservlet
使用方法
xxx/xxServlet?method=“add”;
dbutils
QueryRun的使用
qs.update(),delete(),insert()
查询方法
qs.query();
返回可以是
Map<String,Object> MapHandler();
List<Map<String,Object>> MapListHandler();
Object BeanHandler();
List BeanListHandler();
Object ScalarHandler();
1.JDBC入门
导jar包:驱动!
加载驱动类:Class.forName(“类名”);
给出url、username、password,其中url背下来!
使用DriverManager类来得到Connection对象!
2.什么是JDBC
用Java语言来操作数据库。
3. JDBC原理
JDBC是接口,而JDBC驱动才是接口的实现,没有驱动无法完成数据库连接
4.JDBC核心类(接口)介绍
a.DriverManger(驱动管理器):
注册驱动:这可以让JDBC知道要使用的是哪个驱动
获取Connection:如果可以获取到Connection,那么说明已经与数据库连接上了。
b. Connection
最为重要的一个方法就是用来获取Statement对象
c.Statement是用来向数据库发送SQL语句的
void executeUpdate(String sql):执行更新操作(insert、update、delete等);
ResultSet executeQuery(String sql):执行查询操作,数据库在执行查询后会把查询结果,查询结果就是ResultSet
代码:
public static Connection getConnection() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mydb1";
return DriverManager.getConnection(url, "root", "123");
}
@Test
public void insert() throws Exception {
Connection con = getConnection();
Statement stmt = con.createStatement();
String sql = "insert into user values('zhangSan', '123')";
stmt.executeUpdate(sql);
System.out.println("插入成功!");
}
@Test
public void update() throws Exception {
Connection con = getConnection();
Statement stmt = con.createStatement();
String sql = "update user set password='456' where username='zhangSan'";
stmt.executeUpdate(sql);
System.out.println("修改成功!");
}
@Test
public void delete() throws Exception {
Connection con = getConnection();
Statement stmt = con.createStatement();
String sql = "delete from user where username='zhangSan'";
stmt.executeUpdate(sql);
System.out.println("删除成功!");
}
@Test
public void query() throws Exception {
Connection con = getConnection();
Statement stmt = con.createStatement();
String sql = "select * from user";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
String username = rs.getString(1);
String password = rs.getString(2);
System.out.println(username + ", " + password);
}
}
5.规范化代码
public void query() {
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = getConnection();
stmt = con.createStatement();
String sql = "select * from user";
rs = stmt.executeQuery(sql);
while(rs.next()) {
String username = rs.getString(1);
String password = rs.getString(2);
System.out.println(username + ", " + password);
}
} catch(Exception e) {
throw new RuntimeException(e);
} finally {
try {
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(con != null) con.close();
} catch(SQLException e) {}
}
无论是否出现异常都要关闭 ResultSet、Statement,以及Connection
6.JDBC对象介绍
- ClassNotFoundException
a 没有给出mysql的jar包;
b 类名称打错了,查看类名是不是com.mysql.jdbc.Driver。 - SQLException:查看url是否打错
- Connection: Statement stmt = con.createStatement();
- Statement:
int executeUpdate (String sql):执行更新操作,即执行insert、update、delete语句,其实这个方法也可以执行create table、alter table,以及drop table等语句,但我们很少会使用JDBC来执行这些语句;
ResultSet executeQuery (String sql):执行查询操作,执行查询操作会返回ResultSet,即结果集。
7.ResultSet之获取列数据
可以通过next()方法使ResultSet的游标向下移动
String getString(int columnIndex):获取指定列的String类型数据;
int getInt(int columnIndex):获取指定列的int类型数据;
double getDouble(int columnIndex):获取指定列的double类型数据;
boolean getBoolean(int columnIndex):获取指定列的boolean类型数据;
Object getObject(int columnIndex):获取指定列的Object类型的数据。
参数columnIndex表示列的索引,列索引从1开始,而不是0
ResultSet还提供了一套通过列名称来获取列数据的方法 :
String getString(String columnName):获取名称为columnName的列的String数据;
int getInt(String columnName):获取名称为columnName的列的int数据;
double getDouble(String columnName):获取名称为columnName的列的double数据;
boolean getBoolean(String columnName):获取名称为columnName的列的boolean数据;
Object getObject(String columnName):获取名称为columnName的列的Object数据;
8.PreparedStatement
它是Statement接口的子接口;
防SQL攻击;
过滤用户输入的数据中是否包含非法字符;
分步交验!先使用用户名来查询用户,如果查找到了,再比较密码;
使用PreparedStatement。
提高代码的可读性、可维护性;
提高效率!
JDBCUtils
public class JdbcUtils {
private static final String dbconfig = "dbconfig.properties" ;
private static Properties prop = new Properties() ;
static {
try {
InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream(dbconfig);
prop.load(in);
Class.forName(prop.getProperty("driverClassName"));
} catch(IOException e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection () {
try {
return DriverManager.getConnection(prop.getProperty("url"),
prop.getProperty("username"), prop.getProperty("password"));
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
```java
dbconfig.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb1?useUnicode=true&characterEncoding=UTF8
username=root
password=123
实现Dao
User.java
public class User {
private String uid;
private String username;
private String password;
…
}
UserDao.java
public interface UserDao {
public void add(User user);
public void mod(User user);
public void del(String uid);
public User load(String uid);
public List<User> findAll();
}
UserDaoImpl.java
public class UserDaoImpl implements UserDao {
public void add(User user) {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = JdbcUtils.getConnection();
String sql = "insert into user value(?,?,?)";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, user.getUid());
pstmt.setString(2, user.getUsername());
pstmt.setString(3, user.getPassword());
pstmt.executeUpdate();
} catch(Exception e) {
throw new RuntimeException(e);
} finally {
try {
if(pstmt != null) pstmt.close();
if(con != null) con.close();
} catch(SQLException e) {}
}
}
public void mod(User user) {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = JdbcUtils.getConnection();
String sql = "update user set username=?, password=? where uid=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, user.getUsername());
pstmt.setString(2, user.getPassword());
pstmt.setString(3, user.getUid());
pstmt.executeUpdate();
} catch(Exception e) {
throw new RuntimeException(e);
} finally {
try {
if(pstmt != null) pstmt.close();
if(con != null) con.close();
} catch(SQLException e) {}
}
}
public void del(String uid) {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = JdbcUtils.getConnection();
String sql = "delete from user where uid=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, uid);
pstmt.executeUpdate();
} catch(Exception e) {
throw new RuntimeException(e);
} finally {
try {
if(pstmt != null) pstmt.close();
if(con != null) con.close();
} catch(SQLException e) {}
}
}
public User load(String uid) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = JdbcUtils.getConnection();
String sql = "select * from user where uid=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, uid);
rs = pstmt.executeQuery();
if(rs.next()) {
return new User(rs.getString(1), rs.getString(2), rs.getString(3));
}
return null;
} catch(Exception e) {
throw new RuntimeException(e);
} finally {
try {
if(pstmt != null) pstmt.close();
if(con != null) con.close();
} catch(SQLException e) {}
}
}
public List<User> findAll() {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = JdbcUtils.getConnection();
String sql = "select * from user";
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
List<User> userList = new ArrayList<User>();
while(rs.next()) {
userList.add(new User(rs.getString(1), rs.getString(2), rs.getString(3)));
}
return userList;
} catch(Exception e) {
throw new RuntimeException(e);
} finally {
try {
if(pstmt != null) pstmt.close();
if(con != null) con.close();
} catch(SQLException e) {}
}
}
}
UserDaoFactory.java
public class UserDaoFactory {
private static UserDao userDao;
static {
try {
InputStream in = Thread.currentThread().getContextClassLoader()
.getResourceAsStream("dao.properties");
Properties prop = new Properties();
prop.load(in);
String className = prop.getProperty("cn.itcast.jdbc.UserDao");
Class clazz = Class.forName(className);
userDao = (UserDao) clazz.newInstance();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static UserDao getUserDao() {
return userDao;
}
}
dao.properties
cn.itcast.jdbc.UserDao=cn.itcast.jdbc.UserDaoImpl
Java中的时间类型
java.sql包下给出三个与数据库相关的日期时间类型,分别是:
Date:表示日期,只有年月日,没有时分秒。会丢失时间;
Time:表示时间,只有时分秒,没有年月日。会丢失日期;
Timestamp:表示时间戳,有年月日时分秒,以及毫秒。
这三个类都是java.util.Date的子类。
事务:
同一事务中所有的操作,都在使用同一个Connection对象!
Connection的三个方法与事务相关:
con.setAutoCommit(false)表示开启事务!!!
commit():提交结束事务;con.commit();表示提交事务
rollback():回滚结束事务。con.rollback();表示回滚事务
public void transfer(boolean b) {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = JdbcUtils.getConnection();
//手动提交
con.setAutoCommit(false);
String sql = "update account set balance=balance+? where id=?";
pstmt = con.prepareStatement(sql);
//操作
pstmt.setDouble(1, -10000);
pstmt.setInt(2, 1);
pstmt.executeUpdate();
// 在两个操作中抛出异常
if(b) {
throw new Exception();
}
pstmt.setDouble(1, 10000);
pstmt.setInt(2, 2);
pstmt.executeUpdate();
//提交事务
con.commit();
} catch(Exception e) {
//回滚事务
if(con != null) {
try {
con.rollback();
} catch(SQLException ex) {}
}
throw new RuntimeException(e);
} finally {
//关闭
JdbcUtils.close(con, pstmt);
}
}
事务隔离级别
1 事务的并发读问题
脏读 :读取到另一个事务未提交数据;
事务1:张三给李四转账100元
事务2:李四查看自己的账户
t1:事务1:开始事务
t2:事务1:张三给李四转账100元
t3:事务2:开始事务
t4:事务2:李四查看自己的账户,看到账户多出100元(脏读)
t5:事务2:提交事务
t6:事务1:回滚事务,回到转账之前的状态
不可重复读:两次读取不一致;
事务1:酒店查看两次1048号房间状态
事务2:预订1048号房间
t1:事务1:开始事务
t2:事务1:查看1048号房间状态为空闲
t3:事务2:开始事务
t4:事务2:预定1048号房间
t5:事务2:提交事务
t6:事务1:再次查看1048号房间状态为使用
t7:事务1:提交事务
对同一记录的两次查询结果不一致!
幻读(虚读):读到另一事务已提交数据。
事务1:对酒店房间预订记录两次统计
事务2:添加一条预订房间记录
t1:事务1:开始事务
t2:事务1:统计预订记录100条
t3:事务2:开始事务
t4:事务2:添加一条预订房间记录
t5:事务2:提交事务
t6:事务1:再次统计预订记录为101记录
t7:事务1:提交
对同一表的两次查询不一致!
四大隔离级别
SERIALIZABLE(串行化)
REPEATABLE READ (可重复读)(MySQL)
READ COMMITTED (读已提交数据)(Oracle)
READ UNCOMMITTED (读未提交数据)
事务总结:
事务的特性:ACID;
事务开始边界与结束边界:开始边界(con.setAutoCommit(false)),结束边界(con.commit()或con.rollback());
事务的隔离级别: READ_UNCOMMITTED串行化、READ_COMMITTED可重复读、REPEATABLE_READ读已提交、SERIALIZABLE读未提交。多个事务并发执行时才需要考虑并发事务。
数据库连接池
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/mydb1</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">123</property>
<property name="acquireIncrement">3</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">2</property>
<property name="maxPoolSize">10</property>
</default-config>
}
配置文件要求:
文件名称:必须叫c3p0-config.xml
文件位置:必须在src下
Tomcat配置连接池
修改jdbcutils
public class JdbcUtils {
private static DataSource dataSource = new ComboPooledDataSource();
public static DataSource getDataSource() {
return dataSource;
}
public static Connection getConnection() {
try {
return dataSource.getConnection();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
ThreadLocal
ThreadLocal的内部是Map
BaseServlet
我们知道,写一个项目可能会出现N多个Servlet,而且一般一个Servlet只有一个方法(doGet或doPost),如果项目大一些,那么Servlet的数量就会很惊人。
为了避免Servlet的“膨胀”,我们写一个BaseServlet。它的作用是让一个Servlet可以处理多种不同的请求。不同的请求调用Servlet的不同方法。我们写好了BaseServlet后,让其他Servlet继承BaseServlet
// 例如:http://localhost:8080/demo1/xxx?m=add
String methodName = req.getParameter("method");// 它是一个方法名称
// 当没用指定要调用的方法时,那么默认请求的是execute()方法。
if(methodName == null || methodName.isEmpty()) {
methodName = "execute";
}
Class c = this.getClass();
try {
// 通过方法名称获取方法的反射对象
Method m = c.getMethod(methodName, HttpServletRequest.class,
HttpServletResponse.class);
// 反射方法目标方法,也就是说,如果methodName为add,那么就调用add方法。
String result = (String) m.invoke(this, req, res);
// 通过返回值完成请求转发
if(result != null && !result.isEmpty()) {
req.getRequestDispatcher(result).forward(req, res);
}
} catch (Exception e) {
throw new ServletException(e);
}
}
}
DBUtils:
DBUtils主要类
DbUtils:都是静态方法,一系列的close()方法;
QueryRunner:
update():执行insert、update、delete;
query():执行select语句;
batch():执行批处理。
QueryRunner之更新
QueryRunner的update()方法可以用来执行insert、update、delete语句。
public void fun1() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "insert into user values(?,?,?)";
qr.update(JdbcUtils.getConnection(), sql, "u1", "zhangSan", "123");
}
@Test
public void fun2() throws SQLException {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "insert into user values(?,?,?)";
qr.update(sql, "u1", "zhangSan", "123");
}
package it.cast.hema.review;
import java.io.IOException;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import com.sun.corba.se.spi.orbutil.fsm.Guard.Result;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;
import it.cast.hema.review.JdbcUtils;
public class Demo {
// 插入数据
@Test
public void insert() {
Connection con = null;
PreparedStatement pres = null;
try {
con = JdbcUtils.getConnection();
System.out.println(con);
String sql = "insert into stu values(?,?)";
PreparedStatement sta = con.prepareStatement(sql);
sta.setString(1, "1");
sta.setString(2, "zhangsan");
sta.executeUpdate();
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
try {
if (pres != null)
pres.close();
if (con != null)
con.close();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
// 查询
@Test
public void Query() {
Connection con = null;
PreparedStatement pres = null;
ResultSet rs = null;
try {
con = JdbcUtils.getConnection();
String sql = "select * from stu where username=?";
PreparedStatement sta = con.prepareStatement(sql);
sta.setString(1, "zhangsan");
rs = sta.executeQuery();
while (rs.next()) {
String username = rs.getString(1);
String password = rs.getString(2);
System.out.println(username + " " + password);
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
try {
if (rs != null)
rs.close();
if (pres != null)
pres.close();
if (con != null)
con.close();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
// 删除
@Test
public void delete() {
Connection con = null;
PreparedStatement pres = null;
try {
con = JdbcUtils.getConnection();
String sql = "delete from stu where id=?";
PreparedStatement sta = con.prepareStatement(sql);
sta.setString(1, "1");
sta.executeUpdate();
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
try {
if (pres != null)
pres.close();
if (con != null)
con.close();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
// 更新
@Test
public void update() {
Connection con = null;
PreparedStatement pres = null;
try {
con = JdbcUtils.getConnection();
String sql = "update stu set id=? where username=?";
PreparedStatement sta = con.prepareStatement(sql);
sta.setString(1, "2");
sta.setString(2, "zhangsan");
sta.executeUpdate();
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
try {
if (pres != null)
pres.close();
if (con != null)
con.close();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
// QueryRun
// MapHander一行
@Test
public void fun() {
QueryRunner qr = new QueryRunner();
String sql = "insert into stu values(?,?)";
try {
qr.update(JdbcUtils.getConnection(), sql, "2", "王五");
} catch (SQLException e) {
// TODO Auto-generated catch block
throw new RuntimeException(e);
}
}
// MapListhandler多行多列
@Test
public void fun1() {
QueryRunner qr = new QueryRunner();
String sql = "select * from stu";
try {
// Map<String,Object> map= qr.query(JdbcUtils.getConnection(), sql,
// new MapHandler(), "1");
List<Map<String, Object>> list = qr.query(
JdbcUtils.getConnection(), sql, new MapListHandler());
for (Map<String, Object> map : list) {
System.out.println(map);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
throw new RuntimeException(e);
}
}
// BeanHandler一行
@Test
public void fun2() {
QueryRunner qr = new QueryRunner();
// String sql = "select * from stu where id=?";
String sql = "select * from stu";
try {
// Student stu= qr.query(JdbcUtils.getConnection(), sql, new
// BeanHandler(it.cast.hema.review.Student.class), "1");
List<Student> list = qr.query(JdbcUtils.getConnection(), sql,
new BeanListHandler(Student.class));
for (Student stu : list) {
System.out.println(stu);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// List<Map<String,Object>> list=
// qr.query(JdbcUtils.getConnection(),sql,new MapListHandler());
}
// ColumnListHandler一列多行
@Test
public void fun3() {
QueryRunner qr = new QueryRunner();
// String sql = "select * from stu where id=?";
String sql = "select * from stu";
// try {
// Student stu= qr.query(JdbcUtils.getConnection(), sql, new
// BeanHandler(it.cast.hema.review.Student.class), "1");
// List<Object> list = qr.query(JdbcUtils.getConnection(), sql,
// new ColumnListHandler("username"));
// for (Object stu : list) {
// System.out.println(stu);
// }
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
// List<Map<String,Object>> list=
// qr.query(JdbcUtils.getConnection(),sql,new MapListHandler());
}
// ScalarHandler单行单列
@Test
public void fun4() {
QueryRunner qr = new QueryRunner();
String sql = "select * from stu where id=?";
// String sql = "select * from stu";
// try {
// Student stu= qr.query(JdbcUtils.getConnection(), sql, new
// BeanHandler(it.cast.hema.review.Student.class), "1");
// Object list = qr.query(JdbcUtils.getConnection(), sql,
// new ScalarHandler(), "1");
// for (Object stu : list) {
// System.out.println(stu);
// }
// System.out.println(list);
// } catch (SQLException e) {
// TODO Auto-generated catch block
// e.printStackTrace();
// }
// List<Map<String,Object>> list=
// qr.query(JdbcUtils.getConnection(),sql,new MapListHandler());
}
//
@Test
public void fun7() {
// QueryRunner qr = new QueryRunner();
// // String sql="insert into stu values(?,?)";
// String sql = "select * from stu where id=?";
// try {
// // qr.update(JdbcUtils.getConnection(), sql, "1","wang");
// // Map<String,Object> map= qr.query(JdbcUtils.getConnection(), sql,
// // new MapHandler(), "1");
// // List<Map<String,Object>> list=
// // qr.query(JdbcUtils.getConnection(), sql, new MapListHandler());
// // Object map= qr.query(JdbcUtils.getConnection(), sql, new
// // BeanHandler(Student.class),"1");
List<Object> list = qr.query(JdbcUtils.getConnection(), sql,new
BeanListHandler(Student.class));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Test
public void Dt(){
AccountSevice as = new AccountSevice();
as.transferservice("wangwu", "zhangsan",100);
}
}
使用事务来修改jdbcutils
但是,在Service中不应该出现Connection,它应该只在DAO中出现,因为它是JDBC的东西,JDBC的东西是用来连接数据库的,连接数据库是DAO的事儿!!!但是,事务是Service的事儿,不能放到DAO中!!!
public class JdbcUtils {
private static DataSource dataSource = new ComboPooledDataSource();
private static Connection con = null;
public static DataSource getDataSource() {
return dataSource;
}
public static Connection getConnection() throws SQLException {
if(con == null) {
return dataSource.getConnection();
}
return con;
}
public static void beginTranscation() throws SQLException {
if(con != null) {
throw new SQLException("事务已经开启,在没有结束当前事务时,不能再开启事务!");
}
con = dataSource.getConnection();
con.setAutoCommit(false);
}
public static void commitTransaction() throws SQLException {
if(con == null) {
throw new SQLException("当前没有事务,所以不能提交事务!");
}
con.commit();
con.close();
con = null;
}
public static void rollbackTransaction() throws SQLException {
if(con == null) {
throw new SQLException("当前没有事务,所以不能回滚事务!");
}
con.rollback();
con.close();
con = null;
}
}
再次修改:
现在JdbcUtils有个问题,如果有两个线程!第一个线程调用了beginTransaction()方法,另一个线程再调用beginTransaction()方法时,因为con已经不再为null,所以就会抛出异常了。
public class JdbcUtils {
private static DataSource dataSource = new ComboPooledDataSource();
private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
public static DataSource getDataSource() {
return dataSource;
}
public static Connection getConnection() throws SQLException {
Connection con = tl.get();
if(con == null) {
return dataSource.getConnection();
}
return con;
}
public static void beginTranscation() throws SQLException {
Connection con = tl.get();
if(con != null ) {
throw new SQLException("事务已经开启,在没有结束当前事务时,不能再开启事务!");
}
con = dataSource.getConnection();
con.setAutoCommit(false);
tl.set(con);
}
public static void commitTransaction() throws SQLException {
Connection con = tl.get();
if(con == null ) {
throw new SQLException("当前没有事务,所以不能提交事务!");
}
con.commit();
con.close();
tl.remove();
}
public static void rollbackTransaction() throws SQLException {
Connection con = tl.get();
if(con == null) {
throw new SQLException("当前没有事务,所以不能回滚事务!");
}
con.rollback();
con.close();
tl.remove();
}
}
转账:
public class AccountDao {
public void updateBalance(String name, double balance) throws SQLException {
String sql = "update account set balance=balance+? where name=?";
Connection con = JdbcUtils.getConnection();
QueryRunner qr = new QueryRunner();
qr.update(con, sql, balance, name);
}
}
public class AccountService {
private AccountDao dao = new AccountDao();
public void transfer(String from, String to, double balance) {
try {
JdbcUtils.beginTranscation();
dao.updateBalance(from, -balance);
dao.updateBalance(to, balance);
JdbcUtils.commitTransaction();
} catch(Exception e) {
try {
JdbcUtils.rollbackTransaction();
} catch (SQLException e1) {
throw new RuntimeException(e);
}
}
}
}
AccountService as = new AccountService();
as.transfer("zs", "ls", 100);