简介

笔记：MySQL

DataBase：简称DB

概念：长期存放在计算机内，有组织，可共享的大量数据的集合

作用：保存，安全管理数据（增删改查）

  • 关系型数据库（SQL）：
    • MySQL , Oracle , SQL Server , SQLite , DB2
    • 通过外键关联来建立表与表之间的关系
  • 非关系型数据库（NOSQL）：
    • Redis , MongoDB
    • 数据以对象的形式存储，对象之间的关系通过每个对象自身的属性来决定

DBMS：数据库管理系统 ( DataBase Management System )

SQL：

  • SQL 是处理关系数据库的标准语言，用于插入、搜索、更新和删除数据库记录。
  • SQL 关键字不区分大小写：select 与 SELECT 相同

配置

卸载：教你彻底卸载MySQL 并重装（保姆级教程 ）_mysql怎么卸载干净重装-CSDN博客

安装：MySQL超详细安装配置教程(亲测有效)_mysql安装教程-CSDN博客

安装

推荐使用压缩版

压缩包：MySQL :: Download MySQL Community Server

接下来就只有2步需要改正，其他都和上面的教程一样：

  • 按照上面教程来，直到初始化mysql：mysqld --initialize --console（用这条命令，不要用教程的，在终端里直接看密码，看不懂给AI看）

  • 接下来继续按教程，直到修改密码：

    1. ALTER USER 'root'@'localhost' IDENTIFIED BY '你的新密码';
    2. FLUSH PRIVILEGES;

我的密码：101024

IDEA 连接数据库

  1. 右边Database
  2. 左上角 + -> Data Source -> MySQL
  3. 填写user，password
  4. URL后面加上 ?serverTimezone=UTC
  5. Apply -> OK

如果驱动出现问题：左上角 Driver -> My SQL -> 选择MySQL Connect/J版本 -> Apply -> OK

数据库

三大范式

  1. **第一范式：**每列的原子性（不可再分的最小数据单元）
  2. **第二范式：**每个表只描述一件事情（前提满足第一范式）
  3. **第三范式：**每一列数据都和主键直接相关（前提满足第二范式）

规范化 vs 性能：

  • 性能比规范化更重要
  • 通过添加额外字段,以大量减少搜索时间

操作

名称 描述 命令
DDL（数据定义语言） 定义、管理数据库，数据表 create，drop，alter
DML（数据操作语言） 操作数据 insert，update，delete
DQL（数据查询语言） 查询数据 select
DCL（数据控制语言） 管理数据库语言、权限，数据更改 grant，commit，rollback

以 school 为数据库名为例

连接数据库：mysql -uroot -p101024

创建数据库：create database [if not exists] school;

删除数据库：drop database [if exists] school;

查看所有数据库：show databases;

打开数据库：use school

查看数据库中所有表：show tables;

显示student表的信息：describe student;

退出：exit;

注释：--

1
2
3
4
5
create table [if not exists] `表名`(
   '字段名1' 列类型 [属性][索引][注释],
   '字段名2' 列类型 [属性][索引][注释],
   '字段名n' 列类型 [属性][索引][注释]
)[表类型][表字符集][注释];
1
2
3
4
5
6
7
8
9
10
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 'd',
`name` VARCHAR(30) NOT NULL DEFAULT 'anonymity' COMMENT 'name',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT 'password',
`sex` VARCHAR(10) NOT NULL DEFAULT 'male' COMMENT 'sex',
`birthday` DATETIME DEFAULT NULL COMMENT 'birthday',
`address` VARCHAR(100) DEFAULT NULL COMMENT 'address',
`email` VARCHAR(50) DEFAULT NULL COMMENT 'email',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

查看数据库定义：show create database school

查看数据表定义：show create table student

显示表结构：desc student

查看 mysql 支持引擎：show engines（InnoDB，MyISAM）

功能 MyISAM InnoDB
事务处理 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间大小 较小 较大（约2倍）

数据类型

数值类型：

