黑马程序员技术交流社区

标题: 石家庄SQL数据库 [打印本页]

作者: 李迪13524 时间: 2018-1-11 15:22
标题: 石家庄SQL数据库
# MySQL SQL语句

## 数据库概念

* 数据库:
* 存储数据的仓库. 本质是一个文件系统, 数据按照指定的格式将数据存储起来, 用户可以对数据库中的数据进行增删改查
* DBMS:
* Database Management System, 数据库管理系统. 是操作和管理数据库的大型软件, 用于建立, 使用和维护数据库, 对数据库进行统一管理和控制, 以保证数据库的安全性和完整性. 用户通过数据库管理系统访问数据库中表内的数据

## 常见的数据库

* 常见关系型数据库(应用程序)
* MySQL: **常用**. Oracle开源免费小型数据库, 普通企业都在用
* Oracle: **常用**, Oracle收费大型数据库, 一般大型国企, 银行, 政府部门用
* DB2: IBM出品收费数据库. 一般大型国企, 银行, 政府部门用
* SQLServer: 微软出品收费数据库. C#, .NET使用
* SQLite: 嵌入式数据库, 用于移动设备, 机顶盒等, 安卓手机就是使用的SQLite数据库
* SyBase: 没了
* 扩展: 目前的数据库分类:
* 关系型数据库: 表示表与表之间的关系
* MySQL, Oracle都是关系型数据库
* 非关系型数据库: (NoSQL, Not Only SQL)
* MongoDB
* 用途: 文档信息的存储, 日志, 记录, 博客文章

## 数据库和管理系统

* 作用
* 数据库: 存储**数据表**, 索引, 视图等
* 数据表: 存储数据
* 行: 数据
* 列: 描述数据信息
* 数据: 也叫**记录**, 也就是我们实际要存储的信息
* 数据库管理系统: 操作数据库, 表, 记录
* 记住关系:
* 数据库程序有数据库
* 数据库中有表
* 表中有记录
* 数据库程序 > 库 > 表 > 记录

* 便于理解:

```
# 数据库和仓库的关系

* 京东(数据库程序MySQL)
* 京东的工人(DBMS)                   --- mysqld.exe
* 建立了多个仓库                      --- 数据库
* 每个仓库中有多个货架                --- 数据表
* 每个货架上可以存放商品             --- 数据
* 数据库包含数据表, 数据表包含数据
* 京东的工人(DBMS)
* 工人建立了石家庄仓库, 北京仓库       --- 创建了2个数据库
* 工人往石家庄仓库中放入了一个手机货架 --- 在名为"石家庄"的数据库中创建名为"手机"的数据表
* 工人往手机货架上放入了500个大米手机 --- 在名为"石家庄"数据表中插入了500条记录

# 数据库和Excel表格的关系

* 电脑上有一个目录专门存入Excel文件
* 我们使用Excel编辑单元格, 就相当于DBMS在操作数据
```

## 数据表和Java中的类的对应关系

* 面向对象思想可以让我们把现实事物映射为Java的类, 那数据表能不能映射为Java的类呢?
* 数据表和Java类的关系
* 表: Java类
* 字段名: 类的成员属性
* 记录: 类的对象
* 对应关系有什么用
* 有了这种映射关系, 以后我们会直接通过特殊的方式将JavaBean的各个属性一一对应存入数据库
* 我们在设计表时, 也会参考这种面向对象的方式, 设计表的字段(也就是事物的属性)

```
+---------------------+
|    Person表    | 表名 --- Java类名
+------+-----+--------+
| name | age | gender | 列名 --- 类中的属性
+------+-----+--------+
| 张三  |  13 | 女 | 记录1 --- 类的对象1
| 李四  |  14 | 女 | 记录2 --- 类的对象2
| 王五  |  15 | 女 |
| 赵六  |  16 | 女 |
| 田七  |  17 | 女 |
+------+-----+--------+

// 将表转换为Java类
public class Person {

private String name;
private int age;
private String gender;

public Person(String name, int age, String gender) {
// this.xxx = xxx;
}
}

// 创建对象
Person person1 = new Person("张三", 13, "女");
```

## 数据表和Java类对应关系举例: 用户表

(明白关系即可)

---

## MySQL数据库安装

* 对于MySQL程序的简单介绍
* MySQL分为两个部分
* 服务端: 主要是负责数据的存储
* mysqld.exe
* 客户端: 连接服务端发出操作指令
* 命令行界面: 安装完自带的
* mysql.exe
* GUI界面: SQLyog, Navicat, Sequel pro
* 我们安装的MySQL程序实际安装的是两个端(服务端, 客户端)
* 查看doc安装说明

## 查看MySQL运行在系统服务中

* `Win + R`, 输入`services.msc`, 回车, 打开服务管理器, 找到MySQL服务, 查看打开状态
* 进程管理程序查看`mysqld.exe`, (MySQL deamon)该程序就是MySQL的系统服务
* 服务程序的启动和停止(必须以管理员权限运行cmd):
* 启动: `net start mysql`
* 停止: `net stop mysql`

## MySQL登录和退出

* 打开命令行:

```
# 登录方式1: 先输入用户名再输入密码
mysql -u 用户名 -p
Enter password: 输入密码

# 登录方式2: 输入用户名后直接输入密码
mysql -u用户名 -p密码

# 退出mysql客户端
exit
```

## SQLyog图形界面客户端

## SQL语句介绍和分类

* 什么是SQL: Structured Query Language, 结构化查询语言.
* 为什么叫`结构化查询`: 因为数据表是有结构的, 有行和列, 对行和列进行查询
* SQL语言作用: 和DBMS通信, 操作数据库, 表, 记录
* SQL语言按照作用可以划分为4类:
* DDL: Data Defination Language, 数据定义语言, 用来定义数据库对象(库, 表等)
* `CREATE`
* `ALTER`
* `DROP`
* DML: Data Manipulation Language, 数据操作语言, 对表中的记录进行增, 删, 改
* `INSERT`
* `DELETE`
* `UPDATE`
* DQL: Data Query Language, 数据查询语言, 对表中的记录进行查
* `SELECT`
* `FROM`
* `WHERE`
* DCL: Data Control Language, 数据控制语言, 创建修改用户, 权限
* SQL语法特点
* SQL语句不区分大小写. (默认情况数据也不区分大小写, 但可以设置)
* 但按照惯例, SQL关键字用大写; 数据库名, 表名, 字段名等用小写, 这样便于阅读
* 一条SQL语句可以单行或多行书写, 一条语句的结尾必须以`;`分号结尾
* SQL语句的单词之间通过空格分隔, 参数之间使用`,`逗号分隔
* 字符串和日期要使用`''`单引号或`""`双引号包裹, 推荐单引号
* SQL注释的2+1种

```sql
#单行注释
-- 单行注释, 减号后必须有一个空格

/*
多行注释
*/
```

## 数据表中的数据类型

* 常用数据类型:
* `INT`: 整数
* `DOUBLE`: 浮点数(小数)
* `VARCHAR`: 存储空间是可变的, 可变长度字符
* `CHAR`: 存储空间是固定的, 固定长度字符
* `DATE`: 日期类型, `yyyy-MM-dd`格式的字符串
* 扩展: 除此之外常用的日期时间类型还有如下:
* `TIME`: 时间类型, `HH:mm:ss`格式的字符串
* `DATETIME`: 日期时间类型, `yyyy-MM-dd HH:mm:ss`格式的字符串. 如果没有指定值则默认值是null
* `TIMESTAMP`: 时间戳类型, `yyyy-MM-dd HH:mm:ss`格式的字符串. 如果没有指定值则默认值是当前系统时间
* 扩展:
* 数据类型的作用: 用于开辟内存空间
* CHAR和VARCHAR的区别
* VARCHAR(200), 可变长度字符, 存储占用的内存空间是可变的, 如果实际数据容量小于指定的字符长度大小, 那么就会按照实际的字符数来开辟内存大小
* 优点: 节省内存
* 缺点: 因为每次存入数据时都会判断数据的实际大小, 来动态修改内存空间, 所以需要占用资源, 效率低
* CHAR(200), 固定长度字符, 存储占用的内存空间是不可变的. 无论实际存入的数据容量是多大, 都占用这么大的空间
* 优点: 插入数据时不需要额外操作, 效率高
* 缺点: 可能会浪费空间

## SQL语句创建数据库操作

* 创建数据库:
* 直接创建数据库:
* `CREATE DATABASE 库名;`
* 默认使用安装MySQL时设置的UTF-8编码
* 创建数据库同时指定字符集:
* `CREATE DATABASE 库名 CHARACTER SET '字符集';`
* 创建数据库同时指定字符集和字符校对规则:
* `CREATE DATABASE 库名 CHARACTER SET '字符集' COLLATE '校对规则';`
* 校对规则可以指定是否区分大小写字母, `SHOW COLLATION LIKE 'utf8%';`可以查看支持的校对规则, 其中`-ci`表示CASE INSENSITIVE, 即大小写不敏感
* 默认是`utf8_general_ci`, 若要大小写敏感可以修改为`utf8_bin`
* 删除指定数据库
* 语句: `DROP DATABASE 库名;`
* 删了能反悔吗? 不能
* 查看所有数据库
* 语句: `SHOW DATABASES;`
* MySQL自带的数据库不要擅自修改和删除
* 使用指定数据库(相当于进入数据库内, 然后就可以操作该数据库中的数据表)
* 语句: `USE 库名;`

* 扩展
* 查看当前正在使用的数据库
* 语句: `SELECT DATABASE();`
* 查看指定数据库的建库信息:
* 语句: `SHOW CREATE DATABASE 库名;`
* 修改数据库
* 修改字符集: `ALTER DATABASE 库名 CHARACTER SET '新字符集';`
* 修改校对规则: `ALTER DATABASE 库名 COLLATE '新校对规则';`

* 5分钟练习:
* 使用CREATE DATABASE 库名;创建一个数据库db_drop
* 使用SHOW DATABASES;查看是否创建成功
* 使用DROP DATABASE 库名;删除自己创建的数据库
* 使用SHOW DATABASES;再次查看是否已经删除

```sql
mysql> CREATE DATABASE db_drop;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| db_drop          |
| mysql             |
| performance_schema |
| test             |
+--------------------+
5 rows in set (0.00 sec)

mysql> DROP DATABASE db_drop;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| mysql             |
| performance_schema |
| test             |
+--------------------+
4 rows in set (0.00 sec)
```

---

## 在数据库中创建数据表

* 注意: 创建表的操作必须先要使用一个数据库: `USE 库名;`
* 建表语句格式:

```sql
CREATE TABLE 表名 (
  列名数据类型(长度) 约束,
  列名2 数据类型(长度) 约束,
  ...
  列名n 数据类型(长度) 约束
);

// VARCHAR和CHAR一般需要手动指定长度, 日期数字等其他类型有默认长度一般不需要指定
```

* 建表语句的注意事项:
* 语句可以写在一行内, 但为了阅读性, 一般分行写
* 不同字段定义之间必须以`,`逗号分隔, 最后一个定义不要逗号

## 约束

* 约束:
* constraint
* 作用: 就是对列中记录的值进行限制, 如果插入的数据不符合约束规则, 则该操作会被立刻终止
* 扩展:
* 约束的种类:
* **主键约束**: `PRIMARY KEY`, 限制该列的记录值不能为空且唯一(不能重复), 相当于同时设定了`NOT NULL`和`UNIQUE`
* **自增约束**: `AUTO_INCREMENT`, 设置该列的记录值可以自动增加
* 默认从几开始? 1
* 能用于什么类型? 只能用于数字类型(如INT, DOUBLE, 小数1.23自增后是2)
* 外键约束: `FOREIGN KEY`, 主要是连接另一个表的主键
* 非空约束: `NOT NULL`, 限制该列的记录值不能为空
* 唯一约束: `UNIQUE`, 唯一, 限制该列的记录值不能重复
* 检查约束: `CHECK (条件)`, 限制该列插入的记录值是否符合要求
* 默认值约束: `DEFAULT 值`, 限制该列记录如果没有插入值, 则使用默认值

## SQLyog中SQL代码的保存

* 在SQLyog的Query选项卡中
* `Ctrl + s`, 保存SQL语句到文件
* `Ctrl + o`, 打开.sql文件, 导入SQL语句

## 创建用户表

* 代码演示

```sql
# 创建数据库mybase
CREATE DATABASE mybase;

# 使用库
USE mybase;

# 建表
CREATE TABLE users (
  uid INT,
  uname VARCHAR(20),
  uaddress VARCHAR(200)
);
```

## 主键约束

* 主键约束
* 格式: `PRIMARY KEY`
* 作用: 限制该列非空且唯一
* 自增约束
* 格式: `AUTO_INCREMENT`
* 作用: 使该列的值按照数字规律自动增长

* 5分钟练习:
* 建库mybase
* 使用该库
* 创建用户表users
* 字段
* 用户编号: uid INT PRIMARY KEY AUTO_INCREMENT
* 姓名: uname VARCHAR(20)
* 用户地址: uaddress VARCHAR(200)


```sql
# 建库
CREATE DATABASE mybase;

# 使用库
USE mybase;

# 创建用户表
CREATE TABLE users (
  uid INT PRIMARY KEY AUTO_INCREMENT,
  uname VARCHAR(20),
  uaddress VARCHAR(200)
);

# 执行语句: 选中SQL语句, 点击左上角的一个三角的蓝色圆形按钮, 或按快捷键F9
# 多条SQL语句可以全部选中批量执行, 但注意: 每条语句必须有;分号结尾, 否则无法识别出是几条语句
# 执行完语句后, 在左侧空白处右键, 选择刷新对象浏览器, 即可看到变更
```

---=

## 常见表操作

* 列出当前数据库的所有表
* `SHOW TABLES;`
* 查看表的结构
* `DESC 表名;`: desc=description描述
* 删除表
* `DROP TABLE 表名;`

## 修改表的结构

* 修改表的结构
* 格式: `ALTER TABLE 表名操作关键字列名数据类型约束;`
* 操作关键字
* `ADD`: 增加列.
* `ALTER TABLE 表名 ADD 新列名数据类型约束;`
* `MODIFY`: 修改指定列的数据类型和约束(不能修改列名).
* `ALTER TABLE 表名 MODIFY 要修改的列名新数据类型新约束;`
* `CHANGE`: 修改指定列的列名+数据类型+约束.
* `ALTER TABLE 表名 CHANGE 旧列名新列名新数据类型新约束;`
* `DROP`: 删除列.
* `ALTER TABLE 表名 DROP 列名;`
* 修改表名
* 格式: `RENAME TABLE 旧表名 TO 新表名;`

## 数据表添加记录: 格式1

* 插入记录的格式1: 手动指定主键列的值
* 注意: 字段数量必须和值数量一一对应
* 该方法适用于给指定列插入值

```sql
# 格式1

INSERT INTO 表名
(列名1, 列名2,..., 列名n)
VALUES
(值1, 值2, ..., 值n);
```

* 5分钟练习:
* 使用库mybase
* 创建产品表product并插入数据
* 字段
* 序号: pid INT PRIMARY KEY AUTO_INCREMENT
* 产品名称: pname VARCHAR(100) NOT NULL
* 产品价格: price DOUBLE
* 插入数据
* 1, '笔记本', 5555.99
* 2, '智能手机', 9999
* 2个数据指定相同的pid有什么错误?
* Duplicate entry '1' for key 'PRIMARY': 该列是主键(不能重复且非空)

```sql
# 使用库mybase
USE mybase;

# 建表
CREATE TABLE product (
  pid INT PRIMARY KEY AUTO_INCREMENT,
  pname VARCHAR(100) NOT NULL,
  price DOUBLE
);

# 插入记录
INSERT INTO product
(pid, pname, price)
VALUES
(1, '笔记本', 5555.99);

INSERT INTO product
(pid, pname, price)
VALUES
(2, '智能手机', 9999);
```

## 数据表添加记录: 其他格式

* 插入记录格式2: 不指定主键, 让其自增
* 当设置了主键自增后, 插入记录时可以不考虑主键字段的值. 即字段名和值都没有主键这一列. 同时可以避免产生主键重复的错误
* 该方式适用于设置了主键自增(AUTO_INCREMENT)的情况

```sql
INSERT INTO 表名
(非自增列名1, 非自增列名2, ..., 非自增列名n)
VALUES
(值1, 值2, ..., 值n);
```

* 插入记录格式3: 不指定列名, 给所有列添加值
* 不指定列名, 为所有列添加值
* 该方式适用于为所有列添加值, 可以省去写列名. 但**不能省略主键**

```sql
INSERT INTO 表名
VALUES
(值1, 值2,...,值n);
```

* 批量插入记录:
* 适用于批量插入记录

```sql
INSERT INTO 表名
(列1, 列2)
VALUES
(记录1值1, 记录1值2),
(记录2值1, 记录2值2);

INSERT INTO 表名
VALUES
(记录1值1, 记录1值2),
(记录2值1, 记录2值2);
```

* 注意:
* 插入记录能从中间插入吗? 都是从最后插入的, 只是排序不同
* 如果删除一条记录, 导致自增的id中间断号, 有问题吗? 没问题, 如果想补可以补上

---

## 更新记录(修改记录)

* 更新记录语句(修改记录中的值)

```sql
# 语法
UPDATE 表名 SET
列名1 = 新值,
列名2 = 新值;

# 修改指定条件的记录
UPDATE 表名 SET
列名1 = 新值, 列名2 = 新值
WHERE 条件;
```

* 更新(修改)指定的记录
* 如果不加`WHERE`条件, 则所有数据都会被修改
* 扩展:
* WHERE: 条件子句
* 作用: 筛选符合条件的结果, 然后对这些结果进行操作

