# MySql 安装及 JDBC 的使用

# 问题描述

• 在 Java 语言环境下，使用 ODBC 连接 MySQL 数据库，通过 SQL 语句完成对数据库的基本操作：创建数据表、插入数据、删除数据、修改数据、删除数据表、跨数据表操作
• 实现额外功能：事务支持、索引支持、多用户（多客户端）同时访问数据库，记录它们的访问性能
• 数据库内的初始数据至少有 5 个表格，至少共 100,000 条数据

# 环境

• 语言： Java11
• 开发环境： IDEA 2021.1.1 x64
• 数据库： MySQL 8.0.26
• 数据库部署环境： CentOS Linux 8.2.2004

# MySQL 环境搭建

首先使用 wget 命令获取 MySQL 安装包

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz

安装 MySQL 的依赖 libaio库

yum install libaio

然后使用 tar命令 解压

tar -xvf mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz

将解压后的文件夹重命名为 mysql ，并且移动到 /usr/local/ 目录下

mv mysql-8.0.26-linux-glibc2.12-x86_64 /usr/local/mysql

创建存放数据库数据的文件夹 data

cd /usr/local/mysql
mkdir data

在 mysql 目录下新建 tmp 文件夹 ，并修改文件夹的权限

mkdir tmp
chmod 777 /tmp

创建 mysql用户组 和 mysql 用户 ，并且将 mysql 目录极其下面所有文件权限分配给 mysql 用户

groupadd mysql
useradd -g mysql mysql
chown -R mysql.mysql /usr/local/mysql/

进行数据库初始化

bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

修改 mysql 的配置信息

vim /etc/my.cny
# 向配置文件中添加以下内容
[mysqld]
basedir=/usr/local/mysql   
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/mysql.sock
character-set-server=utf8
port=3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[client]
socket=/usr/local/mysql/mysql.sock
default-character-set=utf8

将 mysql 注册为服务，并检查是否注册成功

chkconfig --add mysql
chkconfig --list mysql

启动服务，并查看服务状态

service mysql start
service mysql status

将 mysql命令 软连接到 /usr/bin 用户执行目录下

ln -s /usr/local/mysql/bin/mysql /usr/bin

连接 mysql 服务

mysql -uroot -p

设置允许 root 用户远程连接

USE mysql
UPDATE user SET host = '%' WHERE user = 'root';
# 刷新以立即生效
FLUSH PRIVILEGES;

# 使用的数据库说明

为了使得接下来的操作能够顺利进行，首先需要现在服务器端创建一个 database。

# SQL 脚本

首先执行 SQL 脚本（脚本内容见源码部分）

mysql -uroot -p
mysql> source /root/tools/MySQL/myemployees.sql

查看执行结果

SHOW DATABASES;
USE myemployees;
SHOW TABLES;

可以看到创建了一个名为 myemployees 的数据库，其中包含五张表： departments , employees , job_grades , jobs , locations 。

# 数据库描述

这是一个关于企业员工信息管理的数据库，其中包含了部门信息，雇员信息，工作种类，收入等级，地点的信息，以下是各张表的字段描述：

# 使用客户机访问 MySQL

选择使用 MySQL 官方提供的 mysql-connector-j 来连接 MySQL，需要在 maven 中添加以下依赖：

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.26</version>
</dependency>

一共创建了两个类

• 第一个类是 MySqlOperation ，其中封装了建立连接，增删改查等操作
• 第二个类是 ConnectionTest 用于测试各类操作

以下是建立连接和释放连接的函数，其作用分别是创建连接释放连接：

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

/**
 * @author DzcGood
 * @date 2021/12/5 - 21:00
 *
 * 此类提供有关数据库的各项操作，包括获取数据库连接对象，增删改查等各项操作
 */
public class MySqlOperation {
    /**数据库连接对象*/
    private static Connection con = null;

    /**Statement类对象*/
    private static Statement stmt = null;

    /**JDBC驱动名及数据库URL*/
    private static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String DB_URL = "jdbc:mysql://59.110.238.134:3306/" +
            "myemployees?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";

    /**数据库的用户名与密码，需要根据自己的设置*/
    private static final String USER = "root";
    private static final String PASSWORD = "wodeQfeima110";