类型 描述 大小
tinyint 非常小 1B
smallint 较小 2B
mediumint 中等大小 3B
int（常用） 标准 4B
bigint 较大 8B
float 单精度 4B
double（常用） 双精度 8B
decimal 字符串型浮点数（金融计算） 取决于总位数和小数位数

字符串型：

类型 说明
char 0 $\leq$ M $\leq$ 255
varchar（常用） 0 $\leq$ M $\leq$ 65535
tinytext 微型文本串
text（常用） 文本串

日期时间：

类型 说明
DATE YYYY-MM-DD
TIME hh:mm:ss
DATETIME YY-MM-DD hh:mm:ss
TIMESTAMP YYYYMMDDhhmmss
YEAR YYYY

null：

  • “没有值” 或 “未知值”
  • 不要用NULL进行算术运算 , 结果仍为NULL

数据属性

**Unsigned：**无符号，该数据列不允许负数

**Zerofill：**0填充

**auto_increment：**自增

  • 通常用于设置主键 , 且为整数类型
  • 可定义起始值和步长

null 和 not null：

  • null：没有值
  • not null：必须有值

**default：**设置默认值

其他：

  1. id：主键
  2. `version`：乐观锁
  3. is_delete：伪删除
  4. gmt_create：创建时间
  5. gmt_update：修改时间

数据表

所有的数据库文件都存在 data目录下，本质是文件，一个文件夹对应一个数据库

