javajoejuan javajoejuan - 4 months ago 22
Java Question

Getting SQLite to delete all records in a table

I’m trying to delete all records in my

MAIN_TABLE
in my database located in my C:/ drive.. I’m using
SQLite
and I’m not on the
Android
platform. I’ve only seen
TURNCATE
used in a couple of examples so I’m not 100% sure that is right..

CODE:

package Exporter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DeleteAllRecords {
//public void runDeleteAllRecords()throws Exception, SQLException {
public static void main(String[] argv) throws Exception {

String driverName = "org.sqlite.JDBC";
Class.forName(driverName);

String url = ("jdbc:sqlite:" + "C:/Test/DATABASE.db");
Connection connection = DriverManager.getConnection(url);
Statement stmt = connection.createStatement();

String sql = "TRUNCATE MAIN_TABLE";
stmt.executeUpdate(sql);
sql = "DELETE FROM MAIN_TABLE";
stmt.executeUpdate(sql);
}
}


ERRORS:

Exception in thread "main" java.sql.SQLException: near "TRUNCATE": syntax error
at org.sqlite.core.NativeDB.throwex(NativeDB.java:397)
at org.sqlite.core.NativeDB._exec(Native Method)
at org.sqlite.jdbc3.JDBC3Statement.executeUpdate(JDBC3Statement.java:116)
at Exporter.DeleteAllRecords.main(DeleteAllRecords.java:21)

Answer

SQLite does not have an actual TRUNCATE command. Rather it has a truncate optimizer which will be used whenever you run DELETE without a WHERE clause. So you should just use DELETE FROM without a WHERE clause to truncate your table:

String sql = "DELETE FROM MAIN_TABLE";
stmt.executeUpdate(sql);

From the documentation:

The Truncate Optimization

When the WHERE is omitted from a DELETE statement and the table being deleted has no triggers, SQLite uses an optimization to erase the entire table content without having to visit each row of the table individually. This "truncate" optimization makes the delete run much faster.

Comments