"数据库什么是数据库概念: 按照数据结构来组织、存储和管理数据的仓库作用: 存储数据, 管理数据数据库分类关系型数据库 (SQL) MySQL,Oracle,Sql Server,DB2,SQLlite 通过表和表之间, 行和列之间的关系进行数据的存储非关系型数据库 (NoSQL) Red ...."

someone1
AI专业者开发社区 1 号成员
AI开发者社区 • 0 回帖 • 0 浏览 • 4 个月前

数据库

什么是数据库

概念: 按照数据结构来组织、存储和管理数据的仓库

作用: 存储数据, 管理数据

数据库分类

关系型数据库 (SQL)

MySQL,Oracle,Sql Server,DB2,SQLlite
通过表和表之间, 行和列之间的关系进行数据的存储

非关系型数据库 (NoSQL)

Redis,MongDB
对象存储, 通过自身的属性来决定

DBMS(数据库管理系统)

数据库的管理软件, 科学有效地管理我们的数据, 维护和获取数据

MySQL

MySQL 是一个关系型数据库管理型系统

SQL 语句

mysql -u root -p123456 -- 命令行连接数据库 update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; -- 修改用户密码 flush privileges; -- 刷新权限 show databases; -- 查看所有的数据库 use 数据库名 -- 切换数据库 show tables; -- 查看数据库中所有的表 describe student; -- 显示数据库中所有的表的信息 create database westos -- 创建一个数据库

exit; -- 退出连接

操作数据库

1. 创建数据库

CREATE DATABASE [IF NOT EXISTS] westos;

2. 删除数据库

DROP DATABASE [IF EXISTS] westos;

3. 使用数据库

USE `school`

4. 查看数据库

SHOW DATABASES

数据库的列类型

数值

tinyint 十分小的数据 1 个字节
smallint 较小的数据 2 个字节
mediumint 中等大小的数据 4 个字节
int 标准的整数 4 个字节 常用的 int
bigint 较大的数据 8 个字节
float 浮点数 4 个字节
double 浮点数 8 个字节
decimal 字符串形式的浮点数金融计算的时候, 一般使用 decimal

字符串

char 字符串固定大小的 0-255
varchar 可变字符串 0-65535 常用的 String
tinytext 微型文本 2^8-1
text 文本串 2^16-1 保存大文本

时间日期

java.util.Date

data YYYY-MM-DD 日期格式
time HH🇲🇲ss 时间格式
datetime YYYY-MM-DD HH🇲🇲ss 最常用的时间格式
timestamp 时间戳:1970.1.1 到现在毫秒数
year 年份表示

null

没有值, 未知
注意 , 不要使用 NULL 进行运算, 结果为 NULL

数据库的字段属性 (重点)

Unsigned:

无符号的整数
声明了该列不能声明为负数

zerofull:

0 填充的
不足的位数, 使用 0 来填充

自增:

通常理解为自增, 自动在上一条记录的基础上 +1(默认)
通常用来设计唯一的主键 index, 必须是整数类型
可以自定义设计主键自增的起始值和步长

非空:

假设设置为 not null, 如果不给他赋值, 就会报错
NULL, 如果不填写值, 默认就是 null

默认:

设置默认的值
例:sex, 默认值为男, 如果不指定该列的值, 则会有默认的值

创建数据库表 (重点)

格式:

CREATE TABLE [IF NOT EXISTS] `表名`(
	'字段名' 列类型 [属性] [索引] [注释],
	'字段名' 列类型 [属性] [索引] [注释],
	......
	'字段名' 列类型 [属性] [索引] [注释]
)

数据表的类型

关于数据库的引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大, 约为两倍

常规使用操作:

MYISAM: 节约空间, 速度较快
INNODB: 安全性高, 事务的处理, 多表多用户操作

/*
在物理空间存在的位置
*/

所有的数据库文件都存在 data 目录下, 本质上还是文件的存储

/*
MySQL 引擎在物理文件上的区别
*/

INNODB 在数据库中只有一个 *.frm, 以及上级目录下的 ibdata1 文件
MYISAM 对应文件:
- *.frm 表结构的定义文件
- *.MYD 数据文件 (data)
- *.MYI 索引文件 (index)

修改和删除数据表字段

修改表

ALTER TABLE 旧表名 RENAME AS 新表名

增加表的字段

