connecting to postgresql via jdbc driver and h2console.war

1
votes

I am using JBoss AS 7.1.1.Final as my application server.

I have posted my question on the JBoss Community user forum: question-here

Reproduced below:

Installed AS 7.1.1.Final on my MacBook Pro.

Downloaded the Quickstart examples. Was able to successfully deploy and test helloworld, jboss-as-greeter wars.

Then i set up the postgresql database engine to try jboss-as-cmt example. I was able to compile and deploy it.

Everything works great except for one thing - i am unable to connect to the postgresql database from the h2console web-app that is shipped with the application server.

The jboss-as-cmt war works perfectly i.e i am able to add customers to the invoice. I can psql into the db engine and i can see all the records. My only problem is that i can't use h2console webapp to get into my jboss-as-cmt postgres database i created.

I am using: "org.postgresql.Driver" for the Driver Class field. "jdbc:postgresql://127.0.0.1:5432/jboss-as-cmt" for the JDBC URL. This is the same as my connection-url value set in the standalone-full.xml config file. "sa" for username and password.

When i press the "Test Connection" button on the h2console app, unfortunately i see -

Class "org.postgresql.Driver" not found [90086-161]
org.h2.message.DbException: Class "org.postgresql.Driver" not found [90086-161]
    at org.h2.message.DbException.get(DbException.java:158)
    at org.h2.util.Utils.loadUserClass(Utils.java:429)
    at org.h2.util.JdbcUtils.getConnection(JdbcUtils.java:146)
    at org.h2.server.web.WebServer.getConnection(WebServer.java:653)
    at org.h2.server.web.WebApp.test(WebApp.java:839)
    at org.h2.server.web.WebApp.process(WebApp.java:215)
    at org.h2.server.web.WebApp.processRequest(WebApp.java:164)
    at org.h2.server.web.WebServlet.doGet(WebServlet.java:118)
    at org.h2.server.web.WebServlet.doPost(WebServlet.java:153)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:754)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:329)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161)
    at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:153)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:155)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:368)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:877)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:671)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:930)
    at java.lang.Thread.run(Thread.java:680)
Caused by: org.h2.jdbc.JdbcSQLException: Class "org.postgresql.Driver" not found [90086-161]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    ... 24 more
Caused by: java.lang.ClassNotFoundException: org.postgresql.Driver from [Module "com.h2database.h2:main" from local module loader @b4e29b (roots: /Users/avyayatek/Downloads/jboss-as-7.1.1.Final/modules)]
    at org.jboss.modules.ModuleClassLoader.findClass(ModuleClassLoader.java:190)
    at org.jboss.modules.ConcurrentClassLoader.performLoadClassUnchecked(ConcurrentClassLoader.java:468)
    at org.jboss.modules.ConcurrentClassLoader.performLoadClassChecked(ConcurrentClassLoader.java:456)
    at org.jboss.modules.ConcurrentClassLoader.performLoadClassChecked(ConcurrentClassLoader.java:423)
    at org.jboss.modules.ConcurrentClassLoader.performLoadClass(ConcurrentClassLoader.java:398)
    at org.jboss.modules.ConcurrentClassLoader.loadClass(ConcurrentClassLoader.java:120)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:169)
    at org.h2.util.Utils.loadUserClass(Utils.java:424)
    ... 22 more

