week09_day05_JDBC

总结
索引
	联合索引
		a. 最左前缀法则
		b. 键是排好序
	覆盖索引
		a. 避免回表
		b. 统计计算会优先选择覆盖索引
	什么情况下应该创建索引
		a. 有唯一性限制字段
		b. 频繁作为where过滤的字段
		c. 分组和排序的字段
		d. distinct 
		e. update, delete 中where过滤条件的字段
		f. 多表连接时的join字段
		
	什么情况不应该创建索引
		a. 不在where, group by, order by或者是distinct中出现的字段 
		b. 数据量不大的情况
		c. 字段中有大量的重复值(字段的区分度低)
		d. 经常更新的字段
		
	什么情况下索引会失效
		a. 对索引列进行了计算
		b. 对索引列使用了函数
		c. or连接的时候，需要每一个列都创建索引，如果有一个没有创建索引，索引会失效。
		d. 模糊查询不能以'%'开头
		e. 查找范围不能太大
		f. not null, <> 

······················································································································································································································

注：本文应当配合JDBC.pdf笔记看，看的更爽

概述：

JDBC (Java Data Base Connectivity) 是一种用于执行 SQL 语句的 Java API。JDBC 是 Java 访问数据库的标准规范，可以为不同的关系型数据库提供统一访问。它由一组用 Java 语言编写的接口和类组成，位于 java.sql 和 javax.sql 包下面。

JDBC 需要连接驱动。客户端要和 DBMS 进行通信，需要满足一定的通信数据格式 (协议)，协议是由数据库厂商制定的，不同的DBMS有不同的协议。因此数据库厂商必须为客户端提供驱动软件，这样客户端才能连接到 DBMS。就像硬件设备厂家为了自家的设备能跑在操作系统上，必须提供驱动程序一样。

Sun 公司指定了 Java 访问数据库的规范，这些规范称为 JDBC。而各个数据库厂商提供了各自的实现，这些实现我们称为驱动 。

蓝色是java应用程序，可以通过使用JDBC定义的API，再通过不同的驱动连接不同的数据库。
JDBC由一组用 Java 语言编写的接口和类组成，不同的数据库底层的协议是不同的，这就要求不同的数据库厂商提供这些接口的实现。所以说连接驱动就是不同的数据库厂商提供这些接口的实现。

···················································································································································································································

以后写项目，会用到单元测试，现在在Moudle Day22_JDBC下导入目录：E:\WangDao\jars\junit4下的hamcrest-core-1.3.jar、junit-4.12.jar和commons-logging-1.2.jar三个jar包。

Day22_JDBC—>右击—>new—>Directroy，目录名写：lib，然后将上述三个jar包复制粘贴到lib目录下

lib—>右击—>Add as Library—>Level写成Module Library，表示仅仅导入的jar包仅仅在本Moudle下起作用。

单元测试例子：

package com.cskaoyan;

import org.junit.Assert;
import org.junit.Test;

/**
 * @author shihao
 * @create 2020-06-04 20:54
 *
 * 单元测试:
 * a. 访问权限必须是public
 * b. 要求方法是成员方法
 * c. 方法的返回值类型必须是void
 * d. 没有参数
 */
public class FooDemo01 {

    //本来是只有main方法才可以run的，导入单元测试的jar包并加上@Test注解后会发现普通成员方法也可以run
    @Test
    public void method(){
        System.out.println("hello world");
        //断言
        //assertEquals(1,a)，第一个参数表示期待的结果，第二个参数表示实际的结果
        //也就是说，期待a == 1
        int a = 2;
        Assert.assertEquals(1,a);
    }

    @Test
    public void test(){
        System.out.println(1/0);
    }
}

···················································································································································································································

入门案例：

package com.cskaoyan.JDBC;

import com.mysql.jdbc.Driver;
import org.junit.Test;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author shihao
 * @create 2020-06-04 21:22
 */
