This lesson takes approximately 60 minutes to complete.
Viewing Screenshots
Move your mouse over this icon to show all screenshots.
You can also move your mouse over each individual icon to see only the screenshot
associated with it.
Overview
This tutorial shows you how to access and manipulate
the data in an Oracle database, from a Java program, using JDeveloper. You run
some simple SQL queries, you insert a row into a table, and you update a related
table. Finally you requery the database to see the results of your work.
You are a Java developer, working
on HR applications. A new department, Facilities Management, in location 1700,
has just been created, headed up by Laura Bissot. You need to insert the Facilities
Management information into the Departments table and update Laura Bissot's
employee record with her new position.
Before you can start to use JDBC in JDeveloper, you
must perform certain setup tasks. You first need a workspace and project in
which to store your work, and then you add the JDBC libraries to the project.
You also need to import the packages that enable you to use the JDBC classes.
Follow these steps to set up your environment:
Select your project
in the Navigator. Right-click and select New... from
the short-cut menu.
2.
In the New Gallery, retain General
as the default category, and select Java Class
from the Items pane.
Click OK.
3 .
In the Create Java Class dialog, type the
name HRUpdates. Note the Optional Attributes
box: leave the Public and Generate Default
Constructor checkboxes checked, and in addition, check the Generate
Main Method checkbox.
Click OK to display the code for the new class in the
Code Editor.
Import the packages
you need to be able to use the Java classes
You need to import the following packages to be able to use the JDBC classes:
The application allows users to input
values for a new department i.e the department Id, its name, and its location
code, and to specify the name of the employee who is going to be the acting
manager of the new department.
You need to list the values to be used at run time, in the Project Properties
window in JDeveloper. You should also declare variables for these four arguments
in your main method.
Double-click the project
name in the Navigator, to invoke the Project Properties dialog, and select
Runner from the options displayed under Profiles
| Development.
2.
Type the values for the new department in
the Program Arguments field, as follows: 280
Facilities_Management 1700 Bissot
Click OK.
3.
In the Code Editor, add the following code
to your public static void main
statement:
throws Exception
This removes the requirement to catch any exceptions thrown in this method.
In general you should catch the exceptions thrown and handle them appropriately.
4.
Declare the variables and store the arguments,
by creating a new line after the first brace "{"
and typing the following statements:
Note that an instance of the HRUpdates class
is created for you as part of the main
method. This is where all the methods of the class are to be defined.
It appears as follows:
HRUpdates hRUpdates =
new HRUpdates();
6.
Add a trace message to display the parameters
being used in the run:
To run your program, right-click in the Code
Editor and choose Run from the context menu. The parameters
specified in the Project Properties dialog are displayed in the Log window.
In order to connect to the database, JDBC drivers must
first register themselves with the driver manager. There are various ways of
doing this, but this example uses the register.Driver()
method of the java.sql.DriverManager
class. You then call the getConnection()
method to create the connection. When you create a Connection
object, it is in autocommit mode, meaning that each SQL statement is committed
after it is executed. However you can disable autocommit, and group the statements
into transactions, which must then be explicitly terminated by calling commit()
or rollback(). This example shows
you how to disable autocommit.
1.
Declare a connection instance in your HRUpdates
class definition. To do this, create a new line after the first curly
brace "{" below your class declaration, and type as follows:
Connection conn;
2.
Create a method called initialize
to hold the connection code.
public void initialize()
throws Exception
{
}
3 .
Register the driver by creating a new line
after the first brace "{" and typing the following statement,
using Code Insight if you wish:
DriverManager.registerDriver(
new oracle.jdbc.driver.OracleDriver());
4 .
To connect to the database, add the connect
statement as follows:
Replace hostname, JDBC port, and dbname with appropriate values for your
environment, and use "hr" and "hr" as the username
and password to connect to the HR schema. If you do not know the values
for the hostname, JDBC port and dbname, check with your database administrator.
5 .
To disable autocommit for this connection,
add the following statement:
conn.setAutoCommit (false);
6 .
Add the following line of code to check that
you have managed to successfully connect to the database:
System.out.println("I
am connected");
7 .
In the code for the main
method, invoke the initialize
method, as follows:
hRUpdates.initialize();
8 .
Run your program. The message "I am connected"
displays in the Log window at the bottom of the screen.
Once you have registered the driver and connected to
the database, you are ready to run queries against the data. Before adding the
new department to the Departments table, you need to confirm that the name of
the employee assigned to manage it, is valid.
In the example, this is done by attempting to list any employee with the same
last name as that input as a parameter.
You create a JDBC statement object, and use the createStatement()
method as the context for executing your SQL statement. For queries you use
the executeQuery() method of
the Statement interface. This method takes a SQL statement as input and returns
a JDBC ResultSet object. The next()
method is then used to iterate through the rows of the result set. The following
steps guide you through the process of executing a query and outputting the
results.
1.
Declare a statement instance in your HRUpdates
class definition. To do this, create a new line below your connection
declaration, and type in the following code:
Statement stmt;
2.
Create a method called listEmp
to hold the employee query code.
public int listEmp(String
lastName) throws Exception
{
}
3.
In the listEmp
method, create a statement object on a new line in the Code Editor, after
the opening brace "{":
stmt = conn.createStatement
();
4.
Use the executeQuery()
method to frame your SQL statement, and return a result set to check the
uniqueness of the name that was passed as a program parameter. To do this,
add the following code to the method:
ResultSet rset = stmt.executeQuery
("SELECT LAST_NAME, EMPLOYEE_ID, JOB_ID FROM EMPLOYEES where LAST_NAME
= '" + lastName + "'");
Note the single quote characters enclosing the lastName variable.
5.
List the values for the employee's name, department
Id and job title of any rows retrieved by the query and count each row.
To loop through the rows of a result set, use the next()
method. Zeroize a count field beforehand.
Note that the rset.getXXX
methods need the required column number of the result row as a parameter.
6 .
Return the count of rows retrieved to the
calling code, as follows:
return
count;
7 .
In the code for the main
method, invoke the listEmp
method, and check that only one employee with that name, exists.
int empCount = hRUpdates.listEmp(deptManager);
if (empCount !=1 )
{
System.out.println ("ERROR: Employee " + deptManager +
" not valid");
return; }
8 .
Run the program. The Log window displays the
name, employee id and job title that matches the name input as a program
parameter. As there is only one row, the name is valid.
Having checked that the nominated manager is a valid
employee, you are ready to insert the new department into the table. There are
four columns: Department_Id, Department_Name, Manager_Id (optional) and Location_Id.
Because you disabled autocommit in a previous step, you must explicitly commit
the Insert to the database. In addition, you add some exception-handling code
to check for SQL errors. If it finds any, it reports the error and terminates
the program. This checks that a department cannot be inserted twice.
Finally you confirm that the insert has taken place, by running a query against
the Departments table.
1.
Declare a prepared statement instance in your
HRUpdates class definition. To do this, create a new line below your statement
declaration, as follows:
PreparedStatement pstmt;
Note that because the insert command requires several
value substitutions, it is preferable to use the PreparedStatement
object instead of the string concatenation technique used in the earlier
query.
2.
Create a method called insertDept
to hold the insert code.
public int insertDept
(String deptId, String deptName, String deptLocation)
{
}
3 .
Now add the insert code with some exception
handling.
Enclose the insert and commit statements within a try
block.Then add a
catch block,that
reports any errors, and terminates the program. The code for this is as
follows:
Note that the pstmt.setXXX
methods need the substitution value index as a parameter.
4.
In the code for the main
method, invoke the insertDept
method, and check for error return.
int result = hRUpdates.insertDept(deptId,
deptName, deptLocation);
if (result != 0)
{
System.out.println("ERROR: Department " + deptId + " not
valid");
return;
}
System.out.println("-Department added");
5 .
Check that the inserted values are correct.
Create a method called listDept
to query the inserted row. (This follows the same approach as the listEmployee
method above).
You now have a new department as specified by run parameters.
The designated manager's record in the Employees table now needs to be updated
to reflect her new department, and her new position, which initially defaults
to AC_MGR (Acting Manager).
1.
Create a method called updateEmp
to hold the update code.
public void updateEmp(String
deptId, String lastName) throws Exception
{
}
2.
Create the update code using the prepared statement object
as you did for the department insert.
Note that this code should ideally contain some try....catch
error handling as coded for the insert step
3.
Iinvoke the updateEmp
method from main:.
hRUpdates.updateEmp(deptId,
deptManager);
4.
Query the Employees table for the designated manager to
see the result of the update.
In main invoke the listEmp
method created earlier, adding a message to show that this is after the
insert and update:
hRUpdates.listEmp(deptManager);
System.out.println("Emp after update");
5.
Close the prepared statement, statement
and connection objects. Create a method called terminate
to hold the closing code.
public void terminate()
throws Exception
{
pstmt.close();
stmt.close();
conn.close();
}
6.
Invoke the terminate
method from main:
hRUpdates.terminate();
7.
Run the program to see the results
of both the department insert and the employee update in the Log
window.
8.
Confirm that the insertDepartment error-handling
code is working by running the program again. It tries to insert the same
row into the Departments table, but since the row now exists, the program
terminates, outputting the 'Error on Insert' message, and the SQLException
unique constraint violation.
Summary
This tutorial showed you how you can use JDeveloper and JDBC
to access an Oracle database from a Java application. You ran queries against
tables, you inserted data into a table, and you updated a table.
Move your mouse over this icon to hide all screenshots
Bookmark
