javatomcatjdbcconnection-pooling

Trying to use connection pooling outside servlet engine


I've a series of methods running within a servlet engine (Tomcat in this case), using connection pooling to access the database written in this way:

// Gets an RSS_Feed.
public static RSS_Feed get(int rssFeedNo) {
    ConnectionPool_DB pool = ConnectionPool_DB.getInstance();
    Connection connection = pool.getConnection();
    PreparedStatement ps = null;
    ResultSet rs = null;

    String query = ("SELECT * " +
                    "FROM RSS_Feed " +
                    "WHERE RSSFeedNo = ?;");

    try {
        ps = connection.prepareStatement(query);
        ps.setInt(1, rssFeedNo);
        rs = ps.executeQuery();
        if (rs.next()) {
            return mapRSSFeed(rs);
        }
        else {
            return null;
        }
    }
    catch(Exception ex) {
        logger.error("Error getting RSS_Feed " + rssFeedNo + "\n", ex);
        return null;
    }
    finally {
        Database_Utils.closeResultSet(rs);
        Database_Utils.closeStatement(ps);
        pool.freeConnection(connection);
    }
}

Is it possible to call such a method outside of the servlet engine at all? I would like to do this in a batch process executed from the command line instead of within the servlet engine. I know I could simply rewrite the query without connection pooling but this is one of many queries involved in the process.

The connection pooling is implemented via Apache Common DBCP.

ConnectionPool_DB.getInstance(); reads:

private ConnectionPool_DB() {
    try {
        InitialContext ic = new InitialContext();
        dataSource = (DataSource) ic.lookup(PropertiesFile.getProperty("myApp", "DATASOURCE"));
        // dataSource = (DataSource) ic.lookup("java:/comp/env/jdbc/myApp");
    }
    catch(Exception ex) {
        logger.error("Error getting a connection pool's datasource\n", ex);
    }
}

Solution

  • I have something like this in a project:

    Context ctx = new InitialContext(); 
    DataSource ds = (DataSource) ctx.lookup("DbConnection");
    ConnectionPool connectionPool = new ConnectionPool(ds)
    

    And inside context xml I define the resouce like this

    <Resource name="DbConnection" 
    auth="SERVLET" 
    type="javax.sql.DataSource"  
    scope="Shareable"            
    driverClassName="**driverClassName**" 
    url="**url**" 
    username="**username**" 
    password="**password**" 
    maxActive="10" 
    maxIdle="10" 
    maxWait="1000"
    />
    

    So I assume you have something similar If so you need to write code to create the DataSource yourself.

    This should help you with that http://docs.oracle.com/javase/tutorial/jdbc/basics/sqldatasources.html