一,JDBC复习
JDBC的全称是Java数据库连接(Java Database Connect),它是一套用于执行SQL语句的Java API。应用程序可通过这套API连接到关系数据库,并使用SQL语句来完成对数据库中数据的查询、更新和删除等操作。
1.1.1为项目导入mysql-jdbc依赖的jar包
我们使用的是mysql-connector-java-8.0.11-bin.jar的驱动,当然也可以使用mysql-connector-java-5.1.46-bin.jar的驱动,但是在加载驱动的时候以及填写链接的时候会有些不同
在 mysql-connector-java-8.0.11-bin.jar中,驱动类的类路径为com.mysql.cj.jdbc.Driver,而在 mysql-connector-java-5.0.8-bin.jar中,驱动类的类路径为com.mysql.jdbc.Driver
//Class.forName:返回与给定的字符串名称相关联类或接口的Class对象。
public static void main(String[] args) {
try {
// 初始化驱动
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("初始化驱动加载成功");
}catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//或者使用DriverManager.register(驱动类的对象)进行初始化驱动
public static void main(String[] args) {
try {
// 初始化驱动
DriverManager.registerDriver(new Driver());
System.out.println("初始化驱动加载成功");
}catch (SQLException e) {
e.printStackTrace();
}
}
1.1.2建立与数据库的连接
public static void main(String[] args) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
// 建立与数据库的Connection连接
// 这里需要提供:
// 数据库所处于的ip:127.0.0.1 或者说localhost (本机)
// 数据库的端口号: 3306 (mysql专用端口号)
// 数据库名称: jdbc(根据你自己的数据名称来设置)
// 编码方式: UTF-8
// 时区: GMT (8版本的驱动要添加,5版本可以不写)
// 是否使用ssl连接: false (8版本的驱动要添加,5版本可以不写)
// 账号: root(根据你自己的用户名填写)
// 密码: root(根据你自己的密码填写)
String url = "jdbc:mysql://localhost:3306/jdbc?characterEncoding=utf8&serverTimezone=GMT&useSSL=false";
// String url = "jdbc:mysql://localhost:3306/jdbc?characterEncoding=utf8"; 这个是5版本的驱动使用的连接
String username = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
1.1.3创建statement
JDBCUtils类
JDBCUtils类,封装了注册驱动,获取连接,关闭连接等方法,方便调用。
package com.jdbc.util;
import java.sql.*;
public class JDBCUtils {
private final String url = "jdbc:mysql://localhost:3306/jdbc?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false";
private final String username = "root";
private final String password = "root";
private volatile static JDBCUtils jdbcUtils = null;
/*
初始化驱动
*/
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private Connection getMysqlConnection(JDBCUtils jdbcUtils){
Connection connection = null;
try {
connection = DriverManager.getConnection(jdbcUtils.url,jdbcUtils.username,jdbcUtils.password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
/**
* 获取MySQL数据连接对象(略读,不要求掌握)
* @return Connection连接对象
*/
public static Connection getMysqlConnection(){
return getInstance().getMysqlConnection(getInstance());
}
/**
* 获取实例对象(略读,不要求掌握)
* @return JDBCUtils对象
*/
private static JDBCUtils getInstance() {
if (null == jdbcUtils){
synchronized (JDBCUtils.class){
if (null == jdbcUtils){
jdbcUtils = new JDBCUtils();
}
}
}
return jdbcUtils;
}
/**
* 关闭连接
* @param connection
*/
public static void close(Connection connection){
if (connection != null) {
try {
connection.close();
System.out.println("连接已关闭");
} catch (SQLException e) {
System.out.println("连接关闭失败");
e.printStackTrace();
}
}
}
/**
* 关闭连接
* @param connection
* @param statement
* @param resultSet
*/
public static void close(Connection connection, Statement statement, ResultSet resultSet){
if (resultSet != null) {
try {
resultSet.close();
System.out.println("resultSet关闭成功");
} catch (SQLException e) {
System.out.println("resultSet关闭失败");
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
System.out.println("statement关闭成功");
} catch (SQLException e) {
System.out.println("statement关闭失败");
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
System.out.println("连接已关闭");
} catch (SQLException e) {
System.out.println("连接关闭失败");
e.printStackTrace();
}
}
}
}
1.1.4执行SQL语句
package com.jdbc.test;
import com.jdbc.util.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestStatement {
public static void main(String[] args) {
// 获取连接
Connection mysqlConnection = JDBCUtils.getMysqlConnection();
// 创建statement对象
Statement statement = null;
String sql = null;
boolean execute = false;
try {
statement = mysqlConnection.createStatement();
System.out.println("statement创建成功");
// 查
sql = "select * from tb_user where `username` = 'Jack'";
execute = statement.execute(sql);
// 通过getResultSet方法可以获取返回结果集
ResultSet resultSet = statement.getResultSet();
while (resultSet.next()){
System.out.println(resultSet.getInt(1));
}
System.out.println("select语句的execute = " + execute);
// 增
sql = "insert into tb_user(`username`,`password`,`gender`) values('Tom','123456',1)";
execute = statement.execute(sql);
System.out.println("insert语句的execute = " + execute);
// 改
sql = "update tb_user set `password` = '12345678' where `username` = 'Tom'";
execute = statement.execute(sql);
System.out.println("update语句的execute = " + execute);
// 删
sql = "delete from tb_user where `username` = 'Tom'";
execute = statement.execute(sql);
System.out.println("delete语句的execute = " + execute);
// 方法statement.execute(sql)的返回值,如果执行的是select语句,且有返回集,则为true,其他情况下均为false。该返回值并不表示SQL语句是否执行成功
} catch (SQLException e) {
System.out.println("sql执行异常");
e.printStackTrace();
}finally {
JDBCUtils.close(mysqlConnection,statement,null);
}
}
}
1.1.5关闭连接
/*以下是来自JDBCUtils中的关闭方法*/
/**
* 关闭连接
* @param connection
*/
public static void close(Connection connection){
if (connection != null) {
try {
connection.close();
System.out.println("连接已关闭");
} catch (SQLException e) {
System.out.println("连接关闭失败");
e.printStackTrace();
}
}
}
/**
* 关闭连接
* @param connection
* @param statement
* @param resultSet
*/
public static void close(Connection connection, Statement statement, ResultSet resultSet){
if (resultSet != null) {
try {
resultSet.close();
System.out.println("resultSet关闭成功");
} catch (SQLException e) {
System.out.println("resultSet关闭失败");
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
System.out.println("statement关闭成功");
} catch (SQLException e) {
System.out.println("statement关闭失败");
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
System.out.println("连接已关闭");
} catch (SQLException e) {
System.out.println("连接关闭失败");
e.printStackTrace();
}
}
}
}
关闭顺序为:先开启的后关闭,即关闭顺序为:resultSet>statement>connection
connection是获得数据库连接是操作数据库的第一步,是应用程序和数据库的一次“握手”过程。数据库打开的连接数是有限的,所以连接使用完成后需要关闭。
statement用于执行静态 SQL 语句并返回它所生成结果的对象。
resultSet表示数据库结果集的数据表,通常通过执行查询数据库的语句生成。ResultSet 对象具有指向其当前数据行的光标。最初,光标被置于第一行之前。next 方法将光标移动到下一行;因为该方法在 ResultSet 对象没有下一行时返回 false,所以可以在 while 循环中使用它来迭代结果集。
1.2预编译Statement
1.PreparedStatement的参数设置相比于Statement来说更加容易维护,可读性更高,不容易犯错
2.PreparedStatement的预编译机制,性能比Statement更快
3.PreparedStatement可以防止SQL注入式攻击
package com.jdbc.test;
import com.jdbc.entity.User;
import com.jdbc.util.JDBCUtils;
import java.sql.*;
public class TestPrepareStatement {
/**
* preparedStatement的参数设置
* @param user
*/
public void userRegister1(User user){
Connection mysqlConnection = JDBCUtils.getMysqlConnection();
String sqlOne = "insert into tb_user(`username`,`password`,`gender`) values("+user.getUsername()+","+user.getPassword()+","+user.getGender()+")";
String sqlTwo = "insert into tb_user(`username`,`password`,`gender`) values(?,?,?)";
try (
Statement statement = mysqlConnection.createStatement();
PreparedStatement prepareStatement = mysqlConnection.prepareStatement(sqlTwo);
){
statement.execute(sqlOne);
// 参数设置是从1开始的,不是从0开始的
prepareStatement.setString(1,user.getUsername());
prepareStatement.setString(2,user.getPassword());
prepareStatement.setInt(3,user.getGender());
prepareStatement.execute();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(mysqlConnection);
}
}
/**
* preparedStatement的预编译机制
* @param user
*/
public void userRegister2(User user){
Connection mysqlConnection = JDBCUtils.getMysqlConnection();
String sqlOne = "insert into tb_user(`username`,`password`,`gender`) values("+"\""+user.getUsername()+"\""+","+user.getPassword()+","+user.getGender()+")";
String sqlTwo = "insert into tb_user(`username`,`password`,`gender`) values(?,?,?)";
try (
Statement statement = mysqlConnection.createStatement();
PreparedStatement prepareStatement = mysqlConnection.prepareStatement(sqlTwo);
){
// 插入十个用户,statement需要执行10次sql语句,需要10都把SQL语句传输到数据库端,数据库每次都要对SQL语句进行编译的执行
for (int i = 0; i < 10; i++) {
statement.execute(sqlOne);
}
// 插入十个用户,PreparedStatement 执行10次,只需要1次把SQL语句传输到数据库端
// 数据库对带?的SQL进行预编译
// 每次执行,只需要传输参数到数据库端
// 1. 网络传输量比Statement更小
// 2. 数据库不需要再进行编译,响应更快
for (int i = 0; i < 10; i++) {
prepareStatement.setString(1,user.getUsername());
prepareStatement.setString(2,user.getPassword());
prepareStatement.setInt(3,user.getGender());
prepareStatement.execute();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(mysqlConnection);
}
}
/**
* 防止SQL注入
* @param user
*/
public void userRegister3(User user){
Connection mysqlConnection = JDBCUtils.getMysqlConnection();
String sqlOne = "select * from tb_user where `username` = "+user.getUsername();
String sqlTwo = "select * from tb_user where `username` = ?";
ResultSet resultSet = null;
try (
// Statement statement = mysqlConnection.createStatement();
PreparedStatement prepareStatement = mysqlConnection.prepareStatement(sqlTwo);
){
// ResultSet resultSet = statement.executeQuery(sqlOne);
// PreparedStatement会对SQL进行了预编译,在第一次执行SQL前数据库会进行分析、编译和优化,同时执行计划同样会被缓存起来,它允许数据库做参数化查询。
// 在使用参数化查询的情况下,数据库不会将参数的内容视为SQL执行的一部分,而是作为一个字段的属性值来处理,这样就算参数中包含破环性语句(or ‘1=1’)也不会被执行。
// preparedStatement执行的SQL语句:select * from tb_user where `username` = "'Jack' or 1=1"
prepareStatement.setString(1,user.getUsername());
resultSet = prepareStatement.executeQuery();
while (resultSet.next()){
user.setId(resultSet.getInt("id"));
user.setUsername(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
user.setGender(resultSet.getInt("gender"));
System.out.println(user);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(mysqlConnection,null,resultSet);
}
}
public static void main(String[] args) {
User user = new User();
user.setUsername("Jack");
user.setPassword("123");
user.setGender(1);
TestPrepareStatement testPrepareStatement = new TestPrepareStatement()
// testPrepareStatement.userRegister1(user);
// testPrepareStatement.userRegister2(user);
// user.setUsername("'Jack' or 1=1");
// testPrepareStatement.userRegister3(user);
}
}
1.3 事务
- 为什么要使用事务?
假设这样一种场景,银行汇款。A向B汇款100元(假设A账户余额大于100元),那么要实现这样的功能,需要进行的操作有:
1.A账户余额减去100元
2.B账户余额增加100元
但是,如果步骤1执行成功,但是步骤2执行出现问题,这个时候就会出现A账户减少100但是B没有收到。这个问题是很严重的,直接导致损害到了用户的利益。
因此,为了防止出现这样的情况,我就要用到事务。
上述情况,如果使用了事务,在事务中出现错误(指的是抛出运行时异常)的时候,整个执行操作都会被自动回滚(或者也可以自己调用rollback方法进行回滚)。这样的话,就保证了用户的利益。
package com.jdbc.test;
import com.jdbc.entity.User;
import com.jdbc.util.JDBCUtils;
import java.sql.*;
public class TestTransaction {
public void userRegister(User user){
Connection mysqlConnection = JDBCUtils.getMysqlConnection();
PreparedStatement prepareStatement = null;
ResultSet generatedKeys = null;
String sql1 = "insert into tb_user(`username`,`password`,`gender`) values(?,?,?)";
String sql2 = "update tb_user set `password` = ? where `id` = ?";
try {
// 设置取消自动提交
mysqlConnection.setAutoCommit(false);
prepareStatement = mysqlConnection.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);
prepareStatement.setString(1,user.getUsername());
prepareStatement.setString(2,user.getPassword());
prepareStatement.setInt(3,user.getGender());
int num = prepareStatement.executeUpdate();
if (num == 1){
generatedKeys = prepareStatement.getGeneratedKeys();
if (generatedKeys.next()){
int id = generatedKeys.getInt(1);
prepareStatement = mysqlConnection.prepareStatement(sql2);
prepareStatement.setString(1,"1234");
prepareStatement.setInt(2,id+1);
int i = prepareStatement.executeUpdate();
if (i == 1){
System.out.println("更新成功");
}else {
// // 抛出运行时异常,自动回滚
// throw new RuntimeException("更新失败");
// 手动调用rollback方法回滚
mysqlConnection.rollback();
System.out.println("事务回滚");
}
}
}else {
// 抛出运行时异常,自动回滚
throw new RuntimeException("用户注册失败");
// // 手动调用rollback方法回滚
// mysqlConnection.rollback();
// System.out.println("事务回滚");
}
// 手动提交
mysqlConnection.commit();
//mysqlConnection.setAutoCommit(false)和mysqlConnection.commit(),之间的所有操作都会被归到一个事务中
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(mysqlConnection,prepareStatement,generatedKeys);
}
}
public static void main(String[] args) {
User user = new User();
user.setUsername("Jack");
user.setPassword("123");
user.setGender(1);
TestTransaction testTransaction = new TestTransaction();
testTransaction.userRegister(user);
}
}
二,关键字 索引
2.1关键字
limit
# limit 要查询的记录序号-1,记录数;
# 获取查询到的第1到10的记录
select * from tb_good limit 0,10;
# 获取查询到的第21到30的记录
select * from tb_good limit 20,10;
# order by(在limit前)
# 排序
# 按买家号降序排列(不加desc默认升序asc)
select * from tb_good order by buyer_number desc
like(常用)
select * from param_department where departmentName like '%经济%';
2.2索引
在数据量过大时,查询的效率变得尤为重要,建立索引是个不错的选择
2.3 拓展
- 外键约束
用户实现数据库表的参照完整性。外键约束可以使两张表紧密结合起来,特别是对于删除/修改级联操作时,会保证数据的完整性。
举一个外键约束的栗子
NO ACTION / RESTRICT
NO ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同
RESTRICT:拒绝对父表的删除或更新操作,不允许操作
也是默认的策略。
CASCADE
CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行。
操作主表的时候影响从表的外键信息
SET NULL
SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL。如果使用该选项,必须保证子表列没有指定NOT NULL
直接举个栗子
1)朋友圈删除,点赞。留言都删除 – CASCADE
2)解散班级,对应的学生置为班级为null就可以了,-- SET NULL
三,c3p0连接池
建立连接断开连接会产生一定开销,JDBC每次获取连接都会进行验证,如果使用连接池,获取连接从连接池中拿,释放连接将连接放入连接池,高效
基本原理就是为数据库建立一个缓冲池。在缓冲池中先创建指定数量的数据库连接,当有连接请求时就从缓冲池中取出处于“空闲”状态的连接,并将此连接标记为“忙碌”,直到该请求进程结束后,它所使用的连接才会重新回到“空闲”状态,并等待下一次请求调用。
这里需要强调一点的是,数据库连接池中的连接数是在其初始化时根据c3p0-config.xml中的最小连接数来确定的。当然,无论连接池的连接数是否有被使用,它都至少会保持最小连接数,如果请求连接数超过最小连接数也会根据c3p0-config.xml中指定的自增长数增加连接数直到达到最大连接数,这时如果请求连接数量还是大于连接池中的连接数的话,剩下的请求将会被放入等待队列直到有空闲连接出现。
3.1 导包
c3p0需要使用两个包
- c3p0-0.9.5.5.jar
- mchange-commons-java-0.2.19.jar
3.2.1 XML配置
c3p0-config.xml文件需要放在Source root(蓝色main目录)的根下,才能够正常的被读取
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<!-- 用于数据库连接的四大必须参数 -->
<property name="user">bluemoomAdmin</property>
<property name="password">!!!123456</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/demo?
characterEncoding=utf-8&useSSL=false</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<!--数据库连接数的配置-->
<!-- 如果池中数据连接不够时一次增长多少个 -->
<property name="acquireIncrement">5</property>
<!-- 初始化数据库连接池时连接的数量,,介于maxPoolSize和minPoolSize之间-->
<property name="initialPoolSize">20</property>
<!-- 数据库连接池中的最大的数据库连接数,如果获得新连接时会使连接总数超过这个值则
不会再获取新连接,而是等待其他连接释放,所以这个值有可能会设计地很大-->
<property name="maxPoolSize">25</property>
<!-- 数据库连接池中的最小的数据库连接数 -->
<property name="minPoolSize">5</property>
<!-- 当连接池用完时客户端调用getConnection()后等待获取新连接的时间,超时后将抛
出SQLException -->
<property name="checkoutTimeout">30000</property>
<!--隔多少秒检查所有连接的空闲时间-->
<property name="idleConnectionTestPeriod">30</property>
<!--连接的最大空闲时间,如果超过这个时间,某个数据库连接还没有被使用,则会断开--
>
<property name="maxIdleTime">30</property>
<!-- 用以控制数据源内加载的PreparedStatements数量 -->
<property name="maxStatements">200</property>
</default-config>
<!-- 命名的配置,可以通过方法调用实现 -->
<named-config name="test">
<property name="user">bluemoomAdmin</property>
<property name="password">!!!123456</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/demo</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">20</property>
<property name="maxPoolSize">25</property>
<property name="minPoolSize">5</property>
</named-config>
</c3p0-config>
3.2.2 代码配置
DATA_SOURCE.setUser("user");
DATA_SOURCE.setPassword("password");
DATA_SOURCE.setJdbcUrl("jdbcUrl");
DATA_SOURCE.setDriverClass("driver");
DATA_SOURCE.setCheckoutTimeout(30000);
DATA_SOURCE.setInitialPoolSize(10);
//配置配置文件配置
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src/config.properties"));
DATA_SOURCE.setUser(properties.getProperty("user"));
DATA_SOURCE.setPassword(properties.getProperty("password"));
DATA_SOURCE.setJdbcUrl(properties.getProperty("jdbcUrl"));
DATA_SOURCE.setDriverClass(properties.getProperty("driver"));
} catch (IOException | PropertyVetoException e) {
e.printStackTrace();
}
3.3 工具类
package utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.SQLException;
public class C3P0Utils {
//默认会使用C3P0-config.xml的配置
private static final ComboPooledDataSource DATA_SOURCE = new ComboPooledDataSource();//为空代表使用默认配置
public static ComboPooledDataSource getDataSource() {
return DATA_SOURCE;
}
public static Connection getConnection() {
Connection conn;
try {
conn = DATA_SOURCE.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
return conn;
}
public static void releaseConnection(AutoCloseable... autoCloseables) {
try {
for (AutoCloseable a : autoCloseables) {
if (a != null) {
a.close();
}
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
四,hikariCP连接池
后期之秀Hikari,其实是日语发音,名字翻译成“光”,号称目前最快的连接池,是在BoneCP基础上进行的修改目前已被Spring官方推荐并整合
五,分页
在浏览器获取信息时如果信息条数过多,可以采取分页的形式降低浏览器负担。
做一个分页需要哪些参数?
- 当前页 CurPage
- 页面大小 pageSize
- 查询总记录数 totalRecord
分页的步骤(非官方版)
1,前端传来查询内容,搜索数据库,找到要求信息的总数
2,得到总数,前端传来页面大小,后端计算一共有多少页,传给前端
3,前端得到总页数,然后给后端传送现在需要第几页
4,后端查询数据库,传给前端相应的数据(limit)
总页数计算
计算公式:int totalPageNum = (totalRecord + pageSize - 1) / pageSize
首先,在计算机进行整型计算的时候会采取向下取整(栗子:4/5 -> 0.8 -> 0,小数点后的直接去掉)
如果有21条记录,页面大小为10,会有(21 + 10 - 1) / 10 = 3页
如果刚好20条记录,页面大小10 会有(20 + 10 - 1) / 10 = 2 页
limit语句
limit (curPage - 1) * pageSize , pageSize
select总记录数
select count(*) from table
Page类封装
package entity;
import java.util.List;
public class Page<T> {
/**
* 当前是第几页
*/
private int currentPage;
/**
* 一页数据量
*/
private int pageSize;
/**
* 数据库一共数据量
*/
private int totalRecord;
/**
* 用于存放数据库中的数据结果集,使用泛型,增强通用性
*/
List<T> list;
/**
* 总共页数
*/
private int totalPage;
/**
* 数据记录起始位置
*/
private int startIndex;
public Page(int currentPage, int pageSize, int totalRecord) {
this.currentPage = currentPage;
this.pageSize = pageSize;
this.totalRecord = totalRecord;
// 总页数 = [总记录数/页面大小],如果不是整除需要+1
if (totalRecord % pageSize == 0) {
this.totalPage = totalRecord / pageSize;
} else {
this.totalPage = totalRecord / pageSize + 1;
}
//计算起始页号,(当前页号-1)*页面大小
this.startIndex = currentPage * pageSize - pageSize;
}
}