ALTER TABLE 表名 ADD　字段名　列属性

修改表的字段

ALTER TABLE 表名 MODIFY 字段名 列属性[] -- 修改约束
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[] -- 字段重命名

删除表的字段

ALTER TABLE 表名 DROP 字段名

MySQL 数据管理

外键 (了解即可)

ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIRN KEY(作为外键的列) REFERENCEES 表名(字段名) -- 为创建好的表添加外键

以上的外键是物理外键, 数据库级别的外键, 不建议使用 (避免数据库过多造成困扰)

最佳实践: 利用程序去实现

DML 语言 (全部记住)

数据库的意义: 数据存储, 数据管理

DML 语言: 数据操作语言

添加 (INSERT)

insert into 表名([字段名1,字段名2,字段名3...]) values ('值 1','值 2','值 3'...)

注意事项:

字段和字段之间用英文逗号隔开
字段是可以省略的, 但是后面的值必须要一一对应, 不能少
可以同时插入多条数据,values 后面的值, 需要使用逗号隔开

修改 (UPDATE)

update 表名 set colnum_name = value,[colnum_name = value...] where [条件]

条件:where 子句运算符 id 等于某个值, 大于某个值, 在某个区间内修改

操作符会返回布尔值

操作符	含义	范围	结果
=	等于	5=6	false
<>	不等于	5<>6	true
>	大于	5>4	true
<	小于	5<6	false
<=	小等于	5<=3	false
>=	大等于	5>=3	true
BETWEEN...AND...	在某个范围内	[2,5]
AND	相当于 &&	5>1 and 1>2	false
OR	相当于 \|\|	5>1 or 1>2	true

注意事项:

colnum_name 是数据库的列, 最好带上 ``
筛选的条件, 如果没有指定, 则会修改所有的列
value, 是一个具体的值, 也可以是一个变量

删除 (DELETE)

delete from 表名 [where 条件]

TRUNCATE

truncate `表名`

作用: 完全清空一个数据库表, 表的结构和索引约束不会变

和 DELETE 的相同点: 都能删除数据, 都不会删除表结构

不同点:

TRUNCATE 重新设置自增列计数器会归零
TRUNCATE 不会影响事务

DQL 查询数据

DQL(Data Query Language) -- 数据查询语言

所有的查询操作都用它 select
简单的查询, 复杂的查询它都能做
数据库中最核心的语言, 最重要的语句
使用频率最高的语句

