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.
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.
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;
- View all tables in the database
ij> show tables;
- View tables in a specific schema
ij> show tables in SYSIBM;
- View structure of a table
ij> describe sys.systables;
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.