How to truncate a table with Spring JdbcTemplate?

Question

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?

Solution 1

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_SUPPORTED and I was good.

Solution 2

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.