Search for specific expression

Laurens · **Joined:** Wed Apr 05, 2006 3:19 pm **Posts:** 43

This is how I search a user by his username:

Code:

public User findByUserName(String userName) {
   return (User)getSession().createCriteria(User.class).add(Restrictions.eq("userName",userName)).uniqueResult();
}

Now I want to search for users with a specific part in the username OR a part in the email address. The case doesn't matter. I tried this, but to no avail:

Code:

@SuppressWarnings("unchecked")
public List<User> findByExpression(String expression, String order, boolean asc) {
   Order orderby = asc ? Order.asc(order) : Order.desc(order);
   return getSession().createCriteria(User.class).add(Restrictions.disjunction().add(Restrictions.like("userName", expression).ignoreCase()).add(Restrictions.like("email", expression))).addOrder(orderby).list();
}

How do I have to do this? Thanks in advance.

kochcp · **Posted:** Wed Apr 19, 2006 3:18 pm

hmm, looks like it should work. take out the orderby clause then try it again.

post the generated sql also

Laurens · **Joined:** Wed Apr 05, 2006 3:19 pm **Posts:** 43

If I type "Laurens" (full name), then he finds the user... but that's not what I want.

Code:

21:25:42,656 DEBUG SQL:346 - 
    select
        this_.userID as userID8_0_,
        this_.userName as userName8_0_,
        this_.password as password8_0_,
        this_.email as email8_0_,
        this_.firstName as firstName8_0_,
        this_.familyName as familyName8_0_,
        this_.regDate as regDate8_0_,
        this_.activeDate as activeDate8_0_,
        this_.ip as ip8_0_,
        this_.host as host8_0_,
        this_.up as up8_0_,
        this_.xp as xp8_0_,
        this_.money as money8_0_,
        this_.rankID as rankID8_0_,
        this_.teamID as teamID8_0_ 
    from
        users this_ 
    where
        (
            lower(this_.userName) like ? 
            or lower(this_.email) like ?
        ) 
    order by
        this_.userID asc
Hibernate: 
    select
        this_.userID as userID8_0_,
        this_.userName as userName8_0_,
        this_.password as password8_0_,
        this_.email as email8_0_,
        this_.firstName as firstName8_0_,
        this_.familyName as familyName8_0_,
        this_.regDate as regDate8_0_,
        this_.activeDate as activeDate8_0_,
        this_.ip as ip8_0_,
        this_.host as host8_0_,
        this_.up as up8_0_,
        this_.xp as xp8_0_,
        this_.money as money8_0_,
        this_.rankID as rankID8_0_,
        this_.teamID as teamID8_0_ 
    from
        users this_ 
    where
        (
            lower(this_.userName) like ? 
            or lower(this_.email) like ?
        ) 
    order by
        this_.userID asc
21:25:42,656 DEBUG AbstractBatcher:424 - preparing statement
21:25:42,656 DEBUG StringType:80 - binding 'lau' to parameter: 1
21:25:42,656 DEBUG StringType:80 - binding 'lau' to parameter: 2
21:25:42,656 DEBUG AbstractBatcher:327 - about to open ResultSet (open ResultSets: 0, globally: 0)
[GC 8920K->8045K(12708K), 0.0023798 secs]
21:25:42,656 DEBUG Loader:682 - processing result set
21:25:42,656 DEBUG Loader:709 - done processing result set (0 rows)
21:25:42,656 DEBUG AbstractBatcher:334 - about to close ResultSet (open ResultSets: 1, globally: 1)
21:25:42,656 DEBUG AbstractBatcher:319 - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
21:25:42,656 DEBUG AbstractBatcher:470 - closing statement
21:25:42,656 DEBUG Loader:839 - total objects hydrated: 0
21:25:42,671 DEBUG StatefulPersistenceContext:748 - initializing non-lazy collections
21:25:42,671 DEBUG ThreadLocalSessionContext:300 - allowing proxied method [getTransaction] to proceed to real session
21:25:42,671 DEBUG JDBCTransaction:103 - commit
21:25:42,671 DEBUG SessionImpl:332 - automatically flushing session
21:25:42,671 DEBUG JDBCContext:185 - before transaction completion
21:25:42,671 DEBUG SessionImpl:388 - before transaction completion
21:25:42,671 DEBUG JDBCTransaction:193 - re-enabling autocommit
21:25:42,671 DEBUG JDBCTransaction:116 - committed JDBC Connection
21:25:42,671 DEBUG JDBCContext:199 - after transaction completion
[GC 8941K->8029K(12708K), 0.0014494 secs]
21:25:42,671 DEBUG ConnectionManager:398 - aggressively releasing JDBC connection
21:25:42,671 DEBUG ConnectionManager:435 - releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
21:25:42,671 DEBUG SessionImpl:417 - after transaction completion
21:25:42,671 DEBUG SessionImpl:348 - automatically closing session
21:25:42,671 DEBUG SessionImpl:268 - closing session
21:25:42,671 DEBUG ConnectionManager:369 - connection already null in cleanup : no action

Something else: why does the connectionmanager complain about a connection that's already closed? Is that normal?

Laurens · **Joined:** Wed Apr 05, 2006 3:19 pm **Posts:** 43

Hi, I found a solution:

I added "MatchMode.ANYWHERE" at the end of the restriction.

I still wonder why it complains about closed connections. :)

kochcp · **Posted:** Wed Apr 19, 2006 3:39 pm

in sql, if you are searching by partial strings, for example lau to try to find lauren it will never work. you have to add wildcards, and in sql you need a %

so, you need to change the search expression from lau to lau%

I don't think that connection manager message is anything to worry about unless you are closing the connection manually as hibernate likes to do that. You should only ever be closing sessions.

Laurens · **Joined:** Wed Apr 05, 2006 3:19 pm **Posts:** 43

kochcp wrote:

in sql, if you are searching by partial strings, for example lau to try to find lauren it will never work. you have to add wildcards, and in sql you need a %

so, you need to change the search expression from lau to lau%

I don't think that connection manager message is anything to worry about unless you are closing the connection manually as hibernate likes to do that. You should only ever be closing sessions.

I think hibernate automatically adds % around the expression if I specify "MatchMode.ANYWHERE". Anyway, this is the way it works, so I'm happy. :) Thanks for trying to help.