Oracle by Example branding Configure a JDBC Data Source in Oracle WebLogic Server

section 0Before You Begin

This 15-minute tutorial shows you how to configure a JDBC data source using the Oracle WebLogic Server Administration Console.

Background

This tutorial is part of the Oracle WebLogic Server 12c series, and assumes that you have completed these tutorials, in this order:

In WebLogic Server, you configure database connectivity by adding data sources to your WebLogic domain. Java Database Connectivity (JDBC) data sources provide database access and database connection management. Each data source contains a pool of database connections that are created when the data source is created and at server startup. To access a database, an application can request a connection from the data source and close the connection after using it. The data source places this connection back in the connection pool for reuse by other applications.

What Do You Need?

  • An installation of Oracle WebLogic Server 12c. See http://www.oracle.com/technetwork/middleware/weblogic/downloads/index.html.
  • A running instance of the Oracle Database 11g or later. In this tutorial, Oracle Database Express Edition 11.2 is used. Also, ensure that you have the required credentials to start the database.
  • A properly configured WebLogic Server domain containing an Administration Server with following settings:
    • Domain Directory: /u01/domains/ExampleDomain
    • Administration Server listen address and port number: localhost:7001
    • Domain administrator credentials: The user name and password you specified when you created the domain.
  • An example application, which is available for downloaded here. This application archive 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.sh, a WebLogic Scripting Tool (WLST) script used to deploy the web application.

Conventions

This tutorial uses the following conventions:

    Entity  Value
    JAVA_HOME Environment Variable   /u01/app/jdk
    ORACLE_HOME Environment Variable   /u01/app/oracle/product/11.2.0/xe
    ORACLE_SID Environment Variable   XE
    PATH Environment Variable   $ORACLE_HOME/bin:$PATH
    WL_HOME Environment Variable   /u01/app/fmw/wlserver/server
    APP_HOME Environment Variable   /tmp/downloads/JDBC

section 1Configure the Oracle Database

To configure the database required by the JDBC client application:

  1. Open a terminal window and navigate to the APP_HOME directory. 
    $ cd ${APP_HOME}
  2. Ensure that the database environment variables such as ORACLE_HOME, ORACLE_SID, and PATH are set by printing them out using the echo command: 
    $ echo ${ORACLE_HOME}
/u01/app/oracle/product/11.2.0/xe
$ echo ${ORACLE_SID}
XE
$ echo ${PATH}
${ORACLE_HOME}/bin:$PATH
    Note: The values of your environment variables may be different. If there are no values printed, then set the variables to the proper values: 
    $ export ORACLE_HOME=<valid_value>
$ export ORACLE_SID=<valid_value>
$ export PATH=<valid_value>
  3. Invoke SQL Plus as the sysdba user: 
    $ ${ORACLE_HOME}/bin/sqlplus/ as sysdba
    Note: If you are using the Oracle Database 12c, then invoke SQL Plus as /nolog user.
  4. Connect to the database: 
    SQL> connect username/password as sysdba
    Where, username and password are your database credentials.
  5. Run the SQL script : 
    SQL> @testds_oracle.sql
    This script creates a user called DBTESTER with the password that you have provided. It grants the user the rights to create sessions, tables, and so on. It then connects to the database as this user and creates three tables: EMPLOYEE, WLS_CATALOG_ITEMS, and WLS_CLIENT_INFO. Finally, it inserts rows into those tables and exits.

section 2Start WebLogic Server

To start the Administration Server:

  1. Open a terminal window and navigate to the domain directory:
    2. $ cd /u01/domains/ExampleDomain/
  2. In the domain directory, enter the command:
    3. $./startWebLogic.sh
  3. When prompted, enter the credentials of the domain administrator.
  4. Examine the terminal output and wait for the Administration Server to reach the RUNNING state.
    5. <SEP 16, 2018 9:57:34,722 AM EDT> <Notice> <WebLogicServer> <BEA-000360> <The server started in RUNNING mode.>
<SEP 16, 2018 9:57:34,817 AM EDT> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to RUNNING.>

section 3Configure a JDBC Data Source