I am able to establish a successful connection from the jboss admin management console (http://localhost:9990/console) to the datasource. On the datasources profile panel, i have the name of the datasource set to "postgresDS", jndi set to "java:jboss/postgresDS". Connection URL is same as what i am use on the h2console - "jdbc:postgresql://127.0.0.1:5432/jboss-as-cmt"

I don't know why h2console is not connecting to the postgresql db engine. It makes a successful connection to the h2 in-memory database that is used in the jboss-as-greeter example.

I have placed the JDBC driver in the $JBOSS_HOME/modules/org/postgresql/main folder. Created the module.xml there. There is a Driver.class file inside the jar in ~/org/postgresql/ folder. I have modified the $JBOSS_HOME/standalone/configuration/standalone-full.xml to add the postgresql related datasource and driver sections.

As i said, i can build jboss-as-cmt and deploy and it works as expected. If i could make the h2console connect to this postgresql database, it will give me some peace.

I am a newbie to JBoss and server side stuff in general.

I have attached some pics for reference.

Thanks for your time.

Any thoughts is highly appreciated. Thanks for your insights and time.

2
postgresqljdbcjboss7.xh2
If you have a question, ask it here, don't link to a question on another website. – Mark Rotteveel
This is odd: Caused by: java.lang.ClassNotFoundException: org.postgresql.Driver from [Module "com.h2database.h2:main" from local module loader @b4e29b (roots: /Users/avyayatek/Downloads/jboss-as-7.1.1.Final/modules)]. It does not make sense for JBoss AS 7 to be trying to load the PostgreSQL JDBC driver from the h2 database module. That said, why do you expect to be able to use PgJDBC from the h2 console app, though? Isn't it just for the h2 database? – Craig Ringer
Anyway, please read the site FAQ (stackoverflow.com/faq). -1 and I agree with @MarkRotteveel. You're at 300 and 1 year membership, I'd expect you to have some idea how things work here and make some more effort with a question. – Craig Ringer
@CraigRinger I honestly don't understand how you could conclude that i have not made any or much effort before posting the question. You are entitled to your opinion but i request you to be a bit more judicious. Back to my question, i don't think h2 console app is just for h2 database. Why would there be options for connecting to other db engines - MySQL, PostgreSQL, SQlite, etc? In the standalone-full.xml, config file i use with the JBoss AS start script, there is a separate section defining postgres datasource/driver. I expected h2console app to load appropriate JDBC drivers but it isn't... – VJ Vélan Solutions
@CraigRinger The H2 Console tool can connect to any database that supports the JDBC API. It is not just for H2 databases. I don't know about how it is bundled with JBoss product however (I didn't even know that it is included there). – Thomas Mueller

2 Answers

1
votes

Firstly, thanks to @ThomasMueller whose comment encouraged me to dig further and arriving at a functional solution. And also thanks to Wolf-Dieter on JBoss Community page for pointing me in the right direction. So, my sincere suggestion to @MarkRotteveel is that it does not hurt to have postings at credible forums and when we find a solution, everyone benefits.

This is what worked for me: Thanks for the link to the source, i created the h2console from the source and this time, i placed the postgres JDBC driver jar file in the WEB-INF/lib folder. Now, i am able to use the h2console to connect to postgresql database.

The h2console app that ships with the quickstart does not have any library jars in the WEB-INF/lib folder. Instead it specifies the dependencies in the META-INF/MANIFEST.mf file. I am positive both the h2 and postgres drivers are loaded while the JBoss starts - i can see the logs. But for some reason, this app only recognizes the h2 driver and fails to recognize the postgres driver. Looking at the source file, i am fairly positive that there is no "hardcoding" anywhere in there. So, it's still a mystery to me.

An alternative to h2console tool - SQUirrel.

Thanks All. Hope this helps someone - not just limited to SO.

0
votes

A driver can register itself as a handler for multiple JDBC protocols. It might have changed but at one point H2 would register itself as a handler for jdbc:postgresql - not entirely unreasonable since H2 can be run in PostgreSQL emulation mode. You would be fine if you used the DriverManager directly (since it only requires the url) but have problems if you used a connection manager (which wants a driver classpath) since the driver is NOT org.postgresql.Driver.

This also caused problems if you had both h2 and postgresql drivers in your classpath. I don't remember if it's "first one registered" or "last one registered" but which driver you got from the DriverManager would depend on the order the jars were loaded. It caused a lot of grief and one reason I got into the habit of always defining a DataSource that explicitly named the classpath to use. It's a bit of overkill with a one-off app that only needs a single connection but it eliminates the ambiguity.

As for the particular problem I just add the jars to the java classpath. I have a small bash shell script that puts about a dozen jars onto the classpath and it allows me to connect to large number of databases without a problem. It avoids the need to rebuild the h2 jar every time I need to update one of the drivers.