5 Replies Latest reply on Apr 18, 2007 2:06 AM by scout1lacno

    Help on Named Query

    scout1lacno

      Hi all,

      Im having problem upon using LIKE with '%' statements on NamedQuery.

      Ordinary query using LIKE without '%'

      SQL
      select o.field1,o.field1 from TblMessages AS o WHERE o.CategoryID = '+CategoryID+' and o.date LIKE '+Date+' ORDER BY o.datReceived DESC ")
      


      Named Query
      
      @NamedQuery(name = "TblMessages.findMessage", query = "select o.field1,o.field1" +
       "from TblMessages AS o WHERE o.CategoryID = :CategoryID and " +
       "and o.date LIKE :date ORDER BY o.datReceived DESC ")
      
       Query queryTest = em.createNamedQuery("TblMessages.findMessages");
       queryTest.setParameter("CategoryID", CategoryID );
       queryTest.setParameter("date", date);
       List employees = queryTest.getResultList();
       return employees;


      The abovde code would work. But

      My question now is how to use the '%' on my named query.

      SQL
      select o.field1,o.field1 from TblMessages AS o WHERE o.CategoryID = '+CategoryID+' and o.date LIKE '+Date+%' ORDER BY o.datReceived DESC ")
      


      Name Query
      @NamedQuery(name = "TblMessages.findMessage", query = "select o.field1,o.field1" +
       "from TblMessages AS o WHERE o.CategoryID = :CategoryID and " +
       "and o.date LIKE :date ORDER BY o.datReceived DESC ")
      
      Query queryTest = em.createNamedQuery("TblMessages.findMessages");
       queryTest.setParameter("CategoryID", CategoryID );
       queryTest.setParameter("date", date);
       List employees = queryTest.getResultList();
       return employees;


      I have read that you can set the parameter as:
      queryTest.setParameter("string", "C%");
      this would work on strings..

      But what If I use dates or integers...
      date = 2007/01/02
      queryTest.setParameter("date", date+"%");

      Doea anyone there have some suggestions?

      Thanks in advance.

      :)


        • 1. Re: Help on Named Query
          ebross

          Hi scout1lacno,

          You have one additional 'and':

          CategoryID and " + "and o.date LIKE :date ORDER BY o.datReceived DESC")
          
          instead of:
          CategoryID" + "and o.date LIKE :date ORDER BY o.datReceived DESC")
          


          This looks good :

          Query queryTest = em.createNamedQuery("TblMessages.findMessages");
           queryTest.setParameter("CategoryID", CategoryID );
           queryTest.setParameter("date", date);
           List employees = queryTest.getResultList();
           return employees;
          

          But you can also do:

          Query queryTest = em.createNamedQuery("TblMessages.findMessages");
           queryTest.setParameter(1, CategoryID ); // the first parameter of the query
           queryTest.setParameter(2, date); // the second parameter of the query
           List employees = queryTest.getResultList();
           return employees;
          

          I hops it helps.

          • 2. Re: Help on Named Query
            elsieq

            Hi scout1lacno,

            I am having a named query issue and came across your post. Would you mind telling me where you defined your named query: with the entity or within your bean class?

            I defined my namedquery with the entity. I am using jboss-4.0.5 with EJB3 so I have not had to deal with mapping files or anything else.
            But I am getting a hibernate mapping exception "Named query not known".

            Any ideas at all?

            • 3. Re: Help on Named Query
              scout1lacno


              Hi elsieq,

              May you please post your code?


              FYI:
              On my previous code, it seems that you can only use '%' on strings.

              • 4. Re: Help on Named Query
                elsieq

                Hi again scout1lacno,

                Here is my entity with named query defined:

                @Entity
                @NamedQuery (name="Groupmember.findGroupsByMemberId", query="SELECT gm FROM Groupmember gm")
                @Table(name = "groupmember", catalog = "cmpe275db", uniqueConstraints = {})
                public class Groupmember implements java.io.Serializable {
                ...
                }


                And here is the relevant portion of my stateless session bean where I try to use the query:

                Query q = em.createNamedQuery
                ("Groupmember.findGroupsByMemberId");


                That is really all that I have done. I am wondering if this error has anything to do with my persistence.xml which simply looks like this:

                <?xml version="1.0" encoding="UTF-8"?>

                <persistence-unit name="cmpe275PU" transaction-type="JTA">
                <jta-data-source>java:/MySQLDS</jta-data-source>



                </persistence-unit>


                So I am not even dealing with using the % yet ... I am just trying to get a basic named query to work. Any help would be greatly appreciated.

                -elsieq

                • 5. Re: Help on Named Query
                  scout1lacno

                  Hi,

                  My code are the following

                  @Entity
                  @Table(name = "tblName")
                  @NamedQueries( {
                   @NamedQuery(name = "TblName.findFilteredDate", query = "select * from table" ),
                   @NamedQuery(name = "TblName.findStuff", query = "select * from tableStuff" )
                   })


                  On your code you have:

                  @NamedQuery (name="Groupmember.findGroupsByMemberId", query="SELECT gm FROM Groupmember gm")


                  Try putting @NamedQueries first and then you can use @NamedQuery.
                  refer to my code above