public class FooDemo02 {
    @Test
    public void foo() throws SQLException {
        //注册驱动
        //这个Driver()是在驱动mysql-connector-java-5.1.48.jar的com.mysql.jdbc包下
        //所以全称应当这样写：Driver driver = new com.mysql.jdbc.Driver();
        Driver driver = new Driver();

        //获取连接
        //url中，jdbc是大协议，mysql是子协议，localhost本机，3306端口号，test表示要连接的数据库
        String url = "jdbc:mysql://localhost:3306/jdbc_db";
        Properties info = new Properties();
        info.setProperty("user", "root");
        info.setProperty("password", "069583");
        Connection connect = driver.connect(url, info);
        //或者写个info.properties的配置文件，然后这样写代码
/*      String url2 = "jdbc:mysql://localhost:3306/jdbc_db";
        Properties info2 = new Properties();
        Reader reader = new FileReader("info.properties");
        info2.load(reader);
        Connection connect2 = driver.connect(url, info);*/

        //获取sql执行平台
        Statement stmt = connect.createStatement();

        //执行sql
        String sql = "select * from t_user;";
        ResultSet rs = stmt.executeQuery(sql);

        //处理结果
        while (rs.next()) {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            String password = rs.getString(3);
            BigDecimal balance = rs.getBigDecimal(4);
            System.out.println(id + " " + name + " " + password + " " + balance);
        }

        //断开连接，释放资源
        rs.close();
        stmt.close();
        connect.close();
    }
}

执行完上面代码后，就会在C:\ProgramData\MySQL\MySQL Server 5.7\Data\SC-201907151249.log中有记录：

···················································································································································································································

三：API 详解

3.1 Driver & DriverManager

package com.cskaoyan.JDBC;

import org.junit.Assert;
import org.junit.Test;

import com.mysql.jdbc.Driver;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author shihao
 * @create 2020-06-05 14:29
 * <p>
 * Driver:
 * Connection connect(String url, Properties info)
 * <p>
 * DriverManager即驱动管理器，通过它去管理一系列的驱动，一个java程序可能既要连接Oracle
 * 又要连接mysql，多个数据库会有多个不同的驱动，我们都可以通过DriverManager管理这些驱动
 * DriverManager:
 * static void registerDriver(Driver driver)
 * //向 DriverManager 注册给定驱动程序。
 * static void deregisterDriver(Driver driver)
 * //从 DriverManager 的列表中删除一个驱动程序。
 * static Connection getConnection(String url, String user, String password)
 * //试图建立到给定数据库 URL 的连接。
 */
public class DriverDemo {
    @Test
    public void testConnection1() throws SQLException {
        //注册驱动
        Driver driver = new Driver();

        //获取连接
        String url = "jdbc:mysql://localhost:3306/jdbc_db";
        Properties info = new Properties();
        info.setProperty("user", "root");
        info.setProperty("password", "069583");
        Connection connect = driver.connect(url, info);

        //断言连接不为空
        //如：String url = "jdbc:mysql//localhost:3306/jdbc_db";这行代码少写个：断言失败
        Assert.assertNotNull(connect);
        //或者你可以断言连接不为空
        //Assert.assertNull(connect);
    }


    //testConnection1()都是硬编码的，如Driver、URL、user、password，一旦其中一个改变就得改代码
    @Test
    public void testConnection() throws SQLException {
        //注册驱动
        Driver driver = new Driver();
        //把driver注册到DriverManager中
        DriverManager.registerDriver(driver);

        //获取连接
        //如果在本机上且端口是3306，可以省略localhost:3306
        //即：String url = "jdbc:mysql:///jdbc_db";
        String url = "jdbc:mysql://localhost:3306/jdbc_db";
        String user = "root";
        String password = "069583";
        Connection connec = DriverManager.getConnection(url, user, password);

        //断言
        Assert.assertNotNull(connec);
    }


