jdbc&数据库连接池&jdbcTemplate教程

冷不防 2023-09-28 17:49 18阅读 0赞

## JDBC ##

#### 文章目录 ####

*  JDBC
 *   *  1.概念
     *  2.本质
     *  3.快速入门
     *   *  1.创建项目
         *  2.导包
         *  3.创建数据库以及表等数据
         *  4.编写代码
     *  4.详解各个对象
     *   *  1.DriverManager：驱动管理对象
         *  2.Connection：数据库连接对象
         *  3.Statement：执行sql的对象
         *  4.ResultSet：结果集对象,封装查询结果
         *  5.PreparedStatement：执行sql的对象
     *  5.JDBC工具类
     *   *  1.编写
         *  2.练习
     *  6.JDBC控制事务
     *   *  1.概念
         *  2.实现
 *  数据库连接池
 *   *   *  1.概念
         *  2.实现
         *  3.C3P0基本使用
         *  4.Druid基本使用
         *  5.Druid工具类
 *  JDBCTemplate（Spring JDBC）
 *   *   *  1.概述
         *  2.JDBCTemplate快速入门
         *  3.练习

### 1.概念 ###

Java DataBase Connectivity，也就是Java 数据库连接，Java语言操作数据库

### 2.本质 ###

其实是官方（sun公司<以及被Oracle公司收购>）定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动jar包，我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类。

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VHAIbSUd-1659253013361)(C:\\Users\\30666\\AppData\\Roaming\\Typora\\typora-user-images\\image-20220730161939709.png)\]][img-VHAIbSUd-1659253013361_C_Users_30666_AppData_Roaming_Typora_typora-user-images_image-20220730161939709.png]

### 3.快速入门 ###

步骤：

1.导入驱动jar包

2.注册驱动

3.获取数据库的连接对象 Connection

4.定义sql语句

5.获取执行sql语句的对象 Statement

6.执行sql语句，接收返回结果

7.处理结果

8.释放资源

#### 1.创建项目 ####

使用idea创建空项目，选择Project Structure–>Modules–>选择’+'号 New Module–>选择Maven–>选择Next–>输入项目名称之后选择Finish即可。（这里注意需要安装和配置maven依赖）

#### 2.导包 ####

`在pom.xml中导入MySql驱动`

<dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
        </dependency>
    </dependencies>

#### 3.创建数据库以及表等数据 ####

> 可能用到的数据库

