如何使用 Spring JdbcTemplate 截断表？
我试图用 Spring 截断表：
jdbcTemplate.execute("TRUNCATE TABLE " + table);
出现错误：
引起：org.springframework.jdbc.BadSqlGrammarException： 声明回调； SQL 语法错误 [截断表结果帐户]； 嵌套异常是 java.sql.SQLException：意外 标记：语句中的 TRUNCATE [截断]
有什么想法吗？
I'm trying to truncate a table with Spring:
jdbcTemplate.execute("TRUNCATE TABLE " + table);
Get the error:
Caused by: org.springframework.jdbc.BadSqlGrammarException:
StatementCallback; bad SQL grammar
[TRUNCATE TABLE RESULT_ACCOUNT];
nested exception is
java.sql.SQLException: Unexpected
token: TRUNCATE in statement
[TRUNCATE]
Any ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
这里的问题是您无法在现有事务中执行任何 DDL（例如截断）。原因是 DDL 执行自动提交，这与事务概念（即：回滚）不相符。所以我将方法设置为
NOT_SUPPORTED并且效果很好。Issue here was you can't do any DDL (such as truncate) within an existing transaction. Reason being that DDL does an auto commit which doesn't jive with transactional concepts (ie: rollback). So I set the method to
NOT_SUPPORTEDand I was good.我发现 SQLException 并不总是直接指向问题。我会尝试直接在数据库上运行截断查询，看看它是否有效。 （您很可能会从数据库本身获得更好的调试信息。）这可能是外键约束违规，需要级联删除调用等。
I've found that the SQLExceptions don't always point directly to a problem. I'd try running the truncate query directly on the database and see if it works. (You'll get much better debug info from the database itself most likely.) It could be a foreign key constraint violation, the need for a cascade drop call, etc.
您确定要执行此命令的数据库支持 TRUNCATE TABLE 命令吗？因为这个错误消息和异常type 听起来确实不像。
您可能需要查看嵌套异常和堆栈跟踪的其余部分，以确定此消息来自何处。
Are you sure that the database you are executing this command against supports the
TRUNCATE TABLEcommand? Because this error message and exception type certainly sounds like it does not.You might want to take a look at the nested exception and the rest of your stacktrace to see for sure where this message comes from.
这里数据库允许截断.. JDBC模板或Jdbc驱动程序不允许截断操作
Here the Database allows Truncate .. JDBC template or the Jdbc driver doesn't allow truncate operations
错误的根本原因是您的 SQL 语句对于您正在使用的数据库无效。你可以看出，因为异常是一个 SQLException（即不是来自 Spring），所以你的 RDBMS 本质上是在说“我不知道“TRUNCAT TABLE FOO”的意思。
你需要阅读数据库系统的手册来了解如何尽管许多主要数据库（至少是最新版本）似乎支持 TRUNCATE TABLE 语句，但听起来您的数据库可能不支持，
尽管效率较低，但您也可以尝试查询 DELETE FROM FOO。 其中
FOO是表的名称。The root cause of the error is that your SQL statement is invalid for the database that you are talking to. You can tell because the exception is an SQLException (i.e. not from Spring), so it your RDBMS essentially saying "I don't know that "TRUNCAT TABLE FOO" means.
You will need to read your database system's manual to find out how to truncate your table. Although many major databases (recent versions anyway) appear to support
TRUNCATE TABLEstatements, it sounds like yours may not.Although less efficient, you can also try the query
DELETE FROM FOOwhereFOOis the name of your table.