JdbcTemplate教程：JdbcTemplate连接MySQL数据库从入门到进阶

一、入门

了解JDBC概念：

• JDBC(Java DataBase Connectivity) ：Java数据库连接技术：具体讲就是通过Java连接数据库，并且可以通过发送SQL指令，实现对表中数据执行增、删、改、查等操作的技术。

原生JDBC编程（了解一下）：

        查询t_user表（包括字段：user_id，username，password）中的数据并展示的实例如下：

package com.csx.test;

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

public class JdbcTest{
    public static void main(String[] args) throws Exception {
        //1 加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2 获取连接
        String username = "root";//用户名
        String password = "root";//密码
        /*
            url参数用来确定连接的数据库信息： 数据库机器ip 端口号port 数据库名db_name 连接的参数(比如编解码集、时区...)
            url格式：jdbc:mysql://ip:port/db_name?k=v参数
        */
        String url = "jdbc:mysql://localhost:3306/jdbcdemo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai";
        Connection conn = DriverManager.getConnection(url, username, password);//通过DriverManager管理驱动获取连接

        //3 准备发送SQL的工具
        String sql = "select * from t_user";
        PreparedStatement pstm = conn.prepareStatement(sql);

        //4 发送执行SQL
        ResultSet rs = pstm.executeQuery();

        //5 处理结果集（如果存在）
        while(rs.next()){
            /*
                rs.getXxx(列顺序从1开始) 或者 rs.getXxx("列名") 获取指定列的数据，Xxx为数据类型
             */
            int userId = rs.getInt("user_id");
            String userName = rs.getString("username");
            String userPassword =rs.getString("password");
            System.out.println("用户Id:"+userId+" 用户名:"+userName+" 用户密码:"+password);
        }
        //6 释放资源(反序关闭：后出现先关闭)
        rs.close();
        pstm.close();
        conn.close();
    }
}

总结：原生的jdbc连接并操作数据库的步骤是比较繁琐的。

•  JdbcTemplate

JdbcTemplate是Spring对JDBC的封装，将数据库连接和SQL操作的相关资源进行了封装，使用时无需关注资源，只需要关注SQL相关的处理即可，目的是使JDBC更加易于使用。

二、JdbcTemplate入门案例

开发场景前置准备：（使用Navicat图形用户界面工具）

• 准备数据库表和相关数据

  • 新建连接

    • 

• 新建查询，创建数据库和表结构并插入数据（全选这些sql然后执行）

  • -- 创建名为jdbcdemo的数据库
    create database jdbcdemo;
    -- 选择数据库
    use jdbcdemo;
    -- 在当前数据库下创建表结构
    create table t_user(
    user_id int primary key auto_increment,
    username varchar(20) unique,
    password varchar(20)
    );
    -- 插入一些数据（user_id自增，无需插入数据）
    insert into t_user(username,password) value('张三','123456');
    insert into t_user(username,password) value('李四','admin');
    insert into t_user(username,password) value('王五','root');

• 接着在idea中新建一个java项目
  • jdk版本不低于jdk1.8
  • 
• 导入相关的依赖java包，并添加为库
  • 最上方的两个jar包时junit单元测试时使用的，目前不需要导入
  • 在项目结构中创建lib目录，并将jar包复制到lib目录下
  • 
  • 右键lib目录，点击添加为库
    • 

阿里云盘jar包资源：阿里云盘分享

百度云盘jar包资源：百度云盘分享

场景需求：使用JdbcTemplate技术向t_user表中插入一条数据

package com.csx.test;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

public class JdbcDemo1 {
    public static void main(String[] args) {
        // 1. 创建数据库连接池（帮助我们获取数据库连接）
        //连接路径: 连接途径:数据库类型://ip:port/数据库名?k=v&k=v(编码集&时区)
        String url = "jdbc:mysql://localhost:3306/jdbcdemo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai";
        String username = "root";
        String password = "root";
        //获取数据库连接池对象
        DataSource dataSource=new DriverManagerDataSource(url,username,password);
        // 2. 创建JDBCTemplate对象 （创建操作数据库的工具）
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        // 3. 书写SQL
        String sql = "insert into t_user(username,password) values('赵六','123456')";
        // 4. 发送执行SQL
        int n = jdbcTemplate.update(sql);
        // 5. 处理结果
        if (n == 1) {
            //如果受影响行数为1，证明操作成功
            System.out.println("添加成功！");
        } else {
            System.out.println("添加失败");
        }
    }
}

