Handwritten database connection and test
Recently, to connect to GP database (Greenplum SQL), you need to establish different number of connections
In fact, when you want to write database connection, you can write interface directly through springdata jpa, which is a kind of thinking
So in the use, I wrote a demo to test the time required to establish a connection, which really has a lot to do with the performance of the server. The specific relationship will be analyzed later
package com.trs.idap.config;
import org.springframework.stereotype.Component;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.LinkedList;
/**
* Created by Administrator on 2018/10/12.
* Description: custom database connection pool
* @author Young
* @create 2018-10-12 17:25
Code implementation:
1. MyPool.java Connection pool class,
2. Specify global parameters: number of initializations, maximum connections, current connections, connection pool collection
3. Constructor: loop to create 3 connections
4. Write a method to create a connection
5. Get connection
Judgment: if there is connection in the pool, take it directly
There is no connection in the pool,
Judge whether the maximum number of connections is reached; when the maximum number is reached, throw an exception; when the maximum number of connections is not reached,
Create a new connection
6. Release connection
Put the connection back into the collection (..)
*/
@Component
public class CustomConnectionPool {
private int intCount=50;
private int maxCount=20;
private int currentCount;
//Connection pool
private LinkedList<Connection> pool = new LinkedList<>();
//Constructor, initializing connection pool
public CustomConnectionPool() {
for (int i = 0 ;i<intCount;i++){
currentCount++;
pool.add(this.createConnection());
}
}
//Create a new connection method
private Connection createConnection() {
try {
//load driver
Class.forName("com.pivotal.jdbc.GreenplumDriver");
//Original target object
final Connection conn = DriverManager.getConnection("jdbc:pivotal:greenplum://192.168.1.228:5432;DatabaseName=idap3", "gpadmin", "gpadmin");
/**Proxy Connection object**/
//Create its proxy object for conn. Returns an instance of a proxy class for a specified interface
Connection proxy = (Connection) Proxy.newProxyInstance(
conn.getClass().getClassLoader(),// Defines the class loader for the proxy class. The object responsible for loading the class.
new Class[]{Connection.class},//List of interfaces to be implemented by proxy class
new InvocationHandler(){// Automatically triggers the transaction processor when the con object method is called
@Override
public Object invoke(Object proxy, Method method,
Object[] args) throws Throwable {
//Method returns
Object result = null;
// The method name of the currently executing method
String methodName = method.getName();
if("close".equals(methodName)){
//Put connection into connection pool
pool.add(conn);
}else{
result = method.invoke(conn, args);////Call target object method
}
return result;
}
}//Call handler to assign method calls
);
return proxy;//Return proxy object
} catch (Exception e) {
throw new RuntimeException(e);
}
}
//Create connection
public Connection getConnection(){
//3.1 judge whether there is a connection in the connection pool. If there is a connection, take it out directly from the connection pool
if(!pool.isEmpty()){
return pool.removeFirst();//Delete one and return the deleted object Connection
}
//3.2 no connection in connection pool: judge that if the maximum number of connections is not reached, create
if(currentCount < maxCount){
//Record the number of connections currently in use
currentCount++;
//Create connection
return this.createConnection();
}
//3.3 throw an exception if the current maximum number of connections has been reached
throw new RuntimeException("The current connection has reached the maximum number of connections");
}
/**
* 4.Release connection (manual release)
*/
public void realeaseConnection(Connection conn){
//4.1 judge that if the number of pools is less than the initial connection, it will be put into the pool
if(pool.size() < intCount){
pool.addLast(conn);
}else{
//4.2 close down
try {
currentCount--;//Current connections - 1
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
long l = System.currentTimeMillis();
CustomConnectionPool pool = new CustomConnectionPool();
System.out.println("Create 50 connection hours"+(System.currentTimeMillis()-l)/1000);
System.out.println("Connection pool:" + pool.pool.size() + "Current connection:" + pool.currentCount);
Connection connection = pool.getConnection();
System.out.println("Connection pool:" + pool.pool.size() + "Current connection:" + pool.currentCount);
Connection connection1 = pool.getConnection();
System.out.println("Connection pool:" + pool.pool.size() + "Current connection:" + pool.currentCount);
}
}
BTY:
In addition, since the connection coordinate of gp is not found in maven warehouse, it needs to be manually pressed into the local pom, so
Since Greenplum could not find a matching jar driver in Maven, it needs to build its own local warehouse
(1) cmd opens the running window
(2) enter the local project name \ lib directory
(3) run MVN install: install file - dfile = greenplus. Jar - dgroupid = lib. Greenplus - dartifactid = greenplus - dversion = 5.10.2 - dpackaging = jar
(4) add POM path to pom.xml of configuration Engineering
<dependency>
<groupId>lib.greenplum</groupId>
<artifactId>greenplum</artifactId>
<version>5.10.2</version>
</dependency>
Attach gp connection jar