Add Envers revision to an existing and Not Audited table

Question

I have a table, populated with an import file. Now I need to declare the related entity AUDIT and We have try to create a procedure for insert the revinfo in the Audit Table, by this way:

Extract from the DB the max REV and max RevTS

 @Query(value="select max(rev) from revinfo",nativeQuery=true)
 int findMaxRev();

 @Query(value="select max(revtstmp) from revinfo",nativeQuery=true)
 Long findMaxrevtstmp();

At this data we add a +1 value, and try to set it in this query:

@Query(value="insert into revinfo (`rev`, `revtstmp`) values (:rev, :revtstmp)", nativeQuery=true)
        void addRevInfo(@Param("rev") int rev, @Param("revtstmp")Long revtstmp);

@Query(value="insert into entity_h (id, audit_revision, action_type, audit_revision_end, audit_revision_end_ts ) "
    + "values (:id, :rev, 0, null, '2017-08-31 10:45:37')", nativeQuery=true)
    void addEnvers(@Param("id")long id, @Param("rev")int rev);

But when we run the addRevInfo query, We obtain this error:

`ERROR: org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Can not issue data manipulation statements with executeQuery()`.

If we run this same query directly in MySQLWorkbench, the insert run without problem.

What's wrong?

Answer 1

Try to add this to you insert query:

@Modifying(clearAutomatically = true)
@Query("....")

With that the EntityManager flush your changes otherwise not, and it permit to use the executeUpdate() instead of exectueQuery() works for

the SQL statement, which returns a single ResultSet object

The executeUpdate instead is

for SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.