第64天学习打卡（MySQL 三大范式 JDBC ）
9.2三大范式（面试会提问）
为什么需要数据规范化
-
-
信息重复
-
更新异常
-
删除异常
- 丢失有效信息
-
插入异常
- 无法正常显示信息
-
三大范式
第一范式（1NF）
原子性：保证每一列不可再分
第二范式（2NF）
前提：满足第一范式
每张表只描述一件事情
第三范式（3NF）
前提：满足第一范式和第二范式
第三范式需要确保数据表中 的每一列数据都和主键直接相关，而不能间接相关。
（三大范式是为了规范数据库）
规范性和性能的问题
关联查询的表不得超过三张表
-
考虑商业化的需求和目标，（成本，用户体验）数据库的性能更加重要
-
在规范性能的问题的时候，需要适当的考虑一下规范性！
-
故意给某些表增加一些冗余的字段（从多表查询中变为单表查询）
-
故意增加一些计算列（从大数据量降低为小数据量的查询：索引 ）
10.JDBC(重点)
10.1 数据库驱动
驱动：声卡、显卡、数据库
我们的程序会通过数据库驱动，和数据库打交道！
10.2JDBC
SUN公司为了简化开发人员（对数据库的统一）操作，提供了一个（Java操作数据库的）规范，俗称JDBC
这些规范的实现由具体的厂商去做
对于开发人员来说。我们只需要掌握JDBC借口的操作即可！
java.sql
javax.sql
还需要导入一个数据库包 mysql-connector-java-8.0.23.jar
下载地址：https://mvnrepository.com/artifact/mysql/mysql-connector-java/8.0.23
下载地址：https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.23/
10.3第一个JDBC程序
创建测试数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
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,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04');
1 创建一个普通项目
2 导入数据库驱动
3 编写程序测试
package com.kuang.lesson01;
import java.sql.*;
// 我的第一个JDBC程序
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");//固定写法，加载驱动Class.forName("com.mysql.cj.jdbc.Driver") sql 8.0版本以上的写法
//2.用户信息和url
//useUnicode=true (设置中文编码)&characterEncoding=utf8（设定中文字符）&useSSL=true（使用安全连接）
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "123456";
//3.连接成功 数据库对象 Connection 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行SQL的对象 Statement 执行sql的对象
Statement statement = connection.createStatement();
// 5.执行SQL的对象去执行SQL ， 可能存在结果，查看返回结果
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部的查询出来的结果
while(resultSet.next()){
System.out.println("id=" + resultSet.getObject("id") );
System.out.println("name=" + resultSet.getObject("NAME") );
System.out.println("pwd=" + resultSet.getObject("PASSWORD") );
System.out.println("email=" + resultSet.getObject("email") );
System.out.println("birth=" + resultSet.getObject("birthday") );
System.out.println("=============================");
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结：
1.加载驱动
2.连接数据库DriverManager
3.获取执行的sql的对象 Statement
4.获得返回的结果集
5.释放连接
DriverManager
//1.加载驱动
//原始写法 DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver()); 使用这个语句会注册两次
Class.forName("com.mysql.cj.jdbc.Driver");//固定写法，加载驱动 （推荐使用）
Connection connection = DriverManager.getConnection(url, username, password);
// connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();
URL唯一定位符
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
//mysql -- 默认端口为3306
// jdbc:mysql:连接msq 这个是协议
// localhost:3306 主机地址:端口号
//jdbcstudy?useUnicode=true 数据库名？参数1&参数2&参数3
//oralce -- 默认端口1521
//jdbc:oracle:think@localhost:8521:Sid
Statement 执行SQL的对象 prepareStatement()执行SQL的对象
String sql = "SELECT * FROM users";//编写SQL
statement.executeQuery();//查询操作返回ResultSet(结果集)
statement.execute();//执行所有的SQL
statement.executeUpdate();//更新 插入 查询删除 都是用这个，返回一个受影响的行
ResultSet查询的结果集：封装了所有的查询结果
获得指定的数据类型
resultSet.getObject();//在不知道类型的情况下使用
//如果在知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
......
遍历，指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行
释放资源
//6.释放连接
resultSet.close();
statement.close();
connection.close();//耗资源，用完关掉
10.4 statement对象
jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法，用于向数据库发送增 、删、改的sql语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化）。
Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作，示例操作:
Statement st = conn.createStatement();
String sql = "insert into user(..)vakues(..)";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功！！");
}
CRUD操作-delete
使用executeUpdate(String sql)方法完成数据删除操作，示例操作：
Statement st = conn.createStatement();
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("删除成功!!!");
}
CRUD操作-update
使用executeUpdate(String sql)方法完成数据修改操作，示例操作：
Statement st = conn.createStatement();
String sql = "update user set name = '' where name= ''";
int num = st.executeupdate(sql);
if(num>0){
System.out.println("修改成功!!!");
}
CRUD操作-read
使用executeQuery(String sql)方法完成数据查询操作。示例操作：
Statement st = conn.createStatement();
String sql = "select * from user where id = 1";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
//根据获取列的数据结构，分别调用rs的相应方法映射到java对象中
}
代码实现
代码实现时在lesson02中进行插入测试出现的错误
No suitable driver found for "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
- 使用8.0新版,加载驱动那改成Class.forName("com.mysql.cj.jdbc.Driver")
- 必须把getConnection函数中的url改为jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&uesSSL=true
主键不能重复
1.提取工具类
package com.kuang.lesson02.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
//提升作用域
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
//获取具体的资源
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
//加载流
properties.load(in);
//获取某一个资源
//从文件里面把信息读取出来
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//1.驱动只用加载一次
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true", username, password);
}
//释放连接资源
public static void release(Connection conn, Statement st, ResultSet rs){
//顺序为倒着关闭资源
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
db.properties里面的代码：
driver=com.mysql.cj.jdbc.Driver
url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true;
username=root
password=123456
2 编写增删改的方法executeUpdate
增
package com.kuang.lesson02;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection conn =null;
Statement st = null;
ResultSet rs = null;
try {
//在try里面的变量最后要释放是释放不了的必须提到前面去
conn = JdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();//获取sql的执行对象
String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
"VALUES(4,'kuangshen','123456','24736743@qq.con','2021--03-03')";
int i = st.executeUpdate(sql);//执行sql
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
删
package com.kuang.lesson02;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection conn =null;
Statement st = null;
ResultSet rs = null;
try {
//在try里面的变量最后要释放是释放不了的必须提到前面去
conn = JdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();//获取sql的执行对象
String sql = "DELETE FROM users WHERE id = 4;";
int i = st.executeUpdate(sql);//执行sql
if(i>0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
改
package com.kuang.lesson02;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {
public static void main(String[] args) {
Connection conn =null;
Statement st = null;
ResultSet rs = null;
try {
//在try里面的变量最后要释放是释放不了的必须提到前面去
conn = JdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();//获取sql的执行对象
String sql = "UPDATE users SET `NAME` = 'kuangshen' ,email ='24736743@qq.com' WHERE id=1";
int i = st.executeUpdate(sql);//执行sql
if(i>0){
System.out.println("更新成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
3 查询executeQuery
package com.kuang.lesson02;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn= JdbcUtils.getConnection();//获取连接对象
st = conn.createStatement();//拿到statement对象
//SQL
String sql = "select * from users where id = 1";
rs = st.executeQuery(sql);//查询完毕会返回一个结果集
while(rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
SQL注入的问题
sql 存在漏洞。会被攻击导致数据泄露 SQL会被拼接 or的原因
package com.kuang.lesson02;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQLinsert {
public static void main(String[] args) {
//login("kuangshen","123456"); 正常登录
login("'or'1=1","'or'1=1");// 技巧
}
//登录业务
public static void login(String username,String password){
Connection conn =null;
Statement st = null;
ResultSet rs = null;
try {
//在try里面的变量最后要释放是释放不了的必须提到前面去
conn = JdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();//获取sql的执行对象
//SELECT * FROM users WHERE `NAME`= 'kuangshen' AND `PASSWORD`='123456';
//SELECT * FROM users WHERE `NAME`= ''or '1=1' AND `PASSWORD`='' or'1=1';
String sql = "select * from users where `NAME`= '"+username+"'AND `password`='"+password+"'";
rs = st.executeQuery(sql);//查询完毕会返回一个结果集
while(rs.next()){
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("password"));
System.out.println("======================");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.5 PreparedStatement 对象
PreparedStatement可以防止SQL注入，效率更高
1 新增
package com.kuang.lesson03;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
// 区别
//使用? 占位符代替参数
String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`)values(?,?,?,?,?)" ;
st = conn.prepareStatement(sql);//预编译SQL，先写sql,然后不执行
//手动给参数赋值 1:代表第一个问号 插入4号
st.setInt(1,4);//id
st.setString(2,"qinjiang");
st.setString(3,"1234112");
st.setString(4,"24734673@qq.com");
//注意点：sql.Date 数据库用的 java.sql.Date
//util.Date Java用的 new Date().getTime() 获得时间戳
st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));
//执行 没有直接调用sql
int i = st.executeUpdate();
if (i>0){
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
2 删除
package com.kuang.lesson03;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
// 区别
//使用? 占位符代替参数
String sql = "delete from users where id=?" ;
st = conn.prepareStatement(sql);//预编译SQL，先写sql,然后不执行
//手动给参数赋值 1:代表第一个问号 插入4号
st.setInt(1,4);
//执行 没有直接调用sql
int i = st.executeUpdate();
if (i>0){
System.out.println("删除成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
3 更新
package com.kuang.lesson03;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
// 区别
//使用? 占位符代替参数
String sql = "update users set`NAME` =? where id=?;" ;
st = conn.prepareStatement(sql);//预编译SQL，先写sql,然后不执行
//手动给参数赋值 1:代表第一个问号
st.setString(1,"狂神");
st.setInt(2,1);
//执行 没有直接调用sql
int i = st.executeUpdate();
if (i>0){
System.out.println("更新成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
4 查询
package com.kuang.lesson03;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "select * from users where id = ?";//编写SQL
st = conn.prepareStatement(sql);//预编译
st.setInt(1,1);//传递参数
rs = st.executeQuery();//执行
if (rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
5 防止SQL注入
package com.kuang.lesson03;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.*;
public class SQLinsert {
public static void main(String[] args) {
// login("lisi","123456"); //正常登录
login("'or'1=1","123456");// 技巧
}
//登录业务
public static void login(String username,String password){
Connection conn =null;
PreparedStatement st = null;
ResultSet rs = null;
try {
//在try里面的变量最后要释放是释放不了的必须提到前面去
conn = JdbcUtils.getConnection();//获取数据库连接
//PreparedStatement防止SQL注入的本质，把传递进来的参数当作字符、
//假设其中存在转移字符，就直接都忽略，比如说引号都会被直接转义
String sql = "select * from users where `NAME`= ? and `PASSWORD` = ?";
st = conn.prepareStatement(sql);//预编译
st.setString(1,username);
st.setString(2,password);
rs = st.executeQuery();//查询完毕会返回一个结果集
while(rs.next()){
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("password"));
System.out.println("======================");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.6 使用IDEA连接数据库
IDEA连接数据库报错：Server returns invalid timezone. Need to set 'serverTimezone' property.
解决办法
连接成功后，可以选择数据库
双击数据库，查看数据库资源
写SQL时的操作
/* 创建账户表*/
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);
10.7 事务
要么都成功，要么都失败
ACID原则
原子性：要么全部完成，要么都不完成
一致性：总数不变
隔离性：多个进程互不干扰
持久性：一旦提交不可逆，持久化到数据库
隔离性的问题：
脏读：一个事务读取了另外一个没有提交的事务
不可重复读：在同一个事务内，重复读取表中的数据，数据表发生了改变
虚读（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读出来的结果不一致。
代码实现
1.开启事务conn.setAutoCommit(false);
2.一组业务执行完毕，提交事务
3可以在catch语句中显示的定义回滚语句，但默认失败就会回滚。
package com.kuang.lesson04;
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction2 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//关闭数据库的自动提交,自动会开启事务
conn.setAutoCommit(false);//开启事务
String sql1 = "update account set money = money - 100 where name = 'A'";
st = conn.prepareStatement(sql1);
st.executeUpdate();
int x = 1/0;//报错
String sql2 = "update account set money = money + 100 where name = 'B'";
st = conn.prepareStatement(sql2);
st.executeUpdate();
// 业务完毕，提交事务
conn.commit();
System.out.println("成功!");
} catch (SQLException e) {
try {
//如果不写下面这个代码 ，失败也会默认回滚
conn.rollback();//如果失败回滚事务
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
finally {
JdbcUtils.release(conn,st,rs);
}
}
}
浙公网安备 33010602011771号