Java连接数据库JDBC和JdbcTemplate

前言

如何通过Java程序连接不同的数据库（MySQL、Orcal）？建立数据库连接之后发送SQL语句，得到查询结果集呢？

JDBC是一种解决方案。

在Java中所有持久层框架的底层都是JDBC。

一、面向接口编程思想

1.耦合是什么？

耦合是软件架构中的调用方和被调用方存在较多依赖；

在高耦合的软件架构中一旦被调用方修改了代码，调用方也需要随之修改代码；

2.面向接口编程的核心思想

为实现整个软件架构的低耦合，现软件架构中的调用方和被调用方法，共同遵守同1套接口规范；

2.1.定义接口（规范）

软件开发之前预先定义接口（一套规范），要求被调用方和调用方双方都要遵守。

2.2.对于被调用方来说：

实现类的方法必须强制遵守接口规范以方便调用方进行调用
实现接口中定义的方法

2.3.对于调用方来说：

关注接口中提供的方法（规范）去实现自己的业务逻辑
不去关注实现类中方法的具体实现过程

3.面向接口编程的目的

面向接口编程（定义接口）的目的是为了减少软件架构中调用方和被调用方之间的耦合程度！

二、JDBC入门

JDBC是面向接口编程思想的一种体现，全称Java DataBase Connectivity ( Java数据库连接 ), 是Java语言操作数据库的一种技术。

Java官方（sun公司）定义的一套操作所有关系型数据库的规则( 接口 )，而具体的实现类由各个数据库厂商来实现并提供出来。

Java开发人员只需要面向JDBC接口编程就可以, 而不需要理会实现类中的方法。

1.项目准备

创建模块，导入jar包。

2.JDBC使用步骤

通过示意图来描述JDB连接数据库的7个步骤。

3.JDBC规范介绍

JDB接口提供了以下规范，帮助使用者连接数据库，并执行SQL语句。

DriverManager：驱动管理对象：可以获取数据库连接对象
Connection：数据库连接对象：可以获取执行SQL的对象，可以管理事务操作。
Statement：执行静态SQL对象：可以执行数据库的增删改查操作
PrepareStatement：执行预编译SQL对象：可以执行带有?占位符的预编译SQL
ResultSet：查询结果集对象：保存封装了MySQL查询结果对象

4.创建表（DML）

package com.zhanggen.jdbc;

import org.junit.Test;

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

public class QuickStart {
    @Test
    public void TestCreatTable() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String dbURL = "jdbc:mysql://192.168.56.18:3306/dbForJava?characterEncoding=utf8";
        String userName = "zhanggen";
        String passWord = "123.com";
        Connection connection = DriverManager.getConnection(dbURL, userName, passWord);
        String createTable = "CREATE TABLE IF NOT EXISTS `staffs`(\n" +
                "`id` INT UNSIGNED AUTO_INCREMENT KEY COMMENT '用户编号',\n" +
                "`username` VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',\n" +
                "`age` TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',\n" +
                "`salary` FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'\n" +
                ")ENGINE=INNODB DEFAULT CHARSET=UTF8;";
        PreparedStatement preparedStatement = connection.prepareStatement("");
        preparedStatement.execute(createTable);
        preparedStatement.close();
        connection.close();


    }
}

创建表

5.基本增删改查

package com.zhanggen.jdbc;

import org.junit.Test;

import java.sql.*;

