How to use an SQL Query with multiple joins and count with hibernate

Question

I have a Java Spring MVC web application which uses hibernate. I am trying to retrieve data from multiple tables using SQL join and also count of rows based on few conditions using a single query. The current SQL that I use is as below:

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;

This query returns the desired result for me. I am trying to use the same query as SQL Query in my method to get the results using hibernat as follows:

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

If I use the SQL query without a join, I get the correct result, but when the current query is used, I get the following exception:

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

I user Postgres database. Is there any way I can get the desired result as a list of objects or maybe a custom class. I am trying to use this single SQL since my database might contain thousands of rows of information and splitting this query will lead to so many database calls that will eventually slow down the system. Is there any way to execute an SQL query with multiple joins and count using hibernate.

score 4 · Accepted Answer · 2017-12-11 13:35:20Z

Because of the parentheses around your column list, your query only returns a single column which is an anonymous records containing the selected columns as fields.

In Postgres select (a,b) is something different then select a,b. The expression (a,b) (also known as "row constructor") creates an anonymous record with two fields.

Remove those useless parentheses and your query should work just fine. It's also a good idea to give your column expressions an alias:

Something like:

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;

Collectives™ on Stack Overflow

How to use an SQL Query with multiple joins and count with hibernate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related