    //testConnection2()依旧是硬编码的，如url、user、password，一旦其中一个改变就得改代码
    //发现变化，封装变化
    //在Moudle Day22_JDBC下右击--->new --->File --->写入info.properties
    //新建info.properties文件。
    @Test
    @SuppressWarnings("unchecked")
    public void testConnection3() throws SQLException, IOException, ClassNotFoundException, IllegalAccessException, InstantiationException {
        //加载配置文件
        Properties info = new Properties();
        Reader reader = new FileReader("info.properties");
        info.load(reader);

        //注册驱动
        //获取类的全限定名
        String driver = info.getProperty("driver");
        //通过反射创建对象
        //通过Class.forName得到字节码文件对象
        Class<Driver> c1 = (Class<Driver>) Class.forName(driver);
        //在字节码文件对象上调用newInstance()得到java对象
        Driver driver1 = c1.newInstance();
        DriverManager.registerDriver(driver1);

        //获取连接
        String url = info.getProperty("url");
        String user = info.getProperty("user");
        String password = info.getProperty("password");
        Connection connec = DriverManager.getConnection(url, user, password);

        //断言
        Assert.assertNotNull(connec);
    }


    //查看com.mysql.jdbc.Driver的源码会发现有这一段代码：
/*    static {
        try {
            java.sql.DriverManager.registerDriver(new Driver());
        } catch (SQLException E) {
            throw new RuntimeException("Can't register driver!");
        }
    }*/
    //这是写在静态代码块中的，也就是说，类加载的时候就会执行，就会注册驱动
    //那我们就没必要自己创建一个对象，把它加载进来
    @Test
    @SuppressWarnings("unchecked")
    public void testConnection4() throws SQLException, IOException, ClassNotFoundException {
        //加载配置文件
        Properties info = new Properties();
        Reader reader = new FileReader("info.properties");
        info.load(reader);

        //注册驱动
        //获取类的全限定名
        String driver = info.getProperty("driver");
        //通过反射创建对象
        //通过Class.forName得到字节码文件对象
        Class<Driver> c1 = (Class<Driver>) Class.forName(driver);

        //获取连接
        String url = info.getProperty("url");
        String user = info.getProperty("user");
        String password = info.getProperty("password");
        Connection connec = DriverManager.getConnection(url, user, password);

        //断言
        Assert.assertNotNull(connec);
    }


    //JDBC4 规范：要求驱动能够自动加载 (类似设备连接 PC 时，驱动能够自动加载运行一样)。
    // 因此，我们只要导入 jar 包就可以了。
    //对我们来说，我们根本就不知道驱动的存在，就像你买无线鼠标，插上就能用。
    @Test
    public void testConnection5() throws SQLException, IOException, ClassNotFoundException {
        //加载配置文件
        Properties info = new Properties();
        Reader reader = new FileReader("info.properties");
        info.load(reader);

        //获取连接
        String url = info.getProperty("url");
        String user = info.getProperty("user");
        String password = info.getProperty("password");
        Connection connec = DriverManager.getConnection(url, user, password);

        //断言
        Assert.assertNotNull(connec);
    }
}

···················································································································································································································

3.2 Statement

我们可以通过 Statement 执行各种 SQL 语句，包括 DDL、DCL、DML、DQL等。
通过 Statement 创建数据库、创建表、插入数据、更新数据、查找数据、删除表、删除数据库。

一般用execute()执行DDL，executeQuery()执行DQL，executeUpdate()执行DML

package com.cskaoyan.JDBC;

import org.junit.Assert;
import org.junit.Test;

import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.*;
import java.util.Properties;

/**
 * @author shihao
 * @create 2020-06-05 16:40
 */
public class StatementDemo {
    @Test
    public void testCreateDb() throws SQLException {
        //获取连接
        Connection connec = getConnection();

        //创建Statement对象
        Statement stmt = connec.createStatement();

        //执行sql语句
        String sql = "create database test";
        //一般用execute()执行DDL，executeQuery()执行DQL，executeUpdate()执行DML
        stmt.execute(sql);
    }

