JDBC学习

MySQL学习笔记（狂神说Java）

狂神说B站视频：https://www.bilibili.com/video/BV1NJ411J79W?p=1

MySQL官网：https://www.mysql.com/

MySQL教程：https://www.runoob.com/mysql/mysql-tutorial.html

一、数据库驱动

驱动：声卡、显卡、数据库

img

我们的程序会通过数据库驱动和数据库打交道！

二、JDBC

Maven下载链接

MySQL官网下载JDBC

三、第一个JDBC程序

process

1.创建测试数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 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','[email protected]','1980-12-04'),
(2,'lisi','123456','[email protected]','1981-12-04'),
(3,'wangwu','123456','[email protected]','1979-12-04')

2.导入数据库驱动

image-20210207104736184

3.编写测试代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
package JDBC;

import java.sql.*;

public class Demo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1 加载驱动
        Class.forName("com.mysql.jdbc.Driver"); //固定写法
        // 2 url 和 用户信息
        // useUnicode=true :使用Unicode编码 支持中文
        // characterEncoding=utf8 :设定中文字符集为 UTF-8
        // 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 statement = connection.createStatement();
        // 5 执行 SQL操作对象
        String sql = "select * from users";
        ResultSet resultSet = statement.executeQuery(sql);//链表类型，返回的结果集,结果集中封装了全部的查询对象
        while (resultSet.next()){
            System.out.println(resultSet.getObject("id"));
            System.out.println(resultSet.getObject("NAME"));
            System.out.println(resultSet.getObject("PASSWORD"));
            System.out.println(resultSet.getObject("email"));
            System.out.println(resultSet.getObject("birthday"));
            System.out.println("=====================================");
        }
        // 6 释放连接 后创建的先释放
        statement.close();
        connection.close();
        connection.close();
    }
}

image-20210207104822012

步骤总结： 加载驱动 - 连接数据库(DriverManager) - 获取执行对象(Statement) - 获得返回结果集 - 释放连接

4.常用对象分析

DriverManager
1
2
3
4
5
6
7
8
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver"); //固定写法

//connection代表数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
connection.commit(); //事务提交
connection.rollback();//数据库回滚
connection.setAutoCommit();//数据库设置自动提交
URL
1
2
3
4
5
6
7
8
9
10
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
// useUnicode=true :使用Unicode编码 支持中文
// characterEncoding=utf8 :设定中文字符集为 UTF-8
// useSSL=true :使用安全的连接

jdbc:mysql//主机地址:3306/数据库名?参数1&参数2&参数3
    
//musql - 3306 
//oralce - 1521
//jdbc:oralce:thin:@localhost:1521:sid
Statement
1
2
3
4
5
6
7
8
9
10
PreparedStatement  //同为执行SQL的对象
//新建 SQL对象
Statement statement = connection.createStatement();

String sql = "select * from users";//编写SQL

statement.executeQuery(); //查询操作，返回ResultSet数据集
statement.executeUpdate(); //更新、插入、删除，返回受影响的行数
statement.execute(); //执行任何SQL
statement.clearBatch(); //执行SQL的批处理
ResultSet
1
//查询的结果集 封装了所有查询结果

获得指定数据类型

1
2
3
4
5
6
7
//在不知道类类型的情况下使用，如果知道就用指定类型
resultSet.getObject(); 
resultSet.getDouble();
resultSet.getString();
resultSet.getFloat();
resultSet.getInt();
.....

遍历(指针)

1
2
3
4
5
resultSet.beforeFirst(); //移动到最前面
resultSet.afterLast(); //移动到最后面
resultSet.next(); //移动到下一个数据
resultSet.previous(); //移动到前一行
resultSet.absolute(row); //移动到指定行

释放资源

1
2
3
statement.close();
connection.close();
connection.close();

四、statement对象

JDBC中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。

Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化)。

Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

1.db.properties

1
2
3
4
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

2.jdbcUtils

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
package com.baixf.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 resourceAsStream = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            // 1 驱动只需要加载一次
            Class.forName(driver);
        }catch (IOException | ClassNotFoundException e){
            e.printStackTrace();
        }
    }
    // 2 获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    // 3 释放连接
    public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if(resultSet!=null) {
            try {
                resultSet.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }
}

