Apache jUDDICommunity Documentation

Chapter 4. Database Setup

4.1. Derby Out-of-the-Box
4.2. Switch to MySQL
4.3. Switch to Postgres
4.4. Switch to Oracle
4.5. Switch to HSQL
4.6. Switch to <other db>
4.7. Override persistence properties in the juddiv3.properties

By default jUDDI uses an embedded Derby database. This allows us to build a downloadable distribution that works out-of-the-box, without having to do any database setup work. We recommend switching to an enterprise-level database before going to production. JUDDI uses the Java Persistence API (JPA) in the back end and we've tested with both OpenJPA and Hibernate. To configure which JPA provider you want to use, you will need to edit the configuration in the persistence.xml. This file can be found in the juddi.war/WEB-INF/classes/META-INF/persistence.xml

For Hibernate the content of this file looks like

<?xml version="1.0" encoding="UTF-8"?>
<persistence 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_1_0.xsd" 
    version="1.0">
    <persistence-unit name="juddiDatabase" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <jta-data-source>java:comp/env/jdbc/JuddiDS</jta-data-source>
        <!-- entity classes -->
        <class>org.apache.juddi.model.Address</class>
        <class>org.apache.juddi.model.AddressLine</class>
        <class>org.apache.juddi.model.AuthToken</class>
        <class>org.apache.juddi.model.BindingCategoryBag</class>
        <class>org.apache.juddi.model.BindingDescr</class>
        <class>org.apache.juddi.model.BindingTemplate</class>
        <class>org.apache.juddi.model.BusinessCategoryBag</class>
        <class>org.apache.juddi.model.BusinessDescr</class>
        <class>org.apache.juddi.model.BusinessEntity</class>
        <class>org.apache.juddi.model.BusinessIdentifier</class>
        <class>org.apache.juddi.model.BusinessName</class>
        <class>org.apache.juddi.model.BusinessService</class>
        <class>org.apache.juddi.model.CategoryBag</class>
        <class>org.apache.juddi.model.Contact</class>
        <class>org.apache.juddi.model.ContactDescr</class>
        <class>org.apache.juddi.model.DiscoveryUrl</class>
        <class>org.apache.juddi.model.Email</class>
        <class>org.apache.juddi.model.InstanceDetailsDescr</class>
        <class>org.apache.juddi.model.InstanceDetailsDocDescr</class>
        <class>org.apache.juddi.model.KeyedReference</class>
        <class>org.apache.juddi.model.KeyedReferenceGroup</class>
        <class>org.apache.juddi.model.OverviewDoc</class>
        <class>org.apache.juddi.model.OverviewDocDescr</class>
        <class>org.apache.juddi.model.PersonName</class>
        <class>org.apache.juddi.model.Phone</class>
        <class>org.apache.juddi.model.Publisher</class>
        <class>org.apache.juddi.model.PublisherAssertion</class>
        <class>org.apache.juddi.model.PublisherAssertionId</class>
        <class>org.apache.juddi.model.ServiceCategoryBag</class>
        <class>org.apache.juddi.model.ServiceDescr</class>
        <class>org.apache.juddi.model.ServiceName</class>
        <class>org.apache.juddi.model.ServiceProjection</class>
        <class>org.apache.juddi.model.Subscription</class>
        <class>org.apache.juddi.model.SubscriptionChunkToken</class>
        <class>org.apache.juddi.model.SubscriptionMatch</class>
        <class>org.apache.juddi.model.Tmodel</class>
        <class>org.apache.juddi.model.TmodelCategoryBag</class>
        <class>org.apache.juddi.model.TmodelDescr</class>
        <class>org.apache.juddi.model.TmodelIdentifier</class>
        <class>org.apache.juddi.model.TmodelInstanceInfo</class>
        <class>org.apache.juddi.model.TmodelInstanceInfoDescr</class>
        <class>org.apache.juddi.model.TransferToken</class>
        <class>org.apache.juddi.model.TransferTokenKey</class>
        <class>org.apache.juddi.model.UddiEntity</class>
        <class>org.apache.juddi.model.UddiEntityPublisher</class>

        <properties>
            <property name="hibernate.archive.autodetection" value="class"/>
            <property name="hibernate.hbm2ddl.auto" value="update"/>
            <property name="hibernate.show_sql" value="false"/>
            <property name="hibernate.dialect" 
                value="org.hibernate.dialect.DerbyDialect"/>
        </properties>
    </persistence-unit>
</persistence>

The persistence.xml reference a datasource “java:comp/env/jdbc/JuddiDS”. Datasource deployment is Application Server specific. If you are using Tomcat, then the datasource is defined in juddi/META-INF/context.xml which by default looks like

<?xml version="1.0" encoding="UTF-8"?>
<Context>
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <!--  -->
    <Resource name="jdbc/JuddiDS" auth="Container"
        type="javax.sql.DataSource" username="" password=""
        driverClassName="org.apache.derby.jdbc.EmbeddedDriver" 
        url="jdbc:derby:juddi-derby-test-db;create=true"
        maxActive="8" 
        />  
</Context>

