This is more something that I think I will forget, rather than having been forgotten. The documentation on named queries is particularly poor.

so using JPA there are hundreds of examples of using native queries but lets create a simple repo

package com.astondive.example;

import com.astondive.model.*;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

@Repository

public interface UserRepository extends JpaRepository<User, UserId> {
	@Query(nativeQuery=true)
	Page<UserDetails> getUserDetails(String userId, Pageable pageable);
}

What is strange here is that the method name does not follow the standard JPA dialect eg findUsersbyId, and the @Query has no query (rather just native=true)

So next we need to look at the model, where all the interesting stuff happens

package com.astondive.example.model;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;

@Data
@Entity
@NoArgsConstructor
@AllArgsConstructor
@SqlResultSetMapping(
        name = "UserDetailsMapping",
        classes = {
                @ConstructorResult(
                        targetClass = UserDetails.class,
                        columns = {
                                @ColumnResult(name = "user_id"),
                                @ColumnResult(name = "known_as"),
                                @ColumnResult(name = "roles")
                        }
                )
        }
)

@SqlResultSetMapping(
        name = "UserDetailsMapping.count",
        columns = {
                @ColumnResult(name = "cnt")
        }
)

@NamedNativeQuery(
        name = "User.getUserDetails",
        query = "SELECT  u.user_id   AS user_id," +
                "        u.known_as   AS known_as," +
                "        group_concat(distinct r.role separator',') as roles," 
                "   FROM users u" +
                "   LEFT JOIN roles r ON u.user_id = r.user_id" +
                "  WHERE u.account = :accountId" +
                "  GROUP BY u.user_id, u.known_as",
        resultSetMapping = "UserDetailsMapping")

@NamedNativeQuery(
        name = "User.getUserDetails.count",
        query = "SELECT COUNT(*) as cnt" +
                "   FROM users u" +
                "   LEFT JOIN roles r ON u.user_id = r.user_id" +
                "  WHERE u.account_id = :accountId ",
        resultSetMapping = "SubscriptionDetailsMapping.count")

public class User {
    @Id
    String userId;

    String knownAs;
 
    @ManyToOne
    Account account;

    @ManyToOne
    Role role;
}

This file is where the majority of the work is done. Firstly we have the mappings, they dictate the class that is going to be returned, that is then named (e.g. name = “UserDetailsMapping”) it also points to the actual class that will be used to store the data in (e.g. targetClass = UserDetails.class) the column results then map to the SQL columns that are returned to class members

Next we define the actual native sql query, this is using MySQL in his case but go write your sql of choice. the important bits here are the name, this uses the same class that the repo will use (i.e. User) as its domain, and then the method name we will use in the repo to make the query (i.e. getUserDetails)

After that there is the SQL query to be used, including any named parameters such as “:accountId” and finally we stae the mapping we defined earlier in the file

To fill in some of the blanks, we need a class to store each row returned and this is defined as UserDetails

package com.astondive.example.model;

import com.google.common.collect.Sets;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.ToString;

import java.util.Set;

@ToString
@Getter
@EqualsAndHashCode
public final class UserDetails {
    private final String userId;
    private final String knownAs;
    private final Set<String> roles;

    @java.beans.ConstructorProperties({"userId", "knownAs", "roles"})
    public UserDetails(String userId, String knownAs, Set<String> roles) {
        this.userId = userId;
        this.knownAs = knownAs;
        this.roles = roles;
    }

    public UserDetails(String userId, String knownAs, String roles) {
        this.knownAs = knownAs;
        this.online = online==null?false:online;
        this.roles = Sets.newHashSet(roles.split(","));
        this.userId = userId;
    }
}

We also need our account class as referenced in the User class

package com.astondive.example.model;

import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;

import java.util.UUID;
import java.security.SecureRandom;
import java.util.Random;
import java.util.concurrent.ThreadLocalRandom;

@Entity
@Data
@NoArgsConstructor
public class Account {
  @Id
  private String id;
  private String name;

  public Account(String name) {
    this.id = makeGuid();
    this.name = name;
  }

  @Transient
  protected String makeGuid() {    
    UUID uuid = UUID.randomUUID();
    return uuid.toString();
  }
}