CODEWITHSUNDEEP
javahqldao
Arem
Arem

Reputation: 35

Use SELECT DISTINCT in createQuery returning a list value

I can't add SELECT DISTINCT taxtCode from this code:

public List<TblTaxType> findAlltaxtCode(String bfnsCode) {
        List<TblTaxType> result = null;

        String hql = "select distinct(taxtCode) from TblTaxType tbl_tax_type WHERE bfnsCode = ?";
        try {

                setSession(HibernateUtil.getSession());

                @SuppressWarnings("unchecked")
                List <TblTaxType>  resultList = getSession().createQuery(hql)
                                                            .setString(0, bfnsCode)
                                                            .list();


            if(!resultList.isEmpty()){  
                result = resultList; 
                Debugger.print("TAX CODES FOUND ");
            }else{
                Debugger.print("TAX CODES NOT FOUND ");
            }
        } catch (Exception e) {
            e.printStackTrace();
            Debugger.print(" TAX CODES NOT FOUND ");

        }

        Debugger.print(hql);
        closeSession();
        return result;
    }

Updated into whole code. The query is right but it seems its not returning a list value. Still java.lang.String cannot be cast to com.test.test.TblTaxType error appearing. How this query returns a list of value? The error occurs whenever a word DISTINCT is added. Is it impossible in HQL to use a distinct and return a list of value like in SQL Query?

Upvotes: 1

Views: 5858

Answers (4)

Ashish Chaurasia
Ashish Chaurasia

Reputation: 1737

You can also use Criteria and Projection together :

Criteria criteria = session.createCriteria(MyEntity.class);
criteria.setProjection(Projections.distinct(Projections.property( "id" )));

Hope it help someone.

Upvotes: 1

engma
engma

Reputation: 1969

the right wat to do this with hibernate is 

select tbl_tax_type  FROM TblTaxType tbl_tax_type WHERE BFNS_CODE = ?

EDIT 2

to get specific list of distinct column use:

select distinct(taxtCode) from TblTaxType WHERE BFNS_CODE = ?

and in hibernate you don't have to use the first select statement to get a list, just use the from statement like this:

from TblTaxType where BFNS_CODE = ?

Upvotes: 0

Arem
Arem

Reputation: 35

Solved it using GROUP BY instead by using DISTINCT.

String hql = "FROM TblTaxType tbl_tax_type WHERE bfnsCode = ? GROUP BY taxtCode";

Upvotes: 1

user96546
user96546

Reputation: 57

When you start your query with FROM TblTaxType OR Select * it returns the table rows with all your table Columns and assigns it to the list with List <TblTaxType> DataType hence it doesn't give any error.

But when you write Select colName it returns only the string present in that table cell. Which it can't convert to java.lang.String cannot be cast to com.test.test.classes.TblTaxType

There is no Problem in Using Distinct in Hibernate. See here :- How do you create a Distinct query in HQL

Upvotes: 0

Related Questions