JDBC

1、JDBC

1.1、数据库驱动

类似于声卡驱动、显卡驱动，数据库也需要驱动！

MySQL驱动 、 Oracle驱动…

我们的程序会通过数据库驱动和数据库打交道！

1.2、JDBC

SUN为了简化开发人员的（对数据库的统一）操作，提供了一个（Java操作数据库的）规范，俗称JDBC！

对于开发人员来说，我们只需要掌握JDBC即可操纵数据库！

需要导入jar包 mysql-connector-java

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.19</version>
</dependency>

1.3、第一个JDBC程序

1. 创建一个普通项目

2. 导入数据库驱动

   <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
   <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <version>8.0.19</version>
   </dependency>
   

3. 编写测试代码

//我的第一个JDBC程序
public class JdbcFirstDemo {
    // 1、加载驱动
    Class.forName("com.mysql,jdbc.Driver");  //固定写法，加载驱动
    
    // 2、用户信息和url
    //useUnicode=true 支持中文编码
    // characterEncoding=utf8 设定中文字符集为utf8
    // useSSL=true 使用安全连接
    
    String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
    String username = "root";
    String password = "123456";

    // 3、连接成功、数据库对象  Connection代表数据库

     Connection connection = DriverManager.getConnection(url,username,password);
     
    // 4、执行SQL对象  即Statement执行SQL对象
    
     Statement statement = connection.createStatement();
     
    // 5、执行SQL的对象去执行SQL ，可能存在结果，查看返回结果
    String sql = "SELECT * FROM users";
    ResultSet resultSet = statement.executeQuery(sql); //返回的结果集，结果集中封装了我们查询的所有的结果
    
    // 6、释放连接
    resultSet.close();
    statement.close();
    connection.close();
    
}

步骤总结：

1. 加载驱动
2. 连接数据库 DriverManager
3. 获得执行sql的对象 statement
4. 获得返回的结果集 resultSet
5. 释放连接

DriverManager

 Class.forName("com.mysql,jdbc.Driver");  //固定写法，加载驱动

 Connection connection = DriverManager.getConnection(url,username,password);
//connection 代表数据库  可以操作数据库相关事务（自动提交、回滚...）

URL

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";

//mysql --固定端口号：3306
// 协议://主机地址：端口号/数据库名？参数1&参数2&参数3...;

statement 执行sql的对象

statement.executeQuery()； //查询操作返回 ResultSet
  等等..

ResultSet 查询的结果集：封装了所有的查询结果

resultSet.getObject //获得指定的未知类型对象
  ...

释放资源

    resultSet.close();
    statement.close();
    connection.close(); //耗资源，用完关掉

1.4、Statement对象

1.4.1、创建 Statement 对象：

​ 建立了到特定数据库的连接之后，就可用该连接发送 SQL 语句。Statement 对象用 Connection 的方法createStatement 创建，如下列代码段中所示：

Connection con = DriverManager.getConnection(url, "sunny","");
Statement stmt = con.createStatement();

为了执行 Statement 对象，被发送到数据库的 SQL 语句将被作为参数提供给 Statement 的方法：

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table2");

1.4.2、使用 Statement 对象执行语句：

​ Statement 接口提供了三种执行 SQL 语句的方法：executeQuery、executeUpdate 和execute。使用哪一个方法由 SQL 语句所产生的内容决定。

方法 executeQuery：

​ 用于产生单个结果集的语句，例如 SELECT 语句。

方法 executeUpdate ：

​ 用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQLDDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数，指示受影响的行数（即更新计数）。对于CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。

方法 execute：

​ 用于执行返回多个结果集、多个更新计数或二者组合的语句。因为多数程序员不会需要该高级功能，所以本概述后面将在单独一节中对其进行介绍。

执行语句的所有方法都将关闭所调用的 Statement 对象的当前打开结果集（如果存在）。这意味着在重新执行 Statement对象之前，需要完成对当前 ResultSet 对象的处理。

​ 应注意，继承了 Statement 接口中所有方法的 PreparedStatement 接口都有自己的executeQuery、executeUpdate 和 execute 方法。Statement 对象本身不包含 SQL语句，因而必须给 Statement.execute 方法提供 SQL 语句作为参数。PreparedStatement 对象并 不将SQL 语句作为参数提供给这些方法，因为它们已经包含预编译 SQL 语句。CallableStatement 对象继承这些方法的PreparedStatement 形式。对于这些方法的 PreparedStatement 或 CallableStatement版本，使用查询参数将抛出 SQLException。

1.4.3、语句完成：