public class CURD {
    //增
    @Test
    public void testInsert() throws ClassNotFoundException, SQLException {
        //1.将MySQL服务器提供的jar包中的Driver接口实现类，注册到JVM中
        Class.forName("com.mysql.jdbc.Driver");
        String dbURL = "jdbc:mysql://192.168.56.18:3306/dbForJava?characterEncoding=utf8";
        String userName = "zhanggen";
        String passWord = "123.com";
        //2.通过DriverManager建立数据库连接通道
        Connection connection = DriverManager.getConnection(dbURL, userName, passWord);
        //3.在通道上创建1个运输SQL的交通工具.
        PreparedStatement preparedStatement = connection.prepareStatement("insert into staffs values (null,?,?,?);");
        //4.使用交通工具运输SQL；
        preparedStatement.setString(1, "马睿");
        preparedStatement.setInt(2, 29);
        preparedStatement.setFloat(3, 99.19F);
        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            System.out.println("插入成功！");
        }
        //5.回收数据库资源
        preparedStatement.close();
        connection.close();

    }

    //改
    @Test
    public void testUpdate() throws ClassNotFoundException, SQLException {
        //1.将MySQL服务器提供的jar包中的Driver接口实现类，注册到JVM中
        Class.forName("com.mysql.jdbc.Driver");
        String dbURL = "jdbc:mysql://192.168.56.18:3306/dbForJava?characterEncoding=utf8";
        String userName = "zhanggen";
        String passWord = "123.com";
        //2.通过DriverManager建立数据库连接通道
        Connection connection = DriverManager.getConnection(dbURL, userName, passWord);
        //3.在通道上创建1个运输SQL的交通工具.
        PreparedStatement preparedStatement = connection.prepareStatement("update staffs set username=? where username=?");
        preparedStatement.setString(1, "张景辉");
        preparedStatement.setString(2, "马睿");
        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            System.out.println("更新成功！");
        }
        //5.回收数据库资源
        preparedStatement.close();
        connection.close();

    }

    //查
    @Test
    public void testFindAll() throws ClassNotFoundException, SQLException {
        //1.将MySQL服务器提供的jar包中的Driver接口实现类，注册到JVM中
        Class.forName("com.mysql.jdbc.Driver");
        String dbURL = "jdbc:mysql://192.168.56.18:3306/dbForJava?characterEncoding=utf8";
        String userName = "zhanggen";
        String passWord = "123.com";
        //2.通过DriverManager建立数据库连接通道
        Connection connection = DriverManager.getConnection(dbURL, userName, passWord);
        //3.在通道上创建1个运输SQL的交通工具.
        PreparedStatement preparedStatement = connection.prepareStatement("select * from staffs");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String user = resultSet.getString("username");
            int age = resultSet.getInt("age");
            float salary = resultSet.getFloat("salary");
            System.out.println("查询结果" + "用户: " + user + " 年龄: " + age + " 工资: " + salary);
        }
        //5.回收数据库资源
        preparedStatement.close();
        connection.close();
    }

    //删
    @Test
    public void testDelete() throws ClassNotFoundException, SQLException {
        //1.将MySQL服务器提供的jar包中的Driver接口实现类，注册到JVM中
        Class.forName("com.mysql.jdbc.Driver");
        String dbURL = "jdbc:mysql://192.168.56.18:3306/dbForJava?characterEncoding=utf8";
        String userName = "zhanggen";
        String passWord = "123.com";
        //2.通过DriverManager建立数据库连接通道
        Connection connection = DriverManager.getConnection(dbURL, userName, passWord);
        //3.在通道上创建1个运输SQL的交通工具.
        PreparedStatement preparedStatement = connection.prepareStatement("delete  from staffs where username=?");
        preparedStatement.setString(1, "张景辉");
        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            System.out.println("删除成功！");
        }
        //5.回收数据库资源
        preparedStatement.close();
        connection.close();

    }
}

JDBC基本增删改查

6.查询结果封装到List集合中

package com.mingde;

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

public class JdbcDemo4 {
    public static void main(String[] args) {
        List allStaffs = findAll();
        System.out.println(allStaffs.size());
        System.out.println(allStaffs);

    }

    //封装查询所有记录方法
    private static List findAll() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<Staff> staffList = new ArrayList<Staff>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String dbURL = "jdbc:mysql://192.168.0.98:3306/zhanggen?characterEncoding=utf8";
            connection = DriverManager.getConnection(dbURL, "weike", "weike@123");
            statement = connection.createStatement();
            String QuerySQL = "select id,username,age,salary from staffs";
            resultSet = statement.executeQuery(QuerySQL);
            while (resultSet.next()) {
                //创建Staff对象并给属性赋值
                Staff staff = new Staff();
                staff.setId(resultSet.getInt("id"));
                staff.setUsername(resultSet.getString("username"));
                staff.setAge(resultSet.getInt("age"));
                staff.setSalary(resultSet.getDouble("salary"));
                //Staff对象装载到list中
                staffList.add(staff);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return staffList;
    }
}

jdbc查询所有记录封装

7.查询封装为工具类

package util;

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

public class JDBCUtils {
    //使用静态代码块，加载类时，读取一次配置文件
    private static String dbURL;
    private static String user;
    private static String password;
    private static String driver;

    static {
        try {
            //加载配置文件
            Properties properties = new Properties();
            //获取src/main/resources的路径/jdbc.properties
            String configFilePath =JDBCUtils.class.getClassLoader().getResource("jdbc.properties").getPath();
            properties.load(new FileReader(configFilePath));
            dbURL = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            //注册驱动
            driver = properties.getProperty("driver");
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //获取连接，返回连接对象
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(dbURL, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //释放数据库资源
    public static void close(Statement statement, Connection connection) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //重写释放数据库资源
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

JDBCUtils

8.SQL预编译和批量执行

SQL预编译可以防止SQL注入，批量执行可以提升SQL执行的效率；

我们通过set数据类型，给PrepareStatement对象的占位符赋值。

package com.mingde;

import java.sql.*;

public class JdbcDemo6 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String dbURL = "jdbc:mysql://192.168.0.98:3306/zhanggen?characterEncoding=utf8";
            connection = DriverManager.getConnection(dbURL, "weike", "weike@123");
            //1.定义带?占位符的SQL语句
            String preparedSQL = "select id,username,age,salary from staffs where username=? and age=?";
            //2.把带?占位符的SQL语句先传输到MySQL Server
            preparedStatement = connection.prepareStatement(preparedSQL);
            //3.给第1个? 赋值
            preparedStatement.setString(1, "Martin");
            //4.给第2个? 赋值
            preparedStatement.setInt(2, 18);
            //5.再把参数传输到MySQL Server，
            //6.然后再进行SQL语句和参数的替换
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                //获取当前行的每个列
                int id = resultSet.getInt("id");
                String userName = resultSet.getString("username");
                int age = resultSet.getInt("age");
                double salary = resultSet.getDouble("salary");
                System.out.println(id + "----" + "----" + userName + "----" + age + "--------" + salary);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }

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

        }
    }
}

