SQL Azure and Connection Pooling

Question

I have been searching high and low and gathered bits and pieces, apologies if this has already been answered elsewhere but I am unable to find it.

I am writing a web application in Java with Tomcat, and SQL Azure in the backend.

There are multiple servlets accessing the SQL Azure DB. I would like to use Connection Pools as managed by Tomcat 8.5

My application context.xml in META-INF is as follows:

        <Context>

    <Resource name="jdbc/sqlazure"
    auth="Container"
    driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
    type="javax.sql.DataSource"
    maxIdle="30"
    username="[username]"
    password="[password]"
    url="jdbc:sqlserver://[database]:1433;database=BackInfo;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"
    removeAbandonedTimeout="30"
    logAbandoned="true" /> 

</Context>

In the Java Code, I access the typical way:

    InitialContext ic = new InitialContext();ds = (DataSource)ic.lookup("java:comp/env/jdbc/sqlazure");

try(Connection con = ds.getConnection())....

Everything seems to work, so let me confirm my understanding here:

I do not need to specify a separate web.xml since I'm using Tomcat 8.5. Correct ?
Azure will automatically create a pool when I connect in this manner. The number of connections in the pool etc cannot (do not need to?) be configured.
Before I realized I would have other servlets that need to access the database, I had one servlet directly creating a Datasource via SQLServerConnectionPoolDataSource and getting a connection from there. The documentation states:

SQLServerConnectionPoolDataSource is typically used in Java Application Server environments that support built-in connection pooling and require a ConnectionPoolDataSource to provide physical connections, such as Java Platform, Enterprise Edition (Java EE) application servers that provide JDBC 3.0 API spec connection pooling.

Does this mean that when I use SQLServerConnectionPoolDataSource directly to ask for a connection, it will check if Tomcat supports pooling and then is basically using JDBC mechanisms to create a pool of SQL Azure connections managed by Tomcat ?
When getting the DataSource via Tomcat JNDI lookup, using SQLServerDriver as specified in context.xml. When the web app starts up, it will check context.xml and use SQLServerDriver to connect to SQL Azure, check if pooling is supported, if yes then Tomcat is using the driver to automatically creating a connection pool DataSource that it returns ?
I also just thought of one other question. Would it make sense to have a Singleton DataSource class that returns a reference to the pooled connection DataSource, or would it be better to have each servlet lookup the datasource in its init() and store in a private variable ?

Thanks

Peter Pan Peter Pan · Accepted Answer · 2016-12-21T06:58:11

Based on my understanding, the jdbc connection pool for SQL Server is created by Java Application, not Azure does. My suggestion is that you need to refer to the Tomcat offical documents below to understand the JNDI Resources & JDBC DataSource.

JNDI Resources: http://tomcat.apache.org/tomcat-8.5-doc/jndi-resources-howto.html
JDBC DataSource: http://tomcat.apache.org/tomcat-8.5-doc/jndi-datasource-examples-howto.html

SQL Azure and Connection Pooling

1 Answers