Why servletContext.getAttribute("connection") and not servletContext.getAttribute("connectionPool") ?

[Luigi R. Viggiano]

Hi,

I am checking DbStarter and I love the idea.

The thing which I find surprising is this (line 50,51)


conn = DriverManager.getConnection(url, user, password);
servletContext.setAttribute("connection", conn);

This means that when I get a connection from the servletContext, I get
a non-pooled connection, and always the same one is used.

Questions:
- it is safe to access the same connection from multiple threads? How
this works with transactions? what happens if programmers calls
connection.close() after usage (which is the normal case for jdbc
connections) ?
- why not using a servletContext.setAttribute("pool", connectionPool),
which looks more suitable -to me- for a web application?

Am I missing something special that makes this approach valid for H2 ?
(and invalid for all other databases I know of)

Thanks!

L.

[Luigi R. Viggiano]

Hi again.

To address the doubts exposed in my previous email, I made my own
implementation of DbStarter. It is inspired to (and mostly copied
from) org.h2.server.web.DbStarter.
It is made to keep some sort of compatibility over
org.h2.server.web.DbStarter, but instead of offering the connection in
the ServletContext, it also offers a data source coming from
org.h2.jdbcx.JdbcConnectionPool.
Other than using the ServletContext.getAttribute("datasource"), it is
possible to use the static methods
DbStarter.getDataSource().getConnection() and/or
DbStarter.getConnection()

If somebody gives me some clarification to assure me over the doubts I
expressed on the current implementation of
org.h2.server.web.DbStarter, I can probably drop this class. But
looking at the code, I think the class below is somehow a correction
over possible problems that org.h2.server.web.DbStarter can have.

Cheers,
Luigi.

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.sql.DataSource;

import org.h2.Driver;
import org.h2.jdbcx.JdbcConnectionPool;
import org.h2.tools.Server;
import org.h2.util.StringUtils;

/**
* This class is inspired to {@link org.h2.server.web.DbStarter}, but
it
* is meant to correct possible (?) problems regarding transactions
and
* concurrency.
*/
public class DbStarter implements ServletContextListener {

private static DbStarter instance;
private static final String CONNECTION = "connection";
private static final String DATA_SOURCE = "datasource";
private JdbcConnectionPool pool;
private Connection conn; // backward compatibility
private Server server;
private String url;
private String user;
private String password;
private String serverParams;

@Override
public void contextInitialized(ServletContextEvent sce) {
instance = this;
Driver.load();
ServletContext ctx = ctx(sce);
loadParams(ctx);
startTheServerIfConfiguredToDoSo(ctx);
setUpConnectionPool(ctx);
setUpConnection(ctx); // backward compatibility
}

protected void loadParams(ServletContext ctx) {
url = param(ctx, "db.url", "jdbc:h2:~/test");
user = param(ctx, "db.user", "sa");
password = param(ctx, "db.password", "sa");
serverParams = param(ctx, "db.tcpServer", null);
}

protected void startTheServerIfConfiguredToDoSo(ServletContext
ctx) {
if (serverParams != null) {
String[] params = StringUtils.arraySplit(serverParams, '
', true);
try {
server = Server.createTcpServer(params);
server.start();
} catch (SQLException e) {
ctx.log("Error during H2 server startup", e);
}
}
}

protected void setUpConnectionPool(ServletContext ctx) {
pool = JdbcConnectionPool.create(url, user, password);
ctx.setAttribute(DATA_SOURCE, pool);
}

// backward compatibility
protected void setUpConnection(ServletContext ctx) {
try {
conn = pool.getConnection();
ctx.setAttribute(CONNECTION, conn);
} catch (SQLException e) {
ctx.log("Error obtaining the H2 SQL connection", e);
}
}

@Override
public void contextDestroyed(ServletContextEvent sce) {
ServletContext ctx = ctx(sce);
closeAllOpenConnections(ctx);
closeConnection(ctx);
disposeConnectionPool(ctx);
stopServer();
instance = null;
}

protected void closeAllOpenConnections(ServletContext ctx) {
try {
Connection conn = pool.getConnection();
try {
Statement stat = conn.createStatement();
try {
stat.execute("SHUTDOWN");
} finally {
stat.close();
}
} finally {
conn.close();
}
} catch (SQLException ex) {
ctx.log("Error during H2 Shutdown", ex);
}
}

// backward compatibility
protected void closeConnection(ServletContext ctx) {
try {
conn.close();
ctx.removeAttribute(CONNECTION);
conn = null;
} catch (SQLException e) {
ctx.log("Error closing the H2 SQL Connection", e);
}
}

private void disposeConnectionPool(ServletContext ctx) {
pool.dispose();
ctx.removeAttribute(DATA_SOURCE);
pool = null;
}

private void stopServer() {
if (server != null) {
server.stop();
server = null;
}
}

private ServletContext ctx(ServletContextEvent sce) {
return sce.getServletContext();
}

/**
* This is useful if you want to obtain a {@link
JdbcConnectionPool}
* reference type. If you don't have special reasons to do so, you
should
* use {@link #getDataSource()}
*/
public static JdbcConnectionPool getConnectionPool() {
return instance.pool;
}

public static DataSource getDataSource() {
return instance.pool;
}

/**
* This method should be compatible with previous version, but
beware:
* now you need to close the connections after using them!
*/
public static Connection getConnection() throws SQLException {
// alternatively this method may just return 'conn' field,
// and made non-static + deprecated, to discourage the use.
return instance.pool.getConnection();
}

public static Server getServer() {
return instance.server;
}

protected static String param(ServletContext ctx, String key,
String defval) {
String param = ctx.getInitParameter(key);
return param == null ? defval : param;
}
}

[Thomas Mueller]

Hi,

> this means that when I get a connection from the servletContext, I get

> a non-pooled connection, and always the same one is used.

Yes.

> - it is safe to access the same connection from multiple threads?

No.

> How this works with transactions?

If you use transactions, you have to synchronize on the connection.

> what happens if programmers calls connection.close() after usage

Then the connection is closed.

> - why not using a servletContext.setAttribute("pool", connectionPool),
> which looks more suitable -to me- for a web application?

You can do that of course.

> Am I missing something special that makes this approach valid for H2 ?
> (and invalid for all other databases I know of)

Both approaches are valid for any database. Using a ConnectionPool is
nice, and has advantages. Maybe I will change the code to use a
connection pool, or having the option of a connection pool.

Regards,
Thomas

[Luigi R. Viggiano]

Thanks Thomas.

--Luigi

[Igal]

+1

On Friday, May 18, 2012 6:37:08 AM UTC-7, Thomas Mueller wrote:
> Maybe I will change the code to use a
> connection pool, or having the option of a connection pool.
>
> Regards,
> Thomas

[Luigi R. Viggiano]

Hi Igal

On Fri, May 18, 2012 at 7:55 PM, Igal <d...@21solutions.net> wrote:
> +1

In the meantime, you can check out mine at this url
https://gist.github.com/2727189

You can use it from your servlets with some convenient static methods:

Connection conn = DbStarter.getConnection();
try {
// use the connection
} finally {
conn.close(); // always remember this.
}

or

DataSource ds = DbStarter.getDataSource();

or use ServletContext.getAttribute("connection"); or
ServletContext.getAttribute("datasource") if you prefer.

I'm using it in my app, so far I got no problems.

Cheers,

Luigi.