- Instances of PreparedStatement contain an SQL statement that has already been compiled. This is what makes a statement "prepared."
- The SQL statement contained in a PreparedStatement object may have one or more IN parameters. An IN parameter is a parameter whose value is not specified when the SQL statement is created. Instead, the statement has a question mark ("?") as a placeholder for each IN parameter. The "?" is also known as a parameter marker. An application must set a value for each question mark in a prepared statement before executing the prepared statement.
Because PreparedStatement objects are precompiled, their execution can be faster than that of Statement objects. Consequently, an SQL statement that is executed many times is often created as a PreparedStatement object to increase efficiency.
Being a subclass of Statement, PreparedStatement inherits all the functionality of Statement. In addition, it adds a set of methods that are needed for setting the values to be sent to the database in place of the placeholders for IN parameters. Also, the three methods execute, executeQuery, and executeUpdate are modified so that they take no argument.
The Statement forms of these methods (the forms that take an SQL statement parameter) should never be used with a PreparedStatement object.
I am going to use Table OHEAD in DB2 database for all my examples here. You can connect to any other database such as MYSQL, SQL Server, Oracle etc using the same JDBC preparedstatement syntax. Here is my table definition for OHEAD
Field Type Size Key Text X1DEL Char 1 Status X1CMP Zoned 3,0 1 Company No. X1ORD Zoned 9,0 2 Order Number X1TYPE Char 1 Transaction Type X1ORDT Zoned 8,0 Order Date X1CUST Char 10 Customer Number X1PO Char 20 PO Number X1OTOT Zoned 11,2 Order Total
Sample Java code for PreparedStatement
package com.as400samplecode;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Properties;
public class JDBCPreparedStatements {
static Properties props;
public static final int SQL_INSERT = 1;
public static final int SQL_SELECT = 2;
public static final int SQL_UPDATE = 3;
public static final int SQL_DELETE = 4;
public static void main(String[] args) {
int transactionType = 0;
String propertiesFileName = "";
if (args.length < 2)
{
JDBCPreparedStatements jdbcPreparedStatements = new JDBCPreparedStatements();
System.err.println("Usage: java "+ jdbcPreparedStatements.getClass().getName()+
" Properties_File_Name Transaction_Type");
System.exit(1);
}
propertiesFileName = args[0].trim();
transactionType = Integer.parseInt(args[1].trim());
switch (transactionType) {
//JDBC Insert PreparedStatement
case SQL_INSERT:
doInsert(propertiesFileName);
break;
//JDBC Select PreparedStatement
case SQL_SELECT:
doSelect(propertiesFileName);
break;
//JDBC Update PreparedStatement
case SQL_UPDATE:
doUpdate(propertiesFileName);
break;
//JDBC Delete PreparedStatement
case SQL_DELETE:
doDelete(propertiesFileName);
break;
default:
break;
}
}
private static void doInsert(String propertiesFileName){
Connection Db2Conn = null;
PreparedStatement Db2Stmt = null;
String sql = null;
try {
props = new Properties();
props.load(new FileInputStream("properties/" + propertiesFileName.trim()));
// Database connection string
String DRIVER = "com.ibm.as400.access.AS400JDBCDriver";
String URL = "jdbc:as400://" + props.getProperty("local_system").trim() + ";naming=system;errors=full";
// Connect to Database
Class.forName(DRIVER);
Db2Conn = DriverManager.getConnection(URL,props.getProperty("userId").trim(),props.getProperty("password").trim());
// Db2 SQL statement to insert data
sql = "INSERT into OHEAD (X1DEL,X1CMP,X1ORD,X1TYPE,X1ORDT,X1CUST,X1PO,X1OTOT) VALUES(?,?,?,?,?,?,?,?)";
Db2Stmt = Db2Conn.prepareStatement(sql);
Db2Stmt.setString(1,"A");
Db2Stmt.setInt(2,1);
Db2Stmt.setInt(3,123);
Db2Stmt.setString(4,"O");
Db2Stmt.setInt(5,20110628);
Db2Stmt.setString(6,"ABCD");
Db2Stmt.setString(7,"TEST PO");
Db2Stmt.setDouble(8, 99.99);
Db2Stmt.executeUpdate();
Db2Stmt.setString(1,"A");
Db2Stmt.setInt(2,1);
Db2Stmt.setInt(3,124);
Db2Stmt.setString(4,"O");
Db2Stmt.setInt(5,20110629);
Db2Stmt.setString(6,"ABCD");
Db2Stmt.setString(7,"TEST PO2");
Db2Stmt.setDouble(8, 19.99);
Db2Stmt.executeUpdate();
}
// Handle any errors that may have occurred.
catch (Exception e) {
e.printStackTrace();
}
finally {
if (Db2Stmt != null) try { Db2Stmt.close(); } catch(Exception e) {}
if (Db2Conn != null) try { Db2Conn.close(); } catch(Exception e) {}
}
}
private static void doSelect(String propertiesFileName){
Connection Db2Conn = null;
PreparedStatement Db2Stmt = null;
ResultSet Db2Rs = null;
String sql = null;
try {
props = new Properties();
props.load(new FileInputStream("properties/" + propertiesFileName.trim()));
// Database connection string
String DRIVER = "com.ibm.as400.access.AS400JDBCDriver";
String URL = "jdbc:as400://" + props.getProperty("local_system").trim() + ";naming=system;errors=full";
// Connect to Database
Class.forName(DRIVER);
Db2Conn = DriverManager.getConnection(URL,props.getProperty("userId").trim(),props.getProperty("password").trim());
// Db2 SQL statement to insert data
sql = "SELECT * from OHEAD where X1CMP = ? and X1ORD = ?";
Db2Stmt = Db2Conn.prepareStatement(sql);
Db2Stmt.setInt(1, 1);
Db2Stmt.setInt(2, 123);
Db2Rs = Db2Stmt.executeQuery();
// Get MetaData for Db2 table
ResultSetMetaData metaData = Db2Rs.getMetaData();
int colCount = metaData.getColumnCount();
// Iterate through the Db2 data in the result set
while (Db2Rs.next()) {
for (int i = 0; i < colCount; i++) {
System.out.println(metaData.getColumnName(i + 1) + ": " + Db2Rs.getString(i+1));
}
}
}
// Handle any errors that may have occurred.
catch (Exception e) {
e.printStackTrace();
}
finally {
if (Db2Rs != null) try { Db2Rs.close(); } catch(Exception e) {}
if (Db2Stmt != null) try { Db2Stmt.close(); } catch(Exception e) {}
if (Db2Conn != null) try { Db2Conn.close(); } catch(Exception e) {}
}
}
private static void doUpdate(String propertiesFileName){
Connection Db2Conn = null;
PreparedStatement Db2Stmt = null;
String sql = null;
try {
props = new Properties();
props.load(new FileInputStream("properties/" + propertiesFileName.trim()));
// Database connection string
String DRIVER = "com.ibm.as400.access.AS400JDBCDriver";
String URL = "jdbc:as400://" + props.getProperty("local_system").trim() + ";naming=system;errors=full";
// Connect to Database
Class.forName(DRIVER);
Db2Conn = DriverManager.getConnection(URL,props.getProperty("userId").trim(),props.getProperty("password").trim());
// Db2 SQL statement to insert data
sql = "UPDATE OHEAD set X1ORDT = ?,X1CUST = ?,X1PO = ?,X1OTOT = ? where X1CMP = ? and X1ORD = ?";
Db2Stmt = Db2Conn.prepareStatement(sql);
Db2Stmt.setInt(1,20110630);
Db2Stmt.setString(2,"ABAB");
Db2Stmt.setString(3,"TEST PO1");
Db2Stmt.setDouble(4, 101.99);
Db2Stmt.setInt(5,1);
Db2Stmt.setInt(6,123);
Db2Stmt.executeUpdate();
// Batch Update
Db2Stmt.setInt(1,20110629);
Db2Stmt.setString(2,"AAAA");
Db2Stmt.setString(3,"TEST PO1A");
Db2Stmt.setDouble(4, 101.01);
Db2Stmt.setInt(5,1);
Db2Stmt.setInt(6,123);
Db2Stmt.addBatch();
Db2Stmt.setInt(1,20110629);
Db2Stmt.setString(2,"BBBB");
Db2Stmt.setString(3,"TEST PO1B");
Db2Stmt.setDouble(4, 101.02);
Db2Stmt.setInt(5,1);
Db2Stmt.setInt(6,124);
Db2Stmt.addBatch();
Db2Stmt.executeBatch();
}
// Handle any errors that may have occurred.
catch (Exception e) {
e.printStackTrace();
}
finally {
if (Db2Stmt != null) try { Db2Stmt.close(); } catch(Exception e) {}
if (Db2Conn != null) try { Db2Conn.close(); } catch(Exception e) {}
}
}
private static void doDelete(String propertiesFileName){
Connection Db2Conn = null;
PreparedStatement Db2Stmt = null;
String sql = null;
try {
props = new Properties();
props.load(new FileInputStream("properties/" + propertiesFileName.trim()));
// Database connection string
String DRIVER = "com.ibm.as400.access.AS400JDBCDriver";
String URL = "jdbc:as400://" + props.getProperty("local_system").trim() + ";naming=system;errors=full";
// Connect to Database
Class.forName(DRIVER);
Db2Conn = DriverManager.getConnection(URL,props.getProperty("userId").trim(),props.getProperty("password").trim());
// Db2 SQL statement to insert data
sql = "DELETE from OHEAD where X1CMP = ? and X1ORD = ?";
Db2Stmt = Db2Conn.prepareStatement(sql);
Db2Stmt.setInt(1,1);
Db2Stmt.setInt(2,123);
Db2Stmt.executeUpdate();
}
// Handle any errors that may have occurred.
catch (Exception e) {
e.printStackTrace();
}
finally {
if (Db2Stmt != null) try { Db2Stmt.close(); } catch(Exception e) {}
if (Db2Conn != null) try { Db2Conn.close(); } catch(Exception e) {}
}
}
}
No comments:
Post a Comment
NO JUNK, Please try to keep this clean and related to the topic at hand.
Comments are for users to ask questions, collaborate or improve on existing.