    @Test
    public void testCreateTb() throws SQLException {
        //获取连接
        Connection connec = getConnection();

        //创建Statement对象
        Statement stmt = connec.createStatement();

        //执行SQL语句
        //执行之前应当将info.properties中的url的数据库改成test
        String sql = "create table t_a(a int)";
        stmt.execute(sql);
    }

    @Test
    public void testInsertData() throws SQLException {
        //获取连接
        Connection connec = getConnection();

        //创建Statement对象
        Statement stmt = connec.createStatement();

        //执行SQL语句
        //执行之前应当将info.properties中的url的数据库改成test
        String sql = "insert into t_a values (1),(2),(3)";
        //executeUpdate(sql)返回受影响的行数
        int rows = stmt.executeUpdate(sql);

        //断言
        Assert.assertEquals(3, rows);
    }

    @Test
    public void testUpdateData() throws SQLException {
        //获取连接
        Connection connec = getConnection();

        //创建Statement对象
        Statement stmt = connec.createStatement();

        //执行SQL语句
        //执行之前应当将info.properties中的url的数据库改成test
        String sql = "update t_a set a = a + 100";
        //executeUpdate(sql)返回受影响的行数
        int rows = stmt.executeUpdate(sql);

        //断言
        Assert.assertEquals(3, rows);
    }


    @Test
    public void testQueryData() throws SQLException {
        //获取连接
        Connection connec = getConnection();

        //创建Statement对象
        Statement stmt = connec.createStatement();

        //执行SQL语句
        //执行之前应当将info.properties中的url的数据库改成test
        String sql = "select * from t_a";
        //executeUpdate(sql)返回受影响的行数
        ResultSet rs = stmt.executeQuery(sql);

        //断言rs.next()是有结果的
        // ResultSet executeQuery(String sql) 执行DQL,返回结果集。不管有没有满足查询条件的记录，
        // ResultSet都不为null.我们可以通过ResultSet的next()方法判断是否有满足查询条件的记录。
        Assert.assertTrue(rs.next());
    }


    //SQL注入问题
    @Test
    public void testInjection() throws SQLException {
        //用户名和密码
        String name = "Thomas";
        String password = "123456";
        name = "'" + name + "'";
        password = "'" + password + "'";

        //但是name和password如果这样写的话就会出现SQL注入
        //String name = "aaa' or 1=";
        //String password = " or 1='1";
        //因为执行的SQL语句是这样的
        //select * from t_user where name='aaa' or 1=' and password=' or 1='1'
        //分析：mysql中将字符串装换成数值，' and password='无法转换，就将其转换为0
        //'1'可以转换，转换结果为1
        //以上sql语句其实就是：select * from t_user where name='aaa' or 1=0 or 1=1
        // where条件有个or 1=1啊，也就是说where条件恒成立
        //整个语句就成了：select * from t_user
        //SQL注入的原因就是把你输入的"aaa' or 1="里面的or当成关键字使用了，
        // '也当成mysql中的符号使用了


        //获取连接
        Connection connec = getConnection();
        //执行平台
        Statement stmt = connec.createStatement();

        //执行SQL语句
        String sql = "select * from t_user where name="
                + name + " and password=" + password;
        System.out.println(sql);
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            int id = rs.getInt("id");
            String userName = rs.getNString("name");
            BigDecimal balance = rs.getBigDecimal("balance");
            System.out.println(id + " " + userName + " " + balance);
        }
    }

    private Connection getConnection() {
        //加载配置文件
        Properties info = new Properties();
        try (Reader reader = new FileReader("info.properties")) {
            info.load(reader);
        } catch (IOException e) {
            e.printStackTrace();
        }

        //获取连接
        String url = info.getProperty("url");
        String user = info.getProperty("user");
        String password = info.getProperty("password");
        Connection connec = null;
        try {
            connec = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connec;
    }
}

