@SqlResultSetMapping — превращение результата SQL-запроса в объект

В этой статье пойдет речь о том, как превратить результат нативного SQL-запроса в объект.

Введение

SQL-запрос дает больше возможностей, чем JPQL-запрос, но его результат труднее обработать (превратить в объект). В общем случае возвращается список записей, каждая из которых — массив. И в массиве столько элементов, сколько выбрано столбцов, а каждый столбец нужно преобразовывать к своему типу.

Ниже пример.

Преобразование SQL-запроса в общем случае (без маппинга)

Пусть есть таблица user:

Таблица user
Таблица user

Мы запрашиваем из нее всё:

 List<Object[]> users = this.em.createNativeQuery(
               "select id, email,  nickname, password,  role,  locked  from user ")
               .getResultList()
for (Object[] user : users) {
           long id = ((BigInteger) user[0]).longValue();
           String email = (String) user[1];
           String nickname = (String) user[2];
           String password = (String) user[3];
           String role = (String) user[4];
           boolean locked = (Boolean) user[5];
           User user1=new User(id, email, nickname, password, role, locked);
           ...
       }

Как видите, каждая строка — массив, а поле id возвращается как BigInteger, который нужно вручную преобразовать в long. А затем из полей построить объект User (если необходимо).

Класс User:

@Data
@Entity
@NoArgsConstructor
@AllArgsConstructor
public class User {
    @Id
    @GeneratedValue(generator = "sequence")
    private Long id;

    private String email;

    private String nickname;

    private String password;

    private String role = "ROLE_USER";

    private boolean locked = false;

}

Впрочем, поскольку в данном запросе мы просто получаем все поля из таблицы user, и User является сущностью, то можно преобразовать результат в User автоматически.

Автоматическое преобразование простого запроса

Для этого вторым параметром createNativeQuery() нужно указать User.class:

List<User> list = this.em.createNativeQuery(
             "select u.id as userid, u.email,  u.nickname,  u.password,  u.role,  u.locked  from user u",
             User.class).getResultList();
for (User user : users) {
         System.out.println(user.getEmail());
}

Cоответственно возвращаемый тип будет уже List<User>, и не List<Object[]>.

Но такое автоматическое преобразование возможно не для всех запросов.

В более сложных случаях нужно задать сопоставление в @SqlResultSetMapping.

@SqlResultSetMapping

Например, пусть необходимо получить данные не из одной, а из двух таблиц — user и post — таблицы пользователей и их постов.

Таблицы user и post
Таблицы user и post

Пост и пользователь находятся в отношение ManyToOne. Сущность User уже приведена выше, а Post выглядит так:

@Entity
@Data
@NoArgsConstructor
public class Post {
    @Id
    @GeneratedValue(generator = "sequence")
    private Long id;

    private String title;

    private String text;

    @ManyToOne
    private User user;
}

Результаты выборки из этих двух таблиц могут быть разными объектами (в зависимости от того, что мы хотим получить):

  • некий DTO-объект (например, перечень заголовков постов и с именем пользователя);
  • сущность и скалярный столбец (список пользователей с количеством постов для каждого пользователя).

Здесь автоматическое преобразование не работает, нужно задавать сопоставление в @SqlResultSetMapping. Исходный код аннотации:

@Repeatable(SqlResultSetMappings.class)
@Target({TYPE}) 
@Retention(RUNTIME)
public @interface SqlResultSetMapping { 

    String name(); 

    EntityResult[] entities() default {};
 
    ConstructorResult[] classes() default {};

    ColumnResult[] columns() default {};
}

Как видите, помимо имени, в ней можно задать сущности, конструкторы для DTO-объектов и скалярные столбцы. В любой комбинации. Ниже рассмотрим примеры использования.

ConstructorResult

Получим DTO-объекты PostDto — перечень постов с именем пользователя:

@Data
@AllArgsConstructor
public class PostDto {
    private long id;
    private String title;
    private long userId;
    private String nickname;
}

SQL-запрос выглядит так:

select p.id, p.title, u.id as userId, u.nickName 
from post as p join user as u 
on p.user_id=u.id

Чтобы сопоставить результат SQL-запроса объекту PostDto, в ConstructorResult пропишем названия столбцов, которые идут в конструктор PostDto:

@SqlResultSetMapping(name = "PostDtoMapping",
        classes = {
                @ConstructorResult(
                        columns = {
                                @ColumnResult(name = "id", type = long.class),
                                @ColumnResult(name = "title"),
                                @ColumnResult(name = "userId", type = long.class),
                                @ColumnResult(name = "nickname")
                        },
                        targetClass = PostDto.class
                )}
)

Обратите внимание, что поскольку есть два столбца с названием id, в SQL мы прописали алиас u.id as userId и использовали его @ConstructorResult.

Аннотация прописывается в классе сущности, а сам SQL-запрос можно задать как внутри em.CreateNativeQuery(), так и прописать его в классе сущности под своим именем.

Во втором случае его можно использовать не только в em.creat…, но и внутри @Query.

Пропишем сам запрос тоже в классе сущности:

