JDBC 连接MySQL & 连接池（c3p0/Druid）& JdbcTemplate

俩草莓

于 2023-07-15 22:13:40 发布

阅读量312

点赞数

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/weixin_44018089/article/details/131682709

版权

一、开头

本文是基于如下版本软件所展现
InteIIiJ IDEA 2021.1.3 (UItimate Edition)
MySQL Community Server - GPL Server version: 8.0.32 
mysql-connector-java-8.0.11.jar
java version "1.8.0_202"

二、JDBC

1、JDBC 的概念
JDBC是Java Database Connectivity （Java数据库连接）技术简称，java语言操作数据库。
JDBC本质是，官方（sun）公司，提供的一套操作关系型数据库的规则（即接口）。
各数据库厂商去实现接口，提供驱动jar包。我们可以使用这些接口（编程）。
真正执行程序的是这些驱动jar包中的实现类。

2、JDBC 的功能
1> 与数据库建立连接。
2> 向数据库发送 SQL 语句并执行这些语句。
3> 处理数据返回的结果。

3、JDBC 的常用接口和类
1> DriverManager:驱动管理对象
–1、注册驱动
–2、获取数据库连接
2> Connection:数据库连接对象
–1、功能
a. 获取执行SQL对象
* Statement createStatement()
* PreparedStatement PreparedStatement(String sql)
b. 事务管理
* 开启事务：setAutoCommit(boolean autoCommit)
* 提交事务：Commit()
* 回滚事务：rollback()

3> Statement:执行SQL对象
–1、执行SQL
a. boolean execute(String sql) : 可以执行任意 SQL
b. int executeUpdate(String sql): 执行 DML (insert\udpate\delete) 语句，DDL (create\alter\drop) 语句。
–返回值：影响行数，可以通过这个影响行数，判断语句是否执行成功，返回值>0 则成功，反之则为失败。
c. ResultSet executeQuery(String sql) : 执行 DQL 语句。

4> ResultSet:结果集对象，封装查询结果
–1、boolean next(): 游标向下移动一行，判断当前是否有记录，是返回 true，否则返回 false.
–2、getXxx(参数): 获取数据
* Xxx 代表数据类型：e.g: int getInt() or String getString()
* 参数列名称

5> PreparedStatement:执行SQL对象
–1、SQL 注入问题：在拼接SQL时，有一些特殊的关键字参与字符串的拼接。会造成安全问题。
– 在拼写SQL后面，随便拼写 a’ or 1=1
–2、解决SQL注入问题，使用PreparedStatement对象解决
–3、预编译的SQL：参数使用?作为占位符
–4、步骤：
–>1 导入数据库驱动 jar 包
–>2 注册驱动
–>3 获取数据库连接对象 Connection
–>4 定义SQL
# 注意：select * from user where username = ? and password = ?;
–>5 获取执行SQL语句的对象 PreparedStatement Connection.PreparedStatement(String sql)
–>6 给? 赋值
–>7 执行SQL，接收返回值
–>8 处理结果
–>9 释放资源

--5、注意：后期都会使用PreparedStatement来完成增删改查的所有操作
     -->1 可以防止SQL注入
     -->2 效率更高

三、数据库的操作（MySQL）

1、创建数据库和数据表

--创建数据库
create database db1;

--使用数据库（指定为当前数据库）
use db1;

--删除表结构
drop table if exists websites;

--创建表结构
create table websites(
id int,
name varchar(30),
url varchar(50),
alexa int ,
country VARCHAR(10)
)

--插入表数据
INSERT INTO `websites` 
VALUES ('1', 'Google', 'https://www.google.cm/', '1', 'USA')
, ('2', '淘宝', 'https://www.taobao.com/', '13', 'CN')
, ('3', '菜鸟教程', 'http://www.runoob.com', '5892', '')
, ('4', '微博', 'http://weibo.com/', '20', 'CN')
, ('5', 'Facebook', 'https://www.facebook.com/', '3', 'USA');

--查询表数据
SELECT * from websites;

2、连接 MySQL

(1)、注册驱动

     Class.forName("com.mysql.cj.jdbc.Driver");

(2)、获取连接

     Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC");

(3)、获取执行者连接

     Statement stmt = conn.createStatement();