三、JdbcTemplate进阶

1.结果集处理

场景：查询t_user表中的数据，并逐条地将每个字段输出在控制台。

• 定义一个User类：

package com.csx.entity;

public class User {
    private Integer userId;
    private String username;
    private String password;
    public User(){}

    public User(Integer userId, String username, String password) {
        this.userId = userId;
        this.username = username;
        this.password = password;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "userId=" + userId +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

• 查询并处理结果

package com.csx.test;

import com.csx.entity.User;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class JdbcDemo2 {
    public static void main(String[] args) {
        //创建数据库连接池
        String url = "jdbc:mysql://localhost:3306/jdbcdemo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai";
        String username = "root";
        String password = "root";
        DataSource dataSource = new DriverManagerDataSource(url, username, password);
        //创建JDBCTemplate
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        //书写SQL
        String sql = "select * from t_user";
        //发送SQL处理结果
        List<User> list=jdbcTemplate.query(sql, new RowMapper<User>() {
            /**
             * RowMapper:更改当前操作的行号-->按照我们书写的代码处理结果集指定行的内容-->将返回的对象添加进集合
             *
             * @param rs 结果集 存放着所有的查询数据
             * @param i 当前被处理的行号  RowMapper执行时自动使用，无需手动处理
             * @return 本行数据对应的对象
             * @throws SQLException
             */
            @Override
            public User mapRow(ResultSet rs, int i) throws SQLException {
                //获取字段值
              int id=rs.getInt("user_id");
              String username=rs.getString("username");
              String password=rs.getString("password");
              User user =new User(id,username,password);
              return  user;
            }
        });
        //查看集合数据
        System.out.println("查询结果为：");
        list.forEach(user -> System.out.println(user));
    }
}

结果集说明：

结果集（ResultSet）：表示查询结果，就是将从MySQL查询到的数据保存在java程序中；并且ResultSet查询结果的列名，不是原表列名，而是select查询结果列名（此时如果设置了字段别名，则列名为字段别名）

 RowMapper接口

功能： 用来把SQL查询结果集ResultSet，映射（转化）为Java对象，便于java程序的后续数据管理和处理。

语法结构：

1. 定义一个类

class Java类{
  1. 类名与对应表名相关（例如：表名为 t_user 类名为 User）
  2. 一个属性对应一个字段
  3. 属性类型必须与字段类型一致 
  4. 属性名必须与字段名相关
    - 字段名只由一部分组成，属性名保持一致  username--username
    - 字段名由多个部分组成，属性名采用小驼峰命名  user_id--userId
  5. 进行正常封装
  6. 提供有参无参的构造
  7. 数据类型声明为引用类型 （属性类型如果是基本类型应为对应的包装类型）
}

2.映射查询结果

List<Java类> personList = jdbcTemplate.query("查询的SQL语句", new RowMapper<Java类>() {
    /**
      功能：作为JdbcTemplate，将查询结果的每个字段映射为一个ORM对象的属性的实现。
      执行时机：查询结果的每条数据的映射，都会调用一次mapRow方法。
      参数：
        ResultSet：结果集，封装了MySQL返回的查询结果。可以看作是之前MySQL客户端返回的行结果。
        rowNum：当前映射的行，在整体本次查询结果的行号。
    */
    @Override
    public Java类 mapRow(ResultSet rs, int rowNum) throws SQLException {
        //获取当前行数据
        rs.getXxxx("查询结果列名");
        rs.getString("");
        rs.getInt("");
        rs.getDouble("");
        // 将结果封装成Java对象，并返回。
        
    }
});

映射查询结果：实际上就是创建一个RowMapper接口的实现类对象作为实参传入query()方法中。

query()执行原理：发送SQL指令获取ResultSet结果集,再将结果集作为实参传入RowMappel
中的mapRow方法,反复调用mapRow()将返回值对象添加进List集合

mapRow(RS对象,int 行号)执行原理：通过传入结果集和行号的方式获取结果集中指定行的
数据,在方法内部获取到本行字段值,并借此创建对应的对象return返回

BeanPropertyRowMapper

 JdbcTemplate还提供了内置的BeanPropertyRowMapper，简化查询结果和实体类对象的转换RowMapper的操作。

 要求：实体类中的属性名要与MySQL中的字段名一一对应（必须要求）

package com.csx.test;

import com.csx.entity.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class JdbcDemo3 {
    public static void main(String[] args) {
        //创建数据库连接池
        String url = "jdbc:mysql://localhost:3306/jdbcdemo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai";
        String username = "root";
        String password = "root";
        DataSource dataSource = new DriverManagerDataSource(url, username, password);
        //创建JDBCTemplate
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        //书写SQL
        String sql = "select * from t_user";
        //发送SQL处理结果
        List<User> list=jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User.class));

        System.out.println("查询结果为：");
        list.forEach(user -> System.out.println(user));
    }
}