## 删除记录

* 删除指定的记录

```sql
# 删除指定条件的记录
DELETE FROM 表名 WHERE 条件;

# 删除所有记录
DELETE FROM 表名;
```

* 清空表

```sql
# 语句
TRUNCATE TABLE 表名;
```

* 注意: DELETE和TRUNCATE清空表有什么区别?
* `DELETE FROM 表名`: 逐条删除记录, 如果有自增, 自增计数器不会重置. 如果继续添加记录, 则自增的序号会继续自增
* `TRUNCATE TABLE 表名;`: 删除表然后重新建表(DROP TABLE/CREATE TABLE), 自增计数器也被删除了, 而创建后会是新的计数器, 所以相当于计数器重置(1)

## 命令行乱码问题

* 乱码原因: 存入记录的字符编码和数据库编码不一致, windows的cmd是GBK编码, 而数据库是utf-8编码
* 临时方法:

```sql
# 将数据库数据修改为Windows的GBK
SET NAMES 'GBK';
```

* 扩展:
* `SET NAMES`实际上对MySQL做了以下3个配置
* [查看原理](http://mysql.taobao.org/monthly/2015/05/07/)

```
SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;
```

## 数据表和测试数据准备

* 8分钟练习:
* 使用库mybase
* 创建新表zhangwu
* 账务id: id 整数主键自增
* 账务名称: zname 字符(200)
* 金额: zmoney 浮点数
* 添加测试数据
* 1, 吃饭支出, 247
* 2, 工资收入, 12345
* 3, 服装支出, 1000
* 4, 吃饭支出, 325
* 5, 股票收入, 8000
* 6, 打麻将支出, 8000
* 7, NULL, 5000

```sql
# 使用库
USE mybase;

# 建表
CREATE TABLE zhangwu (
  id INT PRIMARY KEY AUTO_INCREMENT,
  zname VARCHAR(200),
  zmoney DOUBLE
);

# 批量插入数据
INSERT INTO zhangwu
VALUES
(1, '吃饭支出', 247),
(2, '工资收入', 12345),
(3, '服装支出', 1000),
(4, '吃饭支出', 325),
(5, '股票收入', 8000),
(6, '打麻将支出', 8000),
(7, NULL, 5000);
```

---

## 数据的基本查询

* 语法

```sql
# 查询指定字段
SELECT 列名1, 列名2 FROM 表名;

# 查询所有字段
SELECT * FROM 表名;

# DISTINCT去重
SELECT DISTINCT 列名1, 列名2 FROM 表名;

# AS给字段取别名
SELECT 列名 AS 别名 FROM 表名;

# 对查询结果进行数学计算
SELECT 列名+值 FROM 表名;
```

* `DISTINCT`关键字的作用:
* 按照指定列的值将查询出的记录去重
* 如果`DISTINCT`后面有多个列, 怎么去重?
* 按照多个列的记录值的组合来判断是否重复, 如以下2条记录
* 张三, 16
* 张三, 18
* 如果按姓名列去重, 则只会留下一个张三; 如果按姓名和年龄2列去重, 则2个都会留下
* `AS`关键字的作用:
* 给列取别名: `列名 AS '别名'`
* 给表取别名: `表名 AS '别名'`
* 注意: 别名会修改真正的名称吗? 不会, 在当前执行的SQL语句中有效
* 在查询中对记录的查询结果进行数学计算
* 格式: 在字段名上直接使用数学运算符
* 注意: 数学计算结果仅对查询出的结果生效, 不会影响表内的实际数据

* 5分钟练习:
* 继续对zhangwu表进行以下操作:
* 查询zname和zmoney两个列的记录
* 去重查询zname列的记录
* 查询zname字段并取别名为name(观察查询结果的字段名是否变化)
* 查询zname, zmoney字段, 同时将zmoney字段增加1000(观察zmoney记录的值是否改变, 是否会影响真实的表中数据)

```sql
# 查询zname和zmoney两个列的记录
SELECT zname, zmoney FROM zhangwu;

# 去重查询zname列的记录
SELECT DISTINCT zname FROM zhangwu;

# 查询zname字段并取别名为name(观察查询结果的字段名是否变化)
SELECT zname AS 'name' FROM zhangwu;

# 查询zname, zmoney字段, 同时将zmoney字段增加1000(观察zmoney记录的值是否改变, 是否会影响真实的表中数据)
SELECT zname, zmoney+1000 FROM zhangwu;
```

## 数据的条件查询1:  比较运算符和逻辑运算符

* 条件查询:
* 即按指定条件查询记录返回符合条件的结果集, 使用`SELECT`和`WHERE`组合
* `WHERE`:
* 作用: 条件子句, 过滤符合条件的结果
* 运算符
* 比较运算符
* `=`: 相等
* `<>`或`!=`: 不等
* `>`: 大于
* `<`: 小于
* `>=`: 大于等于
* `<=`: 小于等于
* `BETWEEN...AND...`: 在一个范围内(包含头和尾), `BETWEEN 0 AND 10;` 0~10
* 如:
* `IN ()`: 在列表中, 满足列表中一个即可
* 如: `IN (1, 3, 5)`
* `IS NULL`: 是空
* `LIKE 通配符`: 模糊查询
* 通配符种类
* `%`: 一个百分号可以表示任意个字符. 比如`王%`, `王大锤`, `王五`
* `_`: 一个下划线可以表示一个字符. 比如`王_`, 只能匹配`王五`
* 逻辑运算符
* `AND`: 与. 两边条件同时成立才成立
* `OR`: 或. 两边条件只要有一个成立就成立
* `NOT`: 非, 取相反结果
* `NOT BETWEEN ... AND ...`: 不在范围内
* `NOT IN`: 不在列表中
* `NOT LIKE`: 不匹配
* `IS NOT NULL`: 非空
* 日期也是可以比较的

* 10分钟练习:
* 继续对zhangwu表进行以下操作:
* 查询所有的吃饭支出
* 查询金额大于1000的记录
* 查询金额在2000到5000之间的记录, 使用>=, <=运算符和AND
* 查询金额在2000到5000之间的记录, 使用BETWEEN ... AND ...
* 查询金额符合指定列表(1000, 2000, 5000)的数据记录, 使用=运算符和OR
* 查询金额符合指定列表(1000, 2000, 5000)的数据记录, 使用IN()

```sql
# 查询所有的吃饭支出
SELECT * FROM zhangwu WHERE zname = '吃饭支出';

# 查询金额大于1000的记录
SELECT * FROM zhangwu WHERE zmoney > 1000;

# 查询金额在2000到5000之间的记录, 使用>=, <=运算符和AND
SELECT * FROM zhangwu WHERE zmoney >= 2000 AND zmoney <= 5000;

# 查询金额在2000到5000之间的记录, 使用BETWEEN ... AND ...
SELECT * FROM zhangwu WHERE zmoney BETWEEN 2000 AND 5000;

# 查询金额符合指定列表(1000, 2000, 5000)的数据记录, 使用=运算符和OR
SELECT * FROM zhangwu WHERE zmoney = 1000 OR zmoney = 2000 OR zmoney = 5000;

# 查询金额符合指定列表(1000, 2000, 5000)的数据记录, 使用IN()
SELECT * FROM zhangwu WHERE zmoney IN (1000, 2000, 5000);
```

## 数据的条件查询2: 通配符

* `... WHERE zname IS NOT NULL;`
* name不是null
* `... WHERE NOT (zname IS NULL);`
* 将name是null的结果取反

* 5分钟练习:
* 继续对zhangwu表进行以下操作:
* 查询所有支出: zname含有"支出", 使用LIKE
* 查询5个字符的zname, 通配符用5个下划线
* 查询zname不为空的记录

```sql
# 查询所有支出: zname含有"支出", 使用LIKE
SELECT * FROM zhangwu WHERE zname LIKE '%支出%';

# 查询5个字符的zname, 通配符用5个下划线
SELECT * FROM zhangwu WHERE zname LIKE '_____';

# 查询zname不为空的记录
SELECT * FROM zhangwu WHERE zname IS NOT NULL;
SELECT * FROM zhangwu WHERE NOT (zname IS NULL);
```

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

## 今日扩展

### CRUD

* CRUD是`Create`, `Retrieve`, `Update`, `Delete`的首字母组合, 表示增加, 查询, 修改, 删除4种操作, 用中文一般叫做`增删改查`

### 数据类型

* 大致可分为三类:
* 数值
* 日期/时间
* 字符串

#### 数值类型

|类型       |大小 |范围(有符号)                                                                   |范围(无符号)                            |用途       |
|------------|----|--------------------------------------------------------------------------------|---------------------------------------|------------|
|TINYINT    |1字节|`[-128, 127]`                                                                |`[0,255]`                               |小整数值    |
|SMALLINT |2字节|`[-32768, 32767]`                                                             |`[0, 65535]`                            |大整数值    |
|MEDIUMINT |3字符|`[-8388608, 8388607]`                                                          |`[0, 16777215]`                         |大整数值    |
|INT或INTEGER|4字节|`[-2147483648, 2147483647]`                                                    |`[0, 4294967295]`                      |大整数值    |
|BIGINT    |8字节|`[-9233372036854775808, 9223372036854775807]`                                  |`[0, 18446744073709551615]`             |极大整数值 |
|FLOAT    |4字节|`[-3.402823466E+38, -1.175494351E-38]`, `0`, `[1.175494351E-38, 3.402823466351E+38]`|`[0, [1.175494351E-38, 3.402823466E+38]`|单精度浮点数值|
|DOUBLE    |8字节|`[-1.7976931348623157E+308, -2.2250738585072014E-308]`, `0`, `[2.2250738585072014E-308, 1.7976931348623157E+308]`|`0`, `[2.2250738585072014E-308, 1.7976931348623157E+308]`|双精度浮点数值|
|DECIMAL    |对于DECIMAL(M, D), 如果M>D, 是M+2; 否则是D+2|依赖于M和D的值|依赖于M和D的值|小数值|

#### 日期和时间类型

当时间不合法时, 值为零值

|类型    |大小(字节)|范围                                  |格式             |用途                |
|---------|---------|-----------------------------------------|-------------------|--------------------|
|DATE    |3       |`1000-01-01/9999-12-31`                |`YYYY-MM-DD`       |日期值             |
|TIME    |3       |`-838:59:59/838:59:59`                |`HH:MM:SS`          |时间值或持续时间    |
|YEAR    |1       |`1901/2155`                            |`YYYY`             |年份值             |
|DATETIME |8       |`1000-01-01 00:00:00/9999-12-31 23:59:59`|`YYYY-MM-DD HH:MM:SS`|混合日期和时间值    |
|TIMESTAMP|4       |`1970-01-01 00:00:00/2037 年某时`       |`YYYYMMDD HHMMSS` |混合日期和时间值, 时间戳|

#### 字符串类型

|类型    |大小(字节) |用途                   |
|----------|------------|------------------------|
|CHAR    |0-255    |定长字符串             |
|VARCHAR |0-65535    |变长字符串             |
|TINYBLOB  |0-255    |不超过255个字符的二进制字符串|
|TINYTEXT  |0-255    |短文本字符串             |
|BLOB    |0-65535    |二进制形式的长文本数据    |
|TEXT    |0-65535    |长文本数据             |
|MEDIUMBLOG|0-16777215  |二进制形式的中等长度文本数据 |
|MEDIUMTEXT|0-16777215  |中等长度文本数据          |
|LONGBLOB  |0-4294967295|二进制形式的极大文本数据 |
|LONGTEXT  |0-4294967295|极大文本数据             |

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

# 今日总结

* 数据库
* 概念: 存储数据的仓库. 本质是一个文件系统, 数据按照指定的格式将数据存储起来, 用户可以对数据库中的数据进行增删改查
* DBMS: Database Management System, 数据库管理系统. 是操作和管理数据库的大型软件, 用于建立, 使用和维护数据库, 对数据库进行统一管理和控制, 以保证数据库的安全性和完整性. 用户通过数据库管理系统访问数据库中表内的数据
* 常见数据库
* `MySQL`
* `Oracle`
* 数据库: 存储数据表
* 数据表: 存储记录
* 由行和列组成的
* DBMS: 操作数据库
* 表和Java类的对应关系
* 表名: Java类名
* 列名: 成员属性名
* 记录: 类对象
* MySQL
* 安装
* 配置和遇到的问题
* 命令行的登录和退出
* 登录: `mysql -u 用户名 -p`
* 退出: `exit`
* SQL基本语言
* SQL的概念: Structured Query Language, 结构化查询语言
* 结构: 数据表是由行和列组成的, 是有结构的, 从这种结构中查询
* SQL的4中分类
* DDL: Data Defination Language, 数据定义语言, 用来定义数据库对象(库, 表等)
* CREATE
* ALTER
* DROP
* DML: Data Manipulation Language, 数据操作语言, 对表中的记录进行增, 删, 改
* INSERT
* UPDATE
* DELETE
* DQL: Data Query Language, 数据查询语言, 对表中的记录进行查
* SELECT
* FROM
* WHERE
* DCL: Data Control Language, 数据控制语言, 创建修改用户, 权限
* SQL特点
* SQL不区分大小写
* 一条SQL语句以`;`分号结尾, 一条语句可以多行书写
* 单词之间通过空格分隔, 参数之间通过`,`逗号分隔
* 字符串和日期要用`''`或`""`引起来, 建议单引号
* SQL注释(2+1种)
* 2种单行注释
* `#单行注释`
* `-- 单行注释`: 减号后必须有一个空格
* 1种多行注释: `/* 注释内容 */`
* SQL的数据类型
* `INT`: 整数
* `DOUBLE`: 小数
* `VARCHAR`: 可变长度字符
* `CHAR`: 固定长度字符
* 其他看文档
* VARCHAR和CHAR的区别:
* VARCHAR(200), 可变长度字符, 存储占用的内存空间是可变的, 如果你的数据容量小于我们指定的空间大小, 那么就会按照实际的数据空间来开辟内存大小
* 优点: 节省内存
* 缺点: 因为每次存入数据时都会判断数据的实际大小, 来动态修改内存空间, 所以需要占用资源, 效率低
* CHAR(200), 固定长度字符, 存储占用的内存空间是不可变的. 无论实际存入的数据容量是多大, 都占用这么大的空间
* 优点: 插入数据时不需要额外操作, 效率高
* 缺点: 可能会浪费空间
* SQL对库的操作
* 创建库
* 使用默认字符集创建库: `CREATE DATABASE 库名;`
* 默认是UTF-8编码
* 指定字符集: `CREATE DATABASE 库名 CHARACTER SET '字符集';`
* 删除库
* `DROP DATABASE 库名;`
* 查看所有库
* `SHOW DATABASES;`
* 使用库
* `USE 库名;`
* 查看当前使用的库
* `SELECT DATABASE();`
* SQL对表的操作
* 创建表: `CREATE TABLE 表名 (列名1 数据类型约束, 列名2 数据类型约束);`
* 删除表: `DROP TABLE 表名;`
* 查看当前库中的所有表: `SHOW TABLES;`
* 修改表的结构: `ALTER TABLE 表名操作关键字列名数据类型约束;`
* 增加列: `ALTER TABLE 表名 ADD 新列名新数据类型新约束;`
* 删除列: `ALTER TABLE 表名 DROP 列名;`
* 修改列的数据类型和约束(不能修改列名): `ALTER TABLE 表名 MODIFY 列名新数据类型新约束;`
* 修改列的列名, 数据类型, 约束: `ALTER TABLE 表名 CHANGE 旧列名新列名新数据类型新约束;`
* 修改表名: `RENAME TABLE 旧表名 TO 新表名;`
* SQL对记录的操作
* 增
* 一次插入一条记录: `INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2);`
* 一次性插入一条记录(省略自增主键): `INSERT INTO 表名 (非主键自增列1, 非主键自增列2) VALUES (值1, 值2);`
* 一次插入所有列的记录(省略列名): `INSERT INTO 表名 VALUES (值1, 值2);`
* 值的数量和顺序必须和表的列一致
* 一次插入多个记录(指定列): `INSERT INTO 表名 (列1, 列2) VALUES (记录1值1, 记录1值2), (记录2值1, 记录2值2);`
* 一次插入多个记录(省略列): `INSERT INTO 表名 VALUES (记录1值1, 记录1值2), (记录2值1, 记录2值2);`
* 删
* 删除符合条件的记录: `DELETE FROM 表名 WHERE 条件;`
* 删除所有记录: `DELETE FROM 表名;`
* 清空表: `TRUNCATE TABLE 表名;`
* `DELETE FROM 表名;`和`TRUNCATE TABLE 表名;`的区别
* DELETE, 逐条删除记录, 不会重置自增计数器
* TRUNCATE, 删除表再重建, 会重置自增计数器
* 改
* 修改符合条件的记录: `UPDATE 表名 SET 列名1=新值, 列名2=新值 WHERE 条件;`
* 修改所有记录: `UPDATE 表名 SET 列名1=新值, 列名2=新值;`
* 查
* 查询指定列: `SELECT 列名1, 列名2 FROM 表名;`
* 查询所有列: `SELECT * FROM 表名;`
* 条件查询: `SELECT 列名1, 列名2 FROM 表名 WHERE 条件;`
* 运算符
* 比较运算符
* `=`: 相等
* `<>`或`!=`: 不等
* `>`: 大于
* `<`: 小于
* `>=`: 大于等于
* `<=`: 小于等于
* `BETWEEN...AND...`: 在一个范围内(包含头和尾)
* 如: BETWEEN 0 AND 10
* `IN ()`: 在列表中, 满足列表中一个即可
* 如: IN (1, 3, 5)
* `IS NULL`: 是空
* `LIKE '通配符'`: 模糊查询
* `%`: 一个百分号可以表示任意个字符. 比如'王%', 王大锤, 王五
* `_`: 一个下划线可以表示一个字符. 比如'王_', 只能匹配王五
* 逻辑运算符
* `AND`: 与. 两边条件同时成立才成立
* `OR`: 或. 两边条件只要有一个成立就成立
* `NOT`: 非, 取相反结果
* `NOT BETWEEN ... AND ...`: 不在范围内
* `NOT IN`: 不在列表中
* `NOT LIKE`: 不匹配
* `IS NOT NULL`: 非空
* 去重: `SELECT DICTINCT 列名1, 列名2 ... FROM 表名 WHERE 条件;`
* 别名:
* `字段名 AS 别名`
* `表名 AS 别名`

# day02 高级SQL, JDBC, SQL注入

## 排序查询

* 排序查询
* 是`SELECT`的子句, 将查询后的结果集进行排序
* 格式:
* `ORDER BY 列名排序选项`
* `SELECT ... FROM ... WHERE ... ORDER BY 列名排序选项`
* 排序选项: 适用于数字, 英文字母, 日期
* `ASC`: 默认, 升序
* `DESC`: 降序
* 注意: `WHERE`和`ORDER BY`的顺序
* `WHERE`在前, `ORDER BY`在后, 否则报错
* 因为是排序是对SELECT查询出的结果集再次进行排序, 而不是对表中的数据进行排序. 所以要先把SELECT WHERE的查询结果拿到, 才能使用ORDER BY排序
* 扩展:
* 排序可以按照多列, 如先按a列降序, 如果a列中有相同的值, 则按b列降序
* `SELECT * FROM users ORDER BY age DESC, salary DESC;`
* `ORDER BY`是SQL语句的末尾吗?
* 不一定是SQL语句的末尾, 后面还可以写LIMIT子句

```sql
/* 排序示例 */

# 查询用户表所有列, 按照年龄升序排序
SELECT * FROM users ORDER BY age;
SELECT * FROM users ORDER BY age ASC;

# 查询用户表所有列, 按照年龄降序排序
SELECT * FROM users ORDER BY age DESC;

# 查询用户表中年龄在20~30岁的用户, 并按年龄降序排序
SELECT * FROM users WHERE age BETWEEN 20 AND 30 ORDER BY age DESC;

# 查询用户表中年龄在20~30岁的用户, 先按年龄降序排序, 年龄相同的按工资降序排序
SELECT * FROM users WHERE age BETWEEN 20 AND 30 ORDER BY age DESC, salary DESC;
```

* 5分钟练习
* 继续使用mybase库, 对zhangwu表做以下操作:
* 查询所有列的记录, 按金额排序, 使用默认排序
* 查询所有列的记录, 按金额排序, 使用升序关键字ASC
* 查询所有列的记录, 按金额排序, 使用降序关键字DESC
* 查询所有支出, 按金额降序

```sql
# 查询所有列的记录, 按金额排序, 使用默认排序
SELECT * FROM zhangwu ORDER BY zmoney;

# 查询所有列的记录, 按金额排序, 使用升序关键字ASC
SELECT * FROM zhangwu ORDER BY zmoney ASC;

# 查询所有列的记录, 按金额排序, 使用降序关键字DESC
SELECT * FROM zhangwu ORDER BY zmoney DESC;

# 查询所有支出, 按金额降序
SELECT * FROM zhangwu WHERE zname LIKE '%支出%' ORDER BY zmoney DESC;
```

## 聚合函数

* 聚合函数
* SQL内置了一些函数(类似于API方法), 可以对查询的结果进行相关计算
* 作用:
* 竖向对某一列的值进行计算, 然后返回一个计算结果.
* 格式:
* `SELECT 函数名(列名) FROM 表名 ...;`
* 常用的5个聚合函数
* `COUNT(列名)`: 计算指定列的记录行数.
* 注意: 值为NULL的记录不会被统计
* `SUM(列名)`: 计算指定列的数值总和
* 如果数据类型不是数值, 则结果为: 0
* `MAX(列名)`: 获取指定列的数值中的最大值
* `MIN(列名)`: 获取指定列的数值中的最小值
* `AVG(列名)`: 计算指定列的数值中的平均值
* 如果数据类型不是数值, 则结果为: 0
* 注意:
* 聚合函数的函数名和小括号之间要连着写, 不要有空格:
* 正确: `SUM(zname)`
* 错误: `SUM (zname)`
* NULL与任何数的运算结果都是NULL: `SELECT (NULL + 10)`结果为NULL

* 5分钟练习
* 继续使用mybase库, 对zhangwu表做以下操作:
* 查询表中一共有多少条记录: COUNT
* 计算所有金额的总和: SUM
* 计算总支出: SUM
* 计算总收入: SUM
* 获取最高金额: MAX
* 获取最低金额: MIN
* 获取最高收入: MAX
* 获取最低收入: MIN
* 计算金额平均值: AVG

```sql
# 查询表中一共有多少条记录: COUNT  7条
SELECT COUNT(*) FROM zhangwu;

# 计算所有金额的总和: SUM  34917
SELECT SUM(zmoney) AS '金额总和' FROM zhangwu;

# 计算总支出: SUM 9572
SELECT SUM(zmoney) AS '总支出' FROM zhangwu WHERE zname LIKE '%支出%';

# 计算总收入: SUM  20345
SELECT SUM(zmoney) AS '总收入' FROM zhangwu WHERE zname LIKE '%收入%';

# 获取最高金额: MAX  12345
SELECT MAX(zmoney) AS '最高金额' FROM zhangwu;

# 获取最低金额: MIN  247
SELECT MIN(zmoney) AS '最低金额' FROM zhangwu;

# 获取最高收入: MAX  12345
SELECT MAX(zmoney) AS '最高收入' FROM zhangwu WHERE zname LIKE '%收入%';

# 获取最低收入: MIN  8000
SELECT MIN(zmoney) AS '最低收入' FROM zhangwu WHERE zname LIKE '%收入%';

# 计算金额平均值: AVG  4988.1428...
SELECT AVG(zmoney) AS '平均金额' FROM zhangwu;
```

---

## 分组查询

* 分组查询
* 是`SELECT`的子句, 用于将查询出的结果集再次按照某列分组合并显示, 记录值相同的归为一组. 同时还可以对分组后的结果再次使用条件进行过滤
* 格式:
* `GROUP BY 列名 HAVING 条件`,
* `SELECT ... FROM ... WHERE ... GROUP BY 列名 HAVING 条件;`
* `HAVING`的作用: 可选, 对查询结果进行分组显示后, 再次按条件过滤
* 注意:
* 聚合函数与分组的使用效果
* 使用聚合函数后再进行分组, 才相当于汇总统计
* `SELECT zname, SUM(zmoney) FROM zhangwu GROUP BY zname;`
* 不使用聚合函数的分组, 只有第一条记录的值
* `SELECT zname, zmoney FROM zhangwu GROUP BY zname;`
* `HAVING`和`WHERE`的区别
* 过滤时机不同
* WHERE是在分组前先对查询结果进行过滤
* HAVING是在查询结果分组后再次过滤
* 是否可跟随聚合函数不同
* WHERE条件中不可使用聚合函数
* HAVING条件中可以使用聚合函数
* 条件可用列名不同
* HAVING条件中的列名必须是SELECT或GROUP BY中使用过的列名
* WHERE条件中可以使用表的所有列名

```sql
/* 最终一个完整的SELECT语句格式 */

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... DESC;
```

* 10分钟练习
* 继续使用mybase库, 对zhangwu表做以下操作:
* 按账务名称分组统计各组金额总和(查询zname, zmoney求和列, 并按照zname分组)
* 按账务名称分组统计各组支出金额总和(查询zname, zmoney求和列, 过滤出支出, 并按zname分组)
* 按账务名称分组统计各组支出金额总和, 并按金额总和的降序排列(查询zname, zmoney求和列, 过滤出支出, 并按zname分组, 按zmoney求和列降序排序)
* 按账务名称分组统计各组支出金额总和大于5000的记录, 并按金额总和的降序排列(查询zname, zmoney求和列, 过滤出支出, 并按zname分组, 将分组后的结果再次过滤出大于5000的, 最后按zmoney求和列降序排序)
* 注意: 这个需求中支出应使用where过滤, 分组后大于5000的应使用having过滤

```sql
# 按账务名称分组统计各组金额总和(查询zname, zmoney求和列, 并按照zname分组)
SELECT zname, SUM(zmoney) FROM zhangwu
GROUP BY zname;

# 按账务名称分组统计各组支出金额总和(查询zname, zmoney求和列, 过滤出支出, 并按zname分组)
SELECT zname, SUM(zmoney) FROM zhangwu
WHERE zname LIKE '%支出%'
GROUP BY zname;

# 按账务名称分组统计各组支出金额总和, 并按金额总和的降序排列(查询zname, zmoney求和列, 过滤出支出, 并按zname分组, 按zmoney求和列降序排序)
SELECT zname, SUM(zmoney) AS '金额总和' FROM zhangwu
WHERE zname LIKE '%支出%'
GROUP BY zname
ORDER BY 金额总和 DESC;

# 按账务名称分组统计各组支出金额总和大于5000的记录, 并按金额总和的降序排列(查询zname, zmoney求和列, 过滤出支出, 并按zname分组, 将分组后的结果再次过滤出大于5000的, 最后按zmoney求和列降序排序)
SELECT zname, SUM(zmoney) AS '金额总和' FROM zhangwu
WHERE zname LIKE '%支出%'
GROUP BY zname
HAVING 金额总和 > 5000
ORDER BY 金额总和 DESC;
```

## JDBC概念和数据库驱动程序

* JDBC:
* Java DataBase Connectivity, Java数据库连接. 是一种技术的名称
* 是SUN公司提供的用于执行SQL语句的Java API, 用为多种关系型数据库提供统一的访问. 它由一组用Java语言编写的类和接口组成, 是Java访问数据库的标准规范
* 数据库驱动:
* 两个不同的设备要通信, 需要满足一定的通信数据格式, 数据格式由设备提供商规定, 设备提供商为设备提供驱动软件, 通过驱动软件就可以让两个设备通信.
* Java和MySQL数据库就可以看作2个不同的设备, JDBC需要使用数据库驱动来让Java代码与数据库服务器通信, 将Java中的SQL字符串发送给数据库来执行
* 数据库驱动有多种, 用于不同开发语言, 对于Java也有专门的一个驱动
* Java用于MySQL的驱动从哪里得到: [MySQL官网](https://dev.mysql.com/downloads/connector/j/)

## JDBC原理

* JDBC和数据库驱动的关系
* JDBC是一套API, 可以通过Java代码来使用JDBC, 同时提供了一套接口, 用于让数据库厂商根据自家数据库特点去实现JDBC的功能
* 数据库驱动是数据库厂商根据自家数据库特点去实现JDBC接口而制作的, 用于让JDBC能够调用数据库的相关功能
* Java程序通过使用JDBC的API, 通过多态的方式调用数据库驱动中实现类的方法, 对数据库进行操作

```
         +----------+
         | Java程序 |
         +-----+----+
               |
         +-----+----+
         | JDBC |
         | 接口 |
         +-----+----+
               |
   +---------+---------+
   |                |
+------+------+    +------+------+
|  MySQL驱动 |    |  Oracle驱动 |
| 实现类 |    | 实现类 |
+------+------+    +------+------+
   |                |
+------+------+    +------+------+
| MySQL数据库  |    | Oracle数据库 |
+-------------+    +-------------+
```

## 准备测试数据: 分类表

```sql
# day02 高级SQL, JDBC, SQL注入

-- 创建JDBC测试数据----------------------------------

# 使用mybase库
USE mybase;

# 创建产品分类表
CREATE TABLE sort (
  sid INT PRIMARY KEY AUTO_INCREMENT,
  sname VARCHAR(100),
  sprice DOUBLE,
  sdesc VARCHAR(5000)
);

# 初始化数据
INSERT INTO sort
(sname, sprice, sdesc)
VALUES
('家电', 2000, '优惠的促销'),
('家具', 8900, '家具价格上调, 原材料涨价'),
('儿童玩具', 300, '赚家长钱'),
('生鲜', 500.99, '生鲜商品'),
('服装', 24000, '换季销售'),
('洗涤', 50, '洗发水促销');

# 查看数据
SELECT * FROM sort;
```

---

## JDBC开发步骤

* JDBC开发步骤
* 前提: 导入数据库驱动jar包
* 代码编写步骤:
1. 注册驱动
* 告知JVM使用哪一个数据库驱动
2. 获取连接
* 使用JDBC中的类, 创建对MySQL数据库的连接
3. 获取语句执行平台
* 通过连接对象获取对SQL语句的执行者对象
4. 执行SQL语句
* 使用执行者对象, 向数据库执行SQL语句, 并获取执行结果
5. 处理结果
* 根据项目的业务需求对结果进行操作
6. 释放资源
* 释放数据库连接, 必须

## JDBC开发: 前提.导入数据库驱动jar包

* 步骤
* 在项目根目录下创建`lib`目录
* 将jar包复制到lib目录下
* 右键点击jar文件, `Build Path...`, `Add to Build Path`, 变成奶瓶导入成功

* 3分钟练习:
* 新建一个项目s3-day02
* 在lib目录中导入jar包

## JDBC开发: 1.注册数据库驱动

* 注册数据库驱动的源代码
* `java.sql.DriverManager`类: 管理JDBC驱动
* `static void registerDriver(Driver driver)`: 注册数据库驱动
* `java.sql.Driver`接口: 数据库驱动必须实现的接口. 在数据库驱动jar包中已经实现了
* 注册驱动的2种方式
* 方式1: `DriverManager.registerDriver(new Driver());`
* 其中Driver类是jar包驱动中的实现类, 如`com.mysql.jdbc.Driver`
* 方式2: `Class.forName("com.mysql.jdbc.Driver");`
* 这是MySQL推荐的, 内部也是使用方式1实现的
* 注意:
* MySQL驱动`com.mysql.jdbc.Driver`类中中已经编写了注册驱动的方法. 我们只需要通过反射去加载MySQL的驱动类, 让其中的静态代码块执行, 从而注册驱动即可. 不要手动注册驱动导致资源浪费

* 3分钟练习:
* 继续使用项目s3-day02, 建包com.itheima.practice_01
* 建类Test
* 使用Class类的静态方法注册驱动

```java
// 1.注册驱动, 使用反射的方式
Class.forName("com.mysql.jdbc.Driver");
```

## JDBC开发: 2.获取数据库连接对象

* 这一步相当于SQLyog点击连接按钮
* `java.sql.DriverManager`类
* `static Connection getConnection(String url, String user, String password)`: 获取Connection接口的实现类, 实现类是jar包中实现的
* `url`: 数据库地址, `jdbc:mysql://主机IP:端口号/数据库名`
* 主机IP或端口号可以省略, 默认使用localhost和3306: `jdbc:mysql:///day02_04`
* `user`: 用户名
* `password`: 密码

* 5分钟练习:
* 继续编写代码
* 通过DriverManager的静态方法获取Connection对象, 打印对象查看地址值

```java
// 2.获取连接对象
String url = "jdbc:mysql://localhost:3306/mybase";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection); // com.mysql.jdbc.JDBC4Connection@10d1f30
```

## JDBC开发: 3. 获取SQL语句的执行对象

* 相当于获取SQLyog中的Query窗口, 用来写SQL语句
* `Connection`接口
* `Statement createStatement()`: 获取Statement对象, 该对象用于将SQL语句发送到数据库

* 3分钟练习:
* 继续编写
* 通过connection对象创建sql执行对象Statement

```java
// 3.通过Connection对象获取Statement对象
Statement statement = connection.createStatement();
System.out.println(statement);  // com.mysql.jdbc.StatementImpl@137bc9
```

## JDBC开发: 4.执行INSERT语句, 获取结果集, 释放资源

* `Statement`接口
* `int executeUpdate(String sql)`: 执行SQL语句, 仅限于INSERT, DELETE, UPDATE, 返回生效的行数
* `void close()`: 释放Statement资源
* `Connection`接口
* `void close()`: 释放连接资源

* 3分钟练习:
* 继续编写
* 编写sql
* 向sort表中增加一条记录: '汽车用品', 50000, '疯狂涨价'
* 通过sql执行平台对象执行插入语句
* 释放相关资源

```java
public class Test {

public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1.注册驱动, 使用反射的方式
Class.forName("com.mysql.jdbc.Driver");
// 2.获取连接对象
String url = "jdbc:mysql://localhost:3306/mybase";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
// System.out.println(connection); // com.mysql.jdbc.JDBC4Connection@10d1f30
// 3.通过Connection对象获取Statement对象
Statement statement = connection.createStatement();
// System.out.println(statement);  // com.mysql.jdbc.StatementImpl@137bc9
// 4. 执行SQL语句向sort表中增加一条记录: '汽车用品', 50000, '疯狂涨价'
String sql = "INSERT INTO sort (sname, sprice, sdesc) VALUES ('汽车用品', 50000, '疯狂涨价');";
int rows = statement.executeUpdate(sql);
// 5. 处理结果
System.out.println(rows);
// 6. 释放资源
statement.close();
connection.close();
}
}
```

---=

## JDBC开发: 5.6.执行SELECT语句, 获取结果集, 从结果集中获取数据

* `Statement`类
* `ResultSet executeQuery(String sql)`: 执行SELECT查询, 返回查询结果到`ResultSet`对象中.
* `ResultSet`接口
* `boolean next()`: 将结果集的指针向下移动一行, 并返回是否还有下一条记录
* 注意: 没有调用该方法之前, ResultSet的光标在第一条记录之前, 只有调用一次next方法后, 指针才指向第一条记录
* `int getInt(int column)`: 获取第column列的int类型的数据
* `int getInt(String columnName)`: 获取指定列名的int类型的数据
* `String getString(int column)`: 获取第column列的String类型数据
* `String getString(String columnName)`: 获取指定列名的String类型的数据
* `Object getObject(String columnName)`: 获取指定列名的任何类型数据
* `void close()`: 释放结果集资源

* 5分钟练习:
* 将插入语句注释掉, 继续编写代码
* 编写sql查询sort表中所有记录
* 并从结果集中获取数据打印到控制台
* 释放资源

```java
public class Test {

public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1.注册驱动, 使用反射的方式
Class.forName("com.mysql.jdbc.Driver");
// 2.获取连接对象
String url = "jdbc:mysql://localhost:3306/mybase";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
// System.out.println(connection); // com.mysql.jdbc.JDBC4Connection@10d1f30
// 3.通过Connection对象获取Statement对象
Statement statement = connection.createStatement();
// System.out.println(statement);  // com.mysql.jdbc.StatementImpl@137bc9

/*// 4. 执行SQL语句向sort表中增加一条记录: '汽车用品', 50000, '疯狂涨价'
String sql = "INSERT INTO sort (sname, sprice, sdesc) VALUES ('汽车用品', 50000, '疯狂涨价');";
int rows = statement.executeUpdate(sql);
// 5. 处理结果
System.out.println(rows);*/

// 执行查询
String sql = "SELECT * FROM sort;";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
String sid = resultSet.getString("sid");
String sname = resultSet.getString("sname");
String sprice = resultSet.getString("sprice");
String sdesc = resultSet.getString("sdesc");
System.out.println(sid + "\t" + sname + "\t" + sprice + "\t" + sdesc);
}

// 6. 释放资源
resultSet.close();
statement.close();
connection.close();
}
}
```

## SQL注入攻击

* 用户注册和登录实际就是对数据库进行操作
* 注册: 将用户名和密码INSERT一条记录
* `INSERT INTO users (username, password) VALUES ('zhangsan', '123456');`
* 登录: 按照用户名和密码查询是否有符合条件的记录
* `SELECT * FROM users WHERE username='zhangsan' AND password='123456';`
* 如果查到了, 则说明用户已经注册过, 且用户名密码都匹配, 则允许登录
* 如果查不到, 则说明用户没有注册, 或用户名密码不匹配, 则提示重新输入
* 什么是SQL注入攻击?

```
users表
------------------------------
id  |  username  |  password
------------------------------
1 |  a       |  1
------------------------------

-- 正常登陆
用户名: ____a____
密  码: ____1____

# 服务器端的Java程序获取到用户名和密码后, 最简单的方式是将其使用字符串拼接的方式, 组成一个完整的SQL语句
String sql = "SELECT * FROM user WHERE username = '" + username + "' AND password = '" + password + "';";

SELECT * FROM user WHERE username = 'a' AND password = '1';
                                 -                -

# 查询到1条记录, 说明用户名存在, 且密码正确, 允许登陆
登录成功!

-- 利用SQL注入来登录
用户名:__随便怎么写____
密  码:__1' OR '1=1___

# 来看看字符串拼接以后的SQL语句变成了什么?
SELECT * FROM user WHERE username = '随便怎么写' AND password = '1' OR '1=1';
                                 ---------                ----------
# where条件变成了:
username = '随便怎么写' AND password = '1'
或者
'1=1'
因为'1=1'是永远成立的, 所以这个where条件永远成立, 则相当于没有过滤任何条件, 最终的执行效果等于SELECT * FROM user

# 既然能查到用户, 则允许登录
登录成功!
```

## SQL注入攻击Java代码演示

* 能被注入的原因:
* SQL语句使用了字符串拼接方式
* 使用Statement执行的语句
* 密码: 1' OR '1=1

## 使用PreparedStatement接口预编译SQL语句, 避免SQL注入

* 避免SQL注入的解决方案
* 不使用字符串拼接SQL
* 使用PreparedStatement替代Statement
* 相关API
* `Connection`接口
* `PreparedStatement prepareStatement(String sql)`: 创建预编译对象
* `PreparedStatement`接口
* `setObject(int paramIndex, Object value)`: 设置指定占位符的值
* 注意: 索引从1开始
* SQL语句中的参数使用`?`问号占位符代替, 且不要写单引号
* 步骤:
* 通过Connection对象预编译SQL语句, 获取PreparedStatement对象
* 然后通过PreparedStatement对象调用方法执行语句

---

## PreparedStatement修改记录

* `PreparedStatement`接口
* `int executeUpdate()`: 执行INSERT, DELETE, UPDATE语句, 返回生效的行数
* 注意该方法不要传递参数

* 5分钟练习:
* 继续使用项目s3-day02, 建包com.itheima.practice_02
* 建类Test
* 使用JDBC, PreparedStatement, 将分类表中sid为6的数据修改为汽车用品, 49988
* `String sql = "UPDATE sort SET sname = ?, sprice = ? WHERE sid = ?;";`
* 参数为: 汽车美容, 49988, 7

```java
public class Test {

public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1.注册驱动, 使用反射的方式
Class.forName("com.mysql.jdbc.Driver");
// 2.获取连接对象
String url = "jdbc:mysql://localhost:3306/mybase";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);

// 使用PreparedStatement修改记录
String sql = "UPDATE sort SET sname=?, sprice=? WHERE sid=?;";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
// 设置参数
preparedStatement.setObject(1, "汽车美容");
preparedStatement.setObject(2, "49988");
preparedStatement.setObject(3, 7);
// 执行语句
preparedStatement.executeUpdate();

// 6. 释放资源
preparedStatement.close();
connection.close();
}
}
```

## PreparedStatement执行查询

* `PreparedStatement`
* `ResultSet executeQuery()`: 执行SELECT查询语句, 返回结果集
* 注意该方法不要传递参数

* 5分钟练习:
* 继续编写代码
* 将修改语句相关代码注释掉, 使用PreparedStatement查询sort表中所有记录并打印到控制台

```java
public class Test {

public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1.注册驱动, 使用反射的方式
Class.forName("com.mysql.jdbc.Driver");
// 2.获取连接对象
String url = "jdbc:mysql://localhost:3306/mybase";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);

/*// 使用PreparedStatement修改记录
String sql = "UPDATE sort SET sname=?, sprice=? WHERE sid=?;";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
// 设置参数
preparedStatement.setObject(1, "汽车美容");
preparedStatement.setObject(2, "49988");
preparedStatement.setObject(3, 7);
// 执行语句
preparedStatement.executeUpdate();*/

// 查询所有记录
String sql = "SELECT * FROM sort;";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
String sid = resultSet.getString("sid");
String sname = resultSet.getString("sname");
String sprice = resultSet.getString("sprice");
String sdesc = resultSet.getString("sdesc");
System.out.println(sid + "\t" + sname + "\t" + sprice + "\t" + sdesc);
}

// 6. 释放资源
resultSet.close();
preparedStatement.close();
connection.close();
}
}
```

## 封装JDBC工具类

* JDBCUtils第一版:
* 当类加载时根据4项配置, 创建数据库连接对象
* 定义静态方法获取数据库连接对象
* 定义静态方法释放资源

* 10分钟练习:
* 继续使用项目s3-day02, 建包com.itheima.practice_03
* 复制上一个练习的代码
* 新建JDBCUtils工具类, 完成工具类的编写
* 将测试类中代码修改为使用工具类方法获取连接对象, 使用工具类方法释放资源
* 测试是否可用

```java
/*
JDBC工具类: v1.0
*/
public class JDBCUtils {

// 私有构造方法
private JDBCUtils() {}

// 定义静态的连接对象作为成员变量
private static Connection connection;

// 使用静态代码块执行注册驱动和获取连接
static {
try {
// 注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 获取连接
String url = "jdbc:mysql://localhost:3306/mybase";
String user = "root";
String password = "123456";
connection = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("数据库连接失败");
}
}

// 获取连接
public static Connection getConnection() {
return connection;
}

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

public class Test {

public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 使用工具类获取连接
Connection connection = JDBCUtils.getConnection();

// 查询所有记录
String sql = "SELECT * FROM sort;";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
String sid = resultSet.getString("sid");
String sname = resultSet.getString("sname");
String sprice = resultSet.getString("sprice");
String sdesc = resultSet.getString("sdesc");
System.out.println(sid + "\t" + sname + "\t" + sprice + "\t" + sdesc);
}

// 6. 释放资源
JDBCUtils.close(connection, preparedStatement, resultSet);
}
}
```

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

## 扩展

### `LIMIT ... OFFSET ...`语句
* 在`SELECT`最后使用
* 翻页公式: `条数 * (页数 - 1)`
* 作用: 从总的查询结果中取出一部分作为查询结果
* 应用场景: 常用于分页
* 2种格式
* 格式1: `LIMIT 结果数 OFFSET 偏移量;`
* 格式2: `LIMIT 偏移量, 结果数;`
* 注意: 以上两种格式的参数位置是相反的, 不要写错!

```
USE mybase;

# 分页(1页2条), 第1页
SELECT * FROM sort LIMIT 2 OFFSET 0; 条数 * (页数 - 1)

# 分页(1页2条), 第2页
SELECT * FROM sort LIMIT 2 OFFSET 2; 条数 * (页数 - 1)

# 分页(1页2条), 第3页
SELECT * FROM sort LIMIT 2 OFFSET 4; 条数 * (页数 - 1)

# 分页(1页2条), 第4页
SELECT * FROM sort LIMIT 2 OFFSET 6;
```

### `UNION`合并操作符
* 作用: 用于将两个或多个SELECT语句的查询结果合并为一个结果集
* 必要条件:
* 所有合并的SELECT结果集必须拥有**相同的列**(列的数量相同, 列名相同, 列的顺序相同)
* 默认情况下, **UNION会对结果集进行去重**, 如果不想去重, 可以使用`UNION ALL`

```sql
# 格式
SELECT column_name, ... FROM table_name1
[WHERE condition]
UNION [ALL|DISTINCT]
SELECT column_name, ... FROM table_name2
[WHERE condition];

# 示例
SELECT username FROM user_table1
UNION
SELECT username FROM user_table2;

SELECT * FROM sort WHERE sid = 1 OR sid = 7;

(SELECT * FROM sort WHERE sid = 1)
UNION
(SELECT * FROM sort WHERE sid = 7)

(SELECT * FROM sort)
UNION
(SELECT * FROM sort)
```

* 演示

```
# 第一个SELECT
SELECT username FROM user_table1;

# 输出
|username|
|--------|
|zhangsan|

# 第二个SELECT
SELECT username FROM user_table2;

# 输出
|username|
|--------|
| lisi|

# 使用UNION合并
(SELECT username FROM user_table1)
UNION
(SELECT username FROM user_table2);

# 输出
|username|
|--------|
|zhangsan|
| lisi|
```

### 子查询(也叫嵌套查询)

* 子查询: 使用SELECT语句查询出来的结果集, 实际上也可以看做一个表. 既然是一个表, 我们就可以用他作为其他查询语句的表
* 子查询语句也是一个SELECT语句, 使用小括号`()`括起来
* 注意:
* 在SELECT查询语句中使用子查询, 相当于把结果集看做一个表
* 如果作为WHERE子句的条件, 则子查询可以不起别名
* 如: `SELECT * FROM user_table1 WHERE age < (SELECT AVG(age) FROM user_table1);`
* 如果作为FROM后被查询的表, **子查询必须起别名**
* 如: `SELECT * FROM (SELECT username FROM user_table1) AS user WHERE username = 'zhangsan';`
* 注意:
* 如果不起别名会报错: Every derived table must have its own alias
* 虽然起了别名, 但结果集实际还是结果集, 并不会变成真的表
* 使用UPDATE, DELETE语句时, 被更新的表不能用在set或where的子查询
* 否则会报错: `You can't specify target table 'xxx' for update in FROM clause`
* 解决办法: 在操作被更新的表的子查询外再套一层SELECT * FROM(是否取别名取决于是用在FROM还是WHERE子句), 然后外层再执行UPDATE或DELETE
* 如: `DELETE FROM stutb WHERE score < (SELECT * FROM (SELECT AVG(score) FROM stutb WHERE department LIKE 'javaEE基础班') AS avgscore);`

```sql
# 一个SELECT查询
SELECT username FROM user_table1;

# 输出
|username|
|--------|
|zhangsan|
| lisi|
|  wangwu|

# 如果把以上看做一个表, 假设这个表名为user, 则我们会这样写
SELECT * FROM user WHERE username = 'zhangsan';

# 可以得到
|username|
|--------|
|zhangsan|

# 所以, 我们把user替换为刚才的SELECT语句, 并用小括号将SELECT语句括起来
# 替换结果类似于下面这样
# SELECT * FROM user                                     WHERE username = 'zhangsan';
# SELECT * FROM (SELECT username FROM user_table1) AS user WHERE username = 'zhangsan';
# 执行一下
SELECT * FROM (SELECT username FROM user_table1) WHERE username = 'zhangsan';

# 也可以得到
|username|
|--------|
|zhangsan|

# 这就是子查询, 括号内的查询相当于父查询语句的一个孩子, 使用子查询的结果作为表继续查询

SELECT sname FROM sort;

sheet = SELECT sname FROM sort;

SELECT * FROM (SELECT sname FROM sort) AS z WHERE sname = '家电';
```

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

## 今日总结

* 高级SQL
* 排序
* `ORDER BY 列名 ASC|DESC;`
* 注意:
* 默认顺序为升序ASC
* WHERE在前, ORDER BY在后
* 聚合函数
* 用在`SELECT`后或`HAVING`后
* `COUNT(列名)`: 计算记录数量
* NULL值不会被算入数量
* `SUM(列名)`: 将该列的值求总和
* 如果不是数字类型, 则结果为0
* `MAX(列名)`: 求该列值中的最大值
* `MIN(列名)`: 求该列值中的最小值
* `AVG(列名)`: 求该列所有值的平均值
* 如果不是数字类型, 则结果为0
* 分组
* `GROUP BY 列名 HAVING 条件;`: 对查询结果集分组, 然后再按条件过滤
* 注意:
* 聚合函数与分组的使用效果
* 使用聚合函数后再进行分组, 才相当于汇总统计
* `SELECT zname, SUM(zmoney) FROM zhangwu GROUP BY zname;`
* 不使用聚合函数的分组, 只有第一条记录的值
* `SELECT zname, zmoney FROM zhangwu GROUP BY zname;`
* `HAVING`和`WHERE`的区别
* 过滤时机不同
* WHERE是在分组前先对查询结果进行过滤
* HAVING是在查询结果分组后再次过滤
* 是否可跟随聚合函数不同
* WHERE条件中不可使用聚合函数
* HAVING条件中可以使用聚合函数
* 条件可用列名不同
* HAVING条件中的列名必须是SELECT或GROUP BY中使用过的列名
* WHERE条件中可以使用表的所有列名
* JDBC
* JDBC: Java数据库连接. SUN公司提供的接口, 用来让数据库厂商实现数据库操作的功能
* 数据库驱动: 就是JDBC的实现
* JDBC开发步骤:
* 前提: 导包(数据库驱动)
* 步骤:
1. 注册驱动
* `DriverManager.registerDriver()`: 是真实的注册驱动的方法
* 因为驱动中实现了这个方法, 推荐我们使用:
* `Class.forName("数据库驱动的Driver实现类全类名")`
2. 获取连接:
* `Connection conn = DriverManager.getConnection(url, username, password)`
3. 获取执行平台:
* `Statement s = conn.createStatement();`: 不安全的
* `PreparedStatement ps = conn.prepareStatement(String sql);`: 可以防范SQL注入
* 设置参数:
* 占位符: `?`, 即使是字符串, 也不要给问号加引号
* `void setObject(int index, Object obj)`
* index从1开始
4. 执行SQL语句
* Statement
* 增删改: `int s.executeUpdate(String sql)`
* 判断操作是否成功: 判断返回值是否`>0`则生效, 否则不成功
* 查: `ResultSet rs = s.executeQuery(String sql)`
* 思考: 判断操作是否成功: 是返回null呢还是调用`.next()`用是否为true判断?
* PreparedStatement
* 增删改: `int ps.executeUpdate();`
* 判断操作是否成功: 判断返回值是否`>0`则生效, 否则不成功
* 查: `ResultSet rs = ps.executeQuery();`
5. 处理结果
* `ResultSet`
* `boolean next();`: 将指针向后移动, 如果有下一个记录则true, 否则false
* 默认不执行的之后, 指针在第一条记录之前; 只有调用一次才能指向第一条: `while (rs.next()) {}`
* `String getString(String 列名)`: 获取指定列的值, 以String类型
* `Object getObject(String 列名)`: 获取指定列的值, 以Object类型
6. 释放资源
* `resultSet.close()`
* `preparedStatement.close()`
* `statement.close()`
* `connection.close()`
* SQL注入
* 原理: 拼接SQL导致用户的输入可以改变SQL的意义, Statement无法避免注入
* 超级密码: `1' OR '1=1`
* 如何避免: 使用PreparedStatement替换Statement
* JDBCUtils工具类的自定义
* 在静态代码块中初始化连接对象
* 定义方法获取连接对象, 释放资源

* 今日重点
* 高级SQL语句
* 排序ORDER BY
* 聚合函数
* SUM()
* COUNT()
* MIN()
* MAX()
* AVG()
* 分组: GROUP BY 列名 HAVING 条件
* JDBC
* 数据库驱动和JDBC原理: 多态的调用
* 代码:
* JDBC操作的6个步骤:
1. (先导入jar包) 注册驱动
2. 获取连接
3. 获取执行平台
4. 执行SQL语句
5. 处理结果
6. 释放资源
* 执行平台
* Statement(不能用)
* PreparedStatement(重点)

```
// Statement
// 1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2. 获取连接
Connection conn = DriverManager.getConnection(url, user, password);
// 3. 获取执行平台
Statement statement = conn.createStatement();
// 4. 执行SQL
int rows = statement.executeUpdate(sql); // INSERT UPDATE DELETE DML
ResultSet rs = statement.executeQuery(sql);  // SELECT             DQL
// 5. 操作数据
...
// 6. 释放资源
rs.close();
statement.close();
conn.close()
```

```
// PreparedStatement
// 1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2. 获取连接
Connection conn = DriverManager.getConnection(url, user, password);
// 3. 获取执行平台
String sql = ".... ? ...";
PreparedStatement pst = conn.prepareStatement(sql);
// 如果有参数, 设置参数
pst.setObject(占位符的序号, 参数值);
// 4. 执行sql语句
int rows = pst.executeUpdate();  // INSERT UPDATE DELETE
ResultSet rs = pst.executeQuery();             // SELECT
// 5. 处理结果
while (rs.next()) {
//rs.getXxx();
}
// 6. 释放资源
rs.close();
pst.close();
conn.close();
```

```
// JDBCUtils
public class JDBCUtils {

// 私有构造方法
private JDBCUtils() {}

// 定义静态变量Connection
private static Connection conn;

// 使用静态代码块初始化
static {
try {
// 注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 获取连接
String url = "";
String user = "";
String password = "";
conn = DriverManager.getConnection(url, user, password);
} catch(Exception e) {
throw new RuntimeException("数据库连接失败");
}
}

// 获取连接对象
public static Connection getConnection() {
return conn;
}

// 释放资源
public static void close(Connection conn, Statement statement, ResultSet rs) {  // null
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {}
}
}

# day03 加载本地配置, DBUtils

## 数据表与Java类的转换关系案例演示

* 案例需求:
* 使用JDBC查询sort表的所有记录, 将查询出的结果集中的每条记录封装为一个Sort类对象, 再将这些Sort对象存入List
* 步骤:
1. 按照表和Java类的对应关系, 定义一个以表名为名的类
2. 类中属性就是表的字段
3. 创建一个对象来封装查询出的记录的各个列的值
4. 结果集中的多条记录, 就是多个对象, 将这些对象存入一个集合中, 用集合来表示查询的结果集
* 有什么用?
* 这种方式完成了数据库表向Java对象的转化, 叫做ORM, Object Relation Mapping, 对象关系映射. 就是指Java对象和关系型数据表的相互转换过程
* 如果我们能将数据表转换为Java的类, 那我们操作数据库的表就相当于操作集合和对象, 这样会变得简单很多

## Properties配置文件: 将JDBCUtils的连接参数保存在配置文件中

* JDBC中连接数据库的配置:
* 会改变的配置项:
* 驱动类类名
* url
* 用户名
* 密码
* 我们希望修改更方便, 而不是每次都要修改Java代码后重新编译, 所以想到可以用读取文件的方式动态的获取这些信息
* Properties文件
* 以`.properties`后缀结尾的文件, 以`key=value`方式编写配置
* 配置文件创建位置: 在项目的`src`目录下创建该配置文件, 在编译时会将该配置文件自动复制到`bin`目录中

## Properties配置文件: 创建和编写文件

* 注意:
* properties文件放在src目录下就可以, 不要放在包中

* 3分钟练习: 创建配置文件
* 创建项目s3-day03
* 在src目录下创建`database.properties`文件
* 在其中写入键值对信息
* driverClass=com.mysql.jdbc.Driver
* url=jdbc:mysql://localhost:3306/mybase
* username=root
* password=123456
* 查看在bin目录下是否已经自动复制了配置文件
* 注意: 不要直接在bin目录下创建

## Properties配置文件: 使用类加载器加载文件

* 回忆Java的编译原理和内存机制:
* src下存放java文件, 编译后生成class文件到bin目录下.
* src下的其他文件, 编译后也会自动复制到bin目录下
* 程序运行时, 要使用一个类, 必须先加载类到内存中, 所以就会使用`类加载器`将要用的类的字节码加载到方法区中
* 所以使用类加载器, 同样可以找到bin目录下的其他文件
* **注意**:
* 在src目录下修改配置文件, 内容会自动同步到bin目录中
* 但是不能直接删除bin目录下的配置文件, 如删除则需要重新在src目录中创建配置文件
* 加载bin目录下文件的方式:

```java
// 1.获取类加载器: 获取某个类的字节码对象, 通过字节码对象获取类加载器对象
ClassLoader classLoader = 类名.class.getClassLoader();
// 2.使用类加载器对象加载配置文件, 返回字节输入流
InputStream in = classLoader.getResourceAsStream("相对于bin目录的相对路径");
// 3.使用Properties加载输入流, 获得键值对信息
properties.load(in);
```

## 读取配置文件, 连接数据库

* 注意键名不要写错, 会导致读取配置文件时找不到对应的值
* 回忆Properties
* `String getProperty(String key)`: 通过键获取值

```java
// 最终实现方式:

InputStream in = 某个类名.class.getClassLoader().getResourceAsStream("相对于bin目录的相对路径");
Properties properties = new Properties();
properties.load(in);

String driverClass = properties.getProperty("driverClass");
String url = properties.getProperty("url");
String username = properties.getProperty("username");
String password = properties.getProperty("password");

Class.forName(driverClass);
connection = DriverManager.getConnection(url, username, password);
```

## 打造能读取配置文件的JDBC工具类

* 10分钟练习:
* 继续使用s3-day03项目, 建包com.itheima.utils
* 建类JDBCUtilsConfig, 并优化
* 在工具类中定义5个静态变量:
* private static Connection conn;
* private static String driverClass;
* private static String url;
* private static String username;
* private static String password;
* 定义读取配置文件的方法:
* private static void readConfig()
* 其中使用ClassLoader加载配置文件, 读取配置, 对静态变量driverClass, url, username, password进行初始化
* 定义静态代码块, 在其中
* 调用readConfig()方法读取配置
* 调用Class.forName(driverClass)加载驱动
* 调用conn = DriverManager.getConnection(url, username, password)对连接对象进行初始化
* 定义public static Connection getConnection()方法
* close()释放资源方法不用再定义, 后面会使用DbUtils里面的释放资源的方法

```
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybase
username=root
password=123456
```

```java
/*
JDBC工具类: v2.0
可以读取配置文件
*/
public class JDBCUtilsConfig {

// 声明静态变量
private static Connection connection;
private static String driverClass;
private static String url;
private static String username;
private static String password;

// 静态代码块进行初始化
static {
try {
// 读取配置文件
readConfig();
// 注册驱动
Class.forName(driverClass);
// 获取连接
connection = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("数据库连接失败");
}
}

// 私有构造方法
private JDBCUtilsConfig() {}

// 读取配置文件方法
private static void readConfig() throws Exception {
// 使用类加载器加载bin目录下的配置文件, 获取输入流
InputStream in = JDBCUtilsConfig.class.getClassLoader().getResourceAsStream("database.properties");
// 创建Properties对象
Properties properties = new Properties();
// 加载数据
properties.load(in);
// 通过键获取值
driverClass = properties.getProperty("driverClass");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
}

// 获取连接对象
public static Connection getConnection() {
return connection;
}

}
```

## 测试工具类

* 3分钟练习: 测试工具类
* 继续使用包com.itheima.utils
* 新建Test类, 编写main方法
* 使用优化后的JDBCUtilsConfig获取连接
* 打印连接查看连接对象是否存在

```java
public class Test {

public static void main(String[] args) {
Connection connection = JDBCUtilsConfig.getConnection();
System.out.println(connection);  // com.mysql.jdbc.JDBC4Connection@1fcc7a9
}
}
```

---

## DBUtils工具类介绍, 三个核心类: 一个更好的JDBC工具类

* `DBUtils`
* 是Apache的Commons项目中的组件, 用于简化JDBC的开发. 和我们自定义的JDBCUtils的目的一样, 简化开发
* 三个核心类/接口
* `QueryRunner`类: 提供操作SQL语句的API
* `QueryRunner()`: 创建对象
* `int update(Connection conn, String sql, Object... param)`: 执行INSERT, DELETE, UPDATE语句, 同时传入占位符的参数值, 是可变参数
* `<T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params)`: 执行SELECT查询, 并根据传入的结果集处理器来处理结果
* `ResultSetHandler`接口: 定义SELECT操作后对结果集的操作方法, 可以将结果集转换为Java类
* `ArrayHandler`类: 将结果集的第一条记录封装到一个`Object[]`数组中, 数组中的每一个元素就是该条记录中的每一列的值
* `ArrayListHandler`类: 将结果集中的每一条记录都封装到一个`Object[]`数组中, 再将这些数组封装到`List<Object[]>`集合中
* `BeanHandler`类: 将结果集中的第一条记录封装到一个指定的JavaBean中
* `BeanListHandler`类: 将结果集中的每一条记录都封装到一个指定的JavaBean中, 再将这些JavaBean封装到`List<T>`集合中
* `ColumnListHandler`类: 将结果集中指定的列的字段值, 封装到一个List集合. 用于竖向查一个列
* `ScalarHandler`类: 获取单一结果, 用于聚合函数如`SELECT COUNT(*) FROM 表`的操作
* `MapHandler`类: 将结果集的第一条记录封装到一个Map中. key是列名, value是第一条记录的每个字段的值
* `MapListHandler`类: 将结果集的每一条记录封装到一个Map中. key是列名, value是第一条记录的每个字段的值. 然后再将这些Map封装到一个List中
* `DbUtils`类: 用于关闭资源和事务处理
* `static void closeQuietly(Connection conn)`: 关闭连接, 内部处理了异常

## 事务的简单介绍

* 事务:
* transaction, 是一组操作, 这组操作要么全都成功, 要么全都失败
* 举例: 银行转账, 一方扣钱, 另一方加钱, 必须同时成功, 否则要同时失败. 不能一方扣了钱, 另一方还没有加钱

## QueryRunner类: update方法: 介绍

* `QueryRunner`类
* `int update(Connection conn, String sql, Object... params)`: 执行INSERT, DELETE, UPDATE语句, 同时传入占位符的参数值, 是可变参数

## QueryRunner类: update方法: 执行INSERT语句

* `QueryRunner`类
* `QueryRunner()`: 创建对象
* `int update(Connection conn, String sql, Object... params)`: 执行INSERT, UPDATE, DELETE操作, 返回成功行数
* `DbUtils`类
* `static void closeQuietly(Connection conn)`: 关闭连接, 内部处理了异常

* 5分钟练习: QueryRunner执行INSERT语句
* 继续使用项目s3-day03, 建包com.itheima.practice_01
* 建Test类
* 定义静态成员变量connection, 使用自定义的JDBCUtilsConfig工具类获取connection对象
* 在类中定义public static void insert()方法, 在其中测试QueryRunner类的操作
* 创建QueryRunner对象
* 编写插入记录的sql语句, 使用占位符?
* 设置占位符参数: "体育用品", 289.32, "购买体育用品"
* 调用update()方法执行sql, 返回行数
* 打印行数
* 使用DbUtils类的静态方法closeQuietly(Connection conn), 关闭连接对象
* 在main方法中调用insert()方法测试, 在SQLYog中查看是否成功

```java
public class Test {

private static Connection connection = JDBCUtilsConfig.getConnection();

public static void main(String[] args) throws SQLException {
insert();
}

// 测试DbUtils插入数据
private static void insert() throws SQLException {
// 创建QueryRunner对象
QueryRunner queryRunner = new QueryRunner();
// 定义SQL
String sql = "INSERT INTO sort (sname, sprice, sdesc) VALUES (?, ?, ?);";
// 定义参数数组
Object[] params = {"体育用品", 289.32, "购买体育用品"};
// 执行SQL语句
int rows = queryRunner.update(connection, sql, params);
System.out.println(rows);
// 释放资源
DbUtils.closeQuietly(connection);
}
}
```

## QueryRunner类: 使用update方法执行UPDATE语句

* 5分钟练习: 执行UPDATE语句
* 仍然在刚才的Test类中, 定义public static void update(), 在其中测试执行UPDATE语句
* 创建QueryRunner对象
* 编写sql语句: 将sid为4的记录修改为: "花卉", 100.89, "情人节玫瑰"
* 参数使用占位符, 传入相关参数
* 调用执行sql语句, 打印行数
* 使用DbUtils的方法释放资源
* 在main方法中调用update(), 在SQLyog中查看是否成功

```java
// 测试DbUtils更新数据
private static void update() throws SQLException {
// 创建QueryRunner对象
QueryRunner queryRunner = new QueryRunner();
// 定义SQL
String sql = "UPDATE sort SET sname=?, sprice=?, sdesc=? WHERE sid=?;";
// 定义参数数组
Object[] params = {"花卉", 100.89, "情人节玫瑰", 4};
// 执行SQL语句
int rows = queryRunner.update(connection, sql, params);
System.out.println(rows);
// 释放资源
DbUtils.closeQuietly(connection);
}
```

## QueryRunner类: 使用update方法执行DELETE语句

* 5分钟练习: 执行DELETE语句
* 仍然在刚才的Test类中, 定义另一个静态方法delete(), 在其中测试执行DELETE语句
* 创建QueryRunner对象
* 编写sql语句: 将sid为8的体育用品的记录删除
* 参数使用占位符, 传入相关参数
* 调用执行sql语句, 打印行数
* 使用DbUtils的方法释放资源
* 在main方法中调用delete(), 在SQLyog中查看是否成功

```java
// 测试DbUtils删除数据
private static void delete() throws SQLException {
// 创建QueryRunner对象
QueryRunner queryRunner = new QueryRunner();
// 定义SQL
String sql = "DELETE FROM sort WHERE sid=?;";
// 执行SQL语句
int rows = queryRunner.update(connection, sql, 8);
if (rows > 0) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
// 释放资源
DbUtils.closeQuietly(connection);
}
```

---=

## 根据账务表定义JavaBean类: 表和Java类的转换实现

* JavaBean的要求
* 实现`java.io.Serializable`接口, 定义`serialVersionId`
* 成员属性使用`private`修饰
* 提供`public`的get/set方法
* 至少提供一个无参的构造

## DBUtils工具类处理结果集

* `ResultSetHandler`接口
* 有很多类型的实现类, 常用实现类如下
* `ArrayHandler`: 将结果集的第一条记录封装到一个`Object[]`数组中, 数组中的一个元素就是该条记录中的一个字段值
* `ArrayListHandler`: 将结果集中的每一条记录都封装到一个`Object[]`数组中, 再讲这些数组封装到`List`集合中
* `BeanHandler`: 将结果集中的第一条记录封装到一个指定的JavaBean中
* `BeanListHandler`: 将结果集中的每一条记录都封装到一个指定的JavaBean中, 再将这些JavaBean封装到`List`集合中
* `ColumnListHandler`: 将结果集中指定的列的字段值, 封装到一个List集合. 查一个列
* `ScalarHandler`: 用于单数据, 如`SELECT COUNT(*) FROM 表`的操作
* `MapHandler`: 将结果集的第一条记录封装到一个Map中. key是列名, value是第一条记录的每个字段的值
* `MapListHandler`: 将结果集的每一条记录封装到一个Map中. key是列名, value是第一条记录的每个字段的值. 然后再将这些Map封装到一个List中
* `QueryRunner`对象执行SELECT查询的方法:
* `<T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params)`: 执行SELECT查询, 并根据传入的结果集处理器来处理结果
* 注意返回值是泛型

## QueryRunner类: 使用query方法执行查询

(继续看下一个知识点)

## 结果集处理: ArrayHandler

* `ArrayHandler`:
* 将结果集的第一条记录封装到一个`Object[]`数组中, 数组中的一个元素就是该条记录中的一个字段值
* 如何判断是否有结果?
* 判断返回数组的长度是否大于0

## 结果集处理: ArrayListHandler

* `ArrayListHandler`:
* 将结果集中的每一条记录都封装到一个`Object[]`数组中, 再将这些数组封装到`List`集合中
* 如何判断查询到结果?
* 判断list.size()是否大于0

* 5分钟练习:
* 仍然在com.itheima.practice_01包中的Test类中, 定义另一个静态方法arrayListHandler()
* 创建QueryRunner对象
* 定义查询sort表中所有列的记录的SQL语句
* 使用QueryRunner调用query()方法执行sql语句, 使用创建匿名对象的方式传入ArrayListHandler对象
* 注意返回值是`List<Object[]>`集合
* 先增强for遍历List集合, 得到每个Object[]
* 然后增强for遍历每个Object[], 获取记录值, 不换行打印记录值
* 注意每打印完一条记录要记得换行
* 使用DbUtils关闭连接
* 在main方法中调用测试

```java
// 测试DbUtils查询数据: ArrayListHandler
private static void arrayListHandler() throws SQLException {
// 创建QueryRunner对象
QueryRunner queryRunner = new QueryRunner();
// 定义SQL
String sql = "SELECT * FROM sort";
// 执行语句
List<Object[]> result = queryRunner.query(connection, sql, new ArrayListHandler());
// 遍历集合
for (Object[] objects : result) {
// 继续遍历每条记录的object数组
for (Object value : objects) {
// 打印该条记录的每列的值, 不换行
System.out.print(value + "\t");
}
// 打印完一条记录后换行
System.out.println();
}
// 释放资源
DbUtils.close(connection);
}
```

---

## 结果集处理: BeanHandler

* `BeanHandler<T>(Class<T> type)`:
* **注意**: T泛型写JavaBean的类名, 参数写JavaBean类的字节码对象
* 将结果集中的第一条记录封装到一个指定的JavaBean中
* 使用该处理器的前提
* JavaBean类必须有**空参构造**. 这是一个惯例, 以后大部分ORM框架都有这个要求
* 如何判断是否有结果?
* 判断返回的对象是否为`null`

## 结果集处理: BeanListHandler

* `BeanListHandler<T>(Class<T> type)`:
* **注意**: T泛型写JavaBean的类名, 参数写JavaBean类的字节码对象
* 将结果集中的每一条记录都封装到一个指定的JavaBean中, 再将这些JavaBean封装到`List`集合中

* 5分钟练习:
* 仍然在com.itheima.practice_01包中
* 建Sort类
* 在其中将表转换为Java类的定义, 注意几点
* 私有成员变量
* 空参构造, 有参构造
* get/set方法
* 自动生成toString方法
* 在Test类中定义另一个静态方法beanListHandler()
* 创建QueryRunner对象
* 定义查询sort表中所有列的记录的SQL语句
* 使用QueryRunner调用query()方法执行sql语句, 使用创建匿名对象的方式传入new BeanListHandler<Sort>(Sort.class)对象, 注意传入Sort的字节码对象
* 返回值是`List<Sort>`对象, 遍历集合获取对象, 打印对象
* 使用DbUtils关闭连接
* 在main方法中调用查看结果

```java
// 测试DbUtils查询数据: BeanListHandler
private static void beanListHandler() throws SQLException {
// 创建QueryRunner对象
QueryRunner queryRunner = new QueryRunner();
// 定义SQL
String sql = "SELECT * FROM sort";
// 执行语句
List<Sort> result = queryRunner.query(connection, sql, new BeanListHandler<Sort>(Sort.class));
// 遍历集合
for (Sort sort : result) {
System.out.println(sort);
}
// 释放资源
DbUtils.close(connection);
}
```

## 结果集处理: ColumnListHandler

* `ColumnListHandler`:
* 将结果集中指定的列的字段值, 封装到一个List集合. 查一个列
* 注意:
* 如果创建ColumnListHandler对象时传入的列名不在查询语句中被查询的列中, 则会抛出异常

## 结果集处理: ScalarHandler

* `ScalarHandler`:
* 用于单一值的结果集, 如`SELECT COUNT(*) FROM 表`的操作
* 返回结果的类型:
* 整数是Long
* 小数是Double

* 5分钟练习: 查询`COUNT(*)`
* 仍然在com.itheima.practice_01包中的Test类中, 定义另一个静态方法scalarHandler()
* 创建QueryRunner对象
* 定义SQL语句: SELECT COUNT(*) FROM sort;
* 使用QueryRunner调用query()方法执行sql语句, 使用创建匿名对象的方式传入new ScalarHandler<Long>()对象
* 注意返回值是Long对象, 打印对象
* 使用DbUtils关闭连接
* 在main方法中调用查看结果

```java
// 测试DbUtils查询数据: ScalarHandler
private static void scalarHandler() throws SQLException {
// 创建QueryRunner对象
QueryRunner queryRunner = new QueryRunner();
// 定义SQL
String sql = "SELECT COUNT(*) FROM sort";
// String sql = "SELECT AVG(sprice) FROM sort";
// 执行语句
long count = queryRunner.query(connection, sql, new ScalarHandler<Long>());
// double avgPrice = queryRunner.query(connection, sql, new ScalarHandler<Double>());
// 打印结果
System.out.println(count);
// 释放资源
DbUtils.close(connection);
}
```

---

## 结果集处理: MapHandler

* `MapHandler`: 将结果集的第一条记录封装到一个Map中. key是列名, value是第一条记录的每个字段的值

## 结果集处理: MapListHandler

* `MapListHandler`: 将结果集的每一条记录封装到一个Map中. key是列名, value是第一条记录的每个字段的值. 然后再将这些Map封装到一个List中

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

## 扩展

* 上网看看
* 怎么从mysql官网找mysql程序, 以及mysql的jdbc驱动
* Apache是什么?
* Commons项目是什么?
* 开源又是什么?

## 今日总结

* 使用Properties配置文件
* 作用: 将配置信息从源代码中隔离, 达到可以直接修改配置文件, 而不用修改源代码的目的
* 配置文件的创建
* 在`src`目录下创建, 会自动在`bin`目录下生成
* 以`.properties`后缀结尾
* 配置文件的格式: 键值对, 一行一个键值对
* 读取配置文件
* 通过类加载器, 去加载`bin`目录下的配置文件
* 类加载器的获取:
1. 获取类的字节码对象: `类名.class`
2. 通过类的字节码对象获取类加载器: `ClassLoader 字节码对象.getClassLoader()`
3. 通过类加载器对象读取资源文件: `InputStream getResourceAsStream(String 配置文件路径)`
4. 创建Properties对象: `Properties p = new Properties();`
5. 通过该对象加载流(此时Properties对象才有数据): `p.load(inputStream)`
6. 通过Properties对象获取属性值: `String p.getProperty(String key)`
* DbUtils
* 是什么: 是Apache的Commons项目中的一个组件, 开源的工具类(第三方库)
* 三个核心类/接口
* `QueryRunner`类: 提供操作SQL语句的API
* `QueryRunner()`: 创建对象
* `int update(Connection conn, String sql, Object... param)`: 执行INSERT, DELETE, UPDATE语句, 同时传入占位符的参数值, 是可变参数
* `<T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params)`: 执行SELECT查询, 并根据传入的结果集处理器来处理结果
* `ResultSetHandler`接口: 定义SELECT操作后对结果集的操作方法, 可以将结果集转换为Java类
* `ArrayHandler`类: 将结果集的第一条记录封装到一个`Object[]`数组中, 数组中的每一个元素就是该条记录中的每一列的值
* `ArrayListHandler`类: 将结果集中的每一条记录都封装到一个`Object[]`数组中, 再将这些数组封装到`List<Object[]>`集合中
* `BeanHandler`类: 将结果集中的第一条记录封装到一个指定的JavaBean中
* `BeanListHandler`类: 将结果集中的每一条记录都封装到一个指定的JavaBean中, 再将这些JavaBean封装到`List<T>`集合中
* `ColumnListHandler`类: 将结果集中指定的列的字段值, 封装到一个List集合. 用于竖向查一个列
* `ScalarHandler`类: 获取单一结果, 用于聚合函数如`SELECT COUNT(*) FROM 表`的操作
* `MapHandler`类: 将结果集的第一条记录封装到一个Map中. key是列名, value是第一条记录的每个字段的值
* `MapListHandler`类: 将结果集的每一条记录封装到一个Map中. key是列名, value是第一条记录的每个字段的值. 然后再将这些Map封装到一个List中
* `DbUtils`类: 用于关闭资源和事务处理
* `static void closeQuietly(Connection conn)`: 关闭连接, 内部处理了异常

```java
/*
JDBC工具类: v2.0
可以读取配置文件
*/
public class JDBCUtilsConfig {

// 声明静态变量
private static Connection connection;
private static String driverClass;
private static String url;
private static String username;
private static String password;

// 静态代码块进行初始化
static {
try {
// 读取配置文件
readConfig();
// 注册驱动
Class.forName(driverClass);
// 获取连接
connection = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("数据库连接失败");
}
}

// 私有构造方法
private JDBCUtilsConfig() {}

// 读取配置文件方法
private static void readConfig() throws Exception {
// 使用类加载器加载bin目录下的配置文件, 获取输入流
InputStream in = JDBCUtilsConfig.class.getClassLoader().getResourceAsStream("database.properties");
// 创建Properties对象
Properties properties = new Properties();
// 加载数据
properties.load(in);
// 通过键获取值
driverClass = properties.getProperty("driverClass");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
}

// 获取连接对象
public static Connection getConnection() {
return connection;
}
}
```

# day04 DBCP连接池

## 连接池介绍

* 为什么需要多个连接?
* 因为实际项目运行起来会有很多用户同时操作, 比如很多用户可能同时在查看商品列表, 而商品列表的获取就需要连接数据库执行SELECT语句
* 普通方式连接数据库的弊端
* 每次操作都新建一个连接, 然后执行SQL语句, 执行完毕后关闭连接
* 由于获取连接和释放资源都是既消耗时间, 又消耗系统资源的两个过程, 所以如果每个用户在点击查看商品列表时都去建立一次TCP连接到数据库然后再查询数据, 那么页面显示速度是很慢的
* 解决办法:
* 使用数据库连接池技术
* 什么是连接池?
* 一次性创建多个连接对象, 存放在一个集合中, 好像把这些连接放在一个池子里. 当需要连接数据库时, 不用再创建新的, 而是直接从连接池中拿出来用, 用完再放回连接池内待用, 而不是销毁.
* 通常情况我们采用连接池技术, 来共享Connection. 这样就不需要每次都创建和释放连接了, 而是将这些操作都交给连接池操作
* 当要获取Connection对象时, 从连接池中拿出一个, 当该Connection使用完毕后, 再放回连接池

```
   连接池(存放Connection对象的池子)
