Cannot use delete() on CrudRepository

Question

I'm using the latest spring (4.0.4) and Hibernate (4.3.5) and spring-data-jpa (1.6.0) for the first time and am having trouble getting the repository to work on a MySQL table to write or delete data. It's reading data fine, but when I try to delete, nothing happens. Things work fine if I use an H2 database, but when I switch my data source to be a MySQL server, delete() stops working.

Question 1: Why isn't the CrudRepository sub-class able to delete rows from my table entity when I use a MySQL data source, but it works with the same code if I use an H2 data source?

I can delete data if I create functions like this in my sub-class of CrudRepository:

public interface MyEntityRepository extends CrudRepository<MyEntity, Long> {
    @Modifying
    @Query("delete from MyEntity where entity_id = ?1")
    void delete(Long entityId);

    @Modifying
    @Query("delete from StageTeacher")
    void deleteAll();
}

I am hoping I'm missing something simple. But in my unit test class, I've got this autowired repository reference:

@Autowired
MyEntityRepository myEntityRepository;

When I'm using the MySQL data source these commands do nothing (they don't even case a run-time error):

myEntityRepository.deleteAll();
myEntityRepository.delete(myEntity.getId());

Here's the 2 data sources (H2 is commented out) and the entity manager factory I create using this code:

@Bean
public EntityManagerFactory entityManagerFactory() throws SQLException {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://<host name>:3306/<schema name>");
    dataSource.setUsername("<username>");
    dataSource.setPassword("<password>");

    /*
    EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
    DriverManagerDataSource dataSource = builder.setType(EmbeddedDatabaseType.H2).build();
    */

    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    // used when I have H2 enabled
    //vendorAdapter.setGenerateDdl(true);

    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
    factory.setJpaVendorAdapter(vendorAdapter);

    Properties properties = new Properties();
    properties.setProperty("hibernate.show_sql", "true");
    properties.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQLMyISAMDialect");
    factory.setJpaProperties(properties);

    factory.setPackagesToScan("<package with table entity classes>");
    factory.setDataSource(dataSource);
    factory.afterPropertiesSet();

    return factory.getObject();
}

EDIT: I've added the following to my log4j.xml:

<logger name="org.hibernate">
    <level value="DEBUG"/>
</logger>
<logger name="org.springframework.data">
    <level value="DEBUG"/>
</logger>

I get this in the console when I have my methods un-commented in the Repository sub-class:

DEBUG [main] IntegrationTests.delete(54) | >>>>>>>>>> delete all??
DEBUG [main] AbstractTransactionImpl.begin(160) | begin
DEBUG [main] LogicalConnectionImpl.obtainConnection(226) | Obtaining JDBC connection
DEBUG [main] LogicalConnectionImpl.obtainConnection(232) | Obtained JDBC connection
DEBUG [main] JdbcTransaction.doBegin(69) | initial autocommit status: true
DEBUG [main] JdbcTransaction.doBegin(71) | disabling autocommit
Hibernate: delete from stage_teacher
DEBUG [main] AbstractTransactionImpl.commit(175) | committing
DEBUG [main] JdbcTransaction.doCommit(113) | committed JDBC Connection
DEBUG [main] JdbcTransaction.releaseManagedConnection(126) | re-enabling autocommit
DEBUG [main] LogicalConnectionImpl.releaseConnection(246) | Releasing JDBC connection
DEBUG [main] LogicalConnectionImpl.releaseConnection(264) | Released JDBC connection

and the delete succeeds!

However, if I comment the repository sub-class methods out, I'll get this in the console:

