4.2.3.3.1 Creating the PreparedStatement Object
Refer to Table 4.3. The prepareStatement() method defined in the Connection interface is used to create a PreparedStatement object. Example code to create a PreparedStatement object looks like:
PreparedStatement pstmt = con.prepareStatement(query);
Unlike Statement objects, the PreparedStatement object takes the Oracle statement to be executed as an argument. For dynamic Oracle statements that contain input parameters to be passed into the database, you need to define a position for each input parameter in the query string. Usually, a placeholder is used to inform the database that it can expect a variable in that position. Each place-holder that holds a position for a variable in the query string is represented by a question mark,?, which holds a place for the associated variable during compiling time. When compiled, the place-holder is part of the statement and therefore appears static to the compiler. In this way, no matter what value is later assigned to the variable, the database does not need to recompile the statement. At run time, you can assign values to the variables by using the any setXXX() method defined in the PreparedStatement interface shown in Table 4.5.
Before we can call an execution method to run the PreparedStatement to perform a dynamic query, let’s first take a look at how to use the setXXX() method to reserve a place for the input parameter with the correct data type settings.
4.2.3.3.2 Setting the Input Parameters
All input parameters used for a PreparedStatement interface must be clearly bound to the associated IN parameters in a dynamic query string by using a setXXX() method. This setXXX() method can be divided into three categories based on the different data types:
1) The primitive data type method
2) The object method
3) The stream method
For the primitive and object methods, the syntax is identical, and the difference between them is the type of value that is assigned. For the stream method, both the syntax and the data types are different.
4.2.3.3.3 Set Primitive Data Type and Object IN Values
The primitive data type means all built-in data types used in the Java programming language. The syntax of setting a primitive data type or an object value method is:
setXXX(int position, data _ type value);
where XXX means the associated value type to be assigned, position is an integer used to indi-cate the relative position of the IN parameter in the Oracle statement or the Oracle stored procedure and value is the actual data value to be assigned to the IN parameter.
Some popular setXXX() methods defined in the PreparedStatement interface can be found in Table 4.5.
An example of using the setXXX() method is:
String query = “SELECT product, order _ date FROM Order “+ “WHERE order _ id =? AND customer =?”; PreparedStatement pstmt = con.prepareStatement(query); setInt(1, 101);
setString(2, “Tom Johnson”);
Two dynamic parameters are used in the query string, and both of them are IN parameters. The
data type of the first IN parameter is an integer, the second one is a string and both are represented by a placeholder,?. The first setting method, setInt(1, 101), is to assign an integer value of 101 to the first IN parameter, which is indicated with a position number of 1, and the second setting method, setString(2, “Tom Johnson”), is to assign a string value “Tom Johnson” to the second IN parameter, which is indicated with a position number of 2.
From this example, you can see that there is no difference between setting a primitive parameter and an object value to the IN parameters in an Oracle statement.
Set Object Methods
The setObject() method has three protocols, which are:setObject(int position, object _ type object _ value);
setObject(int position, object _ type object _ value,desired _ data _ type);
setobject(int position, object _ type object _ value,desired _ data _ type, int scale);
data _ type data _ type
The first one is straightforward, and it contains two parameters; the first one is the relative posi-tion of the IN parameter in the Oracle statement, and the second one is the value of a desired object to be assigned to the IN object.
The second one adds one more input parameter, desired _ data _ type, and it is used to indicate a data type to which convert to the object.
The third one adds the fourth input parameter, scale, and it is used to make sure that the object conversion result contains a certain number of digits.
An example of the setObject() method is shown here:
pstmt.setObject(2, 101);
pstmt.setObject(2, 101, Type.FLOAT);
pstmt.setObject(2, 101, Type.FLOAT, 2);
The first method is to set an input parameter, which is the second one in an Oracle statement, to an object (here an integer) with a value of 101. The next method is to set the same input to the same object; however, it needs to convert the object (integer) to a float data type. The final method performs the same operation as the previous one, but it indicates that the conversion result should contain at least twodigits.
Since set stream IN methods are not very popular in Java database applications, we skip that part in this section. If you want to get more detailed information for these methods, refer to some sections in Chapter 6. Now let’s begin to call some appropriate execution methods to run the PreparedStatement object to perform dynamic queries.