​ 当连接处于自动提交模式时，其中所执行的语句在完成时将自动提交或还原。语句在已执行且所有结果返回时，即认为已完成。对于返回一个结果集的executeQuery 方法，在检索完 ResultSet 对象的所有行时该语句完成。对于方法executeUpdate，当它执行时语句即完成。但在少数调用方法 execute的情况中，在检索所有结果集或它生成的更新计数之后语句才完成。

statement-相关概述

Statement 对象用于将 SQL 语句发送到数据库中。

实际上有三种 Statement 对象，它们都作为在给定连接上执行 SQL语句的包容器：

​ Statement、PreparedStatement（它从 Statement 继承而来）和CallableStatement（它从 PreparedStatement 继承而来）。

它们都专用于发送特定类型的 SQL 语句：

​ Statement 对象用于执行不带参数的简单 SQL 语句；

​ PreparedStatement 对象用于执行带或不带 IN参数的预编译 SQL 语句；

​ CallableStatement 对象用于执行对数据库已存储过程的调用。

Statement 接口提供了执行语句和获取结果的基本方法。

PreparedStatement 接口添加了处理 IN 参数的方法；而CallableStatement 添加了处理 OUT 参数的方法。

有些 DBMS将已存储过程中的每条语句视为独立的语句；而另外一些则将整个过程视为一个复合语句。在启用自动提交时，这种差别就变得非常重要，因为它影响什么时候调用commit 方法。在前一种情况中，每条语句单独提交；在后一种情况中，所有语句同时提交。

1.4.4、关闭 Statement 对象：
Statement 对象将由 Java 垃圾收集程序自动关闭。而作为一种好的编程风格，应在不需要 Statement对象时显式地关闭它们。这将立即释放 DBMS 资源，有助于避免潜在的内存问题。

1.5、SQL注入问题

SQL注入:

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

SQL注入问题

用户输入的内容, 在SQL语句拼接过程中, 完成了一条逻辑发生变化的新的SQL语句 !


例如:  

原SQL语句拼接为: 

    String sql = "select id from user15 where username='"+user.getUserName()+"' and password='"+user.getPassWord()+"'";
    
用户输入的帐号密码分别为:

请输入您的帐号:
suibian
请输入您的密码:
suibian' or '1'='1
组成的SQL语句: 
select id from user15 where username='suibian' and password='suibian' or '1'='1'

解决SQL注入问题

使用预编译SQL语句 进行参数的传递

更改执行环境 Statement  

使用新的环境: PreparedStatement


在通过连接对象 获取一个预编译的SQL环境(PreparedStatement)时, 需要传递 一个SQL语句 ! 
在这个语句中 可以出现? , ? 表示准备填充的参数值!


使用步骤: 

    1.  通过连接对象, 获得一个预编译的SQL执行环境

        PreparedStatement state conn.prepareStatement(sql);

        例如: 
            String sql = "select id from user15 where username=? and password=?";
    
            PreparedStatement state  = conn.prepareStatement(sql);  
            
    2.    向预编译参数列表中 传递值: 

        预编译的SQL语句中可以包含0-n个问号, 每一个问号表示一个需要传递的值 
        我们通过PreparedStatement它的setXXX方法,来完成参数的传递
        在传递参数时, 需要指定问号的索引, 问号的索引从1开始            

        例如:
            state.setString(1,user.getUserName());
            state.setString(2,user.getPassWord());

    3.  执行语句:
            ResultSet result = state.executeQuery();

PreparedStatement解决SQL注入

常用方法: 
    
    填充预编译的参数: 

    -   setXXX(问号索引,值)
        向预编译的SQL的?中传递值 
        参数1. 问号的索引 ,从1开始
        参数2. 填充到? 中的值

    -   execute()
    -   executeUpdate();
    -   executeQuery();
        上面的三个方法 与 Statement中方法的含义一致, 只不过不存在参数!

1.6、PrepareStatement对象

1.6.1、PreparedStatement与Statement的区别

1.不需要sql语句拼接，防止sql注入，更加安全

2.用占位符的方式写sql，便于后期维护，提高代码可读性，可以自动对类型进行转换

3.有预编译功能，可以大批量处理sql，（mysql不明显，Oracle很明显）

4.向数据库中添加一条数据

5.PreparedStatement:用于执行sql语句的对象

6.用connection的PreparedStatement(sql)方法获取

7.用executeUpdate(sql)执行sql语句

PreparedStatement对象来实现JDBC增删改查

这是连接数据库的方法，放到此工具类中

