HowTo: add a JDBC connection

1. Introduction

This HowTo page describes how to use the deegree 3 services console to add a new JDBC connection for accessing databases. The description is based on the deegree 3 utahDemo ready for download.

Services console login: "deegree" (without the quote signs) is the default password.

2. Add JDBC connection

1. Click on the jdbc link.
   

2. Enter a suitable name (postgis, oracle, mysql, whatever) for the connection and click on Create new.

3. In the following screen you will be able to edit the XML file of the new jdbc connection. The location of this file is displayed at the top. The name of the file matches the name that you entered in the previous step:

   Editing:
   /home/mays/deegree3/deegree-utah-demo/webapps/ROOT/WEB-INF/workspace/jdbc/mysql.xml

   Below that you get an xml editor with example entries to help you along.

   <JDBCConnection
    configVersion="3.0.0"
    xmlns="http://www.deegree.org/jdbc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.deegree.org/jdbc http://schemas.deegree.org/jdbc/3.0.0/jdbc.xsd">
   
     <!-- [1] JDBC URL (without username / password) -->
     <Url>jdbc:postgresql://localhost:5432/${DB_NAME}</Url>
   
     <!-- [1] DB username -->
     <User>${USERNAME}</User>
   
     <!-- [1] DB password -->
     <Password>${PASSWORD}</Password>
   
     <!-- default is false, set to true eg. for embedded derby databases or for better performance -->
     <ReadOnly>false</ReadOnly>
   
   </JDBCConnection>
   

   
   For the three main databases, PostGIS, Oracle Spatial and MySQL you can copy the following snippets and adapt the entries to your local needs.
   PostGIS example:

   <JDBCConnection
    configVersion="3.0.0"
    xmlns="http://www.deegree.org/jdbc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.deegree.org/jdbc http://schemas.deegree.org/jdbc/3.0.0/jdbc.xsd">
   
     <Url>jdbc:postgresql://localhost:5432/my_database_name</Url>
     <User>my_db_username</User>
     <Password>my_db_password</Password>
     <ReadOnly>false</ReadOnly>
   </JDBCConnection>
   

   
   Oracle example: Oracle connections are currently (3.0.1) not working. Support is planned for one of the upcoming versions.

• 
  MySQL example:

  <JDBCConnection
   configVersion="3.0.0"
   xmlns="http://www.deegree.org/jdbc"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://www.deegree.org/jdbc http://schemas.deegree.org/jdbc/3.0.0/jdbc.xsd">
  
    <Url>jdbc:mysql://localhost:3306/my_database_name</Url>
    <User>my_db_username</User>
    <Password>my_db_password</Password>
    <ReadOnly>false</ReadOnly>
  </JDBCConnection>
  

• To apply the changes, you need to click the Save button at the top of the editor. The editor window will close and the Reload link in the top left corner will turn red to inform you about changes that have not been forwarded to the server side yet.

• Click the Reload button to permanently store the changes in your configuration. (Follow-up message: Applying changes. Please wait).
  

What you have achieved now is a new JDBC connection for your local database.

3. Check success

1. Click on the link Test for the new created JDBC connection.
   

2. If the message at the bottom of the list sais: Connection 'my_db' OK, then you are ready for the next step!

3. If the message at the bottom of the list sais: Connection 'my_db' unavailable: No suitable driver found for ... you will need to add the required libraries to the classpath.

   • Please check if the db-jdbc-connector is located in deegree-utah-demo/webapps/ROOT/WEB-INF/lib/. It is recommendable to use the db-jdbc-connector with the highest available version. It should be equal to or higher than your database version.

     • MySQL: mysql-connector-java-5.1.13-bin.jar (or similar)

     • Oracle: ojdbc14_10g.jar (or similar)

     • PostgreSQL: postgresql-8.4-701.jdbc4.jar (provided) or postgresql-9.0-801.jdbc4.jar (or similar)

   • Restart the application by clicking the Reload button.

   • Test again.

4. What next ?

The JDBC connection is there already. Now we want a new feature store for our WFS or WMS! The logical follow-ups will be handled in other tutorials and teach you how to add a SimpleSQL feature store or how to add a PostGIS feature store.

5. Further reading

Other pages focusing on the deegree 3 services console:

Detailed description of the deegree 3 workspace and associated configuration pages:

---

CategoryDeegree3 CategoryHowTo CategoryServicesConsole