I need some direction with accessing mysql using a java servlet

Question

Here is my code

import java.io.*; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.*; import java.sql.*; @WebServlet(name = "Scores", urlPatterns = {"/Scores"}) public class Scores extends HttpServlet{ private Connection conn; private PreparedStatement psmt; private ResultSet rs; private String tableName; private String ssnNum; @Override public void init() throws ServletException { connect(); } @Override public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); try{ tableName = request.getParameter("tableName"); ssnNum = request.getParameter("ssnNum"); rs = psmt.executeQuery(); out.print("\t\t\t"); out.print(rs.getString("Student") + " " + rs.getString("Score")); out.print("<br>"); out.close(); }catch(Exception e){ System.err.println(e); } } public void connect(){ try{ //Loads Driver Class.forName("com.mysql.jdbc.Driver"); //Establishes a connection to DataBase Javabook conn = DriverManager.getConnection( "jdbc:mysql://localhost/javabook", "root", "password"); psmt = conn.prepareStatement("select Student, Score from " + tableName + " where ssn = " + ssnNum); } catch (Exception e){ System.err.println(e); }//End Try/Catch Block } }

Here is the html

<%@page contentType="text/html" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>TravisMeyersP3</title> </head> <body> Find Your Current Score <form method = "get" action = "Scores"> <p>Social Security Number <font color = "#FF0000">*</font> <input type = "text" name = "ssnNum"> </p> <p>Course Id <select size = "1" name = "tableName"> <option value = "Cpp">C++</option> <option value = "AdvJava">Advanced Java</option> </select> </p> <p><input type = "submit" name = "Submit" value = "Submit"> </p> </body> </html>

I'm using apache tomcat 7.0.21, and java jdk 7 in netbeans. The servlet is supposed to access one of two tables I've created in mysql and display the values for the user parameters. I'm not getting any compile or runtime errors. For some reason the servlet isn't accessing mysql and displaying the result after the user submits the form.

First off, I have to thank Ryan Stewart, The Elite Gentleman and CodeBuzz for taking time out of their days to help me. Along with the above mentioned problems I also had a problem with sql syntax. As I set the variables tableName and ssnNum into my new PreparedStatement (PreparedStatement psmt = conn.prepareStatement("select Student, Score from ? where SSN = ?;");) using psmt.setString(1, tableName) etc. for some reason that was putting single quotes around the string in the prepared statement. MySQL didn't like that and the only place that was showing an error was in the tomcat command window. After fixing the above mentioned problems everything worked great. Again thank you everyone for helping me with this.

Opening a connection on servlet init and leaving it open is a Bad Idea. — Ryan Stewart
– Ryan Stewart, Commented Oct 7, 2011 at 5:05
Servlets aren't thread-safe, so shouldn't have any state. So e.g. your tablename field could be overwritten by another thread that's processing a different HTTP request, with unexpected results. — artbristol
– artbristol, Commented Oct 7, 2011 at 6:55

Ryan Stewart · Accepted Answer · 2011-10-07 05:47:50Z

There's no way that can work. On init(), you create a PreparedStatement using a query that includes the fields tableName and ssnNum. At that point, both of those are null. They don't get assigned until a request is made later on (in doGet()). You're definitely getting errors. You need to find them.

Other stuff:

Use local variables instead of fields where appropriate.
Open a new connection on each request.
Don't close the response Writer (or OutputStream).
//Loads Driver is a terrible comment. The others aren't too hot, either.
Never ever EVER concatenate strings directly into a SQL query. Use a PreparedStatement with placeholders and the set* methods to plug in parameters.
Add some meaningful logging to help you identify what's going on without having to attach a debugger. (You'll obviously have to figure out where the messages are going first, though.)
You'll need to call next() on the ResultSet before you can get values from it.
There's no need for the Class.forName(...) line.
Your ResultSet, Statement, and/or Connection need to be closed when you're done with them. Otherwise, you're hemmhoraging resources.
Instead of catching and ignoring exceptions, allow them to be thrown out of your servlet. It will help your troubleshooting.
Never catch Exception. Only catch the specific types you wish to catch.

Regarding #5, you can start reading up on SQL injection on Wikipedia and XKCD.
psmt = conn.prepareStatement("select Student, Score from ? where ssn = ?"); psmt.setString(1, tableName); psmt.setString(2, ssnNum);

Buhake Sindi · Accepted Answer · 2011-10-07 05:44:20Z

Your HTML <form> declaration is wrong:

<form method = "get" action = "Scores">

Here are some solutions to your errors above:

Your method must be GET and not get (follow the HTTP methods).
Your action must be /Scores and not Scores.
You need to call next() on ResultSet before using the getXXX methods (where XXX can be Int, String, Double, etc.).
Always close the ResultSet first, then the Statement/PreparedStatement, and finally, a Connection.

Also, I would suggest to never do this:

Never allow connections on Servlets at all. You're opening a connection on init() which is bad, since the connection can timeout or close and there's no way you can open it (Servlets are singletons).
Also, I would suggest using PreparedStatement instead of concatenating strings. The driver knows how to convert data types to respective SQL types.

I would suggest that you create a persistence layer that follows the simple Database CRUD operations.

Yagnesh Agola · Accepted Answer · 2011-10-07 05:13:56Z

your are prepared a query statement in init() function where you didn't get any record because
of the null value in the tableName and ssnNum.
so change this and prepared query when you received response in doGet() function after that execute it.

Sandeep Pathak · Accepted Answer · 2011-10-10 06:16:24Z

Create Prepared statement in doGet Method instead of connect method .`

 @Override public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); try{ tableName = request.getParameter("tableName"); ssnNum = request.getParameter("ssnNum"); psmt = conn.prepareStatement("select Student, Score from ? where ssn = ?"); psmt.setString(1,tableName); psmt.setString(2,ssnNum); rs = psmt.executeQuery(); out.print("\t\t\t"); out.print(rs.getString("Student") + " " + rs.getString("Score")); out.print("<br>"); out.close(); }catch(Exception e){ System.err.println(e); } } public void connect(){ try{ //Loads Driver Class.forName("com.mysql.jdbc.Driver"); //Establishes a connection to DataBase Javabook conn = DriverManager.getConnection( "jdbc:mysql://localhost/javabook", "root", "password"); } catch (Exception e){ System.err.println(e); }//End Try/Catch Block }

`

Better approach would be creating a business class that takes care of the database connectivity and use this class from the servlet . See Select Records Using PreparedStatement

Ok. I fixed the problems you guys mentioned. Still the same result. When I submit the form I get a blank page. ???
I originally had the prepared statement in the doget method. I put it in the connect method because I was just trying anything to get it to work.
I fixed the problems he mentioned except for using persistence layers, that's a bit advanced for me, I'll have to read up on it.

Collectives™ on Stack Overflow

I need some direction with accessing mysql using a java servlet

4 Answers 4

3 Comments

Comments

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

3 Comments

Related