我对 Java servlet 和 Web 应用程序开发还很陌生,但这很令人困惑。我在 Eclipse (Mars) 中构建了一个项目,该项目主要由静态 HTML 页面组成,其中一个 JSP 包含一个向 servlet 执行 POST 的表单。此 servlet 只会从请求中获取参数并将一行插入 MySQL 数据库的表中。以下是 servlet 代码:
package boyd.apps.k9;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.*;
import javax.mail.*;
import javax.mail.internet.*;
import javax.activation.*;
/**
* Servlet implementation class ContactUs
*/
@WebServlet("/ContactUs")
public class ContactUs extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public ContactUs() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
String requestType = request.getParameter("requestType");
String name = request.getParameter("name").toUpperCase();
String company = request.getParameter("company").toUpperCase();
String address = request.getParameter("address").toUpperCase();
String city = request.getParameter("city").toUpperCase();
String state = request.getParameter("state").toUpperCase();
String zip = request.getParameter("zip");
String phone = request.getParameter("phone");
String email = request.getParameter("email").toUpperCase();
String comments = request.getParameter("comments");
String hrMin = request.getParameter("hr-min");
String amPm = request.getParameter("am-pm");
String timeToCall = hrMin + amPm;
String sqlstr = null;
Connection conn = null;
Statement stmt = null;
String dbuser = this.getServletContext().getInitParameter("dbuser");
String dbpwd = this.getServletContext().getInitParameter("dbpwd");
String url = this.getServletContext().getInitParameter("dburl");
try
{
Class.forName("com.mysql.jdbc.Driver");
}
catch (ClassNotFoundException e) {
System.out.println("MySQL JDBC Driver not found");
return;
}
try {
//conn = ds.getConnection(dbuser, dbpwd, url);
conn = DriverManager.getConnection(url, dbuser, dbpwd);
} catch (SQLException se) {
System.out.println("Error connecting to database in ContactUs");
se.printStackTrace();
System.exit(1);
}
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
sqlstr = "INSERT INTO LEADS (REQUEST_TYPE, NAME, COMPANY, ADDRESS, CITY, STATE, ZIP, PHONE, TIME_TO_CALL, EMAIL, COMMENTS) VALUES " +
"('" + requestType + "','" + name + "','" + company + "','" + address + "','" + city + "','" + state + "','" + zip +
"','" + phone + "','" + timeToCall + "','" + email + "','" + comments + "')";
stmt.executeUpdate(sqlstr);
} catch (SQLException se) {
System.out.println("Error inserting into LEADS");
se.printStackTrace();
System.exit(1);
}
RequestDispatcher view = null;
view = request.getRequestDispatcher("index.html");
view.forward(request, response);
}
}
如您所见,数据库连接信息来自 web.xml 文件,作为 Servlet 上下文参数(dbuser:;k9_user
密码:xxxx
;url:)jdbc:mysql://localhost:3306/k9
。因此,MySQL 数据库名称为k9
,并且只设置了一个表(LEADS)。作为 MySQL root,为了use k9;
切换到 k9 数据库,我已将 LEADS 上的所有权限授予k9_user
。整个配置在我的本地机器上运行良好,指向本地 MySQL,并通过 Eclipse 上的 Apache Tomcat 8 运行。
然后,我将这个应用程序部署到主机站点的 VM,以完全相同的方式设置 MySQL 数据库,然后打开浏览器运行该应用程序。我导航到入口页面并提交表单,应用程序不仅抛出了下面的错误,而且还使整个 Easy Tomcat 7 应用服务器崩溃(这本身就很奇怪,因为似乎只是出现了“未找到表”错误)。问题是,在该主机服务器上,从命令行,我可以以该用户的身份连接到 k9 MySQL 数据库,并执行“describe leads;”并在其中插入一行。所以,更令人费解的是,这是一个令人费解的问题。无论如何,Tomcat 日志中的错误堆栈如下:
Nov 05, 2015 9:04:30 AM org.apache.catalina.core.ApplicationContext log
INFO: HTMLManager: list: Listing contexts for virtual host 'localhost'
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'k9.LEADS' doesn't exist
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1402)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1317)
at boyd.apps.k9.ContactUs.doPost(ContactUs.java:103)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Service exit with a return value of 1
如果有人遇到过这种不一致的行为,或者在通过 Java 代码使用 MySQL 时看到过类似的错误消息,你能帮帮忙吗?
答案1
在 MySQL 或 JDBC 级别启用完整日志记录,以捕获应用程序和 MySQL 之间的所有查询。检查 k9.LEADS 是否可能被删除且未重新创建。还要仔细检查所有连接是否都转到实际具有 k9.LEADS 的正确服务器。
答案2
我做了进一步的挖掘,发现失败的原因是 MySQL 中的表名区分大小写;因此,它不是确实找到了。我以前使用过 Oracle 数据库,默认情况下,引用表名时不会考虑大小写。不过,还是感谢您的意见。