批量执行

9.MySQL事务

事务：是1组包含多个步骤的数据库操作。如果这组业务被事务管理，则这多个步骤要么一起成功，要么一起失败。

9.1.事务的特性

A：原子性Atomicity：1个事务操作是1个不可分割的整体，1个事物操作一定是这1个整体内的SQL要么全部执行成功，要么全部执行失败。

C：一致性Consistency：一个事务执行前后，数据库的状态是一致的。

假设数据库中有A和B 两个账号；

事务执行前A账号有100元，B账号有100元，A和B账号总计200元。

事物执行后A账号100-20元，B账号100+20元，A和B账号总计还是200元。

I：隔离性Isolation：当多个事务同时执行时，相互直接不产生任何影响。

D：持久性Durability：当1个事务执行完毕后，该事务对数据库产生的影响，一定会持久保存在磁盘上。

9.2.事务之间不隔离产生的问题

在多个事务同时并发执行时，多个事务之间不进行隔离，会产生以下3大问题

脏读：一个事务读取到了另外一个事务没有提交的数据（暂存区未提交数）
不可重复读：一个事务读取到了另外一个事务修改的数据（修改的）
幻读（虚读）：一个事务读取到了另外一个事务新增的数据（新增的）

9.3.事务隔离级别

通过设置事务隔离级别（加锁），来解决事务之间没有隔离性，会产生的问题。

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否

9.4.JDBC事务操作

JDBC中的事务操作包含：开始事务、提交事务或者回归事务。

connection.setAutoCommit(false):开启事务
commit()：事务提交/执行
rollback：事务回滚

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

public class Transaction05 {
    //jdbc的事务操作
    public static void main(String[] args) throws Exception {
        //1.注册MySQL driver
        Class.forName("com.mysql.jdbc.Driver");
        //2.创建数据库连接
        String dbURL = "jdbc:mysql://192.168.0.98:3306/zhanggen?characterEncoding=utf8";
        Connection conn = DriverManager.getConnection(dbURL, "用户", "密码");
        //3.命令启动事务相当于执行start transaction;
        conn.setAutoCommit(false);
        PreparedStatement ps = conn.prepareStatement("");
        String sql1 = "delete from staffs where username='张根85';";
        String sql2 = "delete from staffs where username='张根86';";

        try {
            int count1 = ps.executeUpdate(sql1);
            int count2 = ps.executeUpdate(sql2);
            System.out.println(count1);
            System.out.println(count2);
            conn.commit();
        } catch (SQLException e) {
            //SQL执行出现异常回滚
            System.out.println("回滚了！");
            conn.rollback();
        } finally {
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }

        }


    }
}

事务操作

三、数据库连接池

当Java项目启动之后，会一直对外提供数据库查询业务，比较繁忙的情况下，客户端反复发起系统调用创建数据库连接然后再关闭连接，浪费客户端系统资源，比较耗时。

Java为数据库连接池提供了公共的接口：DataSource，各个连接池厂商去实现这套接口提供jar包。

而这些jar包就可以认为是一款款的数据库连接池产品，业界比较出名的有下面几款：

DBCP: Apache提供的数据库连接池技术。
C3P0: 数据库连接池技术，目前使用它的开源项目有Hibernate、Spring等。
HikariCP: 日本人开发的连接池技术，性能之王, 速度最快的，目前使用它的开源项目有SpringBoot2.0等。（SQL执行速度快）
Druid(德鲁伊): 阿里巴巴提供的数据库连接池技术，是目前最好的数据库连接池。（功能齐全）

虽然有这么多的数据库连接池产品，但是按照面向接口编程的思想，我们程序员无须关注产品是如何实现的，只需要关注他们实现的接口中定义的功能即可:

Connection getConnection()
void connection.close()

1.c3p0创建数据库连接池

c3p0是Apache软件基金会提供的免费开源数据库连接池项目，在十年前比较流行，不再过多赘述；

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--new ComboPooledDataSource("mvcApp");使用 -->
    <named-config name="mvcApp">
        <property name="user">weike</property>
        <property name="password">weike@123</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://192.168.0.98:3306/zhanggen?useUnicode=true&amp;serverTimezone=UTC&amp;characterEncoding=UTF-8</property>
        <!-- 以上的user是数据库的用户，
         password是数据库的密码，driverClass是mysql的数据库驱动，
         jdbcUrl是连接数据库的url -->
        <!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数 -->
        <property name="acquireIncrement">5</property>
        <!--初始化时获取十个连接，取值应在minPoolSize与maxPoolSize之间 -->
        <property name="initialPoolSize">10</property>
        <!--连接池中保留的最小连接数 -->
        <property name="minPoolSize">10</property>
        <!--连接池中保留的最大连接数-->
        <property name="maxPoolSize">10</property>
        <!--超时时间为3秒-->
        <property name="checkoutTimeout">3000</property>
        <!--JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。如果maxStatements与maxStatementsPerConnection均为0，则缓存被关闭。Default: 0-->
        <property name="maxStatements">20</property>
        <!--maxStatementsPerConnection定义了连接池内单个连接所拥有的最大缓存statements数。Default: 0 -->
        <property name="maxStatementsPerConnection">5</property>
    </named-config>