SELECT [ALL | DISTINCT]
{* | table.* | [table.filed1[as alist1][,table.field2][as alist2]][,...]]}
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 表
查询指定字段
SELECT StudentNo,StudentName FROM student
AS 可以给字段起别名, 也可以给表起别名
SELECT StudentNo AS 学号,StudentName AS 学生姓名 FROM student AS s
函数 Concat(a,b)
SELECT CONCAT(姓名:',StudentName) AS 新名字 FROM student

去重 (DISTINCT)

SELECT * FROM result -- 查询全部的考试成绩
SELECT `StudentNo` FROM result -- 查询有哪些同学参加了考试
SELECT DISTINCT `StudentNo` FROM result -- 发现重复数据, 去重

WHERE 条件子句

作用: 检索数据中符合条件的值

逻辑运算符:

运算符	语法	描述
and &&	a and b a&&b	逻辑和
or \|\|	a or b a\|\|b	逻辑或
not !	not a !a	逻辑非

模糊查询: 比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作为 null, 结果为真
IS NOT NULL	a is not null	如果操作符不为 null, 结果为真
BETWEEN	a between b and c	若 a 在 b 和 c 之间, 则结果为真
Like	a like b	SQL 匹配, 如果 a 匹配 b, 则结果为真
In	a in (a1,a2,a3...)	假设 a 在 a1, 或者 a 在 a2... 其中的某一个值中, 结果为真

联表查询

操作	描述	含义
Inner join	如果表中至少有一个匹配,, 就返回行	内连接 INNER JOIN/JOIN 是最常用的连接操作。从数学的角度讲就是求两个表的交集
left join	会从左表返回所有的值, 即使右表中没有匹配	左连接 LEFT JOIN 的含义就是求两个表的交集外加左表剩下的数据，左连接从左表 (A) 产生一套完整的记录, 与匹配的记录(右表(B)) . 如果没有匹配, 右侧将包含 null
right join	会从右表返回所有的值, 即使左表中没有匹配	右连接 RIGHT JOIN 就是求两个表的交集外加右表剩下的数据

select * from blog inner join blog_type on blog.title_type=blog_type.id;
select * from blog join blog_type on blog.title_type=blog_type.id;
select * from blog,blog_type where blog.title_type=blog_type.id;

select * from blog left join blog_type on blog.title_type=blog_type.id;

select * from blog left join blog_type on blog.title_type=blog_type.id where blog_type.id is null;

分页和排序

排序:

在 MySql 中，使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。
尤其非常特别重要：默认按升序（ASC）排列。
order by 后可加 2 个（或多个）字段，字段之间用英文逗号隔开。
若 A 用升序，B 用降序，SQL 该这样写：order by A ASC, B DESC; 默认同理，也可以这样写：order by A, B DESC;
若 A、B 都用降序，必须用两个 DESC，order by A DESC, B DESC;
多个字段时，优先级按先后顺序而定。

分页:

SELECT * FROM table LIMIT 5,10; -- 检索记录行 6-15
SELECT * FROM table LIMIT 95,-1; -- 检索记录行 96-last
SELECT * FROM table LIMIT 5; -- 检索前 5 个记录行
换句话说，LIMIT n 等价于 LIMIT 0,n。

子查询

where(这个值是计算出来的)

本质: 在 where 语句中嵌套一个子查询语句

MySQL 函数

常用函数

SELECT ABS() -- 绝对值
SELECT CELLING() -- 向上取整
SELECT FLOOR() -- 向下取整
SELECT RAND() -- 返回一个 0~1 之间的随机数
SELECT SIGN() -- 判断一个数的符号, 负数返回 -1, 正数返回 1

字符串函数

SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 字符串长度
SELECT CONCAT('我','爱','你') -- 拼接字符串
SELECT INSERT('我爱编程',1,2,'超级热爱') -- 查询, 从某个位置开始替换某个长度
SELECT LOWER('Guoyan') -- 小写字母
SELECT UPPER('Guoyan') -- 大写字母
SELECT INSERT('Guoyan','y') -- 返回第一次出现的字串的索引
SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换出现的指定字符串
SELECT SUBSTR('坚持就能成功',3,2) -- 返回指定的子字符串 (源字符串, 截取的位置, 截取的长度)
SELECT REVERSE('马上') -- 反转

时间和日期函数

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())

系统函数

SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

聚合函数 (常用)

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值

SELECT COUNT(`StudentName`) FROM student; --Count(字段), 会忽略所有的 null 值
SELECT COUNT(*) FROM student; --Count(*), 不会忽略 null 值, 本质, 计算行数
SELECT COUNT(1) FROM student; --Count(1), 不会忽略 null 值, 本质, 计算行数

数据库级别的 MD5 加密 (扩展)

主要增强算法复杂度和不可逆性

UPDATE 表名 SET 字段名=MD5(字段名) WHERE id = 1

事务

什么是事务

将一组 SQL 放在一个批次中去执行

事务原则:ACID 原则: 原子性, 一致性, 隔离性, 持久性 (脏读, 幻读...)

原子性 (Atomicity)

要么都成功, 要么都失败

一致性 (Consistency)

事务前后的数据完整性要保持一致

持久性 (Durability)

事务一旦提交则不可逆, 被持久化到数据库中

隔离性 (Isolation)

事务的隔离性是多个用户并发访问数据库时, 数据库为每一个用户开启的事务, 不能被其他事务的操作数据所干扰, 多个并发事务之间要相互隔离

隔离所导致的一些问题:

脏读:

指一个事务读取了另外一个事务未提交的数据

不可重复读:

在一个事务内读取表中的某一行数据, 多次读取结果不同 (这个不一定是错误, 只是某些场合不对)

虚读 (幻读):

是指在一个事务内读取到了别的事务插入的数据, 导致前后读取不一致

测试事务实现转账

--mysql 是默认开启事务自动提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启 (默认的)
-- 手动处理事务
SET auticommit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始, 从这个之后的 sql 都在同一个事务内
INSERT xx
INSERT xx
-- 提交: 持久化 (成功)
COMMINT
-- 回滚
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点

索引

MySQL 官方对索引的定义为: 索引 (Index) 是帮助 MySQL 高效获取数据的数据结构

