Handwriting Database Connection Pool

1. I believe a lot of people read this article already know what connection pool is used for? Yes, the database connection pool is to establish a "buffer pool" for the database connection, pre-put a certain number of connections in the "buffer pool", when you need to establish a database connection, from the "buffer pool" to take out a, use and then put in. The benefit is that you can avoid frequent database connections that consume a lot of system resources.

2. Common database connection pools are: Dbcp,c3p0, Ali's druid. Well, gossip doesn't say much, this article is designed to deepen everyone's understanding of connection pooling. The database I chose here is MySQL.

3. First talk about the process of connection pooling:

1. First of all, there is a configuration file! When we use data sources in our daily projects, we need to configure database drivers, database user names, database passwords, and connections . These four characters must not be less.

I believe a lot of people read this article already know the connection pool is used for what? Yes, the database connection pool is to establish a "buffer pool" for the database connection, pre-put a certain number of connections in the "buffer pool", when you need to establish a database connection, from the "buffer pool" to take out a, use and then put in. The benefit is that you can avoid frequent database connections that consume a lot of system resources.
Common database connection pools are: Dbcp,c3p0, Ali's druid. Well, gossip doesn't say much, this article is designed to deepen everyone's understanding of connection pooling. The database I chose here is MySQL.
Let's talk about the process of connection pooling:
First of all, there is a configuration file! When we use data sources in our daily projects, we need to configure database drivers, database user names, database passwords, and connections. These four characters must not be less.

#文件名: Db.properties
Jdbc.driver=com.mysql.jdbc.driver
Jdbc.url=jdbc:mysql://localhost:3306/ssm
Jdbc.username=root
Jdbc.password=lfdy
Jdbc.initsize=3
jdbc.maxsize=10
#是否启动检查
Jdbc.health=true
#检查延迟时间
jdbc.delay=3000
#间隔时间
jdbc.period=3000
jdbc.timeout=100000

2. We will write a class based on the above configuration file db.properties and load its properties

public class Gpconfig {
Private String driver;
Private String URL;
Private String username;
private String password;
Private String initsize;
Private String maxSize;
Private String health;
private String delay;
Private String period;
Private String timeout;

Omit the set and Get methods//write the constructor, initialize the property in the constructor
Public Gpconfig () {
Properties prop = new properties ();
Read files in Maven project It seems like this is the only way
InputStream stream = This.getclass (). getResourceAsStream ("/resource/db.properties");
try {
Prop.load (stream);
Invoking the setter method in the constructor, where the property is more, we are certainly not a step-by-step call, it is recommended to use the reflection mechanism
For (Object Obj:prop.keySet ()) {
Get the formal parameter, how to get it? This is not the configuration file key removed, what to remove it? Remove "jdbc."
String fieldName = obj.tostring (). replace ("jdbc.", "");
Field field = This.getclass (). Getdeclaredfield (FieldName);
method = This.getclass (). GetMethod (ToUpper (FieldName), Field.gettype ());
Method.invoke (this, prop.get (obj));
}
} catch (Exception e) {
E.printstacktrace ();
}
}

Read the key in the configuration file and turn him into the correct set method
public string ToUpper (string fieldName) {
char[] chars = Fieldname.tochararray ();
Chars[0]-=32; How to capitalize the first letter of a string
Return "Set" + New String (chars);
}
}

3. OK, our config file is written, and the class that loads the configuration file is also written, what will be written next? Recall, we are not connected to the pool, is not using Class.forName (), getconnection and so on to connect the database? So, let's write a class that has a way to create a connection and get a connection.

public class Gppooldatasource {

Load Configuration Class
Gpconfig config = new Gpconfig ();

Write a parameter that marks how many active connections are currently in use
Private Atomicinteger currentactive = new Atomicinteger (0);

Create a collection, what do you do? To store the connection, after all, we need to create a initsize connection when we just initialized
And when we release the connection, we put the connection in here.
vector<connection> freepools = new vector<> ();

Connection pool in use
vector<gppoolentry> usepools = new vector<> ();

Class in the constructor.
Public Gppooldatasource () {
Init ();
}

Initialize method
public void init () {
try {
Does our JDBC have to be loaded every time? I'm sure it's not, just loading it once is enough.
Class.forName (Config.getdriver ());
for (int i = 0; i < integer.valueof (Config.getinitsize ()); i++) {
Connection conn = Createconn ();
FREEPOOLS.ADD (conn);
}
} catch (ClassNotFoundException e) {
E.printstacktrace ();
}
Check ();
}

Create a connection
Public synchronized Connection Createconn () {
Connection conn = null;
try {
conn = Drivermanager.getconnection (Config.geturl (), Config.getusername (), Config.getpassword ());
Currentactive.incrementandget ();
System.out.println ("Create a connection, the current number of active connections is:" + currentactive.get () + "Connection:" +conn);

} catch (SQLException e) {
E.printstacktrace ();
}
Return conn;
}
/**
* Create a connection is there, should you also get a connection?
* @return
*/
Public synchronized Gppoolentry Getconn () {
Connection conn = null;
if (!freepools.isempty ()) {
conn = freepools.get (0);
Freepools.remove (0);
}else{
if (Currentactive.get () < integer.valueof (Config.getmaxsize ())) {
conn = Createconn ();
}else{
try {
SYSTEM.OUT.PRINTLN ("Connection pool is full, need to wait ...");
Wait (1000);
return Getconn ();
} catch (Interruptedexception e) {
E.printstacktrace ();
}
}
}
Gppoolentry poolentry = new Gppoolentry (conn, System.currenttimemillis ());
What do you do for a connection? Isn't that what you're using? So, each fetch one, put it in the pool that is being used
Usepools.add (Poolentry);
return poolentry;
}


/**
* Create a connection, get the connection is already there, and then it's time to release the connection
*/
Public synchronized void release (Connection conn) {
try {
if (!conn.isclosed () && conn! = null) {
FREEPOOLS.ADD (conn);
}
SYSTEM.OUT.PRINTLN ("A connection was reclaimed, the current number of idle connections is:" +freepools.size ());
} catch (SQLException e) {
E.printstacktrace ();
}
}

Check the time-consuming connection for an extended period and close
private void Check () {
if (Boolean.valueof (Config.gethealth ())) {
Worker worker = new Worker ();
New Java.util.Timer (). Schedule (worker, long.valueof (Config.getdelay ()), long.valueof (Config.getperiod ()));
}
}

Class Worker extends timertask{
@Override
public void Run () {
System.out.println ("routine check ...");
for (int i = 0; i < usepools.size (); i++) {
Gppoolentry entry = Usepools.get (i);
Long startTime = Entry.getusestarttime ();
Long currenttime = System.currenttimemillis ();
if ((currenttime-starttime) >long.valueof (Config.gettimeout ())) {
Connection conn = Entry.getconn ();
try {
IF (conn! = null &&!conn.isclosed ()) {
Conn.close ();
Usepools.remove (i);
Currentactive.decrementandget ();
System.out.println ("Found there is a timeout connection, forcibly closed, the number of connections currently active:" +currentactive.get ());
}
} catch (SQLException e) {
E.printstacktrace ();
}
}
}
}
}
}

4. In the check () method above, to check whether the timeout, so we need to use a wrapper class

public class Gppoolentry {

Private Connection Conn;
Private long usestarttime;
Public Connection Getconn () {
Return conn;
}
public void Setconn (Connection conn) {
This.conn = conn;
}
Public long Getusestarttime () {
return usestarttime;
}
public void Setusestarttime (long usestarttime) {
This.usestarttime = Usestarttime;
}

Public Gppoolentry (Connection Conn, long Usestarttime) {
Super ();
This.conn = conn;
This.usestarttime = Usestarttime;
}
}

5. Well, everything is available, let's write a test test.

public class Gpdatasourcetest {

public static void Main (string[] args) {

Gppooldatasource DataSource = new Gppooldatasource ();

Runnable Runnable = () {
Connection conn = Datasource.getconn (). Getconn ();
SYSTEM.OUT.PRINTLN (conn);
};

Executorservice Executorservice = Executors.newfixedthreadpool (5);
for (int i = 0; i <; i++) {
Executorservice.submit (runnable);
}
Executorservice.shutdown ();
}

}

4. Well, I gave my results:

650) this.width=650; "Width=" 849 "height=" 458 "style=" Width:auto;height:auto; "src=" http://mmbiz.qpic.cn/mmbiz_png/ Xruic8oiyw5vbjialkc2ewvhj8ltrd8vow4mltsricczlibrqa8kaaere5mwtdsoic2f8ojrlgicsplysyiauhkmz5hq/640?wx_fmt=png &wxfrom=5&wx_lazy=1 "alt=" 640?wx_fmt=png&wxfrom=5&wx_lazy=1 "/>

650) this.width=650; "style=" Width:898px;height:auto; "src=" http://mmbiz.qpic.cn/mmbiz_png/ xruic8oiyw5vbjialkc2ewvhj8ltrd8vowvf7gfezhzfbo1skcf6exg5s3a86xb85m3vjbdnzekch2zmpsia4rqrq/640?wx_fmt=png& Wxfrom=5&wx_lazy=1 "alt=" 640?wx_fmt=png&wxfrom=5&wx_lazy=1 "/>

5. In summary, this handwritten connection pool part, in fact, I also learn others, so there are many things unfamiliar, there are many loopholes, now I say I need to improve the place:

• 
  

  • Reflection mechanism

  • Read the Properties file

  • Thread pool

  • Thread

  • Vector Collection

    
    

    
    

650) this.width=650; "style=" Width:auto;height:auto; "src=" http://mmbiz.qpic.cn/mmbiz_jpg/ Xruic8oiyw5upuvcv1cifldft9jiazjawtzuqviaytibsdh5le8ricktou2mn0iblkxzw5kgo4ntibmclfng97qyqsntq/640?wx_fmt=jpeg &wxfrom=5&wx_lazy=1 "alt=" 640?wx_fmt=jpeg&wxfrom=5&wx_lazy=1 "/>

This article is from the "doujh" blog, make sure to keep this source http://doujh.blog.51cto.com/10177066/1937231

Handwriting Database Connection Pool