JDBC Data source connection pool configuration and application _java

Two ways to establish a database connection using JDBC:

1. Use DriverManager in your code to get a database connection. This approach is inefficient, and its performance, reliability, and stability decrease with the increase in user traffic.

2. The way to connect the database using the configuration data source is to increase the database connection pool on the basis of the above method, which is highly efficient.

The way a data source connection pool connects to a database differs from using DriverManager in code to obtain a database connection:

1 The way the data source is connected to the database is in the program, the DataSource object is obtained by querying to a Jndi (Java naming and Directory Interface) server, which invokes the lookup () method of the context interface. Then call the DataSource object's Getconnection () method to establish the connection

2) in order to be able to reuse database connection objects, improve response time to requests and server performance, using connection pooling technology. Connection pooling Technology establishes multiple database connection objects in advance, and then saves the connection object to the connection pool, and when the client request arrives, pulls a connection object from the pool for customer service, and when the request completes, The client calls the close () method to put the connection object back into the pool.

3 in the code to obtain the database connection by using DriverManager, the client program gets the connection object is the physical connection, the call Connection object's close () method closes the connection, but uses the connection pool technology, the client program obtains the connection object is the connection pool the physical connection one handle, The close () method of the Connection object is invoked, and the physical connection is not turned off, and the data source implementation simply deletes the connection object in the client program and the connection object in the pool.

For testing purposes, you can create a user table in the database (here, for example, MySQL 5):

CREATE TABLE ' user ' ( 
 ' id ' int ' unsigned NOT null auto_increment, 
 ' username ' varchar () DEFAULT null, 
 ' PA ssWOrd ' varchar default NULL, 
 ' email ' varchar default NULL, 
 PRIMARY KEY (' id ') 
;

The driver jar package for the import database is under the Tomcat's Lib directory (for example, MYSQL5, the jar package used is: Mysql-connector-java-5.0.8-bin.jar).

1. Use DriverManager in your code to get a database connection. This approach is inefficient, and its performance, reliability, and stability decrease with the increase in user traffic.

The Java code for the Oracle database connection is as follows:

Import java.sql.Connection; 
Import Java.sql.DriverManager; /** * Get database Connection/public class DbConnection {/** Oracle database connection url*/private final static String Db_url = "Jdbc:o 
  
 RACLE:THIN:@127.0.0.1:1521:ORCL "; 
  
 /** Oracle Database Connection driver/private final static String Db_driver = "Oracle.jdbc.driver.OracleDriver"; 
  
 /** Database username */private final static String Db_username = "root"; 
  
 /** Database Password */private final static String Db_password = "admin"; /** * Get Database connection * @return/public Connection getconnection () {/** Declaration Connection Connection object/Connection conn = Nu 
  ll 
   try{/** uses the Class.forName () method to automatically create an instance of this driver and automatically invoke DriverManager to register it/class.forname (db_driver); 
  /** through the DriverManager getconnection () method to obtain the database connection * * conn = drivermanager.getconnection (Db_url,db_username,db_password); 
  }catch (Exception ex) {ex.printstacktrace (); 
 Return conn; /** * Close Database connection * * @param connect */public void CloseConnection (Connection conn) {try{if (conn!=null) {/** to determine whether the current connection object calls the Shutdown method if it is not closed */if (!conn.isclosed ()) {conn.close (); 
  }}catch (Exception ex) {ex.printstacktrace (); } 
 } 
  
}

The JSP code for the

MySQL database connection is as follows:

 <% @page import= "java.sql.*, com.mysql.jdbc.Driver"%> <%@ page language= "Java "Contenttype=" text/html; Charset=utf-8 "pageencoding=" UTF-8 "%>  
 

2. The way to connect the database using the configuration data source is to increase the database connection pool on the basis of the above method, which is highly efficient.

1 The MySQL database data source connection pool JSP code is as follows:

<% @page import= "java.sql.*, javax.naming.*, Javax.sql.DataSource"%> <%@ 
page language= "Java" Contenttype= "text/html; Charset=utf-8 "pageencoding=" UTF-8 "%> 
 
 

2 Add the following code to the Server.xml in Tomcat's conf directory:

<Context> 
 <resource name= "Jdbc/demodb" auth= " 
 Container" type= "Javax.sql.DataSource" Driverclassname= "Com.mysql.jdbc.Driver" 
 url= "Jdbc:mysql://localhost:3306/demo" 
 username= "root" 
 Password= "123" 
 maxactive= "maxidle=" maxwait= "10000"/> 
</Context>

3 in the Web Engineering directory under the Web.xml root node to configure the following:

<resource-ref> 
 <description>mysqldb connection</description> 
 <res-ref-name> jdbc/demodb</res-ref-name> 
 <res-type>javax.sql.DataSource</res-type> 
 <res-auth >Container</res-auth> 

Completing the above steps the connection pool configuration for the data source is complete, but in order to improve the portability of the project, it is a good idea to put the second step in the context.xml of the Engineering Meta-inf directory (this file needs to be established by itself):

<?xml version= "1.0" encoding= "UTF-8"?> 
<Context> <resource name= "Jdbc/demodb" auth= " 
  Container " 
  type=" Javax.sql.DataSource " 
  driverclassname=" Com.mysql.jdbc.Driver "url=" 
  jdbc:mysql:// Localhost:3306/demo " 
  username=" root " 
  password=" 123 " 
  maxactive= 
  " 
  maxidle= "maxwait=" 10000 "/> 

3. Database Operations Tool classes when using the database connection pool for the configuration data source

The code is as follows:

Package db.utils; 
Import java.sql.Connection; 
Import java.sql.PreparedStatement; 
Import Java.sql.ResultSet; 
Import Java.sql.ResultSetMetaData; 
Import java.sql.SQLException; 
Import java.sql.Statement; 
Import Java.text.DateFormat; 
Import java.util.ArrayList; 
Import Java.util.Date; 
Import Java.util.HashMap; 
Import java.util.List; 
 
Import Java.util.Map; 
Import Javax.naming.InitialContext; 
 
Import Javax.sql.DataSource; 
 
Import Org.apache.log4j.Logger; 
  
 /** * Database Operation Auxiliary class * * * public class Dbutils {//private static Logger Logger = Logger.getlogger ("Dbutils");  /** * The statement must be a SQL INSERT, UPDATE, or DELETE statement * @param SQL * @param paramlist: parameter, corresponding to the placeholder one by one in the SQL statement * @return * @throws Exception */public int execute (String sql, list<object> paramlist) throws Exception {if (sql = = Nu ll | | 
 
  Sql.trim (). Equals ("")) {//logger.info ("parameter is valid!");} 
  Connection conn = null; 
  PreparedStatement pstmt = null; 
 int result = 0; try {conn = getconnection (); 
   pstmt = dbutils.getpreparedstatement (conn, SQL); 
   Setpreparedstatementparam (pstmt, paramlist); 
   if (pstmt = = null) {return-1; 
  result = Pstmt.executeupdate (); 
   catch (Exception e) {//logger.info (E.getmessage ()); 
  throw new Exception (e); 
   finally {closestatement (pstmt); 
  CLOSECONN (conn); 
 return result; /** * Converts the result set obtained by the query database to a MAP object * @param sql: Query statement * @param paramlist: Parameter * @return/public List<map  <string, object>> getquerylist (String sql, list<object> paramlist) throws Exception {if (sql = NULL | | 
   Sql.trim (). Equals ("")) {//logger.info ("parameter is valid!"); 
  return null; 
  } Connection conn = null; 
  PreparedStatement pstmt = null; 
  ResultSet rs = null; 
  list<map<string, object>> querylist = null; 
   try {conn = getconnection (); 
   pstmt = dbutils.getpreparedstatement (conn, SQL); Setpreparedstatementparam (Pstmt, paramlist); 
   if (pstmt = = null) {return null; 
   rs = Getresultset (pstmt); 
  Querylist = Getquerylist (RS); 
   catch (RuntimeException e) {//logger.info (E.getmessage ()); 
   SYSTEM.OUT.PRINTLN ("parameter is valid!"); 
  throw new Exception (e); 
   Finally {Closeresultset (RS); 
   Closestatement (PSTMT); 
  CLOSECONN (conn); 
 return querylist; 
  } private void Setpreparedstatementparam (PreparedStatement pstmt, list<object> paramlist) throws Exception { 
  if (pstmt = null | | paramlist = NULL | | | paramlist.isempty ()) {return; 
  } DateFormat df = Dateformat.getdatetimeinstance (); for (int i = 0; i < paramlist.size (); i++) {if (Paramlist.get (i) instanceof Integer) {int paramvalue = (inte 
    GER) Paramlist.get (i)). Intvalue (); 
   Pstmt.setint (i+1, paramvalue); 
    else if (Paramlist.get (i) instanceof float) {Float paramvalue = ((Float) paramlist.get (i)). Floatvalue (); 
   Pstmt.setfloat (i+1, paramvalue); }else if (Paramlist.get (i) instanceof double) {Double paramvalue = (double) paramlist.get (i)). Doublevalue (); 
   Pstmt.setdouble (i+1, paramvalue); 
   else if (Paramlist.get (i) instanceof Date) {pstmt.setstring (i+1, Df.format ((Date) paramlist.get (i))); 
    else if (Paramlist.get (i) instanceof long) {Long paramvalue = ((Long) paramlist.get (i)). Longvalue (); 
   Pstmt.setlong (i+1, paramvalue); 
   else if (Paramlist.get (i) instanceof String) {pstmt.setstring (i+1, (String) paramlist.get (i)); 
 } return; 
  /** * Access to database connection * @return * @throws Exception/private Connection getconnection () throws Exception { 
  InitialContext cxt = new InitialContext (); 
  DataSource ds = (DataSource) cxt.lookup (jndiname); 
  if (ds = = null) {throw new Exception ("Data Source not found!"); 
 return Ds.getconnection (); private static PreparedStatement getpreparedstatement (Connection conn, String sql) throws Exception {if (conn = = null | | sql = = NULL | | 
  Sql.trim (). Equals ("")) {return null; 
  } PreparedStatement pstmt = Conn.preparestatement (Sql.trim ()); 
 return pstmt; /** * Get database query result set * @param pstmt * @return * @throws Exception/private ResultSet Getresultset (Pr 
  Eparedstatement pstmt) throws Exception {if (pstmt = null) {return null; 
  } ResultSet rs = Pstmt.executequery (); 
 Return RS; /** * @param rs * @return * @throws Exception/private list<map<string, object>> Getque 
  Rylist (ResultSet rs) throws Exception {if (rs = = null) {return null; 
  } ResultSetMetaData Rsmetadata = Rs.getmetadata (); 
  int columnCount = Rsmetadata.getcolumncount (); 
  list<map<string, object>> dataList = new arraylist<map<string, object>> (); 
   while (Rs.next ()) {map<string, object> datamap = new hashmap<string, object> (); for (int i = 0; i < ColumnCount i++) {datamap.put (rsmetadatA.getcolumnname (i+1), Rs.getobject (i+1)); 
  } datalist.add (Datamap); 
 return dataList; /** * Close Database connection * @param conn/private void Closeconn (Connection conn) {if (conn = null) {retur 
  N 
  try {conn.close (); 
  catch (SQLException e) {//logger.info (E.getmessage ()); }/** * Close * @param stmt/private void Closestatement (Statement stmt) {if (stmt = null) {RE 
  Turn 
  try {stmt.close (); 
  catch (SQLException e) {//logger.info (E.getmessage ()); 
  }/** * Close * @param rs/private void Closeresultset (ResultSet rs) {if (rs = = null) {return; 
  try {rs.close (); 
  catch (SQLException e) {//logger.info (E.getmessage ()); 
 
 } private String Jndiname = "Java:/comp/env/jdbc/demodb"; 
 public void Setjndiname (String jndiname) {this.jndiname = Jndiname;

 } 
}

Summary: Connect to the database using the configuration data source, which is highly efficient and stable, and is recommended for use.

See more Java syntax, you can pay attention to: "Thinking in the Java Chinese manual", "JDK 1.7 reference manual in English," "JDK 1.6 API Java Chinese Reference manual", "JDK 1.5 API Java Chinese Reference manual", also hope that everyone Support the cloud-dwelling community.