+-------------------------------------+
|                                  |
|  连接1  o---------------o          |
|  连接2  o---------------o          |
|  连接3  o---------------o          |
|  连接4  o---------------o          |
|  连接5  o---------------o          |
|                                  |
+-------------------------------------+

有3个用户使用数据库连接, 从连接池中获取连接
+-------------------------------------+
|                                  |
|                                  |    用户1使用连接1  o---------------o
|                                  |    用户2使用连接2  o---------------o
|                                  |    用户3使用连接3  o---------------o
|  连接4  o---------------o          |
|  连接5  o---------------o          |
|                                  |
+-------------------------------------+

用完之后再将Connection放回连接池
+-------------------------------------+
|                                  |
|  连接1  o---------------o          |
|  连接2  o---------------o          |
|  连接3  o---------------o          |
|  连接4  o---------------o          |
|  连接5  o---------------o          |
|                                  |
+-------------------------------------+
```

## 连接池概念规范, DataSource接口

* `javax.sql.DataSource`接口: 数据源. SUN公司提供的用于让不同厂商实现各自的数据库连接池
* 作用: 通过切换不同的实现类, 可以切换不同的连接池
* 常见连接池
* `DBCP`: Apache公司Commons项目的一个组件, 是开源项目, 是Tomcat内置连接池
* `C3P0`: 开源连接池, Hibernate和Spring都在使用
* `Druid`: 阿里巴巴开发的开源连接池

## DBCP连接池介绍

* `DBCP`: DataBase Connection Pool, 数据库连接池

## 导入jar包

* 所需jar包
* DBCP连接池所需jar包:
* `commons-dbcp-x.x.jar`: DBCP核心
* `commons-pool-x.x.x.jar`: DBCP依赖的连接池功能
* DbUtils工具类
* `commons-dbutils-x.x.jar`: DbUtils工具类
* MySQL数据库驱动
* `mysql-connecter-java-x.x.x-bin.jar`: 数据库驱动

* 3分钟练习
* 创建项目s3-day04
* 导入4个相关jar包, 添加到Build Path

---

## BasicDataSource类的使用

* DBCP的`BasicDataSource`类: 实现了`javax.sql.DataSource`接口
* 构造方法:
* `BasicDataSource()`: 创建对象
* 成员方法:
* `void setDriverClassName(String name)`: 设置驱动类名
* `void setUrl(String url)`: 设置连接地址
* `void setUsername(String username)`: 设置用户名
* `void setPassword(String password)`: 设置密码
* `Connection getConnection()`: 获取连接对象

## BasicDataSource类的常见配置

* `BasicDataSource`类的配置方法
* 成员方法:
* `void setInitialSize(int size)`: 初始连接数. 连接池中最初的连接数
* `void setMaxActive(int n)`: 最大连接数量. 程序能够连接数据库的最大连接数量
* `void setMinIdle(int n)`: 最小空闲连接. 连接池中允许存在的最小空闲数量, 如果小于该数量, 则会创建新的连接, 直到满足该最小空闲连接数
* `void setMaxIdle(int n)`: 最大空闲连接. 连接池中允许存在的最大空闲数量, 如果超出该数量, 则超出的空闲连接会被真实关闭

```
空闲: 在连接池中, 未被真正使用的连接, 称为空闲连接
活跃: 正在被使用的连接, 称为活跃连接

