标题翻译
Error in SQL syntax when running SQL in Java but not in HeidiSQL
问题
Statement statement = con.createStatement();
String escapedXML = EscapeString(billboard.getXml());
String sql = String.format(
"DELIMITER $ \r\nBEGIN NOT ATOMIC\r\n" +
"IF EXISTS(SELECT * FROM billboards where Name='%s') THEN UPDATE billboards SET XML='%s' where Name='%s';\r\n" +
"ELSE insert into billboards(Name, XML, CreatorName) values('%s', '%s', '%s');\r\n" +
"END IF;\r\n" +
"END $\r\n" +
"DELIMITER ;", billboard.getName(), escapedXML, billboard.getName(), billboard.getName(), escapedXML, billboard.getCreatorName());
// Insert or update billboard
statement.execute(sql);
英文翻译
I'm trying to run an insert or update on a table - the string generated from below works fine when copy pasted into HeidiSQL but throws SQLSyntaxErrorExceptions when run from Java:
Statement statement = con.createStatement();
String escapedXML = EscapeString(billboard.getXml());
String sql = String.format(
"DELIMITER $ \r\nBEGIN NOT ATOMIC\r\n" +
"IF EXISTS(SELECT * FROM billboards where Name='%s') THEN UPDATE billboards SET XML='%s' where Name='%s';\r\n" +
"ELSE insert into billboards(Name, XML, CreatorName) values('%s', '%s', '%s');\r\n" +
"END IF;\r\n" +
"END $\r\n" +
"DELIMITER ;", billboard.getName(), escapedXML, billboard.getName(), billboard.getName(), escapedXML, billboard.getCreatorName());
// Insert or update billboard
statement.execute(sql);
I can't figure out why.
答案1
得分: 0
我建议在这里使用insert ... ok duplicate key语法，而不是使用代码块。这样更高效，并且在单个语句中实现了锁定，这应该可以避免您在从您的PHP代码运行查询时遇到的问题。
insert into billboards(Name, XML, CreatorName)
values(?, ?, ?)
on duplicate key update set XML = values(XML)
为了使这个工作正常，您需要在列Name上设置一个唯一（或主键）约束。
另外，考虑使用参数化查询，而不是将变量连接到您的查询字符串中。转义是低效的，而且并不能真正使您的代码更安全。
英文翻译
I would recommend using the insert ... ok duplicate key syntax here rather than a code block. This is more efficient, and implements the lockout a single statement, which should avoid the problem you meet when running the query from your php code.
insert into billboards(Name, XML, CreatorName)
values(?, ?, ?)
on duplicate key update set XML = values(XML)
For this to work, you need a unique (or primary key) constraint on column Name.
Also, consider using a parameterized query rather than concatenating variables in your query stringW Escaping is inefficient and does not really make your code safer.
答案2
得分: 0
你应该尝试使用NamedParameterStatement来处理查询，以便于设置字符串参数并避免它们的重复（使用在GMB之前的回答中建议的重构查询）：
String sql = "INSERT INTO billboards (Name, XML, CreatorName) VALUES (:name, :xml, :creator) "
+ "ON DUPLICATE KEY UPDATE XML = :xml";
NamedParameterStatement statement = new NamedParameterStatement(con, sql);
statement.setString("name", billboard.getName());
statement.setString("xml", EscapeString(billboard.getXml()));
statement.setString("creator", billboard.getCreatorName());
// 插入或更新广告牌
statement.execute(sql);
英文翻译
You should have tried NamedParameterStatement with your query to facilitate setting of string parameters and avoid their duplication (using refactored query suggested in GMB's earlier answer):
String sql = "INSERT INTO billboards (Name, XML, CreatorName) VALUES (:name, :xml, :creator) "
+ "ON DUPLICATE KEY UPDATE SET XML = :xml";
NamedParameterStatement statement = new NamedParameterStatement(con, sql);
statement.setString("name", billboard.getName());
statement.setString("xml", EscapeString(billboard.getXml()));
statement.setString("creator", billboard.getCreatorName());
// Insert or update billboard
statement.execute(sql);
答案3
得分: 0
你之所以得到语法错误是因为DELIMITER是MySQL客户端命令，而不是SQL语句。在JDBC中不能使用MySQL命令。
更多信息：
英文翻译
The reason that you are getting a syntax error is that DELIMITER is a MySQL client command and not an SQL statement. MySQL commands may not be used in with JDBC.
For more information:
专注分享java语言的经验与见解，让所有开发者获益！
评论