3.CRUD操作

Create

使用executeUpdate(String sql)方法完成数据添加操作，示例操作:

1
2
3
4
5
6
statement st = conn.createstatement(;
string sq1 = "insert into user(... . ) values .... . ) ";
int num = st.executeupdate(sq1);
if(num>0){
system.out.println("插入成功!!! ");
}
Read

使用executeQuery(String sql)方法完成数据查询操作，示例操作:

1
2
3
4
5
statement st = conn.createstatement(;
string sql = "select t from user where id=1";Resu7tset rs = st.executeupdate(sq1);
while(rs.next({
//根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}
Update

使用executeUpdate(String sql)方法完成数据修改操作，示例操作:

1
2
3
4
5
statement st = conn. createstatementO;
string sql = "update user set name='" where name='" ";int num = st.executeupdate(sq1);
if(num>0){
system.out.print1n(“修改成功!!! ");
}
Delete

使用executeUpdate(String sql)方法完成数据删除操作，示例操作:

1
2
3
4
5
6
statement st = conn.createstatementO;
string sql = "delete from user where id=1";
int num = st.executeupdate(sq1);
if(num>0){
system.out. println(“删除成功!!! ");
}

4.编写增删改查操作

添加数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
//插入数据
package com.baixf.lesson02;

import com.baixf.lesson02.utils.jdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Demo01 {
    public static void main(String[] args) throws SQLException ,NullPointerException{
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;

        try {
            connection = jdbcUtils.getConnection();//获取数据库连接
            statement = connection.createStatement();//获得SQL的执行对象
            String sql = "INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)" +
                    "VALUES(6,'wangwu','password','[email protected]','2020-12-25')" ;
            int i = statement.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功！");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            assert statement != null;
            jdbcUtils.release(connection,statement,resultSet);
        }
    }
}

image-20210207145206396

删除数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
package com.baixf.lesson02;

import com.baixf.lesson02.utils.jdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Demo02 {
    public static void main(String[] args) throws SQLException {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            //创建数据库连接
            connection = jdbcUtils.getConnection();
            //获得 SQL对象
            statement = connection.createStatement();
            //编写 SQL语句
            String str = "DELETE FROM users WHERE `ID`=6;";
            int i = statement.executeUpdate(str);
            if (i>0){
                System.out.println("删除成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(connection,statement,resultSet);
        }
    }
}

image-20210207145530714

更新数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
package com.baixf.lesson02;

import com.baixf.lesson02.utils.jdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Demo03 {
    public static void main(String[] args) throws SQLException {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            //创建数据库连接
            connection = jdbcUtils.getConnection();
            //获得 SQL对象
            statement = connection.createStatement();
            //编写 SQL语句
            String str = "UPDATE users SET `NAME`='yanghongli',`email`='[email protected]' WHERE `id`=5";
            int i = statement.executeUpdate(str);
            if (i>0){
                System.out.println("修改成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(connection,statement,resultSet);
        }
    }
}

image-20210207150416765

查询数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
package com.baixf.lesson02;

import com.baixf.lesson02.utils.jdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Demo04 {
    public static void main(String[] args) throws SQLException {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            // 建立连接
            connection = jdbcUtils.getConnection();
            // 创建SQL对象
            statement = connection.createStatement();
            // 执行sql语句
            String str = "SELECT * FROM users";
            // resultset接收数据集
            resultSet = statement.executeQuery(str);
            while (resultSet.next()){
                System.out.println(resultSet.getObject("id"));
                System.out.println(resultSet.getObject("NAME"));
                System.out.println(resultSet.getObject("PASSWORD"));
                System.out.println(resultSet.getObject("email"));
                System.out.println(resultSet.getObject("birthday"));
                System.out.println("=====================================");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jdbcUtils.release(connection,statement,resultSet);
        }
    }
}

image-20210207151759111

SQL注入问题

SQL存在漏洞，会被攻击导致数据泄露，SQL会被拼接。

SQL注入是比较常见的网络攻击方式之一，它不是利用操作系统的BUG来实现攻击，而是针对程序员编写时的疏忽，通过SQL语句，实现无账号登录，甚至篡改数据库。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
package com.baixf.lesson02;

import com.baixf.lesson02.utils.jdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Demo05 {
    // SQL 注入
    public static void main(String[] args) throws SQLException {
        login("baixf","password");
        //login("baixf","");
        login("","password");
        login("'or '1=1","123456");
    }

    public static void login(String username, String password) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            // 建立连接
            connection = jdbcUtils.getConnection();
            // 创建SQL对象
            statement = connection.createStatement();
            // 执行sql语句
            String str = "SELECT * FROM `users` WHERE `NAME`='" + username + "' && `PASSWORD`='" + password + "'";
            resultSet = statement.executeQuery(str);
                while (resultSet.next()) {
                    System.out.println(resultSet.getObject("NAME"));
                    System.out.println(resultSet.getObject("PASSWORD"));
                    System.out.println("===================================");
                }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jdbcUtils.release(connection, statement, resultSet);
        }
    }
}

image-20210207154908770

image-20210207154655015

五、PreparedStatement 对象

PreparedStatement 对象可以防止SQL注入，而且效率更快。

image-20210207161653948

1.db.properties

1
2
3
4
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

2.jdbcUtils

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
package com.baixf.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 resourceAsStream = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            // 1 驱动只需要加载一次
            Class.forName(driver);
        }catch (IOException | ClassNotFoundException e){
            e.printStackTrace();
        }
    }
    // 2 获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    // 3 释放连接
    public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if(resultSet!=null) {
            try {
                resultSet.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }
}

3.CRUD操作

增加数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
package com.baixf.lesson03;

import com.baixf.lesson02.utils.jdbcUtils;

import java.sql.*;

public class Demo01 {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = jdbcUtils.getConnection();
            //区别 ： 使用 ? 占位符
            String str = "INSERT INTO `users`(id,NAME,PASSWORD,email,birthday) VALUES(?,?,?,?,?)";
            //预编译 SQL ，先写 SQL 然后赋值
            preparedStatement = connection.prepareStatement(str);
            preparedStatement.setInt(1,6);
            preparedStatement.setString(2,"王冰冰");
            preparedStatement.setString(3,"bxfwlg");
            preparedStatement.setString(4,"[email protected]");
            preparedStatement.setDate(5, new Date(2000));

            //执行
            int i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(connection,preparedStatement,null);
        }
    }
}

image-20210207161607387

删除数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
package com.baixf.lesson03;

import com.baixf.lesson02.utils.jdbcUtils;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Demo02 {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = jdbcUtils.getConnection();
            //区别 ： 使用 ? 占位符
            String str = "delete from users where name = ?";

            //预编译 SQL ，先写 SQL 然后赋值
            preparedStatement = connection.prepareStatement(str);
            //赋值
            preparedStatement.setString(1,"baixf");

            //执行
            int i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("删除成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(connection,preparedStatement,null);
        }
    }
}

image-20210207162244233

更新数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
package com.baixf.lesson03;

import com.baixf.lesson02.utils.jdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Demo03 {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = jdbcUtils.getConnection();
            //区别 ： 使用 ? 占位符
            String str = "update users set birthday = ?  where name = ?";

            //预编译 SQL ，先写 SQL 然后赋值
            preparedStatement = connection.prepareStatement(str);
            //赋值
            preparedStatement.setString(1,"2020-12-25");
            preparedStatement.setString(2,"王冰冰");

            //执行
            int i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("更新成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(connection,preparedStatement,null);
        }
    }
}

image-20210207162852807

查询数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
package com.baixf.lesson03;

import com.baixf.lesson02.utils.jdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Demo06 {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = jdbcUtils.getConnection();
            //区别 ： 使用 ? 占位符
            String str = "select * from users where name = ?";

            //预编译 SQL ，先写 SQL 然后赋值
            preparedStatement = connection.prepareStatement(str);
            //赋值
            preparedStatement.setString(1,"王冰冰");
            //执行并使用数据集接收数据
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                System.out.println("查询成功！");
                System.out.println(resultSet.getObject("id"));
                System.out.println(resultSet.getObject("NAME"));
                System.out.println(resultSet.getObject("PASSWORD"));
                System.out.println(resultSet.getObject("email"));
                System.out.println(resultSet.getObject("birthday"));
                System.out.println("=====================================");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(connection,preparedStatement,null);
        }
    }
}

