1. MySQL(关系型数据库)

Databases 简称：DB

1.1 什么是数据库？

  • 用于存储和管理数据的仓库

1.2 数据库的特点：

  • 持久化存储数据。其实数据库就是一个文件系统。
  • 方便存储和管理数据。
  • 使用了统一的方式操作数据库。 —SQL

1.3 安装过程

  1. 官网下载zip文件（Downloads-MySQL Community Edition-MySQL Community Server-ZIP Archive）
  2. 直接解压到对应的文件夹。
  3. 新建my.ini配置文件（在bin同级目录下）。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[mysql]

# 设置mysql客户端默认字符集

default-character-set=utf8 

[mysqld]

#设置3306端口

port = 3306 

# 设置mysql的安装目录

basedir=D:\Software\MySQL\mysql-8.0.17-winx64

# 设置mysql数据库的数据的存放目录

datadir=D:\Software\MySQL\mysql-8.0.17-winx64\data

# 允许最大连接数

max_connections=200

# 服务端使用的字符集默认为8比特编码的latin1字符集

character-set-server=utf8

# 创建新表时将使用的默认存储引擎

default-storage-engine=INNODB
  1. 使用管理员权限打开cmd窗口，切换到对应的bin目录下，使用命令。
1
2
3
mysqld --install
mysqld --initialize
net start mysql
  1. 在MySQL安装目录下搜索.err文件，其中localhost后面的字符对应初始密码。
  2. alter user user() identified by "123456";更改密码。

安装的MySQL实际上是一个服务，在计算机管理中的服务中来启动和停止MySQL的服务。cmd —> service.msc

net start mysql

net stop mysql

1.4 MySQL的登录和退出

登录：

  1. mysql -u 用户名 -p
  2. mysql -h ip地址 -u 用户名 -p

退出：

  1. exit
  2. quit

2. SQL

2.1 什么是SQL？

Structured Query Language:结构化查询语言.其实就是定义了操作所有关系型数据库的规则。

2.2 SQL通用语法

  1. SQL语句可以单行或多行书写，以分号结尾。
  2. 可使用空格和缩进来增强语句的可读性。
  3. MySQL数据库的SQL语句不区分大小写，但关键字建议用大写。
  4. 注释
    • 单行注释：— 注释内容
    • 多行注释：/ 注释 /

3. 使用SQL操作数据库和表

3.1 操作数据库

一般都有增查改删(CRUD, Create, Retrieve, Update, Delete)四个操作.

展示已有数据库

1
SHOW DATABASES;

查询当前正在使用的数据库

1
SELECT DATABASE();

新建数据库java

1
CREATE DATABASE java;

使用新建的java数据库

1
USE java;

删除数据库java

1
DROP DATABASE java;

这样删除有一个缺点，如果不存在java数据库时执行这条命令会报错，可以使用下面的方法。

1
DROP DATABASE IF EXISTS java;

3.2 操作表

同样地，也是进行增查改删。

MySQL数据类型：

  1. int：整数类型
    age int;
  2. double：小数类型
    score double(5, 2) — 5表示数字最长有5位，2表示保留2位小数
  3. date：日期，只包括年月日，yyyy-MM-dd
  4. datetime：日期，包括年月日时分秒，yyyy-MM-dd HH:mm:ss
  5. timestamp：时间戳类型，包含年月日时分秒， yyyy-MM-dd HH:mm:ss
    如果将来不给这个字段赋值，或复制为null，则默认使用当前系统时间
    注意，添加时间戳类型，需要把默认值设置为current_timestamp(当前时间戳)才能正确获取到当前系统时间。
     ALTER TABLE stu ADD update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
  6. varchar：字符串
    name varchar(20); — 姓名最大20个字符
    zhangsan 8个字符； 张三 2个字符

新建表personInfo

