视图view
视图(view)是一个虚拟表,非真实存在,其本质是根据SQL语句获取动态的数据集,并为其命名,用户使用时只需使用视图名称即可获取结果集,并可以将其当作表来使用。
数据库中只存放了视图的定义,而并没有存放视图中的数据。这些数据存放在原来的表中。
使用视图查询数据时,数据库系统会从原来的表中取出对应的数据。因此,视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变,显示在视图中的数据也会发生改变
创建视图
create view 视图名 as select语句
<视图名>:指定视图的名称。该名称在数据库中必须是唯一的,不能与其他表或视图同名。
<SELECT语句>:指定创建视图的 SELECT 语句,可用于查询多个基础表或源视图
对于创建视图中的 SELECT 语句的指定存在以下限制:
用户除了拥有 CREATE VIEW 权限外,还具有操作中涉及的基础表和其他视图的相关权限。
SELECT 语句不能引用系统或用户变量。
SELECT 语句不能包含 FROM 子句中的子查询。
SELECT 语句不能引用预处理语句参数
CREATE VIEW view_student -- 视图名
AS SELECT * FROM tb_student; -- 表名
-- 默认情况下,创建的视图和基本表的字段是一样的,也可以通过指定视图字段的名称来创建视图
查询视图
describe 视图名; 简写: desc 视图名;
查看表和视图
show full tables;
修改视图
alter view <视图名> as <select语句>
更新视图
某些视图是可更新的。也就是说,可以在UPDATE、DELETE或INSERT等语句中使用它们,以更新基表的内容。对于可更新
的视图,在视图中的行和基表中的行之间必须具有一对一的关系。如果视图包含下述结构中的任何一种,那么它就是不可
更新的:
聚合函数(SUM(), MIN(), MAX(), COUNT()等)
DISTINCT
GROUP BY
HAVING
UNION或UNION ALL
位于选择列表中的子查询
JOIN
FROM子句中的不可更新视图
WHERE子句中的子查询,引用FROM子句中的表。
视图中虽然可以更新数据,但是有很多的限制。一般情况下,最好将视图作为查询数据的虚拟表,而不要通
过视图更新数据。因为,使用视图更新数据时,如果没有全面考虑在视图中更新数据的限制,就可能会造成
数据更新失败。
重命名视图
rename view 视图名 to 新视图名;
删除视图
drop view 视图名;
删除视图时,只能删除视图的定义,不会删除数据
级联操作:cascade
级联删除和级联修改
1、创建表的时候添加外键的同时后面添加级联删除以及级联修改
create table emp(
id int primary key auto_increment,
name varchar(10),
constraint dept_emp_fk
foreign key dept_id
references deot(id)
on update cascade
on delete cascade
);
2、通过sql添加外键的同时,设置级联操作(级联修改和级联删除)
alter table 表名 add constraint 外键名称 foreign key 外键作用从表的字段名称
references 主表名(主键字段) on update cascade on delete cascade;
多表查询
笛卡尔乘积查询: 两张表没有建立连接关系,A表的记录数 * B表的记录数----等于总记录数
A表 3条记录
B表 5条件记录
不设置多张表的连接条件---->15条数据
SELECT emp.`id` '员工编号',emp.`name` '员工姓名',dept.`name` '部门名称'
FROM emp,dept;
多表查询的分析思路:
1)要查询哪张表
2)要查询指定表中的哪些字段名称
3)表和表关系问题 (需要建立连接条件)
1、内连接
隐式内连接: where 条件1 and 条件2...; 利用where语句完成条件的拼接(&&,或者and关键字)
select 字段列表 from 表名1 ,表名2 where 连接条件1 and....连接条件2....;
显示内连接: 表名1 inner join 表名2 on 连接条件
select 字段列表 from 表名1 (inner) join 表名2 on 连接条件1 and 连接条件2....
2、外连接
左外连接: left (outer) join 将左边的数据全部查询以及两张表之间的交集数据(连接条件的数据)全部查询
select 指定字段列表 from 表名1 left outer join 表名2 on 连接条件
右外连接: right (outer) join
select 指定字段列表 from 表名1 right outer join 表名2 on 连接条件
3、子查询 select 嵌套 select
情况1:利用聚合函数查询出来的单列的数据和select语句进行嵌套
SELECT t1.*, t2.* FROM emp t1, dept t2 WHERE t1.`dept_id` = t2.`id`
AND t1.`salary` = (SELECT MAX(salary) FROM emp) ;
SELECT e.name, e.gender, e.salary,e.join_date,d.name FROM emp e
LEFT JOIN dept d ON e.`dept_id` = d.`id` -- 作为两张表的连接条件
WHERE e.`salary` >= (SELECT AVG(salary) FROM emp) ;
情况2:利用子查询 in(值1,值2,值3集合数据)
SELECT e.*,d.`id` '部门编号',d.`name` '部门名称' FROMemp e, dept d
WHERE e.`dept_id` = d.`id` AND e.`dept_id` IN
(SELECT id FROM dept WHERE NAME IN('市场部','财务部') ) ;
情况3:将某张表select的结果集---当做一张虚表和其他表建立连接查询
SELECT t1.*, t2.* FROM dept t1 INNER JOIN
(SELECT * FROM emp WHERE emp.`join_date` > '2021-10-07') t2
ON t1.`id` = t2.`dept_id` ;
数据库的三大范式
-- 1NF:保证数据库表中的每一列是不能在拆分的原子数据项(单独某一列)
-- 2NF:在1NF的基础上,数据库表中每一个非主键字段必须完全依赖主键字段
-- 1)一张表示描述一件事情
-- 2) 非主键字段必须完全依赖主键字段
-- 3NF:在2NF基础上,数据库表中每一个非主键字段不能产生传递依赖(必须在2NF上满足非主键字段必须完全依赖主键字段)
-- 表中A字段依赖于B字段,B字段依赖于C字段, ---->A字段依赖于C字段(字段冗余非常大了)
数据库的事务
事务就是DBA(数据库管理员)操作多个sql的时候,可能有一条执行语句可能异常了,其他sql不执行了,就会导致出现紊乱,将整个sql(多个sql)的执行看成一个"整体",要么同时执行成功,要么同时执行失败。始终保证的数据的一致性(高并发中,读/写)
开启事物:start transaction
回滚事物:rollback
提交事物:commit
-- 插入两个账户信息
INSERT INTO account(NAME,balance) VALUES('zhangsan',1000),('lisi',1000) ;
-- zhangsan 给 lisi转账500
UPDATE account SET balance = balance - 500 WHERE id = 1 ;
-- 出问题了,这块不执行了
UPDATE account SET balance = balance + 500 WHERE id = 2 ;
UPDATE account SET balance = 1000 ;
-- 将 "转账业务"看成一个整体,
-- 开启事务
START TRANSACTION ; -- 将自动提交切换为手动提交
UPDATE account SET balance = balance - 500 WHERE id = 1 ;
-- 出问题了,这块不执行了
UPDATE account SET balance = balance + 500 WHERE id = 2 ;
-- 有问题回滚;事务回滚:撤销之前的所有操作,默认回滚到执行更新之前的数据
ROLLBACK ;
-- 没有问题,正常提交事务
COMMIT ; -- 不提交,不会永久性的更改数据
传统事务特点ACID
1、原子性:执行的某业务操作,同时操作多个sql(增删改),要么同时执行成功,要么同时执行失败
2、一致性:在实际开发中,多个线程同时去写和读的时候,数据必须一致性
3、隔离性:事务和事务之间(业务和业务之间)相互独立的, 互不影响
4、持续性:就是对增删改这些sql语句,一旦事务提交了,对数据库中表的操作是永久性,即使关机了,数据永久保存
传统事务的隔离级别
下面依次从小到大,安全性低大高,效率:从高到低;
1、读未提交:read uncommited
一个事务读取到另一个没有没有提交的事务(最不安全),出现"脏读" (最严重的问题)
2、读已提交:read committed
有效防止脏读,会出现另一种问题"不可重复读"
读取到自己本身没有提交的事务的数据前后两次不一致,本身这个事务没有提交
3、可重复读 :mysql的默认隔离级别 (repetable-read)
有效防止脏读,不可重复读,可能出现"幻读"
4、串行话:serializable
查询数据库的隔离级别
SELECT @@tx_isolation; -- mysql8.0服务器版: SELECT @@transaction_isolation
设置隔离级别
set global transaction isolation level (级别的名称) ;
JDBC
Jdbc是一个普通的Java类,是数据库厂商提供的驱动jar包,可以实现sun提供一套接口规范
java.sql.Driver:驱动接口---->驱动jar包--->实现了这个接口
java.sql.Connection:数据库的连接会话--->驱动jar包--->ConnectionImpl实现了这个接口
...
JDBC操作七大步骤
1)导入驱动包
mysql5.1的jar包---com.mysql.jdbc.Driver 驱动类
mysql8.0的jar包---com.mysql.cj.jdbc.Driver 驱动类
2)注册驱动
Class.forName("com.mysql.jdbc.Driver") ;//mysql-5.1xxxjar包
Class.forName("com.mysql.jdbc.Driver") ; 获取正在运行的这个类的字节码文件对象:
1)加载这个类会执行com.mysql.jdbc.Driver这个类的staitc静态代码块,立即注册驱动
2)为了向下兼容
本身注册驱动的写法:
jdk提供的DriverManager驱动管理类:管理jdbc服务的
public static void registerDriver(Driver driver)throws SQLException 注册驱动
参数:java.sql.Driver接口
书写代码中不需要上面的这个步骤,因为
com.mysql.jdbc.Driver 这个类里面已经注册驱动了
里面有静态代码块
static {
try {
//注册驱动
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can't register driver!");
}
}
3)准备sql语句
String sql = "insert into account(name,balance) values('文章',1000);" ;
4)获取数据库的连接对象
//jdk提供了类DriverManager:管理jdbc服务的
/**
public static Connection getConnection(String url,
String user,
String password)
throws SQLException
url:统一资源定位符
jdbc:mysql://本地默认localhost或者127.0.0.1:端口号/库名 (mysql驱动包5.1jar包这个地址不需要带参数)
mysql驱动包8.0jar包后面带参数
jdbc:mysql://本地默认localhost或者127.0.0.1:端口号/库名?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
编码格式/是否启用证书(不启用,都是默认密码登录)/时区/是否允许数据库公钥验证(默认true):验证登录的密码
端口号:3306 (mysql)
user:用户名:
password:登录mysql的密码
*/
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/myee2302_db_2",
//jdbc协议:数据库协议://域名:端口号/库名
//如果使用mysql8.0的jar包(mysql-server:安装的是8.0)此时后面带上一些参数:时区以及编码格式
//jdbc:mysql://localhost:3306/myee2302_db_2?characterEncoding=utf-8&timezone=utc
"root",
"123456");
5)通过数据库的连接对象获取执行对象
//java.sql.Connnection接口---->Statement createStatement()
Statement stmt = conn.createStatement();
6)执行对象执行sql
//java.sql.Statemnt--->通用方法:更新操作(插入/修改/删除)
//int executeUpdate(String sql)throws SQLException
int count = stmt.executeUpdate(sql);
System.out.println("影响了"+count+"行");
针对DML语句(insert into/delete/update) ,直接影响了的行数,返回值int
针对ddl语句(创建表/修改表/删除表):返回值是0
java.sql.ResultSet:获取数据表的结果集 (接口)
获取查询的多条件记录或者单条记录或者查询单行单列数据...
7)释放资源
stmt.close() ;
conn.close();
jdbc查询的结果集
//注册驱动并获取连接对象
conn = JdbcUtils.getConnection() ;
//准备sql语句
String sql = "select * from student;" ;
//通过连接对象获取执行对象,准备发送sql到数据库
stmt = conn.createStatement() ;
//执行通用的查询操作
rs = stmt.executeQuery(sql) ;
System.out.println("编号\t姓名\t\t\t年龄\t性别\t地址") ;
while(rs.next()){
//有数据,获取
int id = rs.getInt("id") ; //id名称
String name = rs.getString("name") ; //name名称
int age = rs.getInt("age") ; //age名称
String gender = rs.getString("gender") ; //gender
String address = rs.getString("address") ; //address
System.out.println(id+"\t"+name+"\t\t\t"+age+"\t"+gender+"\t"+address);
}
//列的索引值获取
int id = rs.getInt(1) ;
String name = rs.getString(2) ;
int age = rs.getInt(3) ;
String gender = rs.getString(4);
String address = rs.getString(5);
自定义jdbc工具类
//统一资源定位符
private static String url = null ;
//用户名
private static String user = null ;
//密码
private static String password = null ;
//驱动类
private static String driverClassName = null ;
//定义静态代码块
static{
try {
//1)读取src下面的jdbc.properties文件
//创建一个属性集合列表
Properties prop = new Properties() ;
//System.out.println(prop) ;
InputStream inputStream = JdbcUtils.class.getClassLoader().
getResourceAsStream("jdbc.properties");
//将字节输入流加载到属性集合列表中
prop.load(inputStream) ;
//System.out.println(prop) ;
//2)通过key获取value
driverClassName = prop.getProperty("driverClassName");
url = prop.getProperty("url");
user = prop.getProperty("user");
password = prop.getProperty("password");
//3)注册驱动
Class.forName(driverClassName) ;
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//对外私有,不让外界new
private JdbcUtils(){}
//定义公共的静态方法,返回值就是Connection
//public static Connection getConnection(String url,String user,String password){ //一会还得需要传入参数,
public static Connection getConnection(){ //一会还得需要传入参数,
Connection conn = null ;
try {
conn = DriverManager.getConnection(url,user,password) ;//url,user,password
return conn;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null ;
}
//释放资源
public static void close(ResultSet rs,Statement stmt,Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
//主要针对DDL/DML语句操作释放资源
public static void close(Statement stmt,Connection conn){
close(null,stmt,conn);
}
public static void main(String[] args) {
Connection conn = JdbcUtils.getConnection();
System.out.println(conn);
}
配置文件
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myee2302_db_2
user=root
password=123456
junit单元测试
1)导包 junit以及依赖包
2)编写测试类---编写测试方法---上面加入一个注解@Test(标记它是单元测试方法,单独运行)
3)正常使用单元测试:
junit包--提供了Assert:断言 我们的结果是否和预期结果相同--提供一些方法
断言:Assert
@Test
public void testAdd(){
//测试计算器的添加方法
Calculator calc = new Calculator() ;
int result = calc.add(10, 20); //获取到的结果
Assert.assertEquals(30,result); //参数1:预期值,参数2:结果实际值,两个是否相等,相等,则断言成功;否则断言失败
@Test:标记这个方法是单元测试方法
@Before:标记的方法:是在执行单元测试之前执行
@After:标记的方法:是在执行单元测试之后执行
public class StudentTest {
private StudentDao sd ; //声明学生接口StudentDAo
@Before
public void init(){
System.out.println("执行单元测试方法之前执行了...");
sd = new StudentDaoImpl() ;
System.out.println(sd) ;
}
@Test
//测试查询所有的功能 (测试功能是否完整)
public void testFindAll(){
System.out.println("进入单元测试方法了");
//调用功能
List<Student> list = sd.findAll();
for(Student s:list){
System.out.println(s) ;
}
}
@After
public void close(){
System.out.println("执行单元测试方法之后执行了...");
}
}
sql注入
SELECT * FROM USER WHERE username = 'adsfds' AND PASSWORD ='sdfsds 'OR'1'='1';-- 恒成立
1)sql注入
原因:sql语句的实际参数存在字符串拼接,导致语句出现漏洞;(不安全)
2)执行sql语句的非常低
每一次将写的sql发送数据库,(写一个sql,发送一个次sql,频繁的操作库),
高并发情况下,可能造成系统奔溃!
PreparedStatement预编译
//获取连接对象
Connection connection = JdbcUtils.getConnection();
//sql--参数化的sql
String sql = "update employee set name = ?,gender=?,age=?,salary=?,address=?,birthday=? where id =?" ;
//获取预编译对象
PreparedStatement ps = connection.prepareStatement(sql) ;
//参数赋值
ps.setString(1,employee.getName()) ;
ps.setString(2,employee.getGender()) ;
ps.setInt(3,employee.getAge()) ;
ps.setDouble(4,employee.getSalary()) ;
ps.setString(5,employee.getAddress()) ;
ps.setDate(6,new java.sql.Date(employee.getBirthday().getTime())) ;
ps.setInt(7,employee.getId()) ;
//执行
int count = ps.executeUpdate();
System.out.println(count) ;
Statement和PreparedStatement的区别?
1)共同点:
都是可以发送sql到数据库的,都是执行对象,后者继承前者(java.sql.包下的接口)
2)不同点:
2.1)是否会造成sql注入
Statement永远执行的是静态sql语句:语句中存在"硬编码",存在SQL的字符串拼接,就造成sql注入,不安全
PreparedStatement:永远执行的是参数化的sql语句,全部参数都是"?",占位符号,有效防止sql的拼接,预防sql注入,提高执行sql的安全性
2.2)是否会提高sql执行效率
Statement:不会提高sql执行效率,
获取执行对象,然后将sql语句发送数据库(没写一个sql,发一次),频繁的操作访问数据库
PreparedStatement:大大提高SQL执行效率
参数化sql是在获取PreparedStatement,就已经发送给数据库了,然后在PreparedStatement赋值不同的值;
数据库连接池-Druid(德鲁伊)
Druid工具类
//静态实例--->java.lang.ThreadLocal<T> 模拟线程,每一个用户都有自己的Connection
private static ThreadLocal<Connection> tl = new ThreadLocal<>() ; //里面没有连接对象
//声明一个DataSource接口类型 变量
private static DataSource ds ;
//构造方法私有化
private DruidJdbcUtils(){}
//静态代码块
static{
try {
//1)读取德鲁伊的配置文件,让德鲁伊自己封装配置文件参数
InputStream inputStream = DruidJdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties");
//创建属性集合列表
Properties prop = new Properties() ;
//加载属性集合列表
prop.load(inputStream) ;
//2)创建DruidDataSource(连接池)
ds = DruidDataSourceFactory.createDataSource(prop) ;
/* public static DataSource createDataSource(Map properties) throws Exception {
* DruidDataSource dataSource = new DruidDataSource(); //创建德鲁伊的数据源
* config(dataSource, properties); //封装数据:将配置文件的内容封装到dataSource数据源对象汇总
* return dataSource;//返回
*/
// 提供jar包--->com.alibaba.druid.pool.DruidDataSource--->本质实现了一个接口javax.sql.DataSource
// --->Connection getConnection()
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//获取DataSource数据源信息 (连接池的所有参数)
public static DataSource getDataSource(){
return ds ;
}
//从数据源(连接池) 获取连接对象
public static Connection getConnection(){
//1)线程本地线程中获取连接对象---ThreadLocal<T>--->T get():从当前线程中获取存储的内容
Connection conn = tl.get() ;
try {
//2)判断
//如果当前conn对象为null,说明当前线程中没有要操作的连接对象
if(conn==null){
//3)从数据源(连接池中)获取Connection
conn = ds.getConnection();
//4)将从数据源中获取到的conn连接对象,绑定当当前线程中
//ThreadLocal<T>--->public void set(T t):绑定
tl.set(conn) ;
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return conn ;
}
//释放资源
public static void close(ResultSet rs, PreparedStatement stmt, Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
//从当前线程解绑
tl.remove(); //ThreadLocal<T> :从线程中移出
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
//主要针对DDL/DML语句操作释放资源
public static void close(PreparedStatement stmt,Connection conn){
close(null,stmt,conn);
}
德鲁伊Druid配置文件的参数说明
#配置文件properties
#这些名称都是DruidDataSoure提供的参数
#连接数据库的驱动类
driverClassName=com.mysql.jdbc.Driver
#连接数据库的url地址:统一资源定位符
url=jdbc:mysql://localhost:3306/myee2302_db_2
#用户名
username=root
#密码
password=123456
#连接池一旦创建,初始化5个连接数量
initialSize=5
#最大连接数量值:默认值8个,自己设定值,和maxIdel:最大空闲数量相等
maxActive=10
#最大等待时间:为毫秒值,一旦连接池中创建连接对象超过了最大连接数量,等待3秒中,如果还连接不上,连接池会产生错误日志,提示"连接超时"
maxWait=3000
minIdel=最小空闲数量
maxidel=最大空闲数量
Druid应用
public boolean isLogin(User user) throws SQLException {
//获取数据库连接对象
Connection conn = JdbcUtils.getConnection();
//准备sql---参数化的sql(预编译sql语句)
//select * from user where username = 'xxx' and password= 'xxx';
String sql = "select * from user where username = ? and password = ?" ; //英文符号
System.out.println(sql) ;
//1)通过数据库的连接对象获取预编译对象:将参数化的sql发送数据库,将具体的参数类型以及第几个占位符都存在了PreparedStatement中
/**
* PreparedStatement prepareStatement(String sql)
* throws SQLException创建一个PreparedStatement对象,用于将参数化的SQL语句发送到数据库
*/
PreparedStatement stmt = conn.prepareStatement(sql) ;
//2)使用预编译对象给参数进行赋值
//1:表示第一个,2:表示第二个占位符号
//通用的方法public void setXXX(占位符号的索引值,赋值的实际参数) ;
//举例:预编译对象.setString(1,"高圆圆) ;
stmt.setString(1,user.getUsername()) ;
stmt.setString(2,user.getPassword()) ;
//3)通过预编译对象执行这些参数(在预编译对象中执行参数化的sql)
//int executeUpdate();
//ResultSet executeQuery() ;
ResultSet resultSet = stmt.executeQuery();
boolean flag = resultSet.next();
JdbcUtils.close(resultSet,stmt,conn);
return flag ;
}
Jdbc控制事务
Jdbc控制事务:
public void setAutoCommit(boolean auto) ;参数为true,表示自动提交,如果是false,手动提交
void rollback():事务回滚,撤销之前所有更改,必须释放连接对象,连接对象需要从线程中解绑
void commit():提交事务,将更改数据永久保存,提交完毕,释放连接对象,需要从线程中解绑
Connection conn = null ;
PreparedStatement ps1= null ;
PreparedStatement ps2= null ;
try {
//获取连接池中的初始化连接数量中的连接对象
conn = DruidJdbcUtils.getConnection();
//开启手动提交模式(开启事务)
//public void setAutoCommit(boolean auto) ;参数为true,表示自动提交,如果是false,手动提交
conn.setAutoCommit(false) ;
String sql1 = "update account set balance = balance - ? where id = ?" ;
String sql2 = "update account set balance = balance + ? where id = ?" ;
//发送参数化的sql获取编译对象
ps1 = conn.prepareStatement(sql1) ;
ps2 = conn.prepareStatement(sql2) ;
//参数赋值
ps1.setInt(1,500) ;
ps1.setInt(2,3) ;
ps2.setInt(1,500) ;
ps2.setInt(2,4) ;
//在预编译对象中执行
int count = ps1.executeUpdate();
int i = 10/0 ; //明显给了异常,实际开发中可能其他系统监控代码出现bug了也会造成 前面sql执行了,后面没有执行1
int count2 = ps2.executeUpdate();
// void commit():提交事务,将更改数据永久保存,提交完毕,释放连接对象,需要从线程中解绑
conn.commit(); //事务提交
} catch (SQLException throwables) {
try {
// void rollback():事务回滚,撤销之前所有更改,必须释放连接对象,连接对象需要从线程中解绑
conn.rollback();//事务回滚
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
} finally {
DruidJdbcUtils.close(ps1,conn) ;
DruidJdbcUtils.close(ps2,conn) ;
}
工具类库:commons-dbutils
commons-dbutils的使用步骤
commons-dbutils的使用步骤:
1)导包 commons-dbutils-1.6.jar
2)创建执行器 QueryRunner---> 底层PreparedStatement
public QueryRunner(DataSource ds) 参数就是数据源--->自定义工具获取到了数据源 (自动提交)
public QueryRunner():创建执行器,手动提交
3)准备好sql语句
DML语句---添加/修改/删除
insert into
update
delete from...
4)QueryRunner提供通用的更新操作:
public int update(Connection conn, String sql, Object... params) throws SQLException :手动提交
/*public int update(Connection conn, String sql, Object... params) throws SQLException {
return update(conn, (closeConn:)false, sql, params);
closeConn: 手动提交
}*/
public int update(String sql, Object... params):自动提交
/*
public int update(String sql, Object... params) throws SQLException {
Connection conn = this.prepareConnection();
return this.update(conn, true, sql, params);
}
*/
底层自动生成预编译对象并执行,并释放资源
/*
PreparedStatement stmt = null;
int rows = 0;
try {
stmt = this.prepareStatement(conn, sql);
this.fillStatement(stmt, params);
rows = stmt.executeUpdate();
} catch (SQLException e) {
this.rethrow(e, sql, params);
} finally {
close(stmt);
if (closeConn) {
close(conn);
}
}
*/
DQL语句--- 查询语句
5)QueryRunner提供的通用查询操作
public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException
第一个参数:查询的sql语句
第二个参数:ResultSetHandler结果集的处理
public interface ResultSetHandler<T> { //结果集的处理,接口,被BeanHandler等类实现
子实现类:BeanHandler---->将查询的某一条记录封装到实体了中(JavaBean:是具体类,属性私有,对外提供setXXX()/getXXX)
public BeanHandler(Class<T> type) ---> 参数针对查询的记录--封装到类名.class中
子实现类:BeanListHandler<T> ---将查询的多条记录封装到List集合中,List集合都是当前类对象
public BeanListHandler(Class<T> type)
子实现类:
ScalarHandler:通过聚合函数(count(列名称),其他max(xx),avg(xxx))查询单行单的列的数据的结果封装到Object类中
第三个参数:就是赋值的实际参数params,没有参数可以不写
注意事项:
使用commons-dbutils工具库:无论添加/删除/修改/查询数据,
必须保证实体类的属性名称和表的字段名称一一对应,否则数据封装不上去的,永远是null
如果不对应,可以在(查询语句)--->给这字段给别名(保证别名和实体类的属性名称一致)
实体类的属性名称不要出现大写字母,否则也可能是null值
query方法底层自动生成预编译对象并执行,并释放资源
public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
Connection conn = this.prepareConnection();
return this.<T>query(conn, true, sql, rsh, params);
}
PreparedStatement stmt = null;
ResultSet rs = null;
T result = null;
try {
stmt = this.prepareStatement(conn, sql);
this.fillStatement(stmt, params);
rs = this.wrap(stmt.executeQuery());
result = rsh.handle(rs);
} catch (SQLException e) {
this.rethrow(e, sql, params);
} finally {
try {
close(rs);
} finally {
close(stmt);
if (closeConn) {
close(conn);
}
}
}
Druid连接池和DBUtils工具的应用
DruidUtils工具类
//静态实例--->java.lang.ThreadLocal<T> 模拟线程,每一个用户都有自己的Connection
private static ThreadLocal<Connection> tl = new ThreadLocal<>() ; //里面没有连接对象
//声明一个DataSource接口类型 变量
private static DataSource ds ;
//构造方法私有化
private DruidJdbcUtils(){}
//静态代码块
static{
try {
//1)读取德鲁伊的配置文件,让德鲁伊自己封装配置文件参数
InputStream inputStream = DruidJdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties");
//创建属性集合列表
Properties prop = new Properties() ;
//加载属性集合列表
prop.load(inputStream) ;
//2)创建DruidDataSource(连接池)
ds = DruidDataSourceFactory.createDataSource(prop);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//获取DataSource数据源信息 (连接池的所有参数)
public static DataSource getDataSource(){
return ds ;
}
//从数据源(连接池) 获取连接对象
public static Connection getConnection(){
//1)线程本地线程中获取连接对象---ThreadLocal<T>--->T get():从当前线程中获取存储的内容
Connection conn = tl.get() ;
try {
//2)判断
//如果当前conn对象为null,说明当前线程中没有要操作的连接对象
if(conn==null){
//3)从数据源(连接池中)获取Connection
conn = ds.getConnection();
//4)将从数据源中获取到的conn连接对象,绑定当当前线程中
//ThreadLocal<T>--->public void set(T t):绑定
tl.set(conn) ;
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return conn ;
}
//释放资源,也可以不写,DBUtils工具类底层已经关闭了
public static void close(ResultSet rs, PreparedStatement stmt, Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
//从当前线程解绑
tl.remove(); //ThreadLocal<T> :从线程中移出
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
Druid+DBUtils实现CRUD
/**
* 查询所有员工
* @return 返回员工列表
*/
@Override
public List<Employee> findAll() throws SQLException {
//使用apache提供开源工库实现
//创建QueryRunner
//public QueryRunner(DataSource ds)
QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource()) ;
//sql
String sql = "select * from employee" ;
//将查询的多条记录封装List集合,集合List存储每一个员工对象
//public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params)
//子实现类:BeanListHandler<T> ---将查询的多条记录封装到List集合中,List集合都是当前类对象
List<Employee> list = qr.query(sql, new BeanListHandler<>(Employee.class));
return list;
}
/**
* 根据员工编号查询员工
* @param id 员工编号
* @return 返回员工实体
*/
@Override
public Employee findEmpById(int id) throws SQLException {
//创建执行对象
QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource()) ;
//sql
String sql = "select * from employee where id = ?" ;
//执行查询: 将某一条记录封装到实体中
/**
* 它的子实现类:BeanHandler---->将查询的某一条记录封装到实体了中(JavaBean:是具体类,属性私有,对外提供setXXX()/getXXX)
* public BeanHandler(Class<T> type) ---> 参数针对查询的记录--封装到类名.class中
*/
Employee emp = qr.query(sql, new BeanHandler<>(Employee.class), id);
return emp;
}
/**
* 查询总记录数
* @return 返回总条数
*/
@Override
public int getTotalCount() throws SQLException {
//执行对象
QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource()) ;
//sql
String sql = "select count(id) from employee" ;
//将查询的结果:当行单例的数据封装到Object中
/**
* ScalarHandler:通过聚合函数(count(列名称),其他max(xx),avg(xxx))查询单行单的列的数据
* 的结果封装到Object类中
* //public ScalarHandler()
*/
Object object = qr.query(sql, new ScalarHandler<>());
//5----取出来
String s = String.valueOf(object);
int totalCount = Integer.parseInt(s);
return totalCount;
}
/**
* 添加员工
* @param emp 员工实体
*/
@Override
public void addEmployee(Employee emp) throws SQLException {
//创建执行对象
QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource()) ;
//sql
String sql = "insert into employee(name,gender,age,salary,address,birthday) value(?,?,?,?,?,?)" ;
//public int update(String sql, Object... params):自动提交
int count = qr.update(sql,
emp.getName(),
emp.getGender(),
emp.getAge(),
emp.getSalary(),
emp.getAdderss(),
emp.getBirthday()
);
System.out.println("影响了"+count+"行");
}
/**
* 根据员工id修改员工信息
* @param emp 员工实体
* @throws SQLException
*/
@Override
public void updateEmployee(Employee emp) throws SQLException {
//创建执行对象
QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource()) ;
//sql
String sql = "update employee set name =?,gender =?,age =?,salary = ?,address=?,birthday=? where id = ?" ;
//public int update(String sql, Object... params):自动提交
int count = qr.update(sql,
emp.getName(),
emp.getGender(),
emp.getAge(),
emp.getSalary(),
emp.getAdderss(),
emp.getBirthday(),
emp.getId()
);
System.out.println("影响了"+count+"行");
}
/**
* 根据员工姓名模糊查询
* @param name 关键字员工名称
* @return 返回员工列表
*/
@Override
public List<Employee> findEmpByName(String name) throws SQLException {
//执行对象
QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource()) ;
//sql
String sql = "select name as emp_name,gender as emp_gender,age,salary,address as adderss ,birthday from employee where name like ?" ;
//查询
List<Employee> list = qr.query(sql, new BeanListHandler<>(Employee.class), name);
return list;
}
测试
private EmployeeDao ed ;
@Before
public void init(){
ed = new EmployeeDaoImpl() ;
}
//查询所有
@Test
public void testFindAll() throws SQLException {
List<Employee> all = ed.findAll();
if(all!=null){
for(Employee emp :all){
System.out.println(emp);
}
}
}
//通过id查询
@Test
public void testFindEmpById() throws SQLException {
Employee emp = ed.findEmpById(6);
System.out.println(emp);
}
//查询总记录数
@Test
public void testTotalCount() throws SQLException {
int count = ed.getTotalCount();
System.out.println(count);
}
//测试添加员工
@Test
public void testAdd() throws ParseException, SQLException {
//封装员工实体
Employee employee = new Employee() ;
//employee.setName("张三丰") ;
//employee.setGender("男") ;
employee.setAge(45) ;
employee.setSalary(6000.0) ;
employee.setAdderss("西安市") ;
String birthdayDate = "1985-10-20" ;
Date date = new SimpleDateFormat("yyyy-MM-dd").parse(birthdayDate);
employee.setBirthday(new java.sql.Date(date.getTime()));
ed.addEmployee(employee) ;
}
//测试修改
@Test
public void testUpdate() throws ParseException, SQLException {
//封装员工实体
Employee employee = new Employee() ;
employee.setId(7);
//employee.setName("张无忌") ;
//employee.setGender("男") ;
employee.setAge(20) ;
employee.setSalary(8000.0) ;
employee.setAdderss("神木市") ;
String birthdayDate = "1990-05-01" ;
Date date = new SimpleDateFormat("yyyy-MM-dd").parse(birthdayDate);
employee.setBirthday(new java.sql.Date(date.getTime()));
ed.updateEmployee(employee); ;
}
//模糊搜索
@Test
public void testFindEmpByName() throws SQLException {
List<Employee> list = ed.findEmpByName("张%");
if(list!=null){
for(Employee emp:list){
System.out.println(emp);
}
}
}