35.14. Programming Exercises
32.5. PreparedStatement
Once a connection to a particular database is established, it can be used to send SQL statements from your program to the database. The Statement interface is used to execute static SQL statements that contain no parameters. The PreparedStatement interface, extending Statement , is used to execute a precompiled SQL statement with or without parameters. Since the SQL statements are precompiled, they are efficient for repeated executions.
A PreparedStatement object is created using the preparedStatement method in the Connection interface. For example, the following code creates a PreparedStatement pstmt on a particular Connection connection for an SQL insert statement:
Statement pstmt = connection.prepareStatement ( "insert into Student (firstName, mi, lastName) " + "values (?, ?, ?)" );
This insert statement has three question marks as placeholders for parameters representing values for firstName , mi , and lastName in a record of the Student table.
As a subinterface of Statement , the PreparedStatement interface inherits all the methods defined in Statement . It also provides the methods for setting parameters in the object of PreparedStatement . These methods are used to set the values for the parameters before executing statements or procedures. In general, the set methods have the following name and signature:
set X ( int parameterIndex, X value);
where X is the type of the parameter, and parameterIndex is the index of the parameter in the statement. The index starts from 1. For example, the method setString(int parameterIndex, String value) sets a String value to the specified parameter.
The following statements pass the parameters "Jack" , "A" , "Ryan" to the placeholders for firstName, mi, and lastName in PreparedStatement pstmt :
pstmt.setString( 1 , "Jack" ); pstmt.setString( 2 , "A" ); pstmt.setString( 3 , "Ryan" );
After setting the parameters, you can execute the prepared statement by invoking executeQuery() for a SELECT statement and executeUpdate() for a DDL or update statement.
The executeQuery() and executeUpdate() methods are similar to the ones defined in the Statement interface except that they have no parameters, because the SQL statements are already specified in the preparedStatement method when the object of PreparedStatement is created.
Using a prepared SQL statement, Listing 32.2 can be improved in Listing 32.3.
Listing 32.3. FindGradeUsingPreparedStatement.java
(This item is displayed on pages 1114 - 1115 in the print version)
1 import javax.swing.*; 2 import java.sql.*; 3 import java.awt.*; 4 import java.awt.event.*; 5 6 public class FindGradeUsingPreparedStatement extends JApplet { 7 private JTextField jtfSSN = new JTextField( 9 ); 8 private JTextField jtfCourseId = new JTextField( 5 ); 9 private JButton jbtShowGrade = new JButton( "Show Grade" ); 10 11 // PreparedStatement for executing queries 12 private PreparedStatement pstmt; 13 14 /** Initialize the applet */ 15 public void init() { 16 // Initialize database connection and create a Statement object 17 initializeDB(); 18 19 jbtShowGrade.addActionListener( 20 new java.awt.event.ActionListener() { 21 public void actionPerformed(ActionEvent e) { 22 jbtShowGrade_actionPerformed(e); 23 } 24 }); 25 26 JPanel jPanel1 = new JPanel(); 27 jPanel1.add( new JLabel( "SSN" )); 28 jPanel1.add(jtfSSN); 29 jPanel1.add( new JLabel( "Course ID" )); 30 jPanel1.add(jtfCourseId); 31 jPanel1.add(jbtShowGrade); 32 33 add(jPanel1, BorderLayout.NORTH); 34 } 35 36 private void initializeDB() { 37 try { 38 // Load the JDBC driver 39 Class.forName( "com.mysql.jdbc.Driver" ); 40 // Class.forName("oracle.jdbc.driver.OracleDriver"); 41 System.out.println( "Driver loaded" ); 42 43 // Establish a connection 44 Connection connection = DriverManager.getConnection 45 ( "jdbc:mysql://liang.armstrong.edu/test" ); 46 // ("jdbc:oracle:thin:@liang.armstrong.edu: 1521:orcl " , 47 // "scott", "tiger"); 48 System.out.println( "Database connected" ); 49 50 String queryString = "select firstName, mi, " + 51 "lastName, title, grade from Student, Enrollment, Course " + 52 "where Student.ssn = ? and Enrollment.courseId = ? " + 53 "and Enrollment.courseId = Course.courseId" ; 54 55 // Create a statement 56 pstmt = connection.prepareStatement(queryString); 57 } 58 catch (Exception ex) { 59 ex.printStackTrace(); 60 } 61 } 62 63 private void jbtShowGrade_actionPerformed(ActionEvent e) { 64 String ssn = jtfSSN.getText(); 65 String courseId = jtfCourseId.getText(); 66 try { 67 pstmt.setString( 1 , ssn); 68 pstmt.setString( 2 , courseId); 69 ResultSet rset = pstmt.executeQuery(); 70 71 if (rset. next ()) { 72 String lastName = rset.getString( 1 ); 73 String mi = rset.getString( 2 ); 74 String firstName = rset.getString( 3 ); 75 String title = rset.getString( 4 ); 76 String grade = rset.getString( 5 ); 77 78 // Display result in a dialog box 79 JOptionPane.showMessageDialog( null , firstName + " " + mi + 80 " " + lastName + "'s grade on course " + title + " is " + 81 grade); 82 } 83 else { 84 // Display result in a dialog box 85 JOptionPane.showMessageDialog( null , "Not found" ); 86 } 87 } 88 catch (SQLException ex) { 89 ex.printStackTrace(); 90 } 91 } 92 } |
This example does exactly the same thing as Listing 32.2 except that it uses the prepared statement to dynamically set the parameters. The code in this example is almost the same as in the preceding example. The new code is highlighted.
A prepared query string is defined in lines 50 “53 with ssn and courseId as parameters. An SQL prepared statement is obtained in line 56. Before executing the query, the actual values of ssn and courseId are set to the parameters in lines 67 “68. Line 69 executes the prepared statement.
EAN: N/A
Pages: 503