Guide Database Connection Setup

Database Connection Setup

Derby Out-of-the-Box

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 juddiv3.war/WEB-INF/classes/META-INF/persistence.xml. The content of this file is pretty standard between JPA implementations, however there can be slight differences. To make it easy we created different versions for different JPA implementations and target platforms. All JPA implementation have an enhancement phase, where the persistence 'model' classes are enhanced. Hibernate does this at runtime, OpenJPA prefers doing this at compile time. This is the reason we ship two versions of juddi-core, where the juddi-core-openjpa.jar contains classes (byte-code) enhanced by OpenJPA. This is the reason this jar is larger then the juddi-core.jar.

For Hibernate, for testing 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.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>

For OpenJPA the persistence.xml 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.apache.openjpa.persistence.PersistenceProviderImpl</provider>
    <non-jta-data-source>java:comp/env/jdbc/JuddiDS</non-jta-data-source>
    <!-- entity classes -->
    <class>org.apache.juddi.model.Address</class>
    <class>org.apache.juddi.model.AddressLine</class>
   ...
    <class>org.apache.juddi.model.UddiEntity</class>
    <class>org.apache.juddi.model.UddiEntityPublisher</class>
    <properties>
      <property name="openjpa.jdbc.SynchronizeMappings" value="buildSchema(SchemaAction='add')"/>
      <property name="openjpa.Log" value="DefaultLevel=WARN, Tool=INFO"/>
      <property name="openjpa.jdbc.UpdateManager" value="operation-order"/>
      <property name="openjpa.jdbc.DBDictionary" value="derby"/>
      <!-- dialects: derby, postgres, mysql, oracle, sybase, sqlserver
           for a complete list check the OpenJPA documentation -->
      <property name="openjpa.RuntimeUnenhancedClasses" value="warn"/>
      <property name="openjpa.Compatibility" value="CheckDatabaseForCascadePersistToDetachedEntity=true"/>
    </properties>
  </persistence-unit>
</persistence>

In this case we reference a jta-data-source called 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>

By default the juddiv3.war is configured to be used on Tomcat using OpenJPA. However the download bundle lets you specify different target platforms resulting in a different setup. In all cases it will point to the embedded Derby database.

Switching to another Database

We recommend switching to an enterprise-level database before going to production. Most JPA providers support a large number of Databases and switching to another database is achieved by updating the configuration settings in both the persistence.xml and datasource files. The recipe is:

change the database dialect in the persistence.xml.
change the database connection information in the datasource.
add the database specific driver to your classpath.
in some cases (Oracle is one such case) you will need to use sequences for the ID generation, in this case you will need an orm.xml file. We ship a orm.xml.example along side the persistence.xml. Rename this file and update this to your liking.

Some examples for specific databases are given below.

Warning	Tomcat copies the context.xml to <tomcat>/conf/CATALINA/localhost/juddiv3.xml, and if you update the context.xml it may not update this copy. You should simply delete the juddiv3.xml file after updating the context.xml.

Switch to MySQL on Tomcat using OpenJPA

Check if you have are using Hibernate of OpenJPA, by looking at the jars in the juddiv3.war/WEB-INF/lib. Edit the dialect in the persistence.xml For OpenJPA:

<property name="openjpa.jdbc.DBDictionary" value="mysql"/>

Next edit the datasource. For tomcat you need to update the juddiv3/META-INF/context.xml which 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>

Finally you need to add the MySQL mysql driver (i.e. The mysql-connector-java-5.1.6.jar) to the classpath. Note that this jar may already by in the tomcat/lib directory, in which case you can move on to the step and create the mysql juddiv3 database. 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, and delete the <tomcat>/conf/CATALINA/localhost/juddiv3.xml file.

Switch to Postgres on Tomcat using OpenJPA

Check if you have are using Hibernate of OpenJPA, by looking at the jars in the juddiv3.war/WEB-INF/lib. Edit the dialect in the persistence.xml For OpenJPA:

<property name="openjpa.jdbc.DBDictionary" value="postgres"/>

Next edit the datasource. For tomcat you need to update the juddiv3/META-INF/context.xml which 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="juddi" password="juddi"
            driverClassName="org.postgresql.Driver"
            url="jdbc:postgresql://localhost:5432/juddi"
            maxActive="8"
            />
</Context>

To create a MySQL database name juddi use

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

Be sure to have postgresql-8.3-604.jdbc4.jar to the classpath. Note that this jar may already by in the tomcat/lib directory, in which case the final step is to delete the <tomcat>/conf/CATALINA/localhost/juddiv3.xml file.

Switch to Postgres on JBoss using Hibernate

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:

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'. Next edit the juddi-ds.xml datasource file with the settings for the postgres connection info:

<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, for Hibernate on JBoss use:

<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.

Switch to Oracle on Tomcat using Hibernate

To switch over to Oracle you need to add the oracle driver (i.e. the_classes12.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, and delete the <tomcat>/conf/CATALINA/localhost/juddiv3.xml

Changing the Oracle Sequence name

If you are using Hibernate as a persistence layer for jUDDI, then Oracle will generate a default sequence for you ("HIBERNATE_SEQUENCE"). If you are using hibernate elsewhere, you may wish to change the sequence name so that you do not share this sequence with any other applications. If other applications try to manually create the default hibernate sequence, you may even run into situations where you find conflicts or a race condition.

The easiest way to handle this is to create an orm.xml file and place it within the classpath in a META-INF directory, which will override the jUDDI persistence annotations and will allow you to specify a specific sequence name for use with jUDDI. The orm.xml.example specifies a "juddi_sequence" sequence to be used with jUDDI. Rename this file and update it to your liking.

Switch to HSQL on Tomcat using Hibernate

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 jUDDI 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>

Switch to other db

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.

Override persistence properties in the juddiv3.properties

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 use case it makes sense that also persistence properties can be overridden as well in the juddiv3.properties file. The following properties can be set:

Table 1. Hibernate properties that can be referenced in the *juddiv3.properties* file
property name	description	example value
persistenceProvider	JPA Implementation	Hibernate
hibernate.connection.datasource	datasource name	java:/jdbc/JuddiDS
hibernate.hbm2ddl.auto	hibernate to ddl setting	java:/jdbc/JuddiDS
hibernate.default_schema	Schema name	JuddiSchema
hibernate.dialect	DataBase vendor name	org.hibernate.dialect.DB2Dialect

Contents

Database Connection Setup

Readme

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Guide Database Connection Setup

Database Connection Setup

Derby Out-of-the-Box

Switching to another Database

Switch to MySQL on Tomcat using OpenJPA

Switch to Postgres on Tomcat using OpenJPA

Switch to Postgres on JBoss using Hibernate

Switch to Oracle on Tomcat using Hibernate

Changing the Oracle Sequence name

Switch to HSQL on Tomcat using Hibernate

Switch to other db

Override persistence properties in the juddiv3.properties

Clone this wiki locally