数据库框架DBUtils

数据库有关框架

1.框架：提高开发效率。按部就班

2.数据库框架：

　　ORM：Object Relation Mapping 对象关系映射。JavaBean --Object数据库----Relation

　　知名框架：Hibernate ，MyBatis，JPA（Java Persist API：JavaEE技术之一，ORM标准）

-----------------------------------------------------------------------------------------------------------------------

DBUtils，Spring JDBCTemplate不能算是ORM框架，只是对JDBC编码进行简化处理

DBUtils框架

　　　QueryRunner：

　　　　　1.构造方法：

　　　　　　　　　　QueryRunner（）默认构造方法；

　　　　　　　　　　QueryRunner（DataSource ds）需要一个数据源；

　　　　　2.具体方法：

batch：批处理　　　　　　

int[] batch(Connection conn, String sql, Object[][] params)  // Execute a batch of SQL INSERT, UPDATE, or DELETE queries.使用默认构造方法时
int[] batch(String sql, Object[][] params) //  Execute a batch of SQL INSERT, UPDATE, or DELETE queries.

Object[][]params:高维：执行的语句条数，低维：每条语句需要的参数

query：查询

　　　　　　　　　　public <T> T query(String sql,ResultSetHandler<T> rsh,Object... params)throws SQLException  

////Executes the given SELECT SQL query and returns a result object. The Connection is retrieved from the DataSource set in the constructor.

　　　　　　　　　　public <T> T query(Connection conn，String sql,ResultSetHandler<T> rsh, Object... params)throws SQLException

////Executes the given SELECT SQL query and returns a result object. The Connection is retrieved from the DataSource set in the constructor.

View Code

update：DML INSERT UPDATE DELETE

int update(String sql, Object... params)   Executes the given INSERT, UPDATE, or DELETE SQL statement.

　　　　　　　　　　update(Connection conn,String sql, Object... params)    Executes the given INSERT, UPDATE, or DELETE SQL statement.

View Code

注意：多次调用update方法，需要在一个事物中，用update(Connection conn,String sql, Object... params)，用默认构造方法。

例子：

 1 import java.io.File;
 2 import java.io.FileInputStream;
 3 import java.io.FileReader;
 4 import java.io.InputStream;
 5 import java.io.Reader;
 6 import java.sql.Blob;
 7 import java.sql.Clob;
 8 import java.sql.SQLException;
 9 import java.util.Date;
10 
11 import javax.sql.rowset.serial.SerialBlob;
12 import javax.sql.rowset.serial.SerialClob;
13 
14 import org.apache.commons.dbutils.QueryRunner;
15 import org.junit.Test;
16 
17 import com.itheima.util.DBCPUtil;
18 
19 /*
20 create table student(
21     id int primary key,
22     name varchar(100),
23     birthday date
24 );
25  */
26 public class DBUtilDemo1 {
27     private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
28     @Test
29     public void testAdd() throws SQLException{
30         qr.update("insert into student values(?,?,?)", 1,"wf",new Date());
31     }
32 //    @Test
33 //    public void testAdd1() throws SQLException{
34 //        qr.update("insert into student values(?,?,?)", 2,"qhs","1930-09-08");
35 //    }
36     
37     /*
38      * create table t1(id int primary key,content longblob);
39      */
40     @Test 
41     public void testBlob() throws Exception{
42 //        InputStream in = new FileInputStream("src/20.jpg");
43 //        qr.update("insert into t1 values(?,?)", 1,in);
44         
45         InputStream in = new FileInputStream("src/20.jpg");
46         byte b[] = new byte[in.available()];
47         in.read(b);
48         in.close();
49         Blob blob = new SerialBlob(b);
50         qr.update("insert into t1 values(?,?)", 2,blob);
51     }
52     /*
53      * create table t2(id int primary key,content longtext);
54      */
55     @Test 
56     public void testClob() throws Exception{
57         File file = new File("src/jpm.txt");
58         Reader r = new FileReader(file);
59         char ch[] = new char[(int)file.length()];
60         r.read(ch);
61         r.close();
62         Clob c = new SerialClob(ch);
63         qr.update("insert into t2 values(?,?)", 1,c);
64     }
65     /*
66      * create table t3(id int,name varchar(100));
67      */
68     @Test
69     public void testBatch()throws Exception{
70         
71         Object params[][] = new Object[10][];
72         for(int i=0;i<params.length;i++){
73             params[i] = new Object[]{i+1,"aaa"+(i+1)};
74         }
75         qr.batch("insert into t3 values(?,?)", params);
76     }
77 }

DBUtilDemo

 1 import java.io.InputStream;
 2 import java.sql.Connection;
 3 import java.sql.ResultSet;
 4 import java.sql.SQLException;
 5 import java.sql.Statement;
 6 import java.util.Properties;
 7 
 8 import javax.sql.DataSource;
 9 
10 import org.apache.commons.dbcp.BasicDataSourceFactory;
11 
12 public class DBCPUtil {
13     private static DataSource dataSource;
14     static{
15         try {
16             InputStream in = DBCPUtil.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
17             Properties props = new Properties();
18             props.load(in);
19             dataSource = BasicDataSourceFactory.createDataSource(props);
20         } catch (Exception e) {
21             throw new ExceptionInInitializerError("error");
22         }
23     }
24     public static Connection getConnection(){
25         try {
26             return dataSource.getConnection();
27         } catch (SQLException e) {
28             throw new RuntimeException("error");
29         }
30     }
31     public static DataSource getDataSource(){
32         return dataSource;
33     }
34     
35 }