2.SQL动态参数绑定

        在实际开发中，向数据库添加一条数据，是由用户输入决定的（例如前端使用表单收集用户输入，再将数据传入后端），以上书写SQL是直接‘写死’在语法中，这实际上是不合理的。

在Java程序中完成参数的动态绑定

 以上面的查询为例：

 字符串拼接

JDBC中的SQL语句本质上是一个String字符串。可以通过把参数和SQL进行String拼接。

//书写SQL
//这里的userName模拟从用户接收的数据
String userName="张三";
String sql = "select * from t_user where username='"+userName+"'";
//发送SQL处理结果
List<User> list=jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User.class));

        注意：单引号不属于特殊字符，这里拼接的参数为字符串类型时，必须使用单引号包裹起来，目的是为了符合SQL的语法要求

?占位符

使用?标记参数的位置，然后再发送SQL时将参数动态传入

//书写SQL
String sql = "select * from t_user where username=?";
//发送SQL处理结果
List<User> list=jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User.class),"张三");

 注意：参数绑定的参数位置和顺序必须和？占位符保持一致。

1. 增删改DML的方法参数绑定
update(String sql, @Nullable Object... args);

2. DQL查询语句的方法参数绑定
query(String sql, RowMapper<T> rowMapper, @Nullable Object... args);

SQL注入问题分析

  通过输入特殊的参数拼接SQL语句，进而破坏原有的SQL语句的含义，然后非法获取数据库数据或者进行非法数据库操作，是一种常见的web安全漏洞。

 依旧以查询为例：

        当我们使用字符串拼接传参时：

         此时，在sql字符串后面拼接了一个or条件，无论此时的userName的数据是否是数据库中存在的数据，都能从查询成功。（此时发生了安全问题，SQL注入攻击成功）

        当我们使用?占位符传参时：

        此时，发生了一个预编译的过程，SQL的语法结构被固定了，传入的参数的个数，顺序和类型都被固定了，一旦出现参数不正确或者是缺少了参数都会导致查询不到指定的数据

总结：

四、数据库连接池

   原生JDBC存在的问题：

1.每次JDBC访问MySQL，都需要一个Connection对象。

2.一个Connection对象创建，需要经历（开辟内存空间、初始化数据、建立java和MySQL之间的io连接，最后完成Connection的创建），这个过程会消耗CPU资源和JVM内存资源。

3.如果使用完Connection，就销毁掉，会让Connection对象创建消耗的资源白白浪费掉。

  JdbcTemplate解决思路：

1. 事先创建好一个管理器容器，内部准备好一定数量的连接池。(只做一次)
2. 当Java访问数据库需要用连接的时候， 直接从连接池中获取一个conn对象。(避免了创建conn过程消耗的时间，效率高。)
3. 使用完毕conn之后，将conn还回连接池。(以备重复利用)

  连接池：

对一定数量的Connection对象，进行池化管理，可以重复利用connection对象，节约CPU和JVM内存资源。

  编码：

  创建，并配置DataSource
  String url = "jdbc:mysql://localhost:3306/jdbcdemo?useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai";
  String username = "root";
  String password = "root";
  DataSource dataSource = new DriverManagerDataSource(url,username,password);

总结：以上是关于jdbcTemplate的简单应用，本质上jdbcTemplate是对原生JDBC的一个封装，使得我们不必自己创建数据库连接和开启关闭相关资源，并且简化原生JDBC对结果集的繁琐处理，使用jdbcTemplate封装的方法，可以轻松获取数据库中的数据并进行相关操作。