Easy Tomcat 7 连接 MySQL 数据库时出现奇怪错误

Easy Tomcat 7 连接 MySQL 数据库时出现奇怪错误

我对 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 数据库,默认情况下,引用表名时不会考虑大小写。不过,还是感谢您的意见。

相关内容