DBCPtil

 1 driverClassName=com.mysql.jdbc.Driver
 2 url=jdbc:mysql://localhost:3306/day17
 3 username=root
 4 password=123456
 5 initialSize=10
 6 maxActive=50
 7 maxIdle=20
 8 minIdle=5
 9 maxWait=60000
10 connectionProperties=useUnicode=true;characterEncoding=utf8
11 defaultAutoCommit=true
12 defaultReadOnly=
13 defaultTransactionIsolation=REPEATABLE_READ

dbcpconfig.properties

DBUtils所有的结果处理器（查询返回的结果集）

封装的结果是什么样的：

ArrayHandler：适用于结果集只有一条结果的情况。返回Obejct[],数组中的元素就是记录的每列数据。

ArrayListHandler：适用于结果集中有多条结果的情况。返回的是一个List<Object[]>,List封装了记录，Object[]每条记录的每列数据。

BeanHandler, BeanListHandler,

ColumnListHandler:适用于取某一列的值。返回的是List<Object>，集合中就是该列中的数据。

KeyedHandler:适用于结果中有多条的情况。返回的是一个Map<Object,Map<String,Object>>。

MapHandler:适用于结果只有一条的情况。Map<String,Object>,key是字段名，value，字段值。

MapListHandler：适用于结果有多条的情况。List<Map<String,Object>>.List封装了所有的记录，每条记录封装到Map中,key是字段名，value，字段值。

ScalarHandler：适用于结果中只有一行和只有一列的情况。返回的是一个Object。

例子：

 1 import java.util.List;
 2 import java.util.Map;
 3 
 4 import org.apache.commons.dbutils.QueryRunner;
 5 import org.apache.commons.dbutils.handlers.ArrayHandler;
 6 import org.apache.commons.dbutils.handlers.ArrayListHandler;
 7 import org.apache.commons.dbutils.handlers.ColumnListHandler;
 8 import org.apache.commons.dbutils.handlers.KeyedHandler;
 9 import org.apache.commons.dbutils.handlers.MapHandler;
10 import org.apache.commons.dbutils.handlers.MapListHandler;
11 import org.apache.commons.dbutils.handlers.ScalarHandler;
12 import org.junit.Test;
13 
14 import com.itheima.util.DBCPUtil;
15 
16 public class DBUtilDemo2 {
17     private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
18 
19     @Test
20     public void test1() throws Exception {
21         Object values[] = qr.query("select * from t3", new ArrayHandler());
22         for (Object obj : values)
23             System.out.println(obj);
24     }
25 
26     @Test
27     public void test2() throws Exception {
28         List<Object[]> records = qr.query("select * from t3",
29                 new ArrayListHandler());
30         for (Object[] values : records) {
31             System.out.println("------------------");
32             for (Object obj : values)
33                 System.out.println(obj);
34         }
35     }
36 
37     @Test
38     public void test3() throws Exception {
39         List<Object> values = qr.query("select * from t3",
40                 new ColumnListHandler("name"));
41         for (Object obj : values)
42             System.out.println(obj);
43     }
44 
45     @Test
46     public void test4() throws Exception {
47         Map<Object, Map<String, Object>> bmap = qr.query("select * from t3",
48                 new KeyedHandler("id"));
49         for (Map.Entry<Object, Map<String, Object>> bme : bmap.entrySet()) {
50             System.out.println("----------------");
51             for (Map.Entry<String, Object> sme : bme.getValue().entrySet()) {
52                 System.out.println(sme.getKey() + "=" + sme.getValue());
53             }
54         }
55     }
56 
57     @Test
58     public void test5() throws Exception {
59         Map<String, Object> map = qr
60                 .query("select * from t3", new MapHandler());
61         for (Map.Entry<String, Object> sme : map.entrySet()) {
62             System.out.println(sme.getKey() + "=" + sme.getValue());
63         }
64     }
65 
66     @Test
67     public void test6() throws Exception {
68         List<Map<String, Object>> list = qr.query("select * from t3",
69                 new MapListHandler());
70         for (Map<String, Object> map : list) {
71             System.out.println("----------------");
72             for (Map.Entry<String, Object> sme : map.entrySet()) {
73                 System.out.println(sme.getKey() + "=" + sme.getValue());
74             }
75         }
76     }
77     @Test
78     public void test7() throws Exception {
79         Object obj = qr.query("select count(*) from t3",new ScalarHandler(1));
80         System.out.println(obj.getClass().getName());
81         System.out.println(obj);
82     }
83 }