初始连接数: 10
最大连接数: 8
最大空闲数: 5
最小空闲数: 1

创建连接池, 初始连接数: 10
+---------------+
|  ||||| |||||  |
+---------------+

如果长时间没有使用连接, 则因为最大空闲数要求是5个连接, 所以池中会真正关闭多余的连接.
目前池中有10个, 需要关闭5个
+---------------+
|  |||||       |
+---------------+

现在有5个用户要执行查询, 需要使用连接对象, 池中的5个都会被使用.
+---------------+
|             |  |||||
+---------------+

由于最小空闲数要求为1, 而目前池中没有连接了, 所以连接池会创建1个新的连接放入池中
+---------------+
|  |          |  |||||
+---------------+

又有4个用户需要执行查询, 所以从空闲连接中拿出使用.
但因为最大活跃连接数是8, 而目前已经有5个连接在活跃, 所以只能满足3个用户使用连接.
剩下1个用户, 虽然连接池中已经为了满足最小空闲数又补充了1个连接, 但仍然不能使用
+---------------+
|  |          |  ||||| |||
+---------------+

当8个用户使用完毕8个连接后, 这8个连接不会销毁, 而是放回连接池等待再次使用.
此时池中总共有9个连接
+---------------+
|  ||||| |||| |
+---------------+

