MYSQL基础

 数据库·····语言
 DDL定义    DML操作(增删改)    DQL查询    DCL控制

1、库与表的sql语句

  CREATE创建
  DROP删除
  ALTER修改    
  对象:database数据库 table表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(5) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(20) NOT NULL DEFAULT '学生姓名' COMMENT '姓名',
`password` VARBINARY(30) NOT NULL DEFAULT'123456789' COMMENT '密码',
`sex` VARCHAR(5) NOT NULL DEFAULT '男' COMMENT '性别',
`age` INT(5) NOT NULL DEFAULT '18' COMMENT '年龄',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

ENGINE 引擎
-INNODB 现在默认使用,安全性高。
-MYISAM 早期使用,节约空间。
CHARSET 字符集 一般选择utf8。

SHOW CREATE TABLE XXX;-- 查看表的sql创建语句 database对应查看数据库
DESC XXX;--查看表的结构

ALTER TABLE XXX RENAME AS ··· -- 修改表名
ALTER TABLE XXX ADD ··· --增加表的字段
ALTER TABLE XXX DROP ··· --删除字段
ALTER TABLE XXX MODIFY ··· --修改表的字段属性、约束
ALTER TABLE XXX CHANGE ··· -- 表的字段重命名

2、表数据管理

 insert插入    update修改    delete删除
 对象一般是`表名` 
 注意字段用``,写常量赋值时用' '

2.1、外键

1
2
3
ALTER TABLE XXX
ADD CONSTRAINT `FK_xxxid` FOREIGN KEY(`xxxid`) REFERENCES `xxx`(`xxxid`);
-- 不常用,因为外键删除时,父表和子表均要处理,联动关系很麻烦

2.2、插入

1
2
insert into `表名`(`字段名`) values ('值');
-- 将值赋予相应字段,位置一一对应

2.3、修改

1
2
update `表名` set `字段`= '值' where 条件
-- 条件即指定一些判定语句,没有where来判断,则表内数据全更改

2.4、删除

1
2
3
4
5
delete from `表名` where 条件
-- 条件是指定删除对象,无where判断则表数据全删,自增量不变
-- 使用INNODB,在delete后 重启数据库,自增量从1开始,而使用MYISAM重启后自增量不变
truncate table `表名`
-- 清表,自增量归0

3、DQL查询

1
2
3
4
5
6
7
8
select ····· from ·····、
where ··· -- 条件
group by ··· -- 按字段分组
having ··· -- 过滤分组的二级条件
order by ··· -- 排序
limit ··· -- 分页
union ··· -- 结合多个查询语句结果成一个结果集
-- 查询标准格式,功能关键词要按序使用,不可乱序使用。

3.1、DQL

1
2
3
4
5
6
7
8
9
10
11
12
13
select * from `表名` -- *代表查询全部内容
select `字段1` as 别名,`字段2` as 别名 from `表名` as 别名
-- 别名``,''带不带都行,不要别名,查询出来即设置的字段名

-- concat(a,b) 连接a,b两个字符串
select concat('我的名字是 ',`名字字段`) as 别名 from `表名`

-- distinct 去重
select distinct `字段` from `表名`

SELECT VERSION(); -- 查询版本
SELECT 455*8484 AS 结果; -- 基本计算
SELECT `studentresult`+1 AS 成绩加1FROM `result`; -- 改变字段数据

3.2、where及条件语句

简单查询

1
2
3
4
5
6
-- 简单逻辑运算符
and && or|| not

-- where 返回布尔值
SELECT `studentresult` AS 成绩 FROM `result` WHERE `studentresult`>=80 AND `studentresult`<=90;
SELECT `studentresult` AS 成绩 FROM `result` WHERE `studentresult` BETWEEN 80 AND 100;

模糊查询

