Dealing with JPA Entities backed by SQL Queries that return no unique columns

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.