Java Hibernate / Spring, doing partial match query ("contains")?

Question

I'm still fairly new to hibernate and can't seem to find how to do this, maybe I'm searching using the wrong terminology. I am passing in a user provided value from my UI, in the example below this is the variable testvalue. It is for doing a search so if what the user enters is contained anywhere in an entry e.filenametxt I want it to return that row as a match.

For example, testvalue = "file1", I would want it to be able to find a row where e.filenametxt = "file1/someotherinfo/"

String SQL_QUERY = "from EdrmTest e where e.filenametxt is :foreignkeytest ";
Query query = session.createQuery(SQL_QUERY);
query.setParameter("foreignkeytest", testvalue);

Any advice is appreciated.

Answer 1

String SQL_QUERY = "from EdrmTest e where e.filenametxt LIKE :foreignkeytest ";
Query query = session.createQuery(SQL_QUERY);
query.setParameter("foreignkeytest", "%" + testvalue + "%");\

Try that. The wild cards need to be in the variable (for some reason).

Answer 2

You can also use the Criteria API to set these types of clauses programatically; sometimes it can be much nicer to handle queries with this API as opposed to a HQL/String based query if you need to add any sort of dynamic logic (i.e. include certain clauses based on a condition).

Criteria crit = session.createCriteria(EdrmTest.class); //the persistent class goes here
crit.add( Restrictions.like("filenametxt", testvalue + "%") );
//execute the query:
List<EdrmTest> results = (List<EdrmTest>) crit.list();