Apache Derby DataSource configuration in Weblogic Server

Key Points to Note - 

• You can specify your own connection properties while configuring Derby Data Source as its going to create Database instance at runtime with whatever DB details provide as below -

• It creates Derby DB instance files under derby/bin below path by default -/opt/thirdparty/middleware/oracle/wls11g/common/derby/bin

-rwxr-x---. 1 g800077 g800077 4853 Aug  4 10:16 derby_common.sh

• -rwxr-x---. 1 g800077 g800077 5740 Aug  4 10:16 dblook
• -rwxr-x---. 1 g800077 g800077 1079 Aug  4 10:16 setNetworkClientCP
• -rwxr-x---. 1 g800077 g800077 1073 Aug  4 10:16 setEmbeddedCP
• -rwxr-x---. 1 g800077 g800077 5801 Aug  4 10:16 NetworkServerControl
• -rwxr-x---. 1 g800077 g800077 5876 Aug  4 10:16 ij
• -rwxr-x---. 1 g800077 g800077 5807 Aug  4 10:16 startNetworkServer
• -rwxr-x---. 1 g800077 g800077 1075 Aug  4 10:16 setNetworkServerCP
• -rwxr-x---. 1 g800077 g800077 5789 Aug  4 10:16 sysinfo
• -rwxr-x---. 1 g800077 g800077 5810 Aug  4 10:16 stopNetworkServer
• -rwxr-x---. 1 g800077 g800077  240 Aug  4 10:16 startNetworkServer.sh
• -rwxr-x---. 1 g800077 g800077  170 Aug  4 10:16 stopNetworkServer.sh
• -rw-rw-r--. 1 g800077 g800077 1021 Aug 10 05:00 derby.log
• drwxrwxr-x. 5 g800077 g800077 4096 Aug 10 05:00 derbyDB

Start Derby DB services using class -

• Manually setting up CLASSPATH for certain jar's to test 

export CLASSPATH=/opt/thirdparty/middleware/oracle/wls11g/common/derby/lib/derbytools.jar:$CLASSPATH
export CLASSPATH=/opt/thirdparty/middleware/oracle/wls11g/common/derby/lib/derbynet.jar:$CLASSPATH
java org.apache.derby.drda.NetworkServerControl start -p 1027

Most of you might already worked on this. Anyone who has not got chance to work on it and a note to myself on the step by step Apache Derby datasource configuration in Weblogic.

Steps to Configure Apache Derby Datasource in Weblogic

A. Weblogic v10.3.3 and below
Pre-requisites:
--JDK 1.7 with Java DB [Apache Derby distribution] included.
   Java DB is Oracle's supported distribution of the Apache Derby open source database. It supports standard ANSI/ISO SQL through the JDBC and Java EE APIs.

1a. Java DB startup - Navigate to JDK7 <JAVA_HOME_JDK7>/db/bin directory and execute the startNetworkServer.bat or startNetworkServer.sh per your OS preference.
By default, the Java DB listens on TCP/IP port 1527. The listen port can always be modified, refer this documentation.
1b. Download and supplement the Derby JDBC driver jar files to the Weblogic domain classpath in setDomainEnv.cmd or setDomainEnv.sh under <DOMAIN_HOME>/bin. Restart the Weblogic domain for the Derby JDBC driver class to be accessible.
   derbyclient.jar - <JAVA_HOME_JDK7>/db/lib/derbyclient.jar
   derbytools.jar - <JAVA_HOME_JDK7>/db/lib/derbytools.jar

Example:
     set JAVA_HOME_JDK7=E:\Java\jdk1.7.0_25
     set PRE_CLASSPATH=%JAVA_HOME_JDK7%\db\lib\derbyclient.jar;%JAVA_HOME_JDK7%\db\lib\derbytools.jar

B. Weblogic v10.3.4 and above
Starting version Weblogic 10.3.4 the Apache Derby DB is included in the installation along with Weblogic. 

1a. Java DB startup - Set DERBY_FLAG to true in your setDomainEnv.cmd or setDomainEnv.sh under<DOMAIN_HOME>/bin. This setting would launch Java DB startup during the startup of Weblogic instance. Restart the Weblogic instance to bring up the Java DB along with Weblogic.


2. If you have not already done so, in the Change Center of the Administration Console, click "Lock & Edit"

3. In the left pane, navigate to Services > JDBC > Data Sources

4. On the right pane, click on New to add new Data Source

5. Specify Name and JNDI name of the Data Source per your preference. Select the Database Type as Derby and appropriate Database Driver. Click Next

6. I have unchecked the Supports Global Transactions option.Again it is per your preference.

7. Specify the Connection properties and click Next

     Database Name can be absolute path location of the database files to be saved.

     Example: E:\DerbyDB\ApacheDerbyDB

     Or
     It can be relative path location, you will have to just mention dbName and the database files will be created under <JAVA_HOME_JDK7>\db\bin\ [Option A] or <WEBLOGIC_HOME>\common\derby\bin\ [Option B]

     Example: If the dbName is blogdomain_db, E:\Java\jdk1.7.0_25\db\bin\blogdomain_db or C:\Middleware_10.3.4\wlserver_10.3\common\derby\bin\blogdomain_db would be the location of the database files.

     Host Name where the database runs.

     Port on which the database listens for connections

     Username and Password

8. Verify Connection properties, click Test Configuration and proceed Next. If you notice under propertiescreate=true is automatically added, the database will be automatically created if not present already.

9. Select targets for the Data Source. Click Finish

10. In the Change Center of the Administration Console, click "Activate Changes".

Here is derby DS configuration xml file - 

cd /opt/thirdparty/middleware/oracle/user_projects/domains/SyniverseDomain/config/jdbc
derbyDS-8519-jdbc.xml

<?xml version='1.0' encoding='UTF-8'?>
<jdbc-data-source xmlns="http://xmlns.oracle.com/weblogic/jdbc-data-source" xmlns:sec="http://xmlns.oracle.com/weblogic/security" xmlns:wls="http://xmlns.oracle.com/weblogic/security/wls" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/weblogic/jdbc-data-source http://xmlns.oracle.com/weblogic/jdbc-data-source/1.2/jdbc-data-source.xsd">
  <name>derbyDS</name>
  <jdbc-driver-params>
    <url>jdbc:derby://localhost:1527/derbyDB;ServerName=localhost;databaseName=derbyDB</url>
    <driver-name>org.apache.derby.jdbc.ClientDataSource</driver-name>
    <properties>
      <property>
        <name>user</name>
        <value>derbyuser</value>
      </property>
      <property>
        <name>portNumber</name>
        <value>1527</value>
      </property>
      <property>
        <name>databaseName</name>
        <value>derbyDB;create=true</value>
      </property>
      <property>
        <name>serverName</name>
        <value>localhost</value>
      </property>
    </properties>
    <password-encrypted>{AES}LI8Wu1l8eRLG38DoayN52MQ8qutL/DB7WVuzfNM3ERQ=</password-encrypted>
  </jdbc-driver-params>
  <jdbc-connection-pool-params>
    <test-table-name>SQL SELECT 1 FROM SYS.SYSTABLES</test-table-name>
  </jdbc-connection-pool-params>
  <jdbc-data-source-params>
    <jndi-name>derbyDS</jndi-name>
    <global-transactions-protocol>TwoPhaseCommit</global-transactions-protocol>
  </jdbc-data-source-params>
</jdbc-data-source>

Thanks,

Srikanth Govada