Inserting Data Into a Table 

Use

This procedure illustrates how you can insert data into existing database tables using Open SQL/JDBC.

Prerequisites

You must have created the database tables in the Java Dictionary.

More information: Providing Java Dictionary Tables and Data Types

Procedure

You can insert data into a table using either of the following interfaces:

●      java.sql.Statement

                            a.      Get a Statement instance from the connection object.

More information: Getting a Connection to the Database

java.sql.Statement stmt = conn.createStatment();

                            b.      Use the following code to send an SQL statement to the database and add a record to the VEHICLE table:

try {       stmt.executeUpdate ("insert into VEHICLE "                   + "(NAME, PRICE, CURRENCY)"                   + "values ('Audi80', 25000, 'EUR')");    } finally {       stmt.close();    }

Using the same method, you can also send UPDATE or DELETE SQL statements to the database to change or delete records. You must close a Statementobject that is no longer needed using its close()method. We recommend that you do this in a finally block.

●      java.sql.PreparedStatement

A more efficient method of inserting records is to use PreparedStatement objects. The PreparedStatement interface extends the Statement interface, and has the following advantages:

○       When PreparedStatement objects are created, the SQL statement is sent to the database and is translated there only. Translation within the executeUpdate method is then no longer necessary, which is not the case for Statement objects. The translation of SQL statements by the database is a time-consuming operation.

○       PreparedStatement objects are buffered.

More information: Statement Pooling

○       PreparedStatement objects can receive input parameters for column values.

The following code snippet illustrates how you can insert the same data as in the example above using PreparedStatement:

java.sql.PreparedStatement ps =       con.prepareStatement("insert into VEHICLE " +                      "(NAME, PRICE, CURRENCY) " +                      "values (?, ?, ?)");

The question marks are placeholders for fixed values that you must set before executing an SQL statement. The placeholders are filled using the setXXX methods of the PreparedStatement with consecutive numbering starting from 1.

try {    ps.setString (1, "Audi80");    ps.setInt(2, 25000);    ps.setString(3, "EUR");    ps.executeUpdate(); } finally {    ps.close(); }

You can check which setXXX methods are valid for which data types in the type compatibility matrixes.

More information: Data Type Conversion Tables

A PreparedStatement object is not required to have input parameters, but it is particularly useful if only the values of the columns change in an SQL statement.

The most efficient way of inserting multiple data records into a database is to use the batch methods of PreparedStatement.

PreparedStatement ps =    con.prepareStatement("insert into VEHICLE " +                    "(NAME, PRICE, CURRENCY) " +                    "values (?, ?, ?)"); try {    ps.setString (1, "VW Polo");    ps.setInt(2, 15000);    ps.setString(3, "EUR");    ps.addBatch();    ps.setString (1, "BMW 323");    ps.setInt(2, 30000);    ps.setString(3, "EUR");    ps.addBatch(); ...    ps.executeBatch(); } finally {    ps.close(); }

The change operations are collected in the JDBC driver and then sent as a package to the database. To cancel addBatch() operations, you can use the clearBatch() method. You must close a PreparedStatement object that is no longer needed using its close() method. We recommend that you do this in a finally block.

If an error occurs while inserting the records, a BatchUpdateException is triggered. The result may be one of the following:

                            a.      The records that have not been inserted are recorded in an output array (see below).

                            b.      Only the first n records are inserted (where the number n can be determined).

The following example is a typical error handling scenario:

import java.sql.BatchUpdateException; import java.sql.PreparedStatement;   ...   int expectedInsCnt = 0;   ps.setString (1, "VW Polo"); ps.setInt(2, 15000); ps.setString(3, "EUR"); ps.addBatch(); expectedInsCnt ++; ps.setString (1, "BMW 323"); ps.setInt(2, 30000); ps.setString(3, "EUR"); ps.addBatch(); expectedInsCnt ++; try {    ps.executeBatch(); } catch (BatchUpdateException bue) {    int[] insCnts = bue.getUpdateCounts();    int insCntSize = isnCnts.length;    if ( insCntSize == expectedInsCnt ) {       /* case 1 */       for (int i = 0; i < insCntSize; i++) {           if ( insCnts[i] == -3 ) {              // Record i+1 could not be inserted           }       }    } else if (insCntSize < expectedInsCnt) {       /* Case 2: The first insCntSize records were          inserted */    } } finally {    ps.close(); }

The method BatchUpdateException.getUpdateCounts() identifies which records contain errors and which records have been inserted successfully.

The method getUpdateCounts() may also return the generic code -2, which indicates successful operation but the number of affected rows is unknown. The generic code -3 indicates operation failure.