1
2
3
4
5
6
CREATE TABLE personInfo (
    id INT auto_increment,
    name VARCHAR(20),
    age INT,
    PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;

PRIMARY KEY为主键约束，为了便于更快的查找表中的数据，都会在表中设置一个主键。
注意：(设置的时候记得带上括号👀)

  • 每个表只能定义一个主键。
  • 不能为NULL。

新增数据

1
INSERT INTO personInfo VALUES (NULL, '小明', 23);

因为id设置了自增，所以可以插入null

查询数据

1
SELECT * FROM personInfo;

也可以根据条件查询

1
SELECT * FROM personInfo WHERE id=1;

分页查询

1
SELECT * FROM personInfo LIMIT 0, 5;

这条命令表示从第1条数据开始，共查五条。

修改数据

1
UPDATE personInfo SET age=25 WHERE name='小明';

删除数据

1
DELETE FROM personInfo WHERE id=1;

没有条件时则删除表中全部数据！

删除多条数据可以使用类似分页查询的limit，但后面只跟一个参数count，即删除多少条数据

1
DELETE FROM personInfo LIMIT 5;

其它的一些知识(例如外键约束，事务等等)等到用到了再进行整理与学习🧑

4. JDBC

JDBC(Java DataBase Connection)通过Java访问数据库。

4.1 使用Maven新建项目

选择quickstart模板新建项目。

在pom.xml配置文件中添加依赖

1
2
3
4
5
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>

这里我踩了两个坑！首先是添加的依赖版本和我使用的MySQL版本不同，建立连接报错为java.sql.SQLException: java.lang.ClassCastException: java.math.BigInteger cannot be cast to java.lang.Long，然后我把版本修改为一致，结果建立连接继续报错😥，报错信息为The server time zone value ‘�й���׼ʱ��’ is unrecognized or represents more than one time zone.提示为时区问题，需要通过连接设置serverTimezone来解决。使用格林威治时间设置为serverTimezone=GMT;北京时间则为serverTimezone=GMT%2B8。

4.2 Hello JDBC

4.2.1 初始化驱动并建立连接

需要提供：

  • 数据库所处的ip：127.0.0.1或localhost(本机)
  • 数据库工作的端口号：3306(MySQL专用端口号)
  • 数据库名称：personInfo
  • 编码方式：UTF-8
  • 账号：root
  • 密码：admin
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import java.sql.*;

public class TestJDBC {
    
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("初始化驱动成功！");
            Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/personInfo?characterEncoding=UTF-8&serverTimezone=GMT", "root", "admin");
            System.out.println("连接成功，获取连接对象：" + c);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

如果看到控制台输出下面内容，则建立连接成功。

Class.forName和new类似，都是创建一个实例，但Class.forName的好处是：如果成功完成，那么这个类已经加载且已经连接。

4.2.2 新建Statement运行SQL语句

例如在表中新增一条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
import java.sql.*;

public class TestJDBC {
    
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("初始化驱动成功！");
            Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/personInfo?characterEncoding=UTF-8&serverTimezone=GMT", "root", "admin");
            System.out.println("连接成功，获取连接对象：" + c);
            Statement s = c.createStatement();
            String sql = String.format("INSERT INTO personInfo VALUES (NULL, %s, %d)", "'小刚'", 18);
            s.execute(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

注意：name对应的字段需要先加单引号，再加上双引号作为java字符串😜

增改删均使用Statement实例的execute方法；

而查询则使用executeQuery方法，将返回一个ResultSet，通过next方法移动光标，遍历内容。

4.2.3 关闭连接

用完之后记得养成关闭的好习惯，否则造成对资源的占用。

先关Statement，再关Connection。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
import java.sql.*;

public class TestJDBC {
    
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("初始化驱动成功！");
            Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/personInfo?characterEncoding=UTF-8&serverTimezone=GMT", "root", "admin");
            System.out.println("连接成功，获取连接对象：" + c);
            Statement s = c.createStatement();
            String sql = String.format("INSERT INTO personInfo VALUES (NULL, %s, %d)", "'小刚'", 18);
            s.execute(sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (s != null) {
                try {
                    s.close();
                } catch (SQLException) {
                    e.printStackTrace();
                }
            }
            if (c != null) {
                try {
                    c.close();
                } catch (SQLException) {
                    e.printStackTrace();
                }
            }
        }
    }

}

如果觉得这样关闭比较繁琐，可以参考关闭流的方式，使用try-with-resource的方式自动关闭连接，因为Connection和Statement都实现了AutoCloseable接口。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
import java.sql.*;

public class TestJDBC {
    
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("初始化驱动成功！");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/personInfo?characterEncoding=UTF-8&serverTimezone=GMT", "root", "admin");
            System.out.println("连接成功，获取连接对象：" + c);
            Statement s = c.createStatement()) {
            String sql = String.format("INSERT INTO personInfo VALUES (NULL, %s, %d)", "'小刚'", 18);
            s.execute(sql);
        }  catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

5. 高阶操作

5.1 使用PreparedStatement

PreparedStatement继承了Statement，也是用来执行sql语句的，但是它有几个好处：

  1. 参数设置：
    Statement需要进行字符串拼接，容易出错；
    PreparedStatement可以使用？占位符，后期进行设置，易维护。
  2. 性能表现：
    插入多条数据时，PreparedStatement比Statement更快。
  3. 防止SQL注入式攻击
    例如name是用户提交的数据”name=’小明’ OR 1=1”
    此时使用Statement进行字符串拼接，得到的SQL语句是”SELECT * FROM personInfo WHERE name=’小明’ OR 1=1”，由于1=1是恒成立的条件，所以这样就会把所有数据都查出来，而不仅仅名字为小明的数据。如果表中的数据是海量的，那么将会把内存全部消耗光，造成极大的不便；而使用PreparedStatement，使用？对name进行占位，那么即使用户输入了”name=’小明’ OR 1=1”，预编译也会将它作为一整个字段放在name处，结果就是什么都查不到，而不会出现内存占满的情况。
1
2
3
4
5
String sql = "SELECT * FROM personInfo WHERE name=?";
// c是Connection，这里没写对应的代码
PreparedStatement ps = c.prepareStatement(sql);
ps.setString(1, "小明");
ps.execute();

注意：ResultSet和PreparedStatement是这里唯二索引从1开始数的，不要弄错啦🤓

个人理解如果要执行的sql语句不用设置参数就直接使用Statement，需要就用PreparedStatement🤗

5.2 事务

事务相当于打包多个操作，这多个操作要么都成功，要么都失败，不会出现只执行其中一个操作的情况。例如业务操作需要将一个人的年龄加一再减一，不用事务的话，减一sql语句写错了，结果就是年龄将会加一；而使用事务，其中一个语句出错，那么整个事务都不会执行，年龄信息将不变。

通过c.setAutoCommit(false);关闭自动提交

使用c.commit();进行手动提交

此时，增改删语句execute后没有真正生效，需要提交后才会生效。

这里MySQL需要使用innodb存储引擎，在my.ini配置文件中设置default-storage-engine=INNODB，在MySQL中执行下面的语句SELECT SUPPORT FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE='InnoDB';，如果innodb被启用并且是默认数据库，那么会出现SUPPORT-DEFAULT，如果innodb可用，但不是默认引擎，则结果是yes；否则是no。(我的MySQL版本是8.0.17，使用此方法可行，删除data文件夹下的ib文件不可用。)

5.3 ORM和DAO

ORM(Object Relationship Database Mapping)，对象和关系数据库的映射。即一个对象对应数据库的一条记录。

通过将对象看作一条数据，使用java对对象和记录相互操作。

DAO(Data Access Object)，数据访问对象。和ORM思路相同，把数据库相关的操作都封装在这个类中，其它地方看不到JDBC的代码。

新建PersonInfo类

1
2
3
4
5
6
7
public class PersonInfo {

    public int id;
    public String name;
    public int age;

}

新建DAO接口(写成泛型格式)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
public interface DAO<T> {

    // 增添数据
    public void add(T t);

    // 查询单个数据
    public T get(int id);
    // 查询全部数据
    public List<T> list();
    // 分页查询
    public List<T> list(int start, int count);

    // 修改数据
    public void update(T t);

    // 删除数据
    public void delete(int id);

}

新建PersonInfoDAO类

初始化驱动放在构造方法中，使用getConnection方法获取与数据库的连接。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
package com.ssl;

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

public class PersonInfoDAO implements DAO<PersonInfo> {

    // 初始化驱动放在构造方法中
    public PersonInfoDAO() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    // 建立连接
    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8&serverTimezone=GMT", "root", "admin");
    }

    @Override
    public void add(PersonInfo personInfo) {
        String sql = "INSERT INTO personInfo VALUES (NULL, ?, ?)";
        try (Connection c = getConnection();
             PreparedStatement ps = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, personInfo.name);
            ps.setInt(2, personInfo.age);
            ps.execute();

            // 添加完后将自增的id再填回personInfo中
            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()) {
                personInfo.id = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            System.out.println("插入数据成功！");
        }
    }

    @Override
    public PersonInfo get(int id) {
        PersonInfo person = null;
        String sql = "SELECT * FROM personInfo WHERE id=?";
        try (Connection c = getConnection();
        PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                String name = rs.getString("name");
                int age = rs.getInt("age");
                person = new PersonInfo();
                person.id = id;
                person.name = name;
                person.age = age;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return person;
    }

    @Override
    public List<PersonInfo> list() {
        return list(0, Short.MAX_VALUE);
    }

    @Override
    public List<PersonInfo> list(int start, int count) {
        List<PersonInfo> personList = new ArrayList<>();
        String sql = "SELECT * FROM personInfo LIMIT ?, ?";
        try (Connection c = getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setInt(1, start);
            ps.setInt(2, count);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                PersonInfo person = new PersonInfo();
                person.id = id;
                person.name = name;
                person.age = age;
                personList.add(person);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return personList;
    }

    public int getTotal() {
        String sql = "SELECT COUNT(*) FROM personInfo";
        int total = 0;
        try (Connection c = getConnection();
             Statement s = c.createStatement()) {
            ResultSet rs = s.executeQuery(sql);
            while (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    @Override
    public void update(PersonInfo personInfo) {
        String sql = "UPDATE personInfo SET name=?, age=? WHERE id=?";
        try (Connection c = getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, personInfo.name);
            ps.setInt(2, personInfo.age);
            ps.setInt(3, personInfo.id);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            System.out.println("修改成功！");
        }
    }

    @Override
    public void delete(int id) {
        String sql = "DELETE FROM personInfo WHERE id=?";
        try (Connection c = getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setInt(1, id);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            System.out.println("删除成功！");
        }
    }
}

6. 参考