java - sqlresultsetmapping - JPA: Como converter um conjunto de resultados da consulta nativa em uma coleção de classes POJO




spring native query to entity (13)

Eu estou usando o JPA no meu projeto.

Cheguei a uma consulta em que preciso fazer operação de junção em cinco tabelas. Então eu criei uma consulta nativa que retorna cinco campos.

Agora eu quero converter o objeto de resultado para a classe java POJO que contém as mesmas cinco Strings.

Existe alguma maneira em JPA para lançar diretamente esse resultado para a lista de objetos POJO?

Eu vim para a seguinte solução

@NamedNativeQueries({  
    @NamedNativeQuery(  
        name = "nativeSQL",  
        query = "SELECT * FROM Actors",  
        resultClass = db.Actor.class),  
    @NamedNativeQuery(  
        name = "nativeSQL2",  
        query = "SELECT COUNT(*) FROM Actors",  
        resultClass = XXXXX) // <--------------- problem  
})  

Agora aqui no resultClass, precisamos fornecer uma classe que seja a entidade JPA real? OU Podemos convertê-lo para qualquer classe JAVA POJO que contenha os mesmos nomes de coluna?


A maneira mais fácil é usar as projeções . Ele pode mapear resultados de consulta diretamente para interfaces e é mais fácil de implementar do que usando SqlResultSetMapping.

Um exemplo é mostrado abaixo:

@Repository
public interface PeopleRepository extends JpaRepository<People, Long> {

    @Query(value = "SELECT p.name AS name, COUNT(dp.people_id) AS count " +
        "FROM people p INNER JOIN dream_people dp " +
        "ON p.id = dp.people_id " +
        "WHERE p.user_id = :userId " +
        "GROUP BY dp.people_id " +
        "ORDER BY p.name", nativeQuery = true)
    List<PeopleDTO> findByPeopleAndCountByUserId(@Param("userId") Long userId);

    @Query(value = "SELECT p.name AS name, COUNT(dp.people_id) AS count " +
        "FROM people p INNER JOIN dream_people dp " +
        "ON p.id = dp.people_id " +
        "WHERE p.user_id = :userId " +
        "GROUP BY dp.people_id " +
        "ORDER BY p.name", nativeQuery = true)
    Page<PeopleDTO> findByPeopleAndCountByUserId(@Param("userId") Long userId, Pageable pageable);

}



// Interface to which result is projected
public interface PeopleDTO {

    String getName();

    Long getCount();

}

Os campos da interface projetada devem corresponder aos campos dessa entidade. Caso contrário, o mapeamento de campo poderá ser interrompido.

Além disso, se você usar a notação SELECT table.column , sempre defina os aliases que correspondem aos nomes da entidade, conforme mostrado no exemplo.


Como outros já mencionaram todas as soluções possíveis, estou compartilhando minha solução de solução alternativa.

Na minha situação com o Postgres 9.4 , enquanto trabalhava com Jackson ,

//Convert it to named native query.
List<String> list = em.createNativeQuery("select cast(array_to_json(array_agg(row_to_json(a))) as text) from myschema.actors a")
                   .getResultList();

List<ActorProxy> map = new ObjectMapper().readValue(list.get(0), new TypeReference<List<ActorProxy>>() {});

Tenho certeza que você pode encontrar o mesmo para outros bancos de dados.

Também FYI, resultados da consulta nativa do JPA 2.0 como mapa


Estilo antigo usando o Resultset

@Transactional(readOnly=true)
public void accessUser() {
    EntityManager em = this.getEntityManager();
    org.hibernate.Session session = em.unwrap(org.hibernate.Session.class);
    session.doWork(new Work() {
        @Override
        public void execute(Connection con) throws SQLException {
            try (PreparedStatement stmt = con.prepareStatement(
                    "SELECT u.username, u.name, u.email, 'blabla' as passe, login_type as loginType FROM users u")) {
                ResultSet rs = stmt.executeQuery();
                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    System.out.print(rsmd.getColumnName(i) + " (" + rsmd.getColumnTypeName(i) + ") / ");
                }
                System.out.println("");
                while (rs.next()) {
                    System.out.println("Found username " + rs.getString("USERNAME") + " name " + rs.getString("NAME") + " email " + rs.getString("EMAIL") + " passe " + rs.getString("PASSE") + " email " + rs.getInt("LOGIN_TYPE"));
                }
            }
        }
    });
}

