Java topic Database connection pool, manually create connection pool+dbcp+C3P0+Druid+dbUtil

connection pool

(database connection pool)
Closing the link every time you link to the database is rather cumbersome and wastes time and resources. Now we can get the database link through the connection pool, and put it back after use up, which can save the time and operation of constantly creating and closing the connection, which is more efficient. Now the general connection pool can set the configuration of the connection pool by itself.

Create a connection pool manually

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ujiuye.util.JdbcUtil;

public class MyConPool {
public static int initConut=5;//connection pool capacity
public static List<Connection> list;//connection pool
static {
	list=new ArrayList<Connection>();
	for (int i = 0; i <initConut; i++) {
		Connection con=JdbcUtil.getConnection();
		list.add(con);
	}
}
public static Connection getConnection() {
	if (list.isEmpty()) {
		System.out.println("The connection in the connection pool is used up and must wait for someone else to return it");
		return null;
	}
	Connection con=list.get(0);
	list.remove(0);//Take one out, one less in the pool
	
	return  con;
}
public static void close(Connection con ,Statement sm,ResultSet rs) {
	list.add(con);//returned to the connection pool
	try {
		if (sm!=null) {
			sm.close();
		}
		if (null!=rs) {
			rs.close();
		}
	} catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
		
	}
}
}

@Test
public void test1() {
	Connection con1=MyConPool.getConnection();
	Connection con2=MyConPool.getConnection();
	Connection con3=MyConPool.getConnection();
	Connection con4=MyConPool.getConnection();
	Connection con5=MyConPool.getConnection();
	MyConPool.close(con5, null, null);
	Connection con6=MyConPool.getConnection();
	System.out.println(con6);
	System.out.println(con5);
	System.out.println(con6==con5);
}

dbcp

DataSource: the core class

Required jar package



Configuration file: Note that the key name is fixed (the first word), and the name of the configuration file can be written casually

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/databaseName?useUnicode=true&characterEncoding=utf-8
username=root
password=123456
initialSize=5
maxActive=10
maxWait=3000
maxIdle=8
minIdle=3

Create datasource datasource

 BasicDataSourceFactory.createDataSource(properties)
public class DBCPUtil {
public static DataSource ds=null;
static {
	Properties  p=new Properties();
	try {
		p.load(new FileInputStream("src/dbcp.properties"));
//		InputStream is=DBCPUtil.class.getClassLoader().
//		getResourceAsStream("dbcp.properties");
//		p.load(is);
		//Use the dbcp tool class to obtain data sources
		ds= BasicDataSourceFactory.createDataSource(p);
	}  catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
public static Connection getConnection() {
	try {
		return ds.getConnection();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return null;
}
}

c3p0

It is also an open source database connection pool

the name of the resource file
c3p0.properties
Configuration of resource files:

c3p0.driverClass=com.mysql.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql://localhost:3306/java0708?useUnicode=true&characterEncoding=utf-8
c3p0.user=root
c3p0.password=123456
c3p0.maxPoolSize=20
c3p0.minPoolSize=3
c3p0.maxStatements=30
c3p0.maxIdleTime=150

Required jar package


The Java configuration connection pool information is as follows:

ComboPooledDataSource dataSource = new  ComboPooledDataSource();

It should be noted that the name of the resource file and the key of the content are fixed. If it is changed, it will not be accessible directly. We need to manually read the resource file here;

public class C3P0Util {
public static ComboPooledDataSource  ds;
static {
	ds=new ComboPooledDataSource();
}

public static Connection getConnection() {
	try {
		return ds.getConnection();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return null;
}
}

Druid (Alibaba)

Required jar package

Create a data source Datasource

//p is the properties object of the configured properties file
DataSource dataSource = DruidDataSourceFactory.createDataSource(p);
public class DRUIDUtil {
public static DataSource ds;
static {
	Properties p=new Properties();
	try {
		p.load(new FileInputStream("src/dbcp.properties"));
		//Get the data source object
		ds=DruidDataSourceFactory.createDataSource(p);
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
public static Connection getConnection() {
	try {
		return ds.getConnection();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return  null;
}
}

DbUtil(CRUD)

Required jar package

jar package released by Apache
QueryRunner qr=new QueryRunner(datasource)

qr.update(sql,? parameter list (...)) return value int type, affecting several lines
Inquire
qr.query(sql statement, ? parameter list, return value type)

Return value type:
ArrayHandler returns an array
ArrayListHandler returns a collection
BeanHandler returns an object
BeanListHandler returns a collection of objects
ScalarHandler returns a field, generally query aggregate function

Bean , model , vo are the package names used by the entity object created corresponding to the database table

case

public class DbutilTest {
public QueryRunner  qr=new QueryRunner(C3P0Util.ds);

/**Additions, deletions and modifications
 * @throws SQLException
 */
public void testUpdate() throws SQLException {
	int res=qr.update("insert counts(name,money) values(?,?)", "admin",10000);
	System.out.println("influence"+res+"OK");
}
/**Get the auto-incremented primary key while inserting
 * @throws SQLException
 */

public void testUpdategetKey() throws SQLException {
  long key=	qr.insert("insert counts(name,money) values(?,?)", new ScalarHandler<Long>(),"jack",10000);
  System.out.println("Primary key value:"+key);
}
/**ArrayHandler,Returns all columns of a piece of data as an array
 * @throws SQLException
 */

public void test3() throws SQLException {
	Object[] o = qr.query("select*from counts where id=?", new ArrayHandler(),1);
	System.out.println(Arrays.toString(o));
}

public  void test4() throws SQLException {
	  List<Object[]> list = qr.query("select*from counts", new ArrayListHandler());
	  for(Object[] o:list) {
		  System.out.println(Arrays.toString(o));
	  }
}
/**new BeanHandler<>(Counts.class)  Automatically load query results into objects
 * @throws SQLException
 */
public void test5() throws SQLException {
	Counts c = qr.query("select*from counts where id=?",new BeanHandler<>(Counts.class), 1);
	System.out.println(c.getName());
	System.out.println(c);
}
/**new BeanListHandler<>(Counts.class)  Encapsulated as a collection of objects
 * @throws SQLException
 */

public void test6() throws SQLException {
	List<Counts> list = qr.query("select*from counts", new BeanListHandler<>(Counts.class));
	for(Counts c:list) {
		System.out.println(c.getId()+"--"+c.getName()+"--"+c.getMoney());
	}
}
/**ScalarHandler  Queries generally used for aggregate functions
 * @throws SQLException
 */
@Test
public void test7() throws SQLException {
	int max = qr.query("select max(money) from counts", new ScalarHandler<>());
	System.out.println(max);
}
}

Tags: Java Database

Posted by beckjo1 on Sun, 22 May 2022 06:26:33 +0300