database connection statement being missed out
I have the following servlet the problem is when i run it its meant to do an update sql followed by a select and then an insert, but it is missing out the select statement can somebody please help me:
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
// Connects to a database to retrieve music data
publicclass addRentalextends HttpServlet{
publicvoid doGet(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException{
response.setContentType("text/html");
PrintWriter out = response.getWriter();
String user;
Cookie[] cookies = request.getCookies();
Cookie cookie;
for(int i=0; i < 1; i++){
cookie = cookies[i];
cookie.getValue();
user = cookie.getValue();
System.out.println(" cookie vlaue is " + user);
// Database connection code starts here
Connection conn =null;
// loading jdbc driver for mysql (help in mysql.jar file in classpath)
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
}catch(Exception e){
System.out.println(e);
}
// connecting to database
try{
// connection string for demos database, username demos, password demo-pass
conn = DriverManager.getConnection
("jdbc:myurl");
// System.out.println("Connection to database successful.");
}
catch(SQLException se){
System.out.println(se);
}
// Create select statement and execute it
try{
String recordingIDStr = request.getParameter("recordingid");// this id comes from a form
String video_title = request.getParameter("filmtitle");// this id comes from a form
String image = request.getParameter("dvdimage");// this id comes from a form
int recordingID = Integer.parseInt(recordingIDStr);
int priority = 4;
String rentals_allowed;
String rentals_had;
String update ="update video_recordings" +
" set times_rented = times_rented + 1" +
" where recording_id = " + recordingID;
// adds one to number of times rented
System.out.println("insert statement " + update);
Statement stmt3 = conn.createStatement();
int rowsAffected = stmt3.executeUpdate(update);
////////////////////////////////////////////// THIS STATEMENT NOT GETTING EXECUTED///////
String selectSQL ="select num_rentals, num_rentals_had "+
"from customers "+
" where user_name = ' " + user +"'";
Statement stmt4 = conn.createStatement();
ResultSet rs1 = stmt4.executeQuery(selectSQL);
while(rs1.next()){
rentals_allowed =rs1.getString("num_rentals");
rentals_had =rs1.getString("num_rentals_had");
System.out.println(" rents allowed " + rentals_allowed);
}
String insertSQL ="insert into video_rental(user_name, recording_title, recording_id, Priortiy) values (' "+
user +"','" + video_title +"','" + recordingID +"','"+ priority +"')";
System.out.println("insert statement " + insertSQL);
Statement stmt = conn.createStatement();
int rowsAffected2 = stmt.executeUpdate(insertSQL);
out.println("</BODY></HTML>");
stmt.close();
conn.close();
}catch(SQLException se){
System.out.println(se);
}
}
}// from cookie
}
