I am trying to create an independent project regardless of the type of DB using JPQL's @Query .
How do I implement the syntax below in JPA?
SELECT MY_SEQ.NEXTVAL FROM DUAL
For this kind of query, it's more productive to just use nativeQuery = true and use it normally.
To make it database agnostic as you want, you could theoretically just create a DUAL table in the new database.
The short answer is what you are actually looking for. But for the sake of other people who might fall here through Google for other related reasons though, if you really want it, you could theoretically transform Oracle's DUAL table into an entity by using @Subselect like so:
@Table
@Entity
@Subselect("SELECT dummy FROM DUAL")
public class DualEntity {
@Id
@Column(name = "dummy")
private String dummy;
}
Where dummy is the sole column in the DUAL table.
Although this doesn't seem very useful at first glance because most of this kind of queries are much easier to run as native queries, this does let us create repository interfaces for database functions when they aren't related to any actual tables in the database. Example:
@Repository
public interface DualRepository extends JpaRepository<DualEntity, String> {
@Query(nativeQuery = true, value = "SELECT MY_OWNER.MY_FUNCTION(:myParameter) FROM DUAL")
String myFunction(Long myParameter);
}
Which again, this function should probably be part of your code and not part of the database, but older applications tend to have this kind of stuff around. Procedures could also be called through @Query/@Procedure and could also benefit from this kind of mapping.