**表名：**用 ` 包裹（反引号）

**字段名：**用 ' 包裹（单引号）

修改表名：alter table student rename as student1

增加表的字段：alter table student1 add age int

修改表的字段：

  • 修改约束：alter table student1 modify age varchar(11)
  • 重命名：alter table student1 change age age1 int

删除字段：alter table student1 drop age1

删除表：drop table if exists student1

外键

将主表的值放入从表来表示关联，所使用的值是主表的主键值，此时，从表中保存这些值的属性称为外键(foreign key)

作用：保持数据一致性

**创建：**定义外键，添加约束

  1. 创建时定义：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    CREATE TABLE parent (
        id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(50) NOT NULL,
        PRIMARY KEY (id)
    ) ENGINE=InnoDB;

    CREATE TABLE child (
        id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(50) NOT NULL,
        parent_id INT NOT NULL,
        PRIMARY KEY (id),
        KEY `FK_parent_id` (`parent_id`),
        CONSTRAINT `FK_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `parent`(`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE
    ) ENGINE=InnoDB;

  2. 创建后添加：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    CREATE TABLE parent (
        id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(50) NOT NULL,
        PRIMARY KEY (id)
    ) ENGINE=InnoDB;

    CREATE TABLE child (
        id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(50) NOT NULL,
        parent_id INT NOT NULL,
        PRIMARY KEY (id)
    ) ENGINE=InnoDB;

    ALTER TABLE child ADD KEY `FK_parent_id` (`parent_id`);

    ALTER TABLE child
    ADD CONSTRAINT `FK_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `parent`(`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE;

显式添加外键索引：KEY FK_parent_id (parent_id)

主从表同时操作：ON DELETE CASCADE，ON UPDATE CASCADE（否则操作父表外键会被拒绝）

外键不建议使用：数据库操作麻烦

DML

insert

插入语句：

  1. insert into 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3',...)
  2. 不写字段会一一匹配
  3. 插入多条数据：values 后用英文逗号隔开

update

修改数据：

  1. UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];
  2. 不指定条件，修改所有数据

where 逻辑操作符

运算符 说明
= 等于
<> 或 != 不等于
< 小于
> 大于
>= 大于等于
<= 小于等于
BETWEEN 在某个范围之间
AND 且
OR 或
NOT 非

delete

删除数据：DELETE FROM 表名 [WHERE condition];

truncate

清空表所有数据：TRUNCATE [TABLE] table_name;

区别：

  • truncate：重新设置auto_increment；不会影响事务
  • delete：
    • InnoDB：自增列从1开始
    • MyISAM：继续从上一个增量开始

DQL

select

语法：[] 为可选；{} 为必选

1
2
3
4
5
6
7
8
9
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  
  [WHERE ...]  
  [GROUP BY ...]  
  [HAVING]  
  [ORDER BY ...]  
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];

查询所有学生信息：SELECT * FROM student;

查询版本：SELECT VERSION();

查询自增步长：SELECT @@auto_increment_increment;

as

给列取别名：SELECT studentno AS num,studentname AS name FROM student;

给表取别名：SELECT studentno AS num,studentname AS name FROM student AS s;（不会对结果产生影响，在代码中方便指代字段名属于哪个表）

distinct

去重：SELECT DISTINCT studentno FROM result;

where 模糊查询

操作符 语法 说明
IS NULL a IS NULL 为null，返回true
IS NOT NULL a IS NOT NULL 不为null，返回true
BETWEEN a BETWEEN b AND c a 范围在 b 与 c 之间，返回true
LIKE a LIKE b a 匹配 b，返回true
IN a IN (a1, a2, a3, ...) a等于a1, a2中某一个，返回true

通配符：

  • %：替代零个、一个或多个字符
  • _：替代单个字符
通配符 描述
a% 以a开头
%a 以a结尾
%or% or在任意位置
_r% r在第二个位置
a_%_% 以a开头，总共至少有3个字符
a%o 以a开头，以o结尾

join

  • INNER JOIN：返回在两个表中具有匹配值的记录
  • LEFT JOIN：返回左表中的所有记录，以及右表中的匹配记录
  • RIGHT JOIN：返回右表中的所有记录，以及左表中的匹配记录
  • CROSS JOIN：返回两个表中的所有记录（结果集的行数将是两个表行数的乘积）
mysql_join 
1
2
3
4
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno

自连接

呈现父子关系：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

order by

升序：ASC（默认）

降序：DESC

MySQL 函数

数学

绝对值：SELECT ABS(-8);

向上取整：SELECT CEILING(9.4);

向下取整：SELECT FLOOR(9.4);

随机数0~1： SELECT RAND();

返回符号：SELECT SIGN(0);（负数返回-1,正数返回1,0返回0）

字符串

MySQL字符串从1开始

长度：SELECT CHAR_LENGTH('...');

合并：SELECT CONCAT('I','love','coding');

替换：SELECT INSERT('ILoveCodingHelloWorld',1,2,'VeryLove');*从某个位置开始替换某个长度）

小写：LOWER('ILoveCoding');

大写：UPPER('ILoveCoding');

从左边截取：LEFT('hello,world',5);

从右边截取：RIGHT('hello,world',5);

替换字符串：REPLACE('ILoveCodingHelloWorld','Love','VeryLove');

截取字符串：SUBSTR('ILoveCodingHelloWorld',6,11);

返回第一次出现的索引：INSTR('ILoveCoding','L');

反转：REVERSE('ILoveCoding')'

日期和时间

当前日期：SELECT CURRENT_DATE();

当前日期：SELECT CURDATE();

当前日期和时间：SELECT NOW();

当前日期和时间：SELECT LOCALTIME();

当前日期和时间：SELECT SYSDATE(); ;

年月日，时分秒：

SELECT YEAR(NOW());

SELECT MONTH(NOW());

SELECT DAY(NOW());

SELECT HOUR(NOW());

SELECT MINUTE(NOW());

SELECT SECOND(NOW());

聚合函数

满足条件的记录总和：count()

列总和：sum()

列平均值：avg()

最大值：max()

最小值：min()

事务

ACID 原则

  • **原子性：**要么全部完成，要么全部不完成
  • **一致性：**系统处于一致（转账总钱不变多）
  • **隔离性：**隔离状态执行事务
  • **持久性：**完成后，数据更改持久保存在数据库中

隔离产生的问题

  • **脏读：**读取了另一个没提交的事务
  • **不可重复读：**一个事务内读取表中的某一行数据,多次读取结果不同
  • **虚读（幻读）：**一个事务内读取到了别的事务插入的数据,导致前后读取不—致

测试

  1. SET autocommit = 0;：关闭自动提交
  2. START TRANSACTION;：开始一个事务
  3. 进行事务操作
  4. COMMIT;：提交事务 （rollback：回滚）
  5. SET autocommit = 1;：恢复自动提交
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;

CREATE TABLE `account` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(32) NOT NULL,
    `cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)


SET autocommit = 0; 
START TRANSACTION;  
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; 
# rollback;
SET autocommit = 1;

索引

帮助MySQL高效获取数据的数据结构

删除索引：DROP INDEX 索引名 ON 表名字;

删除主键索引：ALTER TABLE 表名 DROP PRIMARY KEY;

显示索引信息：SHOW INDEX FROM student;

分类

主键索引

唯一的标识，主键不可重复

唯一索引

避免重复的列出现

**和主键索引的区别：**唯一索引可以重，主键索引只能有一个（多个列都可以标识位唯一索引）

  1. 创建时添加：

    1
    2
    3
    4
    CREATE TABLE `Grade`(
      `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
      `GradeName` VARCHAR(32) NOT NULL UNIQUE
    )

  2. 创建后添加：

    1
    UNIQUE KEY `GradeID` (`GradeID`)

常规索引

默认：index 或 key 关键字设置

不宜添加太多常规索引

  1. 创建时添加：

    1
    2
    3
    4
    5
    CREATE TABLE `result`(
      `studentno` INT(4) NOT NULL,
      `subjectno`INT(11) NOT NULL AUTO_INCREMENT,
      INDEX/KEY `ind` (`studentNo`,`subjectNo`)
    )

  2. 创建后添加：

    1
    ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

全文索引

  • 只能用于CHAR , VARCHAR , TEXT数据列类型
  • 适合大型数据集

  1. 创建时添加：

    1
    2
    3
    4
    5
    6
    CREATE TABLE 表名 (
    	字段名1 数据类型 [完整性约束条件…],
    	字段名2 数据类型 [完整性约束条件…],
    	[UNIQUE | FULLTEXT | SPATIAL ]  INDEX | KEY  [索引名] (字段名[(长度)] [ASC |DESC])
    );

  2. 创建后添加：

    1
    ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ;

准则

  1. 不是越多越好
  2. 不要对经常变动的数据加索引
  3. 小数据量的表建议不要加索引
  4. 索引一般应加在查找条件的字段

索引的数据结构

  1. hash
  2. btree

用户管理

刷新权限：FLUSH PRIVILEGE

增加用户：CREATE USER '用户名' IDENTIFIED BY '密码';

重命名：RENAME USER '用户名' TO '用户名';

设置密码：SET PASSWORD = PASSWORD('密码')

为指定用户设置密码：SET PASSWORD FOR '用户名' = PASSWORD('密码')

删除用户：DROP USER '用户名'

分配权限：GRANT 权限列表 ON 库名.表名 TO '用户名'

  • all privileges：所有权限
  • *.*：所有库的所有表

查看权限：SHOW GRANTS FOR '用户名'

撤销权限：REVOKE 权限列表 ON 库名.表名 FROM '用户名'

权限类型：mysql用户权限管理：查看用户权限、授予用户权限、收回用户权限_show grant-CSDN博客

备份

导出表：mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件.sql

导出所有表：mysqldump -u用户名 -p密码 库名 > 文件.sql

导出一个库：mysqldump -u用户名 -p密码 -B 库名 > 文件.sql

导入：mysql -u 用户名 -p 数据库名 < 文件.sql

JDBC

导入

见文首博客

  1. 下载MySQL驱动 .jar 包
  2. idea建立项目，在项目目录下新建 lib 文件夹
  3. 复制 .jar 文件到 lib 目录下
  4. 右击 lib 目录，点击 Add as library...

创建数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `jdbcStudy`;

CREATE TABLE `users`(
 `id` INT PRIMARY KEY,
 `NAME` VARCHAR(40),
 `PASSWORD` VARCHAR(40),
 `email` VARCHAR(60),
 birthday DATE
);

INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES('1','zhangsan','123456','zs@sina.com','1980-12-04'),
('2','lisi','123456','lisi@sina.com','1981-12-04'),
('3','wangwu','123456','wangwu@sina.com','1979-12-04')

操作数据库

步骤

  1. 加载驱动（反射）：Class.forName
  2. 连接数据库：Connection
  3. 获取对象：Statement
  4. 执行SQL，获取结果：ResultSet
  5. 释放连接：.close()

代码

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
import java.sql.*;

public class FisrtDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");  // 固定写法，加载驱动

        //2.用户信息和 url
        // useUnicode=true&characterEncoding=utf8&useSSL=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username ="root";
        String password = "101024";

        //3.连接成功,数据库对象  Connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

        //4.执行 SQL 的对象 Statement
        Statement statement = connection.createStatement();

        //5.执行 SQL 的对象去执行 SQL,可能存在结果,查看返回结果
        String sql = "SELECT * FROM users";

        ResultSet resultSet = statement.executeQuery(sql); // 返回的结果集，封装了全部查询结果

        while(resultSet.next()){
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("name=" + resultSet.getObject("NAME"));
            System.out.println("pwd=" + resultSet.getObject("PASSWORD"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("birth=" + resultSet.getObject("birthday"));
            System.out.println("==================================================");
        }

        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();

    }

}

说明

加载驱动

Driver： 源码自动调用DriverManager.registerDriver(new com.mysql.jdbc.Driver());

连接数据库

URL：

  • MySQL：
    1. jdbc:mysql://主机地址:端口号/数据库名？参数1&参数2&参数3
    2. 端口：3306
  • Oracle：
    1. jdbc:oralce:thin:@localhost:1521:sid
    2. 端口：1521

**Connection：**代表数据库

  • 自动提交：connection.setAutocommito();
  • 事务提交：connection.commit();
  • 事务回滚：connection.rollback();

获取对象

Statement：

  • 查询：statement.executeQuery();（返回ResultSet）
  • 执行任何SQL：statement.execute();
  • 更新、插入、删除：statement.executeUpdate();（返回受影响的行数）

获取结果

ResultSet

获取数据类型：

  • resultSet.getObject()
  • resultset.getString();
  • resultse. getInt();
  • resultset.getFloat();
  • resultset.getDate();
  • resultset.getObject();

遍历：

  • resultset.next();：下一个数据
  • resultset.previous();：前一行
  • resultset.absolute(row);：指定行
  • resultset.beforefirst();：最前面
  • resultset.aftertaste();：最后面

释放资源

connection.close();

statement.close();

resultSet.close();

编辑工具类

Statement 对象

Statement对象用于向数据库发送sqL语句

statement.executeUpdate()：向数据库发送增、删、改的sql语句，返回一个整数（更改了几行）

statement.executeQuery()：向数据库发送查询语句，返回查询结果的 ResultSet对象

**注意：**无法阻止SQL注入，见PreparedStatement

CRUD

增：

1
2
3
4
5
6
Statement st = conn. createStatement();
String sql = " insert into user(…) values(…)"
int num= st.executeUpdate(sq1);
if(num>0){
    system.out. printIn("插入成功!!!")
}

删：

1
2
3
4
5
6
Statement st = conn.createStatement();
String sql = "delete from user where id=l";
int num = st.executeUpdate(sql);
if(num > 0){
   system.out.printIn("删除成功!!!"); 
}

改：

1
2
3
4
5
6
Statement st = conn.createStatement();
String sql = "update user set name='' where name =''";
int num = st.executeUpdate(sql)
if(num > 0){
	system.out.printIn("修改成功!!!");
}

查：

1
2
3
4
5
6
Statement st = conn.createStatement();
String sql = "select * from user where id=l";
ResultSet rs = st.executeQuery(sql)
while(rs.next()){
    //根据获取列的数据类型,分别调用rs的相应方法映射到java对象中
}

工具集

配置文件：

1
2
3
4
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=101024

加载、连接、释放工具：（反射）

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
package utils;

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

public class JdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");

            Properties properties = new Properties();
            properties.load(inputStream);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            // 驱动只用加载一次
            Class.forName(driver);

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

    // 获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    // 释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Statement CRUD

增：

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
package CRUD;
import utils.JdbcUtils;

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

public class TestInsert {
    public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();  // 获收数据库连接
            st = conn.createStatement();       // 获取SQL的执行对象
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES(4,'blue','123456','24736743@qq.com','2020-01-01')";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

删：

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
package CRUD;
import utils.JdbcUtils;

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

public class TestDelete {
    public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();  // 获收数据库连接
            st = conn.createStatement();       // 获取SQL的执行对象
            String sql = "DELETE FROM users WHERE id = 4";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("删除成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

改：

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
package CRUD;
import utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();  // 获收数据库连接
            st = conn.createStatement();       // 获取SQL的执行对象
            String sql = "UPDATE users SET `NAME` = 'kuangshen', `email` = '12345678@qq.com' WHERE id = 1";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("更新成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

查：

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
package CRUD;
import utils.JdbcUtils;

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

public class TestSelect {
    public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();  // 获收数据库连接
            st = conn.createStatement();       // 获取SQL的执行对象
            String sql = "SELECT * FROM users where id = 1"; // SQL

            rs = st.executeQuery(sql); // 查询完会返回一个结果集

            while (rs.next()) {
                System.out.println(rs.getString("NAME"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

PreparedStatement CRUD

防止 SQL 注入

增：

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
package plusCRUD;

import utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestInsert {

    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();

            // 区别
            //使用 ? 占位符代替参数
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
            st = conn.prepareStatement(sql);  //预编译SQL,先写sqL,然后不执行

            //手动给参数赋值
            st.setInt(1,4);
            st.setString(2,"blue");
            st.setString(3,"123456");
            st.setString(4,"24736743@qq.com");
            // 注意点:  sqL.Date   数据库    java.sql.Date
            //         utiL.Date   Java    new Date().getTime()获得时间戳
            st.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }

    }

}

删：

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
package plusCRUD;

import utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestDelete {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();

            // 区别
            //使用 ? 占位符代替参数
            String sql = "delete from users where id=?";
            st = conn.prepareStatement(sql);  //预编译SQL,先sqL,然后不执行

            //手动给参数赋值
            st.setInt(1,4);

            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("删除成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }

    }

}

改：

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
package plusCRUD;

import utils.JdbcUtils;

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

public class TestUpdate {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();

            // 区别
            //使用 ? 占位符代替参数
            String sql = "update users set `NAME` = ? where id = ?";
            st = conn.prepareStatement(sql);  //预编译SQL,先sqL,然后不执行

            //手动给参数赋值
            st.setString(1,"blue");
            st.setInt(2,1);

            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("更新成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }

    }

}

查：

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
package plusCRUD;

import utils.JdbcUtils;

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

public class TestSelect {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();   //

            String sql = "select * from users where id=?";  // 编写SQL

            st = conn.prepareStatement(sql);  //预编译

            st.setInt(1,1);   // 传递参数

            rs = st.executeQuery();   // 执行

            if (rs.next()){
                System.out.println(rs.getString("NAME"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }

}

防止SQL注入：

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
package plusCRUD;

import utils.JdbcUtils;

import java.sql.*;

public class plusSQL注入 {

    public static void main(String[] args) {
        //正常登录
        login("lisi","123456");
        login("'' or 1=1","'' or 1=1");
    }

    // 登录业务
    public static void login(String username, String password){

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();  // 获收数据库连接
            // PreparedStatement 的 SQL注入的本质,把传递进米的参数当做字符
            //假设其中在在转义字符,比如说'会被直接转义
            String sql = "select * from users where `NAME`=? and `PASSWORD`=?";

            st = conn.prepareStatement(sql);
            st.setString(1,username);
            st.setString(2,password);

            rs = st.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }

    }

}

事务

  1. 开启事务：conn.setAutoCommit(false);
  2. 提交事务：conn.commit();
  3. 定义回滚：catch 语句中默认失败回滚
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
package transaction;

import utils.JdbcUtils;

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

public class Transaction {
    public static void main(String[] args) {

        Connection  conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            // 关闭数据库的自动提交,自动会开启事务
            conn.setAutoCommit(false);  // 开启事务

            String sql1 = "update account set money = money - 100 where name = 'A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            // int x = 1/0;
            String sql2 = "update account set money = money + 100 where name = 'B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            // 业务完毕，提交事务
            conn.commit();
            System.out.println("success");
        } catch (SQLException e) {
            // 如果失败，默认回滚
//            try {                       //如果失败，回滚事务（显式定义）
//                conn.rollback();
//            } catch (SQLException e1) {
//                e1.printStackTrace();
//            }
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}

连接池

数据库连接 — 执行完毕 — 释放

连接 - 释放：十分浪费系统资源

池化技术：准备一些预先的资源，过来就连接预先准备好的

开源数据源：

  1. DBCP
  2. C3P0
  3. Druid

配置：（例子）

  • 最小连接数：10
  • 最大连接数：15
  • 等待超时：100ms

核心：实现接口 dataSource

DBCP

**导入：**3个包 commons-dbcp、commons-pool、commons-logging

  1. 导入DBCP
  2. 现在还需要再导入 commons-logging 包：Download Apache Commons Logging – Apache Commons Logging

配置文件：dbcpconfig.properties

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=101024

initialSize=10

maxActive=50

maxIdle=20

minIdle=5

maxWait=60000

connectionProperties=useUnicode=true;characterEncoding=gbk

defaultAutoCommit=true

defaultReadOnly=

defaultTransactionIsolation=READ_UNCOMMITTED

加载、连接、释放工具：

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
package DBCP.utils;

import org.apache.commons.dbcp2.BasicDataSourceFactory;

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

public class JdbcUtils_DBCP {
    private static DataSource dataSource = null;

    static {
        try {
            InputStream inputStream = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");

            Properties properties = new Properties();
            properties.load(inputStream);
            // 创建数据源 工厂 -- > 创建
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    // 释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

增：

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
package DBCP;

import DBCP.utils.JdbcUtils_DBCP;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestDBCP {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils_DBCP.getConnection();

            // 区别
            //使用 ? 占位符代替参数
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
            st = conn.prepareStatement(sql);  //预编译SQL,先写sqL,然后不执行

            //手动给参数赋值
            st.setInt(1,4);
            st.setString(2,"red");
            st.setString(3,"123456");
            st.setString(4,"24736743@qq.com");
            // 注意点:  sqL.Date   数据库    java.sql.Date
            //         utiL.Date   Java    new Date().getTime()获得时间戳
            st.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils_DBCP.release(conn,st,rs);
        }
    }
}

C3P0

导入：c3p0、mchange-commons-java 包 导入c3p0

配置文件：c3p0-config.xml（在 src 目录下建）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--默认配置-->
    <default-config>
        <!--数据库驱动-->
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <!--数据库的url-->
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;serverTimezone=UTC</property>
        <!--用户名写自己的-->
        <property name="user">root</property>
        <!--密码写自己的-->
        <property name="password">101024</property>

        <property name="acquireIncrement">5</property>
        <!--初始连接数-->
        <property name="initialPoolSize">10</property>
        <!--最大连接数-->
        <property name="maxPoolSize">20</property>
        <!--最小连接数-->
        <property name="minPoolSize">5</property>
    </default-config>
</c3p0-config>

加载、连接、释放工具：

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
package C3P0.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.*;

public class JdbcUtils_C3P0 {
    private static ComboPooledDataSource dataSource = null;

    static {
        try {
            // 创建数据源 工厂 -- > 创建
            dataSource = new ComboPooledDataSource(); // 不写参数代表默认的数据源
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    // 释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

增：

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
package C3P0;

import C3P0.utils.JdbcUtils_C3P0;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestC3P0 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils_C3P0.getConnection();

            // 区别
            //使用 ? 占位符代替参数
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
            st = conn.prepareStatement(sql);  //预编译SQL,先写sqL,然后不执行

            //手动给参数赋值
            st.setInt(1,5);
            st.setString(2,"green");
            st.setString(3,"123456");
            st.setString(4,"24736743@qq.com");
            // 注意点:  sqL.Date   数据库    java.sql.Date
            //         utiL.Date   Java    new Date().getTime()获得时间戳
            st.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils_C3P0.release(conn,st,rs);
        }
    }
}