IAmYourFaja
IAmYourFaja

Reputation: 56934

JNDI, DBCP, JDBC & MySQL by Example

I'm trying to:

  1. Read a MySQL datasource out of JNDI
  2. Use this JNDI datasource to configure a DBCP connection pool as well as my JDBC connection
  3. Fire a SQL statement using JDBC
  4. Close politely (release connection back to the pool, close anything else out, etc.)

This code will be fired a lot (its a simple logging system for an app), and so anything that can be kept open across multiple SQL executions would be preferably left open (to reduce overhead of opening/closing stuff, etc.).

Here's my best attempt:

    try {
        // 1. Obtain the dbLogger datasource from JNDI.
        Context context = new InitialContext();
        DataSource logDatabaseDS = (DataSource)context.lookup(logDatabaseJNDI);

        // 2. Configure the connection pool with my JNDI datasource.
        // HOW?
        
        // 3. Obtain a connection from the pool.
        // HOW?
        
        // 4. Use this connection to fire a JDBC INSERT.
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("urlFromJNDI", 
                "userFromJNDI", "passwordFromJNDI");
        
        PreparedStatement statement = conn.prepareStatement(InsertSQL);
        statement.setString(1, appName);
        statement.setLong(2, timestamp);
        statement.setString(3, logLevel);
        
        statement.executeUpdate();
        
        // 5. Release the borrowed connection back to the pool.
        // HOW?
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (NamingException e) {
        e.printStackTrace();
    } 

I believe I have the basic setup correct. Just not sure of:

  1. How to configure the DBCP pool using JNDI-set properties (like maxIdle, maxWait, etc.)
  2. Borrow a connection from the DBCP pool in an efficient way (so this code could fires dozens/hundreds of times per second and not leak resources)
  3. Inject the connection string URL, username and password from the JNDI datasource into the DriverManager.getConnection(...) method
  4. Politely/correctly release the connection back into the pool.

And, if I haven't set this process up correctly, please begin by correcting me on the general approach first!

Upvotes: 2

Views: 736

Answers (0)

Related Questions