DML Sample Application

Table Of Contents 

• Overview of the Sample Application
• Required Software
• Application Set-up and Configuration
• Running the Application 
• From Oracle9i JDeveloper 
• From JDK for Windows
• From JDK for Linux
• Description of Sample Files

This sample application shows how to perform SQL INSERT, SELECT, UPDATE, and DELETE operations using JDBC. It uses the JDBC-Thin driver. This sample application simulates an Airlines Details Form in which the Airline code, Name and Partner details are tracked by the application. The user can add new Airlines details and at a later point of time update this record with the changed details. All the Airlines details are saved in the database for the user to browse/update/delete.

Working of the Sample

The sample application uses a database table OTN_AIRLINES. When the application is invoked, the table and few records are created if they do not exist. The status of the connection is shown in the status bar. Errors if any, are shown in the status bar.

All the created/existing records are displayed in the JTable when 'Select' button is pressed. Records can also be selected by giving values for code, name or partner. The records may be inserted, modified and deleted using the form displayed. The underlying code will use SQL queries to perform all the SELECT, INSERT, UPDATE and DELETE operations of the records in the database table.

Here is the code usage for selecting, inserting, updating an otn_airlines record in the database. You can find more details of the code in DMLSample.java file under src/oracle/otnsamples/jdbc/dml folder. Look into Description of Sample Files section for folder and file details.

Select method:

private void selectRecords(String code,String name, String partner) { // The SQL query for selecting the rows from the otn_airlines table String query = "SELECT * FROM otn_airlines " + "WHERE code LIKE ? AND name LIKE ? AND partner LIKE ?"; try { // Create a PreparedStatement based on the query PreparedStatement pst = connection.prepareStatement( query ); // If no query condition has been entered, change the bind values // to select all records if( code.equals("") ) code = "%"; if( name.equals("") ) name = "%"; if( partner.equals("") ) partner = "%"; // Bind the PreparedStatement with corresponding values pst.setString(1, code); pst.setString(2, name); pst.setString(3, partner);// Execute the PreparedStatement ResultSet resultSet = pst.executeQuery(); ................ ................

Insert method

private void insertRecord(String code,String name,String partner) { try { // Prepare a SQL statement to insert a new record into // the otn_airlines table PreparedStatement pst = connection.prepareStatement( "INSERT INTO otn_airlines VALUES (?,?,?)"); // Bind the column values into the PreparedStatement pst.setString(1, code); pst.setString(2, name); pst.setString(3, partner); // Execute the PreparedStatement pst.execute(); // Close the PreparedStatement object pst.close(); ................ ................

Update method:

private void updateRecord(String code, String name, String partner) { try { //Prepare a SQL statement to update the selected record PreparedStatement pst = connection.prepareStatement( "UPDATE otn_airlines SET name = ?, partner = ? WHERE code = ?"); // Bind the values for the update statement pst.setString(1, name); // Sets the value of name pst.setString(2, partner); // Sets value of partner pst.setString(3, code); // Sets the value of code // Execute the PreparedStatement and get the number of rows updated int numb = pst.executeUpdate(); ................ ................

• Oracle9i JDeveloper ( Note: Oracle9i JDeveloper is Oracle's Visual Java Development Tool and can be downloaded from here)
  or JDK1.2.x or above This can be downloaded from here.
• Oracle9i Database or higher running SQL*Net TCP/IP listener. This can be downloaded from here.
• Oracle9i JDBC Driver. This can be downloaded from here.

• Unjar the provided DMLSample.jar using the following command 

> jar xvf DMLSample.jar

Note: You will find jar.exe in JDK_HOME\bin. Ensure JDK_HOME\bin is present in your system path. 
(JDK_HOME is the root directory of the JDKx.x installation). This creates a folder 'DMLSample' with all the source files

• Edit DMLSample/Connection.properties file in your favorite editor. Change the HostName, Port, SID, UserName and 
Password to connect to your own database.

This sample application can be run in 3 different ways listed below.

• From Oracle9i JDeveloper
• From JDK for Windows
• From JDK for Linux

From Oracle9i JDeveloper

• Open Oracle9i JDeveloper and use File/Open option to select the DMLSample.jws from the DMLSample directory.
• Next, select Project/Make DMLSample.jpr from main menu.
• Now, select Run/Run DMLSample.jpr from main menu to run the application.

From JDK for Windows

This section will describe steps to run the application from console using JDK on Windows. The sample can be run either manually or using a script file .

Run application using batch File: run.bat provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.bat from the command prompt, from DMLSample directory. Environmental variables JDBC_HOME and JAVA_HOME have to be set before running run.bat file.
Example:

D:\DMLSample> set JDBC_HOME=d:\oracle9i\jdbc\lib
D:\DMLSample> set JAVA_HOME=d:\jdk1.3.1
D:\DMLSample> run  

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip
• DMLSample directory where Connection.properties exists and current directory are also added to the CLASSPATH
  Example: 
  D:\DMLSample>set CLASSPATH=D:\oracle9i\jdbc\lib\classes12.zip;D:\DMLSample;.;%CLASSPATH%
• Make sure that Java is in the PATH
  Example: D:\DMLSample>set PATH=.;d:\jdk1.3.1\bin;%PATH%
  
• From the directory DMLSample\src\oracle\otnsamples\jdbc\dml, compile all the java files using javac:
  Example: 
  D:\DMLSample\src\oracle\otnsamples\jdbc\dml>javac -d . *.java
• Run the class file using java from the same DMLSample\src\oracle\otnsamples\jdbc\dml directory.
  Example:
  D:\DMLSample\src\oracle\otnsamples\jdbc\dml>java oracle.otnsamples.jdbc.dml.DMLSample
  

From JDK for Red Hat Linux Advanced Server release 2.1

This section will describe steps to run the application from console using JDK on Linux. The sample can be run either manually or using a script file .

Run application using batch File: run.sh provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.sh from the command prompt, from DMLSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set, else the user will be prompted to enter values.

• Go to DMLSample directory and from the $ prompt use the command below to give execute permission to the file.
  $chmod 777 run.sh
• Now run the file:
  $sh run.sh
  

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip
• DMLSample directory where Connection.properties exists and current directory are also added to the CLASSPATH.
  Example:
  $export CLASSPATH=/home1/idcotn/download/jdbc/lib/classes12.zip:
  /home1/idcotn/download/basicjdbc/DMLSample:.:$CLASSPATH
• Make sure that Java is in the PATH
  Example: $export PATH=/usr/java/jdk1.3.1_02/bin:$PATH
  
• From the directory DMLSample/src/oracle/otnsamples/jdbc/dml, compile all the java files using javac:
  Example:
  $javac -d . *.java
• Run the class file using java from the same DMLSample/src/oracle/otnsamples/jdbc/dml directory.
  Example:
  $java oracle.otnsamples.jdbc.dml.DMLSample