20220705
软件开发中，关于数据库的设计
- 分析需求：分析业务和需要处理的数据库的需求；
- 概要设计：设计关系图E-R图
前端软件：Element ui 、Semantic ui、layui、ant design pro
三大范式
1.JDBC
Java操作数据库的规范，简化开发人员对数据库的统一操作。
JDBC连接数据库：
导入jar包：mysql-connector-java-8.0.13.jar
（1）加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");（2）用户信息和url
//userUnicode=true 支持中文编码 characterEncoding=utf8 设定字符为utf-8 useSSL=true 使用安全连接
String url = "jdbc:mysql://localhost:3306/jdbcstudy?" +
"useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8";
String username = "root";
String password = "123456";（3）连接数据库，返回数据库对象（DriverManager）
//connection 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);（4）执行SQL对象（Statement）
Statement statement = connection.createStatement();（5）执行SQL，存在结果集，查看返回结果（resultSet）
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql); //返回结果集，链表的形式
//resultSet 结果集中封装了我们全部查询出来的结果
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("birthday="+resultSet.getObject("birthday"));
System.out.println("--------------------------------------------------");
}（6）关闭连接
resultSet.close();
statement.close();
connection.close();Statement执行SQL的对象
statement.executeQuery();//查询操作返回ResultSet
statement.executeUpdate();//更新、插入、删除，返回受影响的行数
statement.execute();//执行任意SQLResultSet返回结果集，封装了全部的查询结果
resultSet.getObject();//在不知道列数据类型的情况下使用
resultSet.getInt();
resultSet.getString();
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行2. Statement对象
Statement对象用于向数据库发送SQL语句，完成对数据库的增、删、改、查。
查询：statement.executeQuery();
更新、插入、删除：statement.executeUpdate();
（1）增、删、改
String sql = "insert into user(...) values(...)";
String sql = "delete from user where id =...";
String sql = "update user set name = '' where name = ''";
int num = statement.executeUpdate(sql);（2）查询
String sql = "select * from user where id =... ";
ResultSet resultSet = statement.executeQuery(sql);3. 封装工具类，实现增删改查功能
（1）在src目录下添加文件，将连接数据库的信息放在文件中
//db.properties（src目录下）
driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8
username = root
password = 123456（2）封装连接数据库、释放资源操作
//JdbcUtils.java
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(driver);
}catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,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();
}
}
}
}（3）获取数据库连接，实现增删改操作
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 {
conn = JdbcUtils.getConnection(); //获取数据库连接
st = conn.createStatement(); //获得sql的执行对象
String sql = "INSERT into users (id,`name`,`password`,`email`,`birthday`) VALUES(4,'zhaoliu','123456','zhaoliiu@sina.com','2000-01-01')";
//String sql = "DELETE FROM users WHERE id = 4";
//String sql ="UPDATE users SET `name`="张三" WHERE id = 1";
int i = st.executeUpdate(sql); //增删改的方法
if(i>0){
System.out.println("插入成功！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}（4）获取数据库连接，实现查询操作
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestQuery {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "SELECT * FROM users WHERE id = 2";
rs = st.executeQuery(sql);
while(rs.next()){
System.out.println("id = " + rs.getInt("id"));
System.out.println("name = " + rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}4. SQL注入问题
sql存在漏洞，会被攻击，导致数据泄露（SQL会被拼接）
select * from users where `name` ="' ' or '1=1'" and `password` = "' ' or '1=1'" ;
5. PreparedStatement对象
PreparedStatement可以防止SQL注入，效率更高！把传递过来的参数当作字符，比如说'会被直接转义。
使用preparedStatement执行插入操作
import java.util.Date;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class PrepareInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
conn =JdbcUtils.getConnection();
String sql = "insert into users (id,`name`,`password`,`email`,`birthday`) VALUES(?,?,?,?,?)";
pst = conn.prepareStatement(sql); //预编写sql,先写sql，然后不执行
//手动给参数赋值
pst.setInt(1,4);
pst.setString(2,"赵六");
pst.setString(3,"123456");
pst.setString(4,"123321@qq.com");
pst.setDate(5,new java.sql.Date(new Date().getTime()));
//执行
int i = pst.executeUpdate();
if(i>0){
System.out.println("插入成功！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}
}
}使用PreparedStatement执行删除操作
try {
conn =JdbcUtils.getConnection();
String sql = "delete from users where id =?";
pst = conn.prepareStatement(sql); //预编写sql,先写sql，然后不执行
//手动给参数赋值
pst.setInt(1,4);
//执行
int i = pst.executeUpdate();
if(i>0){
System.out.println("删除成功！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}使用PreparedStatement执行修改操作
try {
conn =JdbcUtils.getConnection();
String sql = "UPDATE users SET `name`=? WHERE id = ?";
pst = conn.prepareStatement(sql); //预编写sql,先写sql，然后不执行
//手动给参数赋值
pst.setString(1,"lisi");
pst.setInt(2,2);
//执行
int i = pst.executeUpdate();
if(i>0){
System.out.println("修改成功！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}使用PreparedStatement执行查询操作
try {
conn =JdbcUtils.getConnection();
String sql = "SELECT * FROM users WHERE id = ?";
pst = conn.prepareStatement(sql); //预编写sql,先写sql，然后不执行
//手动给参数赋值
pst.setInt(1,1);
//执行
rs = pst.executeQuery();
if(rs.next()){
System.out.println("id="+rs.getInt("id"));
System.out.println("name="+rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}6. 事务
ACID原则：
原子性：要么全部完成，要么都不完成
一致性：总数不变
隔离性：多个进程互不干涉
持久性：一旦提交不可逆，持久化到数据库
隔离性的问题：
脏读：一个事务读取了另一个没有提交的事务
不可重复读：在同一个事务内，重复读取表中的数据，表数据发生了改变
虚读（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读出来的结果不一致
（1）开启事务：conn.setAutoCommit(false);
（2）一组业务执行完毕，提交事务：conn.commit();
（3）在catch语句中显示定义回滚语句：conn.rollback(); 默认失败就会回滚。
conn = JdbcUtils.getConnection(); //获取数据库连接
//关闭数据库的自动提交，自动会开启事务
conn.setAutoCommit(false);//开启事务
st = conn.createStatement(); //获得sql的执行对象
// sql语句
st.executeUpdate(sql); //增删改的方法
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}7.数据库连接池
使用数据连接池，在项目中不必编写连接数据库的代码
开源数据源实现
DBCP、C3P0、Druid
浙公网安备 33010602011771号