Mysql-JDBC详解

字数统计: 7.7k字   |   阅读时长≈ 35分钟

Mysql-JDBC详解

1. 简介

  • Sun公司为了简化开发人员的（对数据库统一）的操作，提供了一个（java操作数据库的）规范，俗称JDBC。

  • 这些规范的实现由具体的厂商去做。

  • 对于开发人员来说，只需要掌握JDBC接口即可

2. 第一个 JDBC程序

  • 接下来用一个程序来引入jdbc
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.zhuuu;

import java.sql.*;

public class FirstJDBC {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1. 加载驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2. 用户信息和url
        String url = "jdbc:mysql://localhost:3306/mybatis?userUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";

        //3. 连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url, username, password);

        //4. 执行SQL对象 执行SQL的对象
        Statement statement = connection.createStatement();

        //5. 执行SQL的对象，去执行SQL,可能存在结果，查看返回结果
        String sql = "select * from student";

        ResultSet resultSet = statement.executeQuery(sql);    // 结果集中封装了所有查询的结果

        while (resultSet.next()){
            System.out.println("id=" + resultSet.getObject("id"));
            //System.out.println("id=" + resultSet.getObject("name"));
            //System.out.println("id=" + resultSet.getObject("password"));

        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结：

  1. 加载驱动
  2. 连接数据库DriverManger
  3. 获取执行sql的对象 statement
  4. 获得返回的结果集
  5. 释放连接

DriverManger

1
2
3
4
5
6
7
Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载驱动(还有一种写法不建议使用)

Connection connection = DriverManager.getConnection(url, username, password); // connection代表数据库（功能）
// 数据库设置自动提交
// 事务提交
// 事务回滚
connetion.rollback();

url

1
2
3
String url = "jdbc:mysql://localhost:3306/mybatisuserUnicode=true&characterEncoding=utf8&useSSL=true";

//jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3

Statement ：执行SQL对象 

1
2
3
4
5
String sql = "需要的sql语句";

statement.executeQuery(sql); //查询操作返回ResultSet
statement.execute(sql); // 执行任何sql
statement.executeUpdate(sql);// 更新，插入，删除，都是用这个，返回一个受影响的行数

ResultSet 查询的结果集：封装了所有的查询结果(数据结构是一个链表的形式)

resultSet.getObject(); (增删改查都是他做)

resultSet.next(); 移动到下一个数据

释放资源必须做：

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

2.1 编写统一工具类 (jdbc_Utils)

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
package com.zhuuu.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Date;
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 {
          // 读取配置的properties并且封装到对象中 
            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 (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    // 2. 获取链接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    //3. 释放资源
    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();
            }
        }
    }
}

2.2 编写db.properties（解耦）

  • 以上的数据库连接属性可以封装到properties文件中
1
2
3
4
driver=com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
username=root
password=123456

2.3 测试增删改查

  1. 测试插入(insert)
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
package com.zhuuu.JdbcTest;

import com.zhuuu.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 {
            // 1. 获取数据库连接
            Connection connection = JdbcUtils.getConnection();
            // 2. 获取SQL执行对象
            st = conn.createStatement();
          
            // 3. 执行sql并拿到返回结果
            String sql = "insert into school.teacher(id,`name`) values('2','徐老师')";
            int i = st.executeUpdate(sql);
            if (i > 0) System.out.println("插入表成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
          	// 4. 释放链接
            JdbcUtils.release(conn,st,rs);
        }
    }
}
  1. 测试删除（delete）
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
package com.zhuuu.JdbcTest;

import com.zhuuu.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 {
            //获取数据库连接
            Connection connection = JdbcUtils.getConnection();
            //获取SQL执行对象
            st = conn.createStatement();

            String sql = "需要删除的内容";

            int i = st.executeUpdate(sql);
            if (i > 0) System.out.println("插入表成功！");

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}
  1. 测试更新（update）
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.zhuuu.JdbcTest;

