Oracle WebLogic Server 12c: Configuring a JDBC Data Source

Setting Up the Schema in the Oracle Database

To set up the database schema required by the JDBC client application,  perform the following steps:
1.Download the jdbc_obe.zip file to the machine where your WebLogic Server domain and servers are. Extract the file contents to a local drive. In this tutorial, the files are extracted into the /home/oracle/jdbcobe/testds directory.
The zip file contains three files:
  • testds_oracle.sql 
    A SQL script to configure the required schema in the Oracle Database
  • testds.war 
    A simple web application used to test the configured data source
  • deploy_testds.py 
    A WebLogic Scripting Tool (WLST) script used to deploy the web application
2.Log in to the system as the user that started the Oracle Database. Open a Terminal  window and navigate to the location of the SQL script file, testds_oracle.sql. n this tutorial that is:
$ cd /home/oracle/jdbcobe/testds
Make sure that the database environment variables are set by printing them out:
$ echo $ORACLE_HOME 
/u01/app/oracle/product/11.2.0/xe 

$ echo $ORACLE_SID 
XE 

Note: The values of your environment variables may be different. If there are no  values printed, then set the variables to the proper values with:

$ export ORACLE_HOME=valid_value 
$ export ORACLE_SID=valid_value
Then invoke SQL Plus as the sysdba user:
$ $ORACLE_HOME/bin/sqlplus / as sysdba
Screenshot for Step
3.Connect to the database as the system user. You can give the password and the name of the database as you connect. In this tutorial the command is:
SQL> connect system/oracle@XE
Then run the SQL script with this command:
SQL> @testds_oracle.sql
Screenshot for Step

The script, testds_oracle.sql, creates a user called PBPUBLIC with the password   PBPUBLIC. It grants that user the abilities to create sessions, tables, and so on. It then connects to the database as that user and creates three tables:  
EMPLOYEE, WLS_CATALOG_ITEMS, and WLS_CLIENT_INFO. Finally, it inserts rows into those tables and exits.

Starting WebLogic Server

To start the administration server and the dizzy1 managed server of the domain, perform the  following steps:
1.If the administration server of the domain is not already running, it needs to be started. To start it, in a Terminal window, navigate to the domain directory, and enter the command:
$ ./startWebLogic.sh
Screenshot for Step

When prompted for a username and password, enter the credentials of the domain administrator.
2.The managed server dizzy1 should also be running. If it is not already running, start it. In another Terminal window, navigate to the domain directory, then cd into the bin subdirectory. Enter the command:
$ ./startManagedWebLogic.sh dizzy1 http://host01.example.com:8001
This runs the script to start the managed server named dizzy1. Also notice the argument that gives the URL of the domain's administration server:
http://host01.example.com:8001.
Note: Use the host name and port of the administration server of your domain.
Screenshot for Step

When prompted for a username and password, enter the credentials of the domain administrator.

Configuring a JDBC Data Source

To configure a JDBC data source by using the WebLogic Server administration console, perform the following steps:
1.After the servers are up and running, access the WebLogic Server administration console. Open a web browser and enter its URL:
http://hostname:port/console
In this tutorial, that is: http://host01.example.com:8001/console Note: Use the host name and port of the administration server of your domain. On the Welcome screen, log in using the Username and Password entered to start the servers.
Screenshot for Step
2.Creating a data source is a change to the domain's configuration, so the configuration must first be locked. In the Change Center, click the Lock & Edit button.
Screenshot for Step
3.In the administration console under Domain Structure, expand Services (by clicking the   + next to it). Then click Data Sources.
Screenshot for Step
4.On the right, notice that the Summary of JDBC Data Sources section appears. Under the Data Sources table heading, click the New drop-down list. Then select  Generic Data Source.
Screenshot for Step
5.On the first page of the Create a New JDBC Data Source wizard, enter the Name of  the data source asdizzyworldDS. Enter the JNDI Name of the data source as dizzyworldDS. Note: There is no requirement that the data source name and the JNDI name match. The JNDI name is the one to make note of, because it is used by JDBC clients to access  the data source. Use the Database Type drop-down list to select Oracle.
Then click Next.
Screenshot for Step
6.On the second page of the wizard, use the Database Driver drop-down list to select  *Oracle's Driver (Thin) for Instance connections; Versions:9.0.1 and later. Then click Next.
Screenshot for Step
7.On the next page of the wizard, keep all the default Transaction Options, and click Next.
Screenshot for Step
8.On the next page of the wizard, enter the Database Name. In this tutorial it is XE. Your database name may be diffrerent. Enter the Host Name of the dizzy1 managed server. In this tutorial it is host01.example.com.
Use the host name where your dizzy1 server resides. Leave the database Port at the default of 1521. (Unless it is different for your database.) Enter the Database User Name of PBPUBLIC. This is the user that was created by the
SQL script. Enter the database user's password twice, in the Password and Confirm Password fields.
The password isPBPUBLIC. Then click Next.
Screenshot for Step
9.On the next page of the wizard, click the Test Configuration button to check if a connection to the Database can be made, based upon the information entered.
Screenshot for Step
10.If the message "Connection test succeeded" is displayed, click Next.
If the connection test fails, use the Back button to review the entries made for the data source and correct any errors. If there are no errors in the entries and the test still fails, make sure your database is running.
Screenshot for Step
11.On the last page of the wizard, the data source is targeted. Targeting a data source to a server  means that the server will manage that data source and it will be available as one of the resources of that server.
Select the check box next to dizzy1. Then click Finish.
Screenshot for Step
12.In the Summary of JDBC Data Sources area, in the Data Sources table, the new data source,  dizzyworldDS, is listed.
Screenshot for Step
13.To modify the configuration of the new data source, click its name, dizzyworldDS, in the table. Screenshot for Step
14.Under the Settings for dizzyworldDS, click the Connection Pool tab under the Configuration tab.
Screenshot for Step
15.Scroll down to find the "capacity" fields.
For Initial Capacity enter 2.
For Maximum Capacity enter 10.
For Minimum Capacity enter 2.
Then click Save.
Screenshot for Step

