java - 如何使用具有多个连接的 SQL 查询并使用 hibernate 计数

Question

我有一个使用 hibernate 的 Java Spring MVC 网络应用程序。我正在尝试使用 SQL join 从多个表中检索数据，并尝试使用单个查询根据几个条件计算行数。我目前使用的 SQL 如下:

SELECT (s.school_id, u.first_name, u.last_name, u.username, u.email, p.plan_name, s.start_date, s.end_date,(SELECT COUNT(*) FROM c_school_user t WHERE t.user_type = 'TA' and t.school_id = s.school_id), (SELECT COUNT(*) FROM c_school_user t WHERE t.user_type = 'PA' and t.school_id = s.school_id), (SELECT COUNT(*) FROM c_school_user t WHERE t.user_type = 'ST' and t.school_id = s.school_id)) FROM c_school s inner join u_user u on s.user_created = u.user_id inner join c_plan p on s.current_plan = p.plan_Id where s.application_id = 1 and s.site_id = 1;

此查询返回我想要的结果。我正在尝试在我的方法中使用与 SQL 查询相同的查询来使用 hibernat 获取结果，如下所示:

public List<Object[]> getBuyersInformation(int applicationId, int siteId) throws HibernateException 
{

    Session session = getCurrentSession();
    Query query = session.createSQLQuery("SELECT (s.school_id, u.first_name, u.last_name, u.username, u.email, p.plan_name, s.start_date, s.end_date, s.subscription_price, (SELECT COUNT(*) FROM c_school_user t WHERE t.user_type = 'TA' and t.school_id = s.school_id), (SELECT COUNT(*) FROM c_school_user t WHERE t.user_type = 'PA' and t.school_id = s.school_id), (SELECT COUNT(*) FROM c_school_user t WHERE t.user_type = 'ST' and t.school_id = s.school_id)) FROM c_school s inner join u_user u WHERE s.user_created = u.user_id inner join c_plan p WHERE s.current_plan = p.plan_Id where s.application_id = :applicationId and s.site_id = :siteId");
    query.setParameter("applicationId", applicationId);
    query.setParameter("siteId", siteId);
    List<Object[]> results = query.list();
    if(results != null && results.size() > 0)
    {
    return results;
    }
    return null;
}

如果我使用没有连接的 SQL 查询，我会得到正确的结果，但是当使用当前查询时，我会得到以下异常:

org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

我使用 Postgres 数据库。有什么方法可以作为对象列表或自定义类获得所需的结果。我正在尝试使用这个单一的 SQL，因为我的数据库可能包含数千行信息并且拆分此查询将导致如此多的数据库调用，最终会降低系统速度。有什么方法可以使用 hibernate 执行具有多个连接和计数的 SQL 查询。

Answer 1

由于列列表周围的括号，您的查询仅返回一个单个列，该列是包含所选列作为字段的匿名记录。

在 Postgres 中，select (a,b) 与 select a,b 不同。表达式 (a,b)(也称为“行构造函数”)creates an anonymous record有两个字段。

删除那些无用的括号，您的查询应该可以正常工作。给你的列表达式一个别名也是一个好主意:

类似于:

SELECT s.school_id, u.first_name, u.last_name, u.username, u.email, p.plan_name, s.start_date, s.end_date,
       (SELECT COUNT(*) FROM c_school_user t WHERE t.user_type = 'TA' and t.school_id = s.school_id) as ta_count, 
       (SELECT COUNT(*) FROM c_school_user t WHERE t.user_type = 'PA' and t.school_id = s.school_id) as pa_count, 
       (SELECT COUNT(*) FROM c_school_user t WHERE t.user_type = 'ST' and t.school_id = s.school_id) as st_count
FROM c_school s 
  inner join u_user u on s.user_created = u.user_id 
  inner join c_plan p on s.current_plan = p.plan_Id 
where s.application_id = 1 and s.site_id = 1;