I need some direction with accessing mysql using a java servlet
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.
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 Driveris 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.
Your HTML <form> declaration is wrong:
<form method = "get" action = "Scores">
Here are some solutions to your errors above:
- Your method must be
GETand notget(follow the HTTP methods). - Your action must be
/Scoresand notScores. - You need to call
next()onResultSetbefore using the getXXX methods (where XXX can beInt,String,Double, etc.). - Always close the
ResultSetfirst, then theStatement/PreparedStatement, and finally, aConnection.
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
PreparedStatementinstead 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.
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.
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
加载中,请稍侯......
精彩评论