one servlet making multiple calls to database
I have a servlet which take details a user has made and put them into a database, i now want it to make more then one call to the database. How would i make it so first it would execute the sql statement:
select times_rented from video_recordings where recording_id is some string from previous page
This would then be stored have 1 added to it and the value put back into the table, the servlet below is what i want to edit, my question is not so much about the sql but from which point in the code do i need to copy and paste to make multiple calls to and from the database? obviously i will chnage the names of the different connections but which parts do i need to use?
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
publicclass addRentalextends HttpServlet{
publicvoid doGet(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException{
response.setContentType("text/html");
PrintWriter out = response.getWriter();
HttpSession session = request.getSession();
String username = (String)session.getAttribute("username");
if (username ==null){
out.println(
"<BODY BGCOLOR=\"#a00e0e\">\n" +
"<center></center>\n");
out.println("You are not logged in ");
}
else{
Connection conn =null;
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
}catch(Exception e){
System.out.println(e);
}
try{
conn = DriverManager.getConnection
("jdbc:myurl");
}
catch(SQLException se){
System.out.println(se);
}
try{
String recordingIDStr = request.getParameter("recordingid");// this id comes from a form
int recordingID = Integer.parseInt(recordingIDStr);// convert to string to alow to update to table
int userid = 0;// we would have this to get id from cookie or seesion
int priority = 4;
String insertSQL ="insert into video_rental(customer_id, recording_id, Priortiy) values ( "+
userid +"," + recordingID +"," + priority +")";
System.out.println("insert statement " + insertSQL);
Statement stmt = conn.createStatement();
int rowsAffected = stmt.executeUpdate(insertSQL);
String title ="Added rental : "+ recordingID;
out.println(ServletUtilities.headWithTitle(title) +
"<BODY BGCOLOR=\"#a00e0e\">\n" +
"<center></center>\n" +
"<H1 ALIGN=\"CENTER\">" + title +"</H1>\n");
out.println("Added rental");
out.println("</BODY></HTML>");
stmt.close();
conn.close();
}catch(SQLException se){
System.out.println(se);
}
}
}
}
[4753 byte] By [
ajrobsona] at [2007-11-26 14:29:11]
# 1
Connection con = getConnection();
PreparedStatement ps = con.prepareStatement(sql1);
ps.execute();
ps.close();
ps = con.prepareStatement(sql2);
ps.execute();
ps.close();
You can also do:
Connection con = getConnection();
String sql = "insert into mytable values(?)";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, "first insert");
ps.execute();
ps.setString(1, "second insert");
ps.execute();
ps.close();
So, if the SQL is different each time, you should create new PreparedStatements for each different. SQL. If the SQL is the same with different binding parameters, and you want to call it multiple times, you can simply rebind and execute it again.
Remember to clean up all of your Connecttions and Statements when you're done by calling close.
ps.execute();
# 2
Thanks for the reply i am still a bit confused though when comparing your example to mine, given the above servlet and a connection to a database i have copied below from another servlet how would i put them both in the same servlet?
Connection conn = null;
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch(Exception e) {
System.out.println(e);
}
try{
conn = DriverManager.getConnection
("jdbc:myurl");
}
catch(SQLException se) {
System.out.println(se);
}
try{
String recordingIDStr = request.getParameter("recordingid");
int recordingID = Integer.parseInt(recordingIDStr);
int userid = 0;
String insertSQL = "insert into video_sales(customer_id, recording_id) values ( "+
userid + "," + recordingID + ")";
System.out.println("insert statement " + insertSQL);
Statement stmt = conn.createStatement();
int rowsAffected = stmt.executeUpdate(insertSQL);
String title = "Bought dvd need to get film detaisl title: "+ recordingID;
out.println(ServletUtilities.headWithTitle(title) +
"<BODY BGCOLOR=\"#a00e0e\">\n" +
"<center></center>\n" +
"<H1 ALIGN=\"CENTER\">" + title + "</H1>\n");
out.println("Bought DVD need to put title here to");
out.println("</BODY></HTML>");
stmt.close();
conn.close();
} catch(SQLException se) {
System.out.println(se);
}
}
I would need to chnage Statement stmt = conn.createStatement(); to something else like Statement stmt2 = conn.createStatement(); and the same for the string which i use to insert the sql statement but apart from that which parts of this do i need and which can i get rid to put two sql connection statement in my servlet?
# 3
Just like I showed earlier.
String recordingIDStr = request.getParameter("recordingid"); // this id comes from a form
int recordingID = Integer.parseInt(recordingIDStr); // convert to string to alow to update to table
int userid = 0; // we would have this to get id from cookie or seesion
int priority = 4;
String insertRentalSQL = "insert into video_rental(customer_id, recording_id, Priortiy) values (?, ?, ?)";
String insertSaleSQL = "insert into video_sales(customer_id, recording_id) values (?, ?)";
PreparedStatment ps = conn.prepareStatement(insertRentalSQL);
ps.setInt(1, userid);
ps.setInt(2, recordingID);
ps.setInt(3, priority);
ps.executeUpdate();
ps.close();
ps = conn.prepareStatement(insertSaleSQL);
ps.setInt(1, userid);
ps.setInt(2, recordingID);
ps.executeUpdate();
ps.close();
Unless you have VERY good reasons, you should never build a SQL statement directly from input parameters, rather you should bind the paramaters. This prevents SQL Injection attacks against your system.
