使用Hibernate Criteria查询获取特定列

Question

我在代码中使用Criteria Query。它总是会触发select * from ...的查询操作。

相反，我希望从我的查询中忽略一个字段（列），因为该字段存储了大量数据，导致性能问题。

有人可以给个想法吗？

---

一些更新

我在查询中添加了一个投影，并创建了一个类似于...

select
    this_.TEMPLATE_ID as y0_,
    this_.TEMPLATE_NAME as y1_,
    this_.CREATE_DATE as y2_,
    this_.UPDATE_DATE as y3_,
    this_.STATUS_CODE as y4_,
    this_.USER_ID as y5_,
    this_.UPDATED_BY as y6_,
    this_.CATEGORY_ID as y7_,
    this_.PRACTICE_ID as y8_ 
from
    templates this_ 
inner join
    user user1_ 
        on this_.USER_ID=user1_.USER_ID 
inner join
    template_categories category2_ 
        on this_.CATEGORY_ID=category2_.CATEGORY_ID 
where
    y4_=? 
    and y8_=? 
    and y5_ in (
        ?, ?
    ) 
order by
    y1_ asc limit ?

现在的问题是.. where clause 中出现了未知列'y4_'，而对于'y8_', 'y5_'也是相同的错误。这意味着所有的 where 子句都会出错。

我将其修改为如下查询...

select
    this_.TEMPLATE_ID as y0_,
    this_.TEMPLATE_NAME as y1_,
    this_.CREATE_DATE as y2_,
    this_.UPDATE_DATE as y3_,
    this_.STATUS_CODE as y4_,
    this_.USER_ID as y5_,
    this_.UPDATED_BY as y6_,
    this_.CATEGORY_ID as y7_,
    this_.PRACTICE_ID as y8_ 
from
    templates this_ 
inner join
    user user1_ 
        on this_.USER_ID=user1_.USER_ID 
inner join
    template_categories category2_ 
        on this_.CATEGORY_ID=category2_.CATEGORY_ID 
where
    this_.STATUS_CODE=1
    and this_.PRACTICE_ID=1 
    and this_.USER_ID in (
        1, 2
    ) 
order by
    y1_ asc limit ?

它能正常工作。但我不知道如何在HQL中修改它？

- Ketan Bhavsar

Answer 1

使用Projections来指定您想要返回哪些列。

示例

SQL查询

SELECT user.id, user.name FROM user;

Hibernate替代方案

Criteria cr = session.createCriteria(User.class)
    .setProjection(Projections.projectionList()
      .add(Projections.property("id"), "id")
      .add(Projections.property("Name"), "Name"))
    .setResultTransformer(Transformers.aliasToBean(User.class));

  List<User> list = cr.list();

- Hemant Metalia

---

使用JPA是否也可以做到同样的事情？ - max.kuzmentsov

@cingulata 是的，这是可能的。请查看此文章http://www.objectdb.com/java/jpa/query/jpql/select - Hemant Metalia

我得到了“null”的预测属性。 为什么？ 这与'alias'有关吗？ - Eray Tuncer

投影方法 - ++1 - samshers

如果您有时间，能否帮忙解决这个问题：Criteria API和JPQL API中的GROUP BY和CONCAT？ - samshers

Answer 2

您可以使用JPQL和JPA Criteria API来进行任何类型的DTO投影（仅将所选列映射到DTO类）。请查看下面的代码片段，展示如何有选择地选择各种列，而不是选择所有列。这些示例还显示了如何从多个列连接中选择各种列。希望这能帮助到您。

JPQL代码：

String dtoProjection = "new com.katariasoft.technologies.jpaHibernate.college.data.dto.InstructorDto"
                + "(i.id, i.name, i.fatherName, i.address, id.proofNo, "
                + " v.vehicleNumber, v.vechicleType, s.name, s.fatherName, "
                + " si.name, sv.vehicleNumber , svd.name) ";

        List<InstructorDto> instructors = queryExecutor.fetchListForJpqlQuery(
                "select " + dtoProjection + " from Instructor i " + " join i.idProof id " + " join i.vehicles v "
                        + " join i.students s " + " join s.instructors si " + " join s.vehicles sv "
                        + " join sv.documents svd " + " where i.id > :id and svd.name in (:names) "
                        + " order by i.id , id.proofNo , v.vehicleNumber , si.name , sv.vehicleNumber , svd.name ",
                CollectionUtils.mapOf("id", 2, "names", Arrays.asList("1", "2")), InstructorDto.class);

        if (Objects.nonNull(instructors))
            instructors.forEach(i -> i.setName("Latest Update"));

        DataPrinters.listDataPrinter.accept(instructors);

