Unable to connect MYSQL database using JSP and TOMCAT
I am unable to connect database “test” created in mySQL using Java Server Pages (JSP) & Tomcat. I am getting the error message "Unable to connect to database".
I am using following software specifications
- Tomcat 6.0
- jdk1.6.0_21
- mySQL 5.1.49
- mysql-connector-java-5.1.13-bin
I have configured following environment variables as follow
CATALINA_HOME: C:\Program Files\Tomcat 6.0CLASSPATH: C:\Program Files\Tomcat 6.0\lib\servlet-api.jar;C:\Program Files\Tomcat 6.0\lib\jsp-api.jar;C:\Program Files\Java\jdk1.6.0_21\jre\lib\ext\mysql-connector-java-5.1.13-bin.jar.;JAVA_HOME: C:\Program Files\Java\jdk1.6.0_21
I have placed mysql-connector-java-5.1.13-bin jar file at following two locations:
- CATALINE_HOME\lib
- C:\Program Files\Tomcat 6.0\webapps\myapp\WEB-INF\lib
I am currently connected to mySQL Server 5.1 using default user name ‘root’ and ‘password ‘sohail’ Currently ‘test’ database is selected
I am running following code
<%@ page language="java" import="java.sql.*" errorPage=""%>
<%@ page import="java.util.*"%>
<html>
<head>
<title>Test DB Page</title>
</head>
<body>
<h3>Insert Data INTO MYSQL</h3>
<%
Connection con = null;
PreparedStatement stmt = null;
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "sohail");
stmt = con.prepareStatement("INSERT INTO login (loginID, firstname, lastname, gender, birthday, country, emailAddress) VALUES ('?','?','?','?','?','?','?')");
stmt.setString(1, request.getParameter("loginID"));
stmt.setString(2, request.getParameter("firstname"));
stmt.setString(3, request.getParameter("lastname"));
stmt.setString(4, request.getParameter("gender"));
stmt.setString(5, request.getParameter("Date of Birth"));
stmt.setString(6, request.getParameter("Country"));
stmt.setString(6, request.getParameter("emailAddress"));
int num = stmt.executeUpdate();
System.out.println(num + "records updated");
stmt.close();
con.close();
}
catch (Exception ex) {
out.println("Unable to connect to batabase.");
开发者_StackOverflow社区 }
finally {
try {
if (stmt != null)
stmt.close();
}
catch (SQLException e) {}
try {
if (con != null)
con.close();
}
catch (SQLException e) {}
}
%>
Done with new DB
</body>
</html>
Can anyone explain why I am not able to connect Database?
Update: I am getting following exception:
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
Thanks you All
The Problem has been resolved by fixing following 2 errors.
1- Dont Quote the parameters i.e. ?
2- Removed duplication i.e. Parameter no 6
Parameter index out of range (1 > number of parameters, which is 0)
This means that preparedStatement.setSomething(1, something) has failed because 1 is larger than the available number of parameters which is 0.
con.prepareStatement("INSERT INTO login (loginID, firstname, lastname, gender, birthday, country, emailAddress) VALUES ('?','?','?','?','?','?','?')"
You should remove those singlequotes. You don't need to include them. The PreparedStatement will take care about the correct way of quoting. The placeholders ? should be used unquoted. So:
con.prepareStatement("INSERT INTO login (loginID, firstname, lastname, gender, birthday, country, emailAddress) VALUES (?,?,?,?,?,?,?)"
See also:
- PreparedStatement tutorial
That said, this is not the recommended way to use JDBC nor JSP. Java code should be avoided in JSP. Also, the CLASSPATH environment variable is ignored by Tomcat.
Don't quote the parameters. Use
INSERT INTO login (loginID, firstname, lastname, gender, birthday, country,
emailAddress) VALUES (?,?,?,?,?,?,?)");
Your code also set parameter no. 6 twice.
This code is hurting my eyes.
Scriptlet code like this is the wrong way to go.
If you must put this in a JSP, you ought to be using JSTL <sql> tags.
But even that's a bad idea. You've got all your connection information in plain text in the page. You aren't using a connection pool or JNDI lookup. You say "test page" - don't let this go to production.
加载中,请稍侯......
精彩评论