image-20210207163555593

防止注入问题

PreparedStatement 对象防止SQL注入的本质：把传递的参数当作字符

假设存在转义字符，将直接被忽略 ，例如 ‘ (引号)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
package com.baixf.lesson03;

import com.baixf.lesson02.utils.jdbcUtils;

import java.sql.*;

public class Demo07 {
    // 防止 SQL 注入
    public static void main(String[] args) throws SQLException {
        //login("baixf","password");
        login("zhansan","123456");
        //login("","password");
        login("'or '1=1","'or '1=1");
    }

    public static void login(String username, String password) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 建立连接
            connection = jdbcUtils.getConnection();
            // 预编译 sql语句
            String str = "SELECT * FROM `users` WHERE `NAME`=? && `PASSWORD`=?"; //Mybatis
            preparedStatement = connection.prepareStatement(str);
            //赋值
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
            //执行 SQL
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getObject("NAME"));
                System.out.println(resultSet.getObject("PASSWORD"));
                System.out.println("===================================");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            jdbcUtils.release(connection, preparedStatement, resultSet);
        }
    }
}

image-20210207164726143

六、使用IDEA连接数据库

1.建立连接

image-20210207165459982

image-20210207170240272

image-20210207171513022

2.连接成功后选择数据库

image-20210207171551215

3.查看数据库中的内容

image-20210207171653980

4.CRU操作

image-20210207172906572

5.其他操作

image-20210207173116722

image-20210207174100024

七、事务

要么都成功，要么都失败！

1.ACID原则

  • 原子性：要么全部完成，要么不完成
  • 一致性：总数不变
  • 隔离性：多个进程互不干扰
  • 持久性：一旦提交不可逆

2.代码实现

1.开启事务。

2.一组业务执行完毕，提交事务。

3.可以在catch语句中显示的定义 回滚语句，但默认失败就会回滚。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
package com.baixf.lesson04;

import com.baixf.utils.jdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Transaction {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        try {
            //关闭数据库自动提交
            connection = jdbcUtils.getConnection();
            //关闭数据库自动提交，自动开启事务
            connection.setAutoCommit(false);//开启事务
            String sql1 = "UPDATE ACCOUNT SET `money` = `money` - 100 WHERE NAME='A'";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();

            String sql2 = "UPDATE ACCOUNT SET `money` = `money` + 100 WHERE NAME='B'";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();
            //业务完毕，提交事务
            connection.commit();
            System.out.println("成功！");
        } catch (SQLException throwables) {
            connection.rollback();//如果失败则回滚
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

image-20210207175955037

失败案例

回滚！

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
package com.baixf.lesson04;

import com.baixf.utils.jdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Demo01 {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        try {
            //关闭数据库自动提交
            connection = jdbcUtils.getConnection();
            //关闭数据库自动提交，自动开启事务
            connection.setAutoCommit(false);//开启事务
            String sql1 = "UPDATE ACCOUNT SET `money` = `money` - 100 WHERE NAME='A'";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();
            int x=1/0;
            String sql2 = "UPDATE ACCOUNT SET `money` = `money` + 100 WHERE NAME='B'";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();
            //业务完毕，提交事务
            connection.commit();
            System.out.println("成功！");
        } catch (SQLException throwables) {
            connection.rollback();//如果失败则回滚
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

image-20210207180147051

八、数据库连接池

数据库连接–执行完毕–释放

连接 —》释放十分浪费资源

池化技术：准备一些预先的资源，过来就连接预先准备好的

常用连接数：10

最小连接数：10

最大连接数：100

编写连接池，实现一个接口：Datasource

1.开源数据源实现

使用了这些数据库连接池，项目中就不需要编写连接数据库的代码！

  • DBCP (需要用到commons-dbcp-1.4.jar、commons-pool-1.6.jar包)

  • C3P0 (需要用到c3p0-0.9.5.5.jar、mchange-commons-java-0.2.19.jar包)

  • Druid:Alibaba

2.jdbcUtils_DBCP.properties

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
#连接设置 DBCP数据源定义！
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/bank
username=root
password=123456

#初始化连接
initialSize=10

#最大连接数量
maxActive=50

#最大空闲连接
maxIdle=20

#最小空闲连接
minIdle=5

#超时等待时间
maxWait=60000

#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;]
#注意：“user” 与 “password” 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
package com.baixf.utils;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class jdbcUtils_DBCP {

    private static DataSource dataSource = null;
    static {
        try {
            InputStream in = jdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            //创建数据源 工厂模式 -- 》 创建
            dataSource = BasicDataSourceFactory.createDataSource(properties);


        }catch (Exception e){
            e.printStackTrace();
        }
    }
    // 2 获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//从数据源中获取连接
    }

    // 3 释放连接
    public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if(resultSet!=null) {
            try {
                resultSet.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }
}

3.c3p0-config.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<c3p0-config>
  <!-- 使用默认的配置读取连接池对象 -->
  <default-config>
      <!--  连接参数 -->
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
    <property name="user">root</property>
    <property name="password">root</property>
    
    <!-- 连接池参数 -->
    <!--初始化申请的连接数量-->
    <property name="initialPoolSize">5</property>
    <!--最大的连接数量-->
    <property name="maxPoolSize">10</property>
    <!--超时时间-->
    <property name="checkoutTimeout">3000</property>
  </default-config>

  <named-config name="otherc3p0"> 
    <!--  连接参数 -->
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
    <property name="user">root</property>
    <property name="password">root</property>
    
    <!-- 连接池参数 -->
    <property name="initialPoolSize">5</property>
    <property name="maxPoolSize">8</property>
    <property name="checkoutTimeout">1000</property>
  </named-config>
</c3p0-config>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
package com.itheima.utils;

import java.io.IOException;
import java.sql.*;
import java.util.Properties;

//自定义的JDBC工具类, 结合配置文件使用.
public class JDBCUtils2 {
    //1. 构造方法私有化.
    private JDBCUtils2(){}

    //2. 定义一些私有的静态的成员变量, 用来记录配置文件中的信息.
    private static String driverClass;
    private static String url;
    private static String username;
    private static String password;

    //3. 定义方法loadProperties(), 用来读取配置文件, 并将读取到的数据赋值给变量.
    public static void loadProperties() {
        //3.1 定义Properties集合
        Properties pp = new Properties();
        //3.2 加载配置文件中的文件到集合中.
        try {
            pp.load(JDBCUtils2.class.getClassLoader().getResourceAsStream("config.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        //3.3 给成员变量赋值.
        driverClass = pp.getProperty("driverClass");
        url = pp.getProperty("url");
        username = pp.getProperty("username");
        password = pp.getProperty("password");
    }

    //4. 通过静态代码块, 用来注册驱动.
    static {
        try {
            //核心细节: 方法只有被调用, 才会执行.
            //调用loadProperties()方法, 读取配置文件.
            loadProperties();
            //注册驱动.
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //5. 对外提供一个公共的访问方式, 用来获取: 连接对象.
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //6. 释放资源.
    public static void release(Connection conn, Statement stat, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
                rs = null;       //GC会优先回收null对象.
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stat != null) {
                    stat.close();
                    stat = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (conn != null) {
                        conn.close();
                        conn = null;
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void release(Connection conn, Statement stat) {
        try {
            if (stat != null) {
                stat.close();
                stat = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}


参考文章

  1. 狂神说B站视频 ↩
  2. MySQL官网 ↩
  3. MySQL教程 ↩
学习 > Java
#Java #MySQL
JDBC学习
https://blog.baixf.shop/2021/02/07/Java/JDBC学习/
作者
白小飞
发布于
2021年2月7日
许可协议