JDBC详解

在了解Spring的DAO模块时需要有一定的数据库基础，Java语言与数据库连接使用的是JDBC，所以有必要学习下JDBC的内容。

1.JDBC介绍

JDBC (Java DB Connection)---Java数据库连接。JDBC是一种可用于执行SQL语句的JAVA API（ApplicationProgramming Interface应用程序设计接口）。它由一些Java语言编写的类和界面组成。JDBC为数据库应用开发人员和数据库前台工具开发人员提供了一种标准的应用程序设计接口，使开发人员可以用纯JAVA语言编写完整的数据库应用程序。JDBC代表JAVA数据库连接。它是一个软件层，允许开发者在JAVA中编写客户端/服务器应用。
通过使用JDBC，开发人员可以很方便地将SQL语句传送给几乎任何一种数据库。也就是说，开发人员可以不必写一个程序访问Sybase，写另一个程序访问Oracle，再写一个程序访问Microsoft的SQLServer。用JDBC写的程序能够自动地将SQL语句传送给相应的数据库管理系统（DBMS）。不但如此，使用Java编写的应用程序可以在任何支持Java的平台上运行，不必在不同的平台上编写不同的应用。Java和JDBC的结合可以让开发人员在开发数据库应用时真正实现“WriteOnce，RunEverywhere！”。

2.操作步骤

JDBC可以连接不同的数据库，不同的数据库也可以被不同的工具连接，但在连接时基本都是固定的几个步骤。

1）、驱动引入
JDBC是一种可用于执行SQL语句的JAVA API（ApplicationProgramming Interface应用程序设计接口）。它是对外开放的接口，数据库提供商实现了这些接口，这些接口的组合就是驱动。数据库有好多种，例如MySQL、Oracle等，于是乎需要注册不同的驱动来操作对应的数据库,注册驱动也得要有驱动才是，所以首先是将驱动引入项目。
2）、注册驱动
引入驱动之后应用程序也不知道是用的什么数据库，只是把驱动下载了下来放到项目中，所以得注册一下才知道是谁，注册之后会返回对应的驱动管理对象，就和入职一样，你到公司了但不报到那也不知道来了没来，报到了才会有针对个人的流程。
3）、创建连接
数据库和应用程序是分隔开来的，数据库可能存放在远程，那怎么和数据库搭上呢，这就需要连接。
4）、执行操作
连接上之后要干嘛呢，不能一直连着不干事情啊，这也是资源的一种浪费，所以连接之后执行数据库的操作，增删改查等。
5）、返回结果
增删改查操作结束之后，总要有个结果，不然怎么知道成功与否，查询的话会返回查询的数据，增加、删除、修改会返回影响的行数。
6）、释放资源
把结果也返回了,但不能老连着数据库啊，这样也占用资源，创建的对象也没释放，还占空间，所以用完了就把它关掉。

3.Statement的使用

Statement 是 Java 执行数据库操作的一个重要接口，用于在已经建立数据库连接的基础上，向数据库发送要执行的SQL语句。Statement对象，用于执行静态 SQL 语句并返回它所生成结果的对象。
在默认情况下，同一时间每个 Statement 对象在只能打开一个 ResultSet 对象。因此，如果读取一个 ResultSet 对象与读取另一个交叉，则这两个对象必须是由不同的 Statement 对象生成的。如果存在某个语句的打开的当前 ResultSet 对象，则 Statement 接口中的所有执行方法都会隐式关闭它。
在使用Statement之前先进行数据准备，这里在本地mysql中创建了一个数据库daodemodb和一张表t_user，并在表中增加了几条数据用来测试。

/* 数据库创建 */
CREATE DATABASE `daodemodb` /*!40100 DEFAULT CHARACTER SET utf8 */;
/*创建测试表*/
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `money` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*数据准备*/
insert into t_user(name,age,money) 
values 
('张三','24',666.66),
('李四','25',888.88),
('王二','26',999.99),
('小明','27',555.55),
('小赵','28',333.33)

按照上面的操作步骤，需要引入驱动，这里使用pom.xml引入mysql的jdbc驱动。

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.12</version>
    </dependency>