@Entity
@Data
@NoArgsConstructor
@NamedNativeQuery(name = "PostDtos", query = "select p.id, p.title, " +
        "u.id as userId, u.nickName  " +
        "from post as p join user as u on p.user_id=u.id ", resultSetMapping = "PostDtoMapping")
@SqlResultSetMapping(name = "PostDtoMapping",
        classes = {
                @ConstructorResult(
                        columns = {
                                @ColumnResult(name = "id", type = long.class),
                                @ColumnResult(name = "title"),
                                @ColumnResult(name = "userId", type = long.class),
                                @ColumnResult(name = "nickname")
                        },
                        targetClass = PostDto.class
                )}
)
public class Post {
    @Id
    @GeneratedValue(generator = "sequence")
    private Long id;

    private String title;

    private String text;

    @ManyToOne
    private User user;
}

В @NamedNativeQuery указано имя нашего маппинга — PostDtoMapping.

И теперь в кастомном репозитории создадим метод, возвращающий List<PostDto>, внутри вместо самого запроса указываем его имя — PostDtos:

public class PostRepositoryCustomImpl implements PostRepositoryCustom {
    @PersistenceContext
    private EntityManager em;

    @Override
    public List<PostDto> findPostDtos() {
        List<PostDto> results=em.createNamedQuery("PostDtos").getResultList();
        return results;
    }

}
Поскольку к запросы обращаемся по имени, используем не em.createNativeQuery(), а em.createNamedQuery()

Как уже сказано, можно было задать запрос и в аннотации @Query:

public interface PostRepository extends JpaRepository<Post, Long>, PostRepositoryCustom {
    @Query(nativeQuery = true, name = "PostDtos")
    List<PostDto> findDtos();
}

EntityResult и ColumnResult

Теперь получим список пользователей с количеством постов. Причем у пользователя извлекаем все данные. То есть  результат будет сущность User и скалярный столбец. Поэтому подойдет EntityResult (для сущность User) и ColumnResult (столбец с количеством постов)

SQL такой:

select count(p.id) as post_count, u.* 
   from  user as u left join post as p 
   on p.user_id=u.id 
   group by u.id

Пропишем его в сущности User, и там же @SqlResultSetMapping:

@Data
@Entity
@NoArgsConstructor
@AllArgsConstructor
@NamedNativeQuery(name = "UsersWithPostCount", 
        query = "select count(p.id) as post_count, " +
        "u.* " +
        "from  user as u  left join post as p on p.user_id=u.id group by u.id",
        resultSetMapping = "UsersWithPostCountMapping")
@SqlResultSetMapping(name = "UsersWithPostCountMapping",
        entities = @EntityResult(
                entityClass = User.class,
                fields = {
                        @FieldResult(name = "id", column = "id"),
                        @FieldResult(name = "email", column = "email"),
                        @FieldResult(name = "nickname", column = "nickname"),
                        @FieldResult(name = "password", column = "password"),
                        @FieldResult(name = "role", column = "role"),
                        @FieldResult(name = "locked", column = "locked"),
                }
        ),

        columns = @ColumnResult(
                name = "post_count",
                type = int.class
        )
)
public class User {
    @Id
    @GeneratedValue(generator = "sequence")
    private Long id;

    private String email;

    private String nickname;

    private String password;

    private String role = "ROLE_USER";

    private boolean locked = false;

}

Теперь создадим кастомный репозиторий для метода (но можно было бы прописать в @Query):

public class UserRepositoryCustomImpl implements UserRepositoryCustom {
    @PersistenceContext
    private EntityManager em;

    @Override
    public List<Tuple> findUsersWithPostCount() {
        List<Tuple> results = this.em.createNamedQuery("UsersWithPostCount").getResultList();
        return results;
    }

}

Теперь мы получаем Tuple, который состоит из двух объектов  — User и Integer.

Напишем тест:

@Test
public void shouldFindUsersWithPostCount() {
    List<Tuple> users = userRepository.findUsersWithPostCount();
    for (Tuple tuple : users) {
        User user = tuple.get(0, User.class);
        int count = tuple.get(1, Integer.class);
        assertNotNull(user);
        assertTrue(count>=0);
    }
    assertEquals(2, users.size());
}

Можно было бы вернуть List<Object[]> вместо List<Tuple>.

Итоги

@SqlResultSetMapping позволяет декларативно задать преобразование результата SQL-запроса в объект(ы). Мы рассмотрели, как с помощью нее получить сущности, DTO-объекты и скалярные столбцы (в любой комбинации). Но вложенные объекты с помощью этой аннотации задать нельзя.

@SqlResultSetMapping  — JPA-аннотация, и в Hibernate ей соответствует разве что интерфейс ResultTransformer. Он тоже предназначен для преобразования результата запроса, но, в отличие от аннотации,  интерфейс ResultTransformer более гибкий: с ним можно получить объекты с вложенными объектами. Кроме того, ResultTransformer подходит для преобразования результата не только нативного SQL, но и JPQL.

Зато @SqlResultSetMapping компактнее.

Исходный код примера есть на GitHub.

 

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *