我正在尝试将数据插入多个表、用户库存、用户库存详细信息和用户库存图像。我使用的是spring框架提供的jdbctemplate,下面是我要插入的方法。
public List<UserListing> postUserListing(String userId, String vin, String price,
String mileage, String color, List<String> images) {
Preconditions.checkArgument(Strings.isNotEmpty(userId), "user id cannot be empty");
Preconditions.checkArgument(Strings.isNotEmpty(vin), "vin cannot be empty");
Preconditions.checkArgument(Strings.isNotEmpty(price), "price cannot be empty");
Preconditions.checkArgument(Strings.isNotEmpty(mileage), "mileage cannot be empty");
Preconditions.checkArgument(Strings.isNotEmpty(color), "color cannot be empty");
Preconditions.checkArgument((!images.isEmpty()), "images cannot be empty");
UserListing userListing = this.getSingleUserListingByVin(userId, vin);
if (userListing != null) {
throw new InvalidParameterException(
String.format("This listing with vin: %s already exist", vin)
);
}
String postStatement = "START TRANSACTION; " +
this.POST_USER_INVENTORY +
this.POST_USER_INVENTORY_DETAIL +
this.preparePostStatementHelper(images) +
"COMMIT; ";
String userInventoryId = UUID.randomUUID().toString();
jdbcTemplate.update(
connection -> {
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 < 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);
}
这是控制台为ps打印的内容。
START TRANSACTION;
INSERT INTO user_inventories (`id`, `user_id`) VALUES ('db75330e-1da0-455c-850f-22ba08db697e', '7ff0ca63-11e3-4aa4-b5cf-08d9091901fe');
INSERT INTO user_inventory_details (`id`, `user_inventory_id`, `vin`, `price`, `mileage`, `color`) VALUES ('55df5adc-b0c6-4303-9671-1b562fd9a687', 'db75330e-1da0-455c-850f-22ba08db697e', '2FMPK4K99LBA82434', '36,454', '12300', 'silver');
INSERT INTO user_inventory_images (`id`, `user_inventory_id`, `image_url`) VALUES ('17bc4717-9967-4402-a87c-c729d29d7b11', 'db75330e-1da0-455c-850f-22ba08db697e', 'https://www.cstatic-images.com/supersized/in/v1/424056/2FMPK4K99LBA82434/4d915c4f3ea38e2d44d879d143277468.jpg');
INSERT INTO user_inventory_images (`id`, `user_inventory_id`, `image_url`) VALUES ('bd23ec85-edfe-4a46-b0f7-4608f258a84d', 'db75330e-1da0-455c-850f-22ba08db697e', 'https://www.cstatic-images.com/supersized/in/v1/424056/2FMPK4K99LBA82434/d26beabd124e6638c935739f92c056c2.jpg');
INSERT INTO user_inventory_images (`id`, `user_inventory_id`, `image_url`) VALUES ('dc53e3e2-5f4d-4a3c-8cef-3539dee0e873', 'db75330e-1da0-455c-850f-22ba08db697e', 'https://www.cstatic-images.com/supersized/in/v1/424056/2FMPK4K99LBA82434/389ceb910bef8f8255c21d6823b269fe.jpg');
INSERT INTO user_inventory_images (`id`, `user_inventory_id`, `image_url`) VALUES ('9b6201f4-8ae8-4483-b33e-3a61ffa2cb90', 'db75330e-1da0-455c-850f-22ba08db697e', 'https://www.cstatic-images.com/supersized/in/v1/424056/2FMPK4K99LBA82434/7d7e014e4dab59f9026837a37fb0a697.jpg');
COMMIT;
这是jdbc给我的错误
获取数据时出现异常(/postureListing):preparedstatementcallback;错误的sql语法[];嵌套异常是com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception:您的sql语法有错误;查看与您的mysql服务器版本对应的手册,以获得使用“insert into user\u”附近的正确语法( id
, user_id
)第1行的值('db75330e-1da0-455c-850f-'
我非常困惑,因为我在mysql工作台上输入了相同的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;
}
1条答案
按热度按时间pu3pd22g1#
您将多个报表作为一个报表发送。错误引用了第一个“;”。
而不是发送
START TRANSACTION;
,呼叫Connection.setAutoCommit(false);
分别发送每个insert语句。而不是发送
COMMIT;
,呼叫Connection.commit();
,那么Connection.setAutoCommit(true);