[hibernate-issues] [Hibernate-JIRA] Created: (HHH-5161) JPA 2.0 criteria queries do not use parameters for numeric fields

[Tarek Nabil (JIRA)]

JPA 2.0 criteria queries do not use parameters for numeric fields
-----------------------------------------------------------------

                 Key: HHH-5161
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5161
             Project: Hibernate Core
          Issue Type: Bug
          Components: entity-manager
    Affects Versions: 3.5.0-Final
         Environment: Hibernate 3.5.0 with Oracle DB.
            Reporter: Tarek Nabil
         Attachments: param-bug.zip

Using the new JPA 2.0 criteria API to create a query whose where clause consists of one or more predicates, where one of those predicates compares a numeric (int or long) field in an entity to a certain value, the generated query uses the compared value literally in the query where it should have used a parameter.

The attached test case illustrates the problem. It's a minimal Maven2 project which contains one domain class and one DAO.

The domain class maps to the REGION entity from the Oracle HR schema which ships with any Oracle XE installation. I still included the script needed to create the table incase the schema is not available on the test environment.

The DAO has one method which creates a query that selects a region by id and then extracts the set of parameters from the generated TypedQuery. The method then returns the size of the set to the test method which fails if the size is not greater than zero.

The DAO method also executes the query and since Hibernate is configured to show the generated SQL, the following query is shown in standard output:

    select
        region0_.REGION_ID as REGION1_0_,
        region0_.REGION_NAME as REGION2_0_ 
    from
        REGIONS region0_ 
    where
        region0_.REGION_ID=1

Where the correct query should have been:

    select
        region0_.REGION_ID as REGION1_0_,
        region0_.REGION_NAME as REGION2_0_ 
    from
        REGIONS region0_ 
    where
        region0_.REGION_ID=?

IMO, this is a major issue as it can have a severe impact on on Oracle DB performance as it will have to hard parse the query every time it's used with a different value.

I've provided instructions on the minimal changes required to setup the Maven project in order to run the test case in "readme.txt".


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira