I do not really remember what versions were released at what times prior to version 9.0 of 20.03.2010 when probably I first updated into the SVN.
Therefore, we will assume that it was version 8.0 where we could start off.
As I had earlier mentioned, the programmer's (developer's) challenge is ensuring writing good and efficient code. One of the best codes should ensure that the database connections are closed properly after use.
In the traditional Client-Server architecture, (say, when we used to use PowerBuilder applications or VB applications), it was enough that we created a single connection and used it throughout the application. It was just required to ensure that the connection gets closed when the application is shut down / closed. Most of the times, the Operating System itself would take care of closing the connection whenever the application would be closed, killed, destroyed or shut-down.
Whereas in case of a web-application, we do not actually know, when and how many users would request for how many connections and that too how many of them would need it concurrently. You would certainly tell me that this challenge is handled by using the concept of Connection Pooling in Web-applications.
How does connection pooling work?
In connection pooling, the application requests the database server for a pre-defined number of database connections. This pre-defined value could be set up by heuristics or by experience or by some logic, (optimal values could be arrived at over a period of time).
(http://www.javaworld.com/article/2076221/jndi/dive-into-connection-pooling-with-j2ee.html)
Figure 1. Connection management using JDBC 1.0
Figure 2. Connection pooling using JDBC 2.0 Standard extension
As can be seeen from the above, in Figure-2, the connection objects from the database are accessed by the application code via the JDBC driver, using Connection Pooling from the DB Connection Pool created within the environment of the application.
The greatest advantage being that the Database Server is not required to Create and Destroy and Re-create & Re-destroy Connection Objects at every request from the application. In case of web-applications, this activity of create and destroy would be very high. Such activity is a real load on the Database Server - for that matter the DB Server is not supposed to be doing this activity (though it could certainly do the same) - rather should be retrieving data from the data store of updating or inserting data into the data store. (Please read about one of my favourite topics - "Karma" and "Dharma" at http://sunambiar.blogspot.in/2015/02/dharma-karma-kaalam.html).
The application creates as many required number of connection objects and services the requirement of the application from that pool without every time requesting from the database server. This is the essence of connection pooling. The only catch here is that, if connections are not released by the application after use, then the pool would get completely consumed and would not be in position to honour more requests. In such situation, the application would 'hang' earlier than usual - but would protect the database server from 'hanging' thereby protecting the data and the database server and not causing any DoS (Denial of Service) at the database server end - especially if the same database server serves other applications as well.
How is snLibrary configured?
Before we see how Connection pooling is implemented in snLibrary, let us see how snLibrary itself is implemented in a Java-web application.
As you already know, java web applications run on web-containers like Tomcat, JBoss, Web-sphere, Web-logic etc. These containers pick up information stored in a 'web.xml' file usually the in the path given by WEB-INF/web.xml. We therefore, make use of this web.xml for specifying our library configuration details.
snLibrary ensures Web-server Platform Independance:
As I had earlier mentioned, the architecture of the snLibrary is that we could have a developer who need not know about the connection parameters or any other thing specific to the Test Environment; nor a tester who need not know about the connection parameters or any other thing specific to the Production Environment. Thus the developer can have an environment on a Tomcat while the tester could live in the environment of a GlassFish while the production could be on a JBoss server platform.
Let us therefore assume that you have created a folder name c:\snConfig if you are in a Windows environment or /user1/snConfig if you are in a Linux/Unix environment. We shall call this folder as the snConfig folder.
Under this folder, we shall create folders called logs, SSO, mail, sms, webmaster
mkdir c:\snConfig\logs
mkdir c:\snConfig\SSO
mkdir c:\snConfig\mail
mkdir c:\snConfig\sms
mkdir c:\snConfig\webmaster
Copy and paste the following codelet inside the web.xml. Those in italics could be your own values. However c:\snConfig and /user1/snConfig are maintained only as part of a convention.
File:- WEB-INF/web.xml:
<context-param>
<param-name>snConfigFile</param-name>
<param-value>c:/snConfig/MyApplication.ini</param-value>
</context-param>
<context-param>
<param-name>snConfigFile-Linux</param-name>
<param-value>/user1/snConfig/MyApplication.ini</param-value>
</context-param>
<context-param>
<param-name>ApplicationName</param-name>
<param-value>MyApplication</param-value>
</context-param>
<listener>
<description>Listens and retrieves Configuration File Name</description>
<listener-class>config.snConfigFile</listener-class>
</listener>
<servlet>
<servlet-name>setupLog</servlet-name>
<servlet-class>servlets.log.snSetupLog</servlet-class>
<load-on-startup>2</load-on-startup>
</servlet>
This ensures that the you can enter many other permitted values inside the mentioned file. The ApplicationName will be used for logging. The setupLog servlet will ensure that the logger Factory is created with the specified logger properties. The logger properties can be specified in a file and the name of the file has to be maintained in the snConfig.ini file (We will henceforth refer the above mentioned MyApplication.ini configuration file of the snConfigFile attribute mentioned inside the web.xml as snConfig.ini).
Let us now open to see the snConfigFile (in our case, the c:/snConfig/MyApplication.ini file)
File:- c:/snConfig/MyApplication.ini
#-- This is just a name - given especially for ODBC Connections specification ---
#-- The name of the file that defines the logger properties. Here we use log4j standard along with slf4j
LogFilePropertiesFileName=C:/snConfig/logs/MyApplicationLogger.properties
Let us now examine what is written in the Logger properties file.
File:-C:/snConfig/logs/MyApplicationLogger.properties
#
# This defines the logging level for the rootLogger. It is not required
# if you are going to keep the level at debug as the rootLogger by default
# is at the debug level. The value after the comma is the appender for the
# root and we have given it the name R
#
log4j.rootLogger=info,stdout, R
#
# For this example, we are just going to log to the console
#
#log4j.appender.R=org.apache.log4j.ConsoleAppender
#log4j.appender.R=org.apache.log4j.RollingFileAppender
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.encoding=UTF-8
log4j.appender.stdout.target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.conversionPattern=%d{ABSOLUTE} %5p %t %c{1}:%M:%L - %m%n
#THE BLOCK BELOW IS WORKING BLOCK FOR FILEAPPENDER
#Note:- In Windows environment, due to sharing restrictions, ArchivedDailyRollingFileAppender
# might not work. Remark that line off and instead Uncomment and use DailyRollingFileAppender
# mentioned in the next line.
log4j.appender.R=com.qas.newmedia.common.logging.ArchivedDailyRollingFileAppender
#log4j.appender.R=org.apache.log4j.DailyRollingFileAppender
#log4j.appender.R.File=/user1/logs/MyApplication.log
log4j.appender.R.File=c:/snConfig/logs/MyApplicaton.log
log4j.appender.R.layout=org.apache.log4j.PatternLayout
#BELOW IS PATTERN FOR LOGGING IN HOURLY BASIS
log4j.appender.R.DatePattern='.'yyyy-MM-dd-HH'.txt'
#BELOW IS PATTERN FOR LOGGING IN MINUTE BASIS
#log4j.appender.R.DatePattern='.'yyyy-MM-dd-HH-mm
log4j.appender.R.layout.ConversionPattern=%d %5p (%l) - %m%n
#log4j.appender.R.MaxBackupIndex=2
#log4j.appender.R.MaxFileSize=100KB
#END OF WORKING BLOCK FOR FILEAPPENDER
#START OF BLOCK FOR JDBCAPPENDER
#log4j.appender.R.layout=org.apache.log4j.PatternLayout
#log4j.appender.R.layout.ConversionPattern=%d{ABSOLUTE} %5p (%l) - %m%n
#log4j.appender.R=org.apache.log4j.jdbc.JDBCAppender
#log4j.appender.R.URL=jdbc:odbc:imagedb
#log4j.appender.R.user=default
#log4j.appender.R.password=default
#log4j.appender.R.sql=INSERT INTO errLogTable VALUES ('%d', '%c', '%p', '%m')
#END OF BLOCK FOR JDBCAPPENDER
#log4j.appender.R.layout.ConversionPattern='.'yyyy-MM-dd-HH-mm
#log4j.appender.R.layout.ConversionPattern=%p - [%X{RemoteHost}] - %m%n
#log4j.appender.R.layout.ConversionPattern=%-4r %-5p %c{2} %M.%L %x - %m\n
#log4j.appender.R.layout.ConversionPattern=%d{ABSOLUTE} %-5p %c{2} %M.%L %x - %m\n
#
# When logging using the ConsoleAppender, the following value tells how and
# what to log. The SimpleLayout simply logs the level of the message and
# the message itself.
#
#log4j.appender.R.layout=org.apache.log4j.SimpleLayout
#
# In this configuration we are going to use MDC to log messages for a special
# category that we define in the GetCommentsServlet
# So first define the level of logging for the category and add a new appender
# to it.
#
#log4j.logger.demo.log4j.servlet.GetCommentsServlet=debug, R2
#
# For this appender and logger we are only interested in the messages from this
# logger. We dont want the messages to print twice, first from the root appender
# and next one from R1 appender. So we set the additivity to false.
#
#log4j.additivity.demo.log4j.servlet.GetCommentsServlet=false
#
# For this we will use the RollingFileAppender and call the log file as MDC.log
#
#log4j.appender.R2=org.apache.log4j.RollingFileAppender
#log4j.appender.R2.File=MDC.log
#
# This is the maximum size of this file, after which it is rolled over. The name
# of the backup file will be MDC.log.1.. and so on. After 2 backups, the file names
# are reused. This is set up by the MaxBackupIndex.
#
log4j.appender.R2.MaxFileSize=100KB
# Keep two backup files
#log4j.appender.R2.MaxBackupIndex=2
#
# The layout is the PatternLayout
#
#log4j.appender.R2.layout=org.apache.log4j.PatternLayout
#
# and the Conversion pattern includes MDC content (%X{ID})
#
#log4j.appender.R2.layout.ConversionPattern=%p - [%X{RemoteHost}] - %m%n
The Library dependencies for those mentioned above are:
snLibrary.jar, slf4j.jar, log4j.jar
How is Connection Pooling implemented in snLibrary?
For the purpose of connection pooling, let us initially use Common Database Connection Pooling or Common DBCP as it is generally known. This uses Shared Pool Data Source for connection pooling.
In snLibrary, we have different types of connection pooling. The basic type is inline within the snConfigFile and it looks like as given below:
File:- c:/snConfig/MyApplication.ini
DataBase Type=ORACLE
DBMS= Oracle 11gvalidation=SELECT 1 FROM DUAL
If the property DBCustomPropertiesFlag is set to 1, then the parameters of 'customProp-' are considered. If the property of 'DBCP Pool Id' is set to 0, then only the non 'customProp-' attributes are considered. If the 'DBCP Pool Id' is set to 1, then the Hikari library would be used picking up attributes from 'customProp-'. If 'DBCP Pool Id' is set to any other value, then, the library class file needs to be specified in DependantClass-DataSource & DependantClass-Config attributes. (Note this has not yet been tested for other than CommonDBCP and Hikari Pooling libraries!!)
How can I connect and use Connection Pooling in my Java Code?
How do I connect to another Database or Schema from the same application using Connection Pooling through snLibrary?
Now, how do we implement this in the code? See this below.
Note:- It is best practice to set all the unused variables to null before close of the web-page /program.
Use Primary Database Properties from XML File=1
Primary DataBase Connection Properties XML File Name=c:/snConfig/myPrimaryConnDB.xml
DataBase Connection Properties XML File Name-mySybDB=c:/snConfig/ConnSybaseDB.xml
DataBase Connection Properties XML File Name-myOraDB=c:/snConfig/ConnOracleDB.xml
Therefore, we will assume that it was version 8.0 where we could start off.
As I had earlier mentioned, the programmer's (developer's) challenge is ensuring writing good and efficient code. One of the best codes should ensure that the database connections are closed properly after use.
In the traditional Client-Server architecture, (say, when we used to use PowerBuilder applications or VB applications), it was enough that we created a single connection and used it throughout the application. It was just required to ensure that the connection gets closed when the application is shut down / closed. Most of the times, the Operating System itself would take care of closing the connection whenever the application would be closed, killed, destroyed or shut-down.
Whereas in case of a web-application, we do not actually know, when and how many users would request for how many connections and that too how many of them would need it concurrently. You would certainly tell me that this challenge is handled by using the concept of Connection Pooling in Web-applications.
How does connection pooling work?
In connection pooling, the application requests the database server for a pre-defined number of database connections. This pre-defined value could be set up by heuristics or by experience or by some logic, (optimal values could be arrived at over a period of time).
(http://www.javaworld.com/article/2076221/jndi/dive-into-connection-pooling-with-j2ee.html)
Figure 1. Connection management using JDBC 1.0
Figure 2. Connection pooling using JDBC 2.0 Standard extension
As can be seeen from the above, in Figure-2, the connection objects from the database are accessed by the application code via the JDBC driver, using Connection Pooling from the DB Connection Pool created within the environment of the application.
The greatest advantage being that the Database Server is not required to Create and Destroy and Re-create & Re-destroy Connection Objects at every request from the application. In case of web-applications, this activity of create and destroy would be very high. Such activity is a real load on the Database Server - for that matter the DB Server is not supposed to be doing this activity (though it could certainly do the same) - rather should be retrieving data from the data store of updating or inserting data into the data store. (Please read about one of my favourite topics - "Karma" and "Dharma" at http://sunambiar.blogspot.in/2015/02/dharma-karma-kaalam.html).
The application creates as many required number of connection objects and services the requirement of the application from that pool without every time requesting from the database server. This is the essence of connection pooling. The only catch here is that, if connections are not released by the application after use, then the pool would get completely consumed and would not be in position to honour more requests. In such situation, the application would 'hang' earlier than usual - but would protect the database server from 'hanging' thereby protecting the data and the database server and not causing any DoS (Denial of Service) at the database server end - especially if the same database server serves other applications as well.
How is snLibrary configured?
Before we see how Connection pooling is implemented in snLibrary, let us see how snLibrary itself is implemented in a Java-web application.
As you already know, java web applications run on web-containers like Tomcat, JBoss, Web-sphere, Web-logic etc. These containers pick up information stored in a 'web.xml' file usually the in the path given by WEB-INF/web.xml. We therefore, make use of this web.xml for specifying our library configuration details.
snLibrary ensures Web-server Platform Independance:
As I had earlier mentioned, the architecture of the snLibrary is that we could have a developer who need not know about the connection parameters or any other thing specific to the Test Environment; nor a tester who need not know about the connection parameters or any other thing specific to the Production Environment. Thus the developer can have an environment on a Tomcat while the tester could live in the environment of a GlassFish while the production could be on a JBoss server platform.
Let us therefore assume that you have created a folder name c:\snConfig if you are in a Windows environment or /user1/snConfig if you are in a Linux/Unix environment. We shall call this folder as the snConfig folder.
Under this folder, we shall create folders called logs, SSO, mail, sms, webmaster
mkdir c:\snConfig\logs
mkdir c:\snConfig\SSO
mkdir c:\snConfig\mail
mkdir c:\snConfig\sms
mkdir c:\snConfig\webmaster
Copy and paste the following codelet inside the web.xml. Those in italics could be your own values. However c:\snConfig and /user1/snConfig are maintained only as part of a convention.
File:- WEB-INF/web.xml:
<context-param>
<param-name>snConfigFile</param-name>
<param-value>c:/snConfig/MyApplication.ini</param-value>
</context-param>
<context-param>
<param-name>snConfigFile-Linux</param-name>
<param-value>/user1/snConfig/MyApplication.ini</param-value>
</context-param>
<context-param>
<param-name>ApplicationName</param-name>
<param-value>MyApplication</param-value>
</context-param>
<listener>
<description>Listens and retrieves Configuration File Name</description>
<listener-class>config.snConfigFile</listener-class>
</listener>
<servlet>
<servlet-name>setupLog</servlet-name>
<servlet-class>servlets.log.snSetupLog</servlet-class>
<load-on-startup>2</load-on-startup>
</servlet>
This ensures that the you can enter many other permitted values inside the mentioned file. The ApplicationName will be used for logging. The setupLog servlet will ensure that the logger Factory is created with the specified logger properties. The logger properties can be specified in a file and the name of the file has to be maintained in the snConfig.ini file (We will henceforth refer the above mentioned MyApplication.ini configuration file of the snConfigFile attribute mentioned inside the web.xml as snConfig.ini).
Let us now open to see the snConfigFile (in our case, the c:/snConfig/MyApplication.ini file)
File:- c:/snConfig/MyApplication.ini
#-- This is just a name - given especially for ODBC Connections specification ---
Name=My Application
#-- The name of the file that defines the logger properties. Here we use log4j standard along with slf4j
LogFilePropertiesFileName=C:/snConfig/logs/MyApplicationLogger.properties
Let us now examine what is written in the Logger properties file.
File:-C:/snConfig/logs/MyApplicationLogger.properties
#
# This defines the logging level for the rootLogger. It is not required
# if you are going to keep the level at debug as the rootLogger by default
# is at the debug level. The value after the comma is the appender for the
# root and we have given it the name R
#
log4j.rootLogger=info,stdout, R
#
# For this example, we are just going to log to the console
#
#log4j.appender.R=org.apache.log4j.ConsoleAppender
#log4j.appender.R=org.apache.log4j.RollingFileAppender
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.encoding=UTF-8
log4j.appender.stdout.target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.conversionPattern=%d{ABSOLUTE} %5p %t %c{1}:%M:%L - %m%n
#THE BLOCK BELOW IS WORKING BLOCK FOR FILEAPPENDER
#Note:- In Windows environment, due to sharing restrictions, ArchivedDailyRollingFileAppender
# might not work. Remark that line off and instead Uncomment and use DailyRollingFileAppender
# mentioned in the next line.
log4j.appender.R=com.qas.newmedia.common.logging.ArchivedDailyRollingFileAppender
#log4j.appender.R=org.apache.log4j.DailyRollingFileAppender
#log4j.appender.R.File=/user1/logs/MyApplication.log
log4j.appender.R.File=c:/snConfig/logs/MyApplicaton.log
log4j.appender.R.layout=org.apache.log4j.PatternLayout
#BELOW IS PATTERN FOR LOGGING IN HOURLY BASIS
log4j.appender.R.DatePattern='.'yyyy-MM-dd-HH'.txt'
#BELOW IS PATTERN FOR LOGGING IN MINUTE BASIS
#log4j.appender.R.DatePattern='.'yyyy-MM-dd-HH-mm
log4j.appender.R.layout.ConversionPattern=%d %5p (%l) - %m%n
#log4j.appender.R.MaxBackupIndex=2
#log4j.appender.R.MaxFileSize=100KB
#END OF WORKING BLOCK FOR FILEAPPENDER
#START OF BLOCK FOR JDBCAPPENDER
#log4j.appender.R.layout=org.apache.log4j.PatternLayout
#log4j.appender.R.layout.ConversionPattern=%d{ABSOLUTE} %5p (%l) - %m%n
#log4j.appender.R=org.apache.log4j.jdbc.JDBCAppender
#log4j.appender.R.URL=jdbc:odbc:imagedb
#log4j.appender.R.user=default
#log4j.appender.R.password=default
#log4j.appender.R.sql=INSERT INTO errLogTable VALUES ('%d', '%c', '%p', '%m')
#END OF BLOCK FOR JDBCAPPENDER
#log4j.appender.R.layout.ConversionPattern='.'yyyy-MM-dd-HH-mm
#log4j.appender.R.layout.ConversionPattern=%p - [%X{RemoteHost}] - %m%n
#log4j.appender.R.layout.ConversionPattern=%-4r %-5p %c{2} %M.%L %x - %m\n
#log4j.appender.R.layout.ConversionPattern=%d{ABSOLUTE} %-5p %c{2} %M.%L %x - %m\n
#
# When logging using the ConsoleAppender, the following value tells how and
# what to log. The SimpleLayout simply logs the level of the message and
# the message itself.
#
#log4j.appender.R.layout=org.apache.log4j.SimpleLayout
#
# In this configuration we are going to use MDC to log messages for a special
# category that we define in the GetCommentsServlet
# So first define the level of logging for the category and add a new appender
# to it.
#
#log4j.logger.demo.log4j.servlet.GetCommentsServlet=debug, R2
#
# For this appender and logger we are only interested in the messages from this
# logger. We dont want the messages to print twice, first from the root appender
# and next one from R1 appender. So we set the additivity to false.
#
#log4j.additivity.demo.log4j.servlet.GetCommentsServlet=false
#
# For this we will use the RollingFileAppender and call the log file as MDC.log
#
#log4j.appender.R2=org.apache.log4j.RollingFileAppender
#log4j.appender.R2.File=MDC.log
#
# This is the maximum size of this file, after which it is rolled over. The name
# of the backup file will be MDC.log.1.. and so on. After 2 backups, the file names
# are reused. This is set up by the MaxBackupIndex.
#
log4j.appender.R2.MaxFileSize=100KB
# Keep two backup files
#log4j.appender.R2.MaxBackupIndex=2
#
# The layout is the PatternLayout
#
#log4j.appender.R2.layout=org.apache.log4j.PatternLayout
#
# and the Conversion pattern includes MDC content (%X{ID})
#
#log4j.appender.R2.layout.ConversionPattern=%p - [%X{RemoteHost}] - %m%n
The Library dependencies for those mentioned above are:
snLibrary.jar, slf4j.jar, log4j.jar
How is Connection Pooling implemented in snLibrary?
For the purpose of connection pooling, let us initially use Common Database Connection Pooling or Common DBCP as it is generally known. This uses Shared Pool Data Source for connection pooling.
In snLibrary, we have different types of connection pooling. The basic type is inline within the snConfigFile and it looks like as given below:
File:- c:/snConfig/MyApplication.ini
DataBase Type=ORACLE
DBMS= Oracle 11gvalidation=SELECT 1 FROM DUAL
Max Active Connections=5
Max Wait Milliseconds until New Connection=50
Max Idle Connections=5
#Max Active Statements=0
#Max Idle Statements=0
#Min Idle Milliseconds for Statement Eviction=0
driver=oracle.jdbc.driver.OracleDriver
JDBCurl=jdbc:oracle:thin:@mydbserver.ds.co.in:mydbschema
Login=myuser
Pass=mypassword
The above mentioned mode is the simple and straight forward method of connection pooling in snLibrary. The following is an alternate method of implementing the same. In this alternate method, we mention the connection properties in a separate XML file.
File:- c:/snConfig/MyApplication.ini
Use Primary Database Properties from XML File=1
Primary DataBase Connection Properties XML File Name=c:/snConfig/MyPrimaryConnection.xml
In the specified XML file, we provide the attributes necessary for the database connection pool.
File:- c:/snConfig/MyPrimaryConnection.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<entry key="Name">MyApplicationDBCP</entry>
<entry key="DataBase Type">ORACLE</entry>
<entry key="Driver">oracle.jdbc.driver.OracleDriver</entry>
<entry key="validation">SELECT 1 FROM DUAL</entry>
<entry key="Max Active Connections">5</entry>
<entry key="Max Idle Connections">5</entry>
<entry key="Min Idle Milliseconds for Statement Eviction">0</entry>
<entry key="Max Wait Milliseconds until New Connection">50</entry>
<entry key="Max Active Statements">0</entry>
<entry key="Max Idle Statements">0</entry>
<entry key="Load Database Pool on Startup">true</entry>
<entry key="login">myuser</entry>
<entry key="pass">mypassword</entry>
<entry key="JDBCurl">jdbc:oracle:thin:@mydbserver.ds.co.in:mydbschema</entry>
</properties>
More advanced features are available. For example, you wish to use another more efficient Pooling Algorithm / Pooling Library. In that case, the following changes can be made in the XML file. All the new attributes of the new pooling library can be provided after the prefix tag 'customProp-'.
File:-c:/snConfig/MyPrimaryConnection.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>c:/snConfig/JDBCODBC.xml :- Properties File created for Multiple Database Pooling
For Primary DB pooling from external file, Set the property:
Use Primary Database Properties from XML File=1
and specify the file name in the property
Primary DataBase Connection Properties XML File Name=
If the DBCustomPropertiesFlag is set to 1 in this XML file, then all those
properties starting with customProp- Property in this XML file would
only be considered as valid property. ie., other properties would be ignored
and used only if the DBCustomPropertiesFlag is set to 0.
DBCP Pool Id:: values : 0 - CommonDBCP; 1 - HikariDBCP
* - SuN
</comment>
<entry key="DBCustomPropertiesFlag">1</entry>
<entry key="DBCP Pool Id">1</entry>
<!--
<entry key="DependantClass-DataSource"> org.apache.commons.dbcp.datasources.SharedPoolDataSource;</entry>
<entry key="DependantClass-Config"> org.apache.commons.dbcp.cpdsadapter.DriverAdapterCPDS</entry>
-->
<entry key="Name">MyApplicationDBCP</entry>
<entry key="DataBase Type">ORACLE</entry>
<entry key="Driver">oracle.jdbc.driver.OracleDriver</entry>
<entry key="validation">SELECT 1 FROM DUAL</entry>
<entry key="Max Active Connections">5</entry>
<entry key="Max Idle Connections">5</entry>
<entry key="Min Idle Milliseconds for Statement Eviction">0</entry>
<entry key="Max Wait Milliseconds until New Connection">50</entry>
<entry key="Max Active Statements">0</entry>
<entry key="Max Idle Statements">0</entry>
<entry key="Load Database Pool on Startup">true</entry>
<entry key="login">myuser</entry>
<entry key="pass">mypassword</entry>
<entry key="JDBCurl">jdbc:oracle:thin:@mydbserver.ds.co.in:mydbschema</entry>
<entry key="customProp-jdbcUrl">jdbc:oracle:thin:@mydbserver.ds.co.in:mydbschema</entry>
<entry key="customProp-username">myuser</entry>
<entry key="customProp-password">mypassword</entry>
<entry key="customProp-driverClassName">oracle.jdbc.driver.OracleDriver</entry>
<!--
<entry key="customProp-dataSourceClassName">mydbDS</entry>
-->
<!--frequently used-->
<!--
<entry key="customProp-autoCommit">true</entry>
<entry key="customProp-connectionTimeout">30000 </entry>
<entry key="customProp-idleTimeout">600000 </entry>
<entry key="customProp-maxLifetime">1800000</entry>
<entry key="customProp-minimumIdle">10</entry>
-->
<entry key="customProp-connectionTestQuery">SELECT 1 FROM DUAL</entry>
<entry key="customProp-maximumPoolSize">5</entry>
<!--
<entry key="customProp-metricRegistry"></entry>
-->
<entry key="customProp-poolName">My_Application_Pool</entry>
<!--infrequently used-->
<entry key="customProp-connectionInitSql">SELECT 1 FROM DUAL</entry>
<!--
<entry key="customProp-initializationFailFast">true</entry>
<entry key="customProp-isolateInternalQueries">false</entry>
<entry key="customProp-readOnly">false</entry>
<entry key="customProp-registerMbeans"></entry>
<entry key="customProp-catalog"></entry>
<entry key="customProp-connectionCustomizerClassName"></entry>
<entry key="customProp-transactionIsolation"></entry>
-->
<entry key="customProp-leakDetectionThreshold">120000</entry>
<!--
<entry key="customProp-dataSource">mydbDS</entry>
-->
</properties>
Having done these connection parameters,
How can I connect and use Connection Pooling in my Java Code?
Following is a sample Java Code that will open a connection and close the connection.
File:- myClassA.java
import connect.snConnectionBean;
import org.slf4j.LoggerFactory;
import org.slf4j.Logger;
// ...
// ...
// ...
public class myClassA {
public void myFunction () {
Logger log = LoggerFactory.getLogger(myClassA.class);
snConnectionBean snB = new snConnectionBean();
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = snB.setDbConnection();
String query = "select name from employee";
// --- other part of the code
log.info("Successfully executed all {}", query);
} catch (Exception ign) {
System.out.println(" Error : " + ign);
log.error("Error", ign);
log.error("Error in SQL : {}", ign.printStackTrace());
} finally {
log.info("Reached finally with {} ", con);
snB.setCloseConnection(con, stmt, rs);
//snB.setCloseConnection(con, stmt);
//snB.setCloseConnection(con);
rs = null;
stmt = null;
con = null;
}
stmt = null;
con = null;
}
} // end of Method definition
} // end of Class defintion
Notice above how the functions are called for opening the connection and for closing the connection inside the try - catch - finally block. This structure is very very important to ensure proper closing of connections. You may or may not use the log class for logging, but the setDbConnection() and setCloseConnection() does generate the necessary logs. The close connection method, can have along with the 'Connection' variable, the Statement / Prepared Statement variable and/or the ResultSet variable which would also get closed properly.
In the case of a JSP File, we should ensure the following:-
File:- myJspA.jsp
<%@page import="org.slf4j.LoggerFactory"%>
<%@page import="org.slf4j.Logger"%>
<%@page import="connect.snConnectionBean" %>
<%
final Logger log = LoggerFactory.getLogger("myJspA.jsp");
snConnectionBean snB = new snConnectionBean();
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = snB.setDbConnection();
%>
<html>
<html>
//
// Some Useful JSP Code goes here
//
<%
String query = "select name from employee";
// --- other part of the code
Employee emp[] = myOwnNewClass.executeMyQuery(con, query);
log.info("Successfully executed all {}", query);
%>
//
// Still some more Useful JSP Code goes here
//
</html>
</html>
<%
} catch (Exception ign) {
System.out.println(" Error : " + ign);
} finally {
snB.setCloseConnection(con, stmt, rs);
rs = null;
stmt = null;
con = null;
}
stmt = null;
con = null;
}
%>
As shown above, all JSP files should follow this frame so that it ensures that Connections are closed. Moreover, connection should be opened only once in a page. If methods called inside a page require connections, it would be better practice to pass the connection variable as a parameter to that method.
How do I connect to another Database or Schema from the same application using Connection Pooling through snLibrary?
snLibrary provides a method for connecting to any number of databases or schemas. For this, it permits us to name each connection separately. Let us assume that we need to connect to a Sybase Database and another Oracle Database apart from the primary connection that we have already done. Let us therefore name the connections as mySybMultiDB and myOraMultiDB respectively.
For implementing the same, in the snConfigFile, we make certain entries as given below:
File:- c:/snConfig/MyApplication.ini
DataBase Connection Properties XML File Name-mySybDB=c:/snConfig/ConnSybaseDB.xml
DataBase Connection Properties XML File Name-myOraDB=c:/snConfig/ConnOracleDB.xml
Now, we have need to create two separate XML files by name c:/snConfig/ConnSybaseDB.xml and c:/snConfig/ConnOracleDB.xml similar to the attribute definitions already mentioned above (in File:-c:/snConfig/MyPrimaryConnection.xml).
Now, how do we implement this in the code? See this below.
File:- myClassB.java
import connect.snConnectionBean;
import org.slf4j.LoggerFactory;
import org.slf4j.Logger;
// ...
// ...
// ...
public class myClassB {
public void myFunctionMulti () {
Logger log = LoggerFactory.getLogger(myClassB.class);
snConnectionBean snB = new snConnectionBean();
Connection conSyb = null, conOra = null;
Statement stmt = null;
ResultSet rs = null;
try {
conSyb = snB.setDbMultiConnection("mySybDB");
String query = "select name from employee";
// --- other part of the code
log.info("Successfully executed all {}", query);
} catch (Exception ign) {
System.out.println(" Error : " + ign);
log.error("Error", ign);
log.error("Error in SQL : {}", ign.printStackTrace());
} finally {
log.info("Reached finally with {} ", con);
snB.setCloseMultiConnection("mySybDB", rs, stmt, conSyb);
rs = null;
stmt = null;
conSyb = null;
}
stmt = null;
conSyb = null;
}
try {
conOra = snB.setDbMultiConnection("myOraDB");
String queryOra = "select name from address";
// --- other part of the code
log.info("Successfully executed all {}", query);
} catch (Exception ign) {
System.out.println(" Error : " + ign);
log.error("Error", ign);
log.error("Error in SQL : {}", ign.printStackTrace());
} finally {
log.info("Reached finally with {} ", con);
snB.setCloseMultiConnection("myOraDB", rs, stmt, conOra);
rs = null;
stmt = null;
conOra = null;
rs = null;
stmt = null;
conOra = null;
}
} // end of Method definition
} // end of Class defintion
Notice how the multi-connections are identified and connection object is gathered from the Connection Pool !!.
Note:- It is best practice to set all the unused variables to null before close of the web-page /program.
What we have hitherto seen is about connection pooling done inside the web-container of Tomcat or Jboss or Glassfish at the application level. Many applications therefore could implement their own individual connection pools and work within that limited area. However, sometimes, many applications use common schemas of databases and the web-administrator or the database-administrator would suggest using a single pool for all the applications put together.
This is usually done through defining JNDI (Java Naming & Directory Interface) Data Sources in the Web-container (Tomcat, Glassfish, JBoss etc.) itself. When such JNDI resources are defined in the web-container itself, we can use it in snLibrary configuration as follows. However, there is only one restriction that all of the connections should follow the same convention of either JNDI or application specific connection pools. In otherwords, it is not allowed to have a mix of JNDI and application-specific connection pool for the same application.
File:- c:/snConfig/MyApplication.ini
JNDI DataBase Connection Pool Enabled=1
# Note: If JNDI Connection Pool is enabled, then the same is applicable for Multi Connections as well. It is not permitted to have partially JNDI and JDBC.
#JNDI Primary DB Connection Pool Name=java:/myPrimaryPoolDS
JNDI Primary DB Connection Pool Name=java:comp/env/jdbc/myPrimaryPoolDS
JNDI DataBase Connection Pool Name-mySybDB=java:comp/env/jdbc/mySybPoolDS
JNDI DataBase Connection Pool Name-myOraDB=java:comp/env/jdbc/myOraPoolDS
It should be noted that in case the parameter for 'JNDI Database Connection Pool Enabled' is set to 1 (ie. true), then the attributes viz. 'DataBase Connection Properties XML File Name-' are ignored completely.
Thus we have reasonably dealt with the configurations necessary for Connection Pooling using snLibrary in this blog !!. Will see further features in the next blogs.
No comments:
Post a Comment