</c3p0-config>

c3p0-config.xml

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

package com.mingde.c3p0;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;

public class C3P0Demo1 {
    public static void main(String[] args) throws Exception {
        //1.创建数据库连接池对象，指定数据库配置mvcApp
        DataSource dataSource = new ComboPooledDataSource("mvcApp");
        //2.从连接池中获取连接对象
        for (int i = 0; i <= 10; i++) {
            Connection connection = dataSource.getConnection();
            System.out.println(connection);
        }


    }
}

C3P0Demo1.java

2.Druid创建数据库连接池

1个Java项目相当于1个对外提供服务的企业，企业开业之后会一直处于开业状态。（提供对外服务）
1个数据库连接池就像企业中的员工，企业中的员工也需要一直处于在职状态。（提供对内服务）
企业和员工都需要长期处于服务状态；

Java项目启动后一直处于运行状态，程序使用静态代码块创建1个Durid数据库连接池（仅创建1次），连接池创建成功之后一直处于对内服务状态。

2.1.Druid快速入门

package com.zhanggen.jdbc;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import org.junit.Test;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

//测试Druid的使用
public class DruidTest {
    @Test
    public void testSearch() throws SQLException {
        //1.创建连接池
        DruidDataSource dataSource = new DruidDataSource();
        //设置数据库4要素
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");//驱动类的类名称
        dataSource.setUrl("jdbc:mysql://192.168.56.18:3306/dbForJava?characterEncoding=utf8");//数据库连接地址
        dataSource.setUsername("zhanggen"); //数据库用户
        dataSource.setPassword("123.com");//数据库密码
        //2.从连接池中获取连接
        DruidPooledConnection connection = dataSource.getConnection();

        //3.通过连接操作数据库
        PreparedStatement preparedStatement = connection.prepareStatement("select * from staffs");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String user = resultSet.getString("username");
            int age = resultSet.getInt("age");
            float salary = resultSet.getFloat("salary");
            System.out.println("查询结果" + "用户: " + user + " 年龄: " + age + " 工资: " + salary);
        }
        resultSet.close();
        //4.归还数据库连接
        preparedStatement.close(); //这个close()方法指的是归还连接
        connection.close();

    }
}

DruidDemo

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

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.0.98:3306/zhanggen?serverTimezone=PRC
username=weike
password=weike@123
#初始化连接数
initialSize=10
minIdle=5
#最大连接数
maxActive=10
#最大等待时间
maxWait=3000

timeBetweenEvictionRunsMillis=6000
minEvictableIdleTimeMillis=300000
testWhileIdle=true
testOnBorrow=true
testOnReturn=true
poolPreparedStatements=true
maxPoolPreparedStatementPerConnectionSize=20
validationQuery=select 1
filters=stat

druid.properties

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

package com.mingde.druid;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;

public class DruidDemo1 {
    public static void main(String[] args)throws Exception {
        //加载配置文件
        Properties properties=new Properties();
        InputStream resourceAsStream = DruidDemo1.class.getClassLoader().getResourceAsStream("druid.properties");
        properties.load(resourceAsStream);
        //创建连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        //通过工厂类获取连接对象
        for (int i = 0; i <11 ; i++) {
            Connection connection = dataSource.getConnection();
            System.out.println(connection);
        }





    }
}

DruidDemo1.java

2.2. Druid工具类

