Earlier I was working on a Spring Boot application where in I had to join multiple tables and map the returned columns to a JPA entity. But the problem was that none of the returned columns had unique values.
Here’s a snippet of how my JpaRepository looks like (I put … to hide the parts of the query that are not relevant to this post)
@Repository public interface UserJpaRepository extends JpaRepository { @Query(value="SELECT a.date_id as date_id... "FROM user a, address b, account c " + "WHERE ... a.id=?1",nativeQuery = true) List<User> getUserById(Long id); }
But the way these three tables (user, address, account) are setup and the way the query is
structured, none of the columns returned had unique values in them. So I cannot annotate
any attribute in the User Entity with @id annotation. The getUserId(id) would always
return erroneous records. One approachto solve this is by adding newId() to be returned
as a column which is a randomly generated value for each returned row.
@Repository
public interface UserJpaRepository extends JpaRepository {
@Query(value="SELECT
newId() AS col_id a.date_id as date_id...
"FROM user a, address b, account c " +
"WHERE ... a.id=?1",nativeQuery = true)
List<User> getUserById(Long id);
}
So by adding colId to User and marking it as @id, the above method will return the results
properly.
Note that newId() method is available in MS SQL server. Every other standard RDMS provides
a way to do this.