В этой статье пойдет речь о том, как превратить результат нативного SQL-запроса в объект.
Введение
SQL-запрос дает больше возможностей, чем JPQL-запрос, но его результат труднее обработать (превратить в объект). В общем случае возвращается список записей, каждая из которых — массив. И в массиве столько элементов, сколько выбрано столбцов, а каждый столбец нужно преобразовывать к своему типу.
Ниже пример.
Преобразование SQL-запроса в общем случае (без маппинга)
Пусть есть таблица 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 — таблицы пользователей и их постов.
Пост и пользователь находятся в отношение 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(), так и прописать его в классе сущности под своим именем.
Пропишем сам запрос тоже в классе сущности:
@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;
}
}
Как уже сказано, можно было задать запрос и в аннотации @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 более гибкий: с ним можно получить объекты с вложенными dto-объектами. Кроме того, ResultTransformer подходит для преобразования результата не только нативного SQL, но и JPQL.
Зато @SqlResultSetMapping компактнее.
Исходный код примера есть на GitHub.