1. 简单认识 JDBC
2. Spring 中的两个改进设计
- 2.1 DataSource 与连接池
- 2.2 JdbcTemplate
  - 2.2.1 改进点
  - 2.2.2 使用
3. JDBC 控制事务
- 3.1 基本概念
- 3.2 使用
4. 总结与思考
参考

1. 简单认识 JDBC

JDBC（Java DataBace Connectivity）官方（sun 公司）定义的操作所有关系型数据库的规则（接口），各个数据库厂商去实现这套接口并提供数据库驱动 jar 包，使得程序员可以使用这套接口来对数据库进行操作；

1.1 一个简单的使用案例
从案例可以看出，使用 Jdbc 需要的关键几步为：
- 【开始】让程序找到数据库
  - 导入要使用的数据库的驱动 jar 包，eg：mysql-connector-java-xxx.jar；
  - 注册驱动：Class.forName(“com.mysql.jdbc.Driver”); //执行静态代码块实现注册；
    - 注意：在导入 jar 包的时候，会自动实现注册这个过程；

【连接】建立程序和数据库间的传输通道：获取连接 Connection；
【过程】操作数据库
- 创建跨过通道来执行 sql 语句的对象：获取执行 sql 的对象 Statement；
- 执行 sql 语句，并获取结果；
【结束】结束程序与数据库的连接，释放资源；
- 涉及的几个主要的类
DriverManager：驱动管理对象
- 功能
  - 注册驱动：告诉程序该使用哪一个数据库驱动 jar；
  - 获取数据库连接：DriverManager.getConnection(String url, String user, String password)；
    - 实际上 DriverManager 内部维护着一个 CopyOnWriteArrayList 来存放已注册的驱动实例，通过底层循环遍历驱动，找到当前注册的驱动后再调用 driver.connect() 获得 Connection；
Connection：数据库连接对象
- 功能
  - 获取执行 sql 的对象
    - Statement createStatement()
    - PreparedStatement prepareStatement(String sql)【推荐使用】
  - 管理事务（具体使用详见）
Statement/PreStatement：执行 sql 的对象
- PrepareStatement：执行 sql 的对象，相比 Statement 不仅能防止 sql 注入，而且效率更高；
- SQL 注入问题：在拼接 sql 时，有些特殊关键字参与字符串的拼接，会造成安全性问题；
  - sql：select * from user where username = ‘csgu’ and password = ‘a’ or ‘a’=’a’
  - username 和 password 就算错误，但是后边的 ‘a’ = ‘a’ 恒为真；
使用 PrepareStatement 对象来解决 SQL 注入问题；
- 定义 sql
  - 注意：sql 的参数使用？作为占位符，如 “select * from user where id = ?”
- 获取执行 sql 语句的对象 PrepareStatement pstmt = conn.prepareStatement(select_sql);
- 给？赋值：setXXX(参数1,参数2)【XXX 指对应列属性的数据类型，参数1为列的编号，参数2为?的值】
  1.2 IDEA 实现简单的 CURD

案例 1 ：简单使用 executeUpdate 和 executeQuery 实现 CURD ```java package com.cyt.jdbc;

import java.sql.*; import java.util.Iterator;

public class JdbcDemo2 { public static void main(String[] args) { Connection conn = null; Statement stmt = null; try{ Class.forName(“com.mysql.jdbc.Driver”); conn = DriverManager.getConnection(“jdbc:mysql://localhost:3306/test_jdbc”,”root”,”1234”); stmt = conn.createStatement(); String insert_sql = “insert into user values(‘202006’, ‘ly’, 18)”; String update_sql = “update user set age = 3 where name like ‘cyt’”; String delete_sql = “delete from user where age = 3”; String select_sql = “select * from user”; // executeUpdate 执行 DML(insert、update、delete) 语句和 DDL（有关表的一些操作，如create、alter、drop） int res1 = stmt.executeUpdate(delete_sql); //返回值为影响的行数 // executeQuery 执行 DQL(select) 语句 ResultSet res2 = stmt.executeQuery(select_sql);//返回值为查询的表结果集 System.out.println(res1); while (res2.next()) { String id = res2.getString(1); //列号从 1 开始，此外也可以通过重载方法res2.getString(String id)获取 String name = res2.getString(2); int age = res2.getInt(3); System.out.println(“第” + res2.getRow() + “行的遍历结果为: id=” + id + “, name=” + name + “, age=” +age); } } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } finally { if (stmt != null) { //防止报空指针异常 try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } } public class User { private String id; private String name; private int age;

public String getId() {
    return id;
}
public void setId(String id) {
    this.id = id;
}
public String getName() {
    return name;
}
public void setName(String name) {
    this.name = name;
}
public int getAge() {
    return age;
}
public void setAge(int age) {
    this.age = age;
}
@Override
public String toString() {
    return "User{" +
            "id='" + id + '\'' +
            ", name='" + name + '\'' +
            ", age=" + age +
            '}';
}

}


   - 注意：update、delete、insert 都是改变表，只有 select 是获取表的内容，所以只有两种类型的执行语句，即 update 和 query；
- 案例 2 ：将查询的结果集封装成一个相应的 Dao 对象
```java
package com.cyt.jdbc;

