Using H2 with JUnit tests to test a web service

[Calcul8r]

I have developed a web service that I'm running in WebSphere
application server. I want to test a method that simply retrieves all
values from a database table and returns them as part of a payload in
a response. I want to use an in-memory database for testing so the
tests will be easily repeatable.

I create the database and populate the table in a @BeforeClass
method. I set up the database connection as follows....
try {
Class.forName("org.h2.Driver");
} catch (Exception e) {
e.printStackTrace();
}

// Set up table in h2
Connection con = DriverManager.getConnection("jdbc:h2:mem:" +
DATABASE_NAME + ";DB_CLOSE_DELAY=-1");

The database is created correctly, because I can write some temporary
code at the very start of my test case to prove the database is still
alive and well...
// is my database still alive
Connection con = null;
try {
con = DriverManager.getConnection("jdbc:h2:mem:" + DATABASE_NAME +
";IFEXISTS=TRUE");
PreparedStatement stmt1 = con.prepareStatement("SELECT * FROM
BLAH.BLAH2");
ResultSet blah = stmt1.executeQuery();
while (blah.next()) {
System.out.println(blah.getString(1));
System.out.println(blah.getString(2));
}
blah.close();
stmt1.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

And I get output in my console...
ID1
NAME1
ID2
NAME2

Then I actually make the real web services call... I pass the name of
the database in the query string.
In the web services method, I attempt to get a connection to the
database as follows...

Connection con = null;
if (testing != null) {
try {
Class.forName("org.h2.Driver");
con = DriverManager.getConnection("jdbc:h2:tcp://localhost/mem:" +
testing + ";IFEXISTS=TRUE");
} catch (SQLException e) {
e.printStackTrace();
}
}

... and it waits several seconds and ultimately bombs out with...
[6/24/10 17:25:08:406 CDT] 00000023 SystemErr R
org.h2.jdbc.JdbcSQLException: Connection is broken: "session
closed" [90067-137]
[6/24/10 17:25:08:406 CDT] 00000023 SystemErr R at
org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
[6/24/10 17:25:08:406 CDT] 00000023 SystemErr R at
org.h2.message.DbException.get(DbException.java:167)
[6/24/10 17:25:08:406 CDT] 00000023 SystemErr R at
org.h2.message.DbException.get(DbException.java:144)
[6/24/10 17:25:08:406 CDT] 00000023 SystemErr R at
org.h2.engine.SessionRemote.checkClosed(SessionRemote.java:470)
[6/24/10 17:25:08:406 CDT] 00000023 SystemErr R at
org.h2.engine.SessionRemote.connectServer(SessionRemote.java:331)
[6/24/10 17:25:08:406 CDT] 00000023 SystemErr R at
org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:
223)
[6/24/10 17:25:08:406 CDT] 00000023 SystemErr R at
org.h2.engine.SessionRemote.createSession(SessionRemote.java:217)
[6/24/10 17:25:08:406 CDT] 00000023 SystemErr R at
org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:111)
[6/24/10 17:25:08:406 CDT] 00000023 SystemErr R at
org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:95)
[6/24/10 17:25:08:406 CDT] 00000023 SystemErr R at
org.h2.Driver.connect(Driver.java:58)
[6/24/10 17:25:08:406 CDT] 00000023 SystemErr R at
java.sql.DriverManager.getConnection(DriverManager.java:572)
[6/24/10 17:25:08:406 CDT] 00000023 SystemErr R at
java.sql.DriverManager.getConnection(DriverManager.java:218)

Is it possible to do what I am trying to accomplish... or am I hitting
JVM/classloader issues? (Note that I am using tcp://localhost in the
web service connection. I was using version 1.1.112 which also tossed
a broken connection message. This latest version (1.2.137) mentions
the session is closed in addition to the broken connection message.

[Kerry Sainsbury]

Are you saying that your test and your webservice need to talk to the same database? In that case, don't you need to need to use similar connection strings.

Instead of "jdbc:h2:mem:" + DATABASE_NAME

Try this:   "jdbc:h2:tcp://localhost/mem:" + DATABASE_NAME

Seems plausible to me -- but I haven't tested it :-)

Cheers

Kerry

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Calcul8r]

Kerry,

I don't really need to use a database in the testcase. I just wanted
to make 100% certain that the database still existed after closing the
connection in the set up method (and it does). I do want to use that
same database in the web service and I do use the tcp://localhost in
that url. (The name of the variable is "testing" in the web service
and DATABASE_NAME in the testcase.)

Quote:

con = DriverManager.getConnection("jdbc:h2:tcp://localhost/mem:" +
testing + ";IFEXISTS=TRUE");

Marie

> > h2-database...@googlegroups.com<h2-database%2Bunsubscribe@googlegr­oups.com>
> > .

> > For more options, visit this group at

> >http://groups.google.com/group/h2-database?hl=en.- Hide quoted text -
>
> - Show quoted text -

[Kerry Sainsbury]

I did some more testing -- it looks like your machine can't resolve "localhost" (don't laugh -- it happens!)

I get the same error as you if I try to connect to the following:

jdbc:h2:tcp://UNKNOWN_HOST/mem:testing;IFEXISTS=TRUE

Cheers

Kerry

To unsubscribe from this group, send email to h2-database...@googlegroups.com.

[Thomas Mueller]

Hi,

You are using a remote (client) connection, but did you start the TCP
server? I get the same exception as you if I don't. See also:
http://h2database.com/html/tutorial.html#using_server

Regards,
Thomas

[Calcul8r]

Ahh. Thanks Thomas. I didn't realize I had to start the TCP server.
I thought it came along "for free".

Up and running successfully now.
Marie

On Jun 27, 12:52 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

> Ahh.  Thanks Thomas.  I didn't realize I had to start the TCP server.

The error message is confusing. I will try to fix that in the next release.

> I thought it came along "for free".

Only in the 'automatic mixed mode':
http://h2database.com/html/features.html#auto_mixed_mode

Regards,
Thomas