JPA查询标准API代码：

@Test
    public void fetchFullDataWithCriteria() {
        CriteriaBuilder cb = criteriaUtils.criteriaBuilder();
        CriteriaQuery<InstructorDto> cq = cb.createQuery(InstructorDto.class);

        // prepare from expressions
        Root<Instructor> root = cq.from(Instructor.class);
        Join<Instructor, IdProof> insIdProofJoin = root.join(Instructor_.idProof);
        Join<Instructor, Vehicle> insVehicleJoin = root.join(Instructor_.vehicles);
        Join<Instructor, Student> insStudentJoin = root.join(Instructor_.students);
        Join<Student, Instructor> studentInsJoin = insStudentJoin.join(Student_.instructors);
        Join<Student, Vehicle> studentVehicleJoin = insStudentJoin.join(Student_.vehicles);
        Join<Vehicle, Document> vehicleDocumentJoin = studentVehicleJoin.join(Vehicle_.documents);

        // prepare select expressions.
        CompoundSelection<InstructorDto> selection = cb.construct(InstructorDto.class, root.get(Instructor_.id),
                root.get(Instructor_.name), root.get(Instructor_.fatherName), root.get(Instructor_.address),
                insIdProofJoin.get(IdProof_.proofNo), insVehicleJoin.get(Vehicle_.vehicleNumber),
                insVehicleJoin.get(Vehicle_.vechicleType), insStudentJoin.get(Student_.name),
                insStudentJoin.get(Student_.fatherName), studentInsJoin.get(Instructor_.name),
                studentVehicleJoin.get(Vehicle_.vehicleNumber), vehicleDocumentJoin.get(Document_.name));

        // prepare where expressions.
        Predicate instructorIdGreaterThan = cb.greaterThan(root.get(Instructor_.id), 2);
        Predicate documentNameIn = cb.in(vehicleDocumentJoin.get(Document_.name)).value("1").value("2");
        Predicate where = cb.and(instructorIdGreaterThan, documentNameIn);

        // prepare orderBy expressions.
        List<Order> orderBy = Arrays.asList(cb.asc(root.get(Instructor_.id)),
                cb.asc(insIdProofJoin.get(IdProof_.proofNo)), cb.asc(insVehicleJoin.get(Vehicle_.vehicleNumber)),
                cb.asc(studentInsJoin.get(Instructor_.name)), cb.asc(studentVehicleJoin.get(Vehicle_.vehicleNumber)),
                cb.asc(vehicleDocumentJoin.get(Document_.name)));

        // prepare query
        cq.select(selection).where(where).orderBy(orderBy);
        DataPrinters.listDataPrinter.accept(queryExecutor.fetchListForCriteriaQuery(cq));

    }

- Vaneet Kataria

Answer 3

您可以使用 multiselect 函数来完成此操作。

   CriteriaBuilder cb=session.getCriteriaBuilder();
            CriteriaQuery<Object[]> cquery=cb.createQuery(Object[].class);
            Root<Car> root=cquery.from(User.class);
            cquery.multiselect(root.get("id"),root.get("Name"));
            Query<Object[]> q=session.createQuery(cquery);
            List<Object[]> list=q.getResultList();
            System.out.println("id        Name");
            for (Object[] objects : list) {
                System.out.println(objects[0]+"        "+objects[1]);
             }
            

这在 Hibernate 5 中得到支持。createCriteria 在 Hibernate 的后续版本中已被弃用。因此，您可以使用criteria builder代替。

- Muskan Agarwal

---

你并不需要将它映射到一个Object[]数组中。我只是为我的实体添加了额外的构造函数，然后它就正确地重新映射了。 - downvoteit

Answer 4

我喜欢这种方法，因为它简单而干净：

    String getCompaniesIdAndName = " select "
            + " c.id as id, "
            + " c.name as name "
            + " from Company c ";

    @Query(value = getCompaniesWithoutAccount)
    Set<CompanyIdAndName> findAllIdAndName();

    public static interface CompanyIdAndName extends DTO {
        Integer getId();

        String getName();

    }

- tibi

Answer 5

你可以基于这个类映射另一个实体（你应该使用entity-name来区分两者），第二个实体将成为一种dto（不要忘记dto存在设计问题）。 你应该将第二个实体定义为只读，并给它一个好的名称，以便清楚地表明这不是常规实体。 顺便说一下，只选择几列被称为投影，所以用谷歌搜索会更容易。

另外，你可以创建一个带有所需字段列表的命名查询（将它们放在select中），或者使用带有投影的criteria。

- Avihai Marchiano