数据库对库、表、数据处理指令
命令都以“；”结束。
| 数据库操作: 创建： create database 数据库名称 default character utf8; 删除： drop database 数据库名称; 修改编码格式： alter database 数据库名称 default character gbk; 查询数据库字符集： show create database 数据库名称; 查询所有数据库：show databases |
| 数据表操作（在一个数据库上）： 创建： create table 表名(字段名1 字段类型,字段名2 字段类型......); 删除： drop table 表名; 修改： 添加字段： alter table 表名 add [column] 字段名 字段类型; //column可以省略不写 删除字段: alter table 表名 drop [column] 字段名; 修改字段类型： alter table 表名 modify 字段名 新的字段类型; 修改字段名称 ： alter table 表名 change 旧字段名 新字段名 字段类型; 修改表名称： alter table 旧表名 rename [to] 新表名; 查询： show tables （所有表） / desc 表名（一个表）; |
| 数据操作： 增加： 插入部分字段：insert into 表名(字段1，字段2,。。。) values(值1，值2.。。。。); 插入全部字段：insert into 表名values(值1，值2.。。。。);//按照表字段顺序写。
带条件删除： delete from 表名 where 条件; 带条件修改： update 表名 set 字段1=值1，字段2=值2...... where 条件; 查询： 4.1）所有字段（列）： select * from 表名 4.2）指定字段（列）： select 字段1,字段2.... from 表名; 4.3)指定字段取别名： select 字段1 as 别名 from 表名; 4.4 )合并列： select (字段1+字段2) from 表名; 4.5)去重： select distinct 字段 from 表名;//该字段没有重复的。 4.6)条件查询： a)逻辑条件 ：and(与) or（或） SELECT * FROM student WHERE id=2 AND NAME='李四'; -- 交集 b）比较条件： > < >= <= = <> between and(在。。。之间) select * from 表名 where servlet>=90; c）判空条件： 判断null： is null / is not null 判断空字符串： ='' / <>'' d）模糊条件： like %: 替换任意个字符 _: 替换一个字符 SELECT * FROM student WHERE NAME LIKE '李%';查询姓李的人 4.7 分页查询：limit 起始行,查询行数。起始行从0开始 查询第1,2条记录（第1页的数据） SELECT * FROM student LIMIT 0,2; 4.8 排序： order by 字段 asc/desc asc: 正序，顺序（默认） desc：反序，倒序 SELECT * FROM student ORDER BY id ASC; 4.9 分组查询：group by 字段 4.10: 分组后筛选： having 条件 SELECT gender,COUNT(*) FROM student WHERE GROUP BY gender HAVING COUNT(*)>2; 4.11聚合查询：常用的聚合函数： sum() avg() max() min() count() SELECT SUM(servlet) AS 'servlet的总成绩' FROM student; |
2 、删除指令delete和truncate的区别
-- delete from: 1）即可以全表删除又可以带条件删除 2）只能删除表的数据，不能删除表的约束 3)使用delete from删除的数据可以回滚（事务），理解为临时删除，可以在找回来
-- truncate table:1）可以全表删除不能带条件删除 2）即可以删除表的数据，也可以删除表的约束 3）使用truncate table删除的数据不能回滚
3、数据约束
就是对表的约束，一般在建立表的时候用。
作用： 当用户对使用默认值的字段不插入值的时候，就使用默认值。 注意： 对默认值字段插入null是可以的。
作用： 限制字段必须赋值 注意： 1）非空字符必须赋值，不可以为NULL
作用： 对字段的值不能重复 注意： 1）唯一字段可以插入null (有值才唯一，对null不起作用) 2）唯一字段可以插入多个null
注意： 1）通常情况下，每张表都会设置一个主键字段。用于标记表中的每条记录的唯一性。 2）建议不要选择表的包含业务含义的字段作为主键，建议给每张表独立设计一个非业务含义的 id字段。
DELETE不能影响自增长约束，删除了0003，添加一个变成0004了 TRUNCAT可以影响自增长约束
作用：约束两种表的数据 语法：CONSTRAINT 外键名称FOREIGN KEY(副表被约束的字段) REFERENCES 主表名称(主表中参考字段) 其中，主表：约束别人的表，部门表 副表：被约束的表，员工表 外键：副表中被约束的字段称外键 参考字段：主表中，参考字段一般要唯一，所以一般为主键 出现两种表的情况： 解决数据冗余高问题： 独立出一张表 例如： 员工表 和 部门表 问题出现：在插入员工表数据的时候，员工表的部门ID字段可以随便插入！！！！！我们需要的是只有固定的id，不可以乱写id的值。这时可以使用外键约束：约束插入员工表的部门ID字段值。在员工表的部门ID字段添加一个外键约束
问题： 当有了外键约束的时候，必须先修改或删除副表中的所有关联数据，才能修改或删除主表！但是，我们希望直接修改或删除主表数据，从而影响副表数据。（改了主表中参考字段，关联的副表也改变）可以使用级联操作实现！！！ 级联修改： ON UPDATE CASCADE 级联删除： ON DELETE CASCADE 注：语法用在外键的语法上 Eg: CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE
|
3多表查询中的内连接和外连接查询
| 2.2 内连接查询：只有满足条件的结果才会显示(使用最频繁) SELECT empName,deptName -- 2）确定哪些哪些字段 FROM employee,dept -- 1）确定查询哪些表 WHERE employee.deptId=dept.id -- 3）表与表之间连接条件（员工表的deptId和部门表的id有关系）
-- 内连接的另一种语法 SELECT empName,deptName FROM employee INNER JOIN dept ON employee.deptId=dept.id; -- 使用别名 SELECT e.empName,d.deptName FROM employee e INNER JOIN dept d ON e.deptId=d.id; -- 2.2 左[外]连接查询： 使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null -- （注意： 左外连接：左表的数据一定会完成显示！） SELECT d.deptName,e.empName FROM dept d LEFT OUTER JOIN employee e --outer可以省略不写 ON d.id=e.deptId;
-- 2.3 右[外]连接查询: 使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null -- （注意： 右外连接：右表的数据一定会完成显示！） SELECT d.deptName,e.empName FROM employee e RIGHT OUTER JOIN dept d ON d.id=e.deptId;
-- 2.4 自连接查询（一个表的连接） SELECT e.empName,b.empName FROM employee e LEFT OUTER JOIN employee b ON e.bossId=b.id; |
4数据库设计的三大范式（原则）
第一范式： 要求表的每个字段必须是不可分割的独立单元
(字段name的值存放了两个名字就不符合第一范式)
eg: 表1: student : name -- 违反第一范式
鲍珀|二蛋
查询小名为二开头的就不好查。
sutdent ： name small_name --符合第一范式
鲍珀 二蛋
第二范式： 在第一范式的基础上，要求每张表只表达一个意思。即表的每个字段都和表的主键有依赖。
employee（员工）: 员工编号 员工姓名 部门名称 订单名称 --违反第二范式
员工表：员工编号 员工姓名 部门名称 -- 符合第二范式
订单表： 订单编号 订单名称
第三范式： 在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。第 三范式目的是降低数据冗余。
员工表： 员工编号（主键） 员工姓名 部门编号 部门名
--符合第二范式，违反第三范式 部门编号也可以决定部门名，导致（数据冗余高）
员工表：员工编号（主键） 员工姓名 部门编号
部门表：部门编号 部门名
--符合第三范式（降低数据冗余）
5Mysql中的变量
mysql的变量，有三种
5.1全局变量（内置变量）：mysql数据库内置的变量，大概331个 （所有连接都起作用）
--讲两个全局变量
-- 全局变量：character_set_client: mysql服务器的接收数据的编码（cmd客户端本身是gbk,而内置的变量character_set_client默认值是utf-8，输入中文时候，编码规则一样才可以识别。因此，要改一下这个值。）
-- 全局变量：character_set_results：mysql服务器输出数据的编码。（用户看到的数据）
--一般改编码格式，两个全局变量character_set_client，character_set_results都要改。
三个指令：
-- 查看所有全局变量： show variables
-- 查看某个全局变量： select @@变量名
-- 修改全局变量的值： set 变量名=新值
5.2会话变量： 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失！
-- 定义会话变量: set @变量名=值
-- 查看会话变量： select @变量名
5.3局部变量： 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失！！
declare i INT DEFAULT 1;//定义一个变量i ,int类型,默认值为1
6什么叫存储过程
6.1存储过程就是带有逻辑的sql语句（程序）
1）执行效率非常快！存储过程是在数据库的服务器端执行的！！！
2）移植性很差！不同数据库的存储过程是不能移植。
6.2创建语法：
-- 创建存储过程
DELIMITER $ -- 声明存储过程的结束符
CREATE PROCEDURE pro_test() --存储过程名称(参数列表)
BEGIN -- 开始
-- 可以写多个sql语句; -- sql语句+流程控制
SELECT * FROM employee;
END $ -- 结束 结束符，（结束符随便写什么都可以，一般用$符号表示）
-- 执行存储过程
CALL pro_test(); -- CALL 存储过程名称(参数);
6.3带参数说明
参数：
IN： 表示输入参数，可以携带数据带存储过程中
OUT： 表示输出参数，可以从存储过程中返回结果
INOUT： 表示输入输出参数，既可以输入功能，也可以输出功能
| -- 6.3.1 带有输入参数的存储过程 -- 需求：传入一个员工的id，查询员工信息 DELIMITER $ CREATE PROCEDURE pro_findById(IN eid INT) -- IN: 输入参数 BEGIN SELECT * FROM employee WHERE id=eid; END $
-- 调用 CALL pro_findById(4);
-- 6.3.2带有输出参数的存储过程 DELIMITER $ CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20)) -- OUT：输出参数 BEGIN -- 给参数赋值 SET str='helljava'; END $ 调用带参数的存储过程： CALL pro_testOut(@str);//两个作用，等价于set @str; CALL pro_testOut(@str); -- 6.3.3带有输入输出参数的存储过程 DELIMITER $ CREATE PROCEDURE pro_testInOut(INOUT n INT) -- INOUT： 输入输出参数 BEGIN SELECT n;//查看变量 SET n =500;//设置变量 END $ -- 调用 SET @n=10; CALL pro_testInOut(@n);//执行这个语句，输出n=10,（看到的），然后n=500
SELECT @n;//查看n, n等于500；
|
6.4循环条件
| BEGIN IF num=1 THEN SET str='星期一'; ELSEIF num=2 THEN END IF; END $ |
| BEGIN -- 定义一个局部变量 DECLARE i INT DEFAULT 1;//定义一个变量i ,int类型,默认值为1 DECLARE vsum INT DEFAULT 0; WHILE i<=num DO SET vsum = vsum+i; SET i=i+1; END WHILE; SET result=vsum; END $
|
7什么叫触发器
当操作了某张表时，希望同时触发一些动作/行为，可以使用触发器完成！！
添加触发器：Create trigger 触发器名称 after insert on 操作表 for each row 执行动作语句
修改触发器：Create trigger 触发器名称 after update on 操作表 for each row 执行动作语句
删除触发器Create trigger 触发器名称 after delete on 操作表 for each row 执行动作语句
| - 需求： 当向员工表插入一条记录时，希望mysql自动同时往日志表插入数据 -- 日志表 CREATE TABLE test_log( id INT PRIMARY KEY AUTO_INCREMENT, content VARCHAR(100) ) -- 创建触发器(添加) CREATE TRIGGER tri_empAdd（触发器名称） AFTER INSERT ON employee FOR EACH ROW INSERT INTO test_log(content) VALUES('员工表插入了一条记录'); //以上两句就是当往员工表插入一条记录时，日志表的content就赋值为'员工表插入了一条记录' -- 插入数据 INSERT INTO employee(id,empName,deptId) VALUES(7,'扎古斯',1);
|
8数据库事务的特性
原子性：一个事务的操作要么一起成功，要么一起失败回滚！因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。
一致性： 在一个事务执行后，数据库从一个一致性的状态转变为另一个一致性状态。比如：转账，转 2000，收也要是2000.不能多也不能少，一致性
持久性： 事务一旦提交，应该永久保存下来。即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。
隔离性： 多个事务并发过程中，希望能够相互隔离的。
多个事务在并发产生以下的现象：
- 脏读： 一个事务读到了另一个事务没有提交的数据。这是绝对要避免的。
- 不可重复读：一个事务读到了另一个事务已经提交的更新数据（update）。例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。
- 幻读： 一个事务读到了另一个事务已经提交的插入数据（insert）。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。
数据库可以设置不同的隔离级别来防止以上现象：（是否能够防止现象）
MySQL数据库为我们提供的四种隔离级别：
① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
③ Read committed (读已提交)：可避免脏读的发生。
④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。
mysql数据库事务：指令
开启事务： set autocommit=0;
提交事务： commit;
回滚事务： rollback;
9sql语句的分类
DDL语句：数据定义语言：create(创建数据库和表)、drop(删除数据库和表)、alter(修改数据库、表)
DML语句:数据操作语言：insert(添加数据)、delete（删除数据）、update（修改数据）、truncate（删除数据）
DQL语句：数据查询语言select(查询数据)、show(查询表和数据库)
10什么叫JDBC，核心类是什么
JDBC技术就是使用java代码（程序）发送sql语句的技术。数据库自己按照JDBC规范更新驱动程序，程序员只需要根据JDBC规范写代码就可以了。
核心类是在java.sql.* 和 javax.sql.*包中
|- Driver接口： 表示java驱动程序接口。所有的具体的数据库厂商要来实现此接口。
|- connect(url, properties)方法: 连接数据库的一种方法。
参数url: 连接数据库的URL
URL语法： jdbc协议:数据库子协议://主机:端口/数据库
user： 数据库的用户名
password： 数据库用户密码
|- DriverManager类： 驱动管理器类，用于管理所有注册的驱动程序
|-registerDriver(driver)方法 : 注册驱动类对象
|-Connection getConnection(url,user,password); 获取连接对象
|- Connection接口： 表示java程序和数据库的连接对象。
|- Statement createStatement() ： 创建Statement对象
|- PreparedStatement prepareStatement(String sql) 创建PreparedStatement对象
|- CallableStatement prepareCall(String sql) 创建CallableStatement对象
|- Statement接口： 用于执行静态的sql语句
|- int executeUpdate(String sql) ： 执行静态的更新sql语句（DDL，DML），返回的是行数
|- ResultSet executeQuery(String sql) ：执行的静态的查询sql语句（DQL），返回的是结果集
|-PreparedStatement接口：用于执行预编译sql语句
|- int executeUpdate() ： 执行预编译的更新sql语句（DDL，DML）
|-ResultSet executeQuery() ： 执行预编译的查询sql语句（DQL）
|-CallableStatement接口：用于执行存储过程的sql语句（call xxx）
|-ResultSet executeQuery() ： 调用存储过程的方法
|- ResultSet接口：用于封装查询出来的数据
|- boolean next() ： 将光标移动到下一行
|-getXX() : 获取列的值
预编译sql语句与静态的sql语句的区别：
预编译的sql语句的参数用？表示。静态的sql语句是一个完整的语句。
预编译：String sql = "INSERT INTO student(NAME,gender) VALUES(?,?)";
静态：String sql = "INSERT INTO student(NAME,gender) VALUES('李四','女')";
| JDBC连接数据库 //连接数据库的URL，有固定格式的：jdbc协议:数据库子协议:主机:端口/连接的数据库名 private String url = "jdbc:mysql://localhost:3306/day17"; private String user = "root";//用户名 private String password = "root";//密码 方法一： public void test1() throws Exception{ //因为Driver类里边有静态代码块，静态代码块里边有注册驱动程序，所以我们只需要加载这个Driver类就可以了。加载类是通过得到字节码对象的方式加载静态代码块，方法实现见方法三 Driver driver = new com.mysql.jdbc.Driver(); //1.注册驱动程序(可以注册多个驱动程序) DriverManager.registerDriver(driver);//Driver类的静态代码块里边有这句代码 //2.连接到具体的数据库 Connection conn = DriverManager.getConnection(url, user, password); 方法2： public void test3() throws Exception{ //1.通过得到字节码对象的方式加载静态代码块，从而注册驱动程序，也就是导类 Class.forName("com.mysql.jdbc.Driver");// //2.连接到具体的数据库 Connection conn = DriverManager.getConnection(url, user, password); } |
| 使用Statement执行DDL语句 //S1.驱动注册程序 Class.forName("com.mysql.jdbc.Driver"); //S2.获取连接对象 Connection conn = DriverManager.getConnection(url, user, password);
//S3.创建Statement Statement stmt = conn.createStatement(); //S4.准备sql String sql = "CREATE TABLE student(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),gender VARCHAR(2))"; //S5.发送sql语句，执行sql语句,得到返回结果 int count = stmt.executeUpdate(sql); //S6.输出 System.out.println("影响了"+count+"行！"); //S7.关闭连接(顺序:后打开的先关闭)关闭两个对象 stmt.close(); conn.close();
|
| 使用PreparedStatement执行sql语句 //S1.驱动注册程序 Class.forName("com.mysql.jdbc.Driver"); //S2.获取连接对象 Connection conn = DriverManager.getConnection(url, user, password); //2.准备预编译的sql String sql = "INSERT INTO student(NAME,gender) VALUES(?,?)"; //?表示一个参数的占位符
//3.执行预编译sql语句(检查语法正确不正确) stmt = conn.prepareStatement(sql);
//4.设置参数值 /** * 参数一： 参数位置 从1开始 参数二：赋值 */ stmt.setString(1, "李四"); stmt.setString(2, "男");
//5.发送参数，执行sql int count = stmt.executeUpdate();//这个方法就没有参数 System.out.println("影响了"+count+"行"); //6.关闭连接(顺序:后打开的先关闭)关闭两个对象 stmt.close(); conn.close(); |
| 使用Statement执行DQL语句（查询操作） //获取连接 conn = JdbcUtil.getConnection(); //创建Statement stmt = conn.createStatement(); //准备sql String sql = "SELECT * FROM student"; //执行sql ResultSet rs = stmt.executeQuery(sql);
//移动光标 /*boolean flag = rs.next(); if(flag){ //取出列值 //根据索引 int id = rs.getInt(1); String name = rs.getString(2); String gender = rs.getString(3); System.out.println(id+","+name+","+gender);
//列名称 int id = rs.getInt("id"); String name = rs.getString("name"); String gender = rs.getString("gender"); System.out.println(id+","+name+","+gender); }*/
//遍历结果 while(rs.next()){//表的一行一行来 int id = rs.getInt("id"); String name = rs.getString("name"); String gender = rs.getString("gender"); System.out.println(id+","+name+","+gender);
} stmt.close(); conn.close(); |
| CallableStatement执行存储过程 //获取连接 conn = JdbcUtil.getConnection(); //准备sql String sql = "CALL pro_findById2(?,?)"; //第一个？是输入参数，第二个？是输出参数
//预编译 stmt = conn.prepareCall(sql);
//设置输入参数 stmt.setInt(1, 6); //设置输出参数(注册输出参数) /** * 参数一： 参数位置 * 参数二： 存储过程中的输出参数的jdbc类型 VARCHAR(20) */ stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
//发送参数，执行 stmt.executeQuery(); //结果不是返回到结果集中，而是返回到输出参数中
//得到输出参数的值 /** * getString（）索引值： 预编译sql中的输出参数的位置 */ String result = stmt.getString(2); //getXX方法专门用于获取存储过程中的输出参数
System.out.println(result);
|
11JDBC开发步骤是什么
注册驱动程序->获得连接对象->创建执行sql语句的类->执行sql语句->关闭执行类和连接对象。
jdbc开发步骤：
1）注册驱动程序：
Class.forName("com.mysql.jdbc.Driver");
2）从驱动管理类获取连接
Connection conn = DriverManager.getConnection(url,user,password);
3）通过连接对象可以创建Statement，PreparedStatement，CallableStatement
conn.createStatement(): 执行静态sql语句
conn.preparedStatement(sql): 执行预编译的sql语句（带？号的sql）
conn.preparedCall(sql): 执行存储过程调用sql语句
4)如果是预编译的sql，继续参数赋值
stmt.setXXX(1,参数值);
5）执行sql/发送参数
Statement.executeUpdate(sql); 执行更新sql
ResultSet rs =Statement.executeQuery(sql): 执行查询sql
PreparedStatement.executeUpdate() : 执行更新sql
ResultSet rs = PreparedStatement.executeQuery(); 执行查询sql
6）处理结果集
while(rs.next()){
rs.getXXX(索引值);
rs.getXXX(列名称)
}
7)关闭资源：
ResultSet.close();
Statement.close();
Connection.close();
12比较PreparedStatement vs Statement预编译和编译
1）语法不同：PreparedStatement可以使用预编译的sql，而Statment只能使用静态的sql
2）效率不同： PreparedStatement可以使用sql缓存区，效率比Statment高
3）安全性不同： PreparedStatement可以有效防止sql注入，而Statment不能防止sql注入。
效率性：
statement每发送一个sql语句，数据库都是把sql加入缓存区中，然后执行，每次发送一个sql语句，都会检查缓冲区中是否有一模一样的，若没有，则添加。
而PreparedStatement发送一个sql语句，数据库也是把sql加入缓存区中，如果在发一个一样的，参数不一样的，就不用再添加缓存区，直接执行任务。
安全性：
SELECT * FROM users WHERE NAME='eric' OR 1=1 -- 'and password='123456';
String name = "'eric' OR 1=1 -- '";这样就会被注入，mysql数据库默认永远为真，and后边被注释掉了
13JDBC如何进行批处理
批处理：一次发送多条SQL语句
Statement的批处理：
添加批处理 void addBatch(String sql) 把sql添加到缓存区中(没有发送的)
执行批处理 int[] executeBatch() 执行批处理缓存中sql语句(发送到数据库执行)
清空批处理 void clearBatch() 清空缓存区sql语句
PreparedStatement的批处理：
void addBatch() 把参数添加到缓存区中
int[] executeBatch() 执行批处理缓存中所有参数(发送到数据库执行)
void clearBatch() 清空缓存区参数列表
| Statement的批处理： conn = JdbcUtil.getConnection(); stmt = conn.createStatement(); for(int i=1;i<=2000;i++){ //把sql添加到缓存区 stmt.addBatch("INSERT INTO student VALUES("+i+",'张三',20,'男')"); //每20条发送sql if(i%20==0){ //执行批处理命令 stmt.executeBatch(); //清空缓存区 stmt.clearBatch();
|
14 Jdbc处理大容量数据
mysql:
字符串： varchar char 65535
大文本数据： tinytext ， longtext ，text
字节： bit
大字节文件： tinyblob（255byte）， blob（64kb），MEDIUMBLOB（约16M） longblob（4GB）
oracle：
字符串： varchar2 char 65535
大文本数据： clob
字节： bit
大字节文件： blob
| 保存文本到数据库 //获取连接 conn = JdbcUtil.getConnection(); //创建PreparedStatement String sql = "INSERT INTO test1(content) VALUES(?)"; stmt =conn.prepareStatement(sql); //设置参数 /** * 参数一： 参数位置 * 参数二： 输入字符流 */ /** * 读取本地文件，返回输入字符流 */ FileReader reader = new FileReader(new File("e:/Demo1.java")); stmt.setClob(1, reader); //执行sql int count = stmt.executeUpdate(); System.out.println("影响了"+count+"行");
读取数据库 //获取连接 conn = JdbcUtil.getConnection(); String sql = "SELECT * FROM test1 where id=?"; stmt = conn.prepareStatement(sql); //设置参数 stmt.setInt(1, 2); //执行sql,返回结果集 rs = stmt.executeQuery(); if(rs.next()){ //方式一：当做字符串取出数据 /* String content = rs.getString("content"); System.out.println(content); */
//方式二：返回输入流形式 Clob clob = rs.getClob("content"); Reader reader = clob.getCharacterStream(); //写出到文件中 FileWriter writer = new FileWriter(new File("e:/Demo2.java")); char[] buf = new char[1024]; int len = 0; while( (len=reader.read(buf))!=-1){ writer.write(buf, 0, len); } //关闭流 writer.close(); reader.close();
|
15JDBC处理自增长
| /** * 使用jdbc获取自增长的值 * 数据库-- 部门表 CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) )
-- 员工表 CREATE TABLE employee( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), deptId INT, CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) )
-- 插入数据 INSERT INTO dept(NAME) VALUES('软件开发部'); -- 同时再插入员工 INSERT INTO employee(NAME,deptId) VALUES('张三',1); -- 如果是java程序插入部门和员工的时候 -- 先插入部门的时候，如何自增长的值？？？？ -- 再插入员工的时候，需要自增长产生的部门id * */ public class Demo1 {
public static void main(String[] args) { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try{ conn = JdbcUtil.getConnection(); /** * 同时插入一个部门和该部门所在的员工的数据 */ String deptSql = "insert into dept(name) values(?)"; String empSql = "insert into employee(name,deptId) values(?,?)";
/** * 如何获取自增长的生成的值? * 1）设置是否可以返回自增长的值 * RETURN_GENERATED_KEYS: 可以返回自增长生成值 * NO_GENERATED_KEYS:不能返回自增长的值 */ //预编译sql语句，可以返回自增长的值 stmt = conn.prepareStatement(deptSql,Statement.RETURN_GENERATED_KEYS); //参数赋值 stmt.setString(1, "软件维护部"); //执行插入部门的sql stmt.executeUpdate();
/** * 2）获取自增长的值 * 注意： 该ResultSet只包含了自增长的字段 */ rs = stmt.getGeneratedKeys();//返回自增长的结果集，一个表中容许有多个自增长的字段值。 int deptId = 0; if(rs.next()){ deptId = rs.getInt(1);//我们知道部门表中只有一个自增长的值，结果集中取第一个 }
stmt = conn.prepareStatement(empSql); stmt.setString(1, "李四"); stmt.setInt(2, deptId);//deptId是自增长的值， //执行插入员工的sql stmt.executeUpdate(); System.out.println("添加成功"); }catch(Exception e){ e.printStackTrace(); }finally{
} }
}
|
16JDBC事务处理
Java中的方法：
Connection.setAutoCommit(false); 开启事务
Connection .commit(); 提交事务
Connection.rollback(); 回滚事务
| //1)关闭自动提交功能，切换手动管理事务。数据库默认自动提交的。 conn.setAutoCommit(false); // 向数据库发送set autocommit=0; /** * 需求： eric转账2000元给jacky */ //从eric账户上扣除2000元 String delSql = "update account set balance=balance-2000 where name='eric'"; //向jacky账户打入2000元 String addSql = "update account set balance=balance+2000 where name='jacky'";
stmt = conn.prepareStatement(delSql); //执行 stmt.executeUpdate();
//出现异常 int i = 100/0;
stmt = conn.prepareStatement(addSql); stmt.executeUpdate();
//3)当所有sql执行完成，没有问题了，则提交事务 conn.commit(); // 向数据库发送 commit; 命令 System.out.println("转账成功！");
}catch(Exception e){ e.printStackTrace(); //2)当业务出现异常（任何一条sql执行过程中出现问题）,执行catch块，需要回滚 try { conn.rollback(); // 向数据库 发送 rollback; } catch (SQLException e1) { e1.printStackTrace(); } }finally{ JdbcUtil.close(stmt, conn); } }
}
|
17为什么使用连接池技术
之前jdbc开发的步骤： 获取连接 -> 得到Statement-> 发送sql -> 关闭连接
问题： 获取连接对象需要消耗比较多的资源，而每次操作都要重新获取新的连接对象，执行一次操作就把连接关闭，这样连接对象的使用效率并不高！！！
办法： 能不能让连接对象反复使用？？？如果可以的话，连接对象使用率提高！这就可以用连接池实现！！
连接池的作用：
1）提高Connection对象的利用率，提高执行sql的效率
2）控制java程序使用最大连接数，从而防止数据库奔溃
18C3P0连接池技术
核心类：CombopooledDataSource ds;
|
public class App {
@Test //1. 硬编码方式，使用C3P0连接池管理连接 public void testCode() throws Exception { // 创建连接池核心工具类 ComboPooledDataSource dataSource = new ComboPooledDataSource(); // 设置连接参数：url、驱动、用户密码、初始连接数、最大连接数 dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/jdbc_demo"); dataSource.setDriverClass("com.mysql.jdbc.Driver"); dataSource.setUser("root"); dataSource.setPassword("root"); dataSource.setInitialPoolSize(3); dataSource.setMaxPoolSize(6); dataSource.setMaxIdleTime(1000);
// ---> 从连接池对象中，获取连接对象 Connection con = dataSource.getConnection(); // 执行更新 con.prepareStatement("delete from admin where id=7").executeUpdate(); // 关闭 con.close();//C3P0技术已经代理过了。本质是放到连接池中 }
@Test //2. XML配置方式，使用C3P0连接池管理连接 /** * 使用xml配置文件读取c3p0的方法 * 注意： * 1）需要把一个名称为c3p0-config.xml文件放在src目录下，c3p0会自动读取这个文件。 * 2)xml配置文件中属性名称和手动设置方法名保持一致！！！！c3p0则会自动读取 */
//1)创建连接池对象
/** * 1)new ComboPooledDataSource(): 使用没有参数的构造方法，读取的是default-config的配置信息 * 2)new ComboPooledDataSource(name的属性值)： 使用有参的构造方法，读取的是name-config配置信息 */
public void testXML() throws Exception { // 创建c3p0连接池核心工具类 // 自动加载src下c3p0的配置文件【c3p0-config.xml】 ComboPooledDataSource dataSource = new ComboPooledDataSource();// 使用默认的配置 //ComboPooledDataSource cds = new ComboPooledDataSource("oracle");
// 获取连接 Connection con = dataSource.getConnection();//工具特有的方法。 // 执行更新 con.prepareStatement("delete from admin where id=5").executeUpdate(); // 关闭 con.close();
} } |
| <c3p0-config> <!-- 默认配置 --> <default-config> <!-- 连接参数 --> <property name="jdbcUrl">jdbc:mysql://localhost:3306/day18</property> <property name="user">root</property> <property name="password">root</property> <property name="driverClass">com.mysql.jdbc.Driver</property>
<!-- 连接池参数 --> <property name="initialPoolSize">5</property> <property name="maxPoolSize">12</property> <property name="checkoutTimeout">5000</property> <property name="minPoolSize">3</property>
</default-config>
<!-- 命名配置 --> <named-config name="oracle"> <!-- 连接参数 --> <property name="jdbcUrl">jdbc:oracle:thin:@localhost:1521:orcl</property> <property name="user">eric</property> <property name="password">123456</property> <property name="driverClass">oracle.jdbc.driver.OracleDriver</property>
<!-- 连接池参数 --> <property name="initialPoolSize">5</property> <property name="maxPoolSize">12</property> <property name="checkoutTimeout">5000</property> <property name="minPoolSize">3</property> </named-config>
</c3p0-config>
|
- commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。因此dbutils成为很多不喜欢hibernate的公司的首选。
DbUtils组件，
- 简化jdbc操作
- 下载组件，引入jar包 : commons-dbutils-1.6.jar
- QueryRunner:
update()方法
query()方法
实例
|-- DbUtils 关闭资源、加载驱动
|-- QueryRunner 组件的核心工具类：定义了所有的与数据库操作的方法(查询、更新)
方法：Int update(Connection conn, String sql, Object param); 执行更新带一个占位符的sql
Int update(Connection conn, String sql, Object… param); 执行更新带多个占位符的sql
Int[] batch(Connection conn, String sql, Object[][] params) 批处理
T query(Connection conn ,String sql, ResultSetHandler<T> rsh, Object... params) 查询方法
Int update( String sql, Object param);
Int update( String sql, Object… param);
Int[] batch( String sql, Object[][] params)
注意： 如果调用DbUtils组件的操作数据库方法时没有传入连接对象，那么在实例化QueryRunner对象的时候需要传入数据源对象： QueryRunner qr = new QueryRunner(ds);
DbUtils提供的封装结果的一些对象：（查询功能）
ResultSetHandler接口: 用于把结果集封装成不同类型的对象
- ResultSetHandler 接口提供了一个单独的方法：Object handle (java.sql.ResultSet .rs)。自定义
- BeanHandler类： 把结果集的第一行，封装成javabean对象(常用)
- BeanListHandler类： 把结果集的每行封装成javabean，把每个javabean放入List集合中（常用）
3) ArrayHandler, 查询返回结果记录的第一行，封装对对象数组, 即返回：Object[]
4) ArrayListHandler, 把查询的每一行都封装为对象数组，再添加到list集合中
5) ScalarHandler 查询返回结果记录的第一行的第一列。查询聚合函数(例如:count(*)) (常用)
6) MapHandler 查询返回结果的第一条记录封装为map
| 查询方法说明 public class App_query {
private Connection conn;
// 一、查询， 自定义结果集封装数据 @Test public void testQuery() throws Exception { String sql = "select * from admin where id=?"; // 获取连接 conn = JdbcUtil.getConnection(); // 创建DbUtils核心工具类对象 QueryRunner qr = new QueryRunner(); // 查询，ResultSetHandler接口 Admin admin = qr.query(conn, sql, new ResultSetHandler<Admin>() { // 如何封装一个Admin对象 public Admin handle(ResultSet rs) throws SQLException { if (rs.next()) { Admin admin = new Admin(); admin.setId(rs.getInt("id")); admin.setUserName(rs.getString("userName")); admin.setPwd(rs.getString("pwd")); return admin; } return null; }
}, 29);
// 测试 System.out.println(admin); // 关闭 conn.close();
}
// 二、查询， 使用组件提供的结果集对象封装数据， //下边的类都实现了ResultSetHandler接口 // 1）重点BeanHandler: 查询返回单个对象 @Test public void testQueryOne() throws Exception { String sql = "select * from admin where id=?"; // 获取连接 conn = JdbcUtil.getConnection(); // 创建DbUtils核心工具类对象 QueryRunner qr = new QueryRunner(); // 查询返回单个对象,BeanHandler类里边有一个构造方法，参数是class。 Admin admin = qr.query(conn, sql, new BeanHandler<Admin>(Admin.class), 29);
System.out.println(admin); conn.close(); } // 2）重点BeanListHandler: 查询返回list集合，集合元素是指定的对象 @Test public void testQueryMany() throws Exception { String sql = "select * from admin"; conn = JdbcUtil.getConnection(); QueryRunner qr = new QueryRunner(); // 查询全部数据 List<Admin> list = qr.query(conn, sql, new BeanListHandler<Admin>(Admin.class));
System.out.println(list); conn.close(); } } |
20元数据
| 数据库的元数据对象（DatabaseMetaData） 参数元数据对象（ParameterMetaData） 结果集的元数据对象(ResultSetMetaData) |
使用元数据，可以编写更通用的jdbc代码。
什么是元数据？
1）连接数据库
知道连接的哪个数据库，必须使用数据库的元数据对象（DatabaseMetaData）
2）预编译statement执行sql
insert into studnetx(xxxx) values(?,?);
预编译sql之后，知道预编译的sql有几个参数，必须使用参数元数据对象（ParameterMetaData）
3）执行查询sql，返回结果集
ResultSet rs，知道表的字段数，和字段名称，必须使用结果集的元数据对象(ResultSetMetaData)
| //获取数据库元数据对象 DatabaseMetaData dbmd = conn.getMetaData(); //指定哪个数据库 System.out.println(dbmd.getDatabaseProductName());
//数据库版本 System.out.println(dbmd.getDatabaseMajorVersion()); System.out.println(dbmd.getDatabaseMinorVersion());
//使用驱动程序的版本 System.out.println(dbmd.getDriverMajorVersion()); System.out.println(dbmd.getDriverMinorVersion()); //获取url号 System.out.println(metaData.getUserName()); System.out.println(metaData.getURL());
|
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