import com.zhuuu.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 {
            //获取数据库连接
            Connection connection = JdbcUtils.getConnection();
            //获取SQL执行对象
            st = conn.createStatement();

            String sql = "需要更新的的内容";

            int i = st.executeUpdate(sql);
            if (i > 0) System.out.println("插入表成功！");

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }

}
  1. 执行查询（Query）
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
package com.zhuuu.JdbcTest;

import com.zhuuu.utils.JdbcUtils;

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 {
            Connection connection = JdbcUtils.getConnection();
            st = conn.createStatement();

            // 只需要改SQL
            String sql = "select * from school.teacher where id=1";
            rs = st.executeQuery(sql);
            // 返回的对象是一个链表的数据结构
          	// 返回的数据可以根据数据库表的列名来进行取出数据
            if (rs.next()){
                System.out.println(rs.getString("name"));
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

3. SQL 注入

问题：用户名和密码可以是用户传进来的，造成字符串拼接，从而产生错误（or）

  1. 前期准备工作:首先，创建一张试验用的数据表：
1
2
3
4
5
6
7
8
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(64) NOT NULL,
`password` varchar(64) NOT NULL,
`email` varchar(64) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
  • 添加一条测试记录：
1
2
INSERT INTO users (username,password,email)
VALUES('MarcoFly',md5('test'),'marcofly@test.com');
  • 创建前台登录页面：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<html>
<head>
<title>Sql注入演示</title>
<meta http-equiv="content-type" content="text/html;charset=utf-8">
</head>
<body >
<form action="validate.php" method="post">
<fieldset >
<legend>Sql注入演示</legend>
<table>
<tr>
<td>用户名：</td><td><input type="text" name="username"></td>
</tr>
<tr>
<td>密  码：</td><td><input type="text" name="password"></td>
</tr>
<tr>
<td><input type="submit" value="提交"></td><td><input type="reset" value="重置"></td>
</tr>
</table>
</fieldset>
</form>
</body>
</html>
  • 附上效果图：

  • 当用户点击提交按钮的时候，将会把表单数据提交给validate.php页面，validate.php页面用来判断用户输入的用户名和密码有没有都符合要求（这一步至关重要，也往往是SQL漏洞所在）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
!                                         <!--前台和后台对接-->
<html>
<head>
<title>登录验证</title>
<meta http-equiv="content-type" content="text/html;charset=utf-8">
</head>
<body>
<?php
$conn=@mysql_connect("localhost",'root','') or die("数据库连接失败！");;
mysql_select_db("injection",$conn) or die("您要选择的数据库不存在");
$name=$_POST['username'];
$pwd=$_POST['password'];
$sql="select * from users where username='$name' and password='$pwd'";
$query=mysql_query($sql);
$arr=mysql_fetch_array($query);
if(is_array($arr)){
header("Location:manager.php");
}else{
echo "您的用户名或密码输入有误，<a href=\"Login.php\">请重新登录！</a>";
}
?>
</body>
</html>
  • 注意到了没有，我们直接将用户提交过来的数据(用户名和密码)直接拿去执行，并没有实现进行特殊字符过滤，待会你们将明白，这是致命的。
  • 代码分析：如果，用户名和密码都匹配成功的话，将跳转到管理员操作界面(manager.php)，不成功，则给出友好提示信息。
  • 登录成功的界面：

  • 登录失败的提示：

到这里，前期工作已经做好了，接下来将展开我们的重头戏：SQL注入

  1. 构造SQL语句
  • 填好正确的用户名(marcofly)和密码(test)后，点击提交，将会返回给我们“欢迎管理员”的界面。
  • 因为根据我们提交的用户名和密码被合成到SQL查询语句当中之后是这样的：
    select * from users where username='marcofly' and password=md5('test')
  • 很明显，用户名和密码都和我们之前给出的一样，肯定能够成功登陆。但是，如果我们输入一个错误的用户名或密码呢？很明显，肯定登入不了吧。恩，正常情况下是如此，但是对于有SQL注入漏洞的网站来说，只要构造个特殊的“字符串”，照样能够成功登录。
  • 比如：在用户名输入框中输入:' or 1=1# ,密码随便输入，这时候的合成后的SQL查询语句为：
    select * from users where username='' or 1=1#' and password=md5('')
  • 语义分析：“#”在mysql中是注释符，这样井号后面的内容将被mysql视为注释内容，这样就不会去执行了，换句话说，以下的两句sql语句等价：
1
select * from users where username='' or 1=1#' and password=md5('')
  • 等价于
1
select* from users where usrername='' or 1=1
  • 因为1=1永远是都是成立的，即where子句总是为真，将该sql进一步简化之后，等价于如下select语句：
    • select * from users
    • 没错，该sql语句的作用是检索users表中的所有字段

再次构造语句：

1
select * from users where username='admin'#' and password=md5('')
  • 等价于
1
select * from users where username='admin'
  • 这样即可不能输入密码登入上去的。
  • 数据库就会错认为不用用户名既可以登入，绕过后台的验证，已到达注入的目的。
  • 同样利用了SQL语法的漏洞。

4. ParpareStatement (防止sql注入)

  • 作用：ParpareStatement 可以防止SQL注入，效率更高
  • 注意：对于mysql的日期的时间格式
    • new java.sql.Date(new Date().getTime())
    • 与java中的Date是不一样的
  1. 测试插入
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
package com.zhuuu.prepareState;

import com.zhuuu.utils.JdbcUtils;

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

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;


        try {
            JdbcUtils.getConnection();

            // 1. 和之前的区别 : 使用问号占位符代替参数
          	// 原本所有的参数是直接写在sql中的
            String sql = "insert into school.teacher(id,`name`) values(?,?)";

            st= conn.prepareStatement(sql); // 预编译sql 但不执行

            //2/ 手动给参数赋值
            st.setInt(1,4);
            st.setString(2,"朱酱酱");

            //3. 执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}
  1. 测试删除
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.zhuuu.prepareState;

import com.zhuuu.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 {
            JdbcUtils.getConnection();

            //区别
            String sql = "delete from school.teacher where id = ?";

            st= conn.prepareStatement(sql); // 预编译sql 但不执行

            //手动给参数赋值
            st.setInt(1,4);

            //执行
            int i = st.executeUpdate();
            
            if (i>0){
                System.out.println("删除成功");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}
  1. 测试更新
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.zhuuu.prepareState;

import com.zhuuu.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 {
            JdbcUtils.getConnection();

            //1. 区别
            String sql = "update .....";
            st= conn.prepareStatement(sql); // 预编译sql 但不执行

            //2. 手动给参数赋值
            st.setInt(1,4);

            //3. 执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}
  1. 测试查询(完美防止sql注入)
  • 本质：防止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
package com.zhuuu.prepareState;

import com.zhuuu.utils.JdbcUtils;

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

public class TestQuery {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;


        try {
            JdbcUtils.getConnection();

            //1. 区别
            String sql = "select * from school.result where id = ? and password = ?";

            st= conn.prepareStatement(sql); // 预编译sql 但不执行

            //2. 手动给参数赋值
            st.setInt(1,4);

            //3. 执行
            ResultSet rs = st.executeQuery();
            if (rs.next()){
                System.out.println(rs.getString("name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

5. JDBC 事务

  • 事务 ：一句话：要么都成功，要么都失败
  1. 原子性：要么都成功，要么都失败
  2. 一致性：总数不变
  3. 隔离性：多个进程互不干扰
  4. 持久性：一旦提交就不可逆，持久化到数据库
  • 隔离性的问题：
    • 脏读：一个事务读取了另一个没有提交的事务
    • 不可重复读：在同一个事务内，重复读取表中的数据，数据发生了改变
    • 虚读（幻读）：在一个事务内，插入了新的数据
    • 丢失更新：一个食物提交的数据被另一个事务所覆盖。
  • JDBC模拟银行转账失败（代码实现）
    • 结果：两条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
public class TestTransction {
    public static void main(String[] args) throws SQLException {
        
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();
          
          	// 1. 开启事务
            conn.setAutoCommit(false);//开启事务，关闭自动提交

          	// 2. 连接数据库进行数据处理
            String sql1 = "update account set money = money + 100 where name ='A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

          	// 3. 模拟事务失败
            int x = 1/0;
            System.out.println("===============");

          	// 4. 第二件个数据进行处理
            String sql2 = "update account set money = money - 100 where name ='B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            // 5. 业务完毕，提交事务
            conn.commit();
            System.out.printf("成功！");
        } catch (SQLException e) {
            conn.rollback();              // 失败就回滚
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

小结：

  1. 开启事务
  2. 执行sql ，业务执行完毕提交事务
  3. 在catch 语句中显示的定义回滚语句（默认失败就会回滚）

6. JDBC 数据库连接池

池化技术

  • 和线程池差不多
  • 最小连接数，最大连接数
    • 最小：常用的连接数量
    • 最大：最大的业务承载能力
  • 等待连接数（等待超时）
    • 超过时间释放连接

目的：

  • 提前准备好资源，等请求来了直接给他使用
  • 避免了：连接和释放连接的开销

==只要编写一个实现类，实现DataSource接口即可==

  • SpringBoot默认使用hikrari
  • 实现DataSource接口即可 （如果自己编写连接池的话）

6.0 三种数据库连接池（druid、c3p0、dbcp）

  • 使用数据库连接池，可以不用再写连接数据库创建连接的代码了！！！

6.1 DPCP

  • DBCP(DataBase connection pool)数据库连接池是 apache 上的一个Java连接池项目。
  • DBCP通过连接池预先同数据库建立一些连接放在内存中(即连接池中)，应用程序需要建立数据库连接时直接到从接池中申请一个连接使用，用完后由连接池回收该连接，从而达到连接复用，减少资源消耗的目的。
  • properties 来实现

项目结构如下图所示：

  1. DBCP配置文件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
########DBCP配置文件##########
#驱动名
driverClassName=com.mysql.jdbc.Driver
#url
url=jdbc:mysql://127.0.0.1:3306/mydb
#用户名
username=sa
#密码
password=123456
#初试连接数
initialSize=30
#最大活跃数
maxTotal=30
#最大idle数
maxIdle=10
#最小idle数
minIdle=5
#最长等待时间(毫秒)
maxWaitMillis=1000
#程序中的连接不使用后是否被连接池回收(该版本要使用removeAbandonedOnMaintenance和removeAbandonedOnBorrow)
#removeAbandoned=true
removeAbandonedOnMaintenance=true
removeAbandonedOnBorrow=true
#连接在所指定的秒数内未使用才会被删除(秒)(为配合测试程序才配置为1秒)
removeAbandonedTimeout=1
  1. 创建初始化DBCP的类KCYDBCPUtil.java
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
package dbcp;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSourceFactory;

/**
 * DBCP配置类
 * @author SUN
 */
public class KCYDBCPUtil {

  	// 与之前的jdbc_utils相比，所有的数据库连接都放在了配置文件里面
    private static Properties properties = new Properties();
    private static DataSource dataSource;
    // 1. 加载DBCP配置文件
    static{
        try{
            FileInputStream is = new FileInputStream("config/dbcp.properties");
            properties.load(is);
        }catch(IOException e){
            e.printStackTrace();
        }
        try{
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    // 2. 从连接池中获取一个连接
    public static Connection getConnection(){
        Connection connection = null;
        try{
            connection = dataSource.getConnection();
        }catch(SQLException e){
            e.printStackTrace();
        }
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void main(String[] args) {
        getConnection();
    }
}

//  原来的写法
		// 2. 获取链接
    //public static Connection getConnection() throws SQLException {
     //   return DriverManager.getConnection(url, username, password);
   // }
  1. 创建使用JDBC获取数据库连接的类DBConn.java（用于和DBCP连接池对比）
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
// 之前麻烦的写法
package dbcp;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConn {
    private static Connection conn = null;

    //获取一个数据库连接
    public static Connection getConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            DriverManager.registerDriver(new com.mysql.jdbc.Driver());
            String dbUrl = "jdbc:mysql://127.0.0.1:3306/mydb";
            conn = DriverManager.getConnection(dbUrl, "sa", "123456");
//            System.out.println("========数据库连接成功========");
        } catch (Exception e) {
            e.printStackTrace();
//            System.out.println("========数据库连接失败========");
            return null;
        }
        return conn;
    }
}
  1. 测试
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
package dbcp;

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

import org.junit.Test;

public class DBCPTest {

    //测试,每写一条数据前,就新建一个连接
    @Test
    public void testWriteDBByEveryConn() throws Exception{
        for(int i = 0; i < 2000; i++){
            writeDBByEveryConn(i);
        }
        System.out.println("DONE");

    }

    //测试,使用连接池,每写一条数据前,从连接池中获取一个连接
    @Test
    public void testWriteDBByDBCP() throws Exception{
        for(int i = 0; i < 2000; i++){
            writeDBByDBCP(i);
        }
        System.out.println("DONE");
    }

    //测试,只建一条连接,写入所有数据
    @Test
    public void testWriteDBByOneConn() throws Exception{
        Connection conn = DBConn.getConnection();
        Statement stat = conn.createStatement();
        for(int i = 0; i < 2000; i++){
            writeDBByOneConn(i, stat);
        }
        conn.close();
        System.out.println("DONE");
    }

    //不使用连接池写数据库,每写一条数据创建一个连接
    public void writeDBByEveryConn(int data){
        String sql = "insert into dbcp values (" + data + ")";
        Connection conn = DBConn.getConnection();
        try{
            Statement stat = conn.createStatement();
            stat.executeUpdate(sql);
        }catch(Exception e){
            e.printStackTrace() ;
        }finally{
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

    //不使用连接池写数据库,只用一个连接,写所有数据
    public void writeDBByOneConn(int data, Statement stat){
        String sql = "insert into dbcp values (" + data + ")";
        try{
            stat.executeUpdate(sql);
        }catch(Exception e){
            e.printStackTrace() ;
        }
    }

    //通过DBCP连接池写数据库
    public void writeDBByDBCP(int data){
        String sql = "insert into dbcp values (" + data + ")";
        try {
            Connection conn = KCYDBCPUtil.getConnection();
            Statement stat = conn.createStatement();
            stat.executeUpdate(sql);
            conn.commit();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

测试结果如下：

(1) 每次插入一条数据前，就创建一个连接，该条数据插入完成后，关闭该连接。耗时158.318秒

(2) 使用DBCP连接池，每次插入一条数据前，从DBCP连接池中获取一条连接，该条数据插入完成后，该连接交由DBCP连接池管理。耗时122.404秒

(3) 在插入数据之前创建一条连接，2000个数据全部使用该连接，2000个数据插入完毕后，关闭该连接。耗时117.87秒

小结：

  • 通过对比结果看出，向同一个表中插入2000条数据，每插入一条数据前创建一个新连接，会非常耗时，而使用DBCP连接池和使用同一个连接操作，耗时比较接近。

  • 应用程序中，使用完一个数据库连接后，DBCP连接池如何管理该连接。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
分两种情况：

　　　 (1) 应用程序中主动关闭该连接，即DBCPTest.java中第79行  conn.close();

　　　　　这种情况并不是手动将该连接关闭，而是将该连接交回给DBCP连接池，由连接池管理该连接。即用完连接后显示的将数据库连接提交至DBCP连接池。

　　　 (2) 应用程序中不关闭该连接，即将DBCPTest.java中第79行  conn.close()注释掉

　　　　　这种情况DBCP配置文件dbcp.properties中的配置项(注意jar包版本，低版本中使用removeAbandoned=true配置项)　

　　　　　removeAbandonedOnMaintenance=true
     　　　removeAbandonedOnBorrow=true

　　　　  removeAbandonedTimeout=1会起作用
　　　　  
　　　　  removeAbandonedOnMaintenance=true和removeAbandonedOnBorrow=true表示DBCP连接池自动管理应程序中使用完毕的连接，removeAbandonedTimeout=1表示一个连接在程序中使用完毕后，若在1秒之内没有再次使用，则DBCP连接池回收该连接(通常removeAbandonedTimeout不会配置1，此处为了测试使用)。

6.2 C3P0

  • 传统方式问题1:开销大

    • 普通的JDBC数据库连接使用DriverManager来获取，每次向数据库建立连接的时候都要将Connection加载到内存中，再根据JDBC代码（或配置文件）中的用户名和密码进行验证其正确性。
    • 这一过程一般会花费0.05~1s,一旦需要数据库连接的时候就必须向数据库请求一个，执行完后再断开连接。显然，如果同一个数据库在同一时间有数十人甚至上百人请求连接势必会占用大量的系统资源，严重的会导致服务器崩溃。

  • 传统方式问题2:内存泄露危险

    • 因为每一次数据库连接使用完后都需要断开连接，但如果程序出现异常致使连接未能及时关闭，这样就可能导致内存泄漏，最终只能以重启数据库的方法来解决；
    • 另外使用传统JDBC模式开发不能控制需要创建的连接数，系统一般会将资源大量分出给连接以防止资源不够用，如果连接数超出一定数量也会有极大的可能导致内存泄漏。

  • xml文件来配置数据库连接

使用方式：

  1. 导入jar包

  1. 配置xml文件 ： 下面是我配置的c3p0-config.xml，可以作为模板以供大家参考：
  • 当然，除了以上这几种常用的参数设置以外，这里还有一份有关c3p0-config.xml参数的详细清单，如有需要可自行增加。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
  <default-config>
    <!--mysql数据库连接的各项参数-->
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/day06</property>
    <property name="user">root</property>
    <property name="password">root</property>
    <!--配置数据库连接池的初始连接数、最小链接数、获取连接数、最大连接数、最大空闲时间-->
    <property name="initialPoolSize">10</property>
    <property name="minPoolSize">10</property>
    <property name="acquireIncrement">5</property>
    <property name="maxPoolSize">100</property>
    <property name="maxIdleTime">30</property>
  </default-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
<c3p0-config>   
    <default-config>   
    <!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->   
    <property name="acquireIncrement">3</property>   
 
    <!--定义在从数据库获取新连接失败后重复尝试的次数。Default: 30 -->   
    <property name="acquireRetryAttempts">30</property>   
       
    <!--两次连接中间隔时间，单位毫秒。Default: 1000 -->   
    <property name="acquireRetryDelay">1000</property>   
       
    <!--连接关闭时默认将所有未提交的操作回滚。Default: false -->   
    <property name="autoCommitOnClose">false</property>   
       
    <!--c3p0将建一张名为Test的空表，并使用其自带的查询语句进行测试。如果定义了这个参数那么   
    属性preferredTestQuery将被忽略。你不能在这张Test表上进行任何操作，它将只供c3p0测试   
    使用。Default: null-->   
    <property name="automaticTestTable">Test</property>   
       
    <!--获取连接失败将会引起所有等待连接池来获取连接的线程抛出异常。但是数据源仍有效   
    保留，并在下次调用getConnection()的时候继续尝试获取连接。如果设为true，那么在尝试   
    获取连接失败后该数据源将申明已断开并永久关闭。Default: false-->   
    <property name="breakAfterAcquireFailure">false</property>   
       
    <!--当连接池用完时客户端调用getConnection()后等待获取新连接的时间，超时后将抛出   
    SQLException,如设为0则无限期等待。单位毫秒。Default: 0 -->   
    <property name="checkoutTimeout">100</property>   
       
    <!--通过实现ConnectionTester或QueryConnectionTester的类来测试连接。类名需制定全路径。   
    Default: com.mchange.v2.c3p0.impl.DefaultConnectionTester-->   
    <property name="connectionTesterClassName"></property>   
       
    <!--指定c3p0 libraries的路径，如果（通常都是这样）在本地即可获得那么无需设置，默认null即可   
    Default: null-->   
    <property name="factoryClassLocation">null</property>   
       
    <!--强烈不建议使用该方法，将这个设置为true可能会导致一些微妙而奇怪的bug-->   
    <property name="forceIgnoreUnresolvedTransactions">false</property>   
       
    <!--每60秒检查所有连接池中的空闲连接。Default: 0 -->   
    <property name="idleConnectionTestPeriod">60</property>   
       
    <!--初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间。Default: 3 -->   
    <property name="initialPoolSize">3</property>   
       
    <!--最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->   
    <property name="maxIdleTime">60</property>   
       
    <!--连接池中保留的最大连接数。Default: 15 -->   
    <property name="maxPoolSize">15</property>   
       
    <!--JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements   
    属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。   
    如果maxStatements与maxStatementsPerConnection均为0，则缓存被关闭。Default: 0-->   
    <property name="maxStatements">100</property>   
       
    <!--maxStatementsPerConnection定义了连接池内单个连接所拥有的最大缓存statements数。Default: 0 -->   
    <property name="maxStatementsPerConnection"></property>   
       
    <!--c3p0是异步操作的，缓慢的JDBC操作通过帮助进程完成。扩展这些操作可以有效的提升性能   
    通过多线程实现多个操作同时被执行。Default: 3-->   
    <property name="numHelperThreads">3</property>   
       
    <!--当用户调用getConnection()时使root用户成为去获取连接的用户。主要用于连接池连接非c3p0   
    的数据源时。Default: null-->   
    <property name="overrideDefaultUser">root</property>   
       
    <!--与overrideDefaultUser参数对应使用的一个参数。Default: null-->   
    <property name="overrideDefaultPassword">password</property>   
       
    <!--密码。Default: null-->   
    <property name="password"></property>   
       
    <!--定义所有连接测试都执行的测试语句。在使用连接测试的情况下这个一显著提高测试速度。注意：   
    测试的表必须在初始数据源的时候就存在。Default: null-->   
    <property name="preferredTestQuery">select id from test where id=1</property>   
       
    <!--用户修改系统配置参数执行前最多等待300秒。Default: 300 -->   
    <property name="propertyCycle">300</property>   
       
    <!--因性能消耗大请只在需要的时候使用它。如果设为true那么在每个connection提交的   
    时候都将校验其有效性。建议使用idleConnectionTestPeriod或automaticTestTable   
    等方法来提升连接测试的性能。Default: false -->   
    <property name="testConnectionOnCheckout">false</property>   
       
    <!--如果设为true那么在取得连接的同时将校验连接的有效性。Default: false -->   
    <property name="testConnectionOnCheckin">true</property>   
       
    <!--用户名。Default: null-->   
    <property name="user">root</property>   
       
    <!--早期的c3p0版本对JDBC接口采用动态反射代理。在早期版本用途广泛的情况下这个参数   
    允许用户恢复到动态反射代理以解决不稳定的故障。最新的非反射代理更快并且已经开始   
    广泛的被使用，所以这个参数未必有用。现在原先的动态反射与新的非反射代理同时受到   
    支持，但今后可能的版本可能不支持动态反射代理。Default: false-->   
    <property name="usesTraditionalReflectiveProxies">false</property> 
    </default-config>      
</c3p0-config>
  1. 创建C3P0Util类
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
package com.c3p0.utils;
 
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
import javax.sql.DataSource;
 
import com.mchange.v2.c3p0.ComboPooledDataSource;
 
public class C3P0Util {
    //使用ComboPooledDataSource来生成DataSource的实例
    private static DataSource dataSource = new ComboPooledDataSource();
 
    //从连接池中获取连接
    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            throw new RuntimeException();
        }
    }
 
    //释放连接回连接池
    public static void release(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
}
  1. 数据对象
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
package com.c3p0.utils;
 
import java.io.Serializable;
import java.util.Date;
 
public class User implements Serializable {
    private int id;
    private String username;
    private String password;
    private String email;
    private Date birthday;
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public String getUsername() {
        return username;
    }
 
    public void setUsername(String username) {
        this.username = username;
    }
 
    public String getPassword() {
        return password;
    }
 
    public void setPassword(String password) {
        this.password = password;
    }
 
    public String getEmail() {
        return email;
    }
 
    public void setEmail(String email) {
        this.email = email;
    }
 
    public Date getBirthday() {
        return birthday;
    }
 
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
 
    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", password="
                + password + ", email=" + email + ", birthday=" + birthday
                + "]";
    }
 
}
//　　（注：后面的测试程序也是调用此表，我们就对表中的数据进行了封装，后台数据库也是此类结构，这里不再展示）
  1. 测试
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
package com.c3p0.utils;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
 
import org.junit.Test;
 
public class TestCRUD {
    @Test
    public void testInsert() {
        Connection conn = null;
        PreparedStatement ps = null;
        conn = C3P0Util.getConnection();
        try {
            ps = conn.prepareStatement("INSERT INTO users (username,PASSWORD,email,birthday)VALUES('SUN99','123','123456@qq.com','2020-01-01')");
            ps.executeUpdate();
            System.out.println("添加操作执行成功！");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.out.println("添加操作执行失败！");
        } finally {
            C3P0Util.release(conn, ps, null);
        }
    }
 
    @Test
    public void testSelect() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        conn = C3P0Util.getConnection();
        try {
            ps = conn.prepareStatement("Select * from users");
            rs = ps.executeQuery();
            List<User> list = new ArrayList<User>();
            while (rs.next()) {
                User u = new User();
                u.setId(rs.getInt(1));
                u.setUsername(rs.getString(2));
                u.setPassword(rs.getString(3));
                u.setEmail(rs.getString(4));
                u.setBirthday(rs.getDate(5));
                list.add(u);
            }
            for (User user : list) {
                System.out.println(user.toString());
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            C3P0Util.release(conn, ps, rs);
        }
    }
 
    @Test
    public void testDelete() {
        Connection conn = null;
        PreparedStatement ps = null;
        conn = C3P0Util.getConnection();
        try {
            ps = conn.prepareStatement("delete from users where username='SUN99'");
            ps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            C3P0Util.release(conn, ps, null);
        }
    }
 
    @Test
    public void testUpdate() {
        Connection conn = null;
        PreparedStatement ps = null;
        conn = C3P0Util.getConnection();
        try {
            ps = conn.prepareStatement("UPDATE users SET username='SUN100',PASSWORD='456'WHERE id='1'");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            C3P0Util.release(conn, ps, null);
        }
    }
}

小结：

  • C3P0通过dataSource.getConnection()从线程池中获取“空闲”连接，真正的数据库连接创建与释放则是由C3P0在后台自行完成的，我们只花费了获取和释放连接占用权的时间；
  • 使用c3p0-config.xml代替原来JDBC硬编码的形式，提高了代码复用性。

6.3 Druid

打赏
  • 版权声明： 本博客所有文章除特别声明外，均采用 Apache License 2.0 许可协议。转载请注明出处！
  • © 2019-2022 Zhuuu
  • PV: UV:

请我喝杯咖啡吧~

支付宝
微信