DBUtilDemo

  1 1
  2 aaa1
  3 ------------------
  4 1
  5 aaa1
  6 ------------------
  7 2
  8 aaa2
  9 ------------------
 10 3
 11 aaa3
 12 ------------------
 13 4
 14 aaa4
 15 ------------------
 16 5
 17 aaa5
 18 ------------------
 19 6
 20 aaa6
 21 ------------------
 22 7
 23 aaa7
 24 ------------------
 25 8
 26 aaa8
 27 ------------------
 28 9
 29 aaa9
 30 ------------------
 31 10
 32 aaa10
 33 aaa1
 34 aaa2
 35 aaa3
 36 aaa4
 37 aaa5
 38 aaa6
 39 aaa7
 40 aaa8
 41 aaa9
 42 aaa10
 43 ----------------
 44 name=aaa1
 45 id=1
 46 ----------------
 47 name=aaa2
 48 id=2
 49 ----------------
 50 name=aaa3
 51 id=3
 52 ----------------
 53 name=aaa4
 54 id=4
 55 ----------------
 56 name=aaa5
 57 id=5
 58 ----------------
 59 name=aaa6
 60 id=6
 61 ----------------
 62 name=aaa7
 63 id=7
 64 ----------------
 65 name=aaa8
 66 id=8
 67 ----------------
 68 name=aaa9
 69 id=9
 70 ----------------
 71 name=aaa10
 72 id=10
 73 name=aaa1
 74 id=1
 75 ----------------
 76 name=aaa1
 77 id=1
 78 ----------------
 79 name=aaa2
 80 id=2
 81 ----------------
 82 name=aaa3
 83 id=3
 84 ----------------
 85 name=aaa4
 86 id=4
 87 ----------------
 88 name=aaa5
 89 id=5
 90 ----------------
 91 name=aaa6
 92 id=6
 93 ----------------
 94 name=aaa7
 95 id=7
 96 ----------------
 97 name=aaa8
 98 id=8
 99 ----------------
100 name=aaa9
101 id=9
102 ----------------
103 name=aaa10
104 id=10
105 java.lang.Long
106 10

运行结果

利用DBUtils进行事物有关操作：

 1 import java.sql.Connection;
 2 import java.sql.SQLException;
 3 
 4 import org.apache.commons.dbutils.QueryRunner;
 5 
 6 import com.itheima.dao.AccountDao;
 7 import com.itheima.util.DBCPUtil;
 8 
 9 public class AccountDaoImpl implements AccountDao {
10 
11     private QueryRunner qr=new QueryRunner();
12     /* (non-Javadoc)
13      * @see com.itheima.dao.impl.AccountDao#transfer(java.lang.String, java.lang.String, float)
14      */
15     @Override
16     public void transfer(String sourceAccount,String targetAccount,float money){
17         Connection conn=null;
18         try {
19             conn=DBCPUtil.getConnection();
20             conn.setAutoCommit(false);
21             qr.update(conn,"update account set money=money-? where name=?", money,sourceAccount);
22             qr.update(conn,"update account set money=money+? where name=?", money,targetAccount);
23 //            int x=1/0;
24             conn.commit();
25             int x=1/0;
26         } catch (SQLException e) {
27             // TODO Auto-generated catch block
28 //            throw new RuntimeException(e);
29             try {
30                 conn.rollback();
31             } catch (SQLException e1) {
32                 // TODO Auto-generated catch block
33                 e1.printStackTrace();
34             }
35         }
36         finally {
37             if(conn!=null)
38             try {
39                 conn.close();
40             } catch (Exception e) {
41                 // TODO: handle exception
42                 e.printStackTrace();
43             }
44         }
45     }
46     
47 }

DAO

 1 package com.itheima.service.impl;
 2 
 3 import com.itheima.dao.AccountDao;
 4 import com.itheima.dao.impl.AccountDaoImpl;
 5 import com.itheima.domain.Account;
 6 import com.itheima.service.BusinessService;
 7 
 8 public class BusinessServiceImpl implements BusinessService {
 9     private AccountDao dao=new AccountDaoImpl();
10     
11     
12     public void transfer(String sourceAccount,String targetAccount,float money){
13         dao.transfer(sourceAccount, targetAccount, money);
14     }
15 
16 }

service

 1 package com.itheima.test01;
 2 
 3 import com.itheima.service.BusinessService;
 4 import com.itheima.service.impl.BusinessServiceImpl;
 5 import com.itheima.service.impl.*;
 6 
 7 public class Client {
 8 
 9     public Client() {
10         // TODO Auto-generated constructor stub
11     }
12 
13     public static void main(String[] args) {
14         // TODO Auto-generated method stub
15             BusinessService s=new BusinessServiceImpl();
16             s.transfer("bbb","aaa",10);
17     }
18 
19 }

Client

事物管理

 1 import java.sql.SQLException;
 2 
 3 import org.apache.commons.dbutils.QueryRunner;
 4 import org.apache.commons.dbutils.handlers.BeanHandler;
 5 
 6 import com.itheima.dao.AccountDao;
 7 import com.itheima.domain.Account;
 8 import com.itheima.util.TransactionManager;
 9 
10 public class AccountDaoImpl implements AccountDao {
11     private QueryRunner qr = new QueryRunner();
12 
13     public Account findByName(String accountName) {
14         try {
15             return qr.query(TransactionManager.getConnection(),"select * from account where name=?", new BeanHandler<Account>(Account.class), accountName);
16         } catch (SQLException e) {
17             throw new RuntimeException(e);
18         }
19     }
20 
21     public void updateAccount(Account account) {
22         try {
23             qr.update(TransactionManager.getConnection(),"update account set money=? where name=?", account.getMoney(),account.getName());
24         } catch (SQLException e) {
25             throw new RuntimeException(e);
26         }
27     }
28     
29 }