语句 描述
a not null 若为空则真
a is not null 不为空则真
a between b and c a在b与c之间为真
a like b a匹配b成功则为真
a in (a1,a2,a3···) 括号内值有a则为真
1
2
3
4
select `字段` from `表名` where `字段` like '常量'
-- 其中%代表0或多个任意字符,一个_为单个字符
select `字段` from `表名` where `字段` in ('范围',' ',' ')
-- 找出范围内的数据

连接查询

参考网页:https://www.cnblogs.com/mafeng/p/10150013.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
/*
连接不同表来查询数据;
连接查询 INNER/LEFT/RIGHT JOIN(连接表名) ON (判断条件);
这里是连接查询使用join on配套语句,条件语句不用where;
必须要有on;
多个表内有相同字段,需要标明对应的表名;
一般on条件为相同量。
*/
-- 1、内连接 inner join(和join相同)
SELECT s.studentno,studentname FROM student AS s
INNER JOIN result AS r
ON s.studentno = r.studentno
-- 返回符合条件语句的查询字段

-- 2、外连接 left join/right join/full join
select 字段1,字段2,字段3 from 表名1
left join 表名2
on 条件
-- 返回左表的全部需要字段,且返回右表符合条件语句的字段,没有的字段为null(right join:左->右),full join相当于前两个的并集。

-- 3、多表连接
SELECT s.studentno,s.studentname,subjectname
FROM student AS s
INNER JOIN result AS r
ON s.studentno = r.studentno

RIGHT JOIN `subject` AS sub
ON r.subjectno=sub.subjectno
-- 用join语句连接多表,每次连接均与join语句左半部分连接。