/*
     Navicat Premium Data Transfer
    
     Source Server         : localhost
     Source Server Type    : MySQL
     Source Server Version : 80027
     Source Host           : localhost:3306
     Source Schema         : db1
    
     Target Server Type    : MySQL
     Target Server Version : 80027
     File Encoding         : 65001
    
     Date: 30/07/2022 17:42:33
    */
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for account
    -- ----------------------------
    DROP TABLE IF EXISTS `account`;
    CREATE TABLE `account`  (
      `id` int NOT NULL AUTO_INCREMENT,
      `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `balance` double NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of account
    -- ----------------------------
    INSERT INTO `account` VALUES (1, 'zhangsan', 500);
    INSERT INTO `account` VALUES (2, 'lisi', 1000);
    
    
    -- ----------------------------
    -- Table structure for dept
    -- ----------------------------
    DROP TABLE IF EXISTS `dept`;
    CREATE TABLE `dept`  (
      `id` int NOT NULL,
      `dname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `loc` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of dept
    -- ----------------------------
    INSERT INTO `dept` VALUES (10, '研发部', '北京');
    INSERT INTO `dept` VALUES (20, '学工部', '上海');
    INSERT INTO `dept` VALUES (30, '销售部', '广州');
    INSERT INTO `dept` VALUES (40, '财务部', '深圳');
    
    -- ----------------------------
    -- Table structure for emp
    -- ----------------------------
    DROP TABLE IF EXISTS `emp`;
    CREATE TABLE `emp`  (
      `id` int NOT NULL,
      `ename` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `job_id` int NULL DEFAULT NULL,
      `mgr` int NULL DEFAULT NULL,
      `joindate` timestamp NULL DEFAULT NULL,
      `salary` decimal(7, 2) NULL DEFAULT NULL,
      `bonus` decimal(7, 2) NULL DEFAULT NULL,
      `dept_id` int NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      INDEX `job_id`(`job_id`) USING BTREE,
      INDEX `dept_id`(`dept_id`) USING BTREE,
      CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
      CONSTRAINT `emp_ibfk_2` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of emp
    -- ----------------------------
    INSERT INTO `emp` VALUES (1001, '孙悟空', 4, 1004, '2000-12-17 00:00:00', 8000.00, NULL, 20);
    INSERT INTO `emp` VALUES (1002, '卢俊义', 3, 1006, '2001-02-20 00:00:00', 16000.00, 3000.00, 30);
    INSERT INTO `emp` VALUES (1003, '林冲', 3, 1006, '2001-02-22 00:00:00', 12500.00, 5000.00, 30);
    INSERT INTO `emp` VALUES (1004, '唐僧', 2, 1009, '2001-04-02 00:00:00', 29750.00, NULL, 20);
    INSERT INTO `emp` VALUES (1005, '李逵', 4, 1006, '2001-09-28 00:00:00', 12500.00, 14000.00, 30);
    INSERT INTO `emp` VALUES (1006, '宋江', 2, 1009, '2001-05-01 00:00:00', 28500.00, NULL, 30);
    INSERT INTO `emp` VALUES (1007, '刘备', 2, 1009, '2001-09-01 00:00:00', 24500.00, NULL, 10);
    INSERT INTO `emp` VALUES (1008, '猪八戒', 4, 1004, '2007-04-19 00:00:00', 30000.00, NULL, 20);
    INSERT INTO `emp` VALUES (1009, '罗贯中', 1, NULL, '2001-11-17 00:00:00', 50000.00, NULL, 10);
    INSERT INTO `emp` VALUES (1010, '吴用', 3, 1006, '2001-09-08 00:00:00', 15000.00, 0.00, 30);
    INSERT INTO `emp` VALUES (1011, '沙僧', 4, 1004, '2007-05-23 00:00:00', 11000.00, NULL, 20);
    INSERT INTO `emp` VALUES (1012, '李逵', 4, 1006, '2001-12-03 00:00:00', 9500.00, NULL, 30);
    INSERT INTO `emp` VALUES (1013, '小白龙', 4, 1004, '2001-12-03 00:00:00', 30000.00, NULL, 20);
    INSERT INTO `emp` VALUES (1014, '关羽', 4, 1007, '2002-01-23 00:00:00', 13000.00, NULL, 10);
    
    -- ----------------------------
    -- Table structure for job
    -- ----------------------------
    DROP TABLE IF EXISTS `job`;
    CREATE TABLE `job`  (
      `id` int NOT NULL,
      `jname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      `description` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of job
    -- ----------------------------
    INSERT INTO `job` VALUES (1, '董事长', '管理整个公司,接单');
    INSERT INTO `job` VALUES (2, '经理', '管理部门员工');
    INSERT INTO `job` VALUES (3, '销售员', '向客人推销产品');
    INSERT INTO `job` VALUES (4, '文员', '使用办公软件');
    
    -- ----------------------------
    -- Table structure for salarygrade
    -- ----------------------------
    DROP TABLE IF EXISTS `salarygrade`;
    CREATE TABLE `salarygrade`  (
      `grade` int NOT NULL,
      `losalary` int NULL DEFAULT NULL,
      `hisalary` int NULL DEFAULT NULL,
      PRIMARY KEY (`grade`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Records of salarygrade
    -- ----------------------------
    INSERT INTO `salarygrade` VALUES (1, 7000, 12000);
    INSERT INTO `salarygrade` VALUES (2, 12000, 14000);
    INSERT INTO `salarygrade` VALUES (3, 14000, 20010);
    INSERT INTO `salarygrade` VALUES (4, 20010, 30010);
    INSERT INTO `salarygrade` VALUES (5, 30010, 99990);
    
    SET FOREIGN_KEY_CHECKS = 1;

#### 4.编写代码 ####

package com.kk.jdbc.ex01;
    
    import com.mysql.jdbc.Driver;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.Statement;
    
    /**
     * @author : k
     * @Date : 2022/7/30
     * @Desc : jdbc快速入门
     */
    public class Demo01 {
        
        public static void main(String[] args) throws Exception {
        
    
            //注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
    
            //获取数据库的连接对象
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "123456");
    
            //定于sql语句
            String sql = "update account set balance = 500 where id = 1";
    
            //获取执行sql的对象 Statement
            Statement stmt = conn.createStatement();
    
            //执行sql
            int i = stmt.executeUpdate(sql);
    
            //处理结果
            System.out.println(i);
    
            //释放资源
            stmt.close();
            conn.close();
        }
    }

### 4.详解各个对象 ###

#### 1.DriverManager：驱动管理对象 ####

功能：

`1.注册驱动：告诉程序该使用哪一个数据库驱动jar包`

static void registerDriver(Driver driver)：注册与给定的驱动程序 DriverManager

写代码使用：Class.forName(“com.mysql.cj.jdbc.Driver”);

通过查看源码发现在 com.mysql.cj.jdbc.Driver 类中存在静态代码块

static {
        
        try {
        
            java.sql.DriverManager.registerDriver(new Driver());
        } catch (SQLException E) {
        
            throw new RuntimeException("Can't register driver!");
        }
    }

`注意:mysql5之后的驱动jar包可以省略注册驱动步骤`

`2.获取数据库连接`

方法：static Connection getConnection(String url, String user, String password)

参数：

url：指定连接的路径 （jdbc:mysql://ip地址（域名）:端口号/数据库名称）

如：jdbc:mysql://localhost:3306/db1

细节：如果连接的是本地的服务器，并且mysql服务默认的端口是3306，则url可以简写为 jdbc:mysql:///数据库名称

user：数据库用户名

password：数据库密码

#### 2.Connection：数据库连接对象 ####

功能：

1.获取执行sql的对象

1.Statement createStatement()

2.PreparedStatement prepareStatement(String sql)

2.管理事务

1.开启事务：void setAutoCommit(boolean autoCommit)：调用该方法为false即开启事务

2.提交事务：void commit()

3.回滚事务：void rollback()

#### 3.Statement：执行sql的对象 ####

功能：

执行sql

1.boolean execute(String sql)：可以执行任意的sql

2.int executeUpdate(String sql)：执行DML（insert、update、delete）语句、DDL（create、 alter、drop 表和库的操作）语句。

`返回值：影响的行数,可以通过这个影响的行数判断DML语句是否执行成功，返回值>0则执行成功，`

`反之则失败`

3.ResultSet executeQuery(String sql)：执行DQL（select）语句

2.练习

`1.account表 添加一条记录`

package com.kk.jdbc.ex01;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    /**
     * @author : k
     * @Date : 2022/7/30
     * @Desc : account表 添加一条记录
     */
    public class Demo02 {
        
        public static void main(String[] args) {
        
            Connection conn = null;
            Statement stmt = null;
            try {
        
                //注册驱动
                Class.forName("com.mysql.cj.jdbc.Driver");
    
                //获取数据库的连接对象
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "123456");
    
                //定于sql语句
                String sql = "insert into account values(null,'王五',1000)";
    
                //获取 执行sql的对象 Statement
                stmt = conn.createStatement();
    
                //执行sql
                int i = stmt.executeUpdate(sql);
    
                //处理结果
                System.out.println(i);
                if (i > 0) {
        
                    System.out.println("添加成功");
                } else {
        
                    System.out.println("添加失败");
                }
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            } catch (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();
                    }
                }
    
    
            }
    
    
        }
    }

`2.account表 修改记录`

package com.kk.jdbc.ex01;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    /**
     * @author : k
     * @Date : 2022/7/30
     * @Desc : account表 修改记录
     */
    public class Demo03 {
        
        public static void main(String[] args) {
        
            Connection conn = null;
            Statement stmt = null;
            try {
        
                //注册驱动
                Class.forName("com.mysql.cj.jdbc.Driver");
    
                //获取数据库的连接对象
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "123456");
    
                //定于sql语句
                String sql = "update account set balance = 1500 where id = 3";
    
                //获取 执行sql的对象 Statement
                stmt = conn.createStatement();
    
                //执行sql
                int i = stmt.executeUpdate(sql);
    
                //处理结果
                System.out.println(i);
                if (i > 0) {
        
                    System.out.println("修改成功");
                } else {
        
                    System.out.println("修改失败");
                }
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            } catch (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();
                    }
                }
    
    
            }
    
    
        }
    }

`3.account表 删除一条记录`

package com.kk.jdbc.ex01;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    /**
     * @author : k
     * @Date : 2022/7/30
     * @Desc : account表 删除一条记录
     */
    public class Demo04 {
        
        public static void main(String[] args) {
        
            Connection conn = null;
            Statement stmt = null;
            try {
        
                //注册驱动
                Class.forName("com.mysql.cj.jdbc.Driver");
    
                //获取数据库的连接对象
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "123456");
    
                //定于sql语句
                String sql = "delete from account where id = 3";
    
                //获取 执行sql的对象 Statement
                stmt = conn.createStatement();
    
                //执行sql
                int i = stmt.executeUpdate(sql);
    
                //处理结果
                System.out.println(i);
                if (i > 0) {
        
                    System.out.println("删除成功");
                } else {
        
                    System.out.println("删除失败");
                }
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            } catch (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();
                    }
                }
    
    
            }
    
    
        }
    }

#### 4.ResultSet：结果集对象,封装查询结果 ####

boolean next()：游标向下移动一行，判断当前行是否是最后一行末尾（是否有数据），如果是则false，如果不是则 true

getXxx(参数)：获取数据

Xxx代表数据类型，如 int getInt()、String getString()

参数：

int：代表列的编号，从1开始

String：代表列的名称

package com.kk.jdbc.ex02;
    
    import java.sql.*;
    
    /**
     * @author : k
     * @Date : 2022/7/30
     * @Desc : ResultSet：结果集对象,封装查询结果
     */
    public class Demo01 {
        
        public static void main(String[] args) {
        
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            try {
        
                //注册驱动
                Class.forName("com.mysql.cj.jdbc.Driver");
    
                //获取数据库的连接对象
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "123456");
    
                //定于sql语句
                String sql = "select * from account";
    
                //获取 执行sql的对象 Statement
                stmt = conn.createStatement();
    
                //执行sql
                rs = stmt.executeQuery(sql);
    
                //处理结果
                //让游标向下移动一行
                rs.next();
                //获取数据
                int id = rs.getInt(1);
                String name = rs.getString("name");
                double balance = rs.getDouble(3);
    
                System.out.println(id + "---" + name + "---" + balance);
    
    
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            } catch (SQLException e) {
        
                e.printStackTrace();
            } finally {
        
                //释放资源
                //避免空指针异常
    
                if (rs != null) {
        
                    try {
        
                        rs.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();
                    }
                }
    
    
            }
    
    
        }
    }

`注意：以上方式只能获取第一条数据,因此需要改进`

`遍历结果集`

package com.kk.jdbc.ex02;
    
    import java.sql.*;
    
    /**
     * @author : k
     * @Date : 2022/7/30
     * @Desc : ResultSet：结果集对象,封装查询结果
     */
    public class Demo02 {
        
        public static void main(String[] args) {
        
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            try {
        
                //注册驱动
                Class.forName("com.mysql.cj.jdbc.Driver");
    
                //获取数据库的连接对象
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "123456");
    
                //定于sql语句
                String sql = "select * from account";
    
                //获取 执行sql的对象 Statement
                stmt = conn.createStatement();
    
                //执行sql
                rs = stmt.executeQuery(sql);
    
                //处理结果
                //获取数据
                while(rs.next()){
        
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    double balance = rs.getDouble("balance");
                    System.out.println(id + "---" + name + "---" + balance);
                }
    
    
    
    
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            } catch (SQLException e) {
        
                e.printStackTrace();
            } finally {
        
                //释放资源
                //避免空指针异常
    
                if (rs != null) {
        
                    try {
        
                        rs.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();
                    }
                }
    
    
            }
    
    
        }
    }

练习：

定义一个方法，查询emp表的数据将其封装为对象，然后装载集合返回。

1.定义Emp类

2.定义方法 public List findAll()\{\}

3.实现方法 select \* from emp;

代码测试：

`Emp对象`

package com.kk.jdbc.domain;
    
    import java.util.Date;
    
    /**
     * @author : k
     * @Date : 2022/7/30
     * @Desc :
     */
    public class Emp {
        
    
        private int id;
        private String ename;
        private int job_id;
        private int mgr;
        private Date joindate;
        private double salary;
        private double bonus;
        private int dept_id;
    
        public Emp() {
        
        }
    
        public Emp(int id, String ename, int job_id, int mgr, Date joindate, double salary, double bonus, int dept_id) {
        
            this.id = id;
            this.ename = ename;
            this.job_id = job_id;
            this.mgr = mgr;
            this.joindate = joindate;
            this.salary = salary;
            this.bonus = bonus;
            this.dept_id = dept_id;
        }
    
        public int getId() {
        
            return id;
        }
    
        public void setId(int id) {
        
            this.id = id;
        }
    
        public String getEname() {
        
            return ename;
        }
    
        public void setEname(String ename) {
        
            this.ename = ename;
        }
    
        public int getJob_id() {
        
            return job_id;
        }
    
        public void setJob_id(int job_id) {
        
            this.job_id = job_id;
        }
    
        public int getMgr() {
        
            return mgr;
        }
    
        public void setMgr(int mgr) {
        
            this.mgr = mgr;
        }
    
        public Date getJoindate() {
        
            return joindate;
        }
    
        public void setJoindate(Date joindate) {
        
            this.joindate = joindate;
        }
    
        public double getSalary() {
        
            return salary;
        }
    
        public void setSalary(double salary) {
        
            this.salary = salary;
        }
    
        public double getBonus() {
        
            return bonus;
        }
    
        public void setBonus(double bonus) {
        
            this.bonus = bonus;
        }
    
        public int getDept_id() {
        
            return dept_id;
        }
    
        public void setDept_id(int dept_id) {
        
            this.dept_id = dept_id;
        }
    
        @Override
        public String toString() {
        
            return "Emp{" +
                    "id=" + id +
                    ", ename='" + ename + '\'' +
                    ", job_id=" + job_id +
                    ", mgr=" + mgr +
                    ", joindate=" + joindate +
                    ", salary=" + salary +
                    ", bonus=" + bonus +
                    ", dept_id=" + dept_id +
                    '}';
        }
    }

`运行方法`

package com.kk.jdbc.ex02;
    
    import com.kk.jdbc.domain.Emp;
    
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * @author : k
     * @Date : 2022/7/30
     * @Desc : 定义一个方法，查询emp表的数据将其封装为对象，然后装载集合返回。
     */
    public class Demo03 {
        
    
        public static void main(String[] args) {
        
            List<Emp> list = new Demo03().findAll();
            for (Emp emp : list) {
        
                System.out.println(emp);
            }
        }
    
    
    
    
        /**
         * 查询所有emp对象
         *
         * @return
         */
        public List<Emp> findAll() {
        
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            List<Emp> list = null;
            try {
        
                //注册驱动
                Class.forName("com.mysql.cj.jdbc.Driver");
    
                //获取连接
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "123456");
    
                //定于sql语句
                String sql = "select * from emp";
    
                //获取执行sql的对象
                stmt = conn.createStatement();
    
                //执行sql
                rs = stmt.executeQuery(sql);
    
                Emp emp = null;
                list = new ArrayList<>();
                //遍历结果集，封装对象装载集合
                while (rs.next()) {
        
                    int id = rs.getInt("id");
                    String ename = rs.getString("ename");
                    int job_id = rs.getInt("job_id");
                    int mgr = rs.getInt("mgr");
                    Date joindate = rs.getDate("joindate");
                    double salary = rs.getDouble("salary");
                    double bonus = rs.getDouble("bonus");
                    int dept_id = rs.getInt("dept_id");
    
                    //创建emp对象
                    emp = new Emp();
                    emp.setId(id);
                    emp.setEname(ename);
                    emp.setJob_id(job_id);
                    emp.setMgr(mgr);
                    emp.setJoindate(joindate);
                    emp.setSalary(salary);
                    emp.setBonus(bonus);
                    emp.setDept_id(dept_id);
    
    
                    //装载集合
                    list.add(emp);
                }
    
    
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
            finally {
        
                //释放资源
                //避免空指针异常
    
                if (rs != null) {
        
                    try {
        
                        rs.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 list;
        }
    }

`运行结果`

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SpTsAMVy-1659253013363)(C:\\Users\\30666\\AppData\\Roaming\\Typora\\typora-user-images\\image-20220730180335986.png)\]][img-SpTsAMVy-1659253013363_C_Users_30666_AppData_Roaming_Typora_typora-user-images_image-20220730180335986.png]

#### 5.PreparedStatement：执行sql的对象 ####

1.SQL注入问题：在拼接sql时，有一些sql的关键字参与字符串的拼接。会造成安全性问题。

输入用户名随便，密码：a’ or ‘a’ = 'a

2.解决SQL注入问题：使用PreparedStatement对象来解决

3.预编译SQL：参数使用 ？ 作为占位符

4.步骤：

1.导入驱动jar包

2.注册驱动

3.获取数据库的连接对象 Connection

4.定义sql语句

sql的参数使用 ? 作为占位符

如：select \* from user where username = ? and password = ?；

5.获取执行sql语句的对象 PreparedStatement Connection.PreparedStatement(String sql)

6.给 ? 赋值

方法：setXxx(参数1，参数2)

参数1：? 的位置编码 从1开始

参数2：? 的值

7.执行sql语句，接收返回结果，不需要传递sql语句

8.处理结果

9.释放资源

5.注意：后期都会使用PreparedStatement来完成增删改查的所有操作

1.可以防止sql注入

2.效率更高

> 代码测试

package com.kk.jdbc.ex03;
    
    import com.kk.jdbc.util.JDBCUtils;
    
    import java.sql.*;
    import java.util.Scanner;
    
    /**
     * @author : k
     * @Date : 2022/7/30
     * @Desc : 练习  改造  使用PreparedStatement
     * 需求：
     * 1.通过键盘录入用户名和密码
     * 2.判断用户是否登录成功
     */
    public class Demo06 {
        
    
    
        public static void main(String[] args) {
        
            //1.键盘录入用户名和密码
            Scanner sc = new Scanner(System.in);
            System.out.print("请输入用户名：");
            String username = sc.nextLine();
            System.out.print("请输入密码：");
            String password = sc.nextLine();
            //2.调用方法
            boolean flag = new Demo06().login(username, password);
            //3.判断结果输出
            if (flag) {
        
                System.out.println("登录成功");
            } else {
        
                System.out.println("用户名或密码错误");
            }
        }
    
    
        /**
         * 登录方法
         *
         * @param username
         * @param password
         * @return
         */
        public boolean login(String username, String password) {
        
            if (username == null || password == null) {
        
                return false;
            }
    
            //连接数据库判断是否登录成功
            Connection conn = null;
            PreparedStatement pstm = null;
            ResultSet rs = null;
    
            try {
        
                //获取连接
                conn = JDBCUtils.getConnection();
    
                //定义sql
                String sql = "select * from user where username = ? and password = ? ";
    
                //获取执行sql的对象
                pstm = conn.prepareStatement(sql);
    
                //给 ? 赋值
                pstm.setString(1, username);
                pstm.setString(2, password);
    
                //执行查询
                rs = pstm.executeQuery();
    
                //判断
                return rs.next();
            } catch (SQLException e) {
        
                e.printStackTrace();
            } finally {
        
                JDBCUtils.close(rs, pstm, conn);
            }
    
            return false;
        }
    
    }

### 5.JDBC工具类 ###

#### 1.编写 ####

`在resources目录下创建jdbc.properties配置文件`

driver=com.mysql.cj.jdbc.Driver
    url=jdbc:mysql://localhost:3306/db1
    username=root
    password=123456

`创建util包，在util包目录下创建JDBC工具类`

package com.kk.jdbc.util;
    
    import java.io.FileReader;
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.*;
    import java.util.Properties;
    
    /**
     * @author : k
     * @Date : 2022/7/30
     * @Desc : JDBC工具类
     */
    public class JDBCUtils {
        
    
        private static String url;
        private static String username;
        private static String password;
        private static String driver;
    
        /**
         * 文件的读取，只需要读取一次即可拿到这些值 可以使用静态代码块
         */
        static{
        
            try {
        
                //读取资源文件，获取值
                //创建Properties集合类
                Properties pro = new Properties();
    
    
                //获取resources路径下的文件的方式 ---> ClassLoader 类加载器
                InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");;
    
                //加载文件
                pro.load(is);
    
                //获取数据
                url = pro.getProperty("url");
                username = pro.getProperty("username");
                password = pro.getProperty("password");
                driver = pro.getProperty("driver");
    
                //注册驱动
                Class.forName(driver);
    
    
            } catch (IOException e) {
        
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            }
        }
    
        /**
         * 获取连接
         *
         * @return 连接对象
         */
        public static Connection getConnection() throws SQLException {
        
            return DriverManager.getConnection(url,username,password);
        }
    
    
        /**
         * 释放资源
         * @param stmt
         * @param conn
         */
        public static void close(Statement stmt, Connection conn) {
        
            if (stmt != null) {
        
                try {
        
                    stmt.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
    
            if (conn != null) {
        
                try {
        
                    conn.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
        }
    
        /**
         * 释放资源
         * @param stmt
         * @param conn
         * @param rs
         */
        public static void close(ResultSet rs,Statement stmt, Connection conn) {
        
            if (stmt != null) {
        
                try {
        
                    stmt.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
    
            if (conn != null) {
        
                try {
        
                    conn.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
    
            if (rs != null) {
        
                try {
        
                    rs.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
        }
    
    
    }

`测试运行`

package com.kk.jdbc.ex02;
    
    import com.kk.jdbc.domain.Emp;
    import com.kk.jdbc.util.JDBCUtils;
    
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * @author : k
     * @Date : 2022/7/30
     * @Desc : 演示jdbc工具类
     */
    public class Demo04 {
        
    
        public static void main(String[] args) {
        
            List<Emp> list = new Demo04().findAll();
            for (Emp emp : list) {
        
                System.out.println(emp);
            }
        }
    
    
        /**
         * 查询所有emp对象
         *
         * @return
         */
        public List<Emp> findAll() {
        
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            List<Emp> list = null;
            try {
        
    
    
                conn = JDBCUtils.getConnection();
    
                //定于sql语句
                String sql = "select * from emp";
    
                //获取执行sql的对象
                stmt = conn.createStatement();
    
                //执行sql
                rs = stmt.executeQuery(sql);
    
                Emp emp = null;
                list = new ArrayList<>();
                //遍历结果集，封装对象装载集合
                while (rs.next()) {
        
                    int id = rs.getInt("id");
                    String ename = rs.getString("ename");
                    int job_id = rs.getInt("job_id");
                    int mgr = rs.getInt("mgr");
                    Date joindate = rs.getDate("joindate");
                    double salary = rs.getDouble("salary");
                    double bonus = rs.getDouble("bonus");
                    int dept_id = rs.getInt("dept_id");
    
                    //创建emp对象
                    emp = new Emp();
                    emp.setId(id);
                    emp.setEname(ename);
                    emp.setJob_id(job_id);
                    emp.setMgr(mgr);
                    emp.setJoindate(joindate);
                    emp.setSalary(salary);
                    emp.setBonus(bonus);
                    emp.setDept_id(dept_id);
    
    
                    //装载集合
                    list.add(emp);
                }
    
    
            } catch (SQLException e) {
        
                e.printStackTrace();
            } finally {
        
                JDBCUtils.close(rs, stmt, conn);
            }
    
    
            return list;
        }
    }

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PKRQtOc6-1659253013365)(C:\\Users\\30666\\AppData\\Roaming\\Typora\\typora-user-images\\image-20220730202807393.png)\]][img-PKRQtOc6-1659253013365_C_Users_30666_AppData_Roaming_Typora_typora-user-images_image-20220730202807393.png]

#### 2.练习 ####

需求：

1.通过键盘录入用户名和密码

2.判断用户是否登录成功

select \* from user where username = “” and password=“”

如果这个sql有查询结果则成功，否则失败

`在db1数据库中创建user表`

`代码测试`

package com.kk.jdbc.ex02;
    
    import com.kk.jdbc.util.JDBCUtils;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Scanner;
    
    /**
     * @author : k
     * @Date : 2022/7/30
     * @Desc : 练习
     * 需求：
     * 1.通过键盘录入用户名和密码
     * 2.判断用户是否登录成功
     */
    public class Demo05 {
        
    
    
        public static void main(String[] args) {
        
            //1.键盘录入用户名和密码
            Scanner sc = new Scanner(System.in);
            System.out.print("请输入用户名：");
            String username = sc.nextLine();
            System.out.print("请输入密码：");
            String password = sc.nextLine();
            //2.调用方法
            boolean flag = new Demo05().login(username, password);
            //3.判断结果输出
            if(flag){
        
                System.out.println("登录成功");
            }else{
        
                System.out.println("用户名或密码错误");
            }
        }
    
    
        /**
         * 登录方法
         *
         * @param username
         * @param password
         * @return
         */
        public boolean login(String username, String password) {
        
            if (username == null || password == null) {
        
                return false;
            }
    
            //连接数据库判断是否登录成功
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
    
            try {
        
                //获取连接
                conn = JDBCUtils.getConnection();
    
                //定义sql
                String sql = "select * from user where username = '" + username + "' and password = '" + password + "' ";
    
                //获取执行sql的对象
                stmt = conn.createStatement();
    
                //执行查询
                rs = stmt.executeQuery(sql);
    
                //判断
                /*
                if(rs.next()){
                    return true;
                }else {
                    return false;
                }*/
                return rs.next();
            } catch (SQLException e) {
        
                e.printStackTrace();
            } finally {
        
                JDBCUtils.close(rs,stmt,conn);
            }
    
            return false;
        }
    
    }

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6aZNZF6t-1659253013366)(C:\\Users\\30666\\AppData\\Roaming\\Typora\\typora-user-images\\image-20220730204822215.png)\]][img-6aZNZF6t-1659253013366_C_Users_30666_AppData_Roaming_Typora_typora-user-images_image-20220730204822215.png]

--------------------

> 注意：以上使用Statement会发送sql注入问题，因此应该使用PreparedStatement进行改造以解决sql注入问题

### 6.JDBC控制事务 ###

#### 1.概念 ####

1.事务：一个包含多个步骤的业务操作。如果这个业务操作被事务管理，则这么多个步骤要么同时成功，要么同时失败。

2.操作

1.开启事务

2.提交事务

3.回滚事务

3.使用Connection对象来管理事务

开启事务：setAutoCommit(boolean autoCommit):调用该方法设置参数为false，即事务开启

在执行sql之前提交事务

提交事务：commit()

当所有sql都执行完提交事务

回滚事务：rollback()

在catch中回滚事务

#### 2.实现 ####

`代码测试`

package com.kk.jdbc.ex04;
    
    import com.kk.jdbc.util.JDBCUtils;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    /**
     * @author : k
     * @Date : 2022/7/30
     * @Desc : JDBC控制事务
     */
    public class Demo {
        
        public static void main(String[] args) {
        
            Connection conn = null;
            PreparedStatement pstm1 = null;
            PreparedStatement pstm2 = null;
            ResultSet rs = null;
    
            try {
        
                //获取连接
                conn = JDBCUtils.getConnection();
    
                //开启事务
                conn.setAutoCommit(false);
    
                //定义sql
                String sql1 = "update account set balance = balance  - ? where id = ?";
                String sql2 = "update account set balance = balance  + ? where id = ?";
    
                //获取执行sql对象
                pstm1 = conn.prepareStatement(sql1);
                pstm2 = conn.prepareStatement(sql2);
    
                //设置参数
                pstm1.setDouble(1,500);
                pstm1.setInt(2,1);
    
                pstm2.setDouble(1,500);
                pstm2.setInt(2,2);
    
                //执行sql
                pstm1.executeUpdate();
                pstm2.executeUpdate();
    
    
                //提交事务
                conn.commit();
            } catch (Exception e) {
        
                //事务回滚
                try {
        
                    if (conn != null) {
        
                        conn.rollback();
                    }
                } catch (SQLException ex) {
        
                    ex.printStackTrace();
                }
                e.printStackTrace();
            }finally {
        
                JDBCUtils.close(pstm1,conn);
                JDBCUtils.close(pstm2,null);
            }
        }
    }

## 数据库连接池 ##

#### 1.概念 ####

其实就是一个容器（集合），存放数据库连接的容器

当系统初始化好后，容器被创建，容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象，用户 访问完之后会将连接对象归还给容器。

好处：

1.节约系统资源

2.用户访问高效

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tCT5Wb4O-1659253013367)(C:\\Users\\30666\\AppData\\Roaming\\Typora\\typora-user-images\\image-20220730213856214.png)\]][img-tCT5Wb4O-1659253013367_C_Users_30666_AppData_Roaming_Typora_typora-user-images_image-20220730213856214.png]

#### 2.实现 ####

1.标准接口：DataSource javax.sql包下的

方法：

获取连接：getConnection();

归还连接：Connection.close()；如果连接对象Connection是从连接池中获取的，那么调用 Connection.close()方法，则不会再关闭连接了，而是归还连接。

2.一般我们不去实现它，由数据库厂商来实现

1.C3P0：数据库连接池技术

2.Druid：数据库连接池技术、由阿里巴巴提供的

#### 3.C3P0基本使用 ####

步骤

1.导入jar包

2.定义配置文件：

名称：c3p0.properties or c3p0-config.xml

路径：直接将文件放在src目录下即可或者 直接放在resources目录下（推荐）

3.创建核心对象 数据库连接池对象 ComboPooledDataSource

4.获取连接：getConnection

> c3p0-config.xml

<c3p0-config>
        
        <default-config>
            
            <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
            <property name="jdbcUrl">jdbc:mysql://localhost:3306/db1</property>
            <property name="user">root</property>
            <property name="password">123456</property>
    
            
            
            <property name="initialPoolSize">5</property>
            
            <property name="maxPoolSize">10</property>
            
            <property name="checkoutTimeout">3000</property>
        </default-config>
    
        
        <named-config name="otherc3p0">
            
            <property name="driverClass">com.mysql.jdbc.Driver</property>
            <property name="jdbcUrl">jdbc:mysql://localhost:3306/db15</property>
            <property name="user">root</property>
            <property name="password">574839</property>
    
            
            <property name="initialPoolSize">5</property>
            <property name="maxPoolSize">8</property>
            <property name="checkoutTimeout">1000</property>
        </named-config>
    </c3p0-config>

> 代码测试

package com.kk.dataSource.c3p0;
    
    import com.mchange.v2.c3p0.ComboPooledDataSource;
    
    import javax.sql.DataSource;
    import java.sql.Connection;
    import java.sql.SQLException;
    
    /**
     * @author : k
     * @Date : 2022/7/30
     * @Desc : c3p0演示
     */
    public class Demo01 {
        
        public static void main(String[] args) throws SQLException {
        
            //创建数据库连接池对象
            DataSource ds = new ComboPooledDataSource();
    
            //获取连接对象
            Connection conn = ds.getConnection();
    
            //打印
            System.out.println(conn);
    
        }
    }

#### 4.Druid基本使用 ####

步骤：

1.导入jar包

2.定义配置文件

是properties形式的

可以叫任意名称，可以放在任意目录下,我们这里是放于resource目录下

3.加载配置文件

4.获取数据库连接池名称：通过工厂来获取 DruidDataSourceFactory

5.获取连接：getConnection

> 编写配置文件druid.properties

driverClassName=com.mysql.cj.jdbc.Driver
    
    #URL连接数据库的URL，其中travel（以下面例子来说）为连接的数据库，后面的参数可不改但不删
    url=jdbc:mysql://localhost:3306/db1?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
    
    characterEncoding=utf-8
    #安装mysql时候设置的用户与密码
    username=root
    password=123456
    
    #初始化物理连接的个数
    initialSize=5
    
    #最大连接池数量
    maxActive=10
    
    #获取连接时最大等待时间
    maxWait=3000
    
    #用来检测连接是否有效的sql
    validationQuery=SELECT 1
    
    #保证安全性！
    testWhileIdle=true

> 编写测试文件

package com.kk.dataSource.druid;
    
    import com.alibaba.druid.pool.DruidDataSourceFactory;
    
    import javax.sql.DataSource;
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.util.Properties;
    
    /**
     * @author : k
     * @Date : 2022/7/31
     * @Desc : druid基本演示
     */
    public class Demo01 {
        
        public static void main(String[] args) throws Exception {
        
            //定义配置文件
            //加载配置文件
            Properties pro = new Properties();
            InputStream is = Demo01.class.getClassLoader().getResourceAsStream("druid.properties");
            pro.load(is);
    
            //获取连接池对象
            DataSource ds = DruidDataSourceFactory.createDataSource(pro);
    
            //获取连接
            Connection conn = ds.getConnection();
    
            System.out.println(conn);
    
            //释放资源
            conn.close();
    
    
        }
    }

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kn0K8kpz-1659253013368)(C:\\Users\\30666\\AppData\\Roaming\\Typora\\typora-user-images\\image-20220731141904198.png)\]][img-kn0K8kpz-1659253013368_C_Users_30666_AppData_Roaming_Typora_typora-user-images_image-20220731141904198.png]

#### 5.Druid工具类 ####

步骤：

1.定义一个类DruidUtils

2.提供静态代码块加载配置文件，初始化连接池对象

3.提供方法

1.获取连接方法：通过数据库连接池获取连接

2.释放资源

3.获取连接池的方法

> DruidUtils

package com.kk.dataSource.util;
    
    import com.alibaba.druid.pool.DruidDataSourceFactory;
    
    import javax.sql.DataSource;
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Properties;
    
    /**
     * @author : k
     * @Date : 2022/7/31
     * @Desc : Druid工具类
     */
    public class DruidUtils {
        
    
        //定义成员变量 DateSource
        private static DataSource ds;
    
        static {
        
            try {
        
                //加载配置文件
                Properties pro = new Properties();
                InputStream is = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
                pro.load(is);
                //获取DataSource
                ds = DruidDataSourceFactory.createDataSource(pro);
            } catch (IOException e) {
        
                e.printStackTrace();
            } catch (Exception e) {
        
                e.printStackTrace();
            }
        }
    
        /**
         * 获取连接
         *
         * @return
         */
        public static Connection getConnection() throws SQLException {
        
            return ds.getConnection();
        }
    
    
        /**
         * 释放资源
         *
         * @param stmt
         * @param conn
         */
        public static void close(Statement stmt, Connection conn) {
        
            if (stmt != null) {
        
                try {
        
                    stmt.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
            if (conn != null) {
        
                try {
        
                    conn.close();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
    
        }
    
        /**
         * 释放资源
         *
         * @param rs
         * @param stmt
         * @param conn
         */
        public static void close(ResultSet rs, Statement stmt, Connection conn) {
        
            if (rs != null) {
        
                try {
        
                    rs.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
         */
        public static DataSource getDataSource() {
        
            return ds;
        }
    
    }

> 测试工具类

package com.kk.dataSource.druid;
    
    import com.alibaba.druid.pool.DruidDataSourceFactory;
    import com.kk.dataSource.util.DruidUtils;
    
    import javax.sql.DataSource;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import java.util.Properties;
    
    /**
     * @author : k
     * @Date : 2022/7/31
     * @Desc : druid工具类演示   完成添加操作：给account表添加一条记录
     */
    public class Demo02 {
        
        public static void main(String[] args) {
        
            Connection conn = null;
            PreparedStatement pstm = null;
            try {
        
                //获取连接
                conn = DruidUtils.getConnection();
                //定义sql
                String sql = "insert into account values(null,?,?)";
                //获取pstm对象
                pstm = conn.prepareStatement(sql);
                //给 ? 赋值
                pstm.setString(1, "王五");
                pstm.setDouble(2, 1000);
                //执行sql
                int i = pstm.executeUpdate();
                System.out.println(i);
            } catch (SQLException e) {
        
                e.printStackTrace();
            } finally {
        
                //释放资源
                DruidUtils.close(pstm, conn);
            }
        }
    }

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WQ3HI2L9-1659253013368)(C:\\Users\\30666\\AppData\\Roaming\\Typora\\typora-user-images\\image-20220731143742373.png)\]][img-WQ3HI2L9-1659253013368_C_Users_30666_AppData_Roaming_Typora_typora-user-images_image-20220731143742373.png]

## JDBCTemplate（Spring JDBC） ##

#### 1.概述 ####

Spring框架对JDBC的简单封装。提供了JDBCTemplate对象简化JDBC的开发

步骤：

1.导入jar包

2.创建jdbcTemplate对象。依赖于数据源DataSource

jdbcTemplate template = new jdbcTemplate(ds);

3.调用jdbcTemplate的方法完成CRUD操作

update()：执行DML语句，增删改语句

queryForMap()：查询结果将结果集封装为Map集合

queryForList()：查询结果将结果集封装为List集合

query()：查询结果将结果集封装为JavaBean对象

queryForObject()：查询结果将结果集封装为对象

#### 2.JDBCTemplate快速入门 ####

> 导包

<dependencies>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.27</version>
            </dependency>
            <dependency>
                <groupId>com.mchange</groupId>
                <artifactId>c3p0</artifactId>
                <version>0.9.5.4</version>
            </dependency>
    
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.2.11</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context</artifactId>
                <version>5.3.20</version>
            </dependency>
    
            <dependency>
                <groupId>commons-logging</groupId>
                <artifactId>commons-logging</artifactId>
                <version>1.2</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-jdbc</artifactId>
                <version>5.3.20</version>
            </dependency>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
                <scope>compile</scope>
            </dependency>
    
    
        </dependencies>

> 测试代码

package com.kk.dataSource.jdbcTemplate;
    
    import com.kk.dataSource.util.DruidUtils;
    import org.springframework.jdbc.core.JdbcTemplate;
    
    import javax.sql.DataSource;
    
    /**
     * @author : k
     * @Date : 2022/7/31
     * @Desc : jdbcTemplate入门
     */
    public class Demo01 {
        
        public static void main(String[] args) {
        
            //创建JDBCTemplate对象
            DataSource ds = DruidUtils.getDataSource();
            JdbcTemplate template = new JdbcTemplate(ds);
    
            //调用方法
            String sql = "update account set balance = 2000 where id = ?";
            int i = template.update(sql, 3); //第一个参数为 id = 3
            System.out.println(i);
        }
    }

#### 3.练习 ####

`使用emp表`

1.修改1号数据的 salary 为 10000

2.添加一条记录

3.删除刚才添加的记录

4.查询id为1的记录，将其封装为Map集合

5.查询所有的记录，将其封装为List集合

6.查询所有的记录，将其封装为Emp对象的List集合

7.查询总的记录数

> 实体类

package com.kk.domain;
    
    import java.util.Date;
    
    /**
     * @author : k
     * @Date : 2022/7/30
     * @Desc :
     */
    public class Emp {
        
    
        private Integer id;
        private String ename;
        private Integer job_id;
        private Integer mgr;
        private Date joindate;
        private Double salary;
        private Double bonus;
        private Integer dept_id;
    
        public Emp() {
        
        }
    
        public Emp(Integer id, String ename, Integer job_id, Integer mgr, Date joindate, Double salary, Double bonus, Integer dept_id) {
        
            this.id = id;
            this.ename = ename;
            this.job_id = job_id;
            this.mgr = mgr;
            this.joindate = joindate;
            this.salary = salary;
            this.bonus = bonus;
            this.dept_id = dept_id;
        }
    
        public Integer getId() {
        
            return id;
        }
    
        public void setId(Integer id) {
        
            this.id = id;
        }
    
        public String getEname() {
        
            return ename;
        }
    
        public void setEname(String ename) {
        
            this.ename = ename;
        }
    
        public Integer getJob_id() {
        
            return job_id;
        }
    
        public void setJob_id(Integer job_id) {
        
            this.job_id = job_id;
        }
    
        public Integer getMgr() {
        
            return mgr;
        }
    
        public void setMgr(Integer mgr) {
        
            this.mgr = mgr;
        }
    
        public Date getJoindate() {
        
            return joindate;
        }
    
        public void setJoindate(Date joindate) {
        
            this.joindate = joindate;
        }
    
        public Double getSalary() {
        
            return salary;
        }
    
        public void setSalary(Double salary) {
        
            this.salary = salary;
        }
    
        public Double getBonus() {
        
            return bonus;
        }
    
        public void setBonus(Double bonus) {
        
            this.bonus = bonus;
        }
    
        public Integer getDept_id() {
        
            return dept_id;
        }
    
        public void setDept_id(Integer dept_id) {
        
            this.dept_id = dept_id;
        }
    
        @Override
        public String toString() {
        
            return "Emp{" +
                    "id=" + id +
                    ", ename='" + ename + '\'' +
                    ", job_id=" + job_id +
                    ", mgr=" + mgr +
                    ", joindate=" + joindate +
                    ", salary=" + salary +
                    ", bonus=" + bonus +
                    ", dept_id=" + dept_id +
                    '}';
        }
    }

> 代码测试

package com.kk.jdbcTemplate;
    
    import com.kk.dataSource.util.DruidUtils;
    import com.kk.domain.Emp;
    import org.junit.Test;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.RowMapper;
    
    import javax.sql.DataSource;
    import java.sql.Date;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.List;
    import java.util.Map;
    
    /**
     * @author : k
     * @Date : 2022/7/31
     * @Desc : 用于单元测试
     */
    public class Demo02 {
        
    
        //获取JDBCTemplate对象
        DataSource ds = DruidUtils.getDataSource();
        private JdbcTemplate template = new JdbcTemplate(ds);
    
    
        //修改1号数据的 salary 为 10000
        @Test
        public void test1() {
        
            //定义sql
            String sql = "update emp set salary = ? where id = ? ";
            //执行sql
            int i = template.update(sql, 15000, 1001);
            System.out.println(i);
        }
    
    
        //添加一条记录
        @Test
        public void test2() {
        
            String sql = "insert into emp(id,ename,dept_id) values(?,?,?)";
            int i = template.update(sql, 1015, "大白", 10);
            System.out.println(i);
        }
    
    
        //删除刚才添加的记录
        @Test
        public void test3() {
        
            String sql = "delete from emp where id = ?";
            int i = template.update(sql, 1015);
            System.out.println(i);
        }
    
    
        //查询id为1的记录，将其封装为Map集合
        //注意：这个方法查询的结果集长度只能是1
        //将列名作为key，值作为value，将这条记录封装为一个map集合
        @Test
        public void test4() {
        
            String sql = "select * from emp where id = ?";
            Map<String, Object> map = template.queryForMap(sql, 1001);
            System.out.println(map);
    
        }
    
    
        //查询所有的记录，将其封装为List集合
        //注意：将每一条记录封装为一个Map集合，再将Map集合装载到List集合中
        @Test
        public void test5() {
        
            String sql = "select * from emp";
            List<Map<String, Object>> list = template.queryForList(sql);
            for (Map<String, Object> map : list) {
        
                System.out.println(map);
            }
        }
    
    
        //查询所有的记录，将其封装为Emp对象的List集合
        @Test
        public void test6_1() {
        
            String sql = "select * from emp";
            List<Emp> emps = template.query(sql, new RowMapper<Emp>() {
        
                @Override
                public Emp mapRow(ResultSet rs, int rowNum) throws SQLException {
        
                    Emp emp = new Emp();
                    int id = rs.getInt("id");
                    String ename = rs.getString("ename");
                    int job_id = rs.getInt("job_id");
                    int mgr = rs.getInt("mgr");
                    Date joindate = rs.getDate("joindate");
                    double salary = rs.getDouble("salary");
                    double bonus = rs.getDouble("bonus");
                    int dept_id = rs.getInt("dept_id");
    
                    emp.setId(id);
                    emp.setEname(ename);
                    emp.setJob_id(job_id);
                    emp.setMgr(mgr);
                    emp.setJoindate(joindate);
                    emp.setSalary(salary);
                    emp.setBonus(bonus);
                    emp.setDept_id(dept_id);
                    return emp;
                }
            });
    
            for (Emp emp : emps) {
        
                System.out.println(emp);
            }
    
        }
    
        //查询所有的记录，将其封装为Emp对象的List集合
        @Test
        public void test6_2() {
        
            String sql = "select * from emp";
            List<Emp> emps = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
            for (Emp emp : emps) {
        
                System.out.println(emp);
            }
    
        }
    
    
    
        //查询总的记录数
        @Test
        public void test7() {
        
            String sql = "select count(id) from emp";
            Long total = template.queryForObject(sql, Long.class);
            System.out.println(total);
    
        }
    
    }
     i = template.update(sql, 1015, "大白", 10);
            System.out.println(i);
        }
    
    
        //删除刚才添加的记录
        @Test
        public void test3() {
        
            String sql = "delete from emp where id = ?";
            int i = template.update(sql, 1015);
            System.out.println(i);
        }
    
    
        //查询id为1的记录，将其封装为Map集合
        //注意：这个方法查询的结果集长度只能是1
        //将列名作为key，值作为value，将这条记录封装为一个map集合
        @Test
        public void test4() {
        
            String sql = "select * from emp where id = ?";
            Map<String, Object> map = template.queryForMap(sql, 1001);
            System.out.println(map);
    
        }
    
    
        //查询所有的记录，将其封装为List集合
        //注意：将每一条记录封装为一个Map集合，再将Map集合装载到List集合中
        @Test
        public void test5() {
        
            String sql = "select * from emp";
            List<Map<String, Object>> list = template.queryForList(sql);
            for (Map<String, Object> map : list) {
        
                System.out.println(map);
            }
        }
    
    
        //查询所有的记录，将其封装为Emp对象的List集合
        @Test
        public void test6_1() {
        
            String sql = "select * from emp";
            List<Emp> emps = template.query(sql, new RowMapper<Emp>() {
        
                @Override
                public Emp mapRow(ResultSet rs, int rowNum) throws SQLException {
        
                    Emp emp = new Emp();
                    int id = rs.getInt("id");
                    String ename = rs.getString("ename");
                    int job_id = rs.getInt("job_id");
                    int mgr = rs.getInt("mgr");
                    Date joindate = rs.getDate("joindate");
                    double salary = rs.getDouble("salary");
                    double bonus = rs.getDouble("bonus");
                    int dept_id = rs.getInt("dept_id");
    
                    emp.setId(id);
                    emp.setEname(ename);
                    emp.setJob_id(job_id);
                    emp.setMgr(mgr);
                    emp.setJoindate(joindate);
                    emp.setSalary(salary);
                    emp.setBonus(bonus);
                    emp.setDept_id(dept_id);
                    return emp;
                }
            });
    
            for (Emp emp : emps) {
        
                System.out.println(emp);
            }
    
        }
    
        //查询所有的记录，将其封装为Emp对象的List集合
        @Test
        public void test6_2() {
        
            String sql = "select * from emp";
            List<Emp> emps = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
            for (Emp emp : emps) {
        
                System.out.println(emp);
            }
    
        }
    
    
    
        //查询总的记录数
        @Test
        public void test7() {
        
            String sql = "select count(id) from emp";
            Long total = template.queryForObject(sql, Long.class);
            System.out.println(total);
    
        }
    
    }

[img-VHAIbSUd-1659253013361_C_Users_30666_AppData_Roaming_Typora_typora-user-images_image-20220730161939709.png]: https://img-blog.csdnimg.cn/76b1cc0849254bc0a3fc43e77a7e6eba.png
[img-SpTsAMVy-1659253013363_C_Users_30666_AppData_Roaming_Typora_typora-user-images_image-20220730180335986.png]: https://img-blog.csdnimg.cn/486eb84ed2924f15a9c1e06fc63de0cb.png
[img-PKRQtOc6-1659253013365_C_Users_30666_AppData_Roaming_Typora_typora-user-images_image-20220730202807393.png]: https://img-blog.csdnimg.cn/4794ded0ca9045478762e137889021eb.png
[img-6aZNZF6t-1659253013366_C_Users_30666_AppData_Roaming_Typora_typora-user-images_image-20220730204822215.png]: https://img-blog.csdnimg.cn/6573927068334f849de9b84761b7a735.png
[img-tCT5Wb4O-1659253013367_C_Users_30666_AppData_Roaming_Typora_typora-user-images_image-20220730213856214.png]: https://img-blog.csdnimg.cn/1b7e10eba4e44a76addb87740c330be6.png
[img-kn0K8kpz-1659253013368_C_Users_30666_AppData_Roaming_Typora_typora-user-images_image-20220731141904198.png]: https://img-blog.csdnimg.cn/f813dbf7d24e426ba65e6fed5fbd0b96.png
[img-WQ3HI2L9-1659253013368_C_Users_30666_AppData_Roaming_Typora_typora-user-images_image-20220731143742373.png]: https://img-blog.csdnimg.cn/3efed917c5a045cd9d6bbf9659b007c7.png