索引的分类

主键索引 (PRIMARY KEY)
- 唯一的标识, 主键不可重复, 只能有一列作为主键
唯一索引 (UNIQUE KEY)
- 避免重复的列出现, 唯一索引可以重复, 多个列都可以标识唯一索引
常规索引 (KEY/INDEX)
- 默认的,index,key 关键字来设置
全文索引 (FULLTEXT)
- 在特定的数据库下才有,MyISAM
- 快速定位数据

-- 索引的使用
--1. 在创建表的时候给字段增加索引
--2. 创建完毕后, 增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个全文索引 (索引名) 列名
ALTER TABLE school.student ADD FULLTEXT INDEX studentName(studentNAme);
--EXPLAIN 分析 sql 执行的情况
EXPLAIN SELECT  FROM student; -- 非全文索引
EXPLAIN SELECT  FROM student WHERE MATCH(studentName) AGAINST('刘');

索引原则

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表不需要加索引
索引一般加在常用来查询的字段上

索引的数据结构

Hash 类型的索引

Btree:InnoDB 的默认数据结构

阅读:CodingLabs - MySQL 索引背后的数据结构及算法原理

权限管理和备份

用户管理

SQL 命令操作

用户表:mysql.user

本质: 对这张表进行增删改查

-- 创建用户 CREATE USER 用户名 INDENTIFIED BY '密码' -- 修改密码 (修改当前用户密码) SET PASSWORD = PASSWORD('密码') -- 修改密码 (修改指定用户密码) SET PASSWORD FOR 用户名 = PASSWORD('密码') -- 重命名 RENAME USER 原来的名字 TO 新的名字 -- 用户授权 --ALL PRIVILEGES 全部的权限 (除了给别人授权, 其他的都能干) GRANT ALL PRIVILEGES ON . TO 用户名 -- 查询权限 SHOW GRANTS FOR 用户名 -- 查看指定用户的权限

-- 撤销权限 REMOVE ALL PRIVILEGES ON . FROM 用户名

MySQL 备份

作用:

保证重要的数据不丢失
数据转移

方式:

直接拷贝物理文件
在 Navicat 可视化工具手动导出
使用 mysqldump 命令行导出

-- 导出 # mysqldump -h 主机 -u 用户名 -p 密码数据库表名 > 物理磁盘位置/文件夹 -- 导入 #登录的情况下 source 备份文件

规范数据库设计

糟糕的数据库设计:

数据冗余, 浪费空间
数据库插入和删除都会麻烦, 异常 (屏蔽使用物理外键)
程序的性能差

良好的数据库设计:

节省内存空间
保证数据库的完整性
方便我们开发系统

软件开发中, 关于数据库的设计:

分析需求: 分析业务和需要处理的数据库的需求
概要设计: 设计关系图 E-R 图

设计数据库的步骤 (例: 个人博客):

收集信息, 分析需求
- 用户表 (用户登录注销, 用户的个人信息, 写博客, 创建分类)
- 分类表 (文章分类, 谁创建的)
- 文章表 (文章的信息)
- 友链表 (友链信息)
- 自定义表 (系统信息, 某个关键的字, 或者一些主字段)
标识实体 (把需求落地到每个字段)
标识实体之间的关系
- 写博客:user -> blog
- 创建分类:user -> category
- 关注:user -> user
- 友链:links
- 评论:user -> user ->blog

三大范式

第一范式 (1NF):要求数据库表的每一列都是不可分割的源自数据项 (原子性: 保证每一列不可再分)

第二范式 (2NF):在满足第一范式的前提下, 每张表只描述一件事情

第三范式 (3NF):在满足第一范式和第二范式的前提下, 第三范式需要确保数据表中的每一列数据都和主键直接相关, 而不能间接相关

规范性和性能的问题:

关联查询的表不得超过三张表

考虑商业化的需求和目标 (成本, 用户体验), 数据库的性能更加重要
在规范性能的问题的时候, 需要适当的考虑一下规范性
故意给某些表增加一些冗余的字段 (从多表查询变为单表查询)
故意增加一些计算列 (从大数据量降低为小数据量的查询: 索引)

JDBC(重点)

第一个 JDBC 程序

创建一个普通项目
导入数据库驱动
编写测试代码