3.3、自连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
/*
一种逻辑连接,可对一个表查询进行自身连接。
例子:以下语句给每一个种类给了两个相关字段,一些字段pid为1,说明其为父类,而另一些字段pid=父类字段的 子类字段,这便有了父子关系,于是同一张表的信息也有了联系。
*/
CREATE TABLE `school`.`category`(
`categoryid` INT(3) NOT NULL COMMENT 'id',
`pid` INT(3) NOT NULL COMMENT '父id 没有父则为1',
`categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB COLLATE=utf8_general_ci CHARSET=utf8;

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

/*
以下便是自连接,where判断条件即该表中子字段=父字段,查询出父子关系
自连接并没有新的语句,算是一种逻辑连接。
*/
SELECT a.`categoryname` AS father, b.`categoryname` AS son
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`

3.4、排序和分页

1
2
order by 字段 asc -- asc升序 desc降序
limit a,b -- a即第n-1个数据,从0开始;b即每页限制内容大小;0,b即第一页,b,b第二页,2b,b第三页···

3.5、子查询

1
2
3
4
5
6
7
8
9
SELECT studentno,studentname,gradeid
FROM student
WHERE gradeid = (
SELECT gradeid FROM `subject`
WHERE `subjectname`='高等数学-3'
)
/*
inner join是直接连接的,而自连接类似嵌套写法,比方这里,想查询高等数学-3课程的gradeid,但student里没有gradename,所以嵌套一个查询在subject中找到高等数学-3的gradeid
*/

4、函数

1
2
select 函数名(参数)
建议直接查函数库

4.1、数学相关

1
2
3
4
5
6
7
8
abs() -- 绝对值
ceiling() -- 向上取整
floor() -- 向下取整
round() -- 四舍五入
rand() -- 随机数
sign() -- 取符号,0对应0,负数对应-1,正数对应1
sqrt() -- 取平方根
·····

4.2、字符串相关

1
2
3
4
5
6
7
8
char_length() -- 无论是英文、汉字还是数字,均算1字符
length() -- 汉字算3字符,英文、数字算1字符
concat() -- 连接字符串
lower() -- 转小写
upper() -- 转大写
instr(a,b) -- b字符串第一次在a字符串中出现的位置
reverse() -- 反转
·····

4.3、时间日期相关

1
2
3
4
curdate() -- 年月日
now() -- 年月日时分秒
year() month() day() hour() minute() second() -- 年月日时分秒

4.4、聚合函数

1
2
3
4
5
count() -- 返回满足条件的记录数
count(*) -- 含所有行列查询,但结果计算时不忽略NULL
count(1) -- 统计表中所有记录数,不忽略NULL,会包含NULL的记录
count(列名) -- 统计表中字段次数,忽略NULL,不统计NULL字段
sum() avg() min() max() -- 求和、平均值、最小值、最大值

4.5、分组+过滤

1
2
3
4
5
6
SELECT subjectno,AVG(`studentresult`) 
FROM `result` AS r
GROUP BY `subjectno`
HAVING AVG(`studentresult`)>=70
-- group by 字段分组
-- having 分组条件

5、事务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
事务即多方同步操作
eg:转账,a给b转账,a-5000同时b+5000
遵守ACID原则:
Atomicity 原子性
事务要么都成功,要么都失败,即操作同步。
当执行事务时有失败操作,可进行回滚,回到未改变的时候。这个还得靠我们主观判断。
Consistency 一致性
事务完成前后,数据库总量不变,执行前与执行后一致。
Isolation 隔离性
一个事务执行时不受其他事务影响。事务间隔离。
Durability 持久性
事务提交后不可逆,数据持久(永久)保存于数据库。

-- mysql默认开启事务
-- 手动处理事务,要先关闭自闭提交事务。 不然没法回滚。
SET autocommit = 0 -- 关闭自动提交
SET autocommit = 1 -- 开启自动提交(默认,即随时保存)

START TRANSACTION; -- 开启事务
UPDATE information SET money=money-5 WHERE `name`='tang';
UPDATE information SET money=money+5 WHERE `name`='xxx';

COMMIT -- 提交
ROLLBACK -- 回滚
SAVEPOINT -- 设置保存点
ROLLBACK TO SAVEPOINT -- 回退到保存点
RELEASE SAVEPOINT -- --撤销、删除保存点

在并发下事务会产生的问题:
1、脏读
2、不可重复读
3、幻读

事务的隔离级别:
1、序列化
2、可重复读
3、提交读
4、未提交读

6、索引(INDEX)

1
2
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
索引是为了提高数据检索能力而产生的。提高性能。

6.1、索引分类

1
2
3
4
5
6
7
8
9
主键索引(PRIMARY KEY)
唯一标识,不可重复。主键索引只能有一个。
唯一索引(UNIQUE)
索引的字段不重复,唯一索引可有多个。
普通索引(INDEX)
索引的字段可重复。
全文索引(FULLTEXT)
提高查询速度。
char、varchar、text类型 + MyISAM独有 #可能现在新版本没限制了

6.2、100万数据测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- 大数据可明显看出索引提高了搜索性能
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别(0:男;1:女)',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT = 'app用户表'

-- 插入100万数据.
DELIMITER $$ -- 写函数之前必须要写,标志

CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUES(CONCAT('用户',i),'1262306856@qq.com'
,CONCAT('13',FLOOR(RAND() * ((999999999-100000000)+100000000)))
,FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100) );
SET i=i+1;
END WHILE;
RETURN i;
END;
-- 先写函数,再执行。
SELECT mock_data() -- 执行此函数 生成一百万条数据

-- 大数据对比
SELECT * FROM `app_user` WHERE `name`='用户9999'; -- 未加索引前查询1s
CREATE INDEX id_app_user_name ON app_user(`name`); -- 加索引,然后再查询,发现查询约为0s

7、数据库安全

7.1、用户管理

1
2
3
4
create user `用户名` identified by `密码` -- 创建用户
set password [for `用户`] = password(`密码`) -- 修改[当前]用户密码
rename user `旧名` to `新名` -- 修改用户名
drop user `用户` -- 删除用户

7.2、用户权限

1
2
3
4
-- 一般用可视化界面完成
grant all privileges on `数据库名.表名` to `用户` -- 授予所on有权限,除了给他人授权
show grants for `用户` -- 查询权限
revoke all privileges on `数据库名.表名` from `用户` -- 收回所有权限

