Using EXISTS in hibernate to return one row only for performance(without setMaxResult)
<p>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.</p>

<p>Table Person is (There is no Index on table)</p>

<pre><code>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
</code></pre>

<p>I used hibernate</p>

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

<p>Code to use is</p>

<pre><code>if(results.size &gt; 0)
{
   System.out.println("Error exists");
}
</code></pre>

<p>Problem is, it is very inefficient when reading large table.</p>

<p>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.</p>

<p>Or is setMaxResult only solution of that ?</p>

<p>Thanks</p>

<p>Aiden</p>