主页 / 问题 / 1067686
Accepted
Леонид Дубравский
Asked:2020-01-09 18:45:00 +0000 UTC2020-01-09 18:45:00 +0000 UTC

jdbc4.MySQLSyntaxErrorException：您的 SQL 语法有错误。如何决定？

  • 772

有一个要求： 

 INSERT INTO DocumentWarehouse (`Ref`, `Number`, `DateTime`)
        VALUES
        ('4583b3b4-c53f-11e8-bca5-0025b502b04e', '102-01554356', '2018-10-01T09:00:17')
        ON DUPLICATE KEY UPDATE Ref = VALUES(Ref);
        DELETE FROM DocumentWarehouse_Owner WHERE "Ref" IN
        ("4583b3b4-c53f-11e8-bca5-0025b502b04e");
     INSERT INTO DocumentWarehouse_Owner (`Ref`, `RowNumber`)
        VALUES
        ('4583b3b4-c53f-11e8-bca5-0025b502b04e', '1.0');

在 Intelij Idea 中出现错误：

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 'DELETE FROM DocumentWarehous_OwnerDocuments WHERE "Ref" IN
(' at line 5

控制台运行良好。可能是什么？

PS：这是生成被抱怨的部分代码的方法：

private String getDeleteChildRows(EntityModel entityModel, LinkedList<String> refValues) {
        StringBuilder deleteChildFieldRequest = new StringBuilder();
        StringBuilder deleteChildValues = new StringBuilder();

        for (String value : refValues) {
            deleteChildValues.append(String.format("\"%s\",\n", value));
        }
        deleteChildValues.setLength(deleteChildValues.length() - 2);

        for (String childTableName : entityModel.getTabularSection().keySet()) {
            deleteChildFieldRequest.append(String.format("DELETE FROM %s_%s WHERE \"Ref\" IN\n(%s);\n", entityModel.getName(), childTableName, deleteChildValues));
        }
        return deleteChildFieldRequest.toString();
    }

还：

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.37</version>
    </dependency>


 private BasicDataSource ds;
    private static String databaseUrl;
    private static String user;
    private static String password;
    private static int dbPoolSize;

    public DataSource(String databaseUrl, String user, String password, int dbPoolSize) {
        DataSource.databaseUrl = databaseUrl;
        DataSource.user = user;
        DataSource.password = password;
        DataSource.dbPoolSize = dbPoolSize;
        ds = new BasicDataSource();
        ds.setUsername(user);
        ds.setPassword(password);
        ds.setUrl(databaseUrl);
        ds.setMaxIdle(dbPoolSize);
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setDefaultAutoCommit(false);
        ds.setDefaultTransactionIsolation(TRANSACTION_READ_COMMITTED);
        ds.addConnectionProperty("useUnicode", "yes");
        ds.addConnectionProperty("characterEncoding", "utf8");

 private Statement statement;
statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
statement.setFetchSize(Integer.MIN_VALUE);

public void executeRequest(String request) throws SQLException { statement.execute(request); }

java

1 个回答

  1. Best Answer

    您想要做的 - 通过一个 Statement 推送某个请求元组从定义上来说是一个错误的决定。

    如果您在不同的语句中一一执行您的 DML 操作，您将不会有任何问题。

    如果您仍然确定要走一条曲折的道路，那么对于 MySql，您需要启用一个名为 allowMultiQueries 的特殊设置。这很简单 - 在数据库连接设置中，将此参数添加到 URL，如下所示：

    String mySqlUrl = "jdbc:mysql:///example?allowMultiQueries=true";

    第二个选项是您可以使用查询集创建一个过程并执行它。

    • 1

相关问题