为什么我在MySQL中可以成功执行,却出现了SQL语法错误?

huangapple go评论77阅读模式
英文:

Why do I get a sql grammar error when I can successfully execute in MySQL

问题

I've translated the non-code portions of your text as you requested. Here's the translated content:

我正在尝试将数据插入多个表格:user_inventories、user_inventory_details 和 user_inventory_images。我正在使用由Spring框架提供的jdbcTemplate。
以下是我的插入方法。

这是控制台输出的ps内容。

这是JDBC给我的错误。

我非常困惑,因为我在MySQL Workbench上键入相同的SQL命令,它成功执行并执行所有插入。但是,当我尝试使用JDBC时,它每次都给我一个SQL语法错误。
非常感谢您的帮助!

这里有三个常量字段:

private final String POST_USER_INVENTORY = "INSERT INTO user_inventories (`id`, `user_id`) VALUES (?, ?); ";
private final String POST_USER_INVENTORY_DETAIL = "INSERT INTO user_inventory_details (`id`, `user_inventory_id`, `vin`, `price`, `mileage`, `color`) VALUES (?, ?, ?, ?, ?, ?); ";
private final String POST_USER_INVENTORY_IMAGES = "INSERT INTO user_inventory_images (`id`, `user_inventory_id`, `image_url`) VALUES (?, ?, ?); ";

还有一个私有辅助方法:

private String preparePostStatementHelper(List<String> images) {
    String postImagesStatement = "";
    for (int i = 0; i < images.size(); i++) {
        postImagesStatement += this.POST_USER_INVENTORY_IMAGES;
    }
    return postImagesStatement;
}
英文:

I am trying to insert data into multiple tables, user_inventories, user_inventory_details, and user_inventory_images. I am using jdbcTemplate provided by Spring framework
Below are my method to insert.

public List&lt;UserListing&gt; postUserListing(String userId, String vin, String price,
      String mileage, String color, List&lt;String&gt; images) {
    Preconditions.checkArgument(Strings.isNotEmpty(userId), &quot;user id cannot be empty&quot;);
    Preconditions.checkArgument(Strings.isNotEmpty(vin), &quot;vin cannot be empty&quot;);
    Preconditions.checkArgument(Strings.isNotEmpty(price), &quot;price cannot be empty&quot;);
    Preconditions.checkArgument(Strings.isNotEmpty(mileage), &quot;mileage cannot be empty&quot;);
    Preconditions.checkArgument(Strings.isNotEmpty(color), &quot;color cannot be empty&quot;);
    Preconditions.checkArgument((!images.isEmpty()), &quot;images cannot be empty&quot;);

    UserListing userListing = this.getSingleUserListingByVin(userId, vin);
    if (userListing != null) {
      throw new InvalidParameterException(
          String.format(&quot;This listing with vin: %s already exist&quot;, vin)
      );
    }

    String postStatement = &quot;START TRANSACTION; &quot; +
        this.POST_USER_INVENTORY +
        this.POST_USER_INVENTORY_DETAIL +
        this.preparePostStatementHelper(images) +
        &quot;COMMIT; &quot;;

    String userInventoryId = UUID.randomUUID().toString();

    jdbcTemplate.update(
        connection -&gt; {
          PreparedStatement ps = connection.prepareStatement(postStatement);
          ps.setString(1, userInventoryId);
          ps.setString(2, userId);
          ps.setString(3, UUID.randomUUID().toString());
          ps.setString(4, userInventoryId);
          ps.setString(5, vin);
          ps.setString(6, price);
          ps.setString(7, mileage);
          ps.setString(8, color);
          for (int i = 0, j = 0; i &lt; images.size(); i++) {
            ps.setString(9 + j++, UUID.randomUUID().toString());
            ps.setString(9 + j++, userInventoryId);
            ps.setString(9 + j++, images.get(i));
          }
          System.out.println(ps.toString());
          return ps;
        }
    );

    return this.getUserListingsByUserId(userId);
  }

And here is what console prints out for the ps.

