Tomcat 7 / Tomee / Java Datasource

Question

DISCLAIMER: I'm writing this question at work (from memory) when my code is at home on another PC which I might not be able to access tonight. I'm writing the question now while it is fresh in my memory so apologies for any mistakes.

QUESTION : I am writing a web app in Tomee that connects to a MySQL database. I have successfully created a datasource in Tomee as shown below:

<Resource auth="Container" 
          driverClassName="com.mysql.jdbc.Driver" 
          maxActive="100" 
          maxIdle="30" 
          maxWait="10000" 
          name="jdbc/TestDB"  
          type="javax.sql.DataSource" 
          url="jdbc:mysql://localhost:3306/java" 
          username="u5ser"
          password="p4ssword"/>

This works very well but does rely on this Data Source being configured in any/all instances of Tomcat/Tomee that I deploy my code in. Ultimately I'd like to be able to deploy this code in other containers like Glassfish or Weblogic or other Tomcat's/Tomee's. AKA I'd like the Data Source present in the War file.

With this in mind, is there a way of writing my own Datasource in Java (MyDataSource.class) and bundling this into the WAR file and initializing it with a WebListener.

Essentially : I'd like to know how to code the XML above into my own DataSource (even if this isn't the recommended way - I'm just experimenting really).

Also, a consideration is that I would like to code this so that I can read from a properties file and make aspects of the Data Source configurable.

I've found several examples on the Web but it is the maxActive, maxIdle, maxWait bits that I'm after so that I can create a connection pool in code. These seem to be missing.

Any help is appreciated.

score 1 · Accepted Answer · edited May 23 '17 at 12:23

You can include in your WAR Apache Commons DBCP and Apache Commons Pool to create your own datasource:

package com.company.app.util;

import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;

public final class ConnectionProvider {
    private static final DataSource DATA_SOURCE;
    static {
        try {
            BasicDataSource dataSource = new BasicDataSource();
            dataSource.setDriverClassName("com.mysql.jdbc.Driver");
            dataSource.setUrl("jdbc:mysql://localhost/test");
            dataSource.setUsername("root");
            dataSource.setPassword("");
            dataSource.setValidationQuery("select now()");
            dataSource.setTestOnBorrow(true);
            dataSource.setRemoveAbandoned(true);
            DATA_SOURCE = dataSource;
        } catch (Throwable t) {
            throw new ExceptionInInitializerError(t);
        }
    }
    public static Connection getConnection() throws SQLException {
        return DATA_SOURCE.getConnection();
    }
}

If you want to use a properties file, see Reading Properties file in Java and replaces the properties in the above code

I like this I'm going to give this one a go later on (or whenever I can get on the computer at home) — thonnor, Nov 05 '14 at 16:15
I'll accept this as the best answer because it worked to a point and is something I can use in another project, ironic eh?? This has however, raised another question - which I will research using SO, Google and the usual suspects before probably asking it on here. Thanks for the help. — thonnor, Nov 06 '14 at 09:24

score 1 · Answer 2 · answered Nov 05 '14 at 16:09

You can use the MySQL Using the JDBC DriverManager Interface in Java.

You need to import

import java.sql.Connection; 
import java.sql.DriverManager;
import java.sql.SQLException;

try {
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java?user=u5ser&password=p4ssword");
} catch (SQLException ex) {
    // handle any errors
    System.out.println("SQLException Message: " + ex.getMessage());
    System.out.println("SQLState: " + ex.getSQLState());
}

As you said this is experimental :-) you can do that.

Good answer. Would this allow connection pooling though? – thonnor Nov 05 '14 at 16:12 — thonnor, Nov 05 '14 at 16:12

score 0 · Answer 3 · answered Nov 11 '14 at 18:11

0

Did you look @DataSourceDefinition or its xml equivalent (in web.xml)?

answered Nov 11 '14 at 18:11

Romain Manni-Bucau

3,354
1
16
13

I didn't I'm afraid. I ended up trying a few things out and settled for making a META-INF/context.xml with the datasource definied within. It works ok (on tomcat anyway). I'll need to have a rethink for Weblogic/Glassfish but that is a way off yet. – thonnor Nov 11 '14 at 19:56

Tomcat 7 / Tomee / Java Datasource

3 Answers3