import java.sql.*;
// 我的第一个 jdbc 程序
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1. 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
    <span class="hljs-comment">//2.用户信息和url</span>
    <span class="hljs-type">String</span> <span class="hljs-variable">url</span> <span class="hljs-operator">=</span> <span class="hljs-string">"jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&amp;characterEncoding=utf8&amp;userSSL=true&amp;serverTimezone=UTC "</span>;
    <span class="hljs-type">String</span> <span class="hljs-variable">username</span> <span class="hljs-operator">=</span> <span class="hljs-string">"root"</span>;
    <span class="hljs-type">String</span> <span class="hljs-variable">password</span> <span class="hljs-operator">=</span> <span class="hljs-string">"123456"</span>;

    <span class="hljs-comment">//3.连接成功,数据库对象</span>
    <span class="hljs-comment">//connection 代表数据库</span>
    <span class="hljs-type">Connection</span> <span class="hljs-variable">connection</span> <span class="hljs-operator">=</span> DriverManager.getConnection(url, username, password);

    <span class="hljs-comment">//4.执行SQL的对象</span>
    <span class="hljs-type">Statement</span> <span class="hljs-variable">statement</span> <span class="hljs-operator">=</span> connection.createStatement();

    <span class="hljs-comment">//5.</span>
    <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"SELECT * FROM users"</span>;
    <span class="hljs-type">ResultSet</span> <span class="hljs-variable">resultSet</span> <span class="hljs-operator">=</span> statement.executeQuery(sql); <span class="hljs-comment">//返回的结果集</span>

    <span class="hljs-keyword">while</span> (resultSet.next()){
        System.out.println(<span class="hljs-string">"id="</span> + resultSet.getObject(<span class="hljs-string">"id"</span>));
        System.out.println(<span class="hljs-string">"name="</span> + resultSet.getObject(<span class="hljs-string">"name"</span>));
        System.out.println(<span class="hljs-string">"password="</span> + resultSet.getObject(<span class="hljs-string">"password"</span>));
        System.out.println(<span class="hljs-string">"email="</span> + resultSet.getObject(<span class="hljs-string">"email"</span>));
        System.out.println(<span class="hljs-string">"birthday="</span> + resultSet.getObject(<span class="hljs-string">"birthday"</span>));
    }

    <span class="hljs-comment">//6.释放连接</span>
    resultSet.close();
    statement.close();
    connection.close();
}

}

步骤总结:

加载驱动
连接数据库 DriverManager
获得执行 sql 的对象 Statement
获得返回的结果集
释放连接

// 数据库设置自动提交
connection.setAutoCommit();
// 事务提交
connection.commit();
// 事务回滚
connection.rollback();

statement 执行 sql 对象,Preparement 执行 sql 对象

statement.executeQuery(); // 查询操作返回 ResultSet
statement.execute(); // 执行任何 sql
statement.executeUpdate(); // 更新, 插入, 删除都是用这个, 返回一个受影响的行数

ResultSet 查询的结果集: 封装了所有的查询结果

resultSet.getObject(); // 在不知道列类型的情况下使用
// 如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getObject();
...

遍历, 指针

resultSet.beforeFirst(); // 移动到最前面
resultSet.afterLast(); // 移动到最后面
resultSet.next(); // 移动到下一个数据
resultSet.previous(); // 移动到前一行
resultSet.absolute(row); // 移动到指定行

Statement 对象

代码实现:

提取工具类

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;
<span class="hljs-keyword">static</span> {
    <span class="hljs-keyword">try</span>{
        <span class="hljs-type">InputStream</span> <span class="hljs-variable">in</span> <span class="hljs-operator">=</span> JdbcUtils.class.getClassLoader().getResourceAsStream(<span class="hljs-string">"db.properties"</span>);
        <span class="hljs-type">Properties</span> <span class="hljs-variable">properties</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">Properties</span>();
        properties.load(in);

        driver = properties.getProperty(<span class="hljs-string">"driver"</span>);
        url = properties.getProperty(<span class="hljs-string">"url"</span>);
        username = properties.getProperty(<span class="hljs-string">"username"</span>);
        password = properties.getProperty(<span class="hljs-string">"password"</span>);

        <span class="hljs-comment">//1.驱动只用加载一次</span>
        Class.forName(driver);
    } <span class="hljs-keyword">catch</span> (Exception e) {
        e.printStackTrace();
    }
}

