第一章 命令行工具 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语句的增删改查.