java mysqlsyntaxerrorexception？为什么我的select语句不正确？

tyg4sfes  于 2021-06-17  发布在  Mysql
关注(0)|答案(1)|浏览(294)

这是我为连接到mysql数据库的web应用程序编写的代码。它应该使用另一页上的下拉菜单来显示数据库中一个表的全部内容。我还有另一个.jsp，它用一个执行搜索操作的不同sql语句来做基本上相同的事情，它按预期的方式工作。我想如果有什么问题的话，我可能会在输出html表的过程生成方面遇到问题，但是我得到了一个com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception异常作为mysql语句语法。

<%-- 
    Document   : display
    Created on : Nov 22, 2018, 11:43:54 AM
    Author     : Scott
--%>

<%@page import="java.sql.*" %>
<%
    try {
    Class.forName("com.mysql.jdbc.Driver");
    } catch(ClassNotFoundException e) {
        System.out.println("Unable to load driver class!");
    } 
%>

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Henry Books</title>
    </head>
    <body>
        <h1>Henry Books Store Database Table View </h1>
        <%!
            public class Table {
                String URL = "jdbc:mysql://localhost:3306/HenryBooks?autoReconnect=true&useSSL=false";
                String USERNAME = "root";
                String PASSWORD = "SAvick4078100";

                Connection connection = null;
                PreparedStatement viewTable = null;
                ResultSet resultSet = null;

                public Table(){
                    try{
                        connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

                        viewTable = connection.prepareStatement(
                            "SELECT * FROM ?"
                        );
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

                public ResultSet getTable(String tableSelection) {
                    try{
                        viewTable.setString(1, tableSelection);
                        resultSet = viewTable.executeQuery();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }

                    return resultSet;
                }
            }
        %>

        <%
            String tableSelect = new String();

            //recieves input value from dropdown menu name "table option" in index.jsp
            if(request.getParameter("table option") != null) {
                tableSelect = request.getParameter("table option");
            }

            Table view = new Table();
            ResultSet tableView = view.getTable(tableSelect);
            ResultSetMetaData rsMetaData = tableView.getMetaData();

            int cols = rsMetaData.getColumnCount();
            String[] colNames = new String[cols];
            for(int i = 0; i < cols; i++) {
                colNames[i] = rsMetaData.getColumnName(i+1); //i+1 because the column name index in the ResultSetMetaData begins at 1, not 0
            }
        %>

        <table border="1">
            <thead>
                <tr>
                    <% for(int i = 0; i < cols; i++) { %>
                    <th><%= tableView.getString(colNames[i])%></th>
                    <% } %>
                </tr>
            </thead>
            <tbody>
                <% while(tableView.next()) { %>
                <tr>
                    <% for(int i = 0; i < cols; i++) { %>
                    <td><%= tableView.getString(colNames[i])%></td>
                    <% } %>
                </tr>
                <% } %>
            </tbody>
        </table>
    </body>
</html>

但是，我收到了这些错误消息，我不确定这里的sql语法有什么问题。

Severe:   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Author'' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2011)
    at org.apache.jsp.display_jsp$Table.getTable(display_jsp.java:36)
    at org.apache.jsp.display_jsp._jspService(display_jsp.java:113)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:111)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:411)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:473)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:377)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:318)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
    at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
    at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:416)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:283)
    at com.sun.enterprise.v3.services.impl.ContainerMapper$HttpHandlerCallable.call(ContainerMapper.java:459)
    at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:167)
    at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:206)
    at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:180)
    at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:235)
    at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:283)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:200)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:132)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:111)
    at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
    at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:536)
    at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:112)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:117)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:56)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:137)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:591)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:571)
    at java.lang.Thread.run(Thread.java:748)
Warning:   StandardWrapperValve[jsp]: Servlet.service() for servlet jsp threw exception
java.lang.NullPointerException
    at org.apache.jsp.display_jsp._jspService(display_jsp.java:114)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:111)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:411)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:473)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:377)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:318)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
    at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
    at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:416)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:283)
    at com.sun.enterprise.v3.services.impl.ContainerMapper$HttpHandlerCallable.call(ContainerMapper.java:459)
    at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:167)
    at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:206)
    at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:180)
    at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:235)
    at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:283)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:200)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:132)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:111)
    at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
    at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:536)
    at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:112)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:117)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:56)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:137)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:591)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:571)
    at java.lang.Thread.run(Thread.java:748)
JavamysqlHtmljspweb-applications

1条答案

按热度按时间
smtd7mpg

smtd7mpg1#

使用

"SELECT * FROM ?"

动态添加表名将导致查询被删除

SELECT * FROM 'TABLE_NAME'

而是通过如下方式联系字符串动态追加表名：

"SELECT * FROM "+tableName

其中tablename将是string类型的变量。

赞(0）分享  回复(0）举报  2021-06-18
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com