(4)、执行 SQL 语句，并接受结果

  // 1. DQL 语句
  String sql = "select * from user";
  stmt.executeQuery(sql);

  // 2. DML & DDL 语句
  String sql = "update user set name = 'liangcaomei' where id = 1";
  String sql = "inset into user value('1','liangcaomei')"
  ...
  ResultSet rs = stmt.executeUpdate(sql)

(5)、处理结果

     while(rs.next()){
	//打印结果
	System.out.println(rs.getInt("id") + "\t" + rs.getString("name"));
     }

(6)、释放资源

     rs.close();
     stmt.close();
     conn.close();

JDBC 代码展示

1. 配置文件

url=jdbc:mysql://localhost:3306/db1?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
user=root
password=root
driver=com.mysql.cj.jdbc.Driver

2. JDBCUtils工具类

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;

    /**
     * 1. 初始化注册驱动
     * 2. 初始化配置文件中连接数据库的配置信息
     */
    static {
        try {
            //创建 Properties 对象
            Properties prop = new Properties();
            InputStream res = JDBCUtils.class.getResourceAsStream("jdbc.properties"); //successfull
            prop.load(res);

            //获取文件中对应的信息
            url = prop.getProperty("url");
            user = prop.getProperty("user");
            password = prop.getProperty("password");
            driver = prop.getProperty("driver");

            //打印信息
            /*System.out.println(url);
            System.out.println(user);
            System.out.println(password);
            System.out.println(driver);*/

            // 注册驱动
            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     * @return 连接对象
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

     /**
     * 释放资源 -- 2个参数
     * @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();
            }
        }
    }

    /**
     * 释放资源 -- 3个参数
     * @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();
            }
        }
    }

}

3. DemoMain测试（增、删、改、查）

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DemoMain {
    private static Connection conn;
    private static Statement stmt;
    private static ResultSet rs;
    private static List<Emp> list;

    public static void main(String[] args) {
        DemoMain dm = new DemoMain();
        //List<Emp> list = dm.findAll(); //select method
        //System.out.println(list); //打印结果集
        //System.out.println(list.size());//打印数组长度

//        insrtData(); //insert method
//        updateById();//udpate method
        deleteById();

    }

    /**
     * delete record
     */
    private static void deleteById() {
        //1. 注册驱动--已经在 JDBCUtils 工具类中初始化注册
        try {
            //2. 获取数据库连接
            conn = JDBCUtils.getConnection();

            //3. 获取执行SQL对象
            stmt = conn.createStatement();

            //4. 定义SQL
            String sql = "delete from websites where id = 7";

            //5. 执行SQL，接收返回结果
            int i = stmt.executeUpdate(sql);

            //6. 处理结果
            if (i > 0) {
                System.out.println("delete record successfully");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7. 释放资源
            JDBCUtils.close(stmt,conn);
        }


    }

    /**
     * insert data
     */
    private static void insrtData() {
        //1. 注册驱动--已经在 JDBCUtils 工具类中初始化注册
        try {
            //2. 获取连接数据对象
            conn = JDBCUtils.getConnection();

            //3. 获取执行SQL对象
            stmt = conn.createStatement();

            //4. 定义SQL
            String sql = "insert into websites \n" +
                    "VALUES ('6', 'Liangcaomei', 'https://www.Liangcaomei.cn/', '100', 'CN')";

            //5. 执行SQL，并返回结果
            int i = stmt.executeUpdate(sql);

            //6. 处理结果
            if (i > 0) {
                System.out.println("insert data successfully.");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7. 是否资源
            JDBCUtils.close(stmt, conn);
        }


    }

    /**
     * update database
     */
    private static void updateById() {
        //1. 注册驱动--已经在 JDBCUtils 工具类中初始化注册
        try {
            //2. 获取连接数据库对象
            conn = JDBCUtils.getConnection();

            //3. 获取执行SQL对象
            stmt = conn.createStatement();

            //4. 定义SQL
            String sql = "update websites set country = 'CN' where id = 3";

            //5. 执行SQL，判断update是否成功
            int i = stmt.executeUpdate(sql);

            //6. 处理结果
            if (i > 0) {
                System.out.println("update successfully.");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7. 释放资源
            JDBCUtils.close(stmt, conn);
        }
    }

    /**
     * 返回封装好的 list<Emp>数组
     *
     * @return
     */
    public List<Emp> findAll() {
        /*Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Emp> list = null;*/

        try {
            //1. 注册驱动
//             Class.forName(JDBC_DRIVER);

            //2. 获取连接
            conn = JDBCUtils.getConnection();

            //3. 获取执行SQL语句对象
            stmt = conn.createStatement();

            //4.定义SQL (DQL)语句
            //5. 执行SQL 接收结果
            String sql = "select * from websites";
            rs = stmt.executeQuery(sql);

            //6. 遍历结果集 封装对象，装载集合
            Emp emp = null;
            list = new ArrayList<Emp>();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String url = rs.getString("url");
                int alexa = rs.getInt("alexa");
                String country = rs.getString("country");

                // 创建emp 对象 赋值
                emp = new Emp();
                emp.setId(id);
                emp.setName(name);
                emp.setUrl(url);
                emp.setAlexa(alexa);
                emp.setCountry(country);

                // 装载集合
                list.add(emp);
            }
            //7. 释放资源
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs, stmt, conn);
        }

        return list;
    }

}

四、数据库连接池

1、概念：
就是一个容器（集合），存放数据库连接对象的容器。
当系统初始化后，容器被创建，容器会申请一下连接对象，当用户来访问数据库时，从
容器中获取连接对象，用户访问完后，会将连接返还给容器。

2、好处：
I、节约资源
II、用户访问高效

3、实现：
I、标准接口：DataSource javax.sql包下
方法：
获取连接：getConnection()
归还连接：Connection.close() 如果连接对象 Connection 是从连接池获取的。那么调用 Connection.close() 方法，则不会再关闭，
而是归还连接对象

II、一般情况不用我们去实现它，由数据库厂商实现
1. C3P0：数据库连接池技术
2. Druid:数据库连接池技术（阿里巴巴提供）

4. C3P0：数据库连接池技术
步骤
1. 导入jar包（2个）c3p0-0.9.5.5.jar / mchange-commons-java-0.2.19.jar
* 不要忘记导入数据库驱动jar包
2. 定义配置文件：
* 名称：c3p0.properties 或者 c3po-config.xml
* 路径：直接放在src目录下即可。
3. 创建核心对象，数据库连接池对象 ComboPooledDataSource
4. 获取连接：getConnection

5. Druid：数据库连接池技术
步骤
1. 导入jar包（2个）c3p0-0.9.5.5.jar / mchange-commons-java-0.2.19.jar
* 不要忘记导入数据库驱动jar包
2. 定义配置文件：
* 名称：c3p0.properties 或者 c3po-config.xml
* 路径：直接放在src目录下即可。
3. 加载配置类对象 Properties
4. 创建核心对象，数据库连接池对象: 通过工厂来获取 DruidDataSourceFactory
5. 获取连接：getConnection

定义工具类
1. 定义一个类 JDBCDruidUtils
2. 提供静态代码块加载配置文件，初始化连接池对象
3. 提供方法
1. 获取连接方法：通过数据库连接池获取连接
2. 释放资源
3. 获取连接池方法

代码展示（数据库连接池）

3CP0
配置文件：c3p0-config.xml

<c3p0-config>
   <!-- 使用默认的配置读取连接池对象 -->
   <default-config>
      <!--  连接参数 -->
      <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
      <!--"jdbc:mysql://localhost:3306/logindb1?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false";-->
      <!--<property name="jdbcUrl">jdbc:mysql://localhost:3306/logindb1</property>-->
      <!--jdbc:mysql://localhost:3306/website?serverTimezone=Asia/Shanghai-->
      <property name="jdbcUrl">jdbc:mysql://localhost:3306/db1?serverTimezone=Asia/Shanghai</property>
      <property name="user">root</property>
      <property name="password">root</property>

      <!-- 连接池参数 -->
      <!--初始化申请的连接数量-->
      <property name="initialPoolSize">5</property>
      <!--最大的连接数量(池子中最大的数量是10个)-->
      <property name="maxPoolSize">10</property>
      <!--超时时间3000表示，如果出错，3s后报错-->
      <property name="checkoutTimeout">3000</property>
   </default-config>

   <named-config name="otherc3p0">
      <!--  连接参数 -->
      <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
      <property name="jdbcUrl">jdbc:mysql:///bd2?serverTimezone=UTC&amp;useSSL=false</property>
      <property name="user">root</property>
      <property name="password">xxxxxx</property>

      <!-- 连接池参数 -->
      <property name="initialPoolSize">5</property>
      <property name="maxPoolSize">8</property>
      <property name="checkoutTimeout">1000</property>
   </named-config>
</c3p0-config>

测试代码

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;
import java.sql.SQLException;

public class C3P0Demo {
    public static void main(String[] args) throws SQLException {
        //1. 导入jar 包
        //2. 定义配置文件
        //3. 创建核心对象，数据库连接池对象 ComboPooledSource
        ComboPooledDataSource ds = new ComboPooledDataSource();

        //4. 获取连接： getConnection
        Connection conn = ds.getConnection();
        System.out.println(conn); //打印 com.mchange.v2.c3p0.impl.NewProxyConnection@71f2a7d5 [wrapping: com.mysql.cj.jdbc.ConnectionImpl@2cfb4a64]


    }
}

druid
配置文件: druid.properties

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/db1?serverTimezone=Asia/Shanghai
username=root
password=root

# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间

测试代码

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.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

public class DruidDemo {
    static Connection conn;
    static PreparedStatement pstmt;

    public static void main(String[] args) {
        try {
            //1. 获取连接对象 getConnection
            conn = JDBCDruidUtils.getConnection();

            //2. 定义SQL
            String sql = "INSERT INTO `websites` \n" +
                    "VALUES (?, ?, ?,?,?)";

            //3. 获取pstmt对象
            pstmt = conn.prepareStatement(sql);

            //4. 给？赋值
            pstmt.setInt(1,7);
            pstmt.setString(2,"Liangcaomei");
            pstmt.setString(3,"https://www.Liangcaomei.cn/");
            pstmt.setInt(4,111);
            pstmt.setString(5,"HK");

            //5. 执行SQL
            int i = pstmt.executeUpdate();
            System.out.println(i);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            //6.释放资源
            JDBCDruidUtils.close(pstmt,conn);
        }
    }
}

五、Spring JDBC

Spring 框架对 JDBC 的简单封装。提供了一个 JDBCTemplate 对象简化的开发
步骤：
1. 导入 jar 包 --导入 jar 包一定要跟JDK的版本匹配 (spring-jdbc-6.0.6.jar 与 JDK1.9) (spring-jdbc-5.0.5.jar 与 JDK1.8)
  所需 jar 包：commons-logging-1.2.jar/spring-beans-5.1.10.RELEASE.jar/spring-core-5.1.10.RELEASE.jar
  /spring-jdbc-5.1.10.RELEASE.jar/spring-tx-5.1.10.RELEASE.jar
2. 创建 JDBCTemplate 对象，依赖于数据源 DataSource
  JdbcTemplate template = new JdbcTemplate(ds);
3. 调用 JdbcTemplate 的方法来完成 CRUD 的操作
  update(): 执行 DML 语句。增删改语句
  queryForMap(): 查询结果将结果集封装为 Map 集合
  * 注意：这个方法只能查一笔记录
  queryForList(): 查询结果将结果封装成 list 集合
  * 注意：降每一笔记录封装为 Map 集合，再将 Map 集合装载到 list 集合中
  query(): 查询结果，将结果封装成 JavaBean 对象
  * query 的参数 RowMapper
  – 我们用 BeanPropertyRowMapper 实现类。可以完成数据到 JavaBean 的自动封装
  – new BeanPropertyRowMapper<类型>(类型.class)
  queryForObject(): 查询结果，将结果封装成对象
  * 一般使用与聚合函数的查询
  * queryForObject(sql,Long.class)
提醒：如果用 Junit 单元测试时
– 如报：TestEngine with ID ‘junit-vintage’ failed to discover tests
* 解决方案：import org.junit.jupiter.api.Test; 修改为：import org.junit.Test;
1. 练习
  1. 修改 1 记录的 Alexa 为 100
2. 添加一笔记录
3. 删除一笔记录
4. 查询一笔记录，将其封装为 Map 集合
5. 查询所有记录，将其封装为 list 集合
6. 查询所有记录，将其封装为 Websites 对象的 list 集合
7. 查询总记录数

Spring JDBC 代码展示

JavaBean 对象

public class Websites {
    private Integer id;
    private String name;
    private String url;
    private Integer alexa;
    private String country;

    public Websites() {
    }

    public Websites(Integer id, String name, String url, Integer alexa, String country) {
        this.id = id;
        this.name = name;
        this.url = url;
        this.alexa = alexa;
        this.country = country;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public Integer getAlexa() {
        return alexa;
    }

    public void setAlexa(Integer alexa) {
        this.alexa = alexa;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }

    @Override
    public String toString() {
        return "Websites{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", url='" + url + '\'' +
                ", alexa=" + alexa +
                ", country='" + country + '\'' +
                '}';
    }
}

配置文件/JDBCUtils工具类–用的Druid的可查看上面代码展示
JdbcTemplateDemo

import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;


public class JdbcTemplateDemo {
    //Junit 单元测试，可以让方法独立执行
    //1. 获取 JDBCTtemplate 对象
    private JdbcTemplate template = new JdbcTemplate(JDBCDruidUtils.getDataSource());

    /**
     * 1. 修改 1 记录的 Alexa 为 100
     */
    @Test
    public void testUpdate1() {
        //2. 定义SQL
        String sql = "update websites set alexa = 100 where id = ?";
        //3. 执行SQL
        int count = template.update(sql, 1);
        System.out.println(count);
    }

    /**
     * 2. 添加一笔记录
     */
    @Test
    public void testInsert1() {
        //2. 定义SQL
        String sql = "INSERT INTO `websites` \n" +
                "VALUES (?,?,?,?,?)";
        //3. 执行SQL
        int i = template.update(sql, 9, "gll", "www", 99, "cn");
        System.out.println(i);
    }

    /**
     * 3. 删除一笔记录
     */
    @Test
    public void testDelete1() {
        //2. 定义SQL
        String sql = "delete from websites where id = ?";
        //3. 执行SQL
        int i = template.update(sql, 9);
        System.out.println(i);
    }

    /**
     * 4. 查询一笔记录，将其封装为 Map 集合
     */
    @Test
    public void testSelectById() {
        //2. 定义SQL
        String sql = "select * from websites where id = ?";
        //3. 执行SQL
        Map<String, Object> map = template.queryForMap(sql, 1);
        System.out.println(map);
    }

    /**
     * 5. 查询所有记录，将其封装为 list 集合
     */
    @Test
    public void testSelectAll() {
        //2. 定义SQL
        String sql = "select * from websites";
        //3. 执行SQL
        List<Map<String, Object>> maps = template.queryForList(sql);
        System.out.println(maps);
    }

    /**
     * 6. 查询所有记录，将其封装为 Websites 对象的 list 集合
     * 使用 RowMapper 接口，需要重写它的 mapRow 方法
     */
    @Test
    public void TestSelectAll2() {
        //2. 定义SQL
        String sql = "select * from websites";
        //3. 执行SQL
        List<Websites> list = template.query(sql, new RowMapper<Websites>() {
            @Override
            public Websites mapRow(ResultSet rs, int i) throws SQLException {
                Websites web = new Websites();
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String url = rs.getString("url");
                int alexa = rs.getInt("alexa");
                String country = rs.getString("country");

                web.setId(id);
                web.setName(name);
                web.setUrl(url);
                web.setAlexa(alexa);
                web.setCountry(country);

                return web;
            }
        });

        for (Websites web : list) {
            System.out.println(web);
        }
    }

    /**
     * 6. 查询所有记录，将其封装为 Websites 对象的 list 集合
     * 不使用 RowMapper 接口，直接使用它的实现类 BeanPropertyRowMapper
     */
    @Test
    public void TestSelectAll3() {
        //2. 定义SQL
        String sql = "select * from websites";
        //3. 执行SQL
        List<Websites> list = template.query(sql, new BeanPropertyRowMapper<Websites>(Websites.class));
//        System.out.println(list);
        for (Websites web : list) {
            System.out.println(list);
        }
    }

    /**
     * 7. 查询总记录数
     */
    @Test
    public void TestSelectCoun() {
        //2. 定义SQL
        String sql = "select count(id) from websites";
        //3. 执行SQL
        Long total = template.queryForObject(sql, Long.class);
        System.out.println(total);
    }
}