···················································································································································································································

3.3 PreparedStatement
通常我们执行一条 SQL 语句，得经过下面三个过程。

1. 词法和语义解析
2. 优化 SQL 语句，制定执行计划
3. 执行并返回结果

我们把这种普通 SQL 语句称作 Immediate Statements。
但是很多情况下，一条 SQL 语句可能会被反复执行。或者是反复执行的 SQL 语句，它们结构相似，只是参数不同而已。 如果每次执行的时候都要经过上面的词法语义解析、优化 SQL、制定执行计划等，那效率就明显不行了。

MySQL 提供了预编译语句，可以帮我们解决这个问题。在预编译语句中，参数我们用占位符 ? 替代，这样我们就可以为多条不同的 SQL 语句，提供统一的模板。我们把这种 SQL 语句称作 Prepared Statements 或者Parameterized Statements。

预编译语句的优势有以下两点：

1. 一次解析优化，多次执行。
2. 可以防止 SQL 注入问题。

3.3.1 语法

• 编译： PREPARE stmt_name FROM prepare_stmt;
• 执行： EXECUTE stmt_name [USING @var_name [, @var_name] …];
• 删除： {DEALLOCATE | DROP} PREPARE stmt_name

3.3.2 Java 程序调用
在 Java 中我们提供了 PreparedStatement 这个接口来调用数据库的预编译功能。

package com.cskaoyan.JDBC;

import org.junit.Test;

import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.*;
import java.util.Properties;

/**
 * @author shihao
 * @create 2020-06-06 8:34
 */
public class PrepareStatementDemo {
    //可以有效的避免sql注入问题
    @Test
    public void testInjection() throws SQLException {
        //用户名和密码
        String name = "Thomas";
        String password = "123456";
        //String name = "aaa' or 1=";
        //String password = " or 1='1";
		//不用写下面两行代码，因为设置参数时setString会告诉是String类型
        //而对于Statement，它的参数是得自己拼接的
//        name = "'" + name + "'";
//        password = "'" + password + "'";
  
        //获取连接
        Connection connec = getConnection();

        //由于问号的存在，传入的参数只会当做参数来处理，并不会把它当成逻辑的一部分
        String sql = "select * from t_user where name=? and password=?;";
        //预编译后生成PreparedStatement对象
        PreparedStatement pstmt = connec.prepareStatement(sql);

        //设置参数
        pstmt.setString(1, name);
        pstmt.setString(2, password);

        //执行
        ResultSet rs = pstmt.executeQuery();

        //处理结果集
        while (rs.next()) {
            int id = rs.getInt("id");
            String username = rs.getString("name");
            BigDecimal balance = rs.getBigDecimal("balance");
            System.out.println(id + ":" + username + ": " + balance);
        }
    }


    private Connection getConnection() {
        //加载配置文件
        Properties info = new Properties();
        try (Reader reader = new FileReader("info.properties")) {
            info.load(reader);
        } catch (IOException e) {
            e.printStackTrace();
        }

        //获取连接
        String url = info.getProperty("url");
        String user = info.getProperty("user");
        String password = info.getProperty("password");
        Connection connec = null;
        try {
            connec = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connec;
    }
}

···················································································································································································································

PreparedStatement和Statement性能比较：

package com.cskaoyan.JDBC;

import org.junit.Test;

import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.*;
import java.util.Properties;

/**
 * @author shihao
 * @create 2020-06-06 10:51
 */
public class performance {

    //网络传输的时间+mysql执行的时间（大部分时间耗费在了网络传输上）
    //即网络连接一次，插入一条数据，这样网络连接了500次
    //可不可以网络连接一次，数据打包后一次性执行完呢？这就涉及批处理了
    @Test
    public void testPrepareStatementPerformance() throws SQLException {   //21059ms
        Connection connec = getConnection();
        String sql = "insert into t_user(name,password) values(?,?)";
        PreparedStatement pstmt = connec.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 500; i++) {
            pstmt.setString(1, "user" + i);
            pstmt.setString(2, "123456");
            pstmt.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println(end - start + "ms");
    }

    @Test
    public void testStatementPerformance() throws SQLException {   //43338ms
        Connection connec = getConnection();
        Statement stmt = connec.createStatement();
        long start = System.currentTimeMillis();
        for (int i = 0; i < 500; i++) {
            String sql = "insert into t_user(name,password) values('user" + i + "', '123456')";
            stmt.executeUpdate(sql);
        }
        long end = System.currentTimeMillis();
        System.out.println(end - start + "ms");
    }

    private Connection getConnection() {
        //加载配置文件
        Properties info = new Properties();
        try (Reader reader = new FileReader("info.properties")) {
            info.load(reader);
        } catch (IOException e) {
            e.printStackTrace();
        }

        //获取连接
        String url = info.getProperty("url");
        String user = info.getProperty("user");
        String password = info.getProperty("password");
        Connection connec = null;
        try {
            connec = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connec;
    }
}

···················································································································································································································

3.4 Batch
不管是 Statement 还是 PreparedStatement，执行一条 SQL 语句，客户端都要和服务器进行一次通信，这样效率就比较低下。因此，MySQL 提供了批处理功能。

这些方法在Statement接口中定义：

• void addBatch(String sql)
  将给定的 SQL 命令添加到此 Statement 对象的当前（批处理的）命令列表中。
• void clearBatch()
  清空此 Statement 对象的当前 SQL （批处理的）命令列表。
• int[] executeBatch()
  将一批命令提交给数据库来执行，如果全部命令执行成功，则返回更新计数组成的数组。

批处理后的代码性能会有很大的提升：

package com.cskaoyan.JDBC;

import org.junit.Test;

import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.*;
import java.util.Properties;

/**
 * @author shihao
 * @create 2020-06-06 11:19
 * <p>
 * Statement:
 * //批处理一般执行的是DML
 * void addBatch(String sql)
 * void clearBatch()
 * int[] executeBatch()
 */
public class BatchDemo {
    @Test
    public void testPrepareStatementPerformance() throws SQLException {   //265ms
        Connection connec = getConnection();
        String sql = "insert into t_user(name,password) values(?,?)";
        PreparedStatement pstmt = connec.prepareStatement(sql);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 500; i++) {
            //缓冲区中攒够250条数据后就执行，如果不写的话就是全攒够了（500条）再执行，
            //这样会耗费更多的内存
            if (i%250==0){
                //执行缓冲区中的SQL语句
                pstmt.executeBatch();
                //清空缓冲区
                pstmt.clearBatch();
            }
            pstmt.setString(1, "user" + i);
            pstmt.setString(2, "123456");
            //将任务加入到缓冲区
            pstmt.addBatch();
        }
        //把最后一批遗留在缓冲区中的任务执行
        pstmt.executeBatch();
        //清空缓冲区
        pstmt.clearBatch();
        long end = System.currentTimeMillis();
        System.out.println(end - start + "ms");
    }

