0

Let's say I have a database table:

ID     | DESCRIPTION
1      | Some value '123' added
2      | Deleted value '333' by 'Someone'

Jpa entity:

@Entity
@Table(name = "LOG")
public class Log {

    @Id
    @Column(name = "ID")
    private Long id;

    @Column(name = "DESCRIPTION")
    private String description;
    ...
}

And working jpa query to retrieve first found numeric value inside single quotas:

    @Query(value = "select distinct REGEXP_SUBSTR( log.DESCRIPTION, '''(\\d+)''',1,1,'',1) from Log log where 1=1 ", nativeQuery = true)
    List<String> findObjectIdsInDescription();

I'm working on Oracle database hence REGEXP_SUBSTR + native query, which is working fine for me right now.

Is there a database independent approach using jpa query only? I'm asking as I also need it for different databases.

makozaki
  • 3,772
  • 4
  • 23
  • 47
  • Would it make sense to have a custom getter `Log.getIdsInDescription` where you parse the description in java yourself? Therefore, you would pull the log, then call your getter. Or do you need to extract the ids from all the logs? – Emil Terman Feb 23 '23 at 09:41
  • I was hoping to create a solution where database handles it and returns only necessary values. But if nothing works then ofc retrieving whole content of column and parsing/filtering it in java would work. – makozaki Feb 23 '23 at 10:34
  • I'm not sure. But can u check if this helps https://stackoverflow.com/a/44738213/2353403 – Coder Feb 23 '23 at 12:05
  • I'm getting to conclusion that string manipulation in scope of database query is dependent on database hence only native queries can solve it. – makozaki Feb 24 '23 at 09:33

0 Answers0