14 Replies Latest reply on Nov 27, 2006 11:10 PM by Norman Richards

    Where is the database?

    David Geary Newbie

      I have a simple address book seam application that runs in Tomcat using JBoss embedded EJB3.0. I have a stock persistence.xml:

      <persistence>
       <persistence-unit name="userDatabase">
       <provider>org.hibernate.ejb.HibernatePersistence</provider>
       <jta-data-source>java:/DefaultDS</jta-data-source>
       <properties>
       <property name="hibernate.hbm2ddl.auto" value="create-drop"/>
       </properties>
       </persistence-unit>
      </persistence>


      The datasource is java:/DefaultDS, but where is the actual database defined? Is the database HSQL? MYSQL? Where is it? How do I access it?

      I realize this may be my ignorance of EJB3, but I have no idea where the default data source is defined, and I would like to access the database.

      Thanks

        • 1. Re: Where is the database?
          Gavin King Master

          It is the datasource that exists in any default install of JBoss AS.

          • 2. Re: Where is the database?
            David Geary Newbie

            Sorry, but I have no idea what that means. Could you be a little more explicit? Perhaps there's an XML file in some JAR somewhere that defines the database for JBoss embedded EJB 3.0?

            • 3. Re: Where is the database?
              Norman Richards Master

              Check your jboss-beans.xml. I'm sure it is referencing an embedded hsql instance.

              • 4. Re: Where is the database?
                Devon Hillard Novice

                If you're using the default setup, the DefaultDS is configured to use HSQL (http://www.hsqldb.org).

                The settings for DefaultDS are configured here (you can get the jdbc info here as well): $JBOSS_HOME/servers/$YOUR_SERVER/deploy/hsqldb-ds.xml

                The data files themselves will be under $JBOSS_HOME/servers/$YOUR_SERVER/data

                You should be able to access the db using any jdbc client using the settings in the .xml config file.

                Does that answer your question?

                Modoc

                • 5. Re: Where is the database?
                  David Geary Newbie

                  Thanks, Norman. I had to dig through my JARs until I found embedded-jboss-beans.xml in mc-conf.jar, and in that XML file, I found the db definition (shown below).

                  Now the only question I have is how do I access that database from the command line? With mysql, I can just run the mysql command and do a 'show databases;' and I'm off and running. How do I do the same thing with hsqldb?

                  Another question: How do I change to mysql instead of hsqldb? Do I have to unjar mc-conf.jar, edit embedded-jboss-beans.xml, then reJAR the JAR file and restart the application?

                  Sorry if this is more about mysql and hsqldb than Seam. I have no problems with Seam (yet!) and I really want to look at the database that Seam is manipulating. Somehow, it doesn't seem like it should be this difficult.

                  Thanks for your help.


                   <!--
                   <bean class="org.jboss.jdbc.HypersonicDatabase"
                   name="jboss:service=Hypersonic,database=localDB">
                   <property name="database">localDB</property>
                   <property name="inProcessMode">true</property>
                   <property name="dbDataDir">.</property>
                   </bean>
                   -->
                  
                   <bean name="DefaultDSBootstrap" class="org.jboss.resource.adapter.jdbc.local.LocalTxDataSource">
                   <property name="driverClass">org.hsqldb.jdbcDriver</property>
                   <property name="connectionURL">jdbc:hsqldb:.</property>
                   <!-- <property name="connectionURL">jdbc:hsqldb:./hypersonic/localDB</property> -->
                   <property name="userName">sa</property>
                   <property name="jndiName">java:/DefaultDS</property>
                   <property name="minSize">0</property>
                   <property name="maxSize">10</property>
                   <property name="blockingTimeout">1000</property>
                   <property name="idleTimeout">100000</property>
                   <property name="transactionManager"><inject bean="TransactionManager"/></property>
                   <property name="cachedConnectionManager"><inject bean="CachedConnectionManager"/></property>
                   <property name="initialContextProperties"><inject bean="InitialContextProperties"/></property>
                   </bean>
                  
                   <bean name="DefaultDS" class="java.lang.Object">
                   <constructor factoryMethod="getDatasource">
                   <factory bean="DefaultDSBootstrap"/>
                   </constructor>
                   </bean>
                  
                  
                  
                  


                  • 6. Re: Where is the database?
                    Devon Hillard Novice

                     

                    Another question: How do I change to mysql instead of hsqldb? Do I have to unjar mc-conf.jar, edit embedded-jboss-beans.xml, then reJAR the JAR file and restart the application?


                    All you should have to do is to setup the mysql data source in a new xml file in the deploy directory. You can either create a new JNDI data source (MySQLDS) if you want to use it for new code, or replace the existing DefaultDS with a mysql based definition. See the file in my reply above for more info.

                    You shouldn't need to unjar things.

                    • 7. Re: Where is the database?
                      Maurice Zeijen Master

                      It would be best if you would read up on DataSources in a JBoss environment. Take a look at http://wiki.jboss.org/wiki/Wiki.jsp?page=DSdotXML to get started ;). There are example {name}-ds.xml files in your JBoss installation in $JBOSS_ROOT\docs\examples\jca.

                      Good luck.

                      • 8. Re: Where is the database?
                        David Geary Newbie

                        Thanks everyone for the replies. Looks like it's going to be quite awhile before I can take a look at the db that Seam's manipulating.

                        I'll go and RTFM. Sigh.

                        It would be really nice if configuring and looking in the database was 1/2 as easy as it is with Ruby on Rails, but I guess that's asking alot.

                        Thanks again...

                        • 9. Re: Where is the database?
                          Devon Hillard Novice

                          Well, you should really be using an HSQL instance for production use (IMHO) anyhow, so just use mysql or postgres or whatever you're comfortable with. It shouldn't be any trickier than RoR (although I'll admit I haven't gotten too deep into RoR).

                          • 10. Re: Where is the database?
                            David Geary Newbie

                            I've given up on using MYSQL. I don't have time to read through copious amounts of documentation on DataSources in a JBoss environment.

                            So now I'm in this directory...

                            /Applications/jboss-4.0.4RC1/server/default/data/hypersonic

                            ...and I'm trying to access the database with HSQL's database manager with this command:

                            java -cp /Developer/Java/Tools/hsqldb/lib/hsqldb.jar org.hsqldb.util.DatabaseManager

                            When the db mgr starts up, I need to select Type from the pulldown, with about 15 options, half of which throw Java exceptions in the command line window that I started the mgr from. Which one do I select? If I select HSQL Database Engine In-Memory, I don't see any tables at all. Do I need to specify a URL other than jdbc:hsqldb:mem:.?

                            btw, I don't have a $JBOSS_HOME/servers directory. I do have $JBOSS_HOME/server directory that contains a default directory with one other directory in it: hypersonic. I assume that's where I should be trying to access the database.

                            For goodness sakes, all I want to do is see the tables that Seam is allegedly updating. This really shouldn't be so difficult (believe me it's an absolute no-brainer with Rails). Sorry for the rant, but I've now spent about 3 hours just trying to look at my db tables, and I've had nothing but grief.

                            • 11. Re: Where is the database?
                              Devon Hillard Novice

                              Google is your friend:

                              http://wiki.jboss.org/wiki/Wiki.jsp?page=SetUpAMysqlDatasource

                              shows how to use mysql under jboss in a couple of minutes.

                              I can't help you with the hsql db manager, as I don't use hsql.

                              Yes sorry, I mistyped servers instead of server. That is the directory you want.

                              Assuming I don't want to read copious amounts of documentation, how would I do this with RoR? I'm just curious, as I've heard a lot about Rails (both good and bad).

                              Anyhow, I will say, as someone who still doesn't have a solid grasp on much of JBoss, that reading documentation is a must for understanding a new technology, and that JBoss is likely to be VERY different than RoR, or any other non-J2EE Java based app server.

                              • 12. Re: Where is the database?
                                Norman Richards Master

                                I don't normally run outside JBoss, so I can only give you a few pointers for hsql.

                                Hsql CAN accept tcp connections. Try setting the URL to something like jdbc:hsqldb:hsql://localhost:1701. You may need to add another component to physically start the hsql server, but it shouldn't be that hard to figure out.

                                On the other hand, you can also use a URL like jdbc:hsqldb:/path/to/mydb/property .Then your db will be stored in that file. Another process like the db manager can examine that file, but you want to be careful with two active processes reading the file.

                                A third option is to launch the DB manager from WITHIN your application. Yeah, it sounds silly to write launch a swing app from a web app, but the jboss admin console does just that. It's ugly, but it works.

                                Finally, another option is to look at this: http://members.capmac.org/~orb/blog.cgi/tech/java/A_quick_and_dirty_E.html . It is very simple query tool that you can use to type ejbql from a web page. You can add it to your app and examine your application freely. I haven't used it in a while, but I'm sure it shouldn't take more than 5 minutes to get set up.


                                • 13. Re: Where is the database?
                                  David Geary Newbie

                                  Thanks for the suggestions, Norman. I tried setting the URL you suggested in the HSQLDB manager connect dialog, but that throws an exception. I tried numerous other variants and they all throw exceptions too.

                                  I really don't want to have to figure out how to launch the DB manager from within the application, or try to install a Seam component just to examine tables. All of that is way too much trouble for what should be a very simple thing.

                                  I find it difficult to believe that I cannot just type in a URL in the HSQLDB manager connect dialog and access my database when using JBoss embedded EJB3 in Tomcat. I have now spent an entire day trying to just view my db tables. I am really, really, frustrated. This is really quite ridiculous.

                                  Does anyone know how to do this? Is anyone running Seam in Tomcat with the JBoss embedded EJB3 server? If so, how do you access the HSQL database?

                                  To summarize: I have a simple Seam app that manipulates one db table and I want to view that table in the database. My application runs in Tomcat with the JBoss embedded EJB3 server. In $JBOSS_HOME/server/default/data/hypersonic, I start the HSQLDB manager with this command:

                                  java -cp /Developer/Java/Tools/hsqldb/lib/hsqldb.jar org.hsqldb.util.DatabaseManagerSwing

                                  I can connect with the default info in the Contact dialog:

                                  Type: HSQL Database Engine In-Memory
                                  Driver: org.hsqldb.jdbcDriver
                                  URL: jdbc:hsqldb:mem:.
                                  User: sa

                                  These are the same as the settings in the embedded-jboss-beans.xml that's in the mc-conf.jar file in resources/WEB-INF/lib, but I don't see any database tables. If I try a different URL, such as jdbc:hsqldb:hsql://localhost:1701, I get a socket connection error.

                                  Does anyone know what to type in the Connect dialog so that I can connect to my database and view my table?

                                  • 14. Re: Where is the database?
                                    Norman Richards Master

                                    You CANNOT connect to an in memory database from outside the process. You must either do the research to figure out how to start HSQL in a mode that does allow external connections or use a database instance running in another process. For the latter, 2 minutes looking at the HSQL docs shows that you can start up a database instance like this:

                                    java -cp server/default/lib/hsqldb.jar org.hsqldb.Server -database.0 dbfile -dbname.0 dbname


                                    Then you can access the db using this URL:

                                    jdbc:hsqldb:hsql://localhost/dbname


                                    That is how you access from your app and from the database manager.