7.3、数据库备份

1
2
3
4
1.物理备份
2.命令行指令:
mysqldump -u `用户` -h `主机` -p `数据库` `表名` [表1 表2 表3] > `文件路径`
3.导入:source `文件路径`

8、三大范式

参考:https://www.cnblogs.com/wsg25/p/9615100.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
第一范式(1NF):
原子性:每一个字段不可再拆分。
第二范式(2NF):
前提:1NF
数据库表中每一个字段均和主键相关。
第三范式(3NF):
前提:1NF、2NF
数据表中每一个字段都和主键有直接相关,而不是间接相关。

三大范式用来规范数据库的设计

关联查询的表不超过3张:
考虑商业化的需求,数据库性能考虑更重要。
适当考虑规范性。
故意给某些表添加冗余字段。

9、JDBC

Java database connect

相关jar包下载:

https://downloads.mysql.com/archives/c-j/

1
2
3
4
1、创建新工程,新建lib文件夹(library)
2、copy粘贴jar包到lib
3、右键lib,add as Library
导入成功后便可使用

9.1、模板

DriverManager

1
2
3
4
5
6
7
8
9
10
11
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
class.forName("com.mysql.jdbc.Driver");//加载驱动的固定写法,死记

Connection connection = DriverManager.getConnection(url,username,password);
/*
*connection 即数据库
*connection.commit(); 事务提交
*connection.rollback(); 事务回滚
*connection.setAutoCommit(); 数据库自动提交
*/

URL

1
2
3
4
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";

//协议://主机地址:端口号/数据库?参数1&参数2&参数3;
//mysql:jdbc:mysql localhost:3306

Statement(重要!!!)

1
2
3
4
5
//使用数据库对象,增删改查 
//···(sql),String sql对象,然后传对象即可。
statement.executeQuery();//查询返回ResultSet
statement.execute();//执行任何sql
statement.executeUpdate();//增、删、改,返回会影响的函数

ResultSet

1
2
3
4
5
6
7
8
9
10
11
12
13
14
//查询结果集,封装所有查询结果
//获得指定数据类型
resultSet.getObject();
resultSet.getString();
resultSet.getInt();
resultSet.getDouble();
·····

//遍历
resultSet.beforeFirst();//光标移至最前
resultSet.afterLast();//光标移至最后
resultSet.next();//移到下一数据
resultSet.previous();//移至第一行
resultSet.absolute(row);//移至指定行

释放资源

1
2
3
4
//对象.close() 及时释放,connwction很占空间
resultSet.close();
statement.close();
connection.close();

9.2、Java代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2、用户信息 url
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";

String username = "root";

String password = "111111";
//3、连接成功,数据库对象
Connection connection = DriverManager.getConnection(url,username,password);
//4、执行SQL对象
Statement statement = connection.createStatement();
//5、执行SQL对象的结果
String sql = "SELECT * FROM users";

ResultSet resultSet = statement.executeQuery(sql);

while(resultSet.next()){
System.out.println("id=" + resultSet.getObject("id"));
System.out.println("NAME=" + resultSet.getObject("NAME"));
System.out.println("PASSWORD=" + resultSet.getObject("PASSWORD"));
System.out.println("email=" + resultSet.getObject("email"));
System.out.println("birthday=" + resultSet.getObject("birthday"));
}
//6、释放连接
resultSet.close();
statement.close();
connection.close();
}

9.3、工具类实现及其他应用

工具类

