Hibernate comparing value(or parts of) to 2 columns

Question

I have 2 columns name: name, surname I have a search value called search. I want to be able to search on the fullname for example: john doe. (with the space). So far I got it working with only name, or surname by typing in john or doe.

This is what I tried for the fullname:

Criterion fullname = Restrictions.and(Restrictions.like("name",  search).ignoreCase(),
                Restrictions.like("surname", search).ignoreCase());

Full part:

 Private List<Users> getSearch(String search) {
            List<Users> users = new ArrayList<Users>();
            Session dbSession = HibernateUtil.getSessionFactory().getCurrentSession();
            try {
                Transaction tx = dbSession.beginTransaction();
                Criteria cr = dbSession.createCriteria(Users.class);
                Criterion name = Restrictions.or(Restrictions.like("name", search).ignoreCase(),
                Restrictions.like("surname", search).ignoreCase());
                Criterion fullname = Restrictions.and(Restrictions.like("name",  search).ignoreCase(),
                    Restrictions.like("surname", search).ignoreCase());         

                Disjunction disjunction = Restrictions.disjunction();
                disjunction.add(name);
                disjunction.add(fullname);
                cr.add(disjunction);
                users = (List<Users>) cr.list();
                tx.commit();
            } catch (HibernateException he) {
            }
            return users;
        }

To clarify: you want your users to be able to search and find the same "John Doe" when they type "John D", "John", "Doe"? Can they separate the name parts by anything else than whitespace? — mabi, Dec 16 '13 at 14:57
Well now they can find the user by either typing in john or doe, i want them to be able to find the user by typing in john doe. No just whitespace would do. — DaViDa, Dec 16 '13 at 14:59

score 1 · Accepted Answer · answered Dec 16 '13 at 15:12

Well, that's not easily possible. You have two options:

Concatenate the fields in the database and search over that. Not really sure how to do this using JPA/Hibernate. It's the fastest method, but you'd need raw SQL for this.
Select all name fields from the database, concatenate them and do the matching in java.

You may have a third option when you can split the input (based on whitespace?) and search each field for all of the input parts, but this gets you into all sorts of problems: what if your user inputs "John Doe" but you have a "Doe, John"? What if someone expects to find a person using her middle name? What if somebody doesn't have a surname?

To sum it up: fetch all name fields from the database, join them and search the list of strings with String#contains().

Hibernate comparing value(or parts of) to 2 columns

1 Answers1