To configure a JDBC data source using the WebLogic Server Administration Console:

  1. After the Administration Server is up and running, access the WebLogic Server Administration Console. Open a web browser and enter the Console's URL. 
     http://localhost:7001/console
    Optionally, specify the host name and port of your domain's Administration Server in place of localhost:7001 .
  2. On the Welcome screen, enter the domain administrator credentials, and then click Login.
  3. In the Change Center, click Lock & Edit.
    4. Note: To create a data source, or change anything in the domain configuration, you must first lock the domain configuration (to prevent other accounts from making changes during your edit session) and enable an edit session.
  4. In the left panel, under Domain Structure, expand Services and select Data Sources.
  5. On the Summary of JDBC Data Sources page, click New and select Generic Data Source.
    Create a New Generic Data Source.
    Description of the illustration select_ds.png
  6. On the JDBC Data Source Properties page:
    • Enter the Name of the data source as myNewDS.
    • Enter the JNDI Name of the data source as myNewDS.
      • Note: There is no requirement for the data source and JNDI names to be the same.
    • Choose Oracle as the Database Type and click Next.
  7. For Database Driver, select *Oracle's Driver (Thin) for Instance connections; Versions:Any, and then click Next.
  8. On the Transaction Options page, retain all default values and click Next.
  9. On the Connection Properties page:
      Field  Value
      Database Name   XE (your database name may be different)
      Host Name   localhost (use the host name where you have configured your Administration Server)
      Port   1521 (enter your database port)
      Database User Name   DBTESTER (this is the user defined in the SQL script)
      Password   Database user's password (the password defined in the SQL script for the above user)
      Confirm Password   Database user's password
  10. Click Next.
  11. On the Test Database Connection page, review the connection parameters and click Test Configuration.
  12. If the message Connection test succeeded displays, click Next.
    Upon successful connection to the database click Next.
    Description of the illustration success.png
    13. If the test is unsuccessful, click Back and review the entries made for the data source, correct any errors and then retry the test. If there are no errors in the entries and the test still fails, make sure your database is running.
  13. On the Select Targets page, select AdminServer as the data source target.
  14. Click Finish to save the JDBC data source configuration and deploy the data source to the AdminServer (target).

  15. In the Change Center, click Activate Changes.
    • The Console displays the message: All changes have been activated. No restarts are necessary
    • In the Summary of JDBC Data Sources page, the new data source, myNewDS, is now listed in the Sources table.
  16. To modify the configuration of the new data source, select the data source name, myNewDS.
  17. On the Settings for myNewDS page, select Configuration and then select Connection Pool, and scroll down to find the capacity fields and change the existing values to:
    Changes in the capacity field.
    Description of the illustration capacity.png
  18. Click Save.
  19. In the Domain Structure tree, expand Environment and select Servers.
  20. In the Servers table, select AdminServer(admin).
  21. To validate the configuration and target of the data source, view myNewDS in the JNDI Tree. To view the JNDI Tree, on the Settings for AdminServer page:
    • Select Configuration and then select General.
    •  Select View JNDI Tree.
    View JNDI Tree
    Description of the illustration viewjndi.png
    The JNDI tree opens in a new window and myNewDS appears in the JNDI tree. Other entries in the JNDI tree of your server may vary depending on the available resources in your server.
    View the JNDI Tree Structure in a new browser.
    Description of the illustration jnditree.png

section 4Test the JDBC Data Source

To test the JDBC data source with a simple web application:

  1. Deploy the application testds located in the testds.war file.
    • Open a new terminal window and navigate to the APP_HOME directory. 
       $ cd ${APP_HOME}
    • Run the deploy.sh script to deploy the web application in the testds.war file and target it to the Administration Server. This script is part of the example application (extracted earlier) into APP_HOME directory. 
       $./deploy.sh -a testds -p testds.war -U username
      • In the command, -a specifies the name of the application, -p specifies the path to testds.war file and -U specifies the Weblogic Server user name.
    • After you run the command, enter the password for your WebLogic Server Administrator user name when prompted.
    • After the application is successfully deployed, you will get a message similar to:
      • >>>Deploying application testds.war to target Adminserver. Please wait.

Deploying application from /tmp/downloads/JDBC/testds.war to targets Adminserver (upload=false) ...
      
.Completed the deployment of Application with status completed
Current Status of your Deployment:
Deployment command type: deploy
Deployment State : completed
Deployment Message : no message
>>>Application testds deployed.

Exiting WebLogic Scripting Tool.
  2. To verify the success of deployment, return to the Administration Console:
  3. To use the deployed application, in another web browser, enter the host and port for the Administration Server followed by /testds.
    4. http://localhost:7001/testds
    Test Data Source
    Description of the illustration testds.png
  4. When the application opens, enter:
    Field  Value
    Data Source Name: myNewDS
    Table Name: EMPLOYEE
    Username: Use your domain administrator's user name
    Password: Use your domain administrator's password
  5. Click Test Data Source. 
    6. The rows in the EMPLOYEE table are displayed below the fields. The application uses the Data Source Name entered (myNewDS) as the JNDI name to look up the data source from AdminServer. It then 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 are WLS_CATALOG_ITEMS and WLS_CLIENT_INFO.
    Employees Details
    Description of the illustration employees.png

more informationNext Steps

The WebLogic Server 12c collection contains a number of additional tutorials, covering a variety of topics. See the WebLogic Server 12c collection here for additional topics and content.