在mysql中创建表时的SQLException

Question

public void table(String name, String col1, String type1, String null1, String key) throws SQLException {

    String sql = "CREATE TABLE IF NOT EXISTS ? (id INT NOT NULL AUTO_INCREMENT, ? ? ? , PRIMARY KEY (?))";

    PreparedStatement stmt = conn.prepareStatement(sql);
    stmt.setString(1, "`myDb`."+name);
    stmt.setString(2, col1);
    stmt.setString(3, type1);
    stmt.setString(4, null1);
    stmt.setString(5, key);

    stmt.executeUpdate();
}

Servlet controller.java：

if (action.equals("createTable")) {
    String name = request.getParameter("name");
    String null3 = request.getParameter("null1");
    String type3 = request.getParameter("type1");
    String key = request.getParameter("key");
    CreateTable createTable = new CreateTable(conn1);
    try {
        if (col2 == "" && col3 == "" && col4 == "" && col5 == "" && col6 == "" && col7 == ""    && col8 == "" && col9 == "" && col10 == "" && col11 == "" && col12 == "") {
            createTable.table(name, col1, type1, null1, key);
            request.getRequestDispatcher("/memberpanel.jsp").forward(request, response);
            // request.setAttribute("message", "");
            response.setContentType("text/html");
            out.println("<script type=\"text/javascript\">");
            out.println("window.alert('Your table created successfully')");
            out.write("setTimeout(function(){window.location.href='memberpanel.jsp'},1000);");
            out.write("setTimeout(function(){window.location.href='Index.jsp'},1000);");
            out.println("</script>");
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        e.getMessage();
    }

执行时，我收到以下信息：

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 ''`myDb`.admin' (id INT NOT NULL AUTO_INCREMENT, 'rescue' 'VARCHAR(750)' 'NOT NUL' 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:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2683)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2144)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2444)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2362)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2347)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:97)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:97)
    at queries.CreateTable.table(CreateTable.java:478)
    at controller.Controller.doPost(Controller.java:504)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:522)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1095)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:672)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1502)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1458)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)

myDb是我的数据库。admin、rescue、not null、varchar(750)、rescue是表名、COL的测试值。名称、空值或非空值、数据类型和主键。

Answer 1

不能对“非值”占位符使用这样的查询参数。

相反，在Java中将查询构建为单个字符串，然后执行该字符串。

您可以使用string.format()获得类似的内容：

String query = String.format("CREATE TABLE IF NOT EXISTS %s (id INT NOT NULL AUTO_INCREMENT, %s %s %s , PRIMARY KEY (%s))", "`myDb`."+name, col1, type1, null1, key);