Tomcat - JDBC

tomcat

http://terrarum.net/blog/deploying-birt-on-tomcat.html
https://stackoverflow.com/questions/18070948/how-to-implement-connection-pooling-in-birt-reports
http://www.visioneo.org/knowledge-base/-/knowledge_base/article/122720
http://dynasoftusa.com/connection-pooling-in-birt-reports-with-tomcat-server/
https://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html
https://communities.opentext.com/forums/discussion/46440/using-my-report-files-with-tomcat-and-birt-viewer
https://linuxibos.blogspot.com/2012/11/installing-birt-viewer-server-and-birt.html
https://www.youtube.com/watch?v=_0baQeP7V3c

What are the overall steps to setup JDBC connection pool?

  1. Obtained the correct driver
  2. Copy the driver's JAR file to your $CATALINA_HOME/lib directory
  3. Define your database as a resource. Tomcat uses the JNDI API to connect to JDBC-aware databases. JNDI Resources can either be declared on a per-Context basis, or as Global Resources, accessible by multiple contexts through a Resource Reference. If you configure your database as a resource in your application's context, it tells Tomcat to create the resource when the application launches. If you want the database to be available to multiple applications, you can provide this information in Tomcat's server.xml file. If you configure your database as a resource reference in your application's "WEB-INF/web.xml" file, it provides a references to your database that does not contain information specific to your servers, so that the application can easily be ported to another server environment.
  4. Restart your server

How can we find and download the JAR file for using MySQL with Tomcat?

The driver that JDBC needs to connect to MySQL is called Connector/J. It is developed by the MySQL engineering team, and the latest version is available for free from their website. Download the binary distribution appropriate for your platform, extract the JAR file, and copy it to "$CATALINA_HOME/lib". This makes the driver available to JDBC.

How can we do Per-Context JDBC Resource Configuration?

All actively supported versions of Tomcat allow configuration of Contexts through either an entry in Server.xml or through a Context XML fragment in your application's "META-INF/context.xml" file. We strongly recommend you use the second option, as this will allow you to make changes to your database configuration without restarting your entire server, and also make your application more portable.

The Resource element is used to define the JNDI/JDBC resource, as well as set connection pooling attributes. It is nested inside the Context element. Here is a sample MySQL Resource configuration entry:

<Context>
    <Resource 
        name="jdbc/[YourDatabaseName]" 
        auth="Container"
        type="javax.sql.DataSource"
        username="[DatabaseUsername]"
        password="[DatabasePassword]"
        driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://[yourserver]:3306/[yourapplication]"
        maxActive="15"
        maxIdle="3"
    />
</Context>

If your application will connect to more than one database, you can define the additional databases in additional Resource elements, but each must have a unique name.

How can we do GlobalNamingResources JDBC Resource Configuration?

Here is a sample GlobalNamingResources configuration. The only major difference between this example and the per-Context configuration is its location (GlobalNamingResources are defined in server.xml):

<GlobalNamingResources>
    <Resource 
        name="jdbc/[YourDatabaseName]"
        auth="Container"
        type="javax.sql.DataSource"
        username="[DatabaseUsername]"
        password="[DatabasePassword]"
        driverClassName="com.mysql.jdbc.Driver"
        description="Global Address Database"
        url="jdbc:mysql://[yourserver]:3306/[yourapplication]"
        maxActive="15"
        maxIdle="3"
    />
</GlobalNamingResources>

If you will be accessing this resource via a Resource Reference on a per-context basis, you MUST provide a value for the "auth" attribute. Valid values for this attribute are "Container", in which case the Container will connect to the database on behalf of the application, or Application, in which case the application will provide credentials. The correct configuration will vary dependent on your application code.

After defining our database as a global resource, we can link to it in the per-Context basis through a ResourceLink element. Perhaps the global resource is not automatically visible / available to all applications. The ResourceLink element allow us to rename the resource.

<Context>
    <ResourceLink 
        name="jdbc/[ContextSpecificName]"
        global="jdbc/[GlobalResourceName]"
        type="javax.sql.DataSource"
    />
</Context>

The "name" attribute allows you to define a per-Context name for the resource, that can be used in your application code, as long as the "global" value correctly identifies the referenced resource. ResourceLink elements can also be defined on a per-host basis, and referenced via web.xml entries.

Now that you've configured the main Resource entry, it's time to provide Resource Reference information, which will make your application more portable. This is done on a per-Context basis, in "WEB-INF/web.xml".

Here is an example Resource Reference configuration entry:

<web-app>
    <resource-ref>
        <description>[Your Description]</description>
        <res-ref-name>jdbc/[YourDatabaseName]</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>
</web-app>

You cannot define a new name for your Resource here. The value of "res-ref-name" must refer either to a Resource directly configured in a Context element, or to a renamed Global Resource, as defined by a ResourceLink element nested within a Context or Host element.

Any additional deployment descriptors must be ordered as defined in the Servlet Specification.

The <res-ref-name> element defines the name that you will use in your application will use to access the code, so MAKE SURE IT'S RIGHT. Here's an example resource reference you might use in your application code:

Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/[YourResourceName]");

To successfully use a connection pool, you have to write code that respects the fact that connections will need to be re-used by other requests. This means that your code should include provisions for gracefully returning connections to the pool after using them.

If you are experiencing a lot of Connection Closed exceptions, chances are that your requests are attempting to close connections twice, as if they were normal connections, instead of closing them once and then checking to make sure they are closed.

This distinction is important, because closing a connection that is part of a pool does not simply close the connection - it returns it to the pool. If your code simply closes the connection twice with a "finally" statement, your old requests may be closing connections that are in use by new requests, which is what is causing your exceptions. Adding "conn = null" statements to your code both before the "finally" statement and as a part of the "finally" loop will check the status of the connection, to ensure that this does not occur.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License