Recipe 20.7 Using JDBC Prepared Statements


Problem

You want to save the overhead of parsing, compiling, and otherwise setting up a statement that will be called multiple times.

Solution

Use a PreparedStatement.

Discussion

An SQL query consists of textual characters. The database must first parse a query and then compile it into something that can be run in the database. This can add up to a lot of overhead if you are sending a lot of queries. In some types of applications, you'll use a number of queries that are the same syntactically but have different values:

select * from payroll where personnelNo = 12345; select * from payroll where personnelNo = 23740; select * from payroll where personnelNo = 97120;

In this case, the statement needs to be parsed and compiled only once. But if you keep making up select statements and sending them, the database mindlessly keeps parsing and compiling them. Better to use a prepared statement, in which the variable part is replaced by a parameter marker (a question mark). Then the statement need only be parsed (or organized, optimized, compiled, or whatever) once:

PreparedStatement ps = conn.prepareStatement(     "select * from payroll where personnelNo = ?;")

Before you can use this prepared statement, you must fill in the blanks with the appropriate set methods. These take a parameter number (starting at one, not zero like most things in Java) and the value to be plugged in. Then use executeQuery( ) with no arguments since the query is already stored in the statement:

ps.setInt(1, 12345); rs = ps.executeQuery( );

If there are multiple parameters, you address them by number; for example, if there were a second parameter of type double, its value would be set by:

ps.setDouble(2, 12345);

Example 20-9 is the JDBC version of the User accessor, UserDBJDBC. It uses prepared statements for inserting new users, changing passwords, and setting the last login date.

Example 20-9. UserDBJDBC.java
package jabadot; import java.sql.*; import java.io.*; import java.util.*; /** A UserDB using JDBC and a relational DBMS.  * We use the inherited getUser ("Find the User object for a given nickname")  * since we keep everything in memory in this version.  */ public class UserDBJDBC extends UserDB {     protected final static String DB_URL =         JDConstants.getProperty("jabadot.userdb.url");     protected PreparedStatement setPasswordStatement;     protected PreparedStatement addUserStmt;     protected PreparedStatement setLastLoginStmt;     protected PreparedStatement deleteUserStmt;     /** Default constructor */     protected UserDBJDBC( )      throws ClassNotFoundException, SQLException, IOException {         this(DB_URL);     }     /** Constructor */     public UserDBJDBC(String fn)      throws ClassNotFoundException, SQLException, IOException {         super( );         // Load the database driver         Class.forName("jdbc.idbDriver");         Connection conn = DriverManager.getConnection(fn,             "www", "");    // user, password         Statement stmt = conn.createStatement( );         ResultSet rs = stmt.executeQuery("select * from userdb");         while (rs.next( )) {             //name:password:fullname:City:Prov:Country:privs             // Get the fields from the query.             String nick = rs.getString(1);             String pass = rs.getString(2);             String full = rs.getString(3);             String email = rs.getString(4);             String city = rs.getString(5);             String prov = rs.getString(6);             String ctry = rs.getString(7);             int iprivs = rs.getInt(8);             // Construct a user object from the fields             User u = new User(nick, pass, full, email,                 city, prov, ctry, iprivs);             // Add it to the in-memory copy.             users.add(u);         }         stmt.close( );         rs.close( );        // All done with that resultset         // Set up the PreparedStatements now so we don't have to         // re-create them each time needed.         addUserStmt = conn.prepareStatement(             "insert into userdb values (?,?,?,?,?,?,?,?)");         setPasswordStatement = conn.prepareStatement(             "update userdb SET password = ? where name = ?");         setLastLoginStmt = conn.prepareStatement(             "update userdb SET lastLogin = ? where name = ?");         deleteUserStmt = conn.prepareStatement(             "delete from userdb where name = ?");     }     /** Add one user to the list, both in-memory and on disk. */     public synchronized void addUser(User nu)     throws IOException, SQLException {         // Add it to the in-memory list         super.addUser(nu);         // Copy fields from user to DB         addUserStmt.setString(1, nu.name);         addUserStmt.setString(2, nu.password);         addUserStmt.setString(3, nu.fullName);         addUserStmt.setString(4, nu.email);         addUserStmt.setString(5, nu.city);         addUserStmt.setString(6, nu.prov);         addUserStmt.setString(7, nu.country);         addUserStmt.setInt   (8, nu.getPrivs( ));         // Store in persistent DB         addUserStmt.executeUpdate( );     }     public void deleteUser(String nick) throws SQLException {         // Find the user object         User u = getUser(nick);         if (u == null) {             throw new SQLException("User " + nick + " not in in-memory DB");         }         deleteUserStmt.setString(1, nick);         int n = deleteUserStmt.executeUpdate( );         if (n != 1) {    // not just one row??             /*CANTHAPPEN */             throw new SQLException("ERROR: deleted " + n + " rows!!");         }         // IFF we deleted it from the DB, also remove from the in-memory list         users.remove(u);     }     public synchronized void setPassword(String nick, String newPass)      throws SQLException {         // Find the user object         User u = getUser(nick);         // Change it in DB first; if this fails, the info in         // the in-memory copy won't be changed either.         setPasswordStatement.setString(1, newPass);         setPasswordStatement.setString(2, nick);         setPasswordStatement.executeUpdate( );         // Change it in-memory         u.setPassword(newPass);     }     /** Update the Last Login Date field. */     public synchronized void setLoginDate(String nick, java.util.Date date)      throws SQLException {              // Find the user object         User u = getUser(nick);         // Change it in DB first; if this fails, the date in         // the in-memory copy won't be changed either.         // Have to convert from java.util.Date to java.sql.Date here.         // Would be more efficient to use java.sql.Date everywhere.         setLastLoginStmt.setDate(1, new java.sql.Date(date.getTime( )));         setLastLoginStmt.setString(2, nick);         setLastLoginStmt.executeUpdate( );         // Change it in-memory         u.setLastLoginDate(date);     } }

Another example of prepared statements is given in Recipe Recipe 20.11.



Java Cookbook
Java Cookbook, Second Edition
ISBN: 0596007019
EAN: 2147483647
Year: 2003
Pages: 409
Authors: Ian F Darwin

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net