然后就是在代码中依次注册驱动、创建连接、执行操作、返回结果、释放资源步骤，下面代码演示的就是这个过程，从t_user表中查询数据并打印到日志中。

package com.demo.jdbc;

import java.sql.*;
public class StatementDemo {

    public static void main(String[] args) throws SQLException {
        // TODO Auto-generated method stub
        Connection conn=null;
        Statement stmt=null;
        ResultSet rs=null;
        try{
            //注册驱动
            DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
            //通过注册的驱动获得连接对象Connection
            conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/daodemodb?useUnicode=true&characterEncoding=UTF-8"
                    + "&serverTimezone=UTC&useSSL=false","root","123456");
            //通过Statement对象执行操作 返回结果ResultSet
            stmt=conn.createStatement();
            //返回结果 
            rs=stmt.executeQuery("select * from t_user");
            while(rs.next()){
                System.out.println("姓名:"+rs.getString("name")+"  年龄:"+rs.getInt("age") +"  余额:"+rs.getDouble("money"));
                }
            }
        catch(SQLException e){
            System.out.println(e.getMessage());
            e.printStackTrace();
            }
        finally{
            //释放资源
            if(conn!=null){ conn.close(); }
            if(stmt!=null){ stmt.close(); }   
            if(rs!=null){ rs.close(); }
            }
    }

}

输出结果:

姓名:张三  年龄:24  余额:666.66
姓名:李四  年龄:25  余额:888.88
姓名:王二  年龄:26  余额:999.99
姓名:小明  年龄:27  余额:555.55
姓名:小赵  年龄:28  余额:333.33

4.使用PreparedStatement返回自增主键

实际上有三种 Statement 对象，它们都作为在给定连接上执行 SQL 语句的包容器：Statement、PreparedStatement（它从 Statement 继承而来）和 CallableStatement（它从 PreparedStatement 继承而来）。它们都专用于发送特定类型的 SQL 语句： Statement 对象用于执行不带参数的简单 SQL 语句；PreparedStatement 对象用于执行带或不带 IN 参数的预编译 SQL 语句；CallableStatement 对象用于执行对数据库已存在的存储过程的调用。Statement 接口提供了执行语句和获取结果的基本方法。PreparedStatement 接口添加了处理 IN 参数的方法；而 CallableStatement 添加了处理 OUT 参数的方法。
这里向t_user表中插入一条数据，并返回自增主键id的值。在准备sql时使用？来做参数的占位符，在实例化PreparedStatement 对象之后对sql进行传参，这样也能防止注入式攻击。

package com.demo.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class PreparedStatementDemo {

    public static void main(String[] args) throws SQLException {
        // TODO Auto-generated method stub
        Connection conn=null;
        PreparedStatement prestmt=null;
        ResultSet rs=null;
        try{
            //注册驱动
            DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
            //通过注册的驱动获得连接对象Connection
            conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/daodemodb?useUnicode=true&characterEncoding=UTF-8"
                    + "&serverTimezone=UTC&useSSL=false","root","123456");
            //PreparedStatement对象 
            String sql="insert into t_user (name,age,money) values(?,?,?)";
            prestmt=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            prestmt.setString(1, "小李");
            prestmt.setInt(2, 25);
            prestmt.setDouble(3,222.22);
            //返回结果
            int result=prestmt.executeUpdate();
            if(result>0)
            {
                System.out.println("新增成功");
                rs=prestmt.getGeneratedKeys();
                while(rs.next())
                {
                    System.out.println("生成的主键ID为:"+rs.getInt(1));
                }
            } 
            }
        catch(SQLException e){
            System.out.println(e.getMessage());
            e.printStackTrace();
            }
        finally{
            //释放资源
            if(conn!=null){ conn.close(); }
            if(prestmt!=null){ prestmt.close(); }   
            if(rs!=null){ rs.close(); }
            }
    }

}

输出结果:
新增成功
生成的主键ID为:6

5.使用CallableStatement调用存储过程

