Start h2 database

[enrix]

Dear Community,

Is it possible to start the h2 database automatically within the boot of the server?

Best regards.

[jaikiran]

If you are talking about AS7, then H2 database is already integrated with AS7 and a in-memory instance started during the AS7 boot process.

[enrix]

jaikiran pai,

thanks for your qick replay. Unfortunately this is not invariably the case.  Usually i have to start the JBOSS_HOME\modules\com\h2database\h2\main\h2-1.2.145.jar  for using the connection within my ejb. After then i can restart the server and  the database connection is immediately availabale.

[jaikiran]

I'm not sure I understand. Are you using the H2 datasource in your EJBs? Can you please post the relevant code?

[enrix]

I'm using the datasource standalone.xml inside the configuration-Folder of the standalone-installation, where i have set the connection-url and driver-Class vor the h2database. Then i have create an JPA-project with Entities, wich is connecting the datasource via persistence.xml. I have add the JPA-Project in the build path of a EJB and all works fine. Unfortunately i have to start the h2database in the folder JBOSS_HOME\modules\com\h2database\h2\main\h2-1.2.145.jar by hand. Is it possible to start the database automatically?

[nimo22]

There are may ways to start h2-database automatically!

Try to connect via TCP Server

Database URL (path to jboss-h2-directory): jdbc:h2:tcp://localhost/~/test

import org.h2.tools.Server;

...

// start the TCP Server

Server server = Server.createTcpServer(args).start();

...

// stop the TCP Server

server.stop();

Or you can use a servletlistener in embeeded or server mode:

<listener>

<listener-class>org.h2.server.web.DbStarter</listener-class>

</listener>

For more information, look at the docs of h2-database.

[jaikiran]

Actually, Henry doesn't have to start the H2 DB himself. I think he isn't using the datasource in the persistence.xml and instead is specifying the connection URL for Hibernate. Henry, can you post your persistence.xml file? Typically, you would just use:

<jta-data-source>jndiNameOfYourDS</jta-data-source>

and that should be enough.

[enrix]

That's my persistence.xml and the jndi-name for the datasource is jdbc/amatoDB

<?xml version="1.0" encoding="UTF-8"?>

<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">

          <persistence-unit name="amatoPersistence" transaction-type="JTA">

          <provider>org.hibernate.ejb.HibernatePersistence</provider>

          <!-- <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>-->

          <jta-data-source>jdbc/amatoDB</jta-data-source>

          <shared-cache-mode>DISABLE_SELECTIVE</shared-cache-mode>

      <properties>

         <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>

      </properties>

          </persistence-unit>

</persistence>

and here are the configurations in the standalone.xml

            <datasources>

                <datasource jndi-name="jdbc/amatoDB" pool-name="H2DS" enabled="true" jta="true" use-java-context="true" use-ccm="true">

                    <connection-url>

                        jdbc:h2:tcp://localhost/~/amatodb;DB_CLOSE_DELAY=-1

                    </connection-url>

                    <driver>

                        h2

                    </driver>

                    <pool>

                        <prefill>

                            false

                        </prefill>

                        <use-strict-min>

                            false

                        </use-strict-min>

                        <flush-strategy>

                            FailingConnectionOnly

                        </flush-strategy>

                    </pool>

                    <security>

                        <user-name>

                          ****

                        </user-name>

                        <password>

                           **********

                        </password>

                    </security>

                </datasource>

                <drivers>

                    <driver name="h2" module="com.h2database.h2">

                        <xa-datasource-class>

                            org.h2.jdbcx.JdbcDataSource

                        </xa-datasource-class>

                    </driver>

                </drivers>

            </datasources>

All works fine, but i have to start the h2database by myself before i can use the connection.

[enrix]

I Once again. I have tried the proposal by nimo stephan, but it appears an error during the deploy-process. Although the h2.tool.server packages are includes, the following error is displayed:  java.lang.RuntimeException: Error getting reflective information for class

