Prepared Statements in Jdbc

Rather than Statement objects, PreparedStatement objects can be used. This have the advantages over plain Statement objects of:

• For repetitive queries that are very similar except for some parameter values, they are considerably more efficient because the SQL is compiled once and then executed many times, with the parameter values substituted in each execution
• The mechanism for inserting parameter values takes care of all necessary special character quoting in the correct manner for the connected database

The PreparedStatement has its SQL text set when it is constructed. The parameters are specified as '?' characters. After creation, the parameters can be cleared using clearParameters and set using setInt, setString, etc. methods (parameter positions start at 1) and the statement can then be executed using execute, executeUpdate or executeQuery methods as for Statement and with the same return types but with no arguments (as the SQL text has already been set when the statement was created):

PreparedStatement pstmt = con.prepareStatement(
                              "INSERT INTO Customers " +
                              "(CustomerFirstName, CustomerLastName, CustomerAddress) "+
                              "VALUES (?, ?, ?)") ;

pstmt.clearParameters() ;
pstmt.setString(1, "Joan") ;
pstmt.setString(2, "D'Arc") ;
pstmt.setString(3, "Tower of London") ;
count = pstmt.executeUpdate() ;
System.out.println ("\nInserted " + count + " record successfully\n") ;

pstmt.clearParameters() ;
pstmt.setString(1, "John") ;
pstmt.setString(2, "D'Orc") ;
pstmt.setString(3, "Houses of Parliament, London") ;
count = pstmt.executeUpdate() ;
System.out.println ("\nInserted " + count + " record successfully\n") ;