14 Replies Latest reply on Sep 27, 2012 11:20 AM by leandrofranca

    Start h2 database

    Henry F. Newbie

      Dear Community,

       

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

       

      Best regards.

        • 1. Re: Start h2 database
          jaikiran pai Master

          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.

          • 2. Re: Start h2 database
            Henry F. Newbie

            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.

            • 3. Re: Start h2 database
              jaikiran pai Master

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

              • 4. Re: Start h2 database
                Henry F. Newbie

                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?

                • 5. Re: Start h2 database
                  nimo stephan Master

                  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.

                  • 6. Re: Start h2 database
                    jaikiran pai Master

                    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.

                    • 7. Re: Start h2 database
                      Henry F. Newbie

                      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.

                      • 8. Re: Start h2 database
                        Henry F. Newbie

                        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();


                        }

                        }

                         

                         


                        • 9. Re: Start h2 database
                          Matija Mazi Newbie

                          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

                          • 10. Re: Start h2 database
                            jaikiran pai Master

                            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

                            • 11. Re: Start h2 database
                              Matija Mazi Newbie

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

                              • 12. Re: Start h2 database
                                Ales Justin Master

                                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

                                • 13. Re: Start h2 database
                                  Ales Justin Master

                                  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/

                                  • 14. Re: Start h2 database
                                    leandrofranca Newbie

                                    Someone else could do this?