DAO层

 1 import com.itheima.dao.AccountDao;
 2 import com.itheima.dao.impl.AccountDaoImpl;
 3 import com.itheima.domain.Account;
 4 import com.itheima.service.BusinessService;
 5 import com.itheima.util.TransactionManager;
 6 
 7 public class BusinessServiceImpl implements BusinessService {
 8     private AccountDao dao = new AccountDaoImpl();
 9     public void transfer(String sourceAccount,String targetAccount,float money){
10         try{
11             TransactionManager.startTransaction();
12             Account sAccount = dao.findByName(sourceAccount);
13             Account tAccount = dao.findByName(targetAccount);
14             
15             sAccount.setMoney(sAccount.getMoney()-money);
16             tAccount.setMoney(tAccount.getMoney()+money);
17             
18             dao.updateAccount(sAccount);
19             
20 //            int i=1/0;
21             
22             dao.updateAccount(tAccount);
23             TransactionManager.commit();
24         }catch(Exception e){
25             TransactionManager.rollback();
26             e.printStackTrace();
27         }finally{
28             TransactionManager.release();
29         }
30     }
31 }

Service层

 1 import com.itheima.service.BusinessService;
 2 import com.itheima.service.impl.BusinessServiceImpl;
 3 
 4 public class Client {
 5     //方法内的多次方法调用都处在同一个线程中
 6     public static void main(String[] args) {
 7         BusinessService s = new BusinessServiceImpl();
 8         s.transfer("bbb", "aaa", 100);
 9     }
10 
11 }

Client

ThreadLocal类

模拟ThreadLocal功能伪代码

public class ThreadLocal {
　　//容器
　　private Map<Runnable,Object> map=new HashMap<Runnable,Object>();
　　public void set(Object value){//向Map中存放数据
　　　　map.put(Thread.currentThread(),value)
　　}
　　public Object get(){//从Map中取数据
　　　　return map.get(Thread.currentThread());
　　}
}

特点：一个线程放的东西，除了自己谁也拿不到。线程局部变量

线程中的方法调用栈

借助ThreadLocal管理事物：

特点：1.方法内的多次方法调用都处在同一线程中，所以可以考虑，将Connection对象放入ThreadLocal中，这样只要在同一线程中，就是同一个对象调用的方法。

代码撸上：

 1 import java.sql.SQLException;
 2 
 3 import org.apache.commons.dbutils.QueryRunner;
 4 import org.apache.commons.dbutils.handlers.BeanHandler;
 5 
 6 import com.itheima.dao.AccountDao;
 7 import com.itheima.domain.Account;
 8 import com.itheima.util.TransactionManager;
 9 
10 public class AccountDaoImpl implements AccountDao {
11     private QueryRunner qr = new QueryRunner();
12 
13     public Account findByName(String accountName) {
14         try {
15             return qr.query(TransactionManager.getConnection(),"select * from account where name=?", new BeanHandler<Account>(Account.class), accountName);
16         } catch (SQLException e) {
17             throw new RuntimeException(e);
18         }
19     }
20 
21     public void updateAccount(Account account) {
22         try {
23             qr.update(TransactionManager.getConnection(),"update account set money=? where name=?", account.getMoney(),account.getName());
24         } catch (SQLException e) {
25             throw new RuntimeException(e);
26         }
27     }
28     
29 }

AccountDAOImpl

 1 package com.itheima.util;
 2 
 3 import java.io.InputStream;
 4 import java.sql.Connection;
 5 import java.sql.SQLException;
 6 import java.util.Properties;
 7 
 8 import javax.sql.DataSource;
 9 
10 import org.apache.commons.dbcp.BasicDataSourceFactory;
11 
12 public class TransactionManager {
13     private static DataSource dataSource;
14     private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
15     static{
16         try {
17             InputStream in = TransactionManager.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
18             Properties props = new Properties();
19             props.load(in);
20             dataSource = BasicDataSourceFactory.createDataSource(props);
21         } catch (Exception e) {
22             throw new ExceptionInInitializerError("初始化数据源失败");
23         }
24     }
25     public static Connection getConnection(){
26         try {
27             Connection conn = tl.get();//从当前线程中取连接
28             if(conn==null){
29                 conn = dataSource.getConnection();
30                 tl.set(conn);
31             }
32             return conn;
33         } catch (SQLException e) {
34             throw new RuntimeException("获取数据库连接失败");
35         }
36     }
37     public static void startTransaction(){
38         try {
39             Connection conn = getConnection();
40             conn.setAutoCommit(false);
41         } catch (SQLException e) {
42             throw new RuntimeException("开启事务失败");
43         }
44     }
45     public static void rollback(){
46         try {
47             Connection conn = getConnection();
48             conn.rollback();
49         } catch (SQLException e) {
50             throw new RuntimeException("回滚事务失败");
51         }
52     }
53     public static void commit(){
54         try {
55             Connection conn = getConnection();
56             conn.commit();
57         } catch (SQLException e) {
58             throw new RuntimeException("提交事务失败");
59         }
60     }
61     public static void release(){
62         try {
63             Connection conn = getConnection();
64             conn.close();
65             tl.remove();//从当前线程中解绑。服务器有关：用到了线程池。
66         } catch (SQLException e) {
67             throw new RuntimeException("关闭连接失败");
68         }
69     }
70 }

TransactionManager

 1 import com.itheima.dao.AccountDao;
 2 import com.itheima.dao.impl.AccountDaoImpl;
 3 import com.itheima.domain.Account;
 4 import com.itheima.service.BusinessService;
 5 import com.itheima.util.TransactionManager;
 6 
 7 public class BusinessServiceImpl implements BusinessService {
 8     private AccountDao dao = new AccountDaoImpl();
 9     public void transfer(String sourceAccount,String targetAccount,float money){
10         try{
11             TransactionManager.startTransaction();
12             Account sAccount = dao.findByName(sourceAccount);
13             Account tAccount = dao.findByName(targetAccount);
14             
15             sAccount.setMoney(sAccount.getMoney()-money);
16             tAccount.setMoney(tAccount.getMoney()+money);
17             
18             dao.updateAccount(sAccount);
19             
20 //            int i=1/0;
21             
22             dao.updateAccount(tAccount);
23             TransactionManager.commit();
24         }catch(Exception e){
25             TransactionManager.rollback();
26             e.printStackTrace();
27         }finally{
28             TransactionManager.release();
29         }
30     }
31 }

