数据库框架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:查询
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.
update:DML INSERT UPDATE DELETE
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.
注意:多次调用update方法,需要在一个事物中,用update(Connection conn,String sql, Object... params)
,用默认构造方法。
例子:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
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。
例子:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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进行事物有关操作:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
事物管理
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
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中,这样只要在同一线程中,就是同一个对象调用的方法。
代码撸上:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
AOP思想控制事物
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
利用DBUtils进行事物有关操作:
在同一
利用DBUtils进行多表操作
DAO层:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
实体类:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
油条类:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
测试类:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
合群是堕落的开始 优秀的开始是孤行