CODEWITHSUNDEEP
springpostgresqlhibernatejpaspring-data-jpa
fugasjunior
fugasjunior

Reputation: 461

Can't truncate PostgreSQL tables with JPA

I'm trying to quickly delete all data from multiple tables using Spring Data JPA. From what I've read, the fastest way to do this is to truncate the tables.

My code looks like this:

@Service
public class DatabaseService {

    ... autowiring ...

    @Transactional
    public void deleteRepository(){
        repository.truncate();
    }
}

@Repository
public interface repository extends JpaRepository<Trip, Long> {

    @Modifying
    @Query(value = "truncate table my_table",
            nativeQuery = true)
    void truncate();
}

However, when I call the deleteRepository() method, I get the following exception:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'databasePopulatorJob': Invocation of init method failed; nested exception is org.springframework.transaction.TransactionSystemException: Could not roll back JPA transaction; nested exception is org.hibernate.TransactionException: Unable to rollback against JDBC Connection
...
Caused by: org.springframework.transaction.TransactionSystemException: Could not roll back JPA transaction; nested exception is org.hibernate.TransactionException: Unable to rollback against JDBC Connection
...
Caused by: org.hibernate.TransactionException: Unable to rollback against JDBC Connection
...
Caused by: java.sql.SQLException: Connection is closed
...

I'm using latest PostgreSQL database with JDBC driver version 42.2.5.

I've also tried other methods to delete the data like DELETE FROM my_table (my_table contains about 2 million records), but it takes way too long. I'd appretiate any tips.

Upvotes: 1

Views: 1853

Answers (1)

Sham Fiorin
Sham Fiorin

Reputation: 531

If you have relationship between the tables, you wont be able to TRUNCATE the tables, because is Data Integrit Violation Exception, if you are using the database H2, have i kind of trick thing you can do:

SET REFERENTIAL_INTEGRITY FALSE;

TRUNCATE TABLE tbtable;

SET REFERENTIAL_INTEGRITY TRUE;

.sql

Upvotes: 2

Related Questions