而最大空闲数要求为5个, 那么多余的4个连接会被连接池真正关闭销毁, 释放资源. 从而最后剩下5个空闲连接
+---------------+
|  |||||       |
+---------------+

连接池的好处:
如果连接池中的连接数满足使用需求, 则可以减少建立TCP连接的时间, 提高效率
如果某些情况下, 连接池中的连接数不够使用, 连接池也会自动补充连接.
如果极端情况下, 对与连接的需求数量特别大, 也可以靠最大活跃连接数来限制创建的连接数量, 从而避免过度消耗内存
当连接使用高峰度过后, 连接池还会自动销毁多余的连接, 释放内存
```

## 优化JDBCUtils: 修改为获取DataSource

* 8分钟练习: 优化工具类
* 在s3-day04项目中, 建包com.itheima.utils
* 将昨天的JDBCUtils工具类复制进来, 修改工具类
* 定义静态BasicDataSource对象并初始化
* 在静态代码块中使用BasicDataSource对象进行配置
* 删除原有注册驱动和获取连接的代码
* 使用BasicDataSource对象设置连接信息(驱动类, url, 用户名, 密码)
* 使用BasicDataSource对象配置连接池
* 初始连接10
* 最大连接8
* 最大空闲5
* 最小空闲1
* 删除`getConnection()`方法, 添加`DataSource getDataSource()`方法.
* 目前在工具类中我们不再需要获取单个连接对象, 只需要获取数据源对象

```java
/*
JDBC工具类: v3.0
可以读取配置文件
使用DBCP连接池技术
*/
public class JDBCUtils {

// 声明静态变量
private static BasicDataSource dataSource = new BasicDataSource();
private static String driverClass;
private static String url;
private static String username;
private static String password;

// 静态代码块进行初始化
static {
try {
// 读取配置文件
readConfig();
// 设置连接池必选4个配置
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
// 设置连接池可选4个配置
dataSource.setInitialSize(10);  // 初始连接数
dataSource.setMaxActive(8);    // 最大活跃连接数
dataSource.setMaxIdle(5);    // 最大空闲连接数
dataSource.setMinIdle(1);    // 最小空闲连接数
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("数据库连接失败");
}
}

// 私有构造方法
private JDBCUtils() {}

// 读取配置文件方法
private static void readConfig() throws Exception {
// 使用类加载器加载bin目录下的配置文件, 获取输入流
InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("database.properties");
// 创建Properties对象
Properties properties = new Properties();
// 加载数据
properties.load(in);
// 通过键获取值
driverClass = properties.getProperty("driverClass");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
}

// 获取连接池数据源对象
public static DataSource getDataSource() {
return dataSource;
}
}
```

## 测试JDBCUtils工具类

* `QueryRunner`
* `QueryRunner QueryRunner(DataSource ds)`: 在创建对象时传入DataSource, 免去创建连接

* 5分钟练习:
* 使用SQLyog插入测试数据
* 建包com.itheima.practice_01
* 建类Test
* 定义静态的QueryRunner对象, 在初始化时使用QueryRunner(DataSource ds)构造方法, 并利用JDBCUtils获取DataSource对象传入
* 定义`insert()`方法
* 在其中使用queryRunner对象插入数据: "水果", 100.12, "刚上市的核桃"
* 在main方法中测试
* 定义`select()`方法
* 在其中使用queryRunner对象查询所有列的记录: 使用ArrayListHandler
* 在main方法中测试

```java
public class Test {

// 定义静态变量
private static QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());

