Use connection pooling to properly shut down preparedstatement and resultset

Using JDBC to connect the database, the process of obtaining the database connection is introduced in other articles on the network, which is not much introduced, this article mainly analyzes how to handle the connection after getting the database connection and completing the processing process. Let's look at a simple analog database connection pool to get an instance of the connection:,
Import java.sql.Connection; Import Java.sql.DriverManager; Import java.sql.SQLException; Import java.util.LinkedList; Import Java.util.logging.Logger;
public class Datasourceutils {//privatisation constructor, as the first step in building a single case pattern private datasourceutils () {try {class.forname ("Com.mysql.jdbc.Dr Iver "); The catch (ClassNotFoundException e) {e.printstacktrace ();}//The container of the connection pool initializes pool = new linkedlist<connection> (); Fill container for (int i = 0; i < poolsize i++) {Connection con = null; try {con = drivermanager.getconnection (Sqlurl,root, password); catch (SQLException e) {e.printstacktrace ();} pool.add (Con); } log.info ("" + pool.size ()); System.out.println ("");}
private static String root = "root"; private static String Password = "1995"; private static Logger log = Logger.getlogger ("Com.utils.DataSourceUtils");
private static final String Sqlurl = "Jdbc:mysql://localhost:3306/hotelmanager";
private static final int poolsize = 10;

The container of the connection pool is private static linkedlist<connection> pool; Static inner class Implementation single case mode private static class Innerclass {static Datasourceutils instance = new Datasourceutils ();}
Get single Example public Connection Getconneciton () {return getconnection (0);//Get Database connection public Connection getconnection (long Tim e) {//synchronization process synchronized (pool) {Log.info ("number" +pool.size ()); long sumtime = 0; if (Time > 0) {//Timeout calculation processing while Tim E >= sumtime) {if (pool.size () = = 0) {try {pool.wait (); sumtime+=50;} catch (Interruptedexception e) {E.printsta Cktrace (); } else {//If there is a connection in the connection pool, remove and return Connection con = Pool.pollfirst (); Log.info (' Get ' +pool.size ()); return con.}} return null; else {//infinite wait mode: while (true) {if (pool.size () = 0) {try {pool.wait);} catch (Interruptedexception e) {e.prints Tacktrace (); } else {Connection con = Pool.pollfirst (); Log.info (' Get ' +pool.size ()); return con.}} }
The public static Datasourceutils getinstance () {//TODO auto-generated a stub return innerclass.instance}//Connect the database Connect to the public void realiseconnection (Connection con) {synchronized (pool) {pool.addlast (con) in the connection pool; Pool.notifyall ();}}}
A new connection can be obtained from the connection pool provided above and put back into the connection pool after it is exhausted. There is no way to provide a connection pool expansion, which can be added behind. There is no big problem here, look at the process of handling the connection:
Import Java.io.ObjectInputStream.GetField; Import java.sql.Connection; Import java.sql.PreparedStatement; Import Java.sql.ResultSet; Import java.sql.SQLException; Import java.util.ArrayList; Import java.util.List; Import java.util.*; public class Roomdao {//Get encountered Database tool class: private static Datasourceutils data = Datasourceutils.getinstance (); public static fi nal String addsql = "SELECT * from Adminuser"; Test method public static String test () {//Get database connection Connection con = Data.getconneciton (); PreparedStatement PS = null; ResultSet rs = null; Preparation PS = con.preparestatement (addsql) before try {//Execute SQL statement; rs = Ps.executequery ();//return value does not matter returns null;} catch (Sqlexcept Ion e) {e.printstacktrace ();} finally {//free of PreparedStatement and ResultSet performed here, do not open comment first /*if (rs!=null) {try{rs.close ();} catch (SQLException e) {e.printstacktrace ();}} if (ps!=null) {try {ps.close ();} catch (SQLException e) {e.printstacktrace ();}} */Data.realiseconnection (con); return null; ///Run 10 threads while executing the test () method public static void Main (String args[]) {for (int i=0;i<10;i++) {new Thread () {public void run () {while (true) {test ();}} }.start (); } } }
Analysis without opening annotations, that is, without the active release of PreparedStatement and ResultSet: Perform a second class in DOS and use the JVM Memory Analyzer Tool Jconsole.exe detect memory usage, as shown in the following illustration:

You can see that the overall trend of memory usage is climbed, where the polyline is due to the process of JVN automatic GC, which automatically cleans up unwanted objects and therefore reduces memory usage.
Look at the example below to open the comment section, which is the result of PreparedStatement and resultset closing:

The total trend of memory space that can be used by the program is very stable, which means that only the active shutdown of PreparedStatement and RESULTSET,JVM will voluntarily release the object.
Come here, the solution has emerged and the problem has been solved. But there is a problem: If connection is turned off, then PreparedStatement and resultset will be closed. This problem cannot be tested by the above scheme memory, because a large number of short time create connection can cause a problem: The driver is unable to create a connection due to a inability to establish the client portion of a socket.----Database Company Connection error. please refer to this blog for reasons. http://blog.csdn.net/zl386119974/article/details/29205633