Day48-JDBC(复习)、事务(复习)
JDBC(复习)
什么是JDBC:Java连接数据库
需要jar包的支持:
- java.sql
- javax.sql
- mysql-conneter-java…连接驱动(必须要导入)
实验环境搭建
CREATE TABLE users(
id INT PRIMARY KEY,
`name` VARCHAR(40),
`password` VARCHAR(40),
email VARCHAR(60),
birthday DATE
);
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(1,'小赵1号','123456','xz@xz.com','2022-09-09');
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(2,'小赵2号','123456','xz@xz.com','2022-09-09');
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(3,'小赵3号','123456','xz@xz.com','2022-09-09');
导入数据库依赖
<!--mysql的驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
JDBC固定步骤
package com.xiaozhao.test;
import java.sql.*;
/**
* @author 小龚
* @create 2022-09-09 20:26
*/
public class TestJDBC {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//配置信息
//useUnicode=true&characterEncoding=utf-8解决中文乱码
String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8&useSSL=true";
String username = "root";
String password = "123456";
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.连接数据库,代表数据库
Connection connection = DriverManager.getConnection(url,username,password);
//3.向数据库发送SQL对象Statement实现CRUD
Statement statement = connection.createStatement();
//4.编写SQL语句(根据业务,有不同的SQL)
String sql = "select * from users";
//5.换行查询SQL,返回一个ResultSet结果集
ResultSet rs = statement.executeQuery(sql);
while(rs.next()){
System.out.println("id="+rs.getObject("id"));
System.out.println("name="+rs.getObject("name"));
System.out.println("password="+rs.getObject("password"));
System.out.println("email="+rs.getObject("email"));
System.out.println("birthday="+rs.getObject("birthday"));
}
//6.关闭连接释放资源(一定要做),先开的最后关(关闭顺序与开启顺序反着来)
rs.close();
statement.close();
connection.close();
}
}
测试
id=1
name=小赵1号
password=123456
email=xz@xz.com
birthday=2022-09-09
id=2
name=小赵2号
password=123456
email=xz@xz.com
birthday=2022-09-09
id=3
name=小赵3号
password=123456
email=xz@xz.com
birthday=2022-09-09
增删改(这里以删除为例,增删改都是用update)
String sql1 = "delete from users where id =4";
//这里返回的i是int类型,指的是在表中受影响的行数
int i = statement.executeUpdate(sql1);
预编译SQL
package com.xiaozhao.test;
import java.sql.*;
/**
* @author 小龚
* @create 2022-09-09 21:35
*/
public class TestJDBC2 {
public static void main(String[] args) throws Exception{
//配置信息
//useUnicode=true&characterEncoding=utf-8解决中文乱码
String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8&useSSL=true";
String username = "root";
String password = "123456";
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.连接数据库,代表数据库
Connection connection = DriverManager.getConnection(url,username,password);
//3.编写SQL
String sql = "insert into users(id, name, password, email, birthday) VALUES (?,?,?,?,?)";
//4.预编译
PreparedStatement ps = connection.prepareStatement(sql);
//进行赋值
ps.setInt(1,4);//给第一个占位符?赋值为一
ps.setString(2,"小赵4号");
ps.setString(3,"123456");
ps.setString(4,"xz@xz.com");
ps.setDate(5,new Date(System.currentTimeMillis()));
//5.执行SQL
int i = ps.executeUpdate();
//判断i的个数,也就是修改代码的影响行数
if(i>0){
System.out.println("插入成功!");
}
//6.关闭连接释放资源(一定要做),先开的最后关(关闭顺序与开启顺序反着来)
ps.close();
connection.close();
}
}
输出
插入成功!
查看
事务(复习)
要么都成功,要么都失败!
ACID原则:保证数据的安全
开启事务
事务提交 commit()
事务回滚 rollback()
关闭事务
例子:转账
Junit单元测试
导入依赖
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
简单使用
@Test注解只有在方法上有效,只要加了这个注解的方法,就只有直接运行。
测试成功:
测试失败:
搭建一个环境
建表
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(40),
money FLOAT
);
INSERT INTO account(`name`,money) VALUES('小赵A',1000);
INSERT INTO account(`name`,money) VALUES('小赵B',1000);
INSERT INTO account(`name`,money) VALUES('小赵C',1000);
测试类
package com.xiaozhao.test;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* @author 小龚
* @create 2022-09-09 23:03
*/
public class TestJDBC3 {
@Test
public void test() throws Exception{
//配置信息
//useUnicode=true&characterEncoding=utf-8解决中文乱码
String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8&useSSL=true";
String username = "root";
String password = "123456";
Connection connection = null;
try{
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.连接数据库,代表数据库
connection = DriverManager.getConnection(url,username,password);
//3.通知数据库开启事务,false是开启,true是关闭
connection.setAutoCommit(false);
//4.编写
String sql1 = "update account set money = money-100 where id=1";
//执行语句
connection.prepareStatement(sql1).executeUpdate();
//制造错误
int i = 1/0;
//第二条语句
String sql2="update account set money = money+100 where id=2";
//执行语句
connection.prepareStatement(sql2).executeUpdate();
//提交事务
connection.commit();
System.out.println("转账成功!");//以上两条Sql语句成功了,就提交事务
}catch (Exception e){
try {
//如果出现异常,就通知数据库回滚事务
connection.rollback();
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
e.printStackTrace();
}finally {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
测试事务提交失败
表数据没有改变
测试事务提交成功
表中数据改变