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




spring native query to entity (16)

O JPA fornece um SqlResultSetMapping que permite mapear os retornos de sua consulta nativa para uma entidade ou uma classe personalizada .

EDIT JPA 1.0 não permite mapeamento para classes não-entidade. Somente no JPA 2.1, um ConstructorResult foi adicionado para mapear valores de retorno em uma classe java.

Além disso, para o problema do OP em obter contagem, deve ser suficiente definir um mapeamento de conjunto de resultados com um único ColumnResult

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?


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);

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();
}

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


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);
    }
}
}

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();
}

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.


Use o DTO Design Pattern . Foi usado no EJB 2.0 . A entidade era gerenciada por contêiner. DTO Design Pattern é usado para resolver este problema. Mas, pode ser usado agora, quando o aplicativo é desenvolvido Server Side e do Client Side separadamente. DTO é usado quando o Server side não deseja transmitir / retornar Entity com anotação para o Client Side .

Exemplo de DTO:

PersonEntity.java

@Entity
public class PersonEntity {
    @Id
    private String id;
    private String address;

    public PersonEntity(){

    }
    public PersonEntity(String id, String address) {
        this.id = id;
        this.address = address;
    }
    //getter and setter

}

PersonDTO.java

public class PersonDTO {
    private String id;
    private String address;

    public PersonDTO() {
    }
    public PersonDTO(String id, String address) {
        this.id = id;
        this.address = address;
    }

    //getter and setter 
}

DTOBuilder.java

public class DTOBuilder() {
    public static PersonDTO buildPersonDTO(PersonEntity person) {
        return new PersonDTO(person.getId(). person.getAddress());
    }
}

EntityBuilder.java <- mide ser necessário

public class EntityBuilder() {
    public static PersonEntity buildPersonEntity(PersonDTO person) {
        return new PersonEntity(person.getId(). person.getAddress());
    }
}

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;}

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).


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.


Estilo antigo usando 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"));
                }
            }
        }
    });
}

Tudo o que você precisa é de um DTO com um construtor:

public class User2DTO implements Serializable {

    /** pode ser email ou id do Google comecando com G ou F para Facebook */
    private String username;

    private String password;

    private String email;

    private String name;

    private Integer loginType;

    public User2DTO(Object...fields) {
        super();
        this.username = (String) fields[0];
        this.name = (String) fields[1];
        this.email = (String) fields[2];
        this.password = (String) fields[3];
        this.loginType = (Integer) fields[4];
    }

e chame isso:

EntityManager em = repo.getEntityManager();
        Query q = em.createNativeQuery("SELECT u.username, u.name, u.email, 'blabla' as passe, login_type as loginType FROM users u");
        List<Object[]> objList = q.getResultList();
        List<User2DTO> ooBj = objList.stream().map(User2DTO::new).collect(Collectors.toList());

Se você estiver usando o Spring, poderá usar org.springframework.jdbc.core.RowMapper

Aqui está um exemplo:

public List query(String objectType, String namedQuery)
{
  String rowMapper = objectType + "RowMapper";
  // then by reflection you can instantiate and use. The RowMapper classes need to follow the naming specific convention to follow such implementation.
} 

Maneira simples de converter consulta SQL em coleção de classes POJO,

Query query = getCurrentSession().createSQLQuery(sqlQuery).addEntity(Actors.class);
List<Actors> list = (List<Actors>) query.list();
return list;