CODEWITHSUNDEEP
sqlspringjpahibernate-envers
Roberto
Roberto

Reputation: 1395

How to make method in crudRepository to find any elements using list ids?

There is application on Spring + jpa + jpa Crud repository + hibernate-envers

So, I have a table UserRecord with any fields

        Table UserRecord
id   name  surname  age version

       Table UserRecord_AUD
id   name  surname  age version REV REVINFO

So, when I add new user it writes into UserRecord table. If I change age, and try to write, new version of user writes into UserRecord and old version of user moves into UserRecord_AUD table with same id.

all saved entities have own records in UserRecord_AUD

My task is get all entities from UserRecord_AUD by id's list.

Example

===============================
    Table UserRecord
  11-1  Ivan      Ivanov  23 2
  22-2  Natasha   Ivanova 22 1
===============================
    Table UsersRecord_AUD
  11-1  Ivan      Ivanov  9  0
  11-1  Ivan      Ivanov  9  1
  22-2  Natasha   Ivanova 22 0
===============================

I have repository:

@Repository
public interface UserRepository extends CrudRepository<UserRecord, String> 
{}

I need in custom method, which can find all users from table UserRecord_AUD by id -list

instead call any times

repository.find(id);

I need to call

repository.find(ids);

How to make that:

I tried to:

@Query(value = "select id, name, surname, age, REV as VERSION, IDCALC from USERRECORD_AUD where PR_KEY in (:ids)", nativeQuery = true)
List<UserRecord> findHistoryByIds(List<String> ids);

But there is exception

Name for parameter binding must not be null or empty! On JDKs < 8, you need to use @Param for named parameters, on JDK 8 or better, be sure to compile with -parameters.; nested exception is java.lang.IllegalArgumentException: Name for parameter binding must not be null or empty! On JDKs < 8, you need to use @Param for named parameters, on JDK 8 or better, be sure to compile with -parameters.

My JPA entity is

@Entity
@Audited
@Table(name = "UserRecord")
@NoArgsConstructor(access = AccessLevel.PUBLIC)
@AllArgsConstructor
@Getter
@Setter
@Access(AccessType.FIELD)
public class UserRecord {
    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    @Column(name = "id")
    private String id;
    
    @Column(name = "name", length = 100, unique = false)
    private String name;

    @Column(name = "surname", length = 100, nullable = false)
    private String surname;

    
    @Column(name = "age", length = 100, nullable = false)
    private String age;
    
    @Version
    private int version;

Upvotes: 0

Views: 1303

Answers (1)

Barath
Barath

Reputation: 5283

In case of indexed parameter, use indexes

@Query(value = "select id, name, surname, age, REV as VERSION, IDCALC from USERRECORD_AUD where PR_KEY in (?1)", nativeQuery = true)
List<RiskMetricRecord> findHistoryByIds(List<String> ids);

In case of named parameter, use @Param

@Query(value = "select id, name, surname, age, REV as VERSION, IDCALC from USERRECORD_AUD where PR_KEY in :ids", nativeQuery = true)
List<RiskMetricRecord> findHistoryByIds(@Param("ids") List<String> ids);

Upvotes: 1

Related Questions