在使用数据库的过程中，可能会调用存储过程，可以使用CallableStatement来调用存储过程。调用存储函数 1.{?= call <procedure-name>[(<arg1>,<arg2>, ...)]} 。调用存储过程 2.{call <procedure-name>[(<arg1>,<arg2>, ...)]}。通过CallableStatement对象的registerOutParameter() 方法注册Out参数。通过CallableStatement对象的setXxx()方法设定IN或In out 参数，若想将参数设为null，可以使用setNUll()。如果所调用的是带返回参数的存储过程还需要通过CallableStatement对象的getXxx()获取输出参数。
在数据库中创建了存储过程p_selectUserByAge，根据用户年龄查找用户，存储过程一个传入参数age，一个传出参数count，参数count存放根据年龄查找的用户个数。

CREATE  PROCEDURE `p_selectUserByAge`(age int, out count int)
BEGIN
  set count=(select count(1) from t_user t where t.age =age);
  select * from  t_user t where t.age =age;
END

在下面的代码中先使用Connection的prepareCall方法来实例化CallableStatement，再使用CallableStatement对象的registerOutParameter方法设置传入参数，最后执行存储过程返回结果。

package com.demo.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

public class CallableStatementDemo {

    public static void main(String[] args) throws SQLException {
        // TODO Auto-generated method stub
        Connection conn=null;
        CallableStatement callstmt=null;
        ResultSet rs=null;
        try{
            //注册驱动
            DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
            //通过注册的驱动获得连接对象Connection
            conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/daodemodb?useUnicode=true&characterEncoding=UTF-8"
                    + "&serverTimezone=UTC&useSSL=false","root","123456");
            //CallableStatement对象 
            callstmt=conn.prepareCall("{call p_selectUserByAge(?,?)}");
            callstmt.setInt(1,25);
            //设置传入参数
            callstmt.registerOutParameter(2, Types.INTEGER);
            rs=callstmt.executeQuery();
            while(rs.next())
            {
                System.out.println("姓名:"+rs.getString("name")+"  年龄:"+rs.getInt("age") +"  出生日期:"+rs.getDouble("money"));
            }
            System.out.println("存储过程返回值:"+callstmt.getInt(2));
            }
        catch(SQLException e){
            System.out.println(e.getMessage());
            e.printStackTrace();
            }
        finally{
            //释放资源
            if(conn!=null){ conn.close(); }
            if(callstmt!=null){ callstmt.close(); }   
            if(rs!=null){ rs.close(); }
            }
    }
    
}

输出结果:
姓名:李四  年龄:25  出生日期:888.88
姓名:小李  年龄:25  出生日期:222.22
存储过程返回值:2

6.批处理

在实际开发中往往会批量执行sql，Statement和PreparedStatement 都支持批量执行sql语句。但这些sql必须是Insert、update、delete这种执行后返回一个int类数表示影响的行数。 是 Java 执行数据库操作的一个重要接口，用于在已经建立数据库连接的基础上，向数据库发送要执行的SQL语句。Statement对象，用于执行静态 SQL 语句并返回它所生成结果的对象。Statement和PreparedStatement 都是通过addBatch()方法添加一条sql语句，通过executeBatch()方法批量执行sql语句，返回一个int类型的数组，表示各sql的返回值，这样就减少了注入驱动、创建连接这些步骤，提升了效率。首先看下Statement批处理的例子:

package com.demo.jdbc;
import java.sql.*;

public class StatementSQLBatch {

    public static void main(String[] args) throws SQLException {
        // TODO Auto-generated method stub
        Connection conn=null;
        
        ResultSet rs=null;
        Statement stmt=null;
        try{
            //注册驱动
            DriverManager.registerDriver(new com.mysql.jdbc.Driver());
            //通过注册的驱动获得连接对象Connection
            conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/daodemodb?useUnicode=true&characterEncoding=UTF-8"
                + "&serverTimezone=UTC&useSSL=false","root","123456");
            
            stmt=conn.createStatement();
            for(int i=0;i<2;i++)
            {
                String sql="insert into t_user (name,age,money) values('StatementTest"+i+"',"+25+i+",222.22)";
                stmt.addBatch(sql);
            }
            //批处理
            int [] result=stmt.executeBatch();
            System.out.println("影响的行数分别为:");
            for(int i=0;i<result.length;i++)
            {
                System.out.print(result[i]+"  ");
            }
        }
        catch(SQLException e)
        {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        finally
        {
            //释放资源
            if(conn!=null)
            {
                conn.close();
            }
            if(stmt!=null)
            {
                stmt.close();
            }
            if(rs!=null)
            {
                rs.close();
            }
        }
    }
    
}

