- Overview of JDBC—Java Calling SQL and PL/SQL
- Fundamental Steps in JDBC
- INSERT, UPDATE, and DELETE Operations Using JDBC
- Oracle JDBC Extensions
- Calling Stored Procedures in JDBC Programs
- Handling Resultsets in JDBC
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
INSERT, UPDATE, and DELETE Operations Using JDBC
Prepared Statements
The DML operations of INSERT and UPDATE—that is, the write operations—are done by means of the prepareStatement() method of the Connection object created above. A call to this method takes variable bind parameters as input parameters and creates an object instance of the PreparedStatement class.
The following line of code illustrates this:
String sql = "INSERT INTO emp VALUES (?,?,?,?,?,?,?,?)";PreparedStatement dml_stmt = conn.prepareStatement(sql);The input parameters are bound to this object instance using the setXXX() methods on the PreparedStatement object. For each input bind parameter, a setXXX() method is called. Here XXX stands for Int, String, and so on. The following line of code illustrates this:
dml_stmt.setInt(1, val);
Here 1 denotes that the first bind parameter is being set and val denotes an integer variable holding a value.
Once a PreparedStatement object has been constructed, the next step is to execute the associated INSERT or UPDATE statement. This is done by using the executeUpdate() method of the PreparedStatement object. The following line of code illustrates this using the dml_stmt object created above:
dml_stmt.executeUpdate();
The differences between Statement object and PreparedStatement object are the following:
A Statement object cannot accept bind parameters, whereas a PreparedStatement object can.
A PreparedStatement precompiles the SQL and hence the precompiled SQL statement can be reused. In this way, it optimizes the database calls.
PreparedStatement Objects
It is recommended not to use the methods executeQuery(String) or executeUpdate(String) on PreparedStatement objects as there might be a chance of the Oracle driver throwing an exception at runtime.
A complete example is shown below. This example first checks whether a given empno exists in the EMP and if not inserts rows into the EMP table.
import java.sql.*;
public class InsertExample {
public static void main(String[] args)
throws SQLException {
int ret_code;
Connection conn = null;
try {
int i_empno[] = {1001, 1002, 7788};
String i_ename[] = {"JOHN","DAVID","ORATEST"};
String i_job[] = {"MANAGER","ANALYST","CLERK"};
int i_mgr[] = {7839, 1001, 1002};
String i_hiredate = "01-JAN-01";
float i_sal[] = {10000,6000, 4000};
float i_comm[] = {2000,1000,500};
int i_deptno = 10;
//Load and register Oracle driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//Establish a connection
conn = DriverManager.getConnection("jdbc:oracle:thin:@training:1521:
Oracle", "oratest", "oratest");
String sql1 = "SELECT empno FROM emp WHERE empno = ?" ;
String sql2 = "INSERT INTO emp VALUES (?,?,?,?,?,?,?,?)";
PreparedStatement pstmt1 = conn.prepareStatement(sql1);
PreparedStatement pstmt2 = conn.prepareStatement(sql2);
for (int idx=0;idx<3;idx++)
{
pstmt1.setInt(1, i_empno[idx]);
ResultSet rset = pstmt1.executeQuery();
if (rset.next()) {
System.out.println("The employee "
+i_empno[idx]+" already exists.");
rset.close();
}
else
{
pstmt2.setInt(1, i_empno[idx]);
pstmt2.setString(2, i_ename[idx]);
pstmt2.setString(3, i_job[idx]);
pstmt2.setInt(4, i_mgr[idx]);
pstmt2.setString(5, i_hiredate);
pstmt2.setFloat(6, i_sal[idx]);
pstmt2.setFloat(7, i_comm[idx]);
pstmt2.setInt(8, i_deptno);
pstmt2.executeUpdate(); }
} // End of for loop
pstmt1.close();
pstmt2.close();
conn.close();
} catch (SQLException e) {ret_code = e.getErrorCode();
System.err.println(ret_code + e.getMessage()); conn.close();}
}
}
UPDATE and DELETE operations are similar to the INSERT operation described above. However, if there are no bind parameters involved, a Statement object can be used instead of a PreparedStatement object with the values hard-coded directly in the DML statement.
Callable Statements
Callable statements are used for calling Oracle stored procedures from Java and are discussed in a separate section "Calling Stored Procedures in JDBC Programs," later in the chapter.
Using Transactions
A transaction is a collection of DML statements that are executed as if they are a single operation. A JDBC application that needs to execute multiple SQL statements targeted towards a specific function, can make use of JDBC's transaction services. Transactions might need to be grouped in situations where multiple updates are needed and the entire set of transactions is to be committed or the entire set undone in case of a single failure.
Transaction services basically include beginning the transaction, executing the SQL statements that make up the transaction, and either perform a commit on overall success of each SQL statement or rollback the transaction as a whole if one of the SQL statements fails.
A second issue with transactions occurs when changes to the database become visible to the rest of the application system. This is termed isolation level. For example, in a multi-user system, when do changes performed by one user become visible to the remaining users? Transactions can operate at various isolation levels. At the highest isolation level, the changes to the database become visible only when the transaction is committed.
Transaction management in JDBC is handled to some extent by the Connection object. Whenever a new Connection is opened, the transaction auto-commit mode is turned on. In auto-commit mode, every SQL statement is executed as a single transaction that is immediately committed to the database. To execute multiple SQL statements as part of a single transaction, the auto-commit is to be disabled. The next section explains more on this.
Committing
Committing of DML INSERT, UPDATE, or DELETE statements in JDBC programs is done automatically. The auto-commit is set to ON by default in JDBC and a COMMIT is issued after every SQL operation. However, if you choose to set the auto-commit mode off, you can do so by calling the setAutoCommit() of the Connection object as follows:
conn.setAutoCommit(false);
The above line of code should appear immediately after the connection has been established. This is shown below.
//Load and register Oracle driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//Establish a connection
conn = DriverManager.getConnection
("jdbc:oracle:thin:@training:1521:Oracle",
"oratest",
"oratest");
//Disable auto-commit mode
conn.setAutoCommit(false);
Once the auto-commit mode is turned off, an explicit COMMIT or ROLLBACK should be done to commit any unsaved database changes. COMMIT or ROLLBACK can be done by calling the commit() or rollback() methods of the Connection object as shown below.
conn.commit();
or
conn.rollback();
Tips
Explicit COMMIT or ROLLBACK is done for a transaction and not for individual DML statements.
Closing a connection before an explicit commit automatically COMMITs the transaction, even if auto-commit mode is turned off.
Executing a DDL statement automatically COMMITs the transaction even if auto-commit mode is turned off.
Disabling auto-commit improves performance in terms of time and processing effort as a COMMIT need not be issued for every SQL statement affecting the database.
The following gives an example program to illustrate transaction management:
import java.sql.*;
public class TransactionExample {
public static void main(String[] args)
throws SQLException {
int ret_code;
Connection conn = null;
try {
//Load and register Oracle driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//Establish a connection
conn = DriverManager.getConnection("jdbc:oracle:thin:@training:1521:
Oracle", "oratest", "oratest");
//Disable auto-commit mode
conn.setAutoCommit(false);
String sql1 = "SELECT empno FROM emp WHERE empno = ?" ;
String sql2 = "INSERT INTO emp VALUES (?,?,?,?,?,?,?,?)";
String sql3 = "UPDATE dept_audit SET cnt_emp = nvl(cnt_emp,0)
+ 1 WHERE deptno = 10";
PreparedStatement pstmt1 = conn.prepareStatement(sql1);
PreparedStatement pstmt2 = conn.prepareStatement(sql2);
PreparedStatement pstmt3 = conn.prepareStatement(sql3);
pstmt1.setInt(1, 9999);
ResultSet rset = pstmt1.executeQuery();
if (rset.next()) {
System.out.println("The employee with empno 9999 already exists.");
rset.close();
}
else
{
pstmt2.setInt(1, 9999);
pstmt2.setString(2, "CHARLIE");
pstmt2.setString(3, "ANALYST");
pstmt2.setInt(4, 7566);
pstmt2.setString(5, "01-JAN-01");
pstmt2.setFloat(6, 12000);
pstmt2.setFloat(7, (float)10.5);
pstmt2.setInt(8, 10);
pstmt2.executeUpdate(); }
pstmt3.executeUpdate();
pstmt1.close();
pstmt2.close();
pstmt3.close();
// Commit the effect of all both the INSERT and UPDATE
// statements together
conn.commit();
conn.close();
} catch (SQLException e)
{ // Rollback all the changes so as to undo
// the effect of both INSERT and UPDATE
conn.rollback();
ret_code = e.getErrorCode();
System.err.println(ret_code + e.getMessage()); conn.close();
}
}
}
Based on the above example, the new row inserted into EMP table is visible to the application only when the operations of inserting into EMP table and incrementing of the emp_cnt in dept_audit table are both successful. If any one of these operations fails, the entire transaction is rolled back.