Oracle JDeveloper is an integrated development environment (IDE) for Java. JDeveloper also provides support for several other features, including the Application Development Framework (ADF), JDBC database connectivity, and XML processing. Oracle Database is the most commonly used relational database management system (RDBMS). In two tutorials, we shall discuss using JDeveloper with Oracle Database 11g. This tutorial has the following sections:
- Setting the Environment
- Configuring Connection Parameters
- Creating a Connection
- Setting AutoCommit
- Creating a Table
- Adding Table Data
- Conclusion
Setting the Environment
Download and install the following software:
Figure 9: Selecting Tools>Preferences
In the Preferences dialog, select Database>Advanced, as shown in Figure 10, and select the Autocommit checkbox if not already selected.
Figure 10: Autocommit setting
Creating a Table
Next, create an example database table. Two options are available to create a new table:
- Run a SQL Script in a SQL Worksheet
- Use the New Table wizard
We shall use the New Table wizard. The Tables node for the connection lists the tables in the database (see Figure 11).
Figure 11: Tables
To create a new table, right-click the Tables node and select New Table, as shown in Figure 12.
Figure 12: Tables>New Table
The Create Table wizard gets launched, as shown in Figure 13.
Figure 13: Create Table wizard
Next, create a new table called WLSLOG with columns TIME_STAMP,CATEGORY,TYPE,SERVERNAME,CODE and MSG. Modify the default column COLUMN1 to TIME_STAMP, as shown in Figure 14, and set the column as a primary key column. Select Data Type as VARCHAR2 and set Size (255). Select the Not Null checkbox to make the column as not nullable. Click Add Column, as shown in Figure 14, to add another column.
Figure 14: Adding a Column
Add one column at a time. All the columns added are shown in Figure 15. Click OK.
Figure 15: All columns added
The DDL tab displays the DDL used to create the new table (see Figure 16). OK may be selected from either the Table tab or the DDL tab.
Figure 16: DDL
The new table WLSLOG gets created. The new table gets listed in the Tables node. To list only the new table, right-click Tables and select Filter, as shown in Figure 17.
Figure 17: Selecting Tables>Filter
In Filter Tables, specify Filter as WLSLOG% and click OK, as shown in Figure 18.
Figure 18: Filter Tables
The WLSLOG table gets listed, as shown in Figure 19.
Figure 19: WLSLOG table
Double-click the WLSLOG table to display its Structure, as shown in Figure 20.
Figure 20: WLSLOG table Structure
Adding Table Data
Next, add the data listed to the WLSLOG table.
Apr-8-2014-7:06:16-PM-PDT Notice WebLogicServer AdminServer BEA-000365 Server state changed to STANDBY Apr-8-2014-7:06:17-PM-PDT Notice WebLogicServer AdminServer BEA-000365 Server state changed to STARTING Apr-8-2014-7:06:18-PM-PDT Notice WebLogicServer AdminServer BEA-000365 Server state changed to ADMIN Apr-8-2014-7:06:19-PM-PDT Notice WebLogicServer AdminServer BEA-000365 Server state changed to RESUMING Apr-8-2014-7:06:20-PM-PDT Notice WebLogicServer AdminServer BEA-000331 Started WebLogic AdminServer Apr-8-2014-7:06:21-PM-PDT Notice WebLogicServer AdminServer BEA-000365 Server state changed to RUNNING Apr-8-2014-7:06:22-PM-PDT Notice WebLogicServer AdminServer BEA-000360 Server started in RUNNING mode
To add data, we shall run a SQL script in a SQL Worksheet. Start a new SQL Worksheet by selecting Tools>Database>SQL Worksheet (see Figure 21).
Figure 21: Tools>Database>SQL Worksheet
In the Select Connection dialog, select the OracleDBConnection, as shown in Figure 22, and click OK.
Figure 22: Selecting Connection
Copy the following SQL script to the SQL Worksheet.
INSERT INTO wlslog(timestamp,category,type,servername,code,msg)
VALUES('Apr-8-2014-7:06:16-PM-PDT','Notice','WebLogicServer',
'AdminServer','BEA-000365','Server state changed to STANDBY');
INSERT INTO wlslog(timestamp,category,type,servername,code,msg)
VALUES('Apr-8-2014-7:06:17-PM-PDT','Notice','WebLogicServer',
'AdminServer','BEA-000365','Server state changed to STARTING');
INSERT INTO wlslog(timestamp,category,type,servername,code,msg)
VALUES('Apr-8-2014-7:06:18-PM-PDT','Notice','WebLogicServer',
'AdminServer','BEA-000365','Server state changed to ADMIN');
INSERT INTO wlslog(timestamp,category,type,servername,code,msg)
VALUES('Apr-8-2014-7:06:19-PM-PDT','Notice','WebLogicServer',
'AdminServer','BEA-000365','Server state changed to RESUMING');
INSERT INTO wlslog(timestamp,category,type,servername,code,msg)
VALUES('Apr-8-2014-7:06:20-PM-PDT','Notice','WebLogicServer',
'AdminServer','BEA-000361','Started WebLogic AdminServer');
INSERT INTO wlslog(timestamp,category,type,servername,code,msg)
VALUES('Apr-8-2014-7:06:21-PM-PDT','Notice','WebLogicServer',
'AdminServer','BEA-000365','Server state changed to RUNNING');
INSERT INTO wlslog(timestamp,category,type,servername,code,msg)
VALUES('Apr-8-2014-7:06:22-PM-PDT','Notice','WebLogicServer',
'AdminServer','BEA-000360','Server started in RUNNING mode');
The SQL statements may be run one at a time or all together. To run one statement at a time, position the cursor before the statement and click Run Statement, as shown in Figure 23.
Figure 23: Run Statement
We shall run the SQL script instead of running one statement at a time. Click Run Script, as shown in Figure 24.
Figure 24: Run Script
Data gets added to table as indicated by the Script Output, which lists a “1 row inserted” message for each row added, as shown in Figure 25.
Figure 25: Script Output indicates that data has been added
If auto commit were not enabled, we would need to commit the transaction with Commit (see Figure 26). But, because auto commit is enabled, Commit is not to be clicked.
Figure 26: Click Commit if auto commit is not enabled
To rollback a transaction, click Rollback, as shown in Figure 27.
Figure 27: Rollback
The clear the script output, click Clear, as shown in Figure 28.
Figure 28: Script Output>Clear
Conclusion
In this first of two tutorials, we introduced using Oracle JDeveloper 12c with Oracle Database by creating a connection in JDeveloper, creating a database table, and adding table data. In the 2nd tutorial, we shall discuss running a SQL query, using query result set features, dropping a table, and deleting a connection.