Day16
Tip:事务
update from account set money=money+100 where name=‘b’;
update from account set money=money-100 where name=‘a’;
假如不将转账的两个sql语句放到同一个事物中去执行,有可能执行第一句sql成功了,增加了100元钱,但是在执行第二句sql
语句时抛了异常,这个时候就没有执行减去100,那么这个转账其实是存在不安全因素的,所以我们必须将这样的sql语句放到同
一个事物中执行,要不全部成功,要不全不成功
![](http://images0.cnblogs.com/blog/391262/201311/04154101-0d0cee59c02440209b3a3440b2579411.jpg)
create table account(
id int primary key auto_increment,
name varchar(40),
money float
)character set utf8 collate utf8_general_ci;
insert into account(name,money) values('aaa',1000);
insert into account(name,money) values('bbb',1000);
insert into account(name,money) values('ccc',1000);
![](http://images0.cnblogs.com/blog/391262/201311/04162913-6bd65fc1af3d4a07b5ddc8a2cb4a5f63.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/04163019-535f82e2c9b349beaa466d111c3638d4.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/04163534-11bad06fb45249dcb9a33e075c721b07.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/04163602-108af54bb91f45caaec3a76d2ecb3d74.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/04163705-f3303b10dcf443bea299c7c5f08b19ae.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/04165227-4fd94ff831204c3fa8334813f1df856b.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/04165600-1cfb25bafadf4c5ab6cc6d69f89acf3f.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/04170030-2fcdfe27a0274cebacfeeef2ea105170.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/04170303-67a51a488c974b7e8102c2fc5c179f69.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/04170527-33f94b3cc9ce4fd5855f209a1d17ea09.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/04170628-ee2a9e731b8c4fe8b1d322503360ba7c.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/04172927-b5e71a6d1ac34e91a69851e24d324fea.jpg)
Tip:使用事务
![](http://images0.cnblogs.com/blog/391262/201311/04193122-fe0b18ec2c8e4b2f85c99b1ef8ff1e53.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/04193627-f659fbaf246f47188edb5aedc08abaf3.jpg)
package cn.itcast.demo; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import cn.itcast.utils.JdbcUtils_Dbcp; public class Demo1 { /** * 事务:a--->b转帐100元 * @param args * @throws SQLException */ public static void main(String[] args) throws SQLException { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils_Dbcp.getConnection(); //这句话的意思是关闭自动提交,相当于start transaction开启事务 conn.setAutoCommit(false); //禁止自动开启事务,也就是设置手动开启事务 String sql1 = "update account set money=money-100 where name='aaa'"; st = conn.prepareStatement(sql1); //将sql语句预编译一下 st.executeUpdate(); String sql2 = "update account set money=money+100 where name='bbb'"; st = conn.prepareStatement(sql2); st.executeUpdate(); //模拟抛异常了,虽然数据库收到了sql语句,但是抛了异常,如果没有catch住异常,数据库会自动回滚了 //在开发中我们不希望数据库自动回滚,所以我们用catch抓住异常,记录遗产,手动回滚 //int x = 1/0; conn.commit(); //执行完slq语句之后,一定要记住commit提交事务 System.out.println("转帐成功"); }catch (Exception e) { e.printStackTrace(); //抓住异常 conn.rollback(); //手动回滚,其实是我们向数据库发rollback命令,通知数据库主动回滚 }finally{ JdbcUtils_Dbcp.release(conn, st, rs); } } }
默认情况下只要没有提交事务commit就不会真正执行sql语句
Tip:演示银行转帐案例
update from account set money=money-100 where name=‘a’;
update from account set money=money+100 where name=‘b’;
/day16/src/cn/itcast/demo/Demo2.java
以下测试我还是失败了,大概我的Mysql是集成安装的,需要修改一些配置文件吧
package cn.itcast.demo; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Savepoint; import cn.itcast.utils.JdbcUtils_Dbcp; public class Demo2 { /** * 设置事务回滚点 * @param args * @throws SQLException */ public static void main(String[] args) throws SQLException { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; Savepoint sp = null; try{ conn = JdbcUtils_Dbcp.getConnection(); conn.setAutoCommit(false); //start transaction String sql1 = "update account set money=money-100 where name='aaa'"; st = conn.prepareStatement(sql1); st.executeUpdate(); //设置一个事务回滚点 sp = conn.setSavepoint(); String sql2 = "update account set money=money+100 where name='bbb'"; st = conn.prepareStatement(sql2); st.executeUpdate(); //当抛异常之后就被catch住了,在catch中记录异常,回滚到回滚到回滚点,最后一定要记住事务提交 int x = 1/0; conn.commit(); //commit }catch (Exception e) { e.printStackTrace(); conn.rollback(sp); //回滚到回滚点 conn.commit(); //事务回滚后,一定要记得提交 }finally{ JdbcUtils_Dbcp.release(conn, st, rs); } } }
Tip:事务的特性(ACID)
原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。 事务最基本最核心的概率
事务前后数据的完整性必须保持一致。(也就是说钱转来转去钱总额都不变)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响。
如果市面上号称支持事务的数据库则必有这四大特性,同样一个数据库支持则四大特性则说明它也支持事务
这个是面试中最容易考的,如果有人问你什么事事务的四大特性,直接回答ACID
-----------------------------------------------------------------------------------------------------------------------------------------------
Tip:事务的隔离级别
这是非常危险的,假设A向B转帐100元,对应sql语句如下所示
1.update account set money=money+100 while name=‘b’;
2.update account set money=money-100 while name=‘a’;
当第1条sql执行完,第2条还没执行(A未提交时),如果此时B查询自己的帐户,就会发现自己多了100元钱,然后就给A发货了。如果A等B发货后在执行回滚rollback,B就会损失100元,而且还损失了货物
例如银行想查询A帐户余额,第一次查询A帐户为200元,此时A向帐户存了100元并提交了,银行接着又进行了一次查询,此时A帐户为300元了。银行两次查询不一致,可能就会很困惑,不知道哪次查询是准的。
Tip:事务隔离性的设置语句
![](http://images0.cnblogs.com/blog/391262/201311/05112050-186e9741e7ad4349bb24297f531f6cd8.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05112139-5999912dac7e4aa489b3655d73781314.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05112456-24b4a4200d5c43d6950e6635c8b0cab3.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05112714-48e6bc5a45864b2daef132d9b312ef0a.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05123252-59fc3400e30a4addb0c3814ea78ceb0d.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05123905-1a2694c7ad3f4615a9b9e30d0e1a4b32.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05124254-be8abe4aeb3f421987f456b02e54dbdb.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05124638-e5e41d44a0974119a9cd5f4c13374582.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05125005-c6d5ecbc50c348dea5fe989f1ac47062.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05130400-c162b4b5f93f4fbf9041fde16fe883ab.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05130852-982134cb045042fc91e2b118c9d146f0.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05131714-967dea3f34f346a896db422f7bc598e3.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05132141-e817c2d302b34aa68d46b17c7578da84.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05132447-1e25966344d248db9ce0351c463effd7.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05132740-94b906291823464d9405448e56613f9d.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05133043-64b931ffe1ec4afd9cdc00202ca75a31.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05135645-cfd7d323bf4741fe93e9bfc336475cb1.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05134117-ec52351ea6e84c84988a7d1b0bc54c0c.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05135804-9a1b7e3ed231470fbec02beb9a31af05.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05134743-270c6d8fe5ed4275aac444c297e337bc.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05135952-78cbf51aa8814225bbe0c46f1d175750.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05140412-ea9be8d457dd44349e42dd57c951eb61.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05140636-940df4c38f1748c8acfca60ee6d1cee0.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05141024-13e2f1d26ab742b795eeaf11cf175d2c.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05141107-07dd261ff00a4ba995b68ffefcdc8d94.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05141504-7a7f86690a5e40f5b9ae836649fa76be.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05141645-9ef6b0111096409cbcc8748616769cc9.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05143901-5c871a7fa4e748dbaa1f71383f21a814.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05144151-e57bb12805d142e0930406fa9f63dc41.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05144405-a017d5ab1c644516a86c5251bbba9eea.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05144507-73a8cd9921a4477e896cd8f659644c70.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05150631-4cc1391bb550441b9345de3826813a35.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05150828-e7f8cc5065af4a3ba050fc162f9b7b64.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05150931-d3c13cf974704428a848fedcc80f6b18.jpg)
package cn.itcast.demo; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Savepoint; import cn.itcast.utils.JdbcUtils_Dbcp; public class Demo3 { /** * 设置程序的事务隔离级别 * 现在做一个账户统计的程序要用最高级别的事务隔离Connection.TRANSACTION_SERIALIZABLE * 通过两次查询账户个数,在两次查询中间间隔10s,然后在这10s期间用一个mysql窗口向数据库 * 插入一个账户可以发现账户个数不便 * @param args * @throws SQLException */ public static void main(String[] args) throws SQLException { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; Savepoint sp = null; try{ conn = JdbcUtils_Dbcp.getConnection(); //将事务隔离级别设置为可以避免脏读,但是不可避免不可重复读和虚读 conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); conn.setAutoCommit(false); String sql = "select count(*) from account"; //第一次查询 st = conn.prepareStatement(sql); rs = st.executeQuery(); if(rs.next()){ System.out.println(rs.getInt(1)); } //中间等待10s Thread.sleep(1000*10); //第二次查询 st = conn.prepareStatement(sql); rs = st.executeQuery(); if(rs.next()){ System.out.println(rs.getInt(1)); } conn.commit(); //提交事务commit }catch (Exception e) { e.printStackTrace(); conn.rollback(sp); conn.commit(); //事务回滚后,一定要记得提交 }finally{ JdbcUtils_Dbcp.release(conn, st, rs); } } }
Tip:使用数据库连接池优化程序性能
![](http://images0.cnblogs.com/blog/391262/201311/05160629-47c3168b45f043d4b8322a1c36c6732f.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05161047-58fff93150d94da99f0a72de3c5cfc32.jpg)
Tip:编写数据库连接池
下面我们来写一个连接池
![](http://images0.cnblogs.com/blog/391262/201311/05161812-1a47ce0225744f7490d58f662c6ba2d9.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05161845-69ca47bfa7494f62850e30f61d8ca97f.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05162401-5fd3833495a64565aca53274761dbc26.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/05162344-eb066d7f7f84457dba227644ddc9a113.jpg)
package cn.itcast.utils; import java.io.InputStream; import java.io.PrintWriter; import java.sql.Array; import java.sql.Blob; import java.sql.CallableStatement; import java.sql.Clob; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.NClob; import java.sql.PreparedStatement; import java.sql.SQLClientInfoException; import java.sql.SQLException; import java.sql.SQLWarning; import java.sql.SQLXML; import java.sql.Savepoint; import java.sql.Statement; import java.sql.Struct; import java.util.ArrayList; import java.util.LinkedList; import java.util.List; import java.util.Map; import java.util.Properties; import javax.sql.DataSource; public class JdbcPool implements DataSource { private static String url; private static String username; private static String password; private static String driver; //当整个应用一启起来,这个连接池就会找数据库要一批连接 //然后用户要访问数据的时候dao层就会调用连接池的getConnection()这个方法 //所以整个应用一启起来,这个连接池就必须要有一个静态的集合保持获取到的一批连接 //这里还要注意一点,由于这个保存连接的集合不存在索引,所以选定list集合,而不闲着map集合 //又由于在操作这个list集合时涉及到大量的增删,所以选定LinkedList,而不选额ArrayList //因为LinkedList是链表结构,ArrayList是数组结构 private static LinkedList<Connection> list = new LinkedList<Connection>(); //由于当应用一启动起来就会找数据库要一批连接,这个只会操作一次,所以放在静态代码块中 static{ try { //通过配置文件读取数据库和驱动 InputStream in = JdbcUtils_Dbcp.class.getClassLoader().getResourceAsStream("db.properties"); Properties prop = new Properties(); prop.load(in); url = prop.getProperty("url"); username = prop.getProperty("username"); password = prop.getProperty("password"); driver = prop.getProperty("driver"); //装载数据库驱动 Class.forName(driver); //向数据库要10个链接 for(int i=0;i<10;i++){ //通过驱动管理器向数据库要连接 Connection conn = DriverManager.getConnection(url, username, password); //要到有一个链接之后就将这个连接加入到list中 list.add(conn); } } catch (Exception e) { throw new ExceptionInInitializerError(e); } } //dao connection conn.close(); /* 1.写一个子类,重写close方法 × 2.用包装设计模式 3.用动态代理进行增强 */ public Connection getConnection() throws SQLException { //当dao层调用getConnectin()这个方法时,需要判断list集合是否为空 if(list.size()>0){ //这里要用removeFirst(),理由是多个连接不能同时使用同一个connection, //所以这里一处第一个connection,同时返回移除的connection Connection conn = list.removeFirst(); /* *这里 不能直接返回Connection,因为在dao层用完这个连接之后就必须关闭 *一关闭就将这个连接返回给数据库了,而并没有返给连接池,如果是这样写则这个 *连接池就没有意义了,所以这里就必须重写Connection的close方法从而保证连接 *的close()方法不会将连接返还给数据库,而是返回给连接池 *这里就引出了java里面一个重要的概念:当一个类的方法不足以满足你的要求时 *你想对这个方法加强,这个加强的方式有三种 *1.写一个这个类的子类并重写这个方法 *2.用包装设计模式去增强 *3.用动态代理去增强 * *我们这里假如用第一种方式写一个子类去继承Connection并从写了close()方法 *但是这种方式却是行不通的,理由这些连接Connection是DriveManager通过驱动获取到的 *此时得到的Connection包含了很多数据库的信息,而你继承Connection的类却不具备 *这些数据,即使这个子类重写了close方法,也不可能连接到数据库,所以想通过子类加强 *重写加强close的方法是行不通的,从这个问题上我们又引出了java中的一个概念,当要通过 *子类去加强父类的方法时,必须要求父类不具有封装的数据,如果父类有封装的数据则不能使用 *子类加强父类的方法 * *既然第一种继承加强方式不行,那我们选用包装设计模式 *包装设计模式的步骤: *1.写一个类实现与被增强对象相同接口(Connection) *2.定义一个变量,记住被增强对象 *3.定义一个构造函数,接受被增强的对象 *4.重写需要增强的方法 *5.对于不需要增强的方法直接调用被增强对象的方法 * *虽然包装类可以解决这个问题,但是却是很麻烦,理由是实现了接口Connection之后 *虽然只加强了close方法,但是对于不需要加强的方法则直接调用被增强对象的方法 *但是不需要增强的方法实在是太多了,所以最终极的方法非动态代理不可,但是动态 *代理需要在后面讲解 */ MyConnection myconn = new MyConnection(conn,list); return myconn; }else{ //当没有链接时,就给调用者抛一个异常,提示系统忙 throw new RuntimeException("系统忙,等一会再来!!"); } } public Connection getConnection(String username, String password) throws SQLException { // TODO Auto-generated method stub return null; } public PrintWriter getLogWriter() throws SQLException { // TODO Auto-generated method stub return null; } public int getLoginTimeout() throws SQLException { // TODO Auto-generated method stub return 0; } public void setLogWriter(PrintWriter arg0) throws SQLException { // TODO Auto-generated method stub } public void setLoginTimeout(int arg0) throws SQLException { // TODO Auto-generated method stub } @Override public boolean isWrapperFor(Class<?> iface) throws SQLException { // TODO Auto-generated method stub return false; } @Override public <T> T unwrap(Class<T> iface) throws SQLException { // TODO Auto-generated method stub return null; } } /* *既然第一种继承加强方式不行,那我们选用包装设计模式 *包装设计模式的步骤: *1.写一个类实现与被增强对象相同接口(Connection) *2.定义一个变量,记住被增强对象 *3.定义一个构造函数,接受被增强的对象 *4.重写需要增强的方法 *5.对于不需要增强的方法直接调用被增强的方法 */ //1.写一个类实现与被增强对象相同接口(Connection) class MyConnection implements Connection{ //2.定义一个变量,记住被增强对象 private Connection connection; private LinkedList list; //3.定义一个构造函数,接受被增强的对象 //这里由于要将Connection存入到Linkedlist中,所以这里也将Linkedlist传递过来了 public MyConnection(Connection connection,LinkedList list){ this.connection = connection; this.list = list; } //4.重写需要增强的方法 public void close() throws SQLException { //当调用close方法时就将这个connection存入到集合中 this.list.add(this.connection); } //下面其它的方法都是调用要增强对象的方法 public void clearWarnings() throws SQLException { this.connection.clearWarnings(); } public void commit() throws SQLException { this.connection.commit(); } public Statement createStatement() throws SQLException { return this.connection.createStatement(); } public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { return this.connection.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability); } public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException { // TODO Auto-generated method stub return null; } public boolean getAutoCommit() throws SQLException { // TODO Auto-generated method stub return false; } public String getCatalog() throws SQLException { // TODO Auto-generated method stub return null; } public int getHoldability() throws SQLException { // TODO Auto-generated method stub return 0; } public DatabaseMetaData getMetaData() throws SQLException { // TODO Auto-generated method stub return null; } public int getTransactionIsolation() throws SQLException { // TODO Auto-generated method stub return 0; } public Map<String, Class<?>> getTypeMap() throws SQLException { // TODO Auto-generated method stub return null; } public SQLWarning getWarnings() throws SQLException { // TODO Auto-generated method stub return null; } public boolean isClosed() throws SQLException { // TODO Auto-generated method stub return false; } public boolean isReadOnly() throws SQLException { // TODO Auto-generated method stub return false; } public String nativeSQL(String sql) throws SQLException { // TODO Auto-generated method stub return null; } public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { // TODO Auto-generated method stub return null; } public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException { // TODO Auto-generated method stub return null; } public CallableStatement prepareCall(String sql) throws SQLException { // TODO Auto-generated method stub return null; } public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException { // TODO Auto-generated method stub return null; } public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException { // TODO Auto-generated method stub return null; } public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException { // TODO Auto-generated method stub return null; } public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException { // TODO Auto-generated method stub return null; } public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException { // TODO Auto-generated method stub return null; } public PreparedStatement prepareStatement(String sql) throws SQLException { // TODO Auto-generated method stub return null; } public void releaseSavepoint(Savepoint savepoint) throws SQLException { // TODO Auto-generated method stub } public void rollback() throws SQLException { // TODO Auto-generated method stub } public void rollback(Savepoint savepoint) throws SQLException { // TODO Auto-generated method stub } public void setAutoCommit(boolean autoCommit) throws SQLException { // TODO Auto-generated method stub } public void setCatalog(String catalog) throws SQLException { // TODO Auto-generated method stub } public void setHoldability(int holdability) throws SQLException { // TODO Auto-generated method stub } public void setReadOnly(boolean readOnly) throws SQLException { // TODO Auto-generated method stub } public Savepoint setSavepoint() throws SQLException { // TODO Auto-generated method stub return null; } public Savepoint setSavepoint(String name) throws SQLException { // TODO Auto-generated method stub return null; } public void setTransactionIsolation(int level) throws SQLException { // TODO Auto-generated method stub } public void setTypeMap(Map<String, Class<?>> map) throws SQLException { // TODO Auto-generated method stub } @Override public Array createArrayOf(String typeName, Object[] elements) throws SQLException { // TODO Auto-generated method stub return null; } @Override public Blob createBlob() throws SQLException { // TODO Auto-generated method stub return null; } @Override public Clob createClob() throws SQLException { // TODO Auto-generated method stub return null; } @Override public NClob createNClob() throws SQLException { // TODO Auto-generated method stub return null; } @Override public SQLXML createSQLXML() throws SQLException { // TODO Auto-generated method stub return null; } @Override public Struct createStruct(String typeName, Object[] attributes) throws SQLException { // TODO Auto-generated method stub return null; } @Override public Properties getClientInfo() throws SQLException { // TODO Auto-generated method stub return null; } @Override public String getClientInfo(String name) throws SQLException { // TODO Auto-generated method stub return null; } @Override public boolean isValid(int timeout) throws SQLException { // TODO Auto-generated method stub return false; } @Override public void setClientInfo(Properties properties) throws SQLClientInfoException { // TODO Auto-generated method stub } @Override public void setClientInfo(String name, String value) throws SQLClientInfoException { // TODO Auto-generated method stub } @Override public boolean isWrapperFor(Class<?> iface) throws SQLException { // TODO Auto-generated method stub return false; } @Override public <T> T unwrap(Class<T> iface) throws SQLException { // TODO Auto-generated method stub return null; } }
然后一个dao层来调用连接池
/day16/src/cn/itcast/demo/Dao.java
package cn.itcast.demo; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Savepoint; import org.junit.Test; import cn.itcast.utils.JdbcPool; import cn.itcast.utils.JdbcUtils_Dbcp; public class Dao{ /**使用dbcp * @param args * @throws SQLException */ @Test public void select1() throws SQLException { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; Savepoint sp = null; try{
//通过连接池获取链接 JdbcPool pool = new JdbcPool(); conn=pool.getConnection(); String sql = "select count(*) from account"; st = conn.prepareStatement(sql); rs = st.executeQuery(); if(rs.next()){ System.out.println(rs.getInt(1)); } }catch (Exception e) { e.printStackTrace(); }finally{
//这里关闭链接其实会将链接返回给连接池 JdbcUtils_Dbcp.release(conn, st, rs); } } }
/day16/src/cn/itcast/utils/JdbcUtils.java
package cn.itcast.utils; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class JdbcUtils { private static String url; private static String username; private static String password; private static String driver; static{ try { InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties prop = new Properties(); prop.load(in); url = prop.getProperty("url"); username = prop.getProperty("username"); password = prop.getProperty("password"); driver = prop.getProperty("driver"); Class.forName(driver); } catch (Exception e) { throw new ExceptionInInitializerError(e); } } //加载驱动,获取链接 public static Connection getConnection() throws SQLException{ Connection conn = DriverManager.getConnection(url,username,password); return conn; } //释放资源 public static void release(Connection conn,Statement st,ResultSet rs){ try{ if(rs!=null) rs.close(); }catch (Exception e) { e.printStackTrace(); rs=null; } try{ if(st!=null) st.close(); }catch (Exception e) { e.printStackTrace(); st=null; } try{ //这里关闭资源会掉用MyConnection的close方法, //这个close方法会将Connection添加到连接池中的LinkedList if(conn!=null) conn.close(); }catch (Exception e) { e.printStackTrace(); conn=null; } } }
Tip:数据库连接池核心代码
//注意:这个用动态代理构建连接池后面学完javaweb后会详细讲解,这里只是一个引子
Tip:开源数据库连接池
由于连接池实在是太重要了,于是一个开源组织提供了一些开源的连接池
Tomcat内置的数据库连接池其实是DBCP
在C:\Tomcat6.0\lib
Tip:DBCP数据源
在使用的DBCP,也要通过读取配置文件来连接数据库
下面就是配置文件
/day16/src/mysqldbcpconfig.properties
#连接设置 driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/day16 username=root password=root #<!-- 初始化连接找数据库要多少个连接 --> initialSize=10 #最大连接数量为50个 maxActive=50 #<!-- 最大空闲连接为20个 --> maxIdle=20 #<!-- 最小空闲连接为5个 --> minIdle=5 #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒,当连接池中没有连接时等待60秒,等待空闲连接回来 --> maxWait=60000 #JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:[属性名=property;] #注意:"user" 与 "password" 两个属性会被明确地传递,因此这里不需要包含他们。
#这里设置连接的属性,设置了是否用Uicode编码,设置字符集为UTF8 connectionProperties=useUnicode=true;characterEncoding=UTF8 #指定由连接池所创建的连接的自动提交(auto-commit)状态。 defaultAutoCommit=true #driver default 指定由连接池所创建的连接的只读(read-only)状态。 #如果没有设置该值,则“setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix) defaultReadOnly= #driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。 #可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE defaultTransactionIsolation=READ_UNCOMMITTED
倒入了dbcp包和配置文件后我们开始在代码中使用DBCP连接池
不过在这之前我们需要更改JDBC工具类。
/day16/src/cn/itcast/utils/JdbcUtils.java
package cn.itcast.utils; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; public class JdbcUtils { //这里的DataSource要设置成静态的 private static DataSource ds; static{ try { InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("mysqldbcpconfig.properties"); Properties prop = new Properties(); prop.load(in); //这里通过BadicDataSourceFactory工厂类创建DBCP //然后将Properties作为参数传入,表示要连接那个数据库 ds = BasicDataSourceFactory.createDataSource(prop); } catch (Exception e) { throw new ExceptionInInitializerError(e); } } //加载驱动,获取链接 public static Connection getConnection() throws SQLException{ //以前是向数据库要连接,现在是向DataSource要连接 Connection conn = ds.getConnection(); return conn; } //释放资源 public static void release(Connection conn,Statement st,ResultSet rs){ try{ if(rs!=null) rs.close(); }catch (Exception e) { e.printStackTrace(); rs=null; } try{ if(st!=null) st.close(); }catch (Exception e) { e.printStackTrace(); st=null; } try{ //这里关闭资源会掉用MyConnection的close方法, //这个close方法会将Connection添加到连接池中的LinkedList if(conn!=null) conn.close(); }catch (Exception e) { e.printStackTrace(); conn=null; } } }
dao层调用通过DBCP连接池
package cn.itcast.demo; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Savepoint; import org.junit.Test; import cn.itcast.utils.JdbcPool; import cn.itcast.utils.JdbcUtils_Dbcp; public class Dao{ /**使用dbcp * @param args * @throws SQLException */ @Test public void select() throws SQLException { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; Savepoint sp = null; try{ conn = JdbcUtils.getConnection(); //Myconection String sql = "select count(*) from account"; st = conn.prepareStatement(sql); rs = st.executeQuery(); if(rs.next()){ System.out.println(rs.getInt(1)); } }catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtils_Dbcp.release(conn, st, rs); } } @Test public void select2() throws SQLException { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; Savepoint sp = null; try{ conn = JdbcUtils_c3p0.getConnection(); //Myconection String sql = "select count(*) from account"; st = conn.prepareStatement(sql); rs = st.executeQuery(); if(rs.next()){ System.out.println(rs.getInt(1)); } }catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtils_Dbcp.release(conn, st, rs); } } }
Tip: C3P0 数据源
要想使用C3P0就必须先导入它的包
当数据库是mysql时则只需导入如下两个jar包就可以了
当数据库是Oracle时则需要导入上面三个
JdbcUtils_c3p0.java
package cn.itcast.utils; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; import com.mchange.v2.c3p0.ComboPooledDataSource; public class JdbcUtils_c3p0 { private static ComboPooledDataSource ds; static{ try { ds = new ComboPooledDataSource(); ds.setDriverClass("com.mysq1.jdbc.Driver"); ds.setJdbcUrl("jdbc:mysql://localhost:3306/day16"); ds.setUser("root"); ds.setPassword("root"); ds.setMaxPoolSize(20); ds.setMinPoolSize(10); ds.setInitialPoolSize(10); } catch (Exception e) { throw new ExceptionInInitializerError(e); } } //加载驱动,获取链接 public static Connection getConnection() throws SQLException{ Connection conn = ds.getConnection(); //myconnection return conn; } //释放资源 public static void release(Connection conn,Statement st,ResultSet rs){ try{ if(rs!=null) rs.close(); }catch (Exception e) { e.printStackTrace(); rs=null; } try{ if(st!=null) st.close(); }catch (Exception e) { e.printStackTrace(); st=null; } try{ //MyConnection if(conn!=null) conn.close(); }catch (Exception e) { e.printStackTrace(); conn=null; } } }
/day16/src/cn/itcast/demo/Dao.java
package cn.itcast.demo; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Savepoint; import org.junit.Test; import cn.itcast.utils.JdbcPool; import cn.itcast.utils.JdbcUtils_Dbcp; public class Dao{ /**使用dbcp * @param args * @throws SQLException */ @Test public void select2() throws SQLException { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; Savepoint sp = null; try{ conn = JdbcUtils_c3p0.getConnection(); //Myconection String sql = "select count(*) from account"; st = conn.prepareStatement(sql); rs = st.executeQuery(); if(rs.next()){ System.out.println(rs.getInt(1)); } }catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtils_Dbcp.release(conn, st, rs); } } }
由于/day16/src/cn/itcast/utils/JdbcUtils_c3p0.java中直接输入的配置文件,这样写死了,并不好,所以这里我们想要c3p0通过读取配置文件去连接数据库
此时我们就需要去查看c3p0文档
在c3p0的src帮助文档中Configutation有如下一段话
说的是可以通过简单的javaproperties文件,也可以通过XML配置文件,还可以通过系统配置文件来设置c3p0的配置文件,并且配置文件一般以 (c3p0.properties or c3p0-config.xml)
命名
于是我们在工程src上建立一个c3p0-config.xml
然后我们再向下查看c3p0的配置文件,我们可以找到一个配置文件的例子,我们将这个例子粘贴到x3p0-config.xml
然后在MyEclipse中按快捷键Ctrl+Shift+f,自动格式化
在c3p0的配置文件中可以为多个数据库做配置,也有默认配置,实在是很方便
/day16/src/c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <default-config> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/day16</property> <property name="user">root</property> <property name="password">root</property> <property name="acquireIncrement">10</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">5</property> <property name="maxPoolSize">20</property> </default-config><!-- This app is massive! --> <named-config name="mysql"> <!-- 驱动 --> <property name="driverClass">com.mysql.jdbc.Driver</property> <!-- 数据库地址--> <property name="jdbcUrl">jdbc:mysql://localhost:3306/day16</property> <!-- 用户名--> <property name="user">root</property> <!-- 密码--> <property name="password">root</property> <!-- 这个表示增长量,当连接池中没有连接时,会想数据库要10个连接 --> <property name="acquireIncrement">10</property> <!-- 默认初始化连接个数为10个 --> <property name="initialPoolSize">10</property> <!-- 最小连接数为5个 --> <property name="minPoolSize">5</property> <!-- 最大连接数位20个 --> <property name="maxPoolSize">20</property> <!-- intergalactoApp adopts a different approach to configuring statement caching --> </named-config> <named-config name="oracle"> <property name="acquireIncrement">50</property> <property name="initialPoolSize">100</property> <property name="minPoolSize">50</property> <property name="maxPoolSize">1000</property> <!-- intergalactoApp adopts a different approach to configuring statement caching --> <!-- 这个maxStatements表示缓存多少条sql语句 --> <property name="maxStatements">0</property> <property name="maxStatementsPerConnection">5</property> <!-- he's important, but there's only one of him --> <user-overrides user="master-of-the-universe"> <property name="acquireIncrement">1</property> <property name="initialPoolSize">1</property> <property name="minPoolSize">1</property> <property name="maxPoolSize">5</property> <property name="maxStatementsPerConnection">50</property> </user-overrides> </named-config> </c3p0-config>
![](http://images0.cnblogs.com/blog/391262/201311/06135301-f28ea2fb069c4ede930d5a4d3a8b7c6c.jpg)
package cn.itcast.demo; import java.io.InputStream; import java.lang.reflect.Field; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; import com.mchange.v2.c3p0.ComboPooledDataSource; public class JdbcUtils_c3p0 { private static ComboPooledDataSource ds; static{ try{ //由于 c3p0采用了缺省xml的名称,所以这里可以不指定,这个ds会自动搜索c3p0-config.xml这个文件 ds = new ComboPooledDataSource(); //这个表示选用c3p0-config.xml中默认的配置 //ds = new ComboPooledDataSource("mysql"); //这个表示选用c3p0-config.xml中名称为mysql的配置 //ds = new ComboPooledDataSource("oracle"); //这个表示选用c3p0-config.xml中名为oracle的配置 }catch (Exception e) { throw new ExceptionInInitializerError(e); } } //加载驱动,获取链接 public static Connection getConnection() throws SQLException{ Connection conn = ds.getConnection(); //myconnection return conn; } //释放资源 public static void release(Connection conn,Statement st,ResultSet rs){ try{ if(rs!=null) rs.close(); }catch (Exception e) { e.printStackTrace(); rs=null; } try{ if(st!=null) st.close(); }catch (Exception e) { e.printStackTrace(); st=null; } try{ //MyConnection if(conn!=null) conn.close(); }catch (Exception e) { e.printStackTrace(); conn=null; } } //增删改 sql public static void update(String sql,Object params[]) throws SQLException{ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils_c3p0.getConnection(); st = conn.prepareStatement(sql); for(int i=0;i<params.length;i++){ st.setObject(i+1, params[i]); } st.executeUpdate(); }finally{ JdbcUtils_c3p0.release(conn, st, rs); } } public static Object query(String sql,Object params[],ResultSetHandler rsh) throws SQLException{ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils_c3p0.getConnection(); st = conn.prepareStatement(sql); for(int i=0;i<params.length;i++){ st.setObject(i+1, params[i]); } rs = st.executeQuery(); //调用用户的方法去处理 return rsh.handler(rs); }finally{ JdbcUtils_c3p0.release(conn, st, rs); } } } interface ResultSetHandler{ public Object handler(ResultSet rs); } class BeanHandler implements ResultSetHandler{ private Class clazz; public BeanHandler(Class clazz){ this.clazz = clazz; } public Object handler(ResultSet rs) { try{ //1.准备好于保存数据的bean Object bean = clazz.newInstance(); if(!rs.next()){ return null; } //2.通过元技术获取rs中封装的数据的信息 ResultSetMetaData rsm = rs.getMetaData(); int columnCount = rsm.getColumnCount(); for(int i=0;i<columnCount;i++){ String columnName = rsm.getColumnName(i+1); //获取处理的列的列名 name Object data = rs.getObject(columnName); //获取到列的数据 aa Field f = bean.getClass().getDeclaredField(columnName); //得到bean上面列名对应的属性 f.setAccessible(true); f.set(bean, data); } return bean; }catch (Exception e) { throw new RuntimeException(e); } } }
package cn.itcast.demo; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Savepoint; import org.junit.Test; import cn.itcast.utils.JdbcPool; import cn.itcast.utils.JdbcUtils_Dbcp; public class Dao { /** * 使用dbcp * * @param args * @throws SQLException */ @Test public void select2() throws SQLException { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; Savepoint sp = null; try { conn = JdbcUtils_c3p0.getConnection(); // Myconection String sql = "select count(*) from account"; st = conn.prepareStatement(sql); rs = st.executeQuery(); if (rs.next()) { System.out.println(rs.getInt(1)); } } catch (Exception e) { e.printStackTrace(); } finally { JdbcUtils_Dbcp.release(conn, st, rs); } } }
Tip:配置Tomcat数据源
下面我们开看Tomcat内置的连接池(注意有些人也将连接池叫做数据源(DataSource))
![](http://images0.cnblogs.com/blog/391262/201311/06141359-9a7d0e027c6a4f1dbc48f535b3094349.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/06141922-6dd64d55014b41db91c1038350613e28.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/06142556-7884313f516f4e5d8e718e4cf35bc9c3.jpg)
<Context> <Resource name="jdbc/EmployeeDB" auth="Container" type="javax.sql.DataSource" username="root" password="root" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/day16" maxActive="8" maxIdle="4"/> </Context>
由于开始我们说过Tomcat的连接池是存入到JNDI中的,所以我们在Tomcat的帮助文档中这个地方JNDI Resources获取到如何写Context.xml
我没进入到JNDI Resources中,向下拉
这里就是教你如何配置Context.xml
![](http://images0.cnblogs.com/blog/391262/201311/06150625-168b37a1b2364902922a5e62f6d17df5.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/06151400-2c0b81159a734657844f4630c60cbd81.jpg)
package cn.itcast.web.servlet; import java.io.IOException; import java.sql.Connection; import javax.naming.Context; import javax.naming.InitialContext; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.sql.DataSource; public class ServletDemo1 extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //只需要从tomcat中的jndi容器检索出连接池 try{ Context initCtx = new InitialContext(); //初始化jndi,JNDI最核心的类就是Context Context envCtx = (Context) initCtx.lookup("java:comp/env"); //得到tomcat用于保存连接池的jndi容器 DataSource ds = (DataSource)envCtx.lookup("jdbc/EmployeeDB"); //从容器中检索连接池 Connection conn = ds.getConnection(); System.out.println(conn); }catch (Exception e) { e.printStackTrace(); } } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } }
接着我们开启Tomcat服务器,运行SrevletDemo1,发现抛异常了
![](http://images0.cnblogs.com/blog/391262/201311/06153630-3ea94f35b42b4f7889ea086aa15697a4.jpg)
![](http://images0.cnblogs.com/blog/391262/201311/06153836-393f63e1b68644a8ab57b2dae237dbfc.jpg)
Tip:JNDI技术简介
编写自己的JDBC框架
这节课我们学习jdbc优化
Tip:元数据- DataBaseMetaData
package cn.itcast.demo; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import cn.itcast.utils.JdbcUtils_Dbcp; public class Demo4 { /**获取数据库的元信息 * @param args * @throws SQLException */ public static void main(String[] args) throws SQLException { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils_Dbcp.getConnection(); DatabaseMetaData metadata = conn.getMetaData(); System.out.println(metadata.getDatabaseProductName()); System.out.println(metadata.getURL()); System.out.println(metadata.getDriverName()); }finally{ JdbcUtils_Dbcp.release(conn, st, rs); } } }
这个技术学好之后有助于将来学习harbenet等技术
Tip:元数据- ParameterMetaData
在开发中有时候我们也要获取参数的原信息,由于PrepareStatement中包含了sql语句,这些sql语句有有?号代表参数,于是我们可以通过getParameterMetaDate()
获取到这些参数的原信息,这个技术还是用在框架上,如果你用这个技术不用再框架上则一点用都没有
/day16/src/cn/itcast/demo/Demo5.java
package cn.itcast.demo; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ParameterMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import cn.itcast.utils.JdbcUtils_Dbcp; public class Demo5 { /**获取sql语句参数的元信息 * @param args * @throws SQLException */ public static void main(String[] args) throws SQLException { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils_Dbcp.getConnection(); String sql = "insert into account(name,money) values(?,?)"; st = conn.prepareStatement(sql); //这条信息就包含了sql语句中?参数的元信息 ParameterMetaData pmd = st.getParameterMetaData(); //获取到sql语句中参数的个数 System.out.println(pmd.getParameterCount()); //这个是获取sql语句中参数的类型,但是 //这个要报异常java.sql.SQLException //理由是ParameterMetaDate是接口,这个接口由mysql去实现 //这里其实是调用Mysql实现类的getParameterType()方法, //由于mysql驱动没有支持这个方法,所以要报异常 System.out.println(pmd.getParameterType(1)); //throw }finally{ JdbcUtils_Dbcp.release(conn, st, rs); } } }
报的异常
Tip:元数据- ResultSetMetaData
/day16/src/cn/itcast/demo/Demo6.java
package cn.itcast.demo; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import cn.itcast.utils.JdbcUtils_Dbcp; public class Demo6 { /**获取结果集的元信息 * @param args * @throws SQLException */ public static void main(String[] args) throws SQLException { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils_Dbcp.getConnection(); String sql = "select * from account"; rs = conn.prepareStatement(sql).executeQuery(); //获取结果集元信息 ResultSetMetaData rsmd = rs.getMetaData(); //获取account这个表有多少列 System.out.println(rsmd.getColumnCount()); //获取每列的名称 System.out.println(rsmd.getColumnName(1)); System.out.println(rsmd.getColumnName(2)); System.out.println(rsmd.getColumnName(3));
rs.getObject("id"); //最后通过结果集的getObject(获取到的列名)就可以获取值了 }finally{ JdbcUtils_Dbcp.release(conn, st, rs); } } }
有了以上获取元数据的基础,我们就可以开始做jdbc框架,简化jdbc开发
Tip:使用元数据简化JDBC代码
由于JDBC操作数据时,增删改都有重复的代码(唯独查的代码不同),所以我们可以在JdbcUtil工具类中将这些代码重复代码统一起来,以达到优化的目的
增删改的优化,主要是通过将增删改的sql语句和参数传递过来,实现了统一代码,然后只是在增删改的的方法中调用这个工具方法就能简化代码
这里对查找的优化就有些麻烦了,主要是通过元件技术获取到结果集中的数据,然后再通过反射技术存入到用户指定的bean对象中,这里对查的优化是在是
非常精辟的代码
/day16/src/cn/itcast/demo/JdbcUtils_c3p0.java
package cn.itcast.demo; import java.io.InputStream; import java.lang.reflect.Field; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; import com.mchange.v2.c3p0.ComboPooledDataSource; public class JdbcUtils_c3p0 { private static ComboPooledDataSource ds; static{ try{ ds = new ComboPooledDataSource(); }catch (Exception e) { throw new ExceptionInInitializerError(e); } } //加载驱动,获取链接 public static Connection getConnection() throws SQLException{ Connection conn = ds.getConnection(); //myconnection return conn; } //释放资源 public static void release(Connection conn,Statement st,ResultSet rs){ try{ if(rs!=null) rs.close(); }catch (Exception e) { e.printStackTrace(); rs=null; } try{ if(st!=null) st.close(); }catch (Exception e) { e.printStackTrace(); st=null; } try{ //MyConnection if(conn!=null) conn.close(); }catch (Exception e) { e.printStackTrace(); conn=null; } } //这里是对增删改的优化,主要是通过将增删改的sql语句和参数传递过来,实现了统一代码, //然后只是在增删改的的方法中调用这个工具方法就能简化代码 public static void update(String sql,Object params[]) throws SQLException{ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils_c3p0.getConnection(); st = conn.prepareStatement(sql); for(int i=0;i<params.length;i++){ //这里将传递过来的参数付给了sql语句中的? st.setObject(i+1, params[i]); } st.executeUpdate(); }finally{ JdbcUtils_c3p0.release(conn, st, rs); } } //此处是对查的优化 public static Object query(String sql,Object params[],ResultSetHandler rsh) throws SQLException{ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = JdbcUtils_c3p0.getConnection(); st = conn.prepareStatement(sql); for(int i=0;i<params.length;i++){ st.setObject(i+1, params[i]); } rs = st.executeQuery(); //当查询得到了结果集后到这里不知道如何对结果集处理,也不知道该向那个bean插入结果集中的数据 //但是用户知道该向那个bean对象插入结果集中的数据,所以这里就调用用户的方法去处理结果集 //为了调用用户的方法去处理,这里就必须定义用户的行为,所以这里就对外暴露一个接口,由用户去 //实现接口处理结果集,本来做到这里就完了,但是这样的框架对于用户来说除了要出入sql语句和参数外 //还要自己实现一个处理结果集的方法,在下面我们做了常见的结果集处理方式 return rsh.handler(rs); }finally{ JdbcUtils_c3p0.release(conn, st, rs); } } } interface ResultSetHandler{ public Object handler(ResultSet rs); } //此处是常见的结果集处理方式 //再写结果集处理器时由于作者不知道用户会将这个结果集中的数据存入到那个bean中 //但是这里可以通过用户在new结果集处理器时将bean类名传递过来,然后 //通过元技术取出结果集中的数据,然后在通过反射技术存入到用户指定的bean中 class BeanHandler implements ResultSetHandler{ private Class clazz; public BeanHandler(Class clazz){ this.clazz = clazz; } public Object handler(ResultSet rs) { try{ //1.准备好于保存数据的bean Object bean = clazz.newInstance(); //加入这个结果集没有数据则返回null if(!rs.next()){ return null; } //2.通过元技术获取rs中封装的数据的信息 ResultSetMetaData rsm = rs.getMetaData(); //查看数据库表有几列 int columnCount = rsm.getColumnCount(); for(int i=0;i<columnCount;i++){ String columnName = rsm.getColumnName(i+1); //获取处理的列的列名 name Object data = rs.getObject(columnName); //获取到列的数据 aa Field f = bean.getClass().getDeclaredField(columnName); //得到bean上面列名对应的属性 f.setAccessible(true); f.set(bean, data); } return bean; }catch (Exception e) { throw new RuntimeException(e); } } }
/day16/src/cn/itcast/demo/Demo7.java
package cn.itcast.demo; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Date; import java.util.List; import org.junit.Test; import cn.itcast.domain.User; public class Demo7 { public static void main(String[] args) throws SQLException { Demo7 d = new Demo7(); d.find(); } @Test public void insert() throws SQLException{ String sql = "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)"; Object params[] = {1,"hhh","123","hh@sina.com",new Date()}; JdbcUtils_c3p0.update(sql, params); } @Test public void update() throws SQLException{ String sql = "update users set name=?,email=? where id=?"; Object params[] = {"xxx","xx@sina.com",1}; JdbcUtils_c3p0.update(sql, params); } @Test public void delete() throws SQLException{ String sql = "delete from users where id=?"; Object params[] = {1}; JdbcUtils_c3p0.update(sql, params); } @Test public User find() throws SQLException{ String sql = "select * from users where id=?"; Object params[] = {1}; //这里只传递了sql语句,sql语句中的参数,以及结果集处理对象 User user = (User) JdbcUtils_c3p0.query(sql, params, new BeanHandler(User.class)); return user; } public List getAll(){ Connection conn = null; Statement st = null; ResultSet rs = null; try{ conn = JdbcUtils_c3p0.getConnection(); st = conn.createStatement(); String sql = "select * from users"; rs = st.executeQuery(sql); List list = new ArrayList(); while(rs.next()){ //false User user = new User(); user.setBirthday(rs.getDate("birthday")); user.setEmail(rs.getString("email")); user.setId(rs.getInt("id")); user.setName(rs.getString("name")); user.setPassword(rs.getString("password")); list.add(user); } return list; }catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtils_c3p0.release(conn, st, rs); } return null; } }
/day16/src/cn/itcast/demo/JdbcUtils_c3p0.java中的接口ResultSetHandler和结果集处理器BeanHandler都应该独立出来,方便外部调用
public interface ResultSetHandler{ public Object handler(ResultSet rs); }
public class BeanHandler implements ResultSetHandler{ private Class clazz; public BeanHandler(Class clazz){ this.clazz = clazz; } public Object handler(ResultSet rs) { try{ //1.准备好于保存数据的bean Object bean = clazz.newInstance(); //加入这个结果集没有数据则返回null if(!rs.next()){ return null; } //2.通过元技术获取rs中封装的数据的信息 ResultSetMetaData rsm = rs.getMetaData(); //查看数据库表有几列 int columnCount = rsm.getColumnCount(); for(int i=0;i<columnCount;i++){ String columnName = rsm.getColumnName(i+1); //获取处理的列的列名 name Object data = rs.getObject(columnName); //获取到列的数据 aa Field f = bean.getClass().getDeclaredField(columnName); //得到bean上面列名对应的属性 f.setAccessible(true); f.set(bean, data); } return bean; }catch (Exception e) { throw new RuntimeException(e); } }
下面将优化day14_customer项目中的JDBC,这个JDBC优化中涉及到了三种结果集处理器
一是将结果集中的数据存入到用户指定的bean中,
二是将结果集中的数据先存入到用户指定的bean中,然后再存入到list中
三十将结果集中的数据直接返回
/day16_customer/src/cn/itcast/dao/impl/CustomerDaoImpl.java
package cn.itcast.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import cn.itcast.dao.CustomerDao; import cn.itcast.domain.Customer; import cn.itcast.domain.QueryResult; import cn.itcast.exception.DaoException; import cn.itcast.utils.BeanHandler; import cn.itcast.utils.BeanListHandler; import cn.itcast.utils.JdbcUtils; import cn.itcast.utils.JdbcUtils_c3p0; import cn.itcast.utils.NumberHandler; public class CustomerDaoImpl implements CustomerDao { public void add(Customer c){ try{ String sql = "insert into customer(id,name,gender,birthday,cellphone,email,preference,type,description) values(?,?,?,?,?,?,?,?,?)"; Object params[] = {c.getId(),c.getName(),c.getGender(),c.getBirthday(),c.getCellphone(),c.getEmail(),c.getPreference(),c.getType(),c.getDescription()}; JdbcUtils_c3p0.update(sql, params); }catch (Exception e) { throw new DaoException(e); } } public void update(Customer c){ try{ String sql = "update customer set name=?,gender=?,birthday=?,cellphone=?,email=?,preference=?,type=?,description=? where id=?"; Object params[] = {c.getName(),c.getGender(),c.getBirthday(),c.getCellphone(),c.getEmail(),c.getPreference(),c.getType(),c.getDescription(),c.getId()}; JdbcUtils_c3p0.update(sql, params); }catch (Exception e) { throw new DaoException(e); } } public void delete(String id){ try{ String sql = "delete from customer where id=?"; Object params[] = {id}; JdbcUtils_c3p0.update(sql, params); }catch (Exception e) { throw new DaoException(e); } } public Customer find(String id){ try{ String sql = "select * from customer where id=?"; Object params[] = {id}; return (Customer) JdbcUtils_c3p0.query(sql, params, new BeanHandler(Customer.class)); }catch (Exception e) { throw new DaoException(e); } } public List<Customer> getAll(){ try{ String sql = "select * from customer"; //参数为空 Object params[] = {}; //由于返回的Object,所以要强转成list return (List<Customer>) JdbcUtils_c3p0.query(sql, params, new BeanListHandler(Customer.class)); }catch (Exception e) { throw new DaoException(e); } } public QueryResult pageQuery(int startindex,int pagesize){ try{ QueryResult qr = new QueryResult(); String sql = "select * from customer limit ?,?"; Object params[] = {startindex,pagesize}; //由于这里 List list = (List) JdbcUtils_c3p0.query(sql, params, new BeanListHandler(Customer.class)); qr.setList(list); sql = "select count(*) from customer"; // 88 params = new Object[0]; //注意select count(*) from customer这句sql语句返回的是一个bigLong类型的数据, //所以这里必须用long类型,不能用int类型 long totalrecord = (Long)JdbcUtils_c3p0.query(sql, params, new NumberHandler()); //这里在讲long类型强转成int类型 qr.setTotalrecord((int)totalrecord); return qr; }catch (Exception e) { throw new DaoException(e); } } }
/day16_customer/src/cn/itcast/utils/ResultSetHandler.java接口
package cn.itcast.utils; import java.sql.ResultSet; public interface ResultSetHandler { public Object handler(ResultSet rs); }
/day16_customer/src/cn/itcast/utils/BeanHandler.java(这个是将结果集中的数据存入到用户指定的bean中)
package cn.itcast.utils; import java.lang.reflect.Field; import java.sql.ResultSet; import java.sql.ResultSetMetaData; public class BeanHandler implements ResultSetHandler{ private Class clazz; public BeanHandler(Class clazz){ this.clazz = clazz; } public Object handler(ResultSet rs) { try{ //1.准备好于保存数据的bean Object bean = clazz.newInstance(); if(!rs.next()){ return null; } //2.通过元技术获取rs中封装的数据的信息 ResultSetMetaData rsm = rs.getMetaData(); int columnCount = rsm.getColumnCount(); for(int i=0;i<columnCount;i++){ String columnName = rsm.getColumnName(i+1); //获取处理的列的列名 name Object data = rs.getObject(columnName); //获取到列的数据 aa Field f = bean.getClass().getDeclaredField(columnName); //得到bean上面列名对应的属性 f.setAccessible(true); f.set(bean, data); } return bean; }catch (Exception e) { throw new RuntimeException(e); } } }
/day16_customer/src/cn/itcast/utils/BeanListHandler.java(这个是将结果集中的数据先存入到用户指定的bean中然后在存入到list集合中)
package cn.itcast.utils; import java.util.List; import java.lang.reflect.Field; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.util.ArrayList; public class BeanListHandler implements ResultSetHandler { private Class clazz; //通过构造方法获取到用户传递过来的bean类 public BeanListHandler(Class clazz){ this.clazz = clazz; } public Object handler(ResultSet rs) { List list = new ArrayList(); try{ //有while(rs.next())循环就可以保证结果集是一条一条的保存到bean中 while(rs.next()){ //当结果集中有数据时,整出一个bean对象 Object bean = clazz.newInstance(); //通过元技术获取到结果集中的数据 ResultSetMetaData rsmd = rs.getMetaData(); //获取到数据库表有几列 int columnCount = rsmd.getColumnCount(); for(int i=0;i<columnCount;i++){ //获取到列明 String columnName = rsmd.getColumnName(i+1); //根据列名获取到值 Object value = rs.getObject(columnName); //通过反射技术将将值存入到bean中 Field f = bean.getClass().getDeclaredField(columnName); f.setAccessible(true); f.set(bean, value); } //将bean存入到list集合中去 list.add(bean); } //最后返回list集合 return list; }catch (Exception e) { throw new RuntimeException(e); } } }
/day16_customer/src/cn/itcast/utils/NumberHandler.java(这个是直接返回结果集中的数据)
package cn.itcast.utils; import java.sql.ResultSet; public class NumberHandler implements ResultSetHandler { public Object handler(ResultSet rs) { try{ //如果结果集有数据则返回,如果没有就返回0 if(rs.next()){ return rs.getObject(1); } return 0; }catch (Exception e) { throw new RuntimeException(e); } } }
对于这个JDBC的优化的使用条件必须是bean中的名称和数据库表中的名称对应,不然用不了这个优化框架,这个优化框架也就是明天要学的DButils框架
Tip:O-R Mapping简介