Aiden Aiden - 7 months ago 8
SQL Question

Using EXISTS in hibernate to return one row only for performance(without setMaxResult)

I have to print a message if ANY duplicate row with same ID (not a primary KEY) exists in table (very large table), that meets a where clause.

Table Person is (There is no Index on table)

PersonPrimaryKEY PersonName ID Email Address InvoiceID TaxID
1 Bob 1 bob@example.com 1/Harton st 1 1
2 John 2 john@example.com 2/Harton st 2 2
3 Peter 1123 peter@example.com 3/Harton st 3 3


I used hibernate

public Collection<Person> readByNameAndID (String name, String ID)
{
TypedQuery<Person> q = getEntityManager ().createNamedQuery("Select p FROM Person p WHERE Name =:Name AND ID <> :ID", Person.class);
q.setParameter ("Name", Name);
q.setParameter ("ID", ID);
return q.getResultList ();
}


Code to use is

if(results.size > 0)
{
System.out.println("Error exists");
}


Problem is, it is very inefficient when reading large table.

How can I make it very efficient ? I was thinking of using EXISTS or COUNT to do that but how to incorporate it with hibernate so that it returns only ONE row then I check size > 0, which will be efficient.

Or is setMaxResult only solution of that ?

Thanks

Aiden

Answer

Since you are considering records duplicate on the basis of having the same PersonName, the following HQL query should do the trick:

SELECT COUNT(p)
FROM Person p
GROUP BY p.PersonName
HAVING COUNT(p) > 1

If the count from this query is greater than 0, it means you have duplicates present.

Comments