规范数据库设计
为什么需要设计
- 当数据比较复杂的时候, 我们就需要设计了
糟糕的数据库设计 良好的数据库设计 数据冗余, 浪费空间 节省内存空间 数据库插入和删除都会很麻烦, 异常[屏蔽使用物理外键] 保证数据库的完整性 程序的性能差 方便我们开发系统 软件开发中, 关于数据库的设计
- 分析需求: 分析业务和需要处理的数据库的需求
- 概要设计, 设计关系图E-R图
设计数据库的步骤(个人博客)
- 收集信息, 分析需求
- 用户表(用户登录注销, 用户的个人信息, 写博客, 创建分类)
- 分类表(文章分类, 谁创建的)
- 文章表(文章的信息)
- 友情链接(友情链接信息)
- 评论表
- 自定义表(系统信息, 某个关键的字, 或者一些著字段) key: value
- 标识实体(把需求落地到每个字段)
- 标识实体之间的关系
- 写博客uesr–>blog
- 创建分类user–>category
- 关注user–>user
- 友链links
三大范式
第一范式(1NF)
- 原子性: 保证每一列不可再分
第二范式(2NF)
- 前提: 满足第一范式
- 每张表只描述一件事情
第三范式(3NF)
- 前提: 满足第二范式
- 确保数据表中每一列数据都和主键直接相关, 而不能间接相关
规范性和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标, (成本, 用户体验!) 数据库的性能更加重要
- 在规范性能的问题的时候, 需要适当的考虑一下规范性
- 故意给某些表增加一些冗余的字段 (从多表查询中变为单表查询)
- 故意增加一些计算列 (从大数据量降低为小数据量的查询: 索引)
数据库驱动
驱动: 声卡, 显卡, 数据库
JDBC
SUN公司为了简化开发人员的 (对数据的统一) 操作, 提供了一个 (Java操作数据库的) 规范, 俗称JDBC, 这些规范的实现由具体的厂商去做~
对于开发人员来说, 我们只需要JDBC
需要啥
Java.sql
javax.sql
mysql-connector-java-0.0.00.jar
第一个JDBC项目
- 创建一个普通项目
- 导入数据库驱动
- 在项目下新建一个lib目录
- 加载驱动
- 用户信息和url
- 连接成功, 数据库对象
- 执行SQL的对象
- 执行SQL, 可能存在结果, 查看执行结果
- 释放连接
0.在项目下新建一个lib目录
把jar包粘贴到lib目录下面
右击添加为库
第一个JDBC项目代码
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"); //2.用户信息和url String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC"; String username = "root"; String password = "qwertyuiop"; //3.连接成功, 数据库对象 Connection代表数据库 Connection connection = DriverManager.getConnection(url, username, password); //4.执行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")); } //6.释放连接 resultSet.close(); statement.close(); connection.close(); } }
步骤再总结
- 加载驱动
- 连接数据库DriverManager
- 获得执行sql对象Statement
- 获得返回的结果集
- 释放连接
DriverManager
//DriverManager.registerDriver(new com.mysql.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=false&serverTimezone=UTC"; //mysql默认端口3306 //协议://主机地址:端口号/数据库名?参数1&参数2&参数三 //oracle默认端口1521 //jdbc:oracle:thin:@localhost:1521:sid
Statement执行SQL的对象, PrepareStatem执行SQL的对象
String sql = "SELECT * FROM users";//编写SQL statement.executeQuery();//查询操作返回ResultSet statement.execute();//执行任何SQL ststement.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();
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(...)values(...)"; 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对象中 }
db.properti(资源包)
driver = com.mysql.cj.jdbc.Driver url = jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC username = root password = qwertyuiop
JdbcUtils(类)
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(conn!=null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(st!=null){ try { st.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(rs!=null){ try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
增Insert
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,'kuangshen','123456','22334455@qq.com','2020-01-01')"; int i = st.executeUpdate(sql); if(i>0){ System.out.println("插入成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn, st, rs); } } }
删delete
public class TestDelete { 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 = "DELETE FROM users WHERE id=4"; int i = st.executeUpdate(sql); if(i>0){ System.out.println("删除成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn, st, rs); } } }
改Update
public class TestUpdate { 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 = "UPDATE users SET NAME='lisi',`email`='24736743@qq.com' 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); } } }
查Select
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(); //SQL String sql = "select * from users where id=1"; rs = st.executeQuery(sql);//查询, 返回一个结果集 while (rs.next()){ System.out.println(rs.getString("NAME")); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JdbcUtils.release(conn, st, rs); } } }
登录
public class TestLogin { public static void main(String[] args) { login("wangwu","123456"); } //登录业务 public static void login(String username, String password){ Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); st = conn.createStatement(); //SQL 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")); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JdbcUtils.release(conn, st, rs); } } }
SQL注入的问题
sql存在漏洞, 会被攻击导致数据泄露
PreparedStatement对象
PreparedStatement可以防止SQL注入, 效果更好
增
public class TestInsert1 { 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, 然后不执行 //手动给参数赋值 st.setInt(1,4);//id st.setString(2,"cat"); st.setString(3,"0987654"); st.setString(4,"123@qq.com"); //注意点: sql.Data 数据库 java.sql.Date() // util.Data Java new Date().getTime()获得时间戳 st.setDate(5,new java.sql.Date(0));//这个时间搞不懂...... //执行 int i=st.executeUpdate(); if(i>0){ System.out.println("插入成功"); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally{ JdbcUtils.release(conn, st, null); } } }
删
public class TestDelete1 { 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, 然后不执行 //手动给参数赋值 st.setInt(1,4); //执行 int i=st.executeUpdate(); if(i>0){ System.out.println("删除成功"); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally{ JdbcUtils.release(conn, st, null); } } }
改
public class TestUpdate1 { 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, 然后不执行 //手动给参数赋值 st.setString(1, "小屁孩"); st.setInt(2,1); //执行 int i=st.executeUpdate(); if(i>0){ System.out.println("更新成功"); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally{ JdbcUtils.release(conn, st, null); } } }
查
public class TestSelect1 { 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 throwables) { throwables.printStackTrace(); }finally { JdbcUtils.release(conn, st, rs); } } }
登录
public class TestLogin1 { public static void main(String[] args) { login("小屁孩", "123456"); } public static void login(String username, String password){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); 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")); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JdbcUtils.release(conn, st, rs); } } }
把传递进来的参数当作字符
假设其中存在转义字符, 会被直接转义
连接数据库
点击右上角的数据库
点击加号
选择SQLyog
[注意要应用!!!]
先测试再连接
连接成功后, 点击设置
点击架构, 选择数据库, 点击应用, 点击确定
我们可以修改直接表中, 但记得要提交
右上角小漏斗的旁边, SQ小方图标, 点开点击控制台(默认), 就可以编写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', 2000); insert into account(name, money) values ('C', 3000);
*一定要注意导入lib目录 ! ! !
*lib包也要与自己的idea, SQL版本一致 ! ! !
事务
ACID原则
原子性: 要么全部完成, 要么都不完成
一致性: 总数不变
隔离性: 多个进程互不干扰
持久性: 一旦提交不可逆, 持久化到数据库了
隔离性的问题:
脏读: 一个事务读取了另一个没有提交的事务
不可重复读: 在同一个事务内, 重复读取表中的数据, 表数据发生了改变
虚度(幻读): 在一个事务内, 读取到了别人插入的数据, 导致前后读出来的结果不一致
转账
public class TestTransaction1 { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); //1.关闭数据库的自动提交功能, 自动会开启事务 conn.setAutoCommit(false); String sql1 = "update account set money = money-100 where name = 'A'"; st = conn.prepareStatement(sql1); st.executeUpdate(); String sql2 = "update account set money = money+100 where name = 'B'"; st = conn.prepareStatement(sql2); st.executeUpdate(); //业务完毕, 提交事务 conn.commit(); System.out.println("成功"); } catch (SQLException throwables) { try { conn.rollback();//如果失败则回滚事务 } catch (SQLException e) { e.printStackTrace(); } }finally { JdbcUtils.release(conn, st, rs); } } }
数据库连接池
数据库连接→执行完毕→释放
连接→释放 十分浪费系统资源
池化技术: 准备一些预先的资源, 过来就连接预先准备好的
编写连接池, 实现一个接口 DataSource
开源数据源实现:
- DBCP
- C3P0
- Druid: 阿里巴巴
使用了这些数据库连接池之后, 我们在项目开发中, 就不需要编写连接数据库的代码了
DBCP
需要用到的jar包
commons-dbcp2-2.8.0.jar, commons-pool2-2.9.0.jar, commons-logging-1.2.jar
dbcpconfig.properties
# 连接设置 这里面的名字, 是DBCP数据源中定义好的 driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC username=root password=qwertyuiop #<!-- 初始化连接 --> initialSize=10 #最大连接数量 maxActive=50 #<!-- 最大空闲连接 --> maxIdle=20 #<!-- 最小空闲连接 --> minIdle=5 #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 --> maxWait=60000 #JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:【属性名=property;】 #注意:"user" 与 "password" 两个属性会被明确地传递,因此这里不需要包含他们。 connectionProperties=useUnicode=true;characterEncoding=UTF8 #指定由连接池所创建的连接的自动提交(auto-commit)状态。 defaultAutoCommit=true #driver default 指定由连接池所创建的连接的只读(read-only)状态。 #如果没有设置该值,则“setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix) defaultReadOnly= #driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。 #可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE defaultTransactionIsolation=READ_UNCOMMITTED
JdbcUtils_DBCP
public class JdbcUtils_DBCP { private static DataSource dataSource = null; static { try{ InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties"); Properties properties = new Properties(); properties.load(in); //创建数据源 工厂模式-->创建对象 dataSource = BasicDataSourceFactory.createDataSource(properties); } catch (Exception e) { e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws SQLException { return dataSource.getConnection(); //从数据源中获取连接 } //释放连接资源 public static void release(Connection conn, Statement st, ResultSet rs){ if(conn!=null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(st!=null){ try { st.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(rs!=null){ try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
TestDBCP
public class TestDBCP { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; try { conn = JdbcUtils_DBCP.getConnection(); String sql = "INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)"; st = conn.prepareStatement(sql);//预编译sql, 先写sql, 然后不执行 //手动给参数赋值 st.setInt(1,6);//id st.setString(2,"cat"); st.setString(3,"0987654"); st.setString(4,"123@qq.com"); //注意点: sql.Data 数据库 java.sql.Date() // util.Data Java new Date().getTime()获得时间戳 st.setDate(5,new java.sql.Date(0));//这个时间搞不懂...... //执行 int i=st.executeUpdate(); if(i>0){ System.out.println("插入成功"); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally{ JdbcUtils_DBCP.release(conn, st,null); } } }
C3P0
需要的jar包
c3p0-0.9.5.5.jar, mchange-commons-java-0.2.19.jar
c3p0config.xml
<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <!-- c3p0的缺省(默认)配置 如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource();"这样写就表示使用的是c3p0的缺省(默认)--> <default-config> <property name="driverClass">com.mysql.cj.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC</property> <property name="user">root</property> <property name="password">qwertyuiop</property> <property name="acquiredIncrement">5</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">5</property> <property name="maxPoolSize">20</property> </default-config> </c3p0-config>
JdbcUtils_C3P0
public class JdbcUtils_C3P0 { private static ComboPooledDataSource dataSource = null; static { try{ dataSource = new ComboPooledDataSource(); } catch (Exception e) { e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws SQLException { return dataSource.getConnection(); //从数据源中获取连接 } //释放连接资源 public static void release(Connection conn, Statement st, ResultSet rs){ if(conn!=null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(st!=null){ try { st.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(rs!=null){ try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
TestC3P0
public class TestC3P0 { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; try { conn = JdbcUtils_C3P0.getConnection(); String sql = "INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)"; st = conn.prepareStatement(sql);//预编译sql, 先写sql, 然后不执行 //手动给参数赋值 st.setInt(1,7);//id st.setString(2,"cat"); st.setString(3,"0987654"); st.setString(4,"123@qq.com"); //注意点: sql.Data 数据库 java.sql.Date() // util.Data Java new Date().getTime()获得时间戳 st.setDate(5,new java.sql.Date(0));//这个时间搞不懂...... //执行 int i=st.executeUpdate(); if(i>0){ System.out.println("插入成功"); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally{ JdbcUtils_C3P0.release(conn, st,null); } } }
总结
无论使用什么数据源, 本质还是一样的, DataSource接口不会变
[笔记] E:\笔记\数据库\Untitled.md
[代码] F:\Java\Java0222\jdbc
[数据库] F:\Java\Java0222\SQLyog\sql01.sql