Accessing Databases Using JDBC - Java Programming: Comprehensive Concepts and Techniques - page 736

171 PreparedStatement pStmt = con.prepareStatement ( "UPDATE Users SET lastName = ?,"

172 + " firstName = ?, pswd = ?, admin = ? WHERE userID = ?" ) ;

173
174 pStmt.setString ( 1, dbLastName ) ;

175 pStmt.setString ( 2, dbFirstName ) ;

176 pStmt.setBytes ( 3, serializeObj ( pswd )) ;

177 pStmt.setBoolean ( 4, isAdmin ) ;

178 pStmt.setString ( 5, dbUserID ) ;

179
180 pStmt.executeUpdate () ;

181 pStmt.close () ;

182 result = true ;

183 }

184 else

185 throw new IOException ( "User does not exist - cannot update." ) ;

186
187 return result;

188 }

189
190
191 ////////////////////////////////////////////////////////////////////////////

192 // Methods for deleting a record from a table

193 ////////////////////////////////////////////////////////////////////////////

194
195 // delete a record from the Stocks table

196 private void delStock ( String stockSymbol )

197 throws SQLException , IOException , ClassNotFoundException

198 {

199 Statement stmt = con.createStatement () ;

200 stmt.executeUpdate ( "DELETE FROM Stocks WHERE "

201 + "symbol = '" +stockSymbol+ "'" ) ;

202 stmt.close () ;

203 }

204
205 // delete a record from the Users table

206 public void delUser ( User user ) throws SQLException , IOException ,

207 ClassNotFoundException

208 {

209 String dbUserID;

210 String stockSymbol;

211
212 Statement stmt = con.createStatement () ;

213
214 try {

215 con.setAutoCommit ( false ) ;

216
217 dbUserID = user.getUserID () ;

218 if ( getUser ( dbUserID ) != null ) // verify user exists in database

219 {

220 ResultSet rs1 = stmt.executeQuery ( "SELECT userID, symbol "

221 + "FROM UserStocks WHERE userID = '" +dbUserID+ "'" ) ;

222 while ( rs1.next ())

223 {

224 try

225 {

226 stockSymbol = rs1.getString ( "symbol" ) ;

227 delUserStocks ( dbUserID, stockSymbol ) ;

228 }

229 catch ( SQLException ex )

230 {

231 throw new SQLException ( "Deletion of user stock holding failed: "

232 +ex.getMessage ()) ;

233 }

234 } // end of loop thru UserStocks

235

FIGURE 11-36