To switch over to MySQL you need to add the mysql driver (i.e. The mysql-connector-java-5.1.6.jar) to the classpath and you will need to edit the persistence.xml

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

and the datasource. For tomcat you the context.xml should look something like

<?xml version="1.0" encoding="UTF-8"?>
<Context>
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <Resource name="jdbc/JuddiDS" auth="Container"
        type="javax.sql.DataSource" username="root" password=""
        driverClassName="com.mysql.jdbc.Driver" 
        url="jdbc:mysql://localhost:3306/juddiv3"
        maxActive="8"/>  
</Context>

To create a MySQL database name juddiv3 use

mysql> create database juddiv3

and finally you probably want to switch to a user which is a bit less potent then 'root'.

This was written from a JBoss - jUDDI perspective. Non-JBoss-users may have to tweak this a little bit, but for the most part, the files and information needed is here.

Logged in as postgres user, access psql:

# psql

postgres= CREATE USER juddi with PASSWORD 'password';
postgres= CREATE DATABASE juddi;
postgres= GRANT ALL PRIVILEGES ON DATABASE juddi to juddi;

Note, for this example, my database is called juddi, as is the user who has full privileges to the database. The user 'juddi' has a password set to 'password'.

<datasources>
    <local-tx-datasource>
        <jndi-name>JuddiDS</jndi-name>
        <connection-url>jdbc:postgresql://localhost:5432/juddi</connection-url>
        <driver-class>org.postgresql.Driver</driver-class>
        <user-name>juddi</user-name>
        <password>password</password>
        <!-- sql to call when connection is created.  Can be anything, 
        select 1 is valid for PostgreSQL 
        <new-connection-sql>select 1</new-connection-sql>
        -->
        <!-- sql to call on an existing pooled connection when it is obtained 
        from pool.  Can be anything, select 1 is valid for PostgreSQL
        <check-valid-connection-sql>select 1</check-valid-connection-sql>
        -->
        <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml -->
        <metadata>
            <type-mapping>PostgreSQL 8.0</type-mapping>
        </metadata>
    </local-tx-datasource>
</datasources>

In persistence.xml, reference the correct JNDI name of the datasource and remove the derby Dialect and add in the postgresql Dialect:

<jta-data-source>java:comp/env/jdbc/JuddiDS</jta-data-source>
    .....
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>

Be sure to have postgresql-8.3-604.jdbc4.jar in the lib folder!

To switch over to Oracle you need to add the oracle driver (i.e. theclasses12.jar) to the classpath and you will need to edit the persistence.xml

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

To create a Oracle database name juddiv3 with the ultimate in minimalism use

sqlplus> create database juddiv3;

then you probably want to switch to a user which is a bit less potent then 'root' and set the appropriate password.

Please check the Section 10.3, “Changing the Oracle Sequence name” if you want to change the Oracle Sequence name.

First make sure you have a running hsqldb. For a standalone server startup use:

		java -cp hsqldb.jar org.hsqldb.server.Server --port 1747 --database.0 file:juddi --dbname.0 juddi
		

Next, connect the client manager to this instance using:

		java -classpath hsqldb.jar org.hsqldb.util.DatabaseManagerSwing --driver org.hsqldb.jdbcDriver --url jdbc:hsqldb:hsql://localhost:1747/juddi  -user sa
		

and create the juddi user:

		CREATE USER JUDDI PASSWORD "password"  ADMIN;
		CREATE SCHEMA JUDDI AUTHORIZATION JUDDI;
		SET DATABASE DEFAULT INITIAL SCHEMA JUDDI;
		ALTER USER juddi set initial schema juddi;
		

From now on, one can connect as JUDDI user to that database and the database is now ready to go.

To switch over to HSQL you need to add the hsql driver (i.e. The hsqldb.jar) to the classpath and you will need to edit the persistence.xml

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

and the datasource. For tomcat you the context.xml should look something like

<?xml version="1.0" encoding="UTF-8"?>
<Context>
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <!-- HSQL data source -->
    <Resource name="jdbc/JuddiDS" auth="Container"
            type="javax.sql.DataSource" username="JUDDI" password="password"
            driverClassName="org.hsqldb.jdbcDriver"
            url="jdbc:hsqldb:hsql://localhost:1747/juddi"
            maxActive="8"
            />
</Context>

If you use another database, please document, and send us what you had to change to make it work and we will include it here.

The juddiv3.properties file can be externalized; if you give the path of juddiv3.properties in the JVM args, the juddiv3.properties will not be picked up from the WAR. To use this set the juddi.propertiesFile to a location of your configuration file. This allows the user to change the juddi properties without having to open up the juddiv3.war file. For this usecase it makes sense that also persistence properties can be overriden as well in the juddiv3.properties file. The following properties can be set:

property namedescriptionexample value
persistenceProviderJPA ImplementationHibernate
hibernate.connection.datasourcedatasource namejava:/jdbc/JuddiDS
hibernate.hbm2ddl.autohibernate to ddl setting update
hibernate.default_schemaSchema nameJuddiSchema
hibernate.dialectDataBase vendor nameorg.hibernate.dialect.DB2Dialect