Eu encontrei algumas soluções para isso.

Usando entidades mapeadas (JPA 2.0)

Usando o JPA 2.0, não é possível mapear uma consulta nativa para um POJO, isso só pode ser feito com uma entidade.

Por exemplo:

Query query = em.createNativeQuery("SELECT name,age FROM jedi_table", Jedi.class);
@SuppressWarnings("unchecked")
List<Jedi> items = (List<Jedi>) query.getResultList();

Mas neste caso, Jedi , deve ser uma classe de entidade mapeada.

Uma alternativa para evitar o aviso desmarcado aqui seria usar uma consulta nativa nomeada. Então, se declararmos a consulta nativa em uma entidade

@NamedNativeQuery(
 name="jedisQry", 
 query = "SELECT name,age FROM jedis_table", 
 resultClass = Jedi.class)

Então, podemos simplesmente fazer:

TypedQuery<Jedi> query = em.createNamedQuery("jedisQry", Jedi.class);
List<Jedi> items = query.getResultList();

Isso é mais seguro, mas ainda estamos restritos a usar uma entidade mapeada.

Mapeamento Manual

Uma solução que experimentei um pouco (antes da chegada do JPA 2.1) estava fazendo mapeamento contra um construtor POJO usando um pouco de reflexão.

public static <T> T map(Class<T> type, Object[] tuple){
   List<Class<?>> tupleTypes = new ArrayList<>();
   for(Object field : tuple){
      tupleTypes.add(field.getClass());
   }
   try {
      Constructor<T> ctor = type.getConstructor(tupleTypes.toArray(new Class<?>[tuple.length]));
      return ctor.newInstance(tuple);
   } catch (Exception e) {
      throw new RuntimeException(e);
   }
}

Esse método basicamente usa uma matriz de tupla (conforme retornado por consultas nativas) e a mapeia em uma classe POJO fornecida, procurando por um construtor que tenha o mesmo número de campos e do mesmo tipo.

Então podemos usar métodos convenientes como:

public static <T> List<T> map(Class<T> type, List<Object[]> records){
   List<T> result = new LinkedList<>();
   for(Object[] record : records){
      result.add(map(type, record));
   }
   return result;
}

public static <T> List<T> getResultList(Query query, Class<T> type){
  @SuppressWarnings("unchecked")
  List<Object[]> records = query.getResultList();
  return map(type, records);
}

E podemos simplesmente usar essa técnica da seguinte maneira:

Query query = em.createNativeQuery("SELECT name,age FROM jedis_table");
List<Jedi> jedis = getResultList(query, Jedi.class);

JPA 2.1 com @SqlResultSetMapping

Com a chegada do JPA 2.1, podemos usar a anotação @SqlResultSetMapping para resolver o problema.

Precisamos declarar um mapeamento do conjunto de resultados em algum lugar em uma entidade:

@SqlResultSetMapping(name="JediResult", classes = {
    @ConstructorResult(targetClass = Jedi.class, 
    columns = {@ColumnResult(name="name"), @ColumnResult(name="age")})
})

E então nós simplesmente fazemos:

Query query = em.createNativeQuery("SELECT name,age FROM jedis_table", "JediResult");
@SuppressWarnings("unchecked")
List<Jedi> samples = query.getResultList();

É claro que, neste caso, o Jedi não precisa ser uma entidade mapeada. Pode ser um POJO regular.

Usando o mapeamento XML

Eu sou um daqueles que acham a adição de todos estes @SqlResultSetMapping bastante invasivo em minhas entidades, e eu particularmente não gosto da definição de consultas nomeadas dentro de entidades, então alternativamente eu faço tudo isso no arquivo META-INF/orm.xml :

<named-native-query name="GetAllJedi" result-set-mapping="JediMapping">
    <query>SELECT name,age FROM jedi_table</query>
</named-native-query>