<span class="hljs-comment">//获取连接</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> Connection <span class="hljs-title function_">getConnection</span><span class="hljs-params">()</span> <span class="hljs-keyword">throws</span> SQLException {
    <span class="hljs-keyword">return</span> DriverManager.getConnection(url,username,password);
}

<span class="hljs-comment">//释放连接资源</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">release</span><span class="hljs-params">(Connection conn, Statement st, ResultSet rs)</span>{
    <span class="hljs-keyword">if</span> (rs != <span class="hljs-literal">null</span>){
        <span class="hljs-keyword">try</span> {
            rs.close();
        } <span class="hljs-keyword">catch</span> (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    <span class="hljs-keyword">if</span> (st != <span class="hljs-literal">null</span>){
        <span class="hljs-keyword">try</span> {
            st.close();
        } <span class="hljs-keyword">catch</span> (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    <span class="hljs-keyword">if</span> (conn != <span class="hljs-literal">null</span>){
        <span class="hljs-keyword">try</span> {
            conn.close();
        } <span class="hljs-keyword">catch</span> (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

}

编写增删改的方法 (executeUpdate)

import com.gy.lesson02.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) {
    <span class="hljs-type">Connection</span> <span class="hljs-variable">conn</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
    <span class="hljs-type">Statement</span> <span class="hljs-variable">st</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
    <span class="hljs-type">ResultSet</span> <span class="hljs-variable">rs</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;

    <span class="hljs-keyword">try</span> {
        conn = JdbcUtils.getConnection();
        st = conn.createStatement();
        <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"INSERT INTO users (`id`,`name`,`password`,`email`,`birthday`)"</span> +
                <span class="hljs-string">"VALUES(9,'yyy','2222','333@qq.com','2000.02.22')"</span>;
        <span class="hljs-type">int</span> <span class="hljs-variable">i</span> <span class="hljs-operator">=</span> st.executeUpdate(sql);
        <span class="hljs-keyword">if</span> (i &gt; <span class="hljs-number">0</span>){
            System.out.println(<span class="hljs-string">"插入成功"</span>);
        }
    } <span class="hljs-keyword">catch</span> (SQLException throwables) {
        throwables.printStackTrace();
    }<span class="hljs-keyword">finally</span> {
        JdbcUtils.release(conn,st,rs);
    }
}

}

import com.gy.lesson02.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();
            String sql = "DELETE FROM users WHERE id=9";
            int i = st.executeUpdate(sql);
            if (i > 0){
                System.out.println("删除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

import com.gy.lesson02.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();
            String sql = "UPDATE users SET `name`='yyy',`email`='33333@qq.com'WHERE id = 1";
            int i = st.executeUpdate(sql);
            if (i > 0){
                System.out.println("更新成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

编写查的方法 (executeQuery)

import com.gy.lesson02.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;
    <span class="hljs-keyword">try</span>{
        conn = JdbcUtils.getConnection();
        st = conn.createStatement();

        <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"select * from users where id = 1"</span>;
        <span class="hljs-comment">//查询完毕会返回一个结果集</span>
        rs = st.executeQuery(sql);
        <span class="hljs-keyword">if</span> (rs.next()){
            System.out.println(rs.getString(<span class="hljs-string">"name"</span>));
        }
    } <span class="hljs-keyword">catch</span> (SQLException throwables) {
        throwables.printStackTrace();
    }<span class="hljs-keyword">finally</span> {
        JdbcUtils.release(conn,st,rs);
    }
}

}

SQL 注入的问题

sql 存在漏洞, 会被攻击导致数据泄露

import com.gy.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQL注入 {
public static void main(String[] args) {
login("'or'1=1","'or'1=1");
}
<span class="hljs-comment">//登录业务</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">login</span><span class="hljs-params">(String username,String password)</span>{
    <span class="hljs-type">Connection</span> <span class="hljs-variable">conn</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
    <span class="hljs-type">Statement</span> <span class="hljs-variable">st</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
    <span class="hljs-type">ResultSet</span> <span class="hljs-variable">rs</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;

    <span class="hljs-keyword">try</span>{
        conn = JdbcUtils.getConnection();
        st = conn.createStatement();

        <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"select * from users where `name`='"</span>+username+<span class="hljs-string">"' AND `password`='"</span>+password+<span class="hljs-string">"'"</span>;
        <span class="hljs-comment">//查询完毕会返回一个结果集</span>
        rs = st.executeQuery(sql);
        <span class="hljs-keyword">if</span> (rs.next()){
            System.out.println(rs.getString(<span class="hljs-string">"name"</span>));
            System.out.println(rs.getString(<span class="hljs-string">"password"</span>));
        }
    } <span class="hljs-keyword">catch</span> (SQLException throwables) {
        throwables.printStackTrace();
    }<span class="hljs-keyword">finally</span> {
        JdbcUtils.release(conn,st,rs);
    }
}

}

PreparedStatement 对象

PreparedStatement 可以防止 SQL 注入, 效率更好

新增

import com.gy.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
try{
conn = JdbcUtils.getConnection();
        <span class="hljs-comment">//区别:使用?占位符代替参数</span>
        <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"INSERT INTO users (`id`,`name`,`password`,`email`,`birthday`)values(?,?,?,?,?)"</span>;
        ps = conn.prepareStatement(sql);

        <span class="hljs-comment">//手动给参数赋值</span>
        ps.setInt(<span class="hljs-number">1</span>,<span class="hljs-number">5</span>);
        ps.setString(<span class="hljs-number">2</span>,<span class="hljs-string">"ggg"</span>);
        ps.setString(<span class="hljs-number">3</span>,<span class="hljs-string">"123123"</span>);
        ps.setString(<span class="hljs-number">4</span>,<span class="hljs-string">"44444@qq.com"</span>);
        <span class="hljs-comment">//注意点:sql.Date 数据库</span>
        <span class="hljs-comment">//new Date().getTime() 获得时间戳</span>
        ps.setDate(<span class="hljs-number">5</span>,<span class="hljs-literal">null</span>);

        <span class="hljs-comment">//执行</span>
        <span class="hljs-type">int</span> <span class="hljs-variable">i</span> <span class="hljs-operator">=</span> ps.executeUpdate();
        <span class="hljs-keyword">if</span> (i&gt;<span class="hljs-number">0</span>){
            System.out.println(<span class="hljs-string">"插入成功"</span>);
        }
    } <span class="hljs-keyword">catch</span> (SQLException throwables) {
        throwables.printStackTrace();
    }<span class="hljs-keyword">finally</span> {
        JdbcUtils.release(conn,ps,<span class="hljs-literal">null</span>);
    }
}

}

删除

import com.gy.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
try{
conn = JdbcUtils.getConnection();
        <span class="hljs-comment">//区别:使用?占位符代替参数</span>
        <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"DELETE FROM users WHERE id=?"</span>;
        ps = conn.prepareStatement(sql);

        <span class="hljs-comment">//手动给参数赋值</span>
        ps.setInt(<span class="hljs-number">1</span>,<span class="hljs-number">5</span>);

        <span class="hljs-comment">//执行</span>
        <span class="hljs-type">int</span> <span class="hljs-variable">i</span> <span class="hljs-operator">=</span> ps.executeUpdate();
        <span class="hljs-keyword">if</span> (i&gt;<span class="hljs-number">0</span>){
            System.out.println(<span class="hljs-string">"删除成功"</span>);
        }
    } <span class="hljs-keyword">catch</span> (SQLException throwables) {
        throwables.printStackTrace();
    }<span class="hljs-keyword">finally</span> {
        JdbcUtils.release(conn,ps,<span class="hljs-literal">null</span>);
    }
}

}