BusinessServiceImpl

 1 import com.itheima.service.BusinessService;
 2 import com.itheima.service.impl.BusinessServiceImpl;
 3 
 4 public class Client {
 5     //方法内的多次方法调用都处在同一个线程中
 6     public static void main(String[] args) {
 7         BusinessService s = new BusinessServiceImpl();
 8         s.transfer("bbb", "aaa", 100);
 9     }
10 
11 }

Client

AOP思想控制事物

 1 import java.lang.reflect.InvocationHandler;
 2 import java.lang.reflect.Method;
 3 import java.lang.reflect.Proxy;
 4 import java.util.HashSet;
 5 import java.util.Set;
 6 
 7 import com.itheima.service.BusinessService;
 8 import com.itheima.service.impl.BusinessServiceImpl;
 9 
10 public class BeanFactory {
11     private static Set<String> includeMethod = new HashSet<String>();//需要控制事务的方法
12     
13     static{
14         includeMethod.add("transfer");
15     }
16     
17     public static BusinessService getBusinessSerivce(){
18         final BusinessService s = new BusinessServiceImpl();
19         BusinessService proxyS = (BusinessService)Proxy.newProxyInstance(s.getClass().getClassLoader(), 
20                 s.getClass().getInterfaces(), 
21                 new InvocationHandler() {
22                     public Object invoke(Object proxy, Method method, Object[] args)
23                             throws Throwable {
24                         
25                         String methodName = method.getName();
26                         if(includeMethod.contains(methodName)){
27                         
28                             Object rtValue = null;
29                             try{
30                                 TransactionManager.startTransaction();
31                                 rtValue = method.invoke(s, args);
32                                 TransactionManager.commit();
33                             }catch(Exception e){
34                                 TransactionManager.rollback();
35                                 e.printStackTrace();
36                             }finally{
37                                 TransactionManager.release();
38                             }
39                             return rtValue;
40                         }else{
41                             return method.invoke(s, args);
42                         }
43                     }
44                 });
45         return proxyS;
46     }
47 }

BeanFactory

 1 import java.io.InputStream;
 2 import java.sql.Connection;
 3 import java.sql.SQLException;
 4 import java.util.Properties;
 5 
 6 import javax.sql.DataSource;
 7 
 8 import org.apache.commons.dbcp.BasicDataSourceFactory;
 9 
10 public class TransactionManager {
11     private static DataSource dataSource;
12     private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
13     static{
14         try {
15             InputStream in = TransactionManager.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
16             Properties props = new Properties();
17             props.load(in);
18             dataSource = BasicDataSourceFactory.createDataSource(props);
19         } catch (Exception e) {
20             throw new ExceptionInInitializerError("初始化数据源失败");
21         }
22     }
23     public static Connection getConnection(){
24         try {
25             Connection conn = tl.get();//从当前线程中取连接
26             if(conn==null){
27                 conn = dataSource.getConnection();
28                 tl.set(conn);
29             }
30             return conn;
31         } catch (SQLException e) {
32             throw new RuntimeException("获取数据库连接失败");
33         }
34     }
35     public static void startTransaction(){
36         try {
37             Connection conn = getConnection();
38             conn.setAutoCommit(false);
39         } catch (SQLException e) {
40             throw new RuntimeException("开启事务失败");
41         }
42     }
43     public static void rollback(){
44         try {
45             Connection conn = getConnection();
46             conn.rollback();
47         } catch (SQLException e) {
48             throw new RuntimeException("回滚事务失败");
49         }
50     }
51     public static void commit(){
52         try {
53             Connection conn = getConnection();
54             conn.commit();
55         } catch (SQLException e) {
56             throw new RuntimeException("提交事务失败");
57         }
58     }
59     public static void release(){
60         try {
61             Connection conn = getConnection();
62             conn.close();
63             tl.remove();//从当前线程中解绑。服务器有关：用到了线程池。
64         } catch (SQLException e) {
65             throw new RuntimeException("关闭连接失败");
66         }
67     }
68 }

TransactionManager

 1 import com.itheima.dao.AccountDao;
 2 import com.itheima.dao.impl.AccountDaoImpl;
 3 import com.itheima.domain.Account;
 4 import com.itheima.service.BusinessService;
 5 
 6 public class BusinessServiceImpl implements BusinessService {
 7     private AccountDao dao = new AccountDaoImpl();
 8     public void transfer(String sourceAccount,String targetAccount,float money){
 9         Account sAccount = dao.findByName(sourceAccount);
10         Account tAccount = dao.findByName(targetAccount);
11         
12         sAccount.setMoney(sAccount.getMoney()-money);
13         tAccount.setMoney(tAccount.getMoney()+money);
14         dao.updateAccount(sAccount);
15 //            int i=1/0;
16         
17         dao.updateAccount(tAccount);
18     }
19 }

BusinessServiceImpl

 1 import java.sql.SQLException;
 2 
 3 import org.apache.commons.dbutils.QueryRunner;
 4 import org.apache.commons.dbutils.handlers.BeanHandler;
 5 
 6 import com.itheima.dao.AccountDao;
 7 import com.itheima.domain.Account;
 8 import com.itheima.util.TransactionManager;
 9 