    private Connection getConnection() {
        //加载配置文件
        Properties info = new Properties();
        try (Reader reader = new FileReader("info.properties")) {
            info.load(reader);
        } catch (IOException e) {
            e.printStackTrace();
        }

        //获取连接
        String url = info.getProperty("url");
        String user = info.getProperty("user");
        String password = info.getProperty("password");
        Connection connec = null;
        try {
            connec = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connec;
    }
}

···················································································································································································································

3.4 ResultSet
Java 中用 ResultSet 接口代表数据库返回的结果集。
ResultSet 封装执行结果时，采用的类似于表格的方式。ResultSet 对象维护了一个指向表格数据行的游标，初始的时候，游标在第一行之前。ResultSet 的 next() 方法，可以判断是否有还有下一行数据。如果有，返回 true, 将游标指向该行，我们可以通过 ResultSet 对象获取该行的数据。如果没有，返回false。

3.4.1 ResultSet API
getXXX(int index)
getXXX(String columnName)
next()
previous()
absolute(int row)
beforeFirst()
afterLast()

package com.cskaoyan.JDBC;

import org.junit.Test;

import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.*;
import java.util.Properties;

/**
 * @author shihao
 * @create 2020-06-06 14:24
 * <p>
 * ResultSet:
 * getXXX(int index)
 * getXXX(String columnLabel)
 * boolean next()
 * boolean previous()
 * void beforeFirst()
 * void afterLast()
 * void absolute(int row)
 */
public class ResultSetDemo {
    //根据索引获取结果集中的数据
    @Test
    public void testGetter1() throws SQLException {
        Connection connec = getConnection();
        String sql = "select * from t_user where id=?";
        PreparedStatement pstmt = connec.prepareStatement(sql);
        pstmt.setInt(1,1);
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            String password = rs.getString(3);
            BigDecimal balance = rs.getBigDecimal(4);
            System.out.println(id + " " + name + " " + password + " " + balance);
        }
    }

    //根据字段名获取结果集中的数据
    @Test
    public void testGetter2() throws SQLException {
        Connection conn = getConnection();
        String sql = "select id, name as username, password, balance from t_user where id = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, 1);
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            int id = rs.getInt("id");
            //注：如果起别名的话获取字段信息的时候得用别名
            String name = rs.getString("username");
            String password = rs.getString("password");
            BigDecimal balance = rs.getBigDecimal("balance");
            System.out.println(id + " " + name + " " + password + " " + balance);
        }
    }

    //演示光标操作
    @Test
    public void testCursor() throws SQLException {
        Connection connec = getConnection();
        String sql = "select * from t_user where id<=?";
        PreparedStatement pstmt = connec.prepareStatement(sql);
        pstmt.setInt(1,2);
        ResultSet rs = pstmt.executeQuery();
        //将光标移动到最后
        rs.afterLast();
        //从后往前获取数据
        while (rs.previous()) {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            String password = rs.getString(3);
            BigDecimal balance = rs.getBigDecimal(4);
            System.out.println(id + " " + name + " " + password + " " + balance);
        }

        //获取第二行数据  void absolute(int row) (绝对定位，从1开始)
        rs.absolute(2);
        int id = rs.getInt(1);
        String name = rs.getString(2);
        String password = rs.getString(3);
        BigDecimal balance = rs.getBigDecimal(4);
        System.out.println(id + " " + name + " " + password + " " + balance);

        //beforeFirst、next()
        rs.beforeFirst();
        while (rs.next()) {
            int id2 = rs.getInt(1);
            //注：如果起别名的话获取字段信息的时候得用别名
            String name2 = rs.getString(2);
            String password2 = rs.getString(3);
            BigDecimal balance2 = rs.getBigDecimal(4);
            System.out.println(id2 + " " + name2 + " " + password2 + " " + balance2);
        }
    }


