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 <other db>

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 11.3, “Changing the Oracle Sequence name” if you want to change the Oracle Sequence name.

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.