<sql-result-set-mapping name="JediMapping">
        <constructor-result target-class="org.answer.model.Jedi">
            <column name="name" class="java.lang.String"/>
            <column name="age" class="java.lang.Integer"/>
        </constructor-result>
    </sql-result-set-mapping>

E essas são todas as soluções que conheço. Os dois últimos são a maneira ideal se podemos usar o JPA 2.1.


Não tenho certeza se isso se encaixa aqui, mas eu tive uma pergunta semelhante e encontrei seguinte solução simples / exemplo para mim:

private EntityManager entityManager;
...
    final String sql = " SELECT * FROM STORE "; // select from the table STORE
    final Query sqlQuery = entityManager.createNativeQuery(sql, Store.class);

    @SuppressWarnings("unchecked")
    List<Store> results = (List<Store>) sqlQuery.getResultList();

No meu caso eu tive que usar partes SQL definidas em Strings em algum outro lugar, então eu não poderia simplesmente usar NamedNativeQuery.


No modo de hibernação, você pode usar esse código para mapear facilmente sua consulta nativa.

private List < Map < String, Object >> getNativeQueryResultInMap() {
String mapQueryStr = "SELECT * FROM AB_SERVICE three ";
Query query = em.createNativeQuery(mapQueryStr);
NativeQueryImpl nativeQuery = (NativeQueryImpl) query;
nativeQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
List < Map < String, Object >> result = query.getResultList();
for (Map map: result) {
    System.out.println("after request  ::: " + map);
}
return result;}

O procedimento de desempacotamento pode ser executado para atribuir resultados a não entidade (que é Beans / POJO). O procedimento é o seguinte.

List<JobDTO> dtoList = entityManager.createNativeQuery(sql)
        .setParameter("userId", userId)
        .unwrap(org.hibernate.Query.class).setResultTransformer(Transformers.aliasToBean(JobDTO.class)).list();

O uso é para implementação do JPA-Hibernate.


Primeiro declare as seguintes anotações:

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface NativeQueryResultEntity {
}

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface NativeQueryResultColumn {
    int index();
}

Em seguida, anote seu POJO da seguinte maneira:

@NativeQueryResultEntity
public class ClassX {
    @NativeQueryResultColumn(index=0)
    private String a;

    @NativeQueryResultColumn(index=1)
    private String b;
}

Em seguida, escreva o processador de anotações:

public class NativeQueryResultsMapper {

    private static Logger log = LoggerFactory.getLogger(NativeQueryResultsMapper.class);

    public static <T> List<T> map(List<Object[]> objectArrayList, Class<T> genericType) {
        List<T> ret = new ArrayList<T>();
        List<Field> mappingFields = getNativeQueryResultColumnAnnotatedFields(genericType);
        try {
            for (Object[] objectArr : objectArrayList) {
                T t = genericType.newInstance();
                for (int i = 0; i < objectArr.length; i++) {
                    BeanUtils.setProperty(t, mappingFields.get(i).getName(), objectArr[i]);
                }
                ret.add(t);
            }
        } catch (InstantiationException ie) {
            log.debug("Cannot instantiate: ", ie);
            ret.clear();
        } catch (IllegalAccessException iae) {
            log.debug("Illegal access: ", iae);
            ret.clear();
        } catch (InvocationTargetException ite) {
            log.debug("Cannot invoke method: ", ite);
            ret.clear();
        }
        return ret;
    }

    // Get ordered list of fields
    private static <T> List<Field> getNativeQueryResultColumnAnnotatedFields(Class<T> genericType) {
        Field[] fields = genericType.getDeclaredFields();
        List<Field> orderedFields = Arrays.asList(new Field[fields.length]);
        for (int i = 0; i < fields.length; i++) {
            if (fields[i].isAnnotationPresent(NativeQueryResultColumn.class)) {
                NativeQueryResultColumn nqrc = fields[i].getAnnotation(NativeQueryResultColumn.class);
                orderedFields.set(nqrc.index(), fields[i]);
            }
        }
        return orderedFields;
    }
}

Use a estrutura acima da seguinte forma:

String sql = "select a,b from x order by a";
Query q = entityManager.createNativeQuery(sql);

List<ClassX> results = NativeQueryResultsMapper.map(q.getResultList(), ClassX.class);

