In JDeveloper, select File | New. The New Gallery displays.

In the Categories pane accept the default General, and from the Items pane, the default Application Workspace.

Click OK.

In the Create Application Workspace dialog, type HRAdmin. Ensure that the Application Template dropdown is set to No Template [All Technologies].

Note that the application template automatically provides you with a single project configured to allow easy access to all JDeveloper technologies.

The new workspace and project are displayed in the Applications Navigator. Select the workspace name, and choose File | Save All.

Note that the font for the workspace and project names is no longer italicized. Italics indicate that a project or workspace has not yet been saved.

Select the project name in the Navigator, and go to Tools | Project Properties.

The Project Properties dialog opens.

Expand the Profiles node if it is not already expanded. Under the Development node, find and select Libraries.

A list of the available libraries for the J2SE version is displayed.

Scroll through the list to find Oracle JDBC. Select it, and use the single right-pointing arrow to shuttle it into the Selected Libraries pane.

Click OK.

Select your project in the Navigator. Right-click and select New... from the short-cut menu.

In the New Gallery, retain General as the default category, and select Java Class from the Items pane.

Click OK.

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.

In the Code Editor, on a new line under the generated package statement, start to type the following statement:

import java.sql.*;

Note that the Code Insight feature of JDeveloper prompts you as you type by offering you a list of possible options to complete the statement.

Double-click on an option in the list to add it to your statement.

Type the other two import statements as follows, using the Code Insight feature if you wish.

import java.sql.SQLException.*;
import oracle.jdbc.driver.*;

Double-click the project name in the Navigator, to invoke the Project Properties dialog, and select Runner from the options displayed under Profiles | Development.

Type the values for the new department in the Program Arguments field, as follows: 280 Facilities_Management 1700 Bissot

Click OK.

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.

Declare the variables and store the arguments, by creating a new line after the first brace "{" and typing the following statements:

String deptId = args[0];
String deptName = args[1];
String deptLocation = args[2];
String deptManager = args[3];

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();

Add a trace message to display the parameters being used in the run:

System.out.println("using: "
+ " " + deptId
+ " " + deptName
+ " " + deptLocation
+ " " + deptManager
);

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.

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;

Create a method called listEmp to hold the employee query code.

public int listEmp(String lastName) throws Exception
{
}

In the listEmp method, create a statement object on a new line in the Code Editor, after the opening brace "{":

stmt = conn.createStatement ();

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.

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.

int count = 0;
while (rset.next ())
{
count++;
System.out.println(rset.getString(1) + " " + rset.getInt(2) + " " + rset.getString(3));
}

Note that the rset.getXXX methods need the required column number of the result row as a parameter.

Return the count of rows retrieved to the calling code, as follows:

return count;

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;
}

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.

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.

Create a method called insertDept to hold the insert code.

public int insertDept
(String deptId, String deptName, String deptLocation)
{
}

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:

try
{
pstmt = conn.prepareStatement(
"INSERT INTO DEPARTMENTS VALUES(?,?,NULL,?)");
pstmt.setInt(1, Integer.parseInt(deptId));
pstmt.setString(2, deptName);
pstmt.setInt(3, Integer.parseInt(deptLocation));
pstmt.executeUpdate();
conn.commit();
return 0; // return OK
}

catch (SQLException e)
{
System.out.println("Error on Insert" + e);
return 1; // return error
}

Note that the pstmt.setXXX methods need the substitution value index as a parameter.

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");

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).

public void listDept(String deptId) throws Exception
{
stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery ("SELECT * FROM DEPARTMENTS where DEPARTMENT_ID = "
+ deptId + " ");
while (rset.next ())
{
System.out.println( rset.getInt(1) + " " + rset.getString(2)
+ " " + rset.getInt(4));
}
}

Invoke the method from main.

hRUpdates.listDept(deptId);

Create a method called updateEmp to hold the update code.

public void updateEmp(String deptId, String lastName) throws Exception
{
}

Create the update code using the prepared statement object as you did for the department insert.

pstmt = conn.prepareStatement(
"UPDATE EMPLOYEES SET DEPARTMENT_ID= ? , JOB_ID= ? WHERE LAST_NAME= ?");
pstmt.setInt(1, Integer.parseInt(deptId));
pstmt.setString(2, "AC_MGR");
pstmt.setString(3, lastName);
pstmt.executeUpdate();
conn.commit();

Note that this code should ideally contain some try....catch error handling as coded for the insert step

Iinvoke the updateEmp method from main:.

hRUpdates.updateEmp(deptId, deptManager);

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");

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();
}

Invoke the terminate method from main:

hRUpdates.terminate();

Run the program to see the results of both the department insert and the employee update in the Log window.

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.