The message "Settings updated successfully" is displayed.
16.In the Change Center click the Activate Changes button.
Screenshot for Step

The message "All changes have been activated. No restarts are necessary." is displayed.
17.Under Domain Structure, expand Environment and then click Servers.
Screenshot for Step
18.In the Servers table, click the server dizzy1.
Screenshot for Step
19.In the Settings for dizzy1, under the Configuration tab and the General subtab, click the
link View JNDI Tree.
Screenshot for Step
20.The JNDI tree opens in a new browser window (or tab). Notice that dizzyworldDS appears  in the JNDI tree.
Note: Other entries in the JNDI tree of your server can be very different from what is shown here. It all depends upon what resources your server has.
Screenshot for Step

Testing the JDBC Data Source

To test the JDBC data source with a simple web application, perform the following steps:
1.Open a new Terminal window and navigate to the /server/bin directory under the WebLogic installation directory. In this tutorial, that is:  /u01/app/oracle/Middleware/wlserver_12.1/server/bin
Then run the setWLSEnv.sh script as follows:
$ source setWLSEnv.sh
This will set the PATH and the CLASSPATH so the WLST deployment script can be executed.
Screenshot for Step
2.Next navigate to the directory where the testds.war file resides. This file was part of the zip  file extracted earlier. In this tutorial, the zip file was extracted into the   /home/oracle/jdbcobe/testds directory.
Screenshot for Step
3.Before running the supplied WLST script, deploy_testds.py, you may need to edit it. The first line of the script uses the connect() command. The first argument of that command is the domain administrator's username, the second argument is that user's password, and the third argument  is the host and port of the administration server of the domain. Make sure the values for these  arguments are correct for your domain before running this script.
Make any changes required to the script, and save the file.
Screenshot for Step
4.Run the deploy_testds.py script to deploy the web application in the testds.war file and target it to the dizzy1 server. Do this by entering the following command:
$ java weblogic.WLST deploy_testds.py
Screenshot for Step

You should see a message that the deploy operation has succeeded. Note: You can ignore the warning about Context.close(). That warning is always displayed when running a WLST script and is not an issue.

5.To verify the deployment was successful, go the the administration console, and under Domain Structure, clickDeployments. Then in the Deployments table, find TestDS with the "Active" State.
Screenshot for Step
6.To use the deployed application, in another web browser, enter the host and port for the dizzy1 managed server, followed by /testds. In this tutorial, the URL entered is:  http://host01.example.com:8003/testds
When the application comes up, enter the following:
Data Source Name:  dizzyworldDS
Table Name:EMPLOYEE
Username:weblogic (use your domain administrator's username)
Password:Welcome1. (use your domain administrator's password)
Then click the Test Data Source button.
Screenshot for Step
7.The rows in the EMPLOYEE table are displayed below the fields.
Screenshot for Step

The application uses the Data Source Name entered (dizzyworldDS) as the JNDI name to  look up the data source from dizzy1, retrieves a database connection from that data source, and executes the SQL to select all the rows in the table entered in the Table Name field (EMPLOYEE). If you want to test the data source again, try a different table in the Table Name field. The other two tables areWLS_CATALOG_ITEMS and WLS_CLIENT_INFO.