and this is caused by  java.lang.NoClassDefFoundError: Lorg/h2/tools/Server.

And here's what i have done:

	public void initialize_DB_Server(){
		try {
			this.server = Server.createTcpServer("jdbc:h2:tcp://localhost/~/amatodb").start();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

[mazi]

Hi everybody!

I've been having issues with H2 and I can't believe this isn't documented, or at least coherently explained, anywhere. So what I learned today, and also my problem, in hope that it might save someone from spending ours trying to get a seemingly simple thing done, or that someone might help me with this..

Henry, your connection url jdbc:h2:tcp://localhost/~/amatodb will try to connect to a tcp server. For this, a H2 TCP server must be running and no, as far as I know, JBoss does not automatically start a h2 tcp server. Note the difference between "starting a h2 database" and "starting a h2 tcp server": the database can be up and running without any tcp server (which in fact is the case with the default JB7 h2 datasource config in standalone.xml). So Henry, if you just want to access the database from your application deployed in JBoss (and from nowhere else), you can just forget about the TCP server and try connecting to the database wit an in-memory URL like jdbc:h2:mem:amatodb. No TCP server needed for this. The database will be provided for you by JBoss. (Same goes for embedded mode.)

Now what my problem is (and I think some other people's as well) and to which I still don't know a good solution, is this. I'd like to use a H2 in my app via a datasource, and in addition to this I would like to connect to the same database with an external client (eg. SquirrelSQL) to see the contents and structure of the database. (I know I might also use h2's web server, but I don't want to.) Since databases in embedded and in-memory modes can only be accessed from the same VM (same classloader, actually), the only option would be to use the server mode. This brings me to the question (which is indeed very similar to the original question of this topic and is what I think Henry really ment):

Is it possible to configure JBoss AS to start the H2 tcp server automatically? If yes, how?

If this were possible, one could use a tcp url (jdbc:h2:tcp://localhost/xxxx) in the datasource definition as well as from any other client, at the same time.

I found these, they helped to shed some light on the issues but they don't really help with my problem because it's no use trying to start the h2 TCP server from within my app as the TCP server must be running before the datasource is deployed (as explained, the datasource must connect to a tcp url, since connecting it to an in-memory or embedded url would mean no external processes can access the same database):

http://stackoverflow.com/questions/7309359/view-content-of-h2-or-hsqldb-in-memory-database

http://java.dzone.com/articles/share-h2-memory-database-among

So, can anyone answer the above question?

Matija

[jaikiran]

Matija Mazi wrote:

Is it possible to configure JBoss AS to start the H2 tcp server automatically? If yes, how?

H2 server isn't different from any other DB servers. They should be started as a separate process outside of the AS. The application server won't be starting the DB server. You could however setup a service (how to do that depends on your operating system) that starts the H2 server when you startup your system. Steps for that will probably be available in the H2 documentation.

Message was edited by: jaikiran pai - Fixed typo

[mazi]

That's what I thought, and it makes sense. Thanks for clearing that up!

[alesj]

Afair, there was a H2 (HSQL / Hyper) service in previous AS' that was able to start this tcp mode.

Imo, it should be easy to re-write it to new AS7 service notion;

e.g. we still support MBeans from jboss-service.xml

[alesj]

Afair, there was a H2 (HSQL / Hyper) service in previous AS' that was able to start this tcp mode.

Imo, it should be easy to re-write it to new AS7 service notion;

e.g. we still support MBeans from jboss-service.xml

This is what I had in mind:

* http://anonsvn.jboss.org/repos/jbossas/trunk/varia/src/main/java/org/jboss/jdbc/HypersonicDatabase.java

There is also Derby support:

* http://anonsvn.jboss.org/repos/jbossas/trunk/varia/src/main/java/org/jboss/jdbc/

[leandrofranca]

Someone else could do this?