☰

透過JSP在mysql表中建立表格

tag-icon • tag-icon java mysql
透過JSP在mysql表中建立表格

我正在建立一個程式碼，其中將建立一個 mysql 表，該表的名稱將由使用者提供。使用者將輸入各個數據，程式碼將連接所有數據，並以「*」分隔所有數據，並使用輸出的名稱建立表格。但它顯示錯誤

OVS$5PKT Lenny$Dk Blue com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Blue (SlNo INTEGER, ProcessName VARCHAR(25), Water INTEGER, ChemicalName V...' at line 1

如果我在一個欄位中輸入單一資料並且不將其連接起來，則程式碼可以正常運作。但當使用者在多個欄位中輸入資料並將其與分隔符號連接時，它會引發錯誤。

這是代碼 -

<%@ page import = "java.sql.*"%>
<%
    java.lang.String createDate = request.getParameter("createDate");
    java.lang.String origin = request.getParameter("origin");
    java.lang.String factoryName = request.getParameter("factoryName");
    java.lang.String buyerName = request.getParameter("buyerName");
    java.lang.String styleNumber = request.getParameter("styleNumber");
    java.lang.String poNumber = request.getParameter("poNumber");
    java.lang.String colorName = request.getParameter("colorName");
    java.lang.String washType = request.getParameter("washType");

    String conHelper = buyerName + "$" + styleNumber + "$" + colorName;

    out.println(conHelper);

    String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    String DB_USER = "root";
    String DB_PASS = "";

    int originLength = origin.length();
    
    try{

        if(originLength == 7){

            String DB_URL = "jdbc:mysql://localhost:3306/erp-pre-cost(in-house)";
            Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
            Statement stmt = conn.createStatement();

            String createTable = "CREATE TABLE IF NOT EXISTS " + conHelper +
                       " (SlNo INTEGER, " + 
                       " ProcessName VARCHAR(25), " + 
                       " Water INTEGER, " + 
                       " ChemicalName VARCHAR(50), " + 
                       " gL FLOAT, " + 
                       " Doages INTEGER, " + 
                       " UOM ENUM('Kg', 'gm'), " +
                       " MachineRPM INTEGER, " +  
                       " pH FLOAT, " + 
                       " Time INTEGER, " + 
                       " Remarks VARCHAR(30))";

             stmt.executeUpdate(createTable);
             out.println("Success!");

        }else if(originLength == 11){

            String DB_URL = "jdbc:mysql://localhost:3306/erp-pre-cost(sub-contract)";
            Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
            Statement stmt = conn.createStatement();

            String createTable = "CREATE TABLE IF NOT EXISTS " + conHelper +
                       " (SlNo INTEGER, " + 
                       " ProcessName VARCHAR(25), " + 
                       " Water INTEGER, " + 
                       " ChemicalName VARCHAR(50), " + 
                       " gL FLOAT, " + 
                       " Doages INTEGER, " + 
                       " UOM ENUM('Kg', 'gm'), " +
                       " MachineRPM INTEGER, " +  
                       " pH FLOAT, " + 
                       " Time INTEGER, " + 
                       " Remarks VARCHAR(30))";

             stmt.executeUpdate(createTable);
             out.println("Success!");

        }else{
            out.println("Failed!");
        }

    }catch(Exception e){
        out.println(e);
    }

%>

請幫忙！

答案1

如果表名中有空格，則應使用 ` 字元將表名括起來，例如

CREATE TABLE IF NOT EXISTS `OVS$5PKT Lenny$Dk Blue`
    (SlNo INTEGER,
    /*...*/
    Remarks VARCHAR(30));

相關內容