package com.zhiyou.jdbc.connection;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCTools {
    /**
     * 这是连接数据库的方法，放到此工具类中
     * @return
     * @throws IOException 
     *
     *
     */
    static Connection con;
    static String url;
    static Properties properties;
    static{
        //1.创建properties对象
        properties = new Properties();
        try {
            //2.从类路径下加载db.properties文件
            properties.load(JDBCTools.class.getClassLoader().getResourceAsStream("db.properties"));
            //3.获取db.properties中加载的url信息
            url=properties.getProperty("url");
        } catch (IOException e) {
            e.printStackTrace();
        }
          
    }
    //4.通过DriverManager的getConnection()方法获取数据库连接
    public static Connection getConnections(){
        try {
            con = DriverManager.getConnection(url,properties);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
     
    }
    /**
    　　*这是关闭数据库的工具方法，因为曾删改都需要用，所以写在此工具方法中
    */
    public static void close(Statement statement,Connection con){
        try {
            statement.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            con.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        
    }

}

增加：

public void testPreparedStatement(){
      Connection con=null;
      PreparedStatement pstmt=null;//创建PreparedStatement对象
      try {
          //1、准备Connection连接数据库
            con=JDBCTools.getConnections();
          //2、准备sql语句
          //sql语句不再采用拼接方式，应用占位符问号的方式写sql语句。
          String sql="insert into users(name,age,email) values(?,?,?)";
          pstmt =con.prepareStatement(sql);
          //4、占位符设置值
          pstmt.setString(1, "zdm");
          pstmt.setInt(2, 27);
          pstmt.setString(3, "1583785269@qq.com");
          //5、执行sql
          pstmt.executeUpdate();
     } catch (SQLException e) {
        e.printStackTrace();
     }finally{

         //6、关闭数据库等
         JDBCTools.close(pstmt, con); 
     }
    }

修改：

public void testPreparedStatement(){
          Connection con=null;
          PreparedStatement pstmt=null;//创建PreparedStatement对象
            try {
                con=JDBCTools.getConnection();
                //sql语句不再采用拼接方式，应用占位符问号的方式写sql语句。
                String sql="update users set name=?,age=?,email=? where id=?";
                //对占位符设置值，占位符顺序从1开始，第一个参数是占位符的位置，第二个参数是占位符的值。
               pstmt=con.prepareStatement(sql);
               pstmt.setString(1, "asas");
                pstmt.setInt(2, 12);
                pstmt.setString(3, "asa@jjj");
                pstmt.setInt(4, 11);
                //执行sql
                preStatement.executeUpdate();
            } catch (Exception e) {
                e.printStackTrace();
            }finally{

             //关闭数据库等
              JDBCTools.close(pstmt, con);
            }
        }

查询：

public void PreparedStatement(){
        Connection con=null;
        PreparedStatement pstmt=null;//创建PreparedStatement对象
        ResultSet res=null;
      try {
          //1.准备Connection连接数据库
          
          con=JDBCTools.getConnections();            
          //2.准备sql字符串
          
          String sql="select * from users where id=8";
          //3.准备prepareStatement
          
          pstmt=con.prepareStatement(sql);
          
          //4.执行sql得到ResultSet
          
          res = pstmt.executeQuery();
          
          //5.处理ResultSet显示查询到的结果
          
          while(res.next()){
              int id=res.getInt(1);
              String name=res.getString(2);
              int age=res.getInt(6);
              String email=res.getString(4);
              System.out.println("id"+id+"\t name"+name+"\t email"+email);
          }
          
      } catch (Exception e) {
          e.printStackTrace();
      }finally{
        //6.关闭
          JDBCTools.close(res, pstmt, con);
      }
    }

1.7、事务

事务：要么都成功，要么都失败！

ACID原则

• 原子性：要么全部完成，要么不完成
• 一致性：总数不变
• 隔离性：多个进程互不干扰
• 持久性：一旦提交不可逆，持久化到数据库了

隔离性的问题：

• 脏读：一个事物读取了另一个没有提交的事务
• 不可重复读：在同一个事物内，重复读取表中的数据，表数据发生了改变
• 虚度（幻读）：在一个事物内，读取到了别人插入的数据，导致前后读出来的数据不一致

并发事务问题

因为并发事务导致的问题大致有5类，其中两类是更新问题三类是读问题。

• 脏读（dirty read）：读到另一个事务的未提交新数据，即读取到了脏数据；
• 不可重复读（unrepeatable）：对同一记录的两次读取不一致，因为另一事务对该记录做了修改；
• 幻读（虚读）（phantom read）：对同一张表的两次查询不一致，因为另一事务插入了一条记录。

四大隔离级别

4个等级的事务隔离级别，在相同的数据环境下，使用相同的输入，执行相同的工作，根据不同的隔离级别，可以导致不同的结果。不同事务隔离级别能够解决的数据并发问题的能力是不同的。

1、SERIALIZABLE(串行化)

• 不会出现任何并发问题，因为它是对同一数据的访问是串行的，非并发访问的；
• 性能最差

2、REPEATABLE READ(可重复读)（MySQL）

• 防止脏读和不可重复读，不能处理幻读
• 性能比SERIALIZABLE好

3、READ COMMITTED(读已提交数据)（Oracle）

• 防止脏读，不能处理不可重复读和幻读；
• 性能比REPEATABLE READ好

4、READ UNCOMMITTED(读未提交数据)

• 可能出现任何事物并发问题，什么都不处理。
• 性能最好

MySQL隔离级别

MySQL的默认隔离级别为Repeatable read,可以通过下面语句查看：

SELECT @@TX_ISOLATION;

也可以通过下面语句来设置当前连接的隔离级别：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;//[4选1]

JDBC设置隔离级别

con.setTransactionIsolation(int level) :参数可选值如下：

• Connection.TRANSACTION_READ_UNCOMMITTED；
• Connection.TRANSACTION_READ_COMMITTED；
• Connection.TRANSACTION_REPEATABLE_READ；
• Connection.TRANSACTION_READ_SERIALIZABLE。

JDBC事务

在JDBC中处理事务，都是通过Connection完成的。

同一事务中所有的操作，都在使用同一个Connection对象。

Connection的三个方法与事务有关：

• setAutoCommit（boolean）:设置是否为自动提交事务，如果true（默认值为true）表示自动提交，也就是每条执行的SQL语句都是一个单独的事务，如果设置为false，那么相当于开启了事务了；con.setAutoCommit(false) 表示开启事务。
• commit（）：提交结束事务。
• rollback（）：回滚结束事务。

JDBC处理事务的代码格式：

try{
     con.setAutoCommit(false);//开启事务
     ......
     con.commit();//try的最后提交事务      
} catch（） {
    con.rollback();//回滚事务
}

示例：

public class AccountDao {
    /*
    * 修改指定用户的余额
    * */
    public void updateBalance(Connection con, String name,double balance) {
        try {
            String sql = "UPDATE account SET balance=balance+? WHERE name=?";
            PreparedStatement pstmt = con.prepareStatement(sql);
            pstmt.setDouble(1,balance);
            pstmt.setString(2,name);
            pstmt.executeUpdate();
        }catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

import cn.itcast.jdbc.JdbcUtils;
import org.junit.Test;
import java.sql.Connection;
import java.sql.SQLException;

public class Demo1 {
    /*
    * 演示转账方法
    * 所有对Connect的操作都在Service层进行的处理
    * 把所有connection的操作隐藏起来，这需要使用自定义的小工具（day19_1）
    * */
    public void transferAccounts(String from,String to,double money) {
        //对事务的操作
        Connection con = null;
        try{
            con = JdbcUtils.getConnection();
            con.setAutoCommit(false);
            AccountDao dao = new AccountDao();
            dao.updateBalance(con,from,-money);//给from减去相应金额
            if (true){
                throw new RuntimeException("不好意思，转账失败");
            }
            dao.updateBalance(con,to,+money);//给to加上相应金额
            //提交事务
            con.commit();

        } catch (Exception e) {
            try {
                con.rollback();
            } catch (SQLException e1) {
                e.printStackTrace();
            }
            throw new RuntimeException(e);
        }
    }
    @Test
    public void fun1() {
        transferAccounts("zs","ls",100);
    }
}

1.8、数据库连接池

数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高对数据库操作的性能。

数据库连接–执行完毕–释放–连接–释放… 十分浪费资源

池化技术：池化技术能够减少资源对象的创建次数，提高程序的性能，特别是在高并发下这种提高更加明显。使用池化技术缓存的资源对象有如下共同特点：1，对象创建时间长；2，对象创建需要大量资源；3，对象创建后可被重复使用。

当一个连接通道使用完后，系统并不会将其释放，而是将它存储在连接池里。下次需要使用连接通道的时候，系统会判断连接池里是否有空闲的连接通道对象。如果有，设置其连接字符串，修改其连接状态，重新使用。

这样能在很大程度上减少实例化的次数，节省了内存和时间，提升了效率。 要知道，创建一个对象需要做3件事：开辟堆空间，调用构造函数，返回地址。而这些行为是很消耗性能的。

常见连接池：

DBCP

C3P0

Druid ：阿里巴巴

使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了！