public static void main(String[] args) {
// insert();
select();
}

// 查询测试
private static void select() {
try {
// 定义sql
String sql = "select * from sort";
// 执行语句
List<Object[]> list = queryRunner.query(sql, new ArrayListHandler());
for (Object[] objects : list) {
for (Object object : objects) {
System.out.print(object + "\t");
}
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("查询数据失败");
}
}

// 插入测试
private static void insert() {
try {
// 定义sql语句
String sql = "insert into sort (sname, sprice, sdesc) values (?, ?, ?);";
// 定义参数
Object[] params = {"水果", 100.12, "刚上市的核桃"};
// 执行语句
int rows = queryRunner.update(sql, params);
System.out.println(rows);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("插入数据失败");
}
}
}
```

---

## 登录注册案例: 需求分析

* 注册:
* 将用户要注册的用户名先查询库里是否存在(SELECT)
* 如果存在: 用户名已存在, 请更换
* 如果不存在: 创建新用户(INSERT INTO)
* 登录
* 将用户的用户名密码到库里查询是否存在(SELECT)
* 用户名和密码存在且匹配: 登录成功
* 用户名不存在或密码不匹配: 登录失败

## 登录注册案例: 创建数据库和数据表

* 假数据: 这个词在工作中经常说, 一般用于测试功能, 向数据库中写入一些自己模拟的数据

```sql
# day04 DBCP连接池, 登录案例

