17JavaSE数据库连接池

PV:

一、c3p0连接池

1.导包(lib 下)

数据库连接池

c3p0

machange-commons-java

MySQL驱动

mysql-connector-java

2.核心方法

1
2
3
4
5
6
7
8
9
10
11
12
13
// 核心连接池类

                  ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();

                  // 设置四个JDBC基本连接属性

                  comboPooledDataSource.setDriverClass("com.mysql.jdbc.Driver");

                  comboPooledDataSource.setJdbcUrl("jdbc:mysql:///test");

                  comboPooledDataSource.setUser("root");

                  comboPooledDataSource.setPassword("111");

3.jdbc.properties配置文件

1
2
3
4
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mysql
username=root
password=111

4.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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

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

public class JDBCUtils {

    private static final String driverClass;
    private static final String url;
    private static final String username;
    private static final String password;

    static {
        Properties prop = new Properties();

        try {
            prop.load(new FileReader("jdbc.properties"));

            driverClass = prop.getProperty("driverClass");
            url = prop.getProperty("url");
            username = prop.getProperty("username");
            password = prop.getProperty("password");

            // 加载驱动
            loadDriver();

        } catch (IOException e) {
            // e.printStackTrace();
            throw new RuntimeException("配置文件加载失败!");
        }
    }

    // 加载驱动
    public static void loadDriver() {
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            // e.printStackTrace();
            throw new RuntimeException("驱动加载失败!");
        }
    }

    // 获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    // 释放资源
    public static void release(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = null;
        }

        release(conn, stmt);
    }

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

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
}

5.测试

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
// 测试类
public class JDBCTemplateTest1 {

    @Test
    public void test1() throws SQLException, PropertyVetoException {

        // 需求 : 查询 user 表中的所有数据

        // 核心连接池类
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        // 设置四个JDBC基本连接属性
        dataSource.setDriverClass("com.mysql.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/mydb?serverTimezone=UTC&characterEncoding=utf-8");
        dataSource.setUser("root");
        dataSource.setPassword("111");

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            // 1. 建立连接
            conn = dataSource.getConnection();
            // 2. 操作数据
            String sql = "select * from user;";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                String email = rs.getString("email");
                System.out.println(id + " : " + username + " : " + password + " : " + email);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 3. 释放资源
            JDBCUtils.release(conn, stmt, rs);
        }
    }
}

6.通过xml 获取配置信息

ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource(); 会自定加载配置文件

#常用基本连接池属性

acquireIncrement 如果连接池中连接都被使用了，一次性增长3个新的连接
maxPoolSize 最大连接池中连接数量默认：15连接
maxIdleTime 如果连接长时间没有时间，将被回收默认：0 连接永不过期
minPoolSize 连接池中最小连接数量 默认：3
initialPoolSize 连接池中初始化连接数量默认: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
@Test
public void test_c3p0() throws PropertyVetoException {
	// 需求 : 查询 user 表中的所有数据
	
	// 核心连接池类
	ComboPooledDataSource dataSource = new ComboPooledDataSource();
	
	Connection conn = null;
	PreparedStatement stmt = null;
	ResultSet rs = null;
	
	try {
		// 1. 建立连接
		conn = dataSource.getConnection();
		// 2. 操作数据
		String sql = "select * from user;";
		stmt = conn.prepareStatement(sql);
		rs = stmt.executeQuery();
		while (rs.next()) {
			int id = rs.getInt("id");
			String username = rs.getString("username");
			String password = rs.getString("password");
			String email = rs.getString("email");
			System.out.println(id + " : " + username + " : " + password + " : " + email);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		// 3. 释放资源
		JDBCUtils.release(conn, stmt, rs);
	}
}

7.优化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
优化版的JDBCUtils 中的getConnectio 使用数据库连接池对象方式实现
  public class JDBCUtils {
	
	// c3p0 数据库连接池对象属性
	private static final ComboPooledDataSource dataSource = new ComboPooledDataSource();
	
	// 获取连接
	public static Connection getConnection() throws SQLException {
		return dataSource.getConnection();
	}
	
	// 释放资源
	public static void release(Connection conn, Statement stmt, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
		release(conn, stmt);
	}
	
	public static void release(Connection conn, Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			stmt = null;
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn = null;
		}
	}
}

测试

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
@Test
public void test_jdbcUtils() {
	
	// 需求 : 查询 user 表中的所有数据
	
	Connection conn = null;
	PreparedStatement stmt = null;
	ResultSet rs = null;
	
	try {
		// 1. 建立连接
		conn = JDBCUtils.getConnection();
		// 2. 操作数据
		String sql = "select * from user;";
		stmt = conn.prepareStatement(sql);
		rs = stmt.executeQuery();
		while (rs.next()) {
			int id = rs.getInt("id");
			String username = rs.getString("username");
			String password = rs.getString("password");
			String email = rs.getString("email");
			System.out.println(id + " : " + username + " : " + password + " : " + email);
		}
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		// 3. 释放资源
		JDBCUtils.release(conn, stmt, rs);
	}
}

Powered by Hexo and Hexo-theme-hiker

Copyright © 2016 - 2018 Francis的个人博客 All Rights Reserved.

UV : | PV :