由于Statement无法传递参数，必须是完整的sql语句，所以先将sql拼接之后通过addBatch(sql)方法加入到批处理中，然后通过executeBatch方法执行批处理返回影响行数的数组。

输出结果:

影响的行数分别为:
1  1 
PreparedStatement即可以是完整的sql，也可以用带参数的不完整的sql。我们看下使用PreparedStatement进行批处理的例子。

package com.demo.jdbc;

import java.sql.*;

public class PreparedStatementSQLBatch {
    
    public static void main( String[] args ) throws SQLException
    {
        Connection conn=null;
        
        ResultSet rs=null;
        PreparedStatement prestmt=null;
        try{
            //注册驱动
            DriverManager.registerDriver(new com.mysql.jdbc.Driver());
            //通过注册的驱动获得连接对象Connection
            conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/daodemodb?useUnicode=true&characterEncoding=UTF-8"
                + "&serverTimezone=UTC&useSSL=false","root","123456");
            String sql="insert into t_user (name,age,money) values(?,?,?)";
            prestmt=conn.prepareStatement(sql);
            for(int i=0;i<2;i++)
            {
                prestmt.setString(1, "PreparedStatementTest"+i);
                prestmt.setInt(2, 25+i);
                prestmt.setDouble(3,222.22);
                prestmt.addBatch();
            }
            //批处理
            int [] result=prestmt.executeBatch();
            System.out.println("影响的行数分别为:");
            for(int i=0;i<result.length;i++)
            {
                System.out.print(result[i]+"  ");
            }
        }
        catch(SQLException e)
        {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        finally
        {
            //释放资源
            if(conn!=null)
            {
                conn.close();
            }
            if(prestmt!=null)
            {
                prestmt.close();
            }
            if(rs!=null)
            {
                rs.close();
            }
        }
    }
    
}

这里使用占位符?来初始化sql，然后通过不带参数的addBatch加入批处理中，最后还是通过executeBatch执行批处理操作。
执行结果:
影响的行数分别为:
1  1
上面演示了Statement、PreparedStatement批处理的使用，这里还要说明一下，批量执行sql需要数据库的支持，有些数据库可能不支持。批量处理将多条sql语句提交给数据库一块执行，效率高一些，但如果数据比较多，比如几万条sql，就需要分批次执行，例如200条执行一次，如果为了增加一致性，可以在批量处理的基础上增加上事务。

7.事务处理

关系型数据库一般都支持事务。事务有四大特性:原子性、一致性、隔离性、持久性。
原子性：原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。例如转账，A账户转给B账户，包含两个操作，将A账户的钱减去然后将B账户的加上对应的钱数，不可能A账户减了B账户没加上，也不可能A账户没减就给B账户加上了。要么两个操作都成功要么都失败。
一致性：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。假设账户A和账户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。这个就涉及到隔离级别的问题了。
隔离性：隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
持久性：持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
本例中使用t_user表的两个用户来模拟转账操作。目前李四账户有888.88元，张三账户有666.66元。这里用户李四给用户张三转账111.11元，两个账户都有777.77元。

事务有两个结果，一是成功二是回滚，如果在事务中任何一个操作发生异常就会回滚。

上面代码只是做测试并未做金额是否满足转账要求检查，先使用 conn.setAutoCommit(false);将自动提交设置为手动提交，默认是自动，然后批量执行两个sql语句，在conn.commit();提交事务之前如果没出现错误，执行结果会保存到数据库，一旦出现异常，就会执行conn.rollback();回滚操作。执行上面代码转账成功输出结果如下:

上面是提交成功的例子，为了演示事务回滚，可以在提交事务conn.rollback();之前制造一个异常int a=1/0;,然后执行，发现数据库的值并不会改变，并抛出了异常。