    /**
     * 用于创建数据库连接
     * */
    public static void getConnection(){
        try {
            //注册JDBC驱动
            Class.forName(JDBC_DRIVER);
            //建立连接
            System.out.println("正在连接数据库……");
            con = DriverManager.getConnection(DB_URL, USER, PASSWORD);
            //获取Statement对象
            stmt = con.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (con != null) {
            System.out.println("连接创建成功！");
        }else{
            System.out.println("连接创建失败！");
        }
    }

    /**
     * 关闭数据库连接
     * */
    public static void closeConnection(){
        if (con != null) {
            try {
                System.out.println("正在关闭连接……");
                con.close();
                System.out.println("连接已关闭！Catch you later~");
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

接下来编写测试函数进行连接，测试是否连接成功

/**
  *测试能否正常连接服务端数据库
  */
@Test
public void connect(){
    MySqlOperation.getConnection();
    MySqlOperation.closeConnection();
}

运行结果如下：

# 数据库基本操作

在 MySqlOperation类 中添加执行更新相关、查询相关的 sql 语句的函数：

/**
  * 执行更新相关的SQL语句
  * @param sql 要执行的SQL语句
  * */
public static void executeUpdate(String sql){
    if (stmt != null) {
        try {
            stmt.executeUpdate(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

/**
  * 执行查询相关的SQL语句
  * @param sql 要执行的SQL语句
  * */
public static ResultSet executeQuery(String sql){
    ResultSet rs = null;
    if (stmt != null) {
        try {
            rs = stmt.executeQuery(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    return rs;
}

# 创建数据表

此过程中，将创建一个名为 boys 的表格，其中记录了 id 和 name

ConnectionTest类 中的实现

/**
     * 测试能否正常创建表格
     * */
    @Test
    public void createTableTest(){
        //将创建一个名为boys的表格，其中记录了`id`和`name`
        //先查询原有的表格
        MySqlOperation.getConnection();
        String sql1 = "show tables;";
        ResultSet rs = MySqlOperation.executeQuery(sql1);
        System.out.print("现有的表如下：");
        try {
            while(rs.next()){
                System.out.print(rs.getString(1) + ", ");
            }
            System.out.println();
            //新建一个表格
            String sql2 = "create table boys(id INT, name varchar(20));";
            MySqlOperation.executeUpdate(sql2);
            //查询执行新建后的表格
            rs = MySqlOperation.executeQuery(sql1);
            System.out.print("新建表格后，现有的表如下：");
            while(rs.next()){
                System.out.print(rs.getString(1) + ", ");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        System.out.println();
        MySqlOperation.closeConnection();
    }

执行结果：

在服务机中查询表格，可见新增了一个名为 boys 的表格，说明执行正确

# 插入数据

先执行以下脚本，其功能是随机生成 100000 条数据，并插入到 boys 表中（执行时间略长）

USE myemployees;
DELIMITER $
CREATE PROCEDURE insert_values()
BEGIN 
	# 计数器
	DECLARE i INT DEFAULT 1;
	# 接下来随机生成一个名字串，名字长度为6位
	DECLARE str VARCHAR(6) DEFAULT '';
	DECLARE j INT DEFAULT 1;
	DECLARE const_str VARCHAR(52) DEFAULT "abcdefghigklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"; 
	tag1: WHILE i <= 100000 DO
		SET str = '';
		SET j = 1;
		tag2: WHILE j <= 6 DO
			# 每次随机生成一个字母并添加到str中
			SET str = CONCAT(str, SUBSTR(const_str, RAND()*52 + 1, 1));
			SET j = j + 1;
		END WHILE tag2;
		INSERT INTO boys VALUES(i, str);
		SET i = i + 1;
	END WHILE tag1;
		
END $
DELIMITER ;

CALL insert_values();

接下来，试着在新增的 boys 表中插入数据，就插入我的学号和姓名吧

ConnectionTest 类中的实现

/**
     * 在新增的`boys`表中插入数据(20192121026, DengZhiChao)
     * */
    @Test
    public void insertTest(){
        //创建连接
        MySqlOperation.getConnection();
        //插入前有几条数据
        ResultSet rs = MySqlOperation.executeQuery("select count(*) from boys;");
        try {
            rs.next();
            System.out.println("插入前，boys表中有" + rs.getInt(1) + "条数据");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //编写SQL语句，因为学号20192121026超过int范围了所以改成2121026
        String sql = "insert into boys values(2121026,\"DengZhiChao\");";
        //调用MySqlOperation中的方法
        MySqlOperation.executeUpdate(sql);
        //验证结果，插入后有几条数据
        rs = MySqlOperation.executeQuery("select count(*) from boys;");
        try {
            rs.next();
            System.out.println("插入后，boys表中有" + rs.getInt(1) + "条数据");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //释放连接
        MySqlOperation.closeConnection();
    }

执行结果：

在服务机中查询表格中的所有值，可见新增了一条值为 (2121026, 'DengZhiChao') 的记录，说明插入成功

# 修改数据

接下来，把刚才插入的数据 (2121026, 'DengZhiChao') 改成 (2121026, 'DzcGood')

ConnectionTest 类中的实现

/**
     * 把刚才插入的数据`(2121026, 'DengZhiChao')`改成`(2121026, 'DzcGood')`
     * */
    @Test
    public void modifyTest(){
        //建立连接
        MySqlOperation.getConnection();
        //编写SQL语句
        String sql = "update boys set name = \"DzcGood\" where id = 2121026;";
        //调用MySqlOperation中的方法
        MySqlOperation.executeUpdate(sql);
        //验证结果
        System.out.print("执行修改数据后，boys表中id为2121026对应的name为：");
        ResultSet rs = MySqlOperation.executeQuery("select name from boys where id = 2121026;");
        //输出结果
        try {
            while(rs.next()){
                System.out.println(rs.getString("name"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //释放连接
        MySqlOperation.closeConnection();
    }

执行结果：

在服务机中查询表格中的所有值，可见学号 2121026 对应的 name 值为 DzcGood ，说明修改成功

# 删除数据

接下来将数据 (2121026, 'DzcGood') 从 boys表 中删除

ConnectionTest 类中的实现

/**
     *将数据`(2121026, 'DzcGood')`从`boys表`中删除
     * */
    @Test
    public void deleteTest(){
        //建立连接
        MySqlOperation.getConnection();
        //删除前有几条数据
        ResultSet rs = MySqlOperation.executeQuery("select count(*) from boys;");
        try {
            rs.next();
            System.out.println("删除前，boys表中有" + rs.getInt(1) + "条数据");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //编写SQL语句
        String sql = "delete from boys where id = 2121026;";
        //调用MySqlOperation中的方法
        MySqlOperation.executeUpdate(sql);
        //验证结果，删除后有几条数据
        rs = MySqlOperation.executeQuery("select count(*) from boys;");
        try {
            rs.next();
            System.out.println("执行修改数据后，boys表中还有" + rs.getInt(1) + "条数据");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //释放连接
        MySqlOperation.closeConnection();
    }

执行结果：

在服务器中查询 id 为 2121026 的行数量，发现查询到的数量为 0 ，说明删除成功

# 删除数据表

接下来将 boys表 删除

ConnectionTest 类中的实现

/**
     * 删除表格boys
     * */
    @Test
    public void dropTableTest(){
        //先查询原有的表格
        MySqlOperation.getConnection();
        String sql1 = "show tables;";
        ResultSet rs = MySqlOperation.executeQuery(sql1);
        System.out.print("现有的表如下：");
        try {
            while(rs.next()){
                System.out.print(rs.getString(1) + ", ");
            }
            System.out.println();
            //删除表格boys
            String sql2 = "drop table if exists boys;";
            MySqlOperation.executeUpdate(sql2);
            //查询删除boys表格后的表格
            rs = MySqlOperation.executeQuery(sql1);
            System.out.print("删除表格后，现有的表如下：");
            while(rs.next()){
                System.out.print(rs.getString(1) + ", ");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        System.out.println();
        MySqlOperation.closeConnection();
    }

执行结果：

在服务器中执行 show tables; 命令，可以看到， boys 表格已经不存在了

# 跨数据表操作

以 join 为例，查询每个 department 对应的 manager 姓名，这里需要用到 departments 和 employees 两张表

ConnectionTest 类中的实现

/**
     *查询每个`department`对应的`manager`姓名，这里需要用到`departments`和`employees`两张表
     * */
    @Test
    public void joinTest(){
        //建立连接
        MySqlOperation.getConnection();
        //编写sql语句
        String sql = "SELECT d.department_name, e.last_name FROM departments AS d " +
                "INNER JOIN employees AS e ON d.manager_id = e.employee_id;";
        //调用MySqlOperation的方法
        ResultSet rs = MySqlOperation.executeQuery(sql);
        //输出结果
        System.out.println("department_name manager_name");
        try {
            while(rs.next()){
                System.out.print(rs.getString(1) + " ");
                System.out.println(rs.getString(2));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //释放连接
        MySqlOperation.closeConnection();
    }

执行结果：

# 额外功能

# 事务

设置 autocommit = 0 后，向 job_grades 表中插入一条数据，然后将其回滚

ConnectionTest 类中的实现

/**
     * 事务测试
     * */
    @Test
    public void transactionTest(){
        //建立连接
        MySqlOperation.getConnection();
        //设置autocommit
        MySqlOperation.setAutoCommit(false);
        //插入数据
        MySqlOperation.executeUpdate("insert into job_grades values('G', 40001, 50000);");
        //查询数据条数
        ResultSet rs = MySqlOperation.executeQuery("select count(*) from job_grades;");
        try {
            //输出
            rs.next();
            System.out.println("插入后，job_grades表共有" + rs.getInt(1) + "条数据");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //回滚
        System.out.println("开始回滚……");
        MySqlOperation.rollback();
        //查询数据条数
        rs = MySqlOperation.executeQuery("select count(*) from job_grades;");
        try {
            //输出
            rs.next();
            System.out.println("回滚，job_grades表共有" + rs.getInt(1) + "条数据");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //释放连接
        MySqlOperation.closeConnection();
    }

MySqlOperation 类中的实现

/**
     * 设置autocommit
     * @param b autocommit的值
     * */
    public static void setAutoCommit(boolean b){
        try {
            con.setAutoCommit(b);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    
    /**
     * 回滚
     * */
    public static void rollback(){
        try {
            con.rollback();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

执行结果：

在服务器端查询 job_grades 表中的数据条数，结果是 6 条，说明插入操作已被回滚

# 索引

为了让索引的效果更明显，把 boys 表再次生成一下，可以看到，有一条值为 (98000, 'XSyOwT') 的数据，接下来，通过对比创建索引前和创建索引后查询到这条数据的时间来验证索引的效果

ConnectionTest 类中的实现

/**
     * 对boys表创建索引，对比使用索引前和使用索引后的效率差别
     * */
    @Test
    public void indexTest(){
        //建立连接
        MySqlOperation.getConnection();
        String sql = "select * from boys where id = 98000;";
        System.out.println("查询id值为98000的数据……");
        long before = System.currentTimeMillis();
        MySqlOperation.executeQuery(sql);
        long after = System.currentTimeMillis();
        System.out.println("创建索引前，耗时" + (after - before) + "ms");
        //创建索引
        MySqlOperation.executeUpdate("alter table boys add index index_test(id);");
        System.out.println("创建索引后再次查询id值为98000的数据……");
        //再次查询
        before = System.currentTimeMillis();
        MySqlOperation.executeQuery(sql);
        after = System.currentTimeMillis();
        System.out.println("创建索引后，耗时" + (after - before) + "ms");
        //释放连接
        MySqlOperation.closeConnection();
    }

执行结果：
创建索引前，耗时 258ms，创建索引后，耗时 71ms，效率提升还是很明显的。

# 多用户

创建多个线程，每个线程创建一个 mysql 连接，分别对 boys 表执行查询操作，并记录每个线程所用的时间

MySqlOperation 类中的实现

/**
     * 创建size个连接对象，并返回
     * */
    public static Connection[] getConnections(int size) {
        Connection[] cons = new Connection[size];
        try {
            //注册JDBC驱动
            Class.forName(JDBC_DRIVER);
            //建立连接
            for (int i = 0; i < size; i++) {
                cons[i] = DriverManager.getConnection(DB_URL, USER, PASSWORD);
            }
            return cons;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

ConnectionTest 类中的实现

/**
     * 测试多用户执行效率
     * */
    public static void main(String[] args){
        //创建100个连接对象
        final Connection[] cons = MySqlOperation.getConnections(10);
        //存储用时
        final long[] time = new long[100];
        //编写查询语句
        final String sql = "select * from boys where id = ";
        //开启100个线程
        for (int i = 0; i < cons.length; i++) {
            final int temp = i;
            Thread thread = new Thread(new Runnable() {
                @Override
                public void run() {
                    //统计用时
                    long before = System.currentTimeMillis();
                    //查询98000，98001，98002……
                    try {
                        cons[temp].createStatement().executeQuery(sql + (98000 + temp) + ";");
                        long after = System.currentTimeMillis();
                        time[temp] = after - before;
                        System.out.println("线程" + temp +"耗时" + time[temp] + "ms");
                        //关闭连接
                        cons[temp].close();
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                }
            });
            thread.start();
        }
    }

当用户数量为 10 名时，平均耗时 140ms 左右，当用户数量增加到 100名 时，平均耗时就增加到了 200ms 左右

用户数 100 执行结果：