Aiden Aiden - 1 year ago 67
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 1/Harton st 1 1
2 John 2 2/Harton st 2 2
3 Peter 1123 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 ?



Answer Source

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

FROM Person p
GROUP BY p.PersonName

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download