更新

import com.gy.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
try{
conn = JdbcUtils.getConnection();
        <span class="hljs-comment">//区别:使用?占位符代替参数</span>
        <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"update users set `name`=? where id=?;"</span>;
        ps = conn.prepareStatement(sql);

        <span class="hljs-comment">//手动给参数赋值</span>
        ps.setString(<span class="hljs-number">1</span>,<span class="hljs-string">"郭岩"</span>);
        ps.setInt(<span class="hljs-number">2</span>,<span class="hljs-number">1</span>);


        <span class="hljs-comment">//执行</span>
        <span class="hljs-type">int</span> <span class="hljs-variable">i</span> <span class="hljs-operator">=</span> ps.executeUpdate();
        <span class="hljs-keyword">if</span> (i&gt;<span class="hljs-number">0</span>){
            System.out.println(<span class="hljs-string">"更新成功"</span>);
        }
    } <span class="hljs-keyword">catch</span> (SQLException throwables) {
        throwables.printStackTrace();
    }<span class="hljs-keyword">finally</span> {
        JdbcUtils.release(conn,ps,<span class="hljs-literal">null</span>);
    }
}

}

查询

import com.gy.lesson02.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 ps = null;
ResultSet rs = null;
    <span class="hljs-keyword">try</span>{
        conn = JdbcUtils.getConnection();
        <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"select * from users where id=?"</span>;

        ps = conn.prepareStatement(sql);

        ps.setInt(<span class="hljs-number">1</span>,<span class="hljs-number">1</span>);
        <span class="hljs-comment">//执行</span>
        rs = ps.executeQuery();
        <span class="hljs-keyword">if</span> (rs.next()){
            System.out.println(rs.getString(<span class="hljs-string">"name"</span>));
        }
    } <span class="hljs-keyword">catch</span> (SQLException throwables) {
        throwables.printStackTrace();
    }<span class="hljs-keyword">finally</span> {
        JdbcUtils.release(conn,ps,rs);
    }
}

}

