CODEWITHSUNDEEP
javaglassfishjpqlderby
Monash
Monash

Reputation: 37

How to create a "findByDate" NamedQuery in JPQL?

I'm trying to create a @NamedQuery for a web service so that one can return a list of customers that have a given date of birth.

GlassFish reports the following error:

Exception Description: Problem compiling [SELECT c FROM Customer c WHERE c.dob = to_date(:dob, 'dd-Mon-yyyy')]. [38, 45] The identification variable 'to_date' is not defined in the FROM clause.

@NamedQuery(name = "Customer.findByDob", query = "SELECT c FROM Customer c WHERE c.dob = to_date(:dob, 'dd-Mon-yyyy')")

@GET
    @Path("findByDob/{dob}")
    @Produces({"application/json"})
    public List<Customer> findByDob(@PathParam("dob") String dob) {
        Query q = em.createNamedQuery("Customer.findByDob");
        q.setParameter("dob", dob);
        return q.getResultList(); 
}

Any feedback is much appreciated.

Upvotes: 1

Views: 295

Answers (1)

JosemyAB
JosemyAB

Reputation: 407

You can delegate the transformation from Date to String database format in the entity manager. Something like this should works:

@NamedQuery(name = "Customer.findByDob", query = "SELECT c FROM Customer c WHERE c.dob = :dob")

        @GET
        @Path("findByDob/{dob}")
        @Produces({"application/json"})
        public List<Customer> findByDob(@PathParam("dob") String dob) {
            SimpleDateFormat sdf = new SimpleDateFormat("dd-Mon-yyyy");
            Date dobDate = sdf.parse(dob); //Create a new Java Date object. 

            Query q = em.createNamedQuery("Customer.findByDob");
            q.setParameter("dob", dobDate); //The entity manager will transform the Date to query needs.
            return q.getResultList(); 
    }

Upvotes: 1

Related Questions