package util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DruidUtils {
    //1.定义成员变量
    private static DataSource dataSource;

    static {
        try {
            //2.加载配置文件
            Properties properties = new Properties();
            properties.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            //3.创建Druid连接池
            dataSource = DruidDataSourceFactory.createDataSource(properties);

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

    }

    //获取数据库连接方法
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //释放数据库连接资源方法 DML
    public static void Close(Statement statement, Connection connection) {
        try {
            if (statement != null) {
                //归还数据库连接到数据库连接池
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //释放数据库连接资源方法 DDL
    public static void Close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                //归还数据库连接到数据库连接池
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //获取连接池
    public static DataSource getDataSource() {
        return dataSource;
    }
}

DruidUtils.java

2.3.Druid配置文件

Java是编译型语言，程序要想执行，必须把源码编译成.class文件，我们通过交付jar包的形式把Java项目交付给客户。

对于Java项目来说，配置文件显得尤为重要，Java有以下2种配置文件

xml：需要进行xpath的解析，适用于配置内容复杂的配置文件
properties：解析配置文件比较简单，适用于配置内容简单的配置文件

下面我们通过properties配置文件对Druid数据库连接池进行配置；

-------------------------properties配置文件-------------------------------------------

#键值对
#为了反正定义的键名和当前操作系统出现冲突，在键名前面+
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.56.18:3306/dbForJava?characterEncoding=utf8
jdbc.username=zhanggen
jdbc.password=123.com

druid.properties

----------------------Druid读取配置文件----------------------------------------------

package com.zhanggen.jdbc;

import com.alibaba.druid.pool.DruidDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

public class DruidUtil {
    //1.定义静态成员变量
    private static DruidDataSource dataSource;

    //2.定义静态代码块，确保类加载器加载时执行1次
    static {

        //2.1.JDK中提供了一个专门读取properties文件的类，这个配置文件的路径必须在src路径下面，不需指定后缀名称
        ResourceBundle druid = ResourceBundle.getBundle("druid");
        //2.2.创建数据库连接池
        dataSource = new DruidDataSource();
        //2.3设置数据库4要素
        dataSource.setDriverClassName(druid.getString("jdbc.driver"));//驱动类的类名称
        dataSource.setUrl(druid.getString("jdbc.url"));//数据库连接地址
        dataSource.setUsername(druid.getString("jdbc.username")); //数据库用户
        dataSource.setPassword(druid.getString("jdbc.password"));//数据库密码
    }

    //获取数据库连接方法
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //释放数据库连接资源方法 DML
    public static void Close(Statement statement, Connection connection) {
        try {
            if (statement != null) {
                //归还数据库连接到数据库连接池
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //释放数据库连接资源方法 DDL
    public static void Close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                //归还数据库连接到数据库连接池
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //获取连接池
    public static DataSource getDataSource() {
        return dataSource;
    }
}

druid数据库连接池读取配置文件

-------------------使用连接池------------------------------------------------------------

package com.zhanggen.jdbc;

import com.alibaba.druid.pool.DruidDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

public class DruidUtil {
    //1.定义静态成员变量
    private static DruidDataSource dataSource;

    //2.定义静态代码块，确保类加载器加载时执行1次
    static {

        //2.1.JDK中提供了一个专门读取properties文件的类，这个配置文件的路径必须在src路径下面，不需指定后缀名称
        ResourceBundle druid = ResourceBundle.getBundle("druid");
        //2.2.创建数据库连接池
        dataSource = new DruidDataSource();
        //2.3设置数据库4要素
        dataSource.setDriverClassName(druid.getString("jdbc.driver"));//驱动类的类名称
        dataSource.setUrl(druid.getString("jdbc.url"));//数据库连接地址
        dataSource.setUsername(druid.getString("jdbc.username")); //数据库用户
        dataSource.setPassword(druid.getString("jdbc.password"));//数据库密码
    }

    //获取数据库连接方法
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //释放数据库连接资源方法 DML
    public static void Close(Statement statement, Connection connection) {
        try {
            if (statement != null) {
                //归还数据库连接到数据库连接池
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //释放数据库连接资源方法 DDL
    public static void Close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                //归还数据库连接到数据库连接池
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //获取连接池
    public static DataSource getDataSource() {
        return dataSource;
    }
}

DruidUtil.java

2.4.MySQL数据库产生大量超时连接

有些Java项目内部使用类数据库连接池机制；

在生产环境中，开发人员出于项目调试需要经常不断重启自己的Java项目，客户端每次启动连接池，MySQL服务端就会出现大量连接。

虽然程序停止了，但是MySQL还需要维护程序停止之前建立的连接一段时间（默认8小时），这就会导致数据库端出现大量sleep状态的数据库连接；

mysql> show processlist;
+------+-------+------------------+--------------+---------+-------+----------+------------------+
| Id   | User  | Host             | db           | Command | Time  | State    | Info             |
+------+-------+------------------+--------------+---------+-------+----------+------------------+
|  473 | root  | 172.17.0.1:54128 | db_gjdw_eova | Sleep   |     1 |          | NULL             |
|  604 | root  | 172.17.0.1:54420 | db_gjdw_eova | Sleep   |   912 |          | NULL             |
|  606 | root  | 172.17.0.1:54424 | db_gjdw_data | Sleep   |   915 |          | NULL             |
|  634 | root  | 172.17.0.1:54480 | db_gjdw_eova | Sleep   |   912 |          | NULL             |
|  635 | root  | 172.17.0.1:54482 | db_gjdw_data | Sleep   |   596 |          | NULL             |
|  636 | weike | 172.17.0.1:54484 | db_hbdw_data | Sleep   | 20533 |          | NULL             |
|  637 | weike | 172.17.0.1:54486 | db_hbdw_data | Sleep   | 20533 |          | NULL             |
|  638 | weike | 172.17.0.1:54488 | db_hbdw_data | Sleep   | 20533 |          | NULL             |
|  639 | weike | 172.17.0.1:54490 | db_hbdw_data | Sleep   | 20533 |          | NULL             |
|  640 | weike | 172.17.0.1:54492 | db_hbdw_data | Sleep   | 20533 |          | NULL             |
|  641 | weike | 172.17.0.1:54494 | db_hbdw_data | Sleep   | 20533 |          | NULL             |
|  642 | weike | 172.17.0.1:54496 | db_hbdw_data | Sleep   |  9278 |          | NULL             |
|  643 | weike | 172.17.0.1:54498 | db_hbdw_data | Sleep   |  9276 |          | NULL             |
|  644 | weike | 172.17.0.1:54500 | db_hbdw_data | Sleep   | 10605 |          | NULL             |
|  645 | weike | 172.17.0.1:54502 | db_hbdw_data | Sleep   |   722 |          | NULL             |
|  646 | weike | 172.17.0.1:54504 | db_hbdw_data | Sleep   | 20533 |          | NULL             |
|  647 | weike | 172.17.0.1:54506 | db_hbdw_data | Sleep   | 20533 |          | NULL             |
|  648 | weike | 172.17.0.1:54508 | db_hbdw_data | Sleep   | 20533 |          | NULL             |
|  649 | weike | 172.17.0.1:54510 | db_hbdw_data | Sleep   |  3559 |          | NULL             |
|  650 | weike | 172.17.0.1:54512 | db_hbdw_data | Sleep   |  4831 |          | NULL             |
|  651 | weike | 172.17.0.1:54514 | db_hbdw_data | Sleep   | 20144 |          | NULL             |
|  652 | weike | 172.17.0.1:54516 | db_hbdw_data | Sleep   |    67 |          | NULL             |
|  653 | weike | 172.17.0.1:54518 | db_hbdw_data | Sleep   |    67 |          | NULL             |
|  654 | weike | 172.17.0.1:54520 | db_hbdw_data | Sleep   | 20144 |          | NULL             |
|  655 | weike | 172.17.0.1:54522 | db_hbdw_data | Sleep   |  5224 |          | NULL             |
|  700 | weike | 172.17.0.1:54700 | rmyh         | Sleep   |  9553 |          | NULL             |
|  701 | weike | 172.17.0.1:54704 | rmyh         | Sleep   |  9553 |          | NULL             |
|  702 | weike | 172.17.0.1:54708 | rmyh         | Sleep   |  9553 |          | NULL             |
|  703 | weike | 172.17.0.1:54712 | rmyh         | Sleep   |  9553 |          | NULL             |
|  704 | weike | 172.17.0.1:54716 | rmyh         | Sleep   |  9553 |          | NULL             |
|  705 | weike | 172.17.0.1:54720 | rmyh         | Sleep   |  9553 |          | NULL             |
|  706 | weike | 172.17.0.1:54724 | rmyh         | Sleep   |  9553 |          | NULL             |
|  707 | weike | 172.17.0.1:54728 | rmyh         | Sleep   |  9553 |          | NULL             |
|  708 | weike | 172.17.0.1:54732 | rmyh         | Sleep   |  3502 |          | NULL             |
|  709 | weike | 172.17.0.1:54736 | rmyh         | Sleep   |  3502 |          | NULL             |
|  710 | weike | 172.17.0.1:54740 | rmyh         | Sleep   |  9553 |          | NULL             |
|  711 | weike | 172.17.0.1:54744 | rmyh         | Sleep   |  9553 |          | NULL             |
|  712 | weike | 172.17.0.1:54748 | rmyh         | Sleep   |  9553 |          | NULL             |
|  713 | weike | 172.17.0.1:54752 | rmyh         | Sleep   |  9553 |          | NULL             |
|  714 | weike | 172.17.0.1:54756 | rmyh         | Sleep   |  9553 |          | NULL             |
|  715 | weike | 172.17.0.1:54760 | rmyh         | Sleep   |  9553 |          | NULL             |
|  716 | weike | 172.17.0.1:54764 | rmyh         | Sleep   |  7666 |          | NULL             |
|  717 | weike | 172.17.0.1:54768 | rmyh         | Sleep   |  7666 |          | NULL             |
|  718 | weike | 172.17.0.1:54772 | rmyh         | Sleep   |  3502 |          | NULL             |
|  719 | weike | 172.17.0.1:54776 | rmyh         | Sleep   |  3481 |          | NULL             |
|  720 | weike | 172.17.0.1:54780 | rmyh         | Sleep   |  9553 |          | NULL             |
|  721 | weike | 172.17.0.1:54784 | rmyh         | Sleep   |  9553 |          | NULL             |
|  722 | weike | 172.17.0.1:54788 | rmyh         | Sleep   |  9552 |          | NULL             |
|  723 | weike | 172.17.0.1:54792 | rmyh         | Sleep   |  9552 |          | NULL             |
|  724 | weike | 172.17.0.1:54796 | rmyh         | Sleep   |  9552 |          | NULL             |
|  725 | weike | 172.17.0.1:54800 | rmyh         | Sleep   |  9552 |          | NULL             |
|  726 | weike | 172.17.0.1:54804 | rmyh         | Sleep   |  9552 |          | NULL             |
|  727 | weike | 172.17.0.1:54808 | rmyh         | Sleep   |  9552 |          | NULL             |
|  728 | weike | 172.17.0.1:54812 | rmyh         | Sleep   |  9552 |          | NULL             |
|  729 | weike | 172.17.0.1:54816 | rmyh         | Sleep   |  9552 |          | NULL             |
|  730 | weike | 172.17.0.1:54820 | rmyh         | Sleep   |  9552 |          | NULL             |
|  731 | weike | 172.17.0.1:54824 | rmyh         | Sleep   |  9552 |          | NULL             |
|  732 | weike | 172.17.0.1:54828 | rmyh         | Sleep   |  9552 |          | NULL             |
|  733 | weike | 172.17.0.1:54832 | rmyh         | Sleep   |  9552 |          | NULL             |
|  734 | weike | 172.17.0.1:54836 | rmyh         | Sleep   |  9552 |          | NULL             |
|  735 | weike | 172.17.0.1:54840 | rmyh         | Sleep   |  9552 |          | NULL             |
|  736 | weike | 172.17.0.1:54844 | rmyh         | Sleep   |  9552 |          | NULL             |
|  737 | weike | 172.17.0.1:54848 | rmyh         | Sleep   |  9552 |          | NULL             |
|  738 | weike | 172.17.0.1:54852 | rmyh         | Sleep   |  9552 |          | NULL             |
|  739 | weike | 172.17.0.1:54856 | rmyh         | Sleep   |  9552 |          | NULL             |
| 1016 | root  | 172.17.0.1:55408 | db_gjdw_data | Sleep   |   917 |          | NULL             |
| 1017 | root  | 172.17.0.1:55410 | db_gjdw_data | Sleep   |   911 |          | NULL             |
| 1018 | root  | 172.17.0.1:55412 | db_gjdw_data | Sleep   |   911 |          | NULL             |
| 1019 | root  | 172.17.0.1:55414 | db_gjdw_eova | Sleep   |   911 |          | NULL             |
| 1043 | root  | 172.17.0.1:55462 | db_gjdw_eova | Sleep   |   912 |          | NULL             |
| 1044 | root  | 172.17.0.1:55464 | db_gjdw_data | Sleep   |   911 |          | NULL             |
| 1045 | root  | 172.17.0.1:55466 | db_gjdw_eova | Sleep   |   911 |          | NULL             |
| 1046 | root  | 172.17.0.1:55468 | db_gjdw_data | Sleep   |   917 |          | NULL             |
| 1047 | root  | 172.17.0.1:55470 | db_gjdw_data | Sleep   |   911 |          | NULL             |
| 1048 | root  | 172.17.0.1:55472 | db_gjdw_data | Sleep   |   911 |          | NULL             |
| 1055 | weike | 172.17.0.1:55486 | zhanggen     | Sleep   |   765 |          | NULL             |
| 1056 | weike | 172.17.0.1:55488 | zhanggen     | Sleep   |   765 |          | NULL             |
| 1088 | root  | localhost        | NULL         | Query   |     0 | starting | show processlist |
+------+-------+------------------+--------------+---------+-------+----------+------------------+
78 rows in set (0.00 sec)

mysql> show processlist;

我修改了数据库配置文件在MySQL server端对连接超时时间进行了限制。

[mysqld]
user=mysql
character-set-server=utf8
skip-name-resolve
#连接超时时间
wait_timeout=10
#普通SQL执行日志 
general_log=ON
general_log_file=/var/log/docker/query.log 
#慢日志执行日志
slow_query_log=ON 
slow_query_log=5 
slow_query_log_file=/var/log/docker/slow-query.log
default_authentication_plugin=mysql_native_password
sql_mode =STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8

my.cnf

四、JdbcTemplate

JdbcTemplate是Spring框架提供的一种持久层技术，用于操作数据库，它底层封装了JDBC技术；

jdbc Template是建立在1个数据库连接池之上的。

JdbcTemplate处理了资源的建立和释放，帮助我们避免一些常见的错误，比如忘了关闭连接，我们只需要提供SQL语句和提取结果。

核心类：

JdbcTemplate 用于执行增、删、改、查的SQL语句
BeanPropertyRowMapper 用将数据库返回的记录封装进实体对象

核心方法：

update 用来执行增、删、改语句，相当于JDBC的 executeUpdate()
query/queryForObject 用来执行查询语句，相当于JDBC的 excuteQuery()

1..导入maven依赖

 <dependencies>
        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <!--druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.15</version>
        </dependency>

        <!--jdbcTemplate依赖所在-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.1.6.RELEASE</version>
        </dependency>

        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>

        <!--junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
        </dependency>
    </dependencies>

pom.xml

2.JdbcTemplate 快速入门

package com.zhanggen.test;

import com.alibaba.druid.pool.DruidDataSource;
import com.zhanggen.domain.Account;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class JdbcTemplateTest {
    //测试新增数据
    @Test
    public void testSave() {
        //1.创建1个数据源（Druid）对象
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource.setUrl("jdbc:mysql://192.168.56.18:3306/dbForJava?characterEncoding=utf8");
        druidDataSource.setUsername("zhanggen");
        druidDataSource.setPassword("123.com");

        //2.创建JdbcTemlate对象
        JdbcTemplate jdbcTemplate = new JdbcTemplate(druidDataSource);

        //3.执行新增方法
        jdbcTemplate.update("insert into account values (null,?,?)", "河北农村信用社", 200000F);


    }

    //测试查询所有记录方法
    @Test
    public void testQuery() {
        //1.创建1个数据源（Druid）对象
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource.setUrl("jdbc:mysql://192.168.56.18:3306/dbForJava?characterEncoding=utf8");
        druidDataSource.setUsername("zhanggen");
        druidDataSource.setPassword("123.com");

        //2.创建JdbcTemlate对象
        JdbcTemplate jdbcTemplate = new JdbcTemplate(druidDataSource);

        //3.查询所有记录
        List<Account> acountList = jdbcTemplate.query("select * from account;", new BeanPropertyRowMapper<Account>(Account.class));
        for (Account account : acountList) {
            System.out.println(account);
        }

    }

    //测试查询1条记录方法
    @Test
    public void testQueryOne() {
        //1.创建1个数据源（Druid）对象
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource.setUrl("jdbc:mysql://192.168.56.18:3306/dbForJava?characterEncoding=utf8");
        druidDataSource.setUsername("zhanggen");
        druidDataSource.setPassword("123.com");

        //2.创建JdbcTemlate对象
        JdbcTemplate jdbcTemplate = new JdbcTemplate(druidDataSource);

        //3.查询1条记录
        Account acount = jdbcTemplate.queryForObject("select * from account where name = ?", new BeanPropertyRowMapper<>(Account.class), "北京银行");
        System.out.println(acount);


    }
}

jdbc入门

3.JdbcTemplate 执行DML语句

使用Jdbc Template执行增、删、修改操作。

package com.zhanggen;

import org.junit.jupiter.api.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import util.DruidUtils;

public class JdbcTemplateDemo2 {

    private static JdbcTemplate template = new JdbcTemplate(DruidUtils.getDataSource());
    private static String preparedSQL = null;

    public static void indertEntry() throws Exception {
        preparedSQL = "update (taffs set age=? where username=?";
        template.update(preparedSQL, 89, "武松");
    }

    public static void addStaff() {
        preparedSQL = "insert into staffs (username,age,salary) values (?,?,?)";
        template.update(preparedSQL, "张无忌", 30, 98.98);
    }

    public static void deleteStaff() {
        preparedSQL = "delete from staffs where username=?";
        template.update(preparedSQL, "藤田刚");
    }

}

4.JdbcTemplate 执行DQL语句

使用Jdbc Template执行数据库查询操作，并把查询结果封装成对象，添加到List中。

package com.zhanggen;

import org.junit.jupiter.api.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import util.DruidUtils;

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

public class JdbcTemplateDemo2 {

    private static JdbcTemplate template = new JdbcTemplate(DruidUtils.getDataSource());
    private static String preparedSQL = null;

    //1.JdbcTemplate执行DML操作
    public static void indertEntry() throws Exception {
        preparedSQL = "update (taffs set age=? where username=?";
        template.update(preparedSQL, 89, "武松");
    }

    public static void addStaff() {
        preparedSQL = "insert into staffs (username,age,salary) values (?,?,?)";
        template.update(preparedSQL, "张无忌", 30, 98.98);
    }

    public static void deleteStaff() {
        preparedSQL = "delete from staffs where username=?";
        template.update(preparedSQL, "藤田刚");
    }

    //2.JdbcTemplate执行DQL操作
    public static void queryForMap() {
        //查询结果封装为map类型
        preparedSQL = "select username,age,salary from staffs where id = ?";
        Map<String, Object> map = template.queryForMap(preparedSQL, 44);
        System.out.println(map);

    }

    public static void queryForList() {
        //查询结果封装为List类型
        preparedSQL = "select * from staffs";
        List<Map<String, Object>> list = template.queryForList(preparedSQL);
        System.out.println(list);

    }

    public static void query1() {
        //查询staffs表中所有记录，把每一条记录封装为staff对象，并添加到集合中。
        preparedSQL = "select * from staffs";
        List<Staff> staffList = template.query(preparedSQL, new RowMapper<Staff>() {
            @Override
            public Staff mapRow(ResultSet resultSet, int i) throws SQLException {
                Staff staff = new Staff();
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                int age = resultSet.getInt("age");
                double salary = resultSet.getDouble("salary");
                staff.setId(id);
                staff.setUsername(username);
                staff.setAge(age);
                staff.setSalary(salary);
                return staff;
            }
        });
        System.out.println(staffList);
    }

    public static void query2() {
        //简化版
        //查询staffs表中所有记录，把每一条记录自动封装为staff对象，并添加到集合中。
        preparedSQL = "select * from staffs";
        List<Staff> staffList = template.query(preparedSQL, new BeanPropertyRowMapper<Staff>(Staff.class));
        System.out.println(staffList);
    }

    //3.用于做SQL聚合函数的查询
    public static void queryForObject() {
        preparedSQL = "select count(id) from staffs";
        Long total = template.queryForObject(preparedSQL, Long.class);
        System.out.println(total);
    }


}

参考

posted on 2021-12-04 16:18 Martin8866 阅读(454) 评论(0) 编辑收藏举报

刷新页面返回顶部