SQL 注入

import com.gy.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SQL注入 {
public static void main(String[] args) {
login("'' or 1=1","123456");
}
<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">login</span><span class="hljs-params">(String username,String password)</span>{
    <span class="hljs-type">Connection</span> <span class="hljs-variable">conn</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
    <span class="hljs-type">PreparedStatement</span> <span class="hljs-variable">ps</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
    <span class="hljs-type">ResultSet</span> <span class="hljs-variable">rs</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;

    <span class="hljs-keyword">try</span>{
        conn = JdbcUtils.getConnection();
        <span class="hljs-comment">//PreparedStatement防止SQL注入的本质,把传递进来的参数当作字符</span>
        <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"select * from users where `name`=? and `password`=?"</span>;

        ps = conn.prepareStatement(sql);

        ps.setString(<span class="hljs-number">1</span>,username);
        ps.setString(<span class="hljs-number">2</span>,password);
        <span class="hljs-comment">//执行</span>
        rs = ps.executeQuery();
        <span class="hljs-keyword">if</span> (rs.next()){
            System.out.println(rs.getString(<span class="hljs-string">"name"</span>));
        }
    } <span class="hljs-keyword">catch</span> (SQLException throwables) {
        throwables.printStackTrace();
    }<span class="hljs-keyword">finally</span> {
        JdbcUtils.release(conn,ps,rs);
    }
}

}

JDBC 操作事务

import com.gy.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction01 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
    <span class="hljs-keyword">try</span> {
        conn = JdbcUtils.getConnection();
        <span class="hljs-comment">//关闭数据库的自动提交,开启事务</span>
        conn.setAutoCommit(<span class="hljs-literal">false</span>);

        <span class="hljs-type">String</span> <span class="hljs-variable">sql1</span> <span class="hljs-operator">=</span> <span class="hljs-string">"update account set money= money-100 where name = 'A'"</span>;
        ps = conn.prepareStatement(sql1);
        ps.executeUpdate();
        <span class="hljs-type">String</span> <span class="hljs-variable">sql2</span> <span class="hljs-operator">=</span> <span class="hljs-string">"update account set money= money+100 where name = 'B'"</span>;
        ps = conn.prepareStatement(sql2);
        ps.executeUpdate();

        <span class="hljs-comment">//业务完毕,提交事务</span>
        conn.commit();
        System.out.println(<span class="hljs-string">"成功"</span>);
    } <span class="hljs-keyword">catch</span> (SQLException throwables) {
        <span class="hljs-keyword">try</span> {
            <span class="hljs-comment">//如果失败,则默认回滚</span>
            conn.rollback();
        } <span class="hljs-keyword">catch</span> (SQLException e) {
            e.printStackTrace();
        }
        throwables.printStackTrace();
    }<span class="hljs-keyword">finally</span> {
        JdbcUtils.release(conn,ps,rs);
    }
}

}

数据库连接池

数据库连接 --> 执行完毕 --> 释放

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

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

开源数据源实现

DBCP

C3P0

Druid: 阿里巴巴

使用了这些数据库连接池之后, 我们在项目开发中就不需要编写连接数据库的代码了

DBCP

需要用到的 jar 包

commons-dbcp-1.4

commons-pool-1.6

C3P0