import com.cyt.domain.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JdbcDemo3 {
    public static void main(String[] args) throws Exception{

        List<User> users = findAll();
        System.out.println(users);
    }
    public static List<User> findAll(){
        Connection conn = null;
        Statement stmt = null;
        ResultSet res2 = null;
        List<User> users = new ArrayList<>();
        try{
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test_jdbc","root","1234");
            stmt = conn.createStatement();
            String select_sql = "select * from user";
            res2 = stmt.executeQuery(select_sql);//返回值为查询的表结果集
            User user = null;
            while (res2.next()) {
                String id = res2.getString(1); //列号从 1 开始
                String name = res2.getString(2);
                int age = res2.getInt(3);
                user = new User();
                user.setId(id);
                user.setName(name);
                user.setAge(age);
                users.add(user);
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            if (res2 != null) { //防止报空指针异常
                try {
                    res2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) { //防止报空指针异常
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return users;
    }
}

1.3 存在的问题及解决思路

问题
- 【代码重复】不管执行什么 sql 语句，除了真正的执行过程和结果不一样，其他操作都是一样的；
- 【显示转换，耦合度过高】查询的结果集 ResultSet 转化成 domain 对象
  - 如案例 2 所示，不仅需要创建特定的 domain 对象，而且要遍历 domain 类的所有字段来与查询的结果匹配，并进行赋值；
  - 对不同的 domain 对象，都要通过 new 创建对象，且要对匹配字段 set，这需要事先知道该类的构造；
如何抽象呢
- 将“连接通道的开闭”独立出来，抽象成一个工具类；
- 利用反射机制创建 domain 对象，可自动获取有关这个类的所有信息，这块主要用的是字段；
  2. Spring 中的两个改进设计
  2.1 DataSource 与连接池
  2.1.1 改进点
连接工具类 JdbcUtil
- 自定义一个 JdbcUtil，其中 static 主要是为获取连接必须的三个参数 url、name 和 password 赋值； ```java package com.cyt.util;

import java.io.FileNotFoundException; import java.io.FileReader; import java.io.IOException; import java.net.URL; import java.sql.*; import java.util.Properties;

public class JdbcUtil { private static String url; private static String userName; private static String password;

static {
    try {
        // 通过 ClassLoader 获取 src 下的 properties 文件
        Properties properties = new Properties();
        URL resource = JdbcUtil.class.getClassLoader().getResource("jdbcUtil.properties");
        properties.load(new FileReader(resource.getPath()));
        url = properties.getProperty("url");
        userName = properties.getProperty("user");
        password = properties.getProperty("password");
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

public static Connection getConnection(){
    Connection conn = null;
    try {
        conn = DriverManager.getConnection(url, userName, password);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return conn;
}

public static void close(ResultSet rst, Statement stmt, Connection conn){
    if (rst != null) {
        try {
            rst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (stmt != null) {
        try {
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

}


- 连接池 ConnectionPool
   - ConnectionPool 其实就是一个容器（集合），存放数据库连接的容器；
      - 当系统初始化以后，容器被创建，容器会申请一些连接对象，当用户来访问数据库时，会从容器中获取连接对象，用户访问完之后，会将连接返还给容器；
      -  作用：节约资源、用户访问高效；
      - 可从 javax.sql 包下的 DataSource 中获取；
      - 由数据库厂商实现，如 C3P0、Druid 等技术；

![](https://cdn.nlark.com/yuque/0/2020/jpeg/611598/1583396177376-185b5c56-14ac-402d-a24a-e640c733abee.jpeg#align=left&display=inline&height=231&originHeight=411&originWidth=579&size=0&status=done&style=none&width=326)

   - 自定义一个 C3P0 数据库连接池工具类
```java
package com.cyt.util;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

/**
 * C3P0 数据库连接池工具类
 */
public class JdbcPoolUtil {

    private static DataSource ds;

    static {
        ds = new ComboPooledDataSource("mysql");
    }

    public static DataSource getDs() {
        return ds;
    }

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    public static void close(Statement stmt, Connection conn){
        close(null, stmt, conn);
    }

    public static void close(ResultSet rst, Statement stmt, Connection conn){
        if (rst != null) {
            try {
                rst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

2.1.2 使用

C3P0 数据库连接池
- 导入三个 jar 包：c3p0-0.9.5.5.jar 、mchange-commons-java-0.2.19.jar、mysql-connector-java-5.1.6.jar；
- 定义配置文件 c3p0.properties 或者 c3p0-config.xml，直接将文件放在 src 目录下；

package com.cyt.jdbc;

import com.cyt.util.JdbcPoolUtil;
import java.sql.*;

/**
 * 测试 CP30 数据库连接池
 */
public class JdbcDemo7 {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try{
            conn = JdbcPoolUtil.getConnection();
            stmt = conn.createStatement();
            String insert_sql = "insert into user values('202001', 'cyt', 18)";
            int res1 = stmt.executeUpdate(insert_sql); //返回值为影响的行数

            System.out.println(res1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcPoolUtil.close(stmt, conn);
        }
    }
}

2.1.3 DataSource 与 ConnectionPool 的关系

2.2 JdbcTemplate

Spring 框架对 JDBC 的简单封装，提供了一个 JDBCTemplate 对象来简化 JDBC 的开发；

2.2.1 改进点
从 DataSource 中创建执行 SQL 的对象 JdbcTemplate
- JdbcTemplate 与 DataSource 的关系
利用 BeanRowMapper 实现查询结果集到 Dao 对象的转换，主要利用反射机制创建对象并获取其 Fields；
- 先根据反射结果缓存所有的 set 方法，并保存在了mappedFields 中，mapperFields 是一个HashMap（用来匹配 Java 对象的属性和 MySQL 表的字段名，这样返回结果为多个对象时就不用再次确定匹配的列）；

将 ResultSet 映射为 List

2.2.2 使用

package com.cyt.jdbcTemplate;

import com.cyt.domain.AccountDao;
import com.cyt.util.JdbcPoolUtil;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;
import java.util.Map;

/**
 *  Spirng JDBCTemplate
 */
public class JdbcTemplateDemo1 {
    //1. 获取连接池对象，并创建 JdbcTemplate 对象
    JdbcTemplate template = new JdbcTemplate(JdbcPoolUtil.getDs());
    //执行 DML 语句（insert、update、delete）
    public void testDML(){
        //2. 调用方法
        String update_sql = "update account set balance = 1000 where id = '1'";
        int count1 = template.update(update_sql);
        int count2 = template.update("insert into account values('3','zgx',2000)");
        template.update("insert into account values('4','zgx',2000)");
        int count3 = template.update("delete from account where id = '3'");
        System.out.println(count1);
        System.out.println(count2);
        System.out.println(count3);
    }

    //将结果集封装在 map 集合，将列名为 key，值为 value，封装成一个 map, 注意：这个方法查询的结果集长度只能是 1；
    public void testDQL_1(){
        Map<String, Object> map = template.queryForMap("select * from account where id = '4'");
        System.out.println(map);
    }

    //将结果集封装为 list 集合,注意：将每一条记录封装为一个 map 集合，再将 map 集合装载到一个 list 集合中；
    public void testDQL_2(){
        List<Map<String, Object>> list = template.queryForList("select * from account where name = 'zgx'");
        for (Map map: list) {
            System.out.println(map);
        }
    }

    //query()：查询结果将结果集封装为 JavaBean 对象； 
    public void testDQL_3(){
        String sql = "select * from account";
        List<AccountDao> list = template.query(sql, new BeanPropertyRowMapper<AccountDao>(AccountDao.class));
        for (AccountDao accountDao : list) {
            System.out.println(accountDao);
        }
    }

    //queryForObject：查询结果，将结果封装为对象,一般用于聚合函数的查询；
    public void testDQL_4(){
        Integer num = template.queryForObject("select count(id) from account", Integer.class);
        System.out.println(num);
    }
}

3. JDBC 控制事务

3.1 基本概念

事务：一个包含多个步骤的业务操作，如果这个业务操作被事务管理，则这多个步骤的成功或失败同步；

使用 Connection 对象来管理事务

开启事务：setAutoCommit(boolean autoCommit) 设置参数为 flase 则开启事务；
- 在 sql 语句之间开启事务；
提交事务：commit() 在执行之后提交事务；

回滚事务：rollback() 在执行过程出现异常时回滚；

3.2 使用

try{
1. 在 sql 语句之间开启事务：conn.setAutoCommit(false);
2. 之间是执行 SQL 语句的过程
3. 在执行之后提交事务：conn.commit();
} catch(Exception e){
4. 在执行过程出现异常时回滚
       if (conn != null) {
           try {
               conn.rollback();
           } catch (SQLException ex) {
               ex.printStackTrace();
           }
       }
}

4. 总结与思考

思考
- BeanRowMapper 中，Dao 对象中应该定义的是对应表的所有字段，但查询获得的可能只是部分字段，所以在做 rowMapper 映射时，根据字段名匹配，没有匹配上的字段，其取值就是默认初始的值；
总结
- JDBC 三大步骤：连接数据库、执行 SQL 语句、获得结果;
  参考
  在cmd命令行窗口使用MySQL命令
   SQL/MYSQL在CMD命令操作符中创建数据库、表单并插入数据查看
   mysql-connector-java各种版本下载地址
   mysql官网下载驱动包
   jdk1.8 在线帮助文档
   执行 executeQuery 返回的结果 ResutlSet
  maven_repository下载各种依赖包
   【Spring】利用spring的JdbcTemplate查询返回结果映射到自定义类型

java 学习记录

JDBC 学习

1. 简单认识 JDBC

1.1 一个简单的使用案例

1.2 IDEA 实现简单的 CURD

1.3 存在的问题及解决思路

2. Spring 中的两个改进设计

2.1 DataSource 与连接池

2.1.1 改进点