-- 创建登录注册测试数据----------------------------------

# 创建数据库
CREATE DATABASE day04_user;

# 使用新数据库
USE day04_user;

# 创建用户表
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(50) NOT NULL
);

# 初始化数据
INSERT INTO users
(username, password)
VALUES
('a', '1'),
('b', '2');
```

## 登录注册案例: 开发过程分析

* 本案例的开发顺序:
1. 先编写数据库操作
2. 然后写界面

## 登录注册案例: 创建包, 类

* DAO:
* Data Access Object, 数据访问对象, 专门用于操作数据库

* 5分钟练习:
* 创建新项目s3-day04-login
* 创建lib目录, 放入4个jar包
* 建包建类:
* com.itheima.login.dao 用于存放操作数据库的类
* UsersDao.java
* com.itheima.login.ui    用于存放界面相关的类
* UserOperator.java
* com.itheima.login.utils  用于存放工具类
* JDBCUtils: 复制今天编写的使用连接池的工具类
* 复制配置文件, 修改其中连接的库名

---=

## 登录注册案例: 登录功能实现

* 10分钟练习: 编写dao
* 在UserDao中创建QueryRunner成员变量, 使用JDBCUtils获取数据源来初始化
* 定义成员方法`public boolean login(String username, String password)`, 用于执行登录操作
* 在其中执行判断登录的sql语句: SELECT * FROM users WHERE username = ? AND password = ?;
* 传入参数, 使用queryRunner的查询方法, 传入ArrayHandler
* 通过返回的数组长度判断是否有值
* 有则返回true, 表示登录成功
* 无则返回false, 表示登录失败

```java
/*
操作User表的dao类
*/
public class UsersDao {

// 定义QueryRunner对象
private QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());

// 登录方法
public boolean login(String username, String password) {
try {
// 定义SQL语句
String sql = "SELECT * FROM users WHERE username=? AND password=?;";
// 定义参数
Object[] params = {username, password};
// 执行语句
Object[] result = queryRunner.query(sql, new ArrayHandler(), params);
// 判断是否有结果
if (result.length == 0)
// 没有查到用户信息, 不能登录
return false;
// 如果能查到用户信息, 则允许登录
return true;
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("登录dao操作失败");
}
}
}
```

## 登录注册案例: 注册功能实现

* 10分钟练习: 编写注册方法
* 继续在UserDao中定义一个方法: `public boolean register(String username, String password)`, 用于执行注册操作
* 在其中先执行判断用户是否存在的sql语句:
* SELECT username FROM users WHERE username = ?;
* 使用ScalarHandler获取String类型的username返回值
* 通过判断username返回值是否为null判断是否有用户
* 如果不为null, 返回false, 表示用户名已被占用
* 如果为null, 则可以注册
* 执行插入sql将用户信息录入数据库: INSERT INTO users (username, password) VALUES (?, ?);
* 直接返回true

```java
// 注册方法
public boolean register(String username, String password) {
try {
// 定义查询用户名是否被占用的SQL语句
String sql = "SELECT username FROM users WHERE username=?";
// 执行语句
String user = queryRunner.query(sql, new ScalarHandler<String>(), username);
// 判断是否能够查到用户名
if (user != null)
// 能查到相同的用户名, 说明已被占用, 不能注册
return false;
// 如果能执行到这里, 说明用户名没有被占用, 直接插入数据库
// 定义插入用户名和密码的sql
sql = "INSERT INTO users (username, password) VALUES (?, ?);";
// 定义参数
Object[] params = {username, password};
// 执行语句
queryRunner.update(sql, params);
// 直接返回注册成功的结果
return true;
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("注册dao操作失败");
}
}
```

---

## 登录注册案例: 登录功能测试

* 10分钟练习: 完成登录界面, 并测试
* 在UserOperator类中
* 定义静态成员变量Scanner sc
* 定义一个静态的String chooser()方法
* 在其中使用Scanner对象获取用户输入
* 使用syso打印提示:
* 欢迎光临, 请选择功能
* 1.登录  2.注册  3.退出
* 获取输入后返回输入的字符串
* 在main方法中
* 调用chooser()方法获取输入
* 通过switch判断输入的数字
* 1为登录
* syso提示当前为登录功能
* 提示请输入用户名, 获取输入
* 提示请输入密码, 获取输入
* 创建UserDao对象, 调用login方法执行登录, 根据方法返回值判断是否登录成功, syso提示
* 2为注册
* 3为退出
* 提示再见
* System.exit(0)
* 默认提示没有此功能

```java
/*
用户操作界面
*/
public class UserOperater {

// 定义静态变量Scanner
private static Scanner scanner = new Scanner(System.in);

public static void main(String[] args) {
// 调用输入选项的方法
String number = chooser();
// 根据输入选项调用不同的功能
switch (number) {
case "1":  // 登录
// 提示当前功能
System.out.println("当前是[登录]功能, 请输入用户名");
String username = scanner.next();
System.out.println("请输入密码:");
String password = scanner.next();
// 创建UsersDao对象, 调用登录方法
UsersDao dao = new UsersDao();
boolean canLogin = dao.login(username, password);
// 判断结果, 给出提示
if (canLogin) {
System.out.println("登录成功");
} else {
System.out.println("登录失败");
}
break;
case "2":  // 注册

break;
case "3":  // 退出
System.out.println("再见");
System.exit(0);
break;
default:
// 提示选项错误
System.out.println("抱歉, 没有该选项");
break;
}
}

// 获取键盘输入选项
private static String chooser() {
// 打印菜单
System.out.println("---===欢迎光临===---");
System.out.println("请输入您的选项:");
System.out.println("1.登录 2.注册 3.退出");
// 获取键盘录入的选项, 并返回
return scanner.next();
}
}
```

## 登录注册案例: 注册功能测试

* 10分钟练习: 完成注册界面, 测试
* 将UserDao对象提取为静态成员变量
* 在main方法中
* 继续编写注册功能的调用
* 2为注册
* syso提示当前为登录功能
* 提示请输入用户名, 获取输入
* 提示请输入密码, 获取输入
* 使用UserDao对象, 调用signup方法执行注册, 根据方法返回值判断是否注册成功
* 如果注册成功, 提示注册成功
* 如果注册失败, 提示用户名已被占用, 请更换用户名
* 将获取用户选项和switch判断的代码都放入while(true)循环中
* 测试程序

```java
/*
用户操作界面
*/
public class UserOperater {

// 定义静态变量Scanner
private static Scanner scanner = new Scanner(System.in);

public static void main(String[] args) {
// 创建dao对象
UsersDao dao = new UsersDao();

while (true) {
// 调用输入选项的方法
String number = chooser();
// 根据输入选项调用不同的功能
switch (number) {
case "1":  // 登录
// 提示当前功能
System.out.println("当前是[登录]功能, 请输入用户名");
String username = scanner.next();
System.out.println("请输入密码:");
String password = scanner.next();
//调用登录方法
boolean canLogin = dao.login(username, password);
// 判断结果, 给出提示
if (canLogin) {
System.out.println("登录成功");
} else {
System.out.println("登录失败");
}
break;
case "2":  // 注册
// 提示功能
System.out.println("当前是注册功能, 请输入用户名:");
username = scanner.next();
System.out.println("请输入密码:");
password = scanner.next();
// 调用dao的注册方法
boolean success = dao.register(username, password);
// 根据结果给出提示
if (success) {
System.out.println("注册成功");
} else {
System.out.println("当前用户名已被占用, 请尝试" + username + "_123");
}
break;
case "3":  // 退出
System.out.println("再见");
System.exit(0);
break;
default:
// 提示选项错误
System.out.println("抱歉, 没有该选项");
break;
}
}
}

// 获取键盘输入选项
private static String chooser() {
// 打印菜单
System.out.println("---===欢迎光临===---");
System.out.println("请输入您的选项:");
System.out.println("1.登录 2.注册 3.退出");
// 获取键盘录入的选项, 并返回
return scanner.next();
}
}
```

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

## 今日总结

* DBCP连接池
* Apache commons项目中的一个组件
* 作用: 提供连接池, 替代连接. 连接池可以存放多个连接, 反复使用, 而不用每次创建销毁, 提高效率, 节省资源
* 普通连接的弊端: 获取连接, 释放资源太消耗资源.
* Sun公司提供了接口: `javax.sql.DataSource`接口: 连接池规范
* 连接池的实现:
* DBCP
* C3P0
* Druid
* DBCP
* 导包
* `commons-dbcp-x.x.jar`
* `commons-pool-x.x.x.jar`
* `BasicDataSource`: 是DataSource接口的实现类
* 必选
* `BasicDataSource BasicDataSource()`: 创建对象
* `void setDriverClassName(String name)`: 设置驱动类名
* `void setUrl(String url)`: 设置连接地址
* `void setUsername(String username)`: 设置用户名
* `void setPassword(String password)`: 设置密码
* `Connection getConnection()`: 获取连接对象
* 可选
* `void setMaxActive(int n)`: 最大连接数量. 程序能够连接数据库的最大连接数量
* `void setMinIdle(int n)`: 最小空闲连接. 连接池中允许存在的最小空闲数量, 如果小于该数量, 则会创建新的连接, 直到满足该最小空闲连接数
* `void setMaxIdle(int n)`: 最大空闲连接. 连接池中允许存在的最大空闲数量, 如果超出该数量, 则超出的空闲连接会被真实关闭
* `void setInitialSize(int size)`: 初始化连接数. 连接池中最初的连接数
* 改写JDBCUtils
* 将注册驱动, 获取连接的代码修改为必选4项
* 将getConnection()方法替换为getDataSource()方法
* 用户登录注册案例
* 理解如何分析需求, 将其转换为不同的步骤
* 注册:
* 先检查用户名是否存在(SELECT)
* 如果存在: 注册失败, 提示更换用户名
* 如果不存在: 直接注册(INSERT INTO)
* 登录
* 查询用户表中是否有匹配的用户名和密码(SELECT)
* 如果存在: 用户已经注册且密码匹配, 登录成功
* 如果用户名不存在: 用户没有注册或, 登录失败
* 如果用户名存在, 密码不匹配: 登录失败
* dao: 操作数据库的类
* 类名: `XxxDao.java`

# day05 管家婆项目A

## 项目训练目标介绍

* 训练目标
* 熟练使用三层设计思想和相互调用的实现
* View层: 视图层, 可视界面
* Service层: 业务层, 各种功能
* Dao层: 持久层, 操作数据库
* 熟练使用DBUtils操作数据库进行增删改查
* 了解公司项目开发流程, 掌握需求分析, 设计, 功能代码实现
* 扩展: 分层思想
* 软件架构分层模型(即Java代码这部分可以再按照如下分层)
* MVC架构: Model, View, Controller

## 项目中的功能模块

```
+-------------------------------+
|       管家婆家庭记账软件    |
+---------------+---------------+
            |
  +------+------+------+------+
  |    |    |    |    |
+-+-+  +-+-+  +-+-+  +-+-+  +-+-+
|查 |  |多 |  |添 | |编 |  |删 |
|询 |  |条 |  |加 | |辑 |  |除 |
|账 |  |件 |  |账 | |账 |  |账 |
|务 |  |查 |  |务 | |务 |  |务 |
| |  |询 |  | |  | |  | |
+---+  +---+  +---+  +---+  +---+
```

## 技术的选择和jar包

* 管家婆项目所用jar包
* DbUtils工具类
* `commons-dbutils-1.4.jar`: JDBC工具类
* DBCP连接池
* `commons-dbcp-1.4.jar`: DBCP连接池
* `commons-pool-1.3.jar`: DBCP连接池依赖
* MySQL驱动
* `mysql-connnector-java-5.1.28-bin.jar`: MySQL数据库驱动
* 技术选型:
* 当需要实现一种功能时, 有很多技术可以实现, 我们要考虑各种因素(开发成本, 开发速度, 可维护性, 该技术的适用场景等), 或是做一些demo, 最终确定一种技术作为实现手段. 这一过程就是技术选型
* 比如用Java操作数据库, 有多种方式可以实现, 根据各种因素来选择:
* JDBC原生方式
* 开发成本: Java原生支持, 不必依赖于第三方, 免费, 且已经会用, 无成本
* 开发速度: 代码繁琐, 冗余性高, 开发速度慢
* 可维护性: 代码繁琐, 不易于维护
* 适用场景: 简单的需求
* 自己封装工具类方式
* 开发成本: Java原生支持, 不必依赖于第三方, 免费, 且已经会用, 无成本
* 开发速度: 减少了原生方式的冗余代码, 但执行SQL语句的部分和查询结果的处理封装仍然繁琐
* 可维护性: 代码较为繁琐, 不易于维护
* 适用场景: 简单的需求
* 使用第三方类库, 如DbUtils, DBCP等
* 开发成本: 第三方提供的开源jar包, 可随时下载使用, 免费, 且已经会用, 成本较小
* 开发速度: 数据库连接释放, 查询结果封装都已经简化, 开发速度快
* 可维护性: 代码简洁, 易于维护
* 适用场景: 简单的需求
* 使用第三方框架, 如Hibernate, MyBatis等
* 开发成本: 目前不会用, 需要时间学习, 且不知道会遇到什么问题, 学习成本较高
* 开发速度: 先要学习才能开发, 肯定很慢
* 可维护性: 未知
* 适用场景: 未知
* 根据老板提出的需求, 管家婆项目"明天中午就要上线", 所以最终我们技术选型的结果为: DbUtils + DBCP连接池
* 第三方开源项目
* [Github](www.github.com)
* 开源是Open Source, 开放源代码的意思, 表示源代码是公开的, 谁都可以查看其实现原理, 并且根据自己的需要去修改完善, 但一般都有License(使用许可)来约束使用者的行为(是否能用于商业目的, 用于商业目的的要求等)
* 实际开发中常有的3种环境
* 开发环境: 开发人员用于开发项目的环境
* 一般在开发人员的本机电脑上
* 可能会有一些假数据用于开发自测
* 开发完成的功能会先在开发环境中, 由开发人员自测(一般是单元测试, 接口测试, 集成测试), 自测通过后, 发布到测试环境, 由测试人员进行测试
* 测试环境: 测试人员用于测试项目功能的环境. 与生产环境一致的一套单独的环境. 测试人员会以真实使用方式来测试项目是否有bug. 测试通过的功能反馈给开发人员, 开发人员才能发布到生产环境(俗称: 上线)
* 生产环境: 真实用户使用的实际运行环境
* 一般不会停止运行
* 一般在凌晨上线, 因为新项目部署到服务器后需要重启服务器, 中断对客户的服务, 凌晨时用户量少, 不影响业务
* 测试的分类
* 按测试规模分
* 单元测试: 测试方法
* 接口测试: 测试API接口
* 集成测试: 将多个功能组合起来, 看看组合后是否有问题
* 从测试方式分
* 黑盒测试: 也叫功能测试, 把程序看成一个打不开的盒子, 里面黑的看不见. 在不了解源代码逻辑的情况下, 通过用户界面测试功能是否有异常(一般是测试人员)
* 白盒测试: 检查源代码的实现逻辑, 测试代码是否会导致功能有异常(一般是开发人员)
* 产品上线后, 新功能小部分试用查看反馈
* 灰度测试: 70%用户使用1.0.0版本, 30%用户使用1.1.0版本
* 常用术语:
* 联调: 系统各部分开发人员(前端, 后端)一起调试功能, 查看功能是否可用, 是否有bug

## 项目中的工具类

* JDBCUtils
* 加载配置文件
* 使用连接池

## 数据表的设计

* 表设计
* 设计有多少张表
* 表有哪些字段
* 表与表之间有什么关系
* 主键和外键
* 主键PRIMARY KEY, 一般使用不可重复的列, 如id
* 外键FOREIGN KEY, 用于关联另外一个表的主键
* 主键所在的表称为`主表`
* 外键所在的表称为`从表`
* 主表和从表的数据要求
* 主表有的数据, 从表可以有, 可以没有
* 主表没有的数据, 从表肯定没有
* 级联操作
* 主表中的记录更新或删除, 从表中对应的也应该更新或删除
* 表与表的3种关系
* 一对一: 一个学生只能坐一个座位, 一个座位也只能坐一个学生
* 一对多: 一个学生只能属于一个班, 但一个班可以有多个学生
* 多对多: 一个校区可以有多个老师, 一个老师也可以在多个校区教学

---

## 创建数据库, 表, 测试数据

* 库名: gjp: 管家婆
* 表名: gjp_zhangwu: 管家婆账务
* zwid: 账务id, 主键, 自增
* flname: 分类名称, 字符串
* money: 金额, 小数
* zhanghu: 账户消费方式, 字符串
* createtime: 发生日期, 日期
* description: 账务描述, 字符串
* 注意保留SQL文件

```sql
# day05 管家婆项目

