How to set up Apache Derby JDBC data source in Oracle WebLogic application server

I will demonstrate how to to set up Apache Derby JDBC data source in Oracle WebLogic 12c application server on a Microsoft Windows machine.

I was learning Java Persistence API. I had WebLogic 12.1.1 application server installed on my laptop running Microsoft Windows 7.

I needed a database with a small footprint. I selected Java DB database because it is included in JDK 7. 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.

In JDK 7, Java DB is available in <JDK_HOME>/db directory.

Now we need to set up a JDBC data source in the application server. We will run the Apache Derby database as a Network Server and the JDBC data source will connect to the database server using the client drivers.

Hereafter,

The term “database” refers to the Apache Derby database
The term “database server” refers to the Apache Derby Network Server
The term “client” refers to a client of the database server
The term “application server” refers to the WebLogic 12c Application Server
The term “domain” refers to WebLogic Application Server Domain
The term “data source” refers to a JDBC data source.

Starting the database server

Execute <JDK_HOME>/db/bin/startNetworkServer.bat. By default, the server listens on TCP/IP port 1527. It is possible to specify a port other than the default. Refer to the documentation for more details.

Adding database client jars to the application server class path

We need to add the database’s client jars to the class path of the application server because the JDBC data source in the application server will be a client to the database server.

The names of the client jars can be found in setNetworkClientCP.bat file in <JDK_HOME>/db/bin directory.

To add those client jars to the class path of a specific WebLogic domain, edit the PRE_CLASSPATH variable in setDomainEnv.cmd file in <DOMAIN_HOME>/bin directory.

Example:

set PRE_CLASSPATH=%JAVA_HOME%\db\lib\derbyclient.jar;%JAVA_HOME%\db\lib\derbytools.jar

To add the client jars to the class path of the WebLogic server, edit the WEBLOGIC_CLASSPATH variable in commEnv.cmd file in <WL_HOME>/wlserver/common/bin directory.

Configuring data source in the application server

  • Open the Admin Console
  • Go to ‘Data Sources’ page

  • Start creating a new generic data source

  • Specify the name and JNDI name of the data source. Select “Derby” as the database type. Click ‘Next’ button.

  • Select the appropriate Derby database driver . Click the ‘Next’ button.

  • Specify the connection details

‘Database Name’ is the path of the directory where the database files will be saved. Example, D:\root\personal\software\Development\derby_databases\sample2

If a relative path is specified, the database will be created in <JDK_HOME>/db/bin/<relative-path> directory. For example, if the database name is specified as just “sample2”, the database will be created at <JDK_HOME>/db/bin/sample2 directory.

‘Database User Name’ is the name of the database schema that the data source should use.
‘Host Name’ is the name of the machine where the database server runs.
‘Port’ is the port at which the database server listens for connection requests.
Provide a ‘Password’ and remember it.

  • Verify the connection details

Notice that the property ‘create=true’ is automatically added. This means that the database will be automatically created if not present already.

  • Test the configuration by clicking ‘Test Configuration’ button. Once the connection is successfully tested, click the ‘Finish’ button.

Now you can see the data source on the summary page

  • Select the application server on which the data source should be deployed.

Select the data source on the summary page. Go to ‘Targets’ tab. Select the application server. Click the ‘Save’ button.

A message will be displayed on the console indicating whether the deployment was successful or not.

The data source has been set up. You can view the data source on ‘JDBC Data Sources Summary’ page

Because the data source had property ‘create=true’, the database will be automatically created at the location specified in the ‘Database Name’ field.

Connecting to the database server via command-line tools

  • Execute <JDK_HOME>/db/bin/ij.bat. This starts the command-line tool called ij.
  • Connect to the database server

ij> connect ‘jdbc:derby://localhost:1527/D:\root\personal\software\Development\derby_databases\sample2;ServerName=localhost;databaseName=D:\root\personal\software\Development\derby_databases\sample2’;

The exact URL of the database can be found in the application server’s console.

  • View all the schemas in the database

ij> show schemas;

TABLE_SCHEM
————–
APP
NULLID
SQLJ
SYS
SYSCAT
SYSCS_DIAG
SYSCS_UTIL
SYSFUN
SYSIBM
SYSPROC
SYSSTAT

  • View all tables in the database

ij> show tables;

TABLE_SCHEM |TABLE_NAME
————————————–
SYS |SYSALIASES
SYS |SYSCHECKS
SYS |SYSCOLPERMS
SYS |SYSCOLUMNS
SYS |SYSCONGLOMERATES
SYS |SYSCONSTRAINTS
SYS |SYSDEPENDS
SYS |SYSFILES
SYS |SYSFOREIGNKEYS
SYS |SYSKEYS
SYS |SYSPERMS
SYS |SYSROLES
SYS |SYSROUTINEPERMS
SYS |SYSSCHEMAS
SYS |SYSSEQUENCES
SYS |SYSSTATEMENTS
SYS |SYSSTATISTICS
SYS |SYSTABLEPERMS
SYS |SYSTABLES
SYS |SYSTRIGGERS
SYS |SYSVIEWS
SYSIBM |SYSDUMMY1

  • View tables in a specific schema

ij> show tables in SYSIBM;

TABLE_SCHEM |TABLE_NAME
——————————-
SYSIBM |SYSDUMMY1

  • View structure of a table

ij> describe sys.systables;

COLUMN_NAME |TYPE_NAME|
——————————-
TABLEID |CHAR |
TABLENAME |VARCHAR |
TABLETYPE |CHAR |
SCHEMAID |CHAR |
LOCKGRANULARITY |CHAR |

You can do much more using the ij tool. See the documentation for more information.

Advertisements
This entry was posted in Application Server, Database, Java, Java EE, WebLogic and tagged , , , , , , . Bookmark the permalink.

2 Responses to How to set up Apache Derby JDBC data source in Oracle WebLogic application server

  1. Yosalfa R. C. says:

    Clear explanation. Waiting eagerly for the JPA example 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s