START TRANSACTION; 
INSERT INTO user_inventories (`id`, `user_id`) VALUES (&#39;db75330e-1da0-455c-850f-22ba08db697e&#39;, &#39;7ff0ca63-11e3-4aa4-b5cf-08d9091901fe&#39;); 
INSERT INTO user_inventory_details (`id`, `user_inventory_id`, `vin`, `price`, `mileage`, `color`) VALUES (&#39;55df5adc-b0c6-4303-9671-1b562fd9a687&#39;, &#39;db75330e-1da0-455c-850f-22ba08db697e&#39;, &#39;2FMPK4K99LBA82434&#39;, &#39;36,454&#39;, &#39;12300&#39;, &#39;silver&#39;); 
INSERT INTO user_inventory_images (`id`, `user_inventory_id`, `image_url`) VALUES (&#39;17bc4717-9967-4402-a87c-c729d29d7b11&#39;, &#39;db75330e-1da0-455c-850f-22ba08db697e&#39;, &#39;https://www.cstatic-images.com/supersized/in/v1/424056/2FMPK4K99LBA82434/4d915c4f3ea38e2d44d879d143277468.jpg&#39;); 
INSERT INTO user_inventory_images (`id`, `user_inventory_id`, `image_url`) VALUES (&#39;bd23ec85-edfe-4a46-b0f7-4608f258a84d&#39;, &#39;db75330e-1da0-455c-850f-22ba08db697e&#39;, &#39;https://www.cstatic-images.com/supersized/in/v1/424056/2FMPK4K99LBA82434/d26beabd124e6638c935739f92c056c2.jpg&#39;); 
INSERT INTO user_inventory_images (`id`, `user_inventory_id`, `image_url`) VALUES (&#39;dc53e3e2-5f4d-4a3c-8cef-3539dee0e873&#39;, &#39;db75330e-1da0-455c-850f-22ba08db697e&#39;, &#39;https://www.cstatic-images.com/supersized/in/v1/424056/2FMPK4K99LBA82434/389ceb910bef8f8255c21d6823b269fe.jpg&#39;); 
INSERT INTO user_inventory_images (`id`, `user_inventory_id`, `image_url`) VALUES (&#39;9b6201f4-8ae8-4483-b33e-3a61ffa2cb90&#39;, &#39;db75330e-1da0-455c-850f-22ba08db697e&#39;, &#39;https://www.cstatic-images.com/supersized/in/v1/424056/2FMPK4K99LBA82434/7d7e014e4dab59f9026837a37fb0a697.jpg&#39;); 
COMMIT;

Here is the error that jdbc gave me

> Exception while fetching data (/postUserListing) : PreparedStatementCallback; bad SQL grammar []; nested exception is 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 'INSERT INTO user_inventories (id, user_id) VALUES ('db75330e-1da0-455c-850f-' at line 1

I am very confused since I typed the same sql command on my mysql workbench, it successfully execute and do all the insertion. However, when I try to use jdbc, it gives me a sql grammar error every time.
Thanks so much for helping!

Here are three constant fields

  private final String POST_USER_INVENTORY = &quot;INSERT INTO user_inventories (`id`, `user_id`) VALUES (?, ?); &quot;;
  private final String POST_USER_INVENTORY_DETAIL = &quot;INSERT INTO user_inventory_details (`id`, `user_inventory_id`, `vin`, `price`, `mileage`, `color`) VALUES (?, ?, ?, ?, ?, ?); &quot;;
  private final String POST_USER_INVENTORY_IMAGES = &quot;INSERT INTO user_inventory_images (`id`, `user_inventory_id`, `image_url`) VALUES (?, ?, ?); &quot;;

And a private helper method

private String preparePostStatementHelper(List&lt;String&gt; images) {
    String postImagesStatement = &quot;&quot;;
    for (int i = 0; i &lt; images.size(); i++) {
      postImagesStatement += this.POST_USER_INVENTORY_IMAGES;
    }
    return postImagesStatement;
}

答案1

得分: 1

您正在将多个语句发送为一个。错误指的是第一个“;”。

不要发送 START TRANSACTION;,而是调用 Connection.setAutoCommit(false);

将每个插入语句单独发送。

不要发送 COMMIT;,而是调用 Connection.commit();,然后调用 Connection.setAutoCommit(true);

英文:

You are sending multiple statements as one. The error refers to the first ';'.

Instead of sending START TRANSACTION;, call Connection.setAutoCommit(false);

Send each insert statement individually.

Instead of sending COMMIT;, call Connection.commit();, then Connection.setAutoCommit(true);

huangapple
  • 本文由 发表于 2020年7月28日 14:39:33
  • 转载请务必保留本文链接:https://go.coder-hub.com/63128348.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定