oracle 如何使用 Spring JdbcTemplate 截断表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2900016/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to truncate a table with Spring JdbcTemplate?
提问by Marcus Leon
I'm trying to truncate a table with Spring:
我正在尝试用 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]
引起：org.springframework.jdbc.BadSqlGrammarException: StatementCallback; 错误的 SQL 语法 [TRUNCATE TABLE RESULT_ACCOUNT]; 嵌套异常是 java.sql.SQLException: Unexpected token: TRUNCATE in statement [TRUNCATE]
Any ideas?
有任何想法吗？
回答by Marcus Leon
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.
这里的问题是您不能在现有事务中执行任何 DDL（例如截断）。原因是 DDL 执行自动提交，它不符合事务概念（即：回滚）。所以我将方法设置为NOT_SUPPORTED，我很好。
回答by Quotidian
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.
我发现 SQLExceptions 并不总是直接指向问题。我会尝试直接在数据库上运行 truncate 查询，看看它是否有效。（您很可能会从数据库本身获得更好的调试信息。）这可能是外键约束违规，需要级联删除调用等。
回答by matt b
Are you sure that the database you are executing this command against supports the TRUNCATE TABLEcommand? Because this error message and exception typecertainly sounds like it does not.
您确定您正在执行此命令的数据库支持该TRUNCATE TABLE命令吗？因为这个错误消息和异常类型听起来肯定不是。
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.
您可能需要查看嵌套异常和堆栈跟踪的其余部分，以确定此消息的来源。
回答by Shravan Ramamurthy
Here the Database allows Truncate .. JDBC template or the Jdbc driver doesn't allow truncate operations
这里 Database 允许 Truncate .. JDBC 模板或 Jdbc 驱动程序不允许 truncate 操作
回答by Parameshwar
@Autowired
private JdbcTemplate jdbcTemplate;
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
public void flushTab(String tab)
{
try {
Map<String, String> obj = new HashMap<String, String>();
String result = null;
String flush_poi_query = "truncate table " + tab + "";
int delete_poi = -1;
delete_poi = getJdbcTemplate().update(flush_poi_query);
if (delete_poi == 0) {
System.out.printtln("@flush_tab : Truncated @poi tab");
} else {
System.out.printtln("@flush_tab : Truncate failed @poi tab");
}
} catch (Exception ex) {
LOGGER.error("Error @flush_tab ", ex);
System.err.println("Error @flush_tab " + ex);
}
}
Works fine by truncating the table pass
通过截断表通行证可以正常工作
回答by Adam Batkin
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.
错误的根本原因是您的 SQL 语句对于您正在与之对话的数据库无效。您可以判断，因为异常是 SQLException（即不是来自 Spring），因此您的 RDBMS 本质上说“我不知道“TRUNCAT TABLE FOO”是什么意思。
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.
您需要阅读数据库系统手册以了解如何截断表。尽管许多主要数据库（无论如何都是最近的版本）似乎都支持TRUNCATE TABLE语句，但听起来您的数据库可能不支持。
Although less efficient, you can also try the query DELETE FROM FOOwhere FOOis the name of your table.
虽然效率较低，但您也可以尝试查询表名DELETE FROM FOO在哪里FOO。
