需要代码在java中创建连接池
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/2826212/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Need Code to create Connection Pool in java
提问by mohan
Need code to create the connection pool in java? How does we make sure that connection pool doesn't return the same object which is already in use? How happens if client closed the connection after taking it out from Connection pool?
需要代码在java中创建连接池吗?我们如何确保连接池不返回已在使用的相同对象?如果客户端从连接池中取出连接后关闭连接会怎样?
Update 1:
更新 1:
I want to create this in Simple Java terms and want to see how it works in Multithreading Env. I mean which methods would be synchronized and which are not. Also will this class would be a public class? If yes then any one can access this class and reinitialize the connection pool?
我想用简单的 Java 术语创建它,并想看看它在多线程环境中是如何工作的。我的意思是哪些方法会同步,哪些不会。这门课也会是公开课吗?如果是,那么任何人都可以访问此类并重新初始化连接池?
Update 2:
更新 2:
I have some code as below. But i don't how "Closing a connection coming from a pool returns it to the pool, it doesn't close the connection physically." Also i didn't understood this "Because if a connection has been borrowed from the pool and not returned yet, it's not "available" and can't be redistributed to another client of the pool."
我有一些代码如下。但我不知道“关闭来自池的连接将其返回到池中,它不会物理关闭连接。” 我也不明白这个“因为如果连接已从池中借用但尚未返回,则它不“可用”并且无法重新分配给池的另一个客户端。
import java.util.*;
import java.sql.*;
class ConnectionPoolManager
{
String databaseUrl = "jdbc:mysql://localhost:3306/myDatabase";
String userName = "userName";
String password = "userPass";
Vector connectionPool = new Vector();
public ConnectionPoolManager()
{
initialize();
}
public ConnectionPoolManager(
//String databaseName,
String databaseUrl,
String userName,
String password
)
{
this.databaseUrl = databaseUrl;
this.userName = userName;
this.password = password;
initialize();
}
private void initialize()
{
//Here we can initialize all the information that we need
initializeConnectionPool();
}
private void initializeConnectionPool()
{
while(!checkIfConnectionPoolIsFull())
{
System.out.println("Connection Pool is NOT full. Proceeding with adding new connections");
//Adding new connection instance until the pool is full
connectionPool.addElement(createNewConnectionForPool());
}
System.out.println("Connection Pool is full.");
}
private synchronized boolean checkIfConnectionPoolIsFull()
{
final int MAX_POOL_SIZE = 5;
//Check if the pool size
if(connectionPool.size() < 5)
{
return false;
}
return true;
}
//Creating a connection
private Connection createNewConnectionForPool()
{
Connection connection = null;
try
{
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(databaseUrl, userName, password);
System.out.println("Connection: "+connection);
}
catch(SQLException sqle)
{
System.err.println("SQLException: "+sqle);
return null;
}
catch(ClassNotFoundException cnfe)
{
System.err.println("ClassNotFoundException: "+cnfe);
return null;
}
return connection;
}
public synchronized Connection getConnectionFromPool()
{
Connection connection = null;
//Check if there is a connection available. There are times when all the connections in the pool may be used up
if(connectionPool.size() > 0)
{
connection = (Connection) connectionPool.firstElement();
connectionPool.removeElementAt(0);
}
//Giving away the connection from the connection pool
return connection;
}
public synchronized void returnConnectionToPool(Connection connection)
{
//Adding the connection from the client back to the connection pool
connectionPool.addElement(connection);
}
public static void main(String args[])
{
ConnectionPoolManager ConnectionPoolManager = new ConnectionPoolManager();
}
}
回答by mhaller
Use one of the existing, e.g. Apache DBCP
使用现有的一种,例如Apache DBCP
The connections returned by the pool are often proxies which "ignore" the call to close()
from the application. When the connections are returned to the pool, they can be reused. Pools also close and reopen automatically if necessary.
池返回的连接通常是代理,它们“忽略”close()
来自应用程序的调用。当连接返回到池中时,它们可以被重用。如有必要,池也会自动关闭并重新打开。
回答by Pascal Thivent
Need code to create the connection pool in java?
需要代码在java中创建连接池吗?
Not sure what the question is but don't create yet another connection pool, use an existing solution like C3P0, Apache DBCP, Proxoolor BoneCP(a new player in that field). I would use C3P0.
不确定问题是什么,但不要创建另一个连接池,使用现有的解决方案,如C3P0、Apache DBCP、Proxool或BoneCP(该领域的新玩家)。我会使用 C3P0。
How does we make sure that connection pool doesn't return the same object which is already in use?
我们如何确保连接池不返回已在使用的相同对象?
Because if a connection has been borrowed from the pool and not returned yet, it's just not in the pool and can't be assigned to another client of the pool (resources are removed from the pool until they are returned).
因为如果一个连接已经从池中借用并且还没有返回,它只是不在池中并且不能分配给池的另一个客户端(资源从池中移除,直到它们被返回)。
How happens if client closed the connection after taking it out from Connection pool?
如果客户端从连接池中取出连接后关闭连接会怎样?
The connection a client gets from a pool is not really a java.sql.Connection
, it's a wrapper (a proxy) for a java.sql.Connection
that customizes the behavior of some methods. The close()
method is one of them and does notclose the Connection
instance but returns it to the pool.
客户端从池中获得的连接并不是真正的java.sql.Connection
,它java.sql.Connection
是定制某些方法行为的 的包装器(代理)。该close()
方法是其中之一,不会关闭Connection
实例而是将其返回到池中。
回答by Phani
If your application runs on a server, then configure as Data Source, where server will take care of Pooling or else if a simple Java Client then use Apache DBCP(if to the database) or else use Apache Commons Pooling API See here: Apache Commons
如果您的应用程序在服务器上运行,则配置为数据源,其中服务器将负责池化,否则如果是简单的 Java 客户端,则使用 Apache DBCP(如果是数据库)或使用 Apache Commons Pooling API 参见此处:Apache Commons
回答by Ryan Elkins
Don't write your own. There are plenty of librarires out there that will do this for you that are open source and easy to use and will have solved all the problems you'll encounter trying to make it yourself.
不要自己写。有很多库可以为您做到这一点,它们是开源且易于使用的,并且已经解决了您在尝试自己制作时遇到的所有问题。
Here is a simple example that uses Apache's Commons DBCP and Commons Pool:
这是一个使用 Apache 的 Commons DBCP 和 Commons Pool 的简单示例:
First set up a DataSource.
首先设置一个DataSource。
javax.sql.DataSource source = new org.apache.commons.dbcp.BasicDataSource();
source.setDriverClassName("com.mysql.jdbc.Driver");
source.setUsername("username");
source.setPassword("password");
source.setUrl("jdbc:mysql://localhost:3306/myDatabase");
Once you have a DataSource, it is easy to get a connection from the pool.
一旦有了数据源,就很容易从池中获取连接。
java.sql.Connection connection = source.getConnection();
closing the connection will return it to the pool for you.
关闭连接会将其返回到池中。
connection.close();
回答by Jonathan Cole
One argument for rolling your own connpool is the configuration and additional jars that are avoided. I agree you need to enable the 3rd party interfaces so you can swap in a mature connpool, but having your own tiny solution can have its place. Self cleaning Vector with synchronized block and a conn wrapper with close() marking the conn as available works very well for servlet apps.
滚动你自己的 connpool 的一个论点是配置和避免的额外 jars。我同意您需要启用 3rd 方接口,以便您可以在成熟的 connpool 中进行交换,但是拥有自己的小型解决方案也可以占有一席之地。带有同步块和带有 close() 的 conn 包装器的自清洁 Vector 将 conn 标记为可用,非常适用于 servlet 应用程序。
回答by Princesh
Just use Semaphores. Ideally what you should do is use CP3O
or DBCP
as your connection pool. Now you can throttle you connection based on Semaphore.
只需使用信号量。理想情况下,您应该做的是使用CP3O
或DBCP
作为您的连接池。现在您可以根据信号量限制您的连接。
Every time you do Get
, you acquire and on every Release
you release it from Semaphore. More over semaphores are thread safe.
每次你这样做Get
,你都会Release
从信号量中获取并释放它。更多的信号量是线程安全的。
回答by Jagadeesh
I hope this source code helps http://jagadeeshmanne.blogspot.in/2014/03/connection-pool-in-java-jdbc.html
我希望这个源代码有帮助 http://jagadeeshmanne.blogspot.in/2014/03/connection-pool-in-java-jdbc.html
Configuration.java
配置文件
package com.jmanne.utils;
public class Configuration {
public String DB_USER_NAME ;
public String DB_PASSWORD ;
public String DB_URL;
public String DB_DRIVER;
public Integer DB_MAX_CONNECTIONS;
public Configuration(){
init();
}
private static Configuration configuration = new Configuration();
public static Configuration getInstance(){
return configuration;
}
private void init(){
DB_USER_NAME = "root"
DB_PASSWORD = "root"
DB_URL = "jdbc:mysql://localhost:3306/jmanne"
DB_DRIVER = "com.mysql.jdbc.Driver"
DB_MAX_CONNECTIONS = 5
}
}
JdbcConnectionPool.java
JdbcConnectionPool.java
package com.jmanne.db;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.jmanne.utils.Configuration;
import com.mysql.jdbc.Connection;
public class JdbcConnectionPool {
List<connection> availableConnections = new ArrayList<connection>();
public JdbcConnectionPool()
{
initializeConnectionPool();
}
private void initializeConnectionPool()
{
while(!checkIfConnectionPoolIsFull())
{
availableConnections.add(createNewConnectionForPool());
}
}
private synchronized boolean checkIfConnectionPoolIsFull()
{
final int MAX_POOL_SIZE = Configuration.getInstance().DB_MAX_CONNECTIONS;
if(availableConnections.size() < MAX_POOL_SIZE)
{
return false;
}
return true;
}
//Creating a connection
private Connection createNewConnectionForPool()
{
Configuration config = Configuration.getInstance();
try {
Class.forName(config.DB_DRIVER);
Connection connection = (Connection) DriverManager.getConnection(
config.DB_URL, config.DB_USER_NAME, config.DB_PASSWORD);
return connection;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public synchronized Connection getConnectionFromPool()
{
Connection connection = null;
if(availableConnections.size() > 0)
{
connection = (Connection) availableConnections.get(0);
availableConnections.remove(0);
}
return connection;
}
public synchronized void returnConnectionToPool(Connection connection)
{
availableConnections.add(connection);
}
}
DataSource.java
数据源.java
package com.jmanne.db;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
public class DataSource {
static JdbcConnectionPool pool = new JdbcConnectionPool();
public static Connection getConnection() throws ClassNotFoundException, SQLException{
Connection connection = pool.getConnectionFromPool();
return connection;
}
public static void returnConnection(Connection connection) {
pool.returnConnectionToPool(connection);
}
}
回答by Vrajendra Singh Mandloi
I have one solution for the same to create a connection pool utility which can help you create a pool of default size 10.
我有一个相同的解决方案来创建一个连接池实用程序,它可以帮助您创建一个默认大小为 10 的池。
@Component public class ConnectionPool { private static final Logger logger = LoggerFactory.getLogger(ConnectionPool.class); private static final int MAX_POOL_SIZE_LIMIT = 10; private BlockingQueue activeConnectinoQueue = new LinkedBlockingQueue<>(); private BlockingQueue usedConnectinoList = new LinkedBlockingQueue<>(); private int initialPoolSize = 5;
@Component public class ConnectionPool { private static final Logger logger = LoggerFactory.getLogger(ConnectionPool.class); 私有静态最终 int MAX_POOL_SIZE_LIMIT = 10; private BlockingQueue activeConnectinoQueue = new LinkedBlockingQueue<>(); private BlockingQueue usedConnectinoList = new LinkedBlockingQueue<>(); 私有 int initialPoolSize = 5;
@Autowired
@Qualifier("dataSource")
private DataSource dataSource;
public void initConnectionPool() {
logger.info("ConnectionPool initialization started.");
if(activeConnectinoQueue.isEmpty() && usedConnectinoList.isEmpty()) {
for (int i=0; i<initialPoolSize; i++) {
createConnections();
}
}
logger.info("ConnectionPool initialization completed. ConnectionPool size : {}", activeConnectinoQueue.size());
}
private void createConnections() {
try {
Connection connection = dataSource.getConnection();
activeConnectinoQueue.add(connection);
}catch (SQLException e) {
logger.error("Error in getting connection from pool : ", e);
}
}
public Connection getConnection() {
if(activeConnectinoQueue.isEmpty()) {
initConnectionPool();
}
Connection connection = activeConnectinoQueue.remove();
try {
if(connection.isClosed()) {
connection = dataSource.getConnection();
}
}catch (SQLException e) {
logger.error("Error while getting connection from pool : ", e);
}
usedConnectinoList.add(connection);
return connection;
}
public void releaseConnection(Connection connection) {
if(connection != null) {
usedConnectinoList.remove(connection);
activeConnectinoQueue.add(connection);
}
}
public void setInitialPoolSize(int initialPoolSize) {
if(!(initialPoolSize < 0 || initialPoolSize > MAX_POOL_SIZE_LIMIT)) {
this.initialPoolSize = initialPoolSize;
}
}
public int getInitialPoolSize() {
return initialPoolSize;
}
public int getConnectionPoolSize() {
return activeConnectinoQueue.size() + usedConnectinoList.size();
}
public void setDataSource(AbstractDataSource dataSource) {
this.dataSource = dataSource;
}
public void closeConnectionPool() {
logger.info("Closing connectionPool started.");
close(usedConnectinoList);
close(activeConnectinoQueue);
logger.info("ConnectionPool Closed.");
}
private void close(BlockingQueue<Connection> connectinosQueue) {
for (int i=0; i<connectinosQueue.size(); i++) {
Connection connection = connectinosQueue.remove();
if(connection != null) {
try {
connection.close();
} catch (SQLException e) {
logger.error("Error in initializing connection pool : ", e);
}
}
}
}
}
}
now To make it safe we need to attach a factory object..
现在为了安全起见,我们需要附加一个工厂对象..
public enum ConnectionFactory {
CONNECTION;
private ConnectionPool connectionPool;
public void setConnectionPool(ConnectionPool connectionPool) {
this.connectionPool = connectionPool;
}
public Connection getConnection() {
return connectionPool.getConnection();
}
public void closeConnection() {
connectionPool.closeConnectionPool();
}
public void releaseConnection(Connection connection) {
connectionPool.releaseConnection(connection);
}
public int getConnectionPoolSize() {
return connectionPool.getConnectionPoolSize();
}
@Component
public static class ConnectionBuilder {
@Autowired
private ConnectionPool connectionPool;
public void setConnectionPool(ConnectionPool connectionPool) {
this.connectionPool = connectionPool;
}
@PostConstruct
public void postConstruct() {
for (ConnectionFactory cfactory : EnumSet.allOf(ConnectionFactory.class)) {
cfactory.setConnectionPool(connectionPool);
}
}
}
}
}
回答by Md. Nasir Uddin
Java Connection Pool ?
There are three way to create JDBC Connection Pooling is very easy...
Java 连接池?
有三种方法可以创建 JDBC 连接池很容易...
Apache Commons DBCP
public class DBCPDataSource { private static BasicDataSource ds = new BasicDataSource(); static { ds.setUrl("jdbc:h2:mem:test"); ds.setUsername("user"); ds.setPassword("password"); ds.setMinIdle(5); ds.setMaxIdle(10); ds.setMaxOpenPreparedStatements(100); } public static Connection getConnection() throws SQLException { return ds.getConnection(); } private DBCPDataSource(){ } }
Now you can get connection
Connection con = DBCPDataSource.getConnection();
HikariCP
public class HikariCPDataSource { private static HikariConfig config = new HikariConfig(); private static HikariDataSource ds; static { config.setJdbcUrl("jdbc:h2:mem:test"); config.setUsername("user"); config.setPassword("password"); config.addDataSourceProperty("cachePrepStmts", "true"); config.addDataSourceProperty("prepStmtCacheSize", "250"); config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048"); ds = new HikariDataSource(config); } public static Connection getConnection() throws SQLException { return ds.getConnection(); } private HikariCPDataSource(){} }
Apache Commons DBCP
public class DBCPDataSource { private static BasicDataSource ds = new BasicDataSource(); static { ds.setUrl("jdbc:h2:mem:test"); ds.setUsername("user"); ds.setPassword("password"); ds.setMinIdle(5); ds.setMaxIdle(10); ds.setMaxOpenPreparedStatements(100); } public static Connection getConnection() throws SQLException { return ds.getConnection(); } private DBCPDataSource(){ } }
现在你可以连接
Connection con = DBCPDataSource.getConnection();
HikariCP
public class HikariCPDataSource { private static HikariConfig config = new HikariConfig(); private static HikariDataSource ds; static { config.setJdbcUrl("jdbc:h2:mem:test"); config.setUsername("user"); config.setPassword("password"); config.addDataSourceProperty("cachePrepStmts", "true"); config.addDataSourceProperty("prepStmtCacheSize", "250"); config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048"); ds = new HikariDataSource(config); } public static Connection getConnection() throws SQLException { return ds.getConnection(); } private HikariCPDataSource(){} }
Now you can get connection
现在你可以连接
Connection con = HikariCPDataSource.getConnection();
C3PO
public class C3poDataSource { private static ComboPooledDataSource cpds = new ComboPooledDataSource(); static { try { cpds.setDriverClass("org.h2.Driver"); cpds.setJdbcUrl("jdbc:h2:mem:test"); cpds.setUser("user"); cpds.setPassword("password"); } catch (PropertyVetoException e) { // handle the exception } } public static Connection getConnection() throws SQLException { return cpds.getConnection(); } private C3poDataSource(){} }
C3PO
public class C3poDataSource { private static ComboPooledDataSource cpds = new ComboPooledDataSource(); static { try { cpds.setDriverClass("org.h2.Driver"); cpds.setJdbcUrl("jdbc:h2:mem:test"); cpds.setUser("user"); cpds.setPassword("password"); } catch (PropertyVetoException e) { // handle the exception } } public static Connection getConnection() throws SQLException { return cpds.getConnection(); } private C3poDataSource(){} }
Now you can get connection
现在你可以连接
Connection con = C3poDataSource.getConnection();
回答by kalpesh
I have some model code in Java which has connection pool with multithreading.
我在 Java 中有一些模型代码,它具有多线程连接池。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.concurrent.ConcurrentLinkedQueue;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;
abstract class ObjectPool<T> {
private ConcurrentLinkedQueue<T> pool;
ScheduledExecutorService executorService;
ObjectPool(int minObjects) {
pool = new ConcurrentLinkedQueue<T>();
for (int i = 0; i < minObjects; i++) {
pool.add(createObject());
}
}
ObjectPool(final int minObjects, final int maxSize, final long interval){
pool = new ConcurrentLinkedQueue<T>();
for (int i = 0; i < minObjects; i++) {
pool.add(createObject());
}
executorService = Executors.newSingleThreadScheduledExecutor();
executorService.scheduleWithFixedDelay(new Runnable(){
public void run() {
int size = pool.size();
while(size > maxSize){
pool.remove();
}
Iterator i = pool.iterator();
while(i.hasNext()){
T t = (T) i.next();
if(checkExpiry(t)){
System.out.println("Expiry existed...");
i.remove();
}
}
while(pool.size() < minObjects){
System.out.println("Adding more objects to pool");
pool.add(createObject());
}
}
}, interval, interval, TimeUnit.MILLISECONDS);
}
public T borrowObject() {
if (pool.peek() == null)
return createObject();
return pool.remove();
}
public void addObject(T obj) {
if (obj == null)
return;
pool.add(obj);
}
public abstract T createObject();
public abstract boolean checkExpiry(T t);
}
class MultithreadQuery extends Thread{
private ObjectPool<Connection> pool;
private int threadNo;
String query;
MultithreadQuery(ObjectPool<Connection> pool,int threadNo, String query){
this.pool = pool;
this.threadNo = threadNo;
this.query = query;
}
@Override
public void run(){
Connection con = pool.borrowObject();
Statement stmt;
try {
stmt = con.createStatement();
System.out.println("Query started for thread->"+ threadNo);
ResultSet rs=stmt.executeQuery(query);
while(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
System.out.println("closing connection....");
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
pool.addObject(con);
System.out.println("Query ended for thread->"+ threadNo);
}
}
public class ObjectPoolPatternDemo {
ObjectPool<Connection> pool;
public void setUp(){
pool = new ObjectPool<Connection>(4, 10, 1) {
@Override
public Connection createObject() {
Connection con;
try {
con = DriverManager.getConnection("URL","Username","Password");
return con;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
@Override
public boolean checkExpiry(Connection conn) {
boolean expiryFlag = false;
try {
if(conn.isClosed())
expiryFlag = true;
} catch (SQLException e) {
e.printStackTrace();
}
return expiryFlag;
}
};
}
public static void main(String[] args) throws SQLException {
ObjectPoolPatternDemo oppd = new ObjectPoolPatternDemo();
oppd.setUp();
ExecutorService es = Executors.newFixedThreadPool(4);
String query = "select * from TABLE";
es.execute(new MultithreadQuery(oppd.pool,1,query));
es.execute(new MultithreadQuery(oppd.pool,2,query));
es.execute(new MultithreadQuery(oppd.pool,3,query));
es.execute(new MultithreadQuery(oppd.pool,4,query));
es.execute(new MultithreadQuery(oppd.pool,5,query));
es.execute(new MultithreadQuery(oppd.pool,6,query));
es.shutdown();
try {
es.awaitTermination(1, TimeUnit.DAYS);
} catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("finally completed...");
}
}