第一章
命令行工具
mysqladmin:MySQL服务器管理工具
mysql:MySQL客服端链接工具
mysqldump
演示链接到服务器host=127.0.0.1,用户名为root,密码为空
mysql.exe -h 127.0.0.1 -u root
>use test;
>select*from example;
>quit;
注:初始状态下,root密码为空
C/S架构(Client/Server)
一台主机运行MySQL
多台客服机同时链接.
第二章 2.1创建数据库
数据库 Database,就是存储数据的仓库
MySQL自带4个数据库
test是测试用.
其余的三个是系统库
右键创建example
右键example 更多数据库操作,删除数据库
1.在MySQL服务器里,可以创建多个数据库
2.数据库的命名规则:
-用有意义的名字.
-多个单词用下划线连接,如company_project
-全部使用小写字母.
3.不要操作MySQL自带的系统库.
2.2 创建表
-创建数据库rh_school,用于存储学校内的数据.
-创建表rh_student,用于存储学生信息
学生信息:
列名 数据类型 长度
id int
name varchar 32
phone varchar 16
birthday date
向表中添加数据.
对rh_student点右键打开表.
添加数据,保存.
改完后,点保存(离开该行时会自动保存.)
C/S架构
此时数据已经存储到数据库里
用户从其他电脑也可以用SQLyog登录访问MySQL
学习了如何创建一张表,向表里添加多行数据,了解三种数据类型.
2.3 数据类型
整数 int 更多tinyint,smallint,int,bigint.
字符串 varchar 更多 char,varchar
浮点型 double double,float
日期时间 datetime 更多date,time,datetime,timestamp.
大块数据 text/blob 更多tinytext,text,mediumtext,longtext,blob...
整数类型 tinyint(1字节),smallint(2字节),mediumint(3字节),int(4字节),bigint(8字节).
//如果不确定,就用int,一般够大了.
浮点类型:用于保存小数
double(8字节)
float(4字节)
//如果不确定,就用double类型.
字符串类型.
char:固定长宽字符串
varchar:可变长度字符串
注:1.必须指定最大长度
2.如果你不确定,就用varchar
3.最大的字符串适合用varchar?10k以下吧
日期类型.
datetime:日期时间 具体格式:2018-3-10 11:09:09
date:日期
time:时间
timestamp:时间戳(自1970-01-01 00:00:00)
year:年份
不确定使用datetime.
大块数据.
BLOB(binary large object),二进制大对象
比如,你想把一个图片文件做为一列存储到数据里(不建议)
text 存储大段文本数据.
blob 存储大段字节数据
text(65k),mediumtext(16M),longtext(4G)
2.4 列的属性
列名的设置:可以是中文,但通常是英文
-不要使用SQL里的关键字 database table
-使用有意义的单词 简写
-第一个单词小写(与java兼容,减少麻烦)
如:realName,fileSize,storePath
列的属性,使用2.3推荐的.
长度length
对于varchar/text字段,需设置最大长度
-不区分中英文,按字符数统计.
-太长的字符串,不适合varchar存储.
varchar(512)和varchar(1024)实质没有差别,按实际大小存储.
对于其他类型,不需要设置长度(意义不大)
例如,int(4)表示最小显示宽度为4,不足时凑空格或0(需勾选ZeroFill属性)
列的属性:非空?
空值null:指这一列的值尚未设置
当一列设置为非空时,表示该列必须设置.
String str=null; //空值NULL
String str=""; //空字符串.
默认值
若某列没有设置值,固定赋值.
2.5 主键和自增
主键 Primary Key
主键,即是唯一标识.
例如:在student表中,可以设id为主键,因为id可以唯一标识一行记录
在订单表中,可以将订单id设为主键,订单id可以唯一标识一个订单.
主键设计遵循以下原则:
可以唯一标识一条数据.
通常为整形或字符串型(短字符串)
一个表最多只要一个主键
主键可以是第一列,也可以是最后一列,数据库里的列的顺序是不重要的(顺序无关)
建议所有的表都设置主键(非必须)
1.name字段可以做为student表的主键吗
2.为什么应该用比较短的字段做为主键,长字符串行吗
3.联合主键.
使用多字段合在一起做为主键,id+name,但还是一个主键.
主键的作用
主键唯一标识一行记录
MySQL会把主键单独拿出了,存为一个索引,排序后存储(方便后续查找)
显然,整数主键的查找速度更快.
自增
当以整形字段做为主键时,可以同时设置为自增
所谓自增,即是插入记录时,由MySQL来生成ID,保证ID是自增(每次+1)
自增的起点:
新建的表,默认为1开始.
或表中有数据,从上一个最大值开始.
过号作废,不重复(删除10以后,下一个auto不会是10,是11)
可以指定起点.
改变表高级自动增量.
3.1 SQL
SQL Structured Query Language
SQL语句:客户端用来操作数据库的语句.
在SQLyog里演示:
CREATE DATABASE xxx_example;
DROP DATABASE xxx_example;
作用:创建/删除数据库"xxx_example"
客户端和服务器的交互
用户名/密码
SQLyog电脑A----------->MySQL电脑B
<-----------
登录成功
CREATE DATABASE xxx_example;
----------->
<-----------
操作成功
SQLyog历史记录
实际上每个SQLyog里的操作,都是生成了一个SQL语句发给服务器
在询问窗口练习SQL语句,效率最高.
SELECT * FROM rh_school.`rh_student`
3.2 SQL创建数据库
CREATE DATABASE xx_sample 创建
关键词不区分大小写,CREATE/create均可
名称以反引号引起来. //暂时不能添加''添加''无法正确创建.
DROP DATABASE xxx_example;删除
UES xxx_sample;
切换数据库.
数据库命名规则
-用有意义的名字.
-多个单词用下划线连接,如company_project
-全部使用小写字母.不要使用空格和特殊字符.
-不要使用系统关键词.
3.3 SQL创建表
CREATE TABLE命令可以创建表
CREATE TABLE `student`(
`id`int(11)NOT NULL,
`name`varchar(64)NOT NULL,
`phone`varchar(16)DEFAULT'1380000',
`birthday`date DEFAULT NULL,
PRIMARY KEY(`id`)
);
数据是使用单引号,前面的列名是反引号.``''
删除表
DROP TABLE IF EXISTS `student`; 如果存在再删除,IF EXISTS.
ALTER TABLE //修改表不太常用.
3.4 SQL插入数据
创建准备用的表.
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`id`INT(11)NOT NULL COMMENT '学号',
`name`VARCHAR(64)NOT NULL COMMENT'姓名',
`phone`VARCHAR(16)DEFAULT '13090'COMMENT '手机号',
`birthday`DATE DEFAULT NULL COMMENT'生日',
PRIMARY KEY (`id`)
);
插入数据
INSERT INTO命令可以插入数据.
语法:
INSERT INTO 表名
(列1,列2....)
VALUES
()
INSERT INTO `rh_sample`.`student`
(id,`name`,phone,birthday)
VALUES
(20180001,'xx','130313131','1982-1-1')
列名指定时不需要按照顺序.
但是列名需要和值values一一对应.
简化写法1:
如果按照顺序将所有列赋值,可以不写列名
INSERT INTO`rh_sample`.`student`
VALUES('20180001','xx','123123','1991-12-21')
简写2:
如果名字其得比较规范,那么数据库名,表名,列名都可以省略引号
INSERT INTO student
VALUES('20180001','xx','123123','1991-12-21')
简写3:
对于整数或者小数值,可以省略单引号,对于其他类型,必须加单引号.
INSERT INTO student
VALUES(20180001,'xx','123123','1991-12-21')
在一条SQL里插入多行数据.
INSERT INTO `rh_sample`.`student`
(id,`name`,phone,birthday)
values
(20180001,'xx','130313131','1982-1-1'),
(20180002,'xx1','130313131','1982-1-1'),
(20180003,'xx2','130313131','1982-1-1'),
(20180004,'xx3','130313131','1982-1-1')
只列出部分字段.
3.5 查询SQL数据 最常用最重要.
sql script sql脚本
SELECT * FROM student
SELECT * FROM student WHERE id='20180004'
指定过滤条件
使用WHERE字句可以指定过滤条件,不符合条件的行将被删除不显示.
WHERE id='20180001'
WHERE id <>'20180001'; //不等于
WHERE phone IS NULL; //手机号为空
WHERE birthday >'2011-1-1'; //11年以后
多个条件可以用AND OR
例如:
WHERE id>'20180002'AND id<'20180005'
WHERE id>'20180002'OR id<'20180005'
还可以小括号联立多个条件
xxx AND yyy AND(zzz OR www)
指定列
在查询时,可以只返回部分列,
select * from student; //返回全部列
select name,birthday from student where id<20180003; //返回姓名,生日.
3.6 SQL数据更新 UPDATE
UPDATE 表名SET 列名='value' WHERE 过滤条件
示例:
UPDATE student SET phone='13800100' WHERE id='20180001'
示例2:
UPDATE student SET phone=NULL WHERE id>='20180001'AND id<='20180003'
可见,一条UPDATE 语句可以一次性修改所有符合条件的行.
示例3:
UPDATE student SET phone='130000',birthday='1991-1-1'WHERE id='20180001'
可见一条UPDATE 语句可以修改多个列.
示例4:
UPDATE article SET numView=numView+8 WHERE id='1000'
可见 SET语句里可以比较特殊的写法 (在原数字上加8)
3.7删除数据
删除一个表里的所有数据(清空/截断表)
TRUNCATE TABLE article;
注意:自增基数也被重置.
删除指定行
用DELETE 命令删除指定行,
DELETE FROM 表名 WHERE 过滤条件
注意:如果不添加where限制,会删除所有行.
增,删,查,改操作中select语句是重点.
在数据库领域,通常把这4种操作简称为CRUD
增加(CREATE) 读取(RETRIEVE) 更新(UPDATE) 删除(DELETE)
4.1 结果集ResultSet
查询结果集ResultSet
使用SELECT语句查询时,MySQL返回多行,称为结果集
多行数据
SQLtog<----------------- MySQL
客户端------------------> 服务器
Select id,name FROM student
返回指定列
返回所有列
SELECT * FROM student //*表示全部
返回部分列
SELECT id,name FROM student
更改列标签Column Label(或称列名)
可以手工指定结果集的列名
SELECT id AS`学号`,`name`AS`姓名`FROM student;
排序使用ORDER BY字句,可以让结果排序
SELECT * FROM student WHERE sex='1' ORDER BY id ASC,birthday DESC
其中,ASC表示升序,DESC表示降序.
4.2 高级查询
范围查询 使用IN可以指定一个范围
SELECT * FROM student WHERE id IN('20180001','20180003','20180005');
使用IN指定一个列表,凡在此列表中的则符合条件,
(注:NOT IN 表示不在列表里.)
空值查询
示例:SELECT * FROM student WHERE phone IS NULL
SELECT * FROM student WHERE phone IS NOT NULL
相似查询
对于字符串字段,可以使用精准查询或者相似查询,
精准查询:where name ='张良'
相似查询
WHERE name LIKE'%良%'
WHERE name LIKE'张%'
WHERE name LIKE'张%良'
注:对于Oracle,SQL Server统配符可能不是%
使用函数
对于字符串段,可以使用精确查询或相似查询
比如:
SELECT * FROM student WHERE YEAR(birthday)>=1980 AND YEAR(birthday)<1990;
其中YEAR(birthday),就是MySQL内嵌的函数,可以求日期字段的年份.
MySQL有几十个小函数,可以用于对字段或结果集进行简单处理,
比如: SELECT MAX(id) FROM student //取集合里的最大ID
注:Oracle,MySQL,SQL Server各自支持的函数不一样,不通用.
5.1 表的设计
介绍基础技术.
命名规范:
数据库名:全小写,用下划线链接
推荐为company_project
表名:全小写,用下划线连接
user_role student_exam
列名:第一个词小写,后面首字母大写
displayName , numView
表的规模
一个数据库里,通常有很多表,但每张表的特点是不一样的.
表的规模:该表的最终存储规模
有的表只能存储几百条记录,而有的达到几万条
结论:只有存储规模较大的表才值得精打细算.
表的访问
每个表访问频率不同
有的很少被访问,有的访问频繁.
例如:
商品表,可能会经常用于查询
系统日志表记录了系统事件,很少被访问.
结论:当一个表经常被访问时,要精心设计.
合适的数据类型
tinyint 比 int可能有的好处
-节省存储空间(如果表的规模太小,则忽略)
-加快传输效率(如果MySQL同机部署,则忽略)
-加快数据备份的速度(如果数据备份不常发生,则忽略)
5.2 主键和索引
主键:快速定位,保证唯一性
设计时注意两点:
1.每个表都应该设立主键
2.主键字段一般用整数或短字符串
索引Index
只能从前往后遍历,为了加快日期查询速度,可以建立`生日`索引
(1).索引里存储生日和行指针
(2).索引里按顺序存储.
添加索引
ALTER TABLE `af_school`.`student` ADD INDEX (`birthday`);
在建立索引时,可以额外指定类型
-普通索引:默认索引类型
-唯一索引 Unique:索引里的值不允许重复 ALTER TABLE `af_school`.`student` ADD UNIQUE INDEX (`phone`);
-主索引 Primary:主键也是一种索引
-全文索引 FullText: 为长文本建立的索引
在执行增删改操作时,除了要修改主表,还要同步修改索引,因此索引不应无节制的添加.
5.3表的关联
已经有一张表student,存储学生信息
现在添加一张表,表示每个学生所考试成绩
在逻辑上,表exam和student有关联性,
exam.id和student.id一一对应
所有,exam表中不需要存储name等字段.
关联查询
SELECT`name`,`chinese`,`english`,`math`,`phone` FROM exam,student WHERE exam.`id`=student.`id`
两个关联表所有列全部查询 SELECT * FROM exam,student WHERE exam.`id`=student.`id` //id会输出2次.
SELECT b.`name`,a.`chinese`,a.`english`,a.`math`,`phone`
FROM exam a,student b
WHERE a.`id`=b.`id`
设置exam为a,student为b.方便查询.
5.4 内连接
多表关联查询,有以下几种方式:
-内连接 INNER JOIN
-外连接
左外连接LEFT OUTER JOIN
有外连接RIGHT OUTER JOIN
-全连接,交叉连接...
内连接
示例:
SELECT *
FROM student a INNER JOIN exam b
ON a.id =b.id
WHERE a.sex=0
其中,ON表示连接条件.
内连接INNER JOIN 内连接等效于5.3的WHERE查询
SELECT * FROM student a,exam b WHERE a.id=b.id AND a.sex=0;
SELECT a.`name`,b. *
FROM student a INNER JOIN `leave_event` b
ON a.`id`=b.`stuId`;
性能问题
1.谁JOIN谁有什么区别.
a JOIN b
b JOIN a
-不用管,MySQL会自己进行优化. (驱动表和被驱动表,小表驱动大表)
2.连接查询比单表查询要慢.
5.5 外连接.
OUTER JOIN
SELECT *
FROM student a LEFT OUTER JOIN exam b
ON a.`id`=b.`id`;
左外连接表示以左边表为基准,右边没有的项补为null
如果左边少项,右边多,则输出右表多的项不输出.
5.6 外键
外键 FOREIGN KEY ,是一种约束条件.保证逻辑上的完整性.
例如:
有student学生表 学生信息,
有exam 表示学生成绩,以学号字段进行关联
现在,在学生表里删除一条学生的记录,可以吗.
显然不管外部约束,盲目删除student表的数据,会导致整个系统数据不完整.
修改exam表,添加外键约束.
然后从student删除一条数据.
外键作为约束条件,用于维护数据的完整性.
删除选项 cascade 级联设置,在student表中删除一项,在exam自动删除.
//外键不能同时多表关联一个主表的同一字段.
6.1 用户与授权
用户与密码
默认的,MySQL自带root用户,而且密码为空
系统表mysql.user
记录了用户名密码.
host:允许从那台主机登录(客户端IP限制)
User:用户名
Password:密码
权限:select,insert,create.
注:这个表时联合主键host+user
1 设置用户授权
GRANT命令的语法：
GRANT
ALL PRIVILEGES
ON 库名.表名
TO '用户名' @ 'IP地址'
IDENTIFIED BY '密码'
WITH GRANT OPTION ;
其中，
all privileges 表示授予所有权限，其实可以把各项权限单列出来
on 指定该用户可以访问的数据库/表
to 指定用户名、和客户端IP范围
identified by 指定密码
后面还可以分别指定增删改查权限
FLUSH PRIVILEGES; /* 运行此命令则立即生效，不需要重启服务器 */
2 修改密码
UPDATE mysql.user
SET `password`=PASSWORD('a1b2c3')
WHERE `Host`='127.0.0.1' AND `User`='root'
其中，PASSWORD是一个MySQL的函数，用于把字符串加密成密码字符串
3 允许 root远程访问
运行以下两行，即允许客户端从任意IP来访问服务器
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'a1b2c3' WITH GRANT OPTION;
FLUSH PRIVILEGES;
其中，'root'@'%' 是设置这两个字段: Host='%' User='root' host%表示可以从任意IP地址访问.
其实也设置为一个子网范围，如 'root'@'192.168.8.%' 则允许root从IP地址为 192.168.8.%的子网来访问。
4 立即生效
所有的用户、密码、授权操作不会立即生效，而是在重启MySQL服务器后才会生效。
如果想立即生效，则运行
FLUSH PRIVILEGES;
注意事项
1.数据库应该设置一个密码.
2.用户指访问数据库的用户,并非业务系统里的用户.
3.MySQL一般不开放远程访问,一般限制为内网访问,否则容易受攻击
4.所有,一般开放'root'@'机户内网网段'就够了.
6.2 允许远程访问
指从另外一台电脑登录访问MySQL服务器
服务器配置
1.MySQL里,允许'root'@'%'访问
注意:先不要设为'root'@'192.168.1.%'
2.关闭防火墙
注:MySQL的端口为3306,但为了简单起见,先统一关闭.
6.3 服务器配置
修改my.ini里的配置项,
一般不需要修改.
6.4 备份与还原
设想,如果有一天,MySQL主机发生了物理损坏.如果数据丢失怎么办
-定期备份
-如果出现灾害,重建服务器,并导入数据
MySQL自带的两个工具
mysqldump.exe 用于备份
mysql.exe 用于还原
1.切换到mysql的bin目录
cd /d d:\mysql5\bin
2.用mysqldump.exe导出
mysqldump.exe -hlocalhost -uroot -pa1b2c3 --databases af_school >d:\backup.sql
其中,主机localhost 用户名root 密码a1b2c3 数据库af_school 导出到d:\backup.sql
3.导入SQL
mysql.exe -hlocalhost -uroot -pa1b2c3 <d:\backup.sql
7.1 Java数据库连接(JDBC)
JDBC java database connectivity
使用java技术进行数据库连接的技术
JDBC API:java.sql.*
里面规定了各种数据库服务器操作的标准接口.
(另外需要一个相应的驱动jar包)
public class Test
{
public static void testConnect() throws Exception
{
// 注册MySQL驱动 (可以省略这一步)
Class.forName("com.mysql.jdbc.Driver");
// 连接MySQL服务器
String username= "cqcrh";
String password = "cqkill";
String connectionUrl = "jdbc:mysql://127.0.0.1:3306/af_school?useUnicode=true&characterEncoding=UTF-8";
Connection conn = DriverManager.getConnection(connectionUrl, username, password);
System.out.println("连接成功!");
conn.close();
System.out.println("关闭连接!");
}
public static void main(String[] args)
{
try
{
testConnect();
} catch (Exception e)
{
e.printStackTrace();
}
}
}
1.java.sql.*下规定了标准接口,而MySQL驱动里则是各个接口的实现.
2.MySQL驱动的内部封装了与服务器的交互协议
3.Class.forName("com.mysql.jdbc.Driver");
这一行用于显示注册MySQL驱动,但是可以省略,后面专门讲解.
7.2 JDBC查询数据.
连接见7.1
//数据库查询,Statement语句 ResultSet结果集
Statement stmt = conn.createStatement();
ResultSet rs=stmt.executeQuery("SELECT * FROM student");
//如果有数据,rs.next()返回值为true
while(rs.next())
{
//输出行数据
System.out.println(rs.getInt("id")+ "\t"+rs.getString("name")+"\t"+rs.getString("phone"));
}
ResultSet里面存储了结果集,while只是遍历而已.
7.3 JDBC插入数据
String sql ="INSERT INTO student(`id`,`name`,`birthday`)"
+"VALUES('20180100','rhc','1999-4-19')";
System.out.println("SQL"+sql);
Statement stmt =conn.createStatement();
stmt.execute(sql);
int count =stmt.getUpdateCount();
System.out.println("受影响的行为"+count);
当主键设为自增时.
1.插入数据时,不写该字段
2.执行时指定RETURN_GENERATED_KEYS
3.取出返回的自增主键.
String sql="INSERT INTO leave_event(`stuId`,`daysFrom`)"+"VALUES('20181234','2018-1-2')";
Statement stmt =conn.createStatement();
stmt.execute(sql,Statement.RETURN_GENERATED_KEYS); //statement.RETURN_GENERATED_KEYS 返回自增主键.
//取得自动生成的主键的值
ResultSet rs=stmt.getGeneratedKeys();
while(rs.next())
{
int id=rs.getInt(1);
System.out.println("产生的主键为"+id);
}
delete和update用法相同,修改sql语句就行额.
7.4 SQL语句的构造
最常出错的地方就是SQL语句的构造.
在SQLyog里写可以.
可以写一个工具类来实现.
7.5 预处理查询
实现连接
//1.构造一个SQL,参数值用?号代替,称为占位符
String sql ="INSERT INTO student (id,name,birthday) VALUES(?,?,?)";
//2.创建PreparedStatement对象(于MySQL产生一次交互)
PreparedStatement ptmt = conn.prepareStatement(sql);
//3.设置参数值
ptmt.setInt(1, 20183001);
ptmt.setString(2,"星星");
ptmt.setString(3, "1991-12-1");
//4.执行查询
ptmt.execute();
分为两步,第一步发送sql,第二步发送值.
预处理的优点.
使用预处理查询处理步骤:
-mySQL接收到预处理的SQL
-解析SQL请求
-服务器收到参数的值
-执行查询
-返回结果
在连续执行多个结构相同,参数不同的查询时,使用预处理技术有性能优势.
String sql=....
PreparedStatement ptmt=...
while(...)
{
ptmt.set(1,..)
ptmt.set(2,...)
ptmt.execute();
}
在解析结构时只用执行一次.先传结构再传数据.
并不是所有的服务器都支持预处理技术.
8.1 结果集的分析.
结果集JDBC返回的一个结果集
包括2部分信息:
-多行数据
-描述信息,即元数据MetaData
从结果集里面,可以读取每一行数据
Result rs =stmt.executeQuery(sql);
while(rs.next())
{
}
*结果集里的数据都是字符串,都可以用getString来获取.
*可以按列标题,也可以按列序号,列序号从1开始,1,2,3...
//前面连接数据库语句
//SQL语句数据库查询,Statement语句,ResultSet结果集
String sql="SELECT id AS 学号,name AS 姓名,birthday FROM student";
Statement stmt = conn.createStatement();
ResultSet rs =stmt.executeQuery(sql);
//设置id为学号,name为姓名.用getString只能获取学号,姓名,不能是id,name了.
//显示结果集数据
while(rs.next())
{
String id = rs.getString("学号");
String name =rs.getString("姓名");
String birthday =rs.getString("birthday");
System.out.println(id+"\t"+name+"\t"+birthday);
}
获取元数据
结果集的描述信息,即:有多个列,列名/列标题,列类型...
代码
//前面连接数据库语句
//SQL语句数据库查询,Statement语句,ResultSet结果集
String sql="SELECT id AS 学号,name AS 姓名,birthday FROM student";
Statement stmt = conn.createStatement();
ResultSet rs =stmt.executeQuery(sql);
//显示元数据信息
ResultSetMetaData rsmd =rs.getMetaData();
int numColumns = rsmd.getColumnCount();
for(int i=1;i<=numColumns;i++) //序号1,2,3,4...
{
String name =rsmd.getColumnName(i); //原始列名
String lable =rsmd.getColumnLabel(i); //别名 AS
int type = rsmd.getColumnType(i); //类型,参数,java.sql.Types定义
String typeName =rsmd.getColumnTypeName(i); //类型名称
System.out.printf("第%d列:%s,%s,%s(%d) \n",i,name,lable,typeName,type);
}
8.2 POJO对象.
POJO, Plain Ordinary Java Object
简单Java对象
-属性
-构造方法
-Getter/Setter
示例:创建一个Student类,表示学生信息.
在数据库查询到结果后转换成POJO.
例如:从af_school_student表中查询结果,转成List<Student>对象...
public class AfSqlConnection
{
//
public String ip; // 服务器IP
public int port = 3306; // 服务器端口
public String catalog; // 数据库名 Catalog
public String username;
public String password;
//
Connection conn;
public AfSqlConnection(String ip, int port,
String catalog,
String username,
String password)
{
this.ip = ip;
this.port = port;
this.catalog = catalog;
this.username = username;
this.password = password;
}
// 连接数据库
public void connect () throws Exception
{
// 示例 jdbc:mysql://127.0.0.1:3306/af_school?useUnicode=true&characterEncoding=UTF-8
String urlfmt = "jdbc:mysql://%s:%d/%s?useUnicode=true&characterEncoding=UTF-8";
String connectionUrl = String.format(urlfmt, ip, port, catalog);
conn = DriverManager.getConnection(connectionUrl, username, password);
}
// 关闭连接
public void close()
{
try {
conn.close();
}catch(Exception e) {
}
}
// 执行查询
public ResultSet executeQuery(String sql) throws Exception
{
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
return rs;
}
}
设计一个sqlConnection工具类,
使用工具类,实现添加POJO对象.
public class Test2
{
public static void testQuery() throws Exception
{
AfSqlConnection conn = new AfSqlConnection("127.0.0.1", 3306,
"af_school","root", "cqkill000");
conn.connect();
System.out.println("连接成功!");
///////////////////////////////////////////////
// 数据库查询, Statement语句 ResultSet结果集
String sql = "SELECT id , name ,sex, phone, birthday FROM student";
ResultSet rs = conn.executeQuery(sql);
// POJO对象列表
List<Student> rows = new ArrayList<>();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
// 显示结果集的数据
while(rs.next())
{
// 取出这一行记录
String id = rs.getString("id");
String name = rs.getString("name");
String sex = rs.getString("sex");
String phone = rs.getString("phone");
String birthday = rs.getString("birthday");
// 转成POJO对象
Student s = new Student();
s.setId( Integer.valueOf( id ));
s.setName( name );
if(phone!=null) s.setPhone(phone);
if(sex!=null)
{
s.setSex( sex.equals("1"));
}
if(birthday!=null)
{
s.setBirthday(sdf.parse(birthday) );
}
rows.add(s);
}
//////////////////////////////////////////////
conn.close();
System.out.println("关闭连接!");
for(int i=0;i<rows.size();i++)
{
Student s =rows.get(i);
System.out.println(s); //重写了Student的toString方法.
}
}
public static void main(String[] args)
{
try
{
testQuery();
} catch (Exception e)
{
e.printStackTrace();
}
}
}
8.3 POJO的自动映射 //重学.
public static void testQuery() throws Exception
{
AfSqlConnection conn = new AfSqlConnection("127.0.0.1", 3306,
"af_school","root", "a1b2c3");
conn.connect();
System.out.println("连接成功!");
///////////////////////////////////////////////
// SQL查询并自动映射为POJO
String s1 = "SELECT id , name ,sex, phone, birthday FROM student";
List<Student> students = conn.executeQuery(s1, Student.class);
//.class方法属于java基础类,用于描述一个class对象.
String s2 = "SELECT * FROM exam";
List<Exam> exams = conn.executeQuery(s2, Exam.class);
//////////////////////////////////////////////
conn.close();
System.out.println("关闭连接!");
}
实现原理:相关源码AfSqlConnection,AfSqlReflect
1.从ResultSet里取得每列的label和value
2.根据label,得到POJO里的setter
例如:从`birthday`->setBirthday()
3.利用反射技术,调用setBirthday()
POJO注意事项.
-属性使用包装类型,即大写的Integer ,Long,Boolean(以便应对数据库里的NULL)
-属性名应该与数据库名对应
-所有属性加上set get方法.
8.4 POJO的手动映射
POJO<-->table
自动映射:自动将列名和类的属性一一对应.
手动映射:通过额外的设置,描述POJO与table的对应关系.
通常有2种方法,XML,或者注解,
POJO另外找方法学习.
9.1 MySQL连接数.
MySQL连接数:指客户端发起的网络连接数量.
查看最大连接数(my.ini中设置)
SHOW VARIABLES LIKE 'max_connections'
查看当前连接
SHOW STATUS LIKE 'Threads%' //连接数量
SHOW processlist //连接详情
SHOW full processlist
JDBC connection
DriverManager.getConnection(connectionUrl, username, password);
//JDBC连接的方法
.colse //JDBC断开连接.
1创建Connection时,MySQL连接数+1
2关闭Connection时,连接数-1
jdbc驱动为我们创建的Connection对象对应了一个网络连接(socket连接)
思考:如果JDBC操作完不关闭连接,会如何.
9.2 连接池
有两种方式(短连接/长连接)
-每次创建一个连接,查询完毕后断开连接.
-只保持一个连接,每次都使用这个连接查询.
短连接方式
使用短连接方式,效率低
(连接耗时几百毫秒,响应速度慢.)
使用System.currentTimeMills()记录时间.
连接SQL服务器是一个重量级操作.
长连接方式
一个连接上,执行多个人次的操作.
(连接一直存在,用完了不关闭)
问题:同一时间只能满足一路查询,不能支持并发.
连接池 Connection Pool
连接池时一种常用的提高MySQL使用率的技术
设置思路:
-保持M个长连接
-当需要访问MySQL时,从中取了一个空闲的连接,用这个连接进行SQL操作
另外,内部会设置一个最大连接数限制,避免无限制开连接.
C3P0连接池.
C3P0是一个第三方库,里面实现了连接池的设计策略
www.mchange.com/projects/c3p0
9.3 C3P0连接池
C3P0 第三方库,
内部实现了连接池技术.
package my;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class SchoolDB
{
// 全局对象
static ComboPooledDataSource cpds = new ComboPooledDataSource();
public void testQuery() throws Exception
{
// 一个Connection代表一次访问
Connection conn = cpds.getConnection();
// 查询操作
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM student");
while(rs.next())
{
// 取出这一行记录
int id = rs.getInt("id");
String name = rs.getString("name");
String phone = rs.getString("phone"); // 可能为null
Date birthday = rs.getDate("birthday");
System.out.println(id + "\t" + name + "\t" + phone );
}
conn.close();// 连接放回池子
}
///////////////////////////////
public static void main(String[] args)
{
try
{
SchoolDB db = new SchoolDB();
db.testQuery();
db.testQuery();
} catch (Exception e)
{
e.printStackTrace();
}
}
}
c3p0的实际使用.
c3p0-config.xml的配置 //必须使用c3p0-config.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://127.0.0.1/af_school</property>
<property name="user">root</property>
<property name="password">cqkill000</property>
<property name="initialPoolSize">5</property>
<property name="minPoolSize">2</property>
<property name="maxPoolSize">10</property>
<property name="maxIdleTime">30</property>
</default-config>
</c3p0-config>
第一次使用时:内部初始化连接池,需要一定实际.
-connection.close()没有关闭连接,只是返回连接池.
9.4 c3p0集成到afsql.
比如集成到afsql,mybatis,hibernate.
只需要将原来的DriverManager.getConnection(connectionUrl, username, password); 改为
static ComboPooledDataSource cpds = new ComboPooledDataSource();
Connection conn = cpds.getConnection();
常用的连接池 Proxool c3p0 DBCP
10.1 字符串转义
问题:使用SQL插入一行,如果字符串含有单引号.
INSERT INTO `book` VALUES('小王','美丽'人'生'); //人字用单引号引上.
在SQL中加入\
INSERT INTO `book` VALUES('小王','美丽\'人\'生'); //在单引号前加入\
单引号需要转义
反斜杠需要转义
不同数据库对转义的规定定义可能不一样,但是原理是一样的
10.2 SQL注入的问题
一种利用程序漏洞恶意攻击.
系统登录的校验
例如:登录系统时.
-用户输入:用户名,密码
-后台从数据库校验,如果用户名密码匹配,则登录入系统,否则提示登录失败.
SQL注入手段攻击系统
String username = "a' OR '1'='1";
String password = "b' OR '1'='1";
a' OR '1'='1 使用SQL语句判断后半部分 1=1,达成登录条件.
SQL注入预防.
可见,SQL语句拼写不规范时,可能受到攻击:
所以:
1.总是给值字段加上单引号
2.总是对值进行转义.
即使用规范的SQL语法,不会受到SQL注入攻击.
11.1 获取表的结构
information_schema.中描述了数据库,表,列的信息
数据库:schemate , 表:tables ,列:columns
例如:访问tables表
SELECT * FROM `tables` WHERE tbale_schemate='af_school';
访问columns表
SELECT * FROM `tables` WHERE tbale_schemate='af_school' AND table_name='student';
用代码获取.
public class Test
{
// schemaName : 数据库名
// 功能: 传入数据库名，自动查询下面有多个表
public static void showTables(String schemaName) throws Exception
{
// 连接到information_schema库
String username= "root";
String password = "a1b2c3";
String connectionUrl = "jdbc:mysql://127.0.0.1:3306/information_schema?useUnicode=true&characterEncoding=UTF-8";
Connection conn = DriverManager.getConnection(connectionUrl, username, password);
System.out.println("连接成功!");
///////////////////////////////////////////////
// 拼凑SQL
AfSqlWhere where = new AfSqlWhere();
where.add2("TABLE_SCHEMA", schemaName);
String sql = "SELECT `TABLE_NAME` FROM `TABLES` " + where;
System.out.println("SQL: " + sql);
// 执行SQL
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql) ;
// 显示结果
while(rs.next())
{
String tableName = rs.getString("TABLE_NAME");
System.out.println("表: " + tableName );
}
//////////////////////////////////////////////
conn.close();
System.out.println("关闭连接!");
}
// schemaName : 数据库名
// 功能: 传入数据库名，自动查询下面有多个表
public static void showColumns(String schemaName, String tableName) throws Exception
{
// 连接到information_schema库
String username= "root";
String password = "a1b2c3";
String connectionUrl = "jdbc:mysql://127.0.0.1:3306/information_schema?useUnicode=true&characterEncoding=UTF-8";
Connection conn = DriverManager.getConnection(connectionUrl, username, password);
System.out.println("连接成功!");
///////////////////////////////////////////////
// 拼凑SQL
AfSqlWhere where = new AfSqlWhere();
where.add2("TABLE_SCHEMA", schemaName);
where.add2("TABLE_NAME", tableName);
String sql = "SELECT `COLUMN_NAME`,`DATA_TYPE` FROM `COLUMNS` " + where;
System.out.println("SQL: " + sql);
// 执行SQL
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql) ;
// 显示结果
while(rs.next())
{
String columnName = rs.getString("COLUMN_NAME");
String dataType = rs.getString("DATA_TYPE");
System.out.println("列名: " + columnName + ", \t类型:" + dataType );
}
//////////////////////////////////////////////
conn.close();
System.out.println("关闭连接!");
}
public static void main(String[] args)
{
try
{
// showTables("af_school");
showColumns("af_school", "student");
} catch (Exception e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
绝大多数系统中,表的结构都是事先设计好的,中间不会发生变化,也不会再程序里创建表.
所以本章不是很重要.
11.2 自动生成POJO类
在information_schema中描述了数据库,表,列的信息.从而可以自动生成POJO类
=>数据库名
=>自动获取所以table信息
=>自动获取每个table下的列名和列类型
=>有了表名,列名+列类型,自动创建一个POJO类.
13.1 SQLite
SQLite特点
1.所以表数据存在一个独立的文件里
2.本地访问,不需要安装服务器
3.数据量10万条以下
4.直接使用SQL语句
应用场景
1.嵌入式场景 例如:安卓app里可以使用
2.桌面单机程序.例如JavaFX可以用他存储一些本地数据.
和JSON的区别:SQLite时关系形数据库,支持SQL语句的增删改查.