Se você usar o Spring-jpa , este é um complemento para as respostas e essa pergunta. Por favor, corrija isso se houver alguma falha. Eu usei principalmente três métodos para obter "mapear Object[] resultado Object[] para um pojo" com base na necessidade prática que encontro:

  1. JPA construído no método é suficiente.
  2. JPA embutido no método não é suficiente, mas um sql personalizado com sua Entity é suficiente.
  3. O primeiro 2 falhou e eu tenho que usar um nativeQuery . Aqui estão os exemplos. O pojo esperado:

    public class Antistealingdto {
    
        private String secretKey;
    
        private Integer successRate;
    
        // GETTERs AND SETTERs
    
        public Antistealingdto(String secretKey, Integer successRate) {
            this.secretKey = secretKey;
            this.successRate = successRate;
        }
    }

Método 1 : Altere o pojo em uma interface:

public interface Antistealingdto {
    String getSecretKey();
    Integer getSuccessRate();
}

E repositório:

interface AntiStealingRepository extends CrudRepository<Antistealing, Long> {
    Antistealingdto findById(Long id);
}

Método 2 : Repositório:

@Query("select new AntistealingDTO(secretKey, successRate) from Antistealing where ....")
Antistealing whatevernamehere(conditions);

Nota: a sequência de parâmetros do construtor POJO deve ser idêntica na definição POJO e no sql.

Método 3 : Use @SqlResultSetMapping e @NamedNativeQuery em Entity como o exemplo na resposta de Edwin Dalorzo.

Os dois primeiros métodos chamariam muitos manipuladores intermediários, como conversores personalizados. Por exemplo, o AntiStealing define um secretKey , antes de persistir, um conversor é inserido para criptografá-lo. Isso resultaria nos dois primeiros métodos retornando uma chave secretKey convertida, que não é o que eu quero. Enquanto o método 3 venceria o conversor, e retornado secretKey seria o mesmo que é armazenado (um criptografado).


Sim, com o JPA 2.1 é fácil. Você tem anotações muito úteis. Eles simplificam sua vida.

Primeiro, declare sua consulta nativa e, em seguida, seu mapeamento do conjunto de resultados (que define o mapeamento dos dados retornados pelo banco de dados para seus POJOs). Escreva sua classe POJO para se referir a (não incluída aqui por brevidade). Por último mas não menos importante: crie um método em um DAO (por exemplo) para chamar a consulta. Isso funcionou para mim em um aplicativo dropwizard (1.0.0).

Primeiro, declare uma consulta nativa em uma classe de entidade:

@NamedNativeQuery (
name = "domain.io.MyClass.myQuery",
query = "Select a.colA, a.colB from Table a",
resultSetMapping = "mappinMyNativeQuery")   // must be the same name as in the SqlResultSetMapping declaration

Abaixo você pode adicionar a declaração de mapeamento do conjunto de resultados:

@SqlResultSetMapping(
name = "mapppinNativeQuery",  // same as resultSetMapping above in NativeQuery
   classes = {
      @ConstructorResult( 
          targetClass = domain.io.MyMapping.class
          columns = {
               @ColumnResult( name = "colA", type = Long.class),  
               @ColumnResult( name = "colB", type = String.class)
          }
      )
   } 
)

Mais tarde, em um DAO, você pode consultar a consulta como

public List<domain.io.MyMapping> findAll() {
        return (namedQuery("domain.io.MyClass.myQuery").list());
    }

É isso aí.


Usando o Hibernate:

@Transactional(readOnly=true)
public void accessUser() {
    EntityManager em = repo.getEntityManager();
    org.hibernate.Session session = em.unwrap(org.hibernate.Session.class);
    org.hibernate.SQLQuery q = (org.hibernate.SQLQuery) session.createSQLQuery("SELECT u.username, u.name, u.email, 'blabla' as passe, login_type as loginType FROM users u")
        .addScalar("username", StringType.INSTANCE).addScalar("name", StringType.INSTANCE)
        .addScalar("email", StringType.INSTANCE).addScalar("passe", StringType.INSTANCE)
        .addScalar("loginType", IntegerType.INSTANCE)
        .setResultTransformer(Transformers.aliasToBean(User2DTO.class));

    List<User2DTO> userList = q.list();
}