10 public class AccountDaoImpl implements AccountDao {
11     private QueryRunner qr = new QueryRunner();
12 
13     public Account findByName(String accountName) {
14         try {
15             return qr.query(TransactionManager.getConnection(),"select * from account where name=?", new BeanHandler<Account>(Account.class), accountName);
16         } catch (SQLException e) {
17             throw new RuntimeException(e);
18         }
19     }
20 
21     public void updateAccount(Account account) {
22         try {
23             qr.update(TransactionManager.getConnection(),"update account set money=? where name=?", account.getMoney(),account.getName());
24         } catch (SQLException e) {
25             throw new RuntimeException(e);
26         }
27     }
28     
29 }

AccountDAOImpl

 1 import com.itheima.service.BusinessService;
 2 import com.itheima.service.impl.BusinessServiceImpl;
 3 import com.itheima.util.BeanFactory;
 4 
 5 public class Client {
 6     //方法内的多次方法调用都处在同一个线程中
 7     public static void main(String[] args) {
 8         BusinessService s = BeanFactory.getBusinessSerivce();
 9         s.transfer("bbb", "aaa", 100);
10     }
11 
12 }

Client

利用DBUtils进行事物有关操作：

　　在同一

利用DBUtils进行多表操作

DAO层：

 1 package com.itheima.dao.impl;
 2 
 3 import org.apache.commons.dbutils.QueryRunner;
 4 import org.apache.commons.dbutils.handlers.BeanHandler;
 5 
 6 import com.itheima.domain.IdCard;
 7 import com.itheima.domain.Person;
 8 import com.itheima.util.DBCPUtil;
 9 
10 public class PersonDaoImpl {
11     private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
12     public void addPerson(Person p){
13         try{
14             qr.update("insert into person values(?,?)", p.getId(),p.getName());
15             IdCard idcard = p.getIdCard();
16             if(idcard!=null){
17                 qr.update("insert into id_card values(?,?)", p.getId(),idcard.getNum());
18             }
19         }catch(Exception e){
20             throw new RuntimeException(e);
21         }
22     }
23     //关联的idcard要不要查：建议查出来
24     public Person findPersonById(int personId){
25         try{
26             Person p = qr.query("select * from person where id=?", new BeanHandler<Person>(Person.class),personId);
27             if(p!=null){
28                 IdCard idcard = qr.query("select * from id_card where id=?", new BeanHandler<IdCard>(IdCard.class),personId);
29                 p.setIdCard(idcard);
30             }
31             return p;
32         }catch(Exception e){
33             throw new RuntimeException(e);
34         }
35     }
36 }

PersonDaoImpl

 1 import java.sql.SQLException;
 2 import java.util.List;
 3 
 4 import org.apache.commons.dbutils.QueryRunner;
 5 import org.apache.commons.dbutils.handlers.BeanHandler;
 6 import org.apache.commons.dbutils.handlers.BeanListHandler;
 7 
 8 import com.itheima.domain.Department;
 9 import com.itheima.domain.Employee;
10 import com.itheima.util.DBCPUtil;
11 
12 //以部门为出发点进行操作
13 public class DepartmentDaoImpl {
14     private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
15     public void addDepartment(Department d){
16         try {
17             //保存部门的基本信息
18             qr.update("insert into department values(?,?)", d.getId(),d.getName());
19             //判断部门下面是否有员工
20             List<Employee> emps = d.getEmps();
21             if(emps!=null&&emps.size()>0){
22                 //有：保存员工信息。depart_id就是部门的id
23                 for(Employee e:emps){
24                     qr.update("insert into employee values(?,?,?,?)", e.getId(),e.getName(),e.getSalary(),d.getId());
25                 }
26             }
27         } catch (SQLException e) {
28             throw new RuntimeException(e);
29         }
30     }
31     //部门关联的员工要不要查：看需求 
32     public Department getDepartmentById(int departmentId){
33         try {
34             Department d = qr.query("select * from department where id=?", new BeanHandler<Department>(Department.class), departmentId);
35             if(d!=null){
36                 //查询该部门下的员工
37                 List<Employee> emps = qr.query("select * from employee where depart_id=?", new BeanListHandler<Employee>(Employee.class), departmentId);
38                 d.setEmps(emps);
39             }
40             return d;
41         } catch (SQLException e) {
42             throw new RuntimeException(e);
43         }
44     }
45 }

DepartmentDaoImpl

 1 import java.sql.SQLException;
 2 import java.util.List;
 3 
 4 import org.apache.commons.dbutils.QueryRunner;
 5 import org.apache.commons.dbutils.handlers.BeanHandler;
 6 import org.apache.commons.dbutils.handlers.BeanListHandler;
 7 
 8 import com.itheima.domain.Student;
 9 import com.itheima.domain.Teacher;
10 import com.itheima.util.DBCPUtil;
11 
12 public class TeacherDaoImpl {
13     private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
14 
15     public void addTeacher(Teacher t) {
16         try {
17             // 保存老师信息
18             qr.update("insert into teacher values(?,?,?)", t.getId(),
19                     t.getName(), t.getSalary());
20             // 看老师有没有关联的学生
21             List<Student> students = t.getStudents();
22             if (students != null && students.size() > 0) {
23                 // 如果有：
24                 for (Student s : students) {
25                     // 看看学员在数据库中存在吗
26                     Student stu = qr.query("select * from student where id=?",
27                             new BeanHandler<Student>(Student.class), s.getId());
28                     if (stu == null) {
29                         // 不存在才插入学生信息
30                         qr.update("insert into student values(?,?,?)",
31                                 s.getId(), s.getName(), s.getGrade());
32                     }
33                     // 不管学生存在还是不存在，都得维护关系
34                     qr.update("insert into teacher_student values(?,?)",
35                             t.getId(), s.getId());
36                 }
37             }
38         } catch (SQLException e) {
39             e.printStackTrace();
40         }
41 
42     }
43 
44     // 学生要不要查：看需求
45     public Teacher findTeacherById(int teacherId) {
46         try {
47             Teacher t = qr.query("select * from teacher where id=?",new BeanHandler<Teacher>(Teacher.class), teacherId);
48             if(t!=null){
49                 //查学生信息
50 //                String sql = "select * from student where id in (select s_id from teacher_student where t_id=?)";
51 //                String sql = "select s.* from student s,teacher_student ts where s.id=ts.s_id and ts.t_id=?";
52                 String sql = "select s.* from student s inner join teacher_student ts on s.id=ts.s_id where ts.t_id=?";
53                 List<Student> students = qr.query(sql, new BeanListHandler<Student>(Student.class),teacherId);
54                 t.setStudents(students);
55             }
56             return t;
57         } catch (SQLException e) {
58             throw new RuntimeException(e);
59         }
60     }
61 }

TeacherDaoImpl

实体类：

 1 package com.itheima.domain;
 2 
 3 import java.util.ArrayList;
 4 import java.util.List;
 5 //一对多：one2many
 6 public class Department {
 7     private int id;
 8     private String name;
 9     private List<Employee> emps = new ArrayList<Employee>();
10     public int getId() {
11         return id;
12     }
13     public void setId(int id) {
14         this.id = id;
15     }
16     public String getName() {
17         return name;
18     }
19     public void setName(String name) {
20         this.name = name;
21     }
22     public List<Employee> getEmps() {
23         return emps;
24     }
25     public void setEmps(List<Employee> emps) {
26         this.emps = emps;
27     }
28     @Override
29     public String toString() {
30         return "Department [id=" + id + ", name=" + name + ", emps=" + emps
31                 + "]";
32     }
33     
34     
35 }

Department

 1 package com.itheima.domain;
 2 //多对一:many 2 one
 3 public class Employee {
 4     private int id;
 5     private String name;
 6     private float salary;
 7     private Department department;
 8     public int getId() {
 9         return id;
10     }
11     public void setId(int id) {
12         this.id = id;
13     }
14     public String getName() {
15         return name;
16     }
17     public void setName(String name) {
18         this.name = name;
19     }
20     public float getSalary() {
21         return salary;
22     }
23     public void setSalary(float salary) {
24         this.salary = salary;
25     }
26     public Department getDepartment() {
27         return department;
28     }
29     public void setDepartment(Department department) {
30         this.department = department;
31     }
32     @Override
33     public String toString() {
34         return "Employee [id=" + id + ", name=" + name + ", salary=" + salary
35                 + ", department=" + department + "]";
36     }
37     
38 }

Employee

 1 package com.itheima.domain;
 2 
 3 public class IdCard {
 4     private int id;
 5     private String num;
 6     private Person person;
 7     public int getId() {
 8         return id;
 9     }
10     public void setId(int id) {
11         this.id = id;
12     }
13     public String getNum() {
14         return num;
15     }
16     public void setNum(String num) {
17         this.num = num;
18     }
19     public Person getPerson() {
20         return person;
21     }
22     public void setPerson(Person person) {
23         this.person = person;
24     }
25     @Override
26     public String toString() {
27         return "IdCard [id=" + id + ", num=" + num + ", person=" + person + "]";
28     }
29     
30 }

IdCard

 1 package com.itheima.domain;
 2 
 3 import java.util.ArrayList;
 4 import java.util.List;
 5 //many 2 many
 6 public class Student {
 7     private int id;
 8     private String name;
 9     private String grade;
10     private List<Teacher> teachers = new ArrayList<Teacher>();
11     public int getId() {
12         return id;
13     }
14     public void setId(int id) {
15         this.id = id;
16     }
17     public String getName() {
18         return name;
19     }
20     public void setName(String name) {
21         this.name = name;
22     }
23     public String getGrade() {
24         return grade;
25     }
26     public void setGrade(String grade) {
27         this.grade = grade;
28     }
29     public List<Teacher> getTeachers() {
30         return teachers;
31     }
32     public void setTeachers(List<Teacher> teachers) {
33         this.teachers = teachers;
34     }
35     @Override
36     public String toString() {
37         return "Student [id=" + id + ", name=" + name + ", grade=" + grade
38                 + ", teachers=" + teachers + "]";
39     }
40     
41 }

student

 1 package com.itheima.domain;
 2 
 3 import java.util.ArrayList;
 4 import java.util.List;
 5 //many 2 many
 6 public class Teacher {
 7     private int id;
 8     private String name;
 9     private float salary;
10     private List<Student> students = new ArrayList<Student>();
11     public int getId() {
12         return id;
13     }
14     public void setId(int id) {
15         this.id = id;
16     }
17     public String getName() {
18         return name;
19     }
20     public void setName(String name) {
21         this.name = name;
22     }
23     public float getSalary() {
24         return salary;
25     }
26     public void setSalary(float salary) {
27         this.salary = salary;
28     }
29     public List<Student> getStudents() {
30         return students;
31     }
32     public void setStudents(List<Student> students) {
33         this.students = students;
34     }
35     @Override
36     public String toString() {
37         return "Teacher [id=" + id + ", name=" + name + ", salary=" + salary
38                 + ", students=" + students + "]";
39     }
40     
41 }

Teacher

 1 package com.itheima.domain;
 2 
 3 public class Person {
 4     private int id;
 5     private String name;
 6     private IdCard idCard;
 7     public int getId() {
 8         return id;
 9     }
10     public void setId(int id) {
11         this.id = id;
12     }
13     public String getName() {
14         return name;
15     }
16     public void setName(String name) {
17         this.name = name;
18     }
19     public IdCard getIdCard() {
20         return idCard;
21     }
22     public void setIdCard(IdCard idCard) {
23         this.idCard = idCard;
24     }
25     @Override
26     public String toString() {
27         return "Person [id=" + id + ", name=" + name + ", idCard=" + idCard
28                 + "]";
29     }
30     
31 }

Person

油条类：

 1 package com.itheima.util;
 2 
 3 import java.io.InputStream;
 4 import java.sql.Connection;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7 import java.sql.Statement;
 8 import java.util.Properties;
 9 
10 import javax.sql.DataSource;
11 
12 import org.apache.commons.dbcp.BasicDataSourceFactory;
13 
14 public class DBCPUtil {
15     private static DataSource dataSource;
16     static{
17         try {
18             InputStream in = DBCPUtil.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
19             Properties props = new Properties();
20             props.load(in);
21             dataSource = BasicDataSourceFactory.createDataSource(props);
22         } catch (Exception e) {
23             throw new ExceptionInInitializerError("初始化数据源失败");
24         }
25     }
26     public static Connection getConnection(){
27         try {
28             return dataSource.getConnection();
29         } catch (SQLException e) {
30             throw new RuntimeException("获取数据库连接失败");
31         }
32     }
33     public static DataSource getDataSource(){
34         return dataSource;
35     }
36     
37 }

DBCPutil

测试类：

 1 package com.itheima.util;
 2 
 3 import java.io.InputStream;
 4 import java.sql.Connection;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7 import java.sql.Statement;
 8 import java.util.Properties;
 9 
10 import javax.sql.DataSource;
11 
12 import org.apache.commons.dbcp.BasicDataSourceFactory;
13 
14 public class DBCPUtil {
15     private static DataSource dataSource;
16     static{
17         try {
18             InputStream in = DBCPUtil.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
19             Properties props = new Properties();
20             props.load(in);
21             dataSource = BasicDataSourceFactory.createDataSource(props);
22         } catch (Exception e) {
23             throw new ExceptionInInitializerError("初始化数据源失败");
24         }
25     }
26     public static Connection getConnection(){
27         try {
28             return dataSource.getConnection();
29         } catch (SQLException e) {
30             throw new RuntimeException("获取数据库连接失败");
31         }
32     }
33     public static DataSource getDataSource(){
34         return dataSource;
35     }
36     
37 }

TeacherDaoImplTest

 1 package com.itheima.test;
 2 
 3 import org.junit.Test;
 4 
 5 import com.itheima.dao.impl.DepartmentDaoImpl;
 6 import com.itheima.domain.Department;
 7 import com.itheima.domain.Employee;
 8 
 9 public class DepartmentDaoImplTest {
10     private DepartmentDaoImpl dao = new DepartmentDaoImpl();
11     @Test
12     public void testAdd(){
13         Department d = new Department();
14         d.setId(1);
15         d.setName("公关部");
16         
17         Employee e1 = new Employee();
18         e1.setId(1);
19         e1.setName("王斐");
20         e1.setSalary(10000);
21         
22         Employee e2 = new Employee();
23         e2.setId(2);
24         e2.setName("苑明星");
25         e2.setSalary(10000);
26         
27         //建立关联关系：从部门角度出发
28         d.getEmps().add(e1);
29         d.getEmps().add(e2);
30         
31         dao.addDepartment(d);
32     }
33     @Test
34     public void testQuery(){
35         Department d = dao.getDepartmentById(1);
36         System.out.println(d);
37         for(Employee e:d.getEmps())
38             System.out.println(e);
39     }
40 }

DepartmentDaoImplTest

 1 package com.itheima.test;
 2 
 3 import static org.junit.Assert.*;
 4 
 5 import org.junit.Test;
 6 
 7 import com.itheima.dao.impl.PersonDaoImpl;
 8 import com.itheima.domain.IdCard;
 9 import com.itheima.domain.Person;
10 
11 public class PersonDaoImplTest {
12     private PersonDaoImpl dao = new PersonDaoImpl();
13     @Test
14     public void testAddPerson() {
15         Person p = new Person();
16         p.setId(1);
17         p.setName("wzt");
18         
19         IdCard idcard = new IdCard();
20 //        idcard.setId(2);//设置应该无效
21         idcard.setNum("3701XXX");
22         
23         p.setIdCard(idcard);
24         
25         dao.addPerson(p);
26     }
27 
28     @Test
29     public void testFindPersonById() {
30         Person p = dao.findPersonById(1);
31         System.out.println(p);
32         IdCard idcard = p.getIdCard();
33         System.out.println(idcard);
34     }
35 
36 }

PersonDaoImplTest

合群是堕落的开始优秀的开始是孤行