-- 创建管家婆项目测试数据----------------------------------

# 创建数据库
CREATE DATABASE gjp;

# 使用新数据库
USE gjp;

# 创建表
CREATE TABLE gjp_zhangwu (
  zwid INT PRIMARY KEY AUTO_INCREMENT, # 账务id, 主键
  flname VARCHAR(200),                   # 分类名称
  money DOUBLE,                         # 金额
  zhanghu VARCHAR(100),                # 消费方式
  createtime DATE,                      # 消费日期
  description VARCHAR(1000)             # 费用描述
);

# 初始化数据(这就是开发用的假数据)
INSERT INTO gjp_zhangwu
(zwid, flname, money, zhanghu, createtime, description)
VALUES
(1,'吃饭支出',247,'交通银行','2016-03-02','家庭聚餐'),
(2,'工资收入',12345,'现金','2016-03-15','开工资了'),
(3,'服装支出',1998,'现金','2016-04-02','买衣服'),
(4,'吃饭支出',325,'现金','2016-06-18','朋友聚餐'),
(5,'股票收入',8000,'工商银行','2016-10-28','股票大涨'),
(6,'股票收入',5000,'工商银行','2016-10-28','股票又大涨'),
(7,'工资收入',5000,'交通银行','2016-10-28','又开工资了'),
(8,'礼金支出',5000,'现金','2016-10-28','朋友结婚'),
(9,'其他支出',1560,'现金','2016-10-29','丢钱了'),
(10,'交通支出',2300,'交通银行','2016-10-29','油价还在涨啊'),
(11,'吃饭支出',1000,'工商银行','2016-10-29','又吃饭'),
(12,'工资收入',1000,'现金','2016-10-30','开资'),
(13,'交通支出',2000,'现金','2016-10-30','机票好贵'),
(14,'工资收入',5000,'现金','2016-10-30','又开资');

# 查看数据
SELECT * FROM gjp_zhangwu;
```

## 项目中的分层设计

* 为什么要分层
* 解耦
* 便于维护
* 用包来实现分层

```
分层交互流程图

o------o  Input  +------+  data  +------------+ call +---------+ call +-----+  SQL +----+
|    | ------> |    | -----> |          | -------> |       | -------> |    | -----> | |
| User |       | View |       | Controller |       | Service |       | Dao |       | DB |
|    | <------ |    | <----- |          | <------- |       | <------- |    | <----- | |
o------o show  +------+  data  +------------+ data +---------+ data +-----+  data  +----+
```

* 项目包名设计
* `view`: 视图层, 存放界面相关代码
* `controller`: 控制层, 用于获取界面层的数据, 或向界面设置数据. 功能实现要交给业务层
* `service`: 业务层, 实现功能, 与controller控制层和数据访问层dao交互
* `dao`: 数据访问层, 用于操作数据库表的数据
* `db`: 数据库
* `domain`: 也有用entity, bean命名的, 存放所有的JavaBean
* `tools`: (一般包名用util)工具包, 存放各种工具类
* `test`: (一般不把测试代码放在包中, 而是项目中与src平级的test目录中)测试包, 用于存放单元测试代码
* 注意: 不能跨层调用, 否则分层就没有意义了

## 创建项目: 分层, 导入jar包

* 项目名称: gjp

```
项目包结构图

gjp                            # 项目名称
|_ src
| |_ com.itheima.gjp    # 包名
|       |_ app          # 程序入口
|       |_ controller    # 控制层
|       |_ dao          # 数据访问层
|       |_ domain       # 实体类
|       |_ service       # 业务层
|       |_ tools          # 工具类
|       |_ view          # 视图层
|_ lib
      |_ commons-dbcp-1.4.jar
      |_ commons-dbutils-1.6.jar
      |_ commons-pool-1.5.6.jar
      |_ mysql-connector-java-5.1.28-bin.jar
```

* 10分钟练习:
* 创建项目gjp
* 创建包结构, 导入4个jar包

---

## 创建domain中的JavaBean

* JavaBean写什么类名? 写什么属性?
* 类名和表名一致
* 属性名和列名一致

* 10分钟:
* 在`domain`包下创建`Zhangwu`实体类
* 根据gjp_zhangwu表结构定义Java类
* 提供一个无参构造和一个全参构造
* 重写toString方法

```java
public class Zhangwu {

private int zwid; // 账务id, 主键
private String flname; // 分类名称
private double money; // 金额
private String zhanghu; // 消费方式
private String createtime; // 消费日期
private String description; //费用描述

public Zhangwu() {
super();
}

public Zhangwu(int zwid, String flname, double money, String zhanghu, String createtime, String description) {
super();
this.zwid = zwid;
this.flname = flname;
this.money = money;
this.zhanghu = zhanghu;
this.createtime = createtime;
this.description = description;
}

public int getZwid() {
return zwid;
}

public void setZwid(int zwid) {
this.zwid = zwid;
}

public String getFlname() {
return flname;
}

public void setFlname(String flname) {
this.flname = flname;
}

public double getMoney() {
return money;
}

public void setMoney(double money) {
this.money = money;
}

public String getZhanghu() {
return zhanghu;
}

public void setZhanghu(String zhanghu) {
this.zhanghu = zhanghu;
}

public String getCreatetime() {
return createtime;
}

public void setCreatetime(String createtime) {
this.createtime = createtime;
}

public String getDescription() {
return description;
}

public void setDescription(String description) {
this.description = description;
}

@Override
public String toString() {
return "Zhangwu [zwid=" + zwid + ", flname=" + flname + ", money=" + money + ", zhanghu=" + zhanghu
+ ", createtime=" + createtime + ", description=" + description + "]";
}
}
```

## 创建JDBCUtils工具类

* 5分钟: 在`tools`包下创建工具类`JDBCUtils`

```java

```

# 创建其他包中的类

* 10分钟:
* 在`app`包中创建`MainApp.java`类, 并定义`main()`方法: 用于程序入口
* 在`dao`包下创建`ZhangwuDao.java`类: 用于实现对账务表的增删改查
* 创建一个`QueryRunner`类的成员变量(非静态)
* 在`service`包下创建`ZhangwuService.java`类: 用于接收controller层的计算, 并传递给dao层
* 创建一个`ZhangwuDao`类的成员变量(非静态)
* 在`controller`包下创建`ZhangwuController.java`类: 用于接收视图层数据, 传递给service
* 创建一个`ZhangwuService`类的成员变量(非静态)
* 在`view`包下创建`MainView.java`类: 用于显示界面
* 创建一个`ZhangwuController`类的成员变量(非静态)

* MainApp.java

```java
/*
程序运行入口
*/
public class MainApp {

public static void main(String[] args) {
// 启动界面

}
}
```

* ZhangwuDao.java

```java
/*
操作Zhangwu表的dao
*/
public class ZhangwuDao {

// 定义queryrunner
private QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
}
```

* ZhangwuService.java

```java
/*
业务层
调用dao操作数据
向controller返回数据
*/
public class ZhangwuService {

// 定义dao对象
private ZhangwuDao dao = new ZhangwuDao();
}
```

* ZhangwuController.java

```java
/*
控制层
接收view传递的数据, 向service传递数据
向view返回结果
*/
public class ZhangwuController {

// 定义Service对象
private ZhangwuService service = new ZhangwuService();
}
```

* MainView.java

```java
/*
视图层
获取用户输入和显示信息
*/
public class MainView {

// 定义Controller对象
private ZhangwuController controller = new ZhangwuController();
}
```

---=

## 实现用户界面菜单

* 界面在View层写
* 在`MainView`类中定义`run()`方法, 该方法需要
* 显示功能菜单界面
* 接收键盘输入的功能选项
* 根据选项值, 调用对应的功能方法
* 在`MainApp`类的`main()`方法中, 调用`run()`方法, 让程序执行, 显示功能界面菜单

* 10分钟:
* 在`MainView`类中定义`run()`方法
* 创建Scanner对象
* 在while死循环中编写界面
* 首先输出标题
* "----------------管家婆家庭记账软件----------------"
* "1.添加账务  2.删除账务  3.编辑账务  4.查询账务  5.退出系统"
* "请输入要操作的功能序号[1-5]:"
* 使用scanner对象获取用户输入
* 使用switch根据用户输入分别写case
* 实现退出系统, 提示再见, 并`System.exit(0)`
* default提示"输入错误"
* 在`MainApp.java`的main方法中, 创建MainView对象, 并调用run方法测试

* MainView.java

```java
/*
视图层
获取用户输入和显示信息
*/
public class MainView {

// 定义Controller对象
private ZhangwuController controller = new ZhangwuController();

// 启动界面显示
public void run() {
// 创建键盘录入
Scanner scanner = new Scanner(System.in);
// 循环菜单
while (true) {
// 打印菜单
System.out.println("----------------管家婆家庭记账软件----------------");
System.out.println("1.添加账务  2.删除账务  3.编辑账务  4.查询账务  5.退出系统");
System.out.println("请输入要操作的功能序号[1-5]:");
// 获取输入选项
int choice = scanner.nextInt();
// 根据选项判断调用方法
switch (choice) {
case 1:  // 添加账务

break;
case 2:  // 删除账务

break;
case 3:  // 编辑账务

break;
case 4:  // 查询账务

break;
case 5:  // 退出
System.out.println("再见");
System.exit(0);
break;
default:
System.out.println("您输入的选项有误");
break;
}
}
}
}
```

* MainApp.java

```java
/*
程序运行入口
*/
public class MainApp {

public static void main(String[] args) {
// 启动界面
new MainView().run();
}
}
```

## 实现查询的界面菜单

* 5分钟
* 在`MainView`类中:
* 定义`selectZhangwu()`方法
* 打印要查询账务的方式: "1.查询所有  2.条件查询"
* 创建Scanner对象, 接收键盘输入, 调用对应方法
1. 查询所有
2. 条件查询
* default提示输入有误
* 定义`selectAll()`方法, 表示查询所有
* 定义`select()`方法, 表示条件查询

* MainView.java

```java
// 查询所有账务
private void selectZhangwu() {
// 显示子菜单
Scanner scanner = new Scanner(System.in);
System.out.println("1.查询所有  2.条件查询");
int choice = scanner.nextInt();
// 判断选项
switch (choice) {
case 1:  // 查询所有
selectAll();
break;
case 2:  // 条件查询
select();
break;
default:
System.out.println("输入有误");
break;
}
}

// 查询所有
private void selectAll() {
// 调用controller对象的方法查询所有
}

// 条件查询
private void select() {

}
```

---

## 查询所有账务: controller, service

* 10分钟
* 在`ZhangwuController`类中定义方法`void selectAll()`
* 并在其中调用service对象的`selectAll()`方法
* 在`ZhangwuService`类中定义方法`List<Zhangwu> selectAll()`
* 并在其中调用dao对象的`selectAll()`方法
* 在`ZhangwuDao`类中定义方法`List<Zhangwu> selectAll()`

* MainView.java

```java
// 查询所有
private void selectAll() {
// 调用controller对象的方法查询所有
List<Zhangwu> list = controller.selectAll();
}
```

* ZhangwuController.java

```java
// 查询所有账务
public List<Zhangwu> selectAll() {
return service.selectAll();
}
```

* ZhangwuService.java

```java
// 查询所有账务
public List<Zhangwu> selectAll() {
return dao.selectAll();
}
```

* ZhangwuDao.java

```java
// 查询所有账务
public List<Zhangwu> selectAll() {
return null;
}
```

## 查询所有账务: dao

* 10分钟:
* 编写ZhangwuDao的selectAll方法
* 查询所有: "SELECT * FROM gjp_zhangwu;"
* 使用queryRunner结合BeanListHandler查询出List集合, 直接返回该集合
* 捕获异常, 抛出运行时异常

```java
// 查询所有账务
public List<Zhangwu> selectAll() {
try {
// 定义SQL语句
String sql = "SELECT * FROM zhangwu;";
// 执行语句
List<Zhangwu> list = queryRunner.query(sql, new BeanListHandler<>(Zhangwu.class));
// 返回查询的结果
return list;
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("查询所有失败");
}
}
```

## 查询所有账务: view

* 5分钟
* 在MainView类中定义`void selectAll()`方法
* 调用ZhangwuController对象的selectAll方法获取List结果
* 打印表头: "ID\t类别\t账户\t金额\t时间\t\t说明"
* 遍历集合并打印
* 注意对于集合对象是否为null的判断

```java
// 查询所有
private void selectAll() {
// 调用controller对象的方法查询所有
List<Zhangwu> list = controller.selectAll();
// 打印表头
System.out.println("ID\t类别\t账户\t金额\t时间\t\t说明");
// 遍历
for (Zhangwu zhangwu : list) {
System.out.println(
zhangwu.getZwid() + "\t"
+ zhangwu.getFlname() + "\t"
+ zhangwu.getMoney() + "\t"
+ zhangwu.getZhanghu() + "\t"
+ zhangwu.getCreatetime() + "\t"
+ zhangwu.getDescription()
);
}
}
```

---

## 条件查询账务实现: View层

* 条件查询主要让用户输入时间范围, 然后按时间查询出过滤后的结果

* 5分钟
* 在`MainView`中定义`void select()`方法
* 提示: "> 条件查询: 输入格式为: XXXX-XX-XX"
* 提示: "请输入开始日期:"
* 创建Scanner对象, 接收用户开始日期
* 提示: "请输入结束日期"
* 使用Scanner对象获取结束日期
* (下一步准备调用controller的方法)

```java
// 条件查询
private void select() {
// 创建键盘录入
Scanner scanner = new Scanner(System.in);
System.out.println("当前是条件查询, 请输入日期, 格式为YYYY-MM-DD");
System.out.print("请输入开始日期:");
String startDate = scanner.next();
System.out.print("请输入结束日期:");
String endDate = scanner.next();
// 调用controller的方法, 传递开始和结束日期

}
```

## 条件查询账务实现: controller, service

* 10分钟
* 在`ZhangwuController`中定义`List<Zhangwu> select(String beginDate, String endDate)`方法
* 内部调用service对象的同名方法
* 在`ZhangwuService`中定义`List<Zhangwu> select(String beginDate, String endDate)`方法
* 内部调用dao对象的同名方法
* 在`ZhangwuDao`中定义`List<Zhangwu> select(String beginDate, String endDate)`方法
* 等待实现

* 10分钟:

* ZhangwuController.java

```java
// 条件查询账务
public List<Zhangwu> select(String startDate, String endDate) {
return service.select(startDate, endDate);
}
```

* ZhangwuService.java

```java
// 条件查询账务
public List<Zhangwu> select(String startDate, String endDate) {
return dao.select(startDate, endDate);
}
```

* ZhangwuDao.java

```java
// 条件查询账务
public List<Zhangwu> select(String startDate, String endDate) {
return null;
}
```

## 条件查询账务实现: dao

* 10分钟
* 编写ZhangwuDao下的`select(String beginDate, String endDate)`方法
* 按条件查询: "SELECT * FROM gjp_zhangwu WHERE createtime BETWEEN ? AND ?;"
* 调用queryRunner对象结合BeanListHandler执行查询, 返回List集合
* 捕获异常, 抛出运行时异常

```java
// 条件查询账务
public List<Zhangwu> select(String startDate, String endDate) {
try {
// 定义SQL
String sql = "SELECT * FROM gjp_zhangwu WHERE createtime BETWEEN ? AND ?;";
// 定义参数
Object[] params = {startDate, endDate};
// 执行语句
return queryRunner.query(sql, new BeanListHandler<>(Zhangwu.class), params);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("条件查询失败");
}
}
```

## 条件查询账务实现: view

* 抽取打印列表方法, 简化代码

* 10分钟:
* 在`MainView`中定义`void print(List<Zhangwu> list)`方法
* 先判断list有无数据, 如果没有则提示: 暂无数据, 并结束方法
* 如果有数据, 则打印表头: "ID\t类别\t账户\t金额\t时间\t\t说明"
* 遍历集合并打印记录
* 仍然在MainView中, 补充`select()`方法中的调用controller的代码
* 调用controller对象的`select(beginDate, endDate)`方法得到List
* 调用刚才定义的print方法

* MainView.java

```java
// 查询所有
private void selectAll() {
// 调用controller对象的方法查询所有
List<Zhangwu> list = controller.selectAll();
// 打印
print(list);
}

// 条件查询
private void select() {
// 创建键盘录入
Scanner scanner = new Scanner(System.in);
System.out.println("当前是条件查询, 请输入日期, 格式为YYYY-MM-DD");
System.out.print("请输入开始日期:");
String startDate = scanner.next();
System.out.print("请输入结束日期:");
String endDate = scanner.next();
// 调用controller的方法, 传递开始和结束日期
List<Zhangwu> list = controller.select(startDate, endDate);
// 打印
print(list);
}

// 打印数据
private void print(List<Zhangwu> list) {
if (list.size() == 0) {
// 提示没有记录
System.out.println("没有账务信息");
} else {
// 打印表头
System.out.println("ID\t类别\t账户\t金额\t时间\t\t说明");
// 遍历
for (Zhangwu zhangwu : list) {
System.out.println(
zhangwu.getZwid() + "\t"
+ zhangwu.getFlname() + "\t"
+ zhangwu.getMoney() + "\t"
+ zhangwu.getZhanghu() + "\t"
+ zhangwu.getCreatetime() + "\t"
+ zhangwu.getDescription()
);
}
}
}
```

}
```

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2