如何在Hibernate DAO实现中使用SELECT查询？附关联查询SQL示例

嘿，我来帮你搞定这个需求！你给出的SQL是要查询八年级学生对应的监护人信息，关联了三个表对吧？下面我给你三种在Hibernate DAO里实现的常用方式，按需选择就行：

方法1：使用HQL（Hibernate Query Language） #

HQL是Hibernate的面向对象查询语言，比原生SQL更贴合实体类设计。首先假设你已经有了对应的实体类：

• RtsGuardian：映射rts_guardian表，属性包括guardianId、guardianName、guardianEmail
• RtsWard：映射rts_ward表，属性包括wardId、garde
• RtsWrdgrdJunction：映射rts_wrdgrd_junction表，关联guardianId和wardId

如果你的实体类已经配置了关联关系（比如RtsWard和RtsGuardian是多对多，通过中间表关联），那HQL可以简化很多：

public List<Object[]> getGuardiansForGradeVIII() {
    Session session = HibernateUtil.getSessionFactory().openSession();
    try {
        String hql = "SELECT g.guardianName, g.guardianId, g.guardianEmail " +
                     "FROM RtsWard w " +
                     "JOIN w.guardians g " +
                     "WHERE w.garde = 'VIII'";
        Query query = session.createQuery(hql);
        return query.list();
    } finally {
        session.close();
    }
}

如果没配置关联关系，也可以直接关联三个实体：

public List<Object[]> getGuardiansForGradeVIII() {
    Session session = HibernateUtil.getSessionFactory().openSession();
    try {
        String hql = "SELECT g.guardianName, g.guardianId, g.guardianEmail " +
                     "FROM RtsGuardian g, RtsWard w, RtsWrdgrdJunction junct " +
                     "WHERE g.guardianId = junct.guardianId " +
                     "AND w.wardId = junct.wardId " +
                     "AND w.garde = 'VIII'";
        Query query = session.createQuery(hql);
        return query.list();
    } finally {
        session.close();
    }
}

返回的Object[]里，索引0是监护人姓名，1是ID，2是邮箱。如果你想更优雅，可以创建一个GuardianDTO类，用HQL的SELECT NEW语法直接映射：

public List<GuardianDTO> getGuardiansForGradeVIII() {
    Session session = HibernateUtil.getSessionFactory().openSession();
    try {
        String hql = "SELECT NEW com.yourpackage.GuardianDTO(g.guardianName, g.guardianId, g.guardianEmail) " +
                     "FROM RtsWard w " +
                     "JOIN w.guardians g " +
                     "WHERE w.garde = 'VIII'";
        Query<GuardianDTO> query = session.createQuery(hql, GuardianDTO.class);
        return query.list();
    } finally {
        session.close();
    }
}

记得GuardianDTO要有对应的构造函数哦。

方法2：使用Criteria API（类型安全查询） #

Criteria API是编译期类型安全的，能避免HQL的字符串拼写错误，适合复杂查询场景：

public List<GuardianDTO> getGuardiansForGradeVIII() {
    Session session = HibernateUtil.getSessionFactory().openSession();
    try {
        CriteriaBuilder cb = session.getCriteriaBuilder();
        CriteriaQuery<GuardianDTO> cq = cb.createQuery(GuardianDTO.class);
        
        // 定义查询的根和关联
        Root<RtsWard> wardRoot = cq.from(RtsWard.class);
        Join<RtsWard, RtsGuardian> guardianJoin = wardRoot.join("guardians");
        
        // 设置投影（要查询的字段）
        cq.select(cb.construct(GuardianDTO.class,
                               guardianJoin.get("guardianName"),
                               guardianJoin.get("guardianId"),
                               guardianJoin.get("guardianEmail")))
          .where(cb.equal(wardRoot.get("garde"), "VIII"));
        
        Query<GuardianDTO> query = session.createQuery(cq);
        return query.list();
    } finally {
        session.close();
    }
}

这里同样依赖实体类的关联关系，如果没有配置关联，也可以手动关联三个表，不过稍微繁琐一点。

方法3：使用原生SQL（复用你给出的SQL） #

如果就想直接用你写好的原生SQL，Hibernate完全支持原生SQL查询：

public List<Object[]> getGuardiansForGradeVIII() {
    Session session = HibernateUtil.getSessionFactory().openSession();
    try {
        String sql = "select rg.guardian_name, rg.guardian_id, rg.guardian_email " +
                     "from rts_guardian rg, rts_ward rw, rts_wrdgrd_junction junct " +
                     "where rg.guardian_id = junct.guardian_id " +
                     "and rw.ward_id=junct.ward_id " +
                     "and rw.garde='VIII'";
        NativeQuery query = session.createNativeQuery(sql);
        return query.list();
    } finally {
        session.close();
    }
}

同样，也可以映射到DTO，需要给SQL字段起别名对应DTO属性：

public List<GuardianDTO> getGuardiansForGradeVIII() {
    Session session = HibernateUtil.getSessionFactory().openSession();
    try {
        String sql = "select rg.guardian_name as guardianName, rg.guardian_id as guardianId, rg.guardian_email as guardianEmail " +
                     "from rts_guardian rg, rts_ward rw, rts_wrdgrd_junction junct " +
                     "where rg.guardian_id = junct.guardian_id " +
                     "and rw.ward_id=junct.ward_id " +
                     "and rw.garde='VIII'";
        NativeQuery<GuardianDTO> query = session.createNativeQuery(sql, GuardianDTO.class);
        // 可选：用addScalar明确指定字段类型映射
        query.addScalar("guardianName", StringType.INSTANCE)
             .addScalar("guardianId", LongType.INSTANCE)
             .addScalar("guardianEmail", StringType.INSTANCE);
        return query.list();
    } finally {
        session.close();
    }
}

内容的提问来源于stack exchange，提问作者Satish Mishra US