Announcement Announcement Module
Collapse
No announcement yet.
Using 'like' in namedquery Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using 'like' in namedquery

    Hi,

    I'm very new to Spring and I'm trying to execute a query where one of the 3 parameters could be filled in or empty. So a user can enter a name, firstname and customerstreet. When one of the field is empty it shoud return more results.
    The query in DB2 looks like:

    Code:
    select * from soctar_from_supplier_new where name like 'ENGELS' and firstname like '%' and custstreet like '%'
    Searching for somebody with the name should result in 33 records.


    The name query is:
    Code:
    <query name="supplierPerson.findSupplierFromParameters"><![CDATA[from SupplierPerson where name like ? and firstName like ? and custStreet like ?]]></query>
    The DAO Hibernate implementation is:

    Code:
    public List findSupplierFromParameters(String name, String firstName, String custStreet) throws DAOException {
    		try{
    
    		Object values[] = new Object[3];
    			values[0] = name;
    			values[1] = firstName;
    			values[2] = custStreet;
    
    			return getHibernateTemplate().findByNamedQuery("supplierPerson.findSupplierFromParameters", values);
    	}
    Executing this query should give 33 result, but I'm getting 0

    Code:
    assertEquals(33, (supplierpersonDao.findSupplierFromParameters("ENGELS", "","")).size());
    I guess I have to replace empty strings with '%' I just don't get where or how...

  • #2
    It's better you to use Criteria API of Hibernate to fetch the data you need.

    Comment


    • #3
      Code:
      public List findSupplierFromParameters(String name, String firstName, String custStreet) throws DAOException {
      		try{
      
      		Object values[] = new Object[3];
                              if(name == null || name.equals("")){
      			   values[0] = "%";
                              else{
                                 values[0] = name;
                              }
                              ....
                              ....
      			return getHibernateTemplate().findByNamedQuery("supplierPerson.findSupplierFromParameters", values);
      	}
      OR

      Code:
      <query name="supplierPerson.findSupplierFromParameters"><![CDATA[from SupplierPerson where name like ? and (firstName like ? or ? is null)and (custStreet like ? or ? is null)]]></query>

      However I recommend you to use DetachedCriteria
      Last edited by aric; May 19th, 2009, 03:59 PM.

      Comment


      • #4
        Thanks for the suggestions!
        With the query I wrote there is nothing wrong and my thinking was ok, the problem I have now is that my application crashes when one of the columns is 'null'

        Is there a way to tell HibernateTemplate to ignore 'null' values or replace them with empty strings?

        Comment


        • #5
          Hold on, are you saying you can actually use "like ?" in a query in DB2? I thought that was impossible. I thought you had to have the expression (in place of the "?") hard-coded into the SQL and not passed in as a paramter.

          As for your problem, are you saying the value you pass in is null or the value you are comparing against in the database is null?

          Comment


          • #6
            Hold on, are you saying you can actually use "like ?" in a query in DB2? I thought that was impossible.
            I didn't know that like was impossible in DB2 (It's the first time I connect to a DB2, so I can't tell)

            As for your problem, are you saying the value you pass in is null or the value you are comparing against in the database is null?
            When of the fields in the database of the object I'm trying to retrieve is null I get this:

            Code:
            Caught java.io.CharConversionException.  See attached Throwable for details. ERRORCODE=-4220, SQLSTATE=null; nested exception is com.ibm.db2.jcc.a.SqlException: [jcc][t4][1065][12306][3.52.95] Caught java.io.CharConversionException.  See attached Throwable for details. ERRORCODE=-4220, SQLSTATE=null

            Comment


            • #7
              Ok, I'm making progress.

              The problem has nothing to do with hibernate or my code but the db2 jdbc driver seems to have problems with fields (in the database) that contain accented characters such as ,,, ...

              The DBVisualiser (tool that comes with DB2 as a GUI front-end) show fields with special characters as 'null' but consulting the database directly with the commandline on linux shows the accented values.

              The com.ibm.db2.jcc.DB2Driver seems to crash here....

              Comment

              Working...
              X