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;
        }

Answer 1

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

1. 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.

2. 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().