Usando o Hibernate:

@Transactional(readOnly=true)
public void accessUser() {
EntityManager em = repo.getEntityManager();
    org.hibernate.Session session = em.unwrap(org.hibernate.Session.class);
    org.hibernate.SQLQuery q = (org.hibernate.SQLQuery) session.createSQLQuery("SELECT u.username, u.name, u.email, 'blabla' as passe, login_type as loginType FROM users u").addScalar("username", StringType.INSTANCE).addScalar("name", StringType.INSTANCE).addScalar("email", StringType.INSTANCE).addScalar("passe", StringType.INSTANCE).addScalar("loginType", IntegerType.INSTANCE)
        .setResultTransformer(Transformers.aliasToBean(User2DTO.class));

    List<User2DTO> userList = q.list();
}

Veja o exemplo abaixo para usar um POJO como pseudo entidade para recuperar o resultado da consulta nativa sem usar o complexo SqlResultSetMapping. Só precisa de duas anotações, uma @Enity nua e uma @Id falsa no seu POJO. @Id pode ser usado em qualquer campo de sua escolha, um campo @Id pode ter chaves duplicadas, mas não valores nulos.

Como o @Enity não mapeia para nenhuma tabela física, então este POJO é chamado de pseudo-entidade.

Ambiente: eclipselink 2.5.0-RC1, jpa-2.1.0, mysql-connector-java-5.1.14

Você pode baixar o projeto maven completo here

A consulta nativa é baseada nos funcionários da amostra mysql db http://dev.mysql.com/doc/employee/en/employees-installation.html

persistence.xml

<?xml version="1.0" encoding="UTF-8"?><persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.1" 
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
<persistence-unit name="jpa-mysql" transaction-type="RESOURCE_LOCAL">
    <class>org.moonwave.jpa.model.pojo.Employee</class>
    <properties>
        <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/employees" />
        <property name="javax.persistence.jdbc.user" value="user" />
        <property name="javax.persistence.jdbc.password" value="***" />
        <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
    </properties>
</persistence-unit>

Employee.java

package org.moonwave.jpa.model.pojo;

@Entity
public class Employee {

@Id
protected Long empNo;

protected String firstName;
protected String lastName;
protected String title;

public Long getEmpNo() {
    return empNo;
}
public void setEmpNo(Long empNo) {
    this.empNo = empNo;
}
public String getFirstName() {
    return firstName;
}
public void setFirstName(String firstName) {
    this.firstName = firstName;
}
public String getLastName() {
    return lastName;
}
public void setLastName(String lastName) {
    this.lastName = lastName;
}   
public String getTitle() {
    return title;
}
public void setTitle(String title) {
    this.title = title;
}
public String toString() {
    StringBuilder sb = new StringBuilder();
    sb.append("empNo: ").append(empNo);
    sb.append(", firstName: ").append(firstName);
    sb.append(", lastName: ").append(lastName);
    sb.append(", title: ").append(title);
    return sb.toString();
}
}

EmployeeNativeQuery.java

public class EmployeeNativeQuery {
private EntityManager em;
private EntityManagerFactory emf;

public void setUp() throws Exception {
    emf=Persistence.createEntityManagerFactory("jpa-mysql");
    em=emf.createEntityManager();
}
public void tearDown()throws Exception {
    em.close();
    emf.close();
}

@SuppressWarnings("unchecked")
public void query() {
    Query query = em.createNativeQuery("select e.emp_no as empNo, e.first_name as firstName, e.last_name as lastName," + 
            "t.title from employees e join titles t on e.emp_no = t.emp_no", Employee.class);
    query.setMaxResults(30);
    List<Employee> list = (List<Employee>) query.getResultList();
    int i = 0;
    for (Object emp : list) {
        System.out.println(++i + ": " + emp.toString());
    }
}

public static void main( String[] args ) {
    EmployeeNativeQuery test = new EmployeeNativeQuery();
    try {
        test.setUp();
        test.query();
        test.tearDown();
    } catch (Exception e) {
        System.out.println(e);
    }
}
}