入门数据库连接数和连接池理解

最新推荐文章于 2025-06-16 20:33:18 发布

原创最新推荐文章于 2025-06-16 20:33:18 发布 · 710 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #java

后端同时被 3 个专栏收录

4 篇文章

订阅专栏

Java

2 篇文章

订阅专栏

MySQL

2 篇文章

订阅专栏

本文介绍了一种使用Java和线程池进行MySQL批量查询的优化方案，通过创建线程池和数据源，实现了对数据库的高效并发访问，对比了不同连接释放策略下查询效率的差异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、环境与配置

数据库：MySQL

版本：5.7.29

语言：Java

语言版本：JDK8

CPU：i5-7500

内存：16GB 2400Mhz

二、代码

喜欢干货的直接Copy，转载请注明出处。



import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Callable;
import java.util.concurrent.Executors;
import java.util.concurrent.FutureTask;
import java.util.concurrent.ThreadPoolExecutor;

import org.apache.commons.dbcp.BasicDataSource;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;


public class MysqlTest implements Callable<String> {

	static ThreadPoolExecutor executor = (ThreadPoolExecutor) Executors.newFixedThreadPool(8);
	
	static BasicDataSource dataSource = null;
	@Override
	public String call() throws Exception {
		Connection conn = dataSource.getConnection();
		for (int i = 0; i < 1000; i++) {
			JSONArray arr = new JSONArray();
			excuteSQL(arr, conn);
		}
		return "1";
	}
	public static void main(String[] args) {
		Connection conn = null;
		dataSource = new BasicDataSource();
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUrl("jdbc:mysql://192.168.5.42/rdp");
		dataSource.setUsername("root");
		dataSource.setPassword("****");
		//dataSource.setMaxActive(8000); Data source rejected establishment of connection,  message from server: "Too many connections"
		dataSource.setMaxActive(8);
		dataSource.setMinIdle(1);
		
		
		
		try {
			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			long c1  = System.currentTimeMillis();
			// 只使用一个连接且不释放的情况 total cost time = 4496
			//conn = DriverManager.getConnection("jdbc:mysql://192.168.5.42/rdp?user=root&password=****&useSSL=false");
			// 没有使用线程池的情况 不释放连接 total cost time = 4637
			/*conn = dataSource.getConnection();
			for (int i = 0; i < 8000; i++) {
				// 使用8个链接数，释放的情况 total cost time = 4689
				//conn = dataSource.getConnection();
				JSONArray arr = new JSONArray();
				excuteSQL(arr, conn);
			}*/
			// 8连接数使用8线程池的情况  total cost time = 1656
			List<FutureTask<String>> list = new ArrayList<>();
			for (int i = 0; i < 8; i++) {
				FutureTask<String> futureTask = new FutureTask<String>(new MysqlTest());
				executor.execute(futureTask);
				list.add(futureTask);
			}
			executor.shutdown();
			for (int i = 0; i < 8; i++) {
				if ("1".equals(list.get(i).get())) {
					//System.out.println(list.get(i).get());
				}
			}
			
			long c2  = System.currentTimeMillis() - c1;
			System.out.println("total cost time = " + c2);
		} catch (Exception e) {
			e.printStackTrace();
		} 
	}

	private static void excuteSQL(JSONArray arr, Connection conn) {
		Statement stmt = null;
		ResultSet rs = null;
		long l1 = System.nanoTime();
		long c1  = System.currentTimeMillis();
		//
		// Create a Statement instance that we can use for
		// 'normal' result sets assuming you have a
		// Connection 'conn' to a MySQL database already
		// available
		// CallableStatement cStmt = conn.prepareCall("SELECT * from user");
		// "{call demoSp(?, ?)}"
		// cStmt.registerOutParameter(2, Types.INTEGER);
		// cStmt.registerOutParameter("inOutParam", Types.INTEGER);
		// boolean hadResults = cStmt.execute();
		// while (hadResults) {
		// hadResults = cStmt.getMoreResults();
		// }
		try {
			String sql = "SELECT * from user limit 10";
			stmt = conn.createStatement();
			//rs = stmt.executeQuery(sql);
			stmt.execute(sql);
			rs = stmt.getResultSet();
//			PreparedStatement prepareStatement = conn.prepareStatement(sql);
//			rs = prepareStatement.executeQuery();
			//rs.last(); // 将游标移至最后一行
			
			ResultSetMetaData metaData = rs.getMetaData();
			while (rs.next()) {
				JSONObject json = new JSONObject();
				int count = metaData.getColumnCount();
				for (int i = 1; i <= count; i++) {
					String name = metaData.getColumnName(i);
					Object val = rs.getObject(name);
					json.put(name, val);
				}
				arr.add(json);
			}
			
			long l2 = System.nanoTime() - l1;
			long c2 = System.currentTimeMillis() - c1;
			//System.out.println("cost time(ms) = " + c2 +" ,naotime = " + l2);
			//System.out.println(arr.toJSONString());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException ex) {
					// ignore
				}
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException ex) {
					// ignore
				}
			}
			
//			if (conn != null) {
//				try {
//					conn.close();
//				} catch (SQLException ex) {
//					// ignore
//				}
//			}
		}

		//
		// Issue the DDL queries for the table for this example
		//

		// stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial");
		// stmt.executeUpdate(
		// "CREATE TABLE autoIncTutorial ("
		// + "priKey INT NOT NULL AUTO_INCREMENT, "
		// + "dataField VARCHAR(64), PRIMARY KEY (priKey))");

		//
		// Insert one row that will generate an AUTO INCREMENT
		// key in the 'priKey' field
		//

		// stmt.executeUpdate(
		// "INSERT INTO autoIncTutorial (dataField) "
		// + "values ('Can I Get the Auto Increment Field?')",
		// Statement.RETURN_GENERATED_KEYS);

		//
		// Example of using Statement.getGeneratedKeys()
		// to retrieve the value of an auto-increment
		// value
		//

		// int autoIncKeyFromApi = -1;
		//
		// rs = stmt.getGeneratedKeys();
		//
		// if (rs.next()) {
		// autoIncKeyFromApi = rs.getInt(1);
		// } else {
		//
		// // throw an exception from here
		// }
		//
		// System.out.println("Key returned from getGeneratedKeys():"
		// + autoIncKeyFromApi);
		
	}
}