JPA:如何将本机查询结果集转换为POJO类集合

我在我的项目中使用JPA。

我遇到一个查询，我需要对五个表进行连接操作。因此，我创建了一个返回五个字段的本机查询。

现在我想将结果对象转换为java POJO类，其中包含相同的五个字符串。

在JPA中有任何方法可以直接将结果转换为POJO对象列表吗??

我想出了如下的解决办法。

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

现在在resultClass中，我们需要提供一个实际的JPA实体类吗? 或我们可以将其转换为包含相同列名的任何JAVA POJO类?

当前回答

既然其他人已经提到了所有可能的解决方案，我将分享我的变通方案。

在我使用Postgres 9.4的情况下，当我和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>>() {});

我相信您可以在其他数据库中找到相同的方法。

另供参考，JPA 2.0本机查询结果为映射

2017-11-11 05:18:07

其他回答

如果你使用Spring，你可以使用org.springframework.jdbc.core.RowMapper

这里有一个例子:

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

2016-02-29 11:38:05

首先声明以下注释:

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

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

然后按以下方式注释你的POJO:

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

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

然后写注释处理器:

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

使用上述框架如下:

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

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

2015-05-07 20:53:27

可以执行展开过程，将结果分配给非实体(即Beans/POJO)。操作步骤如下。

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

该用法用于JPA-Hibernate实现。

2018-03-26 10:24:43

最简单的方法就是使用投影。它可以将查询结果直接映射到接口，并且比使用SqlResultSetMapping更容易实现。

一个例子如下所示:

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

}

投影接口中的字段必须与该实体中的字段匹配。否则字段映射可能会中断。

同样，如果你使用SELECT表。列表示法总是定义与实体名称匹配的别名，如示例所示。

2019-04-03 05:17:26