DEBUG [main] IntegrationTests.delete(54) | >>>>>>>>>> delete??
DEBUG [main] AbstractTransactionImpl.begin(160) | begin
DEBUG [main] LogicalConnectionImpl.obtainConnection(226) | Obtaining JDBC connection
DEBUG [main] LogicalConnectionImpl.obtainConnection(232) | Obtained JDBC connection
DEBUG [main] JdbcTransaction.doBegin(69) | initial autocommit status: true
DEBUG [main] JdbcTransaction.doBegin(71) | disabling autocommit
Hibernate: select stageteach0_.entity_id as entity_i1_0_0_, stageteach0_.active as active2_0_0_, stageteach0_.alias as alias3_0_0_, stageteach0_.allow_marketing_emails as allow_ma4_0_0_, stageteach0_.allow_password_resets as allow_pa5_0_0_, stageteach0_.console_setting_id as console_6_0_0_, stageteach0_.date_created as date_cre7_0_0_, stageteach0_.date_deactivated as date_dea8_0_0_, stageteach0_.date_modified as date_mod9_0_0_, stageteach0_.default_role_id as default10_0_0_, stageteach0_.district_teacher_id as distric11_0_0_, stageteach0_.email_address as email_a12_0_0_, stageteach0_.first_name as first_n13_0_0_, stageteach0_.first_name_localized as first_n14_0_0_, stageteach0_.iid as iid15_0_0_, stageteach0_.last_name as last_na16_0_0_, stageteach0_.last_name_localized as last_na17_0_0_, stageteach0_.main_teacher_id as main_te18_0_0_, stageteach0_.password as passwor19_0_0_, stageteach0_.pref_language_id as pref_la20_0_0_, stageteach0_.rest_id as rest_id21_0_0_, stageteach0_.salutation_id as salutat22_0_0_, stageteach0_.school_teacher_id as school_23_0_0_, stageteach0_.status_id as status_24_0_0_, stageteach0_.tcd as tcd25_0_0_, stageteach0_.teacher_type_id as teacher26_0_0_, stageteach0_.username as usernam27_0_0_ from stage_teacher stageteach0_ where stageteach0_.entity_id=?
DEBUG [main] ResultSetProcessorImpl.extractResults(127) | Starting ResultSet row #0
DEBUG [main] EntityReferenceInitializerImpl.resolveEntityKey(142) | On call to EntityIdentifierReaderImpl#resolve, EntityKey was already known; should only happen on root returns with an optional identifier specified
DEBUG [main] TwoPhaseLoad.doInitializeEntity(160) | Resolving associations for [org.mind.gen40.domain.gen40.StageTeacher#10956]
DEBUG [main] TwoPhaseLoad.doInitializeEntity(286) | Done materializing entity [org.mind.gen40.domain.gen40.StageTeacher#10956]
DEBUG [main] AbstractLoadPlanBasedEntityLoader.load(208) | Done entity load : org.mind.gen40.domain.gen40.StageTeacher#10956
DEBUG [main] AbstractTransactionImpl.commit(175) | committing
DEBUG [main] JdbcTransaction.doCommit(113) | committed JDBC Connection
DEBUG [main] JdbcTransaction.releaseManagedConnection(126) | re-enabling autocommit
DEBUG [main] LogicalConnectionImpl.releaseConnection(246) | Releasing JDBC connection
DEBUG [main] LogicalConnectionImpl.releaseConnection(264) | Released JDBC connection

Does the failure to delete have something to do with this message?

On call to EntityIdentifierReaderImpl#resolve, EntityKey was already known; should only happen on root returns with an optional identifier specified

I'm not sure what that means...

Question 2: Do I have to sub-class CrudRepository for each table that I need basic CRUD operations on, or can I use a reference to the table entity class to create a CrudRepository at run-time for a given table?

Question 3: If I need to manually create my delete and insert methods on a large number of CrudRepository sub-classes, are there any suggestions on generating table entities and DAO or repository classes given tables in MySQL?

What does "stops working" mean specifically? Are you getting any exceptions? Turn your Hibernate SQL logging on (DEBUG level) and post the actual SQL queries it's issuing. — chrylis -cautiouslyoptimistic-
– chrylis -cautiouslyoptimistic-, Commented May 23, 2014 at 22:54
When I do not have the delete() and deleteAll() methods (each with the Modifying and Query annotations as shown above) in the CrudRepository sub-class, I am getting no exceptions. Without my methods in the repository sub-class, when I run my unit tests, the SQL never runs to delete rows at all, but there is no exception or failure either. — webhammer
– webhammer, Commented May 23, 2014 at 23:01
Crank up the logging. Something somewhere will explain why it's not continuing. — chrylis -cautiouslyoptimistic-
– chrylis -cautiouslyoptimistic-, Commented May 23, 2014 at 23:02
What logging system are you using? How are you launching the application? — chrylis -cautiouslyoptimistic-
– chrylis -cautiouslyoptimistic-, Commented May 23, 2014 at 23:08
I got it logging a ton of information by putting this into my log4j.xml: <logger name="org.hibernate"> <level value="DEBUG"/> </logger> ... thanks! I'm trying to sort thru this to see the logging that only relates to the delete attempt. — webhammer
– webhammer, Commented May 23, 2014 at 23:13

webhammer · Accepted Answer · 2014-05-27 15:10:16Z

I've discovered that the problem was rooted in the fact that I was running my code using a unit test. Apparently unit tests are setup as transactions that are automatically rolled back, but I can't find documentation to support that. My unit test was creating a row in a table and then deleting the row based on it's ID. It was always failing because it was always rolling back the transaction.

If you need unit tests that actually do a delete then add the rollback(false) annotation so your unit test method looks like this:

@Autowired
MyEntityRepository myEntityRepository;

@Test
@Rollback(false)
public void createAndThenDeleteRow() {
    MyEntity testRecord = new TestRecord( "fake", "data" );
    TestRecord savedRecord = myEntityRepository.save( testRecord );
    Long id = savedRecord.getId();
    TestRecord loadedRecord = myEntityRepository.findOne( id );
    assertNotNull( loadedRecord );
    myEntityRepository.delete( id );
    TestRecord reloadedRecord = myEntityRepository.findOne( id );
    assertNull( reloadedRecord );
}

Collectives™ on Stack Overflow

Cannot use delete() on CrudRepository

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related