1
2
3
4
5
###db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=111111
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {

try{
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);

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

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

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

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

if(statement!=null){
try{
statement.close();
}catch (SQLException e){
e.printStackTrace();
}
}

if(connection!=null){
try{
connection.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
}

增删改:executeUpdate

模板几乎一样,编写不同的sql语句然后调用即可。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
//增
public class TEST {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//数据库连接
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)"+
"VALUES(7,'s','sss','8888888','2555-08-24')";
int i = statement.executeUpdate(sql);
if(i>0){
System.out.println("成功插入");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
//删
public class TestDelete {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//数据库连接
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "delete from users where id=7";
int i = statement.executeUpdate(sql);
if(i>0){
System.out.println("删除了");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
//改
public class TestUpdata {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//数据库连接
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "update users set `NAME`='ttttt' where id=1";
int i = statement.executeUpdate(sql);
if(i>0){
System.out.println("修改成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}

查:executeQuery

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
//查
public class TestSelect {
public static void main(String[] args){
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;

try{
connection = JdbcUtils.getConnection();
statement = connection.createStatement();

String sql = "select * from users where id=3";
resultSet = statement.executeQuery(sql);//查询完返回ResultSet

while(resultSet.next()){
System.out.println(resultSet.getString("NAME") +"\n"+ resultSet.getString("PASSWORD"));
}

}catch (SQLException e){
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}

SQL注入

拼接sql语句,非法攻击,极其不安全。

解释:https://baike.baidu.com/item/sql%E6%B3%A8%E5%85%A5/150289?fr=aladdin

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
public static void main(String[] args){
//login("wangwu","123456");
login("' or '1=1","' or '1=1");//没有输入正确用户密码,就能访问全部数据。
}

public static void login(String username,String password){
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;

try{
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
//select * from users where `NAME`='wangwu' and `PASSWORD`='123456';
//select * from users where `NAME`= '' or '1=1' and `PASSWORD`='' or '1=1';
String sql = "select * from users where `NAME`='" +username+ "'and `PASSWORD` ='" +password+ "'";
resultSet = statement.executeQuery(sql);//查询完返回ResultSet

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

}catch (SQLException e){
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}

PreparedStatement(更推荐使用)

PreparedStatement可防止SQL注入,且执行效率更高。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
//修改的模板,增删改就参数变动即可,和Statement类似.
public static void main(String[] args) {
Connection c = null;
PreparedStatement p = null;
ResultSet r = null;
try {
c = JdbcUtils.getConnection();
//PreparedStatement和Statement区别
//这里是预编译,使用?占位符代替参数
String sql = "insert into users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
p = c.prepareStatement(sql);//预编译sql,先写不执行

//手动赋值,参数1和sql中x位置对应
p.setInt(1,8);
p.setString(2,"xxxx");
p.setString(3,"sdsdsdsd");
p.setString(4,"88888@qq");
//sql.Date 数据库 java.sql.Date
//util.Date java new Date().getTime()
p.setDate(5,new java.sql.Date(new Date().getTime()));

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

}catch (SQLException e){
e.printStackTrace();
}finally {
JdbcUtils.release(c,p,r);
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
//查询的模板,大致一样,使用的是executeQuery
public static void main(String[] args) {
Connection c = null;
PreparedStatement p = null;
ResultSet r = null;
try {
c = JdbcUtils.getConnection();
//PreparedStatement和Statement区别
//这里是预编译,使用?占位符代替参数
String sql = "select * from users where id =?";
p = c.prepareStatement(sql);//预编译sql,先写不执行

//赋值
p.setInt(1,1);

//执行
r = p.executeQuery();
if(r.next()){
System.out.println(r.getString("NAME")+"\n"+r.getString("email"));
}

}catch (SQLException e){
e.printStackTrace();
}finally {
JdbcUtils.release(c,p,r);
}
}

防止SQL注入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
/*
*PreparedStatement防止SQL注入本质是:将传入的参数当作字符,语句中使用了占位符,规定了sql语句的结
*构。用户可以设置"?"的值,但是不能改变sql语句的结构。参数中的引号会被转义。
*/
public static void main(String[] args){
//login("wangwu","123456");
login("'' or '1=1'","'' or '1=1'");
}

public static void login(String username,String password){
Connection c = null;
PreparedStatement p = null;
ResultSet r = null;

try{
c = JdbcUtils.getConnection();
String sql = "select * from users where `NAME`=? and `PASSWORD`=?";
p=c.prepareStatement(sql);

p.setString(1,username);
p.setString(2,password);

r = p.executeQuery();//查询完返回ResultSet
while(r.next()){
System.out.println(r.getString("NAME"));
System.out.println(r.getString("email"));
}

}catch (SQLException e){
e.printStackTrace();
}finally {
JdbcUtils.release(c,p,r);
}
}

JDBC操作事务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
public static void main(String[] args) {
Connection c = null;
PreparedStatement p = null;
ResultSet r = null;

try {
c = JdbcUtils.getConnection();
//关闭数据库自动提交,java会自动开启事务,所以不用再写开启事务语句
c.setAutoCommit(false);//开启事务

String sql1 = "update account set money=money+100 where `name`='A'";
p = c.prepareStatement(sql1);
p.executeUpdate();

String sql2 = "update account set money=money-100 where `name`='B'";
p = c.prepareStatement(sql2);
p.executeUpdate();

c.commit();
System.out.println("修改好了");
}catch (SQLException e){
//失败默认回滚,不用自己写
// try {
// c.rollback();
// }catch (SQLException e1){
// e1.printStackTrace();
// }
e.printStackTrace();
}finally {
JdbcUtils.release(c,p,r);
}
}

9.4、数据库连接池

使用连接池,不用自己编写连接数据库相关代码。

DBCP

commons-dbcp-1.4.jar

commons-pool-1.6.jar

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
###dbcpconfig.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=111111

#初始化连接数
initialSize=10

#最大连接数
maxActive=50

#最大空闲连接
maxIdle=20

#最小空闲连接
minIdle=5

#最长等待超时时间 以毫秒为单位
maxWait=60000

connectionProperties=useUnicode=true;characterEncoding=UTF8

defaultAutoCommit=true

defaultReadOnly=

defaultTransactionIsolation=READ_UNCOMMITTED

工具类

使用连接池,不用自己去连接数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
private static DataSource dataSource = null;

static {

try{
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties properties = new Properties();
properties.load(in);

//创建数据源 工厂模式
dataSource = BasicDataSourceFactory.createDataSource(properties);
}catch (Exception e){
e.printStackTrace();
}
}

//获得连接
public static Connection getConnection() throws SQLException{
return dataSource.getConnection();//数据源获得连接
}

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

if(statement!=null){
try{
statement.close();
}catch (SQLException e){
e.printStackTrace();
}
}

if(connection!=null){
try{
connection.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}

dbcp数据库功能使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
public static void main(String[] args) {
Connection c = null;
PreparedStatement p = null;
try {
c = DbcpUtils.getConnection();
//PreparedStatement和Statement区别
//使用?占位符代替参数
String sql = "insert into users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
p = c.prepareStatement(sql);//预编译sql,先写不执行
//手动赋值
p.setInt(1,10);
p.setString(2,"x");
p.setString(3,"sd");
p.setString(4,"888@qq");
//sql.Date 数据库 java.sql.Date
//util.Date java new Date().getTime()
p.setDate(5,new java.sql.Date(new Date().getTime()));

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

}catch (SQLException e){
e.printStackTrace();
}finally {
DbcpUtils.release(c,p,null);
}
}

C3P0

c3p0-0.9.5.5.jar

mchange-commons-java-0.2.19.jar

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
###c3p0-config.xml 注意c3p0配置文件是xml


<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>

  <default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf-8</property>
<property name="user">root</property>
<property name="password">111111</property>

<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
  </default-config>
  
  <named-config name="MYSQL"> 
    <property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf-8</property>
<property name="user">root</property>
<property name="password">111111</property>

<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
  </named-config>
  
</c3p0-config>

10、小结

搞了半个月?总算是弄完数据库了,增删改查还要在今后的实践中熟练,下一站前端套餐走起。