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?

Answer 1

Are you sure that the database you are executing this command against supports the TRUNCATE TABLE command? 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.

Answer 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.

Answer 3

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 TABLE statements, it sounds like yours may not.

Although less efficient, you can also try the query DELETE FROM FOO where FOO is the name of your table.

Answer 4

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.