Java数据库连接--JDBC调用存储过程，事务管理和高级应用

相关链接：Jdbc调用存储过程

一、JDBC常用的API深入详解及存储过程的调用

1、存储过程的介绍

　　我们常用的操作数据库语言SQL语句在执行的时候要先进行编译，然后执行，而存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过制定存储过程的名字并给出参数(如果该存储过程带有参数) 来执行它。存储过程是数据库中 的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

　　一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或者平台上执行相同函数，或者封装特定功能时，存储过程非常有用。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。

存储过程有以下优点：

　　(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

　(2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库
专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

　　(3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。

因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL

语句在每次运行时都要进行编译和优化，速度相对要慢一些。

　　(4).存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，

那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。

　　(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免

了非授权用户对数据的访问，保证了数据的安全。

简单说，好处主要：

　　1、由于数据执行动作时，先编译后执行，然而存储过程是一个编译过的代码块，所以执行效率比T-SQL高。

　　2、一个存储过程在程序中交互可以替代大队的T-SQL语句，所以也能降低网络的通信量，提高通信效率。

　　3、通过存储过程能够使没有权限的用户在控制之下间接存取数据库，保证数据安全。

2、Jdbc调用无参数存储过程

存储过程代码：
    CREATE PROCEDURE imooc_db.sp_select_nofilter()
    BEGIN
            select * from imooc_goddess;
    END;

调用代码：
    Connection conn=DBUtil.getConnection();
    CallableStatement c=conn.prepareCall("call sp_select_nofilter()");
    c.execute();
    ResultSet rs=c.getResultSet();

在数据库中新建存储过程：

 　　　

注意：创建的存储过程名称不要加"()",不然在调用存储过程会出错。

代码示例：

 package produceDao;

 import java.sql.CallableStatement;
 import java.sql.Connection;
 import java.sql.ResultSet;
 import java.sql.SQLException;

 import sql.Dao.DBUtil;

 public class ProduceDAO {
     public void select_nofilter() throws SQLException{
         //1、获得连接
         Connection connection=DBUtil.getConnection();
         //2、获得CallableStatement
         CallableStatement cs=connection.prepareCall("call sp_select_nofilter()");
         //3、执行存储过程
         cs.execute();
         //4、处理返回结果：结果集，出参
         ResultSet rs=cs.getResultSet();
         //遍历结果集
         while (rs.next()){
             System.out.println(rs.getString("user_name")+":"+rs.getString("email"));
         }
     }
 }

ProduceDao.java

 package producetest;

 import java.sql.SQLException;
 import produceDao.ProduceDAO;

 public class JdbcProduceTest {
     public static void main(String []args) throws SQLException{
         ProduceDAO dao=new ProduceDAO();
         dao.select_nofilter();
     }
 }

JdbcProduceTest.java

运行结果：

3、Jdbc调用含输入参数存储过程

新建一个过程：

如果输入空白字符，会全部显示，结果如下：

如果传入一个'蔡'，则结果如下：

 //只有输入in，没有输出的存储过程
     public List<Goddess> select_filter(String sp_name) throws SQLException{
         List<Goddess> result=new ArrayList<>();
         //1、获得连接
         Connection connection=DBUtil.getConnection();
         //2、获得CallableStatement
         CallableStatement cs=connection.prepareCall("call sp_select_filter(?)");
         cs.setString(1, sp_name);
         //3、执行存储过程
         cs.execute();
         //4、处理返回结果：结果集，出参
         ResultSet rs=cs.getResultSet();
         Goddess goddess=null;
         //遍历结果集
         while (rs.next()){
             goddess=new Goddess();
             goddess.setId(rs.getInt("id"));
             goddess.setUserName(rs.getString("user_name"));
             goddess.setAge(rs.getInt("age"));
             result.add(goddess);
         }
         return result;
     }

测试：

 public class JdbcProduceTest {
     public static void main(String []args) throws SQLException{
         ProduceDAO dao=new ProduceDAO();
         //dao.select_nofilter();
         String sp_name="白";
         List<Goddess> res=null;
         res=dao.select_filter(sp_name);
         for(int i=0;i<res.size();i++){
             System.out.println(res.get(i).getId()+":"+res.get(i).getUserName()+":"+res.get(i).getAge());
         }
     }
 }

运行结果：

4、Jdbc调用含输出参数存储过程

建立一个过程

调用存储过程：显示出一共多少记录，结果如下：

含有输出参数的存储过程

 //Jdbc调用含有输出参数的的存储过程
     public Integer select_count()throws SQLException{
         Integer count=0;
         Connection connection=DBUtil.getConnection();
         CallableStatement cs=connection.prepareCall("call sp_select_count(?)");
         cs.registerOutParameter(1, Types.INTEGER);//注册输出参数，第二个参数时告诉jdbc，输出参数的类型。
         cs.execute();
         //处理返回的结果：这个结果不是结果集，而是出参
         count=cs.getInt(1);
         return count;
     }

测试：

 public class JdbcProduceTest {
     public static void main(String []args) throws SQLException{
         ProduceDAO dao=new ProduceDAO();
 //        //dao.select_nofilter();
 //        String sp_name="蔡";
 //        List<Goddess> res=null;ss
 //        res=dao.select_filter(sp_name);
 //        for(int i=0;i<res.size();i++){
 //            System.out.println(res.get(i).getId()+":"+res.get(i).getUserName()+":"+res.get(i).getAge());
 //        }
         String sp_name="蔡";
         List<Goddess> res=null;
         Integer count=0;

         //带输入参数的存储过程
         res=select_filter(sp_name);
         showResult(res);

         count=select_count();
         System.out.println("一共有"+count+"个女神！");
     }

     public static List<Goddess> select_filter(String sp_name)throws SQLException{
         ProduceDAO dao=new ProduceDAO();
         return dao.select_filter(sp_name);
     }
     public static Integer select_count()throws SQLException{
         ProduceDAO dao=new ProduceDAO();
         return dao.select_count();
     }
     public static void showResult(List<Goddess> result){
         for(int i=0;i<result.size();i++){
             System.out.println(result.get(i).getId()+":"+result.get(i).getUserName()+":"+result.get(i).getAge());
         }
     }

 }

运行结果：

二、JDBC的事务管理

事务的概念：

　　事务是作为单个逻辑工作单元执行的一系列操作。这些操作作为一个整体一起向系统提交，要么都执行，要么都不执行。

事务的特点：

　　1、原子性：事务是一个完整的操作。不能对它进行再分割，是最小的一个单元。

　　2、一致性：当事务完成时，数据必须处于一致状态。（例如银行转账，张三要给李四转100元。则第一步张三的账户需要减去100元，第二步李四的账户需要加上100元。这是两个操作，但是应该在一个事务里面。如果没有在一个事务里面，张三减去100，李四并没有增加100，那这样数据就出现了不一致性，张三的钱跑哪去了呢 ）

　　3、隔离性：对数据进行修改的所有并发事务是彼此隔离的。（比如业务A:张三减100，李四加100；同时业务B也是张三减100，李四加100进行操作。业务A和B是同时的，这时候就出现了并发，这个时候是怎么变化的呢？当业务员A进行操作的时候，业务员B就要等待……就是同一时间对数据库的操作要保持一个事务的锁定。也就是说我在做的时候，别人是不能做的。我做完了之后别人才能做，彼此之间是隔离的）

　　4、永久性：事务完成之后，它对数据库的修改是永久保存的。

1、Jdbc实现事务管理

(1)、我们通过提交commit()或者回退rollback()来管理事务的操作。

　　当事务完成之后，我们通过commit将事务提交到数据库之中，然后数据库会变成持久化的，我们的数据就会永久保存了。

　　如果采用rollback的话，事务回滚，比如说我们插入的数据、更新的数据都会变成原来没有更新、没有插入的样子。

(2)、事务操作默认是自动提交的

　　当我们调用完insert语句，不用调用commit语句，自己就自动提交了。

(3)、可以调用setAutoCommit(false)来禁止自动提交。

2、通过代码实现事务的管理

　　首先我们要注意，在JDBC中，事务操作默认是自动提交。也就是说，一条对数据库的更新表达式代表一项事务操作。操作成功后，系统将自动调用commit()来提交，否则将调用rollback()来回退。

　　其次，在JDBC中，可以通过调用setAutoCommit(false)来禁止自动提交。之后就可以把多个数据库操作的表达式作为一个事务，在操作完成 后调用commit()来进行整体提交。倘若其中一个表达式操作失败，都不会执行到commit()，并且将产生响应的异常。此时就可以在异常捕获时调用 rollback()进行回退。这样做可以保持多次更新操作后，相关数据的一致性。

 private static final String URL="jdbc:mysql://localhost:3306/mydb";
 2 private static final String NAME="root";
 private static final String PASSWORD="mysql123";
 try{
         conn=DriverManager.getConnection(URL,NAME,PASSWORD);
         conn.setAutoCommit(false);//禁止自动提交，设置回退
         stmt=conn.createStatement();
         //数据库更新操作1
         stmt.executeUpdate("update firsttable Set Name='testTransaction' where ID=1");
         //数据库更新操作2
         stmt.executeUpdate("insert into firsttable ID=12,Name='testTransaction2'");
         //事务提交
         conn.commit();
     }catch(Exception e){
         e.printStackTrace();
         try {
             //操作不成功则回退
             conn.rollback();
         } catch (Exception ex) {
             // TODO: handle exception
             ex.printStackTrace();
         }
     }

三、数据库连接池(dbcp、c3p0)

连接池产生的背景：

　　数据库连接是一种重要资源。大部分很重要的数据都存在数据库里，那么在产生连接池之前，我们连接数据库的方式：直连。（获取连接->使用->关闭连接）程序小的话可以采用这种方式，但是如果程序很大，比如大型网站，它可能每分钟或者每秒变化量在100万次，就是说同时访问数据库有100万个用户，这时候如果我们不用连接池的话，我们就需要创建100万个连接这样的话就会对数据库造成很大的压力，如果数据库承受不了的话就崩溃了，服务器也崩溃了，网站就瘫痪了。

即：

①数据库连接是一种重要资源；

②频繁的连接数据库会增加数据库的压力；

③为解决以上问题出现连接池技术。

（池子里保持一定数量的连接，当使用时就从池子中拿一个连接出来，当使用完连接后就把它释放到池子里。当你同时访问数据库人很多的时候，这个时候连接不够用，就需要等待，减少数据库的压力）

常用的开源数据库连接池：

1. dbcp
2. c3p0

1、dbcp的使用步骤

　　1、首先，导入相关jar包：

　　　　　　commons-dbcp-1.4.jar

　　　　　　commons-pool-1.5.6.jar

　　　　　　commons-logging-1.1.1.jar

　　2、根目录下设置配置文件(/src下)：dbcp.properties

 driverClassName=com.mysql.jdbc.Driver
 url=jdbc:mysql://localhost:3306/mydb
 username=root
 password=mysql123

 maxIdle=30
 maxIdle=10
 maxWait=1000

 removeAbandoned=true
 removeAbandonedTimeout=180

　　3、配置并测试dbcp连接

案例一：dbcptest.java

 package db;

 import java.sql.Connection;
 import java.sql.SQLException;

 import org.apache.commons.dbcp.BasicDataSource;

 public class testjdbc {
     public static void main(String []args) throws SQLException{
         BasicDataSource dataSource=null;
         dataSource=new BasicDataSource();
         dataSource.setDriverClassName("com.mysql.jdbc.Driver");
         dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/mydb");
         dataSource.setUsername("root");
         dataSource.setPassword("mysql123");
         Connection connection=dataSource.getConnection();
         System.out.println(connection);
     }
 }

运行结果：

　　

案例二：

DBCPUtil.java

 package db;

 import java.sql.Connection;
 import java.sql.SQLException;
 import java.util.Properties;

 import javax.sql.DataSource;

 import org.apache.commons.dbcp.BasicDataSource;
 import org.apache.commons.dbcp.BasicDataSourceFactory;

 public class DBCPUtil {
     private static DataSource DS;
     private static final String configFile="dbcp.properties";

     /**
      * 从数据源获得一个连接
      * @throws SQLException
      * */

     public Connection getConn() throws SQLException,Exception{
         Connection conn=null;
         if(DS!=null){
             conn=DS.getConnection();//从数据源里拿到连接
             conn.setAutoCommit(false);//关闭连接的自动提交
             return conn;
         }
         return conn;
     }

     /**
      * 默认的构造函数
      * @throws Exception
      * **/
     public DBCPUtil() throws Exception{
         initDbcp();
     }

     private static void initDbcp() throws Exception{
         Properties pops=new Properties();
         pops.load(DBCPUtil.class.getClassLoader().getResourceAsStream(configFile));

         DS=BasicDataSourceFactory.createDataSource(pops);//通过BasicDataSourceFactory提供的工厂类，拿到DataSource数据源
     }

     //构造函数，初始化了DS，指定数据库
     public DBCPUtil(String url){
         initDS(url);
     }

     //构造函数，初始化了DS，指定了所有参数
     public DBCPUtil(String connectURL,String username,String password,String driverClass,int initialSize,
             int maxIdle,int minIdle,int maxWait){
         initDS(connectURL,username,password,driverClass,initialSize,maxIdle,minIdle,maxWait);
     }

     public static void initDS(String url){
         initDS(url,"root","mysql123","com.mysql.jdbc.Driver",10,20,5,1000);
     }
     public static void initDS(String connectURL,String userName,String password,String driverClass,int initialSize,int maxIdle,int minIdle,int maxWait){
         BasicDataSource ds=new BasicDataSource();//new一个数据源
         ds.setDriverClassName(driverClass);
         ds.setUsername(userName);
         ds.setPassword(password);
         ds.setUrl(connectURL);
         ds.setInitialSize(initialSize);
         ds.setMaxIdle(maxIdle);
         ds.setMaxWait(maxWait);
         ds.setMinIdle(minIdle);
         DS=ds;
     }
 }

DBCPUtil.java

GoddessDao.java

 //查询单个女神
     public Goddess get(Integer id) throws SQLException{
         Goddess goddess=null;
         Connection connection=DBUtil.getConnection();
         String sql=""+
                 "select * from imooc_goddess "+
                 "where id=?";
         PreparedStatement psmt=connection.prepareStatement(sql);
         psmt.setInt(1, id);
         //psmt.execute();execute()是执行更改数据库操作(包括新增、修改、删除)；executeQuery()是执行查询操作
         ResultSet rsResultSet=psmt.executeQuery();
         while(rsResultSet.next()){
             goddess=new Goddess();
             goddess.setId(rsResultSet.getInt("id"));
             goddess.setUserName(rsResultSet.getString("user_name"));
             goddess.setSex(rsResultSet.getString("sex"));
             goddess.setAge(rsResultSet.getInt("age"));
             goddess.setBirthday(rsResultSet.getDate("birthday"));
             goddess.setEmail(rsResultSet.getString("email"));
             goddess.setMobile(rsResultSet.getString("mobile"));
             goddess.setCreateUser(rsResultSet.getString("create_user"));
             goddess.setCreateDate(rsResultSet.getDate("create_date"));
             goddess.setUpdateUser(rsResultSet.getString("update_user"));
             goddess.setUpdateDate(rsResultSet.getDate("update_date"));
             goddess.setIsDel(rsResultSet.getInt("isdel"));
         }
         return goddess;
     }
 //查询单个女神(根据id去查询，DBCP连接池的方式)
     public Goddess getByDbcp(Integer id) throws Exception{
         // TODO Auto-generated method stub
         DBCPUtil db=new DBCPUtil();//
         Goddess goddess=null;
         Connection connection=db.getConn();//拿到数据库的连接，通过DBCP
         String sql=""+
                 "select * from imooc_goddess "+
                 "where id=?";
         PreparedStatement psmt=connection.prepareStatement(sql);
         psmt.setInt(1, id);
         //psmt.execute();execute()是执行更改数据库操作(包括新增、修改、删除)；executeQuery()是执行查询操作
         ResultSet rsResultSet=psmt.executeQuery();
         while(rsResultSet.next()){
             goddess=new Goddess();
             goddess.setId(rsResultSet.getInt("id"));
             goddess.setUserName(rsResultSet.getString("user_name"));
             goddess.setSex(rsResultSet.getString("sex"));
             goddess.setAge(rsResultSet.getInt("age"));
             goddess.setBirthday(rsResultSet.getDate("birthday"));
             goddess.setEmail(rsResultSet.getString("email"));
             goddess.setMobile(rsResultSet.getString("mobile"));
             goddess.setCreateUser(rsResultSet.getString("create_user"));
             goddess.setCreateDate(rsResultSet.getDate("create_date"));
             goddess.setUpdateUser(rsResultSet.getString("update_user"));
             goddess.setUpdateDate(rsResultSet.getDate("update_date"));
             goddess.setIsDel(rsResultSet.getInt("isdel"));
         }
         return goddess;
     }

GoddessDao.java

测试：DbcpTest.java

 package db;

 import java.sql.SQLException;

 import java.util.Date;

 import sql.Dao.GoddessDao;
 import sql.model.Goddess;
 public class DbcpTest {
     public static void main(String []args) throws Exception{
         //1、通过普通的方式操作数据库
         Date a=new Date();
         get();
         Date b=new Date();
         System.out.println(b.getTime()-a.getTime());

         //2、通过DBCP连接池的方式操作数据库
         Date c=new Date();
         getByDbcp();
         Date d=new Date();
         System.out.println(d.getTime()-c.getTime());

         //通过运行发现，第一种方式要比第二种方式花费时间要多

     }

     private static void get() throws SQLException {
         // TODO Auto-generated method stub
         GoddessDao dao=new GoddessDao();
         Goddess goddess=dao.get(2);
         System.out.println("普通连接："+goddess.toString());
     }
     private static void getByDbcp() throws Exception {
         // TODO Auto-generated method stub
         GoddessDao dao=new GoddessDao();
         Goddess goddess=dao.getByDbcp(2);
         System.out.println("dbcp连接："+goddess.toString());
     }
 }

DbcpTest.java

运行结果：

　　

2、c3p0使用步骤

　　c3p0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等。

默认情况下（即没有配置连接池的情况下），Hibernate会采用内建的连接池。但这个连接池性能不佳，因此官方也只是建议仅在开发环境下使用。Hibernate支持第三方的连接池，官方推荐的连接池是C3P0,Proxool。

　　1、导入相关jar包

　　　　c3p0-0.9.2.jar

　　　　mchange-commons-java-0.2.3.4.jar

　　2、配置根目录配置文件

 <?xml version="1.0" encoding="UTF-8"?>
 <c3p0-config>
     <named-config name="helloC3p0">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/mydb</property>
        <property name="user">root</property>
        <property name="password">mysql123</property>

        <property name="acquireIncrement">50</property>
        <!-- 若数据库中连接数不足时，一次向数据库服务器申请多少个链接。 -->

        <property name="initialPoolSize">5</property>
        <!-- 初始化数据库连接时，连接的个数 -->

        <property name="minPoolSize">50</property>
        <!-- 数据库连接池中的最小的数据库连接数 -->
        <property name="maxPoolSize">1000</property>
        <!-- 数据库连接池中的最大的数据库连接数 -->

        <property name="maxStatements">20</property>
        <!-- c3p0数据库连接池可以使用的Statement对象的个数 -->
        <property name="maxStatementsPerConnection">5</property>
        <!-- c3p0数据库连接池每个连接可以使用的Statement对象的个数 -->
     </named-config>
 </c3p0-config>

　　3、测试

实例一：C3P0Test.java

 package c3p0;

 import javax.sql.DataSource;

 import com.mchange.v2.c3p0.ComboPooledDataSource;

 public class C3poTest {
     private static final String helloC3p0="c3p0-config.xml";
     public static void main(String []args) throws Exception{
 //        ComboPooledDataSource cpds=new ComboPooledDataSource();
 //        cpds.setDriverClass("com.mysql.jdbc.Driver");
 //        cpds.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/mydb");
 //        cpds.setUser("root");
 //        cpds.setPassword("mysql123");
         DataSource dataSource=new ComboPooledDataSource("helloC3p0");
         System.out.println(dataSource.getConnection());
     }
 }

运行结果：

　　

3、连接池总结

　　

四、JDBC的替代产品(Hibernate、MyBatis)

　　上面介绍的都是手工的连接数据库，写SQL语句。这部分的替代产品会代替我们的这些工作。

替代工具：

　　　　Commons-dbutils

　　　　Hibernate;

　　　　Mybatis;

1、Commons-dbutils

　　字面的意思可以理解为：通用的数据库工具类。

　　Apache组织提供的一个开源JDBC工具类库，对传统操作数据库的类进行二次封装，可以把结果集转化为List。

特点：

 杜绝了资源泄露。修正了JDBC代码并不难，但是这通常导致连接泄露并且难以跟踪到。
 大段的持久化数据到数据库代码彻底精简，剩下的代码清晰地表达了编码的意图。
 不需要手工从ResultSet里set值到JavaBean中，每一行数据都将会以一个Bean示例的形式出现。

核心接口：

　　1、DbUtils：提供如关闭、装载JDBC驱动程序等常规工作的工具类；

　　2、QueryRunner:该类简化了Sql查询，它常与ResultSetHandler组合在一起。

　　3、ResultSetHandler：执行处理一个java.sql.ResultSet，将数据转变并处理为任何一种形式，这样有益于其应用而且使用起来容易。

具体的参考  ：Apache的DBUtils框架学习

2、Hibernate

具体参考： 新手上路第一个Hibernate例子。 以及  Hibernate框架学习

3、Mybatis

具体参考：mybatis实战教程

转载：JAVA数据库连接--JDBC调用存储过程，事务管理和高级应用