数据库有关框架
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.
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.
注意:多次调用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 }
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 }
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。
例子:
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 }
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 }
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 }
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 }
事物管理
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 }
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 }
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中,这样只要在同一线程中,就是同一个对象调用的方法。
代码撸上:
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 }
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 }
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 }
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思想控制事物
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 }
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 }
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 }
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 }
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层:
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 }
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 }
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 }
实体类:
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 }
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 }
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 }
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 }
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 }
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 }
油条类:
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 }
测试类:
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 }
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 }
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 }