    private Connection getConnection() {
        //加载配置文件
        Properties info = new Properties();
        try (Reader reader = new FileReader("info.properties")) {
            info.load(reader);
        } catch (IOException e) {
            e.printStackTrace();
        }

        //获取连接
        String url = info.getProperty("url");
        String user = info.getProperty("user");
        String password = info.getProperty("password");
        Connection connec = null;
        try {
            connec = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connec;
    }
}

···················································································································································································································

3.4.2 常用数据类型对应表

···················································································································································································································

3.5 管理连接、语句和结果集
每个 Connection 对象都可以创建一个或多个 Statement 对象。同一个 Statement 对象可以用于多个不相关的命令和查询。但是，一个 Statement 对象最多只能有一个打开的结果集。如果需要执行多个查询操作，且需要同时分析查询结果，那么必须创建多个 Statement 对象。

使用完 ResultSet、Statement 或 Connection 对象后，应立即调用 close() 方法。这些对象都使用了规模较大的数据结构，它们会占用数据库服务器有限的资源。

如果 Statement 对象上有一个打开的结果集，那么调用 close() 方法将自动关闭该结果集。同样地，调用 Connection 类的 close() 方法将关闭该连接上所有的 Statement。

但我们最好用完一个结果集就关闭这个结果集，用完一个Statement对象就关闭这个Statement对象。而不是等到最好关闭Connection，及时释放资源。就像水龙头用完就关，而不是好几个水龙头都用完了再去关闭总闸

···················································································································································································································

3.6 JDBC 事务相关 API

这些方法在Connection接口中定义：

void setAutoCommit(boolean autoCommit)
void commit()
void rollback()
void rollback(Savepoint savepoint)
Savepoint setSavepoint()
void releaseSavepoint(Savepoint savepoint)
void setTransactionIsolation(int level)
static int TRANSATION_NONE, TRANSACTION_READ_UNCOMMITTED,
TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ,
TRANSACTION_SERIALIZABLE

void setTransactionIsolation(int level)
level指的就是以下5种字段：

编码方式：按位编码：0000、0001、0010、0100

package com.cskaoyan.JDBC;

import org.junit.Test;

import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author shihao
 * @create 2020-06-06 15:10
 * <p>
 * Connection:
 * Statement createStatement()
 * PreparedStatement prepareStatement(String sql)
 * void setAutoCommit(boolean autoCommit)
 * void commit()
 * Savepoint setSavepoint()
 * Savepoint setSavepoint(String name)
 * void releaseSavepoint(Savepoint savepoint)
 * void rollback()
 * void rollback(Savepoint savepoint)
 * void setTransactionIsolation(int level)
 * static int TRANSACTION_NONE, TRANSACTION_READ_UNCOMMITTED, TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ, TRANSACTION_SERIALIZABLE
 */
public class TransantionDemo {
    @Test
    public void testTransaction1() throws SQLException {
        Connection connec = getConnection();
        //1号转账
        String sql = "update t_user set balance = balance - 100 where id = ?";
        PreparedStatement pstmt = connec.prepareStatement(sql);
        pstmt.setInt(1, 1);
        pstmt.executeUpdate();

        //中间出现除零异常后，代表转账过程出现故障，会发现1号balance少了100,2号balance却没变
        //因为整个过程不是原子性操作
        System.out.println(1 / 0);

        //2号收账单
        String sql2 = "update t_user set balance = balance + 100 where id = ?";
        PreparedStatement pstmt2 = connec.prepareStatement(sql);
        pstmt.setInt(1, 2);
        pstmt.executeUpdate();
    }

    @Test
    public void testTransaction2() throws SQLException {
        Connection connec = getConnection();
        //1号转账
        String sql = "update t_user set balance = balance - 100 where id = ?";
        PreparedStatement pstmt = connec.prepareStatement(sql);
        pstmt.setInt(1, 1);
        //开启事务
        connec.setAutoCommit(false);
        pstmt.executeUpdate();

        System.out.println(1 / 0);

        //2号收账单
        String sql2 = "update t_user set balance = balance + 100 where id = ?";
        PreparedStatement pstmt2 = connec.prepareStatement(sql);
        pstmt.setInt(1, 2);
        pstmt.executeUpdate();

        //提交事务
        connec.commit();
    }

    private Connection getConnection() {
        //加载配置文件
        Properties info = new Properties();
        try (Reader reader = new FileReader("info.properties")) {
            info.load(reader);
        } catch (IOException e) {
            e.printStackTrace();
        }

        //获取连接
        String url = info.getProperty("url");
        String user = info.getProperty("user");
        String password = info.getProperty("password");
        Connection connec = null;
        try {
            connec = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connec;
    }
}