HQL: Using IN with dynamic input

lsacco · **Posted:** Thu Jul 13, 2006 10:09 pm

I am attempting to use HQL's IN functionality to query for a result set "in" a particular set. When I query using HQL I get no results back, but when I query with SQL, results are returned. I need to produce the IN set dynamically based on input into this method, so I have create a little string manip function to address the commas (and some attempt to play with the apostrophes too -- in the commented code below). Here's some code:

Code:

        list = agileItemDao.findByQuery("from AgileItem i join fetch i.itemRevs r where r.revNumber in (?) and i.itemNumber in (?)", new Object[] { createINParam(revisionNumbers), createINParam(itemNumbers) });

    private String createINParam(String[] strs) {
        StringBuffer sb = new StringBuffer(512);
        for (int i = 0; i < strs.length; i++) {
            String str = strs[i];
                sb.append(str);
//            else if (i == 0)
//                sb.append(str + "'");
//            else if (i + 1 == strs.length)
//                sb.append("'" + str);
//            else
//                sb.append("'" + str + "'");
            if (i + 1 < strs.length)
                sb.append(", ");
        }
        System.out.println("IN PARMS = " + sb.toString());
        return sb.toString();
    }

After reviewing the output from debug, I'm not sure why hibernate won't pick up my parameters properly (see below). I take the query outputed along with the string parameters and it returns 4 results in my SQL tool.

Has anyone else had success building up on IN set to query?

Thanks,
Lou

Hibernate version:
3.1.1

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:
Oracle 9.2

The generated SQL (show_sql=true):
Hibernate: select agileitem0_.ID as ID5_0_, itemrevs1_.ID as ID6_1_, agileitem0_.CLASS as CLASS5_0_, agileitem0_.SUBCLASS as SUBCLASS5_0_, agileitem0_.ITEM_NUMBER as ITEM4_5_0_, agileitem0_.CATEGORY as CATEGORY5_0_, agileitem0_.DESCRIPTION as DESCRIPT6_5_0_, agileitem0_.DOCSIZE as DOCSIZE5_0_, agileitem0_.MODIFYDATE as MODIFYDATE5_0_, agileitem0_.OBJVERSION as OBJVERSION5_0_, agileitem0_.DELETE_FLAG as DELETE10_5_0_, agileitem0_.PRODUCT_LINES as PRODUCT11_5_0_, agileitem0_.FLAGS as FLAGS5_0_, agileitem0_.DEFAULT_CHANGE as DEFAULT13_5_0_, agileitem0_.COMMODITY as COMMODITY5_0_, agileitem0_.ENCODE_NAME as ENCODE15_5_0_, agileitem0_.PART_FAMILY as PART16_5_0_, agileitem0_.CONV_FACTOR as CONV17_5_0_, itemrevs1_.REV_NUMBER as REV2_6_1_, itemrevs1_.OLD_REVNUMBER as OLD3_6_1_, itemrevs1_.OBSOLETE_DATE as OBSOLETE4_6_1_, itemrevs1_.EFFECTIVE_DATE as EFFECTIVE5_6_1_, itemrevs1_.FUNCTION_ID as FUNCTION6_6_1_, itemrevs1_.INCORP_DATE as INCORP7_6_1_, itemrevs1_.LOC06 as LOC8_6_1_, itemrevs1_.LOC07 as LOC9_6_1_, itemrevs1_.LOC08 as LOC10_6_1_, itemrevs1_.LOC09 as LOC11_6_1_, itemrevs1_.LOC10 as LOC12_6_1_, itemrevs1_.DATE01 as DATE13_6_1_, itemrevs1_.DATE02 as DATE14_6_1_, itemrevs1_.DATE03 as DATE15_6_1_, itemrevs1_.DATE04 as DATE16_6_1_, itemrevs1_.DATE05 as DATE17_6_1_, itemrevs1_.TEXT01 as TEXT18_6_1_, itemrevs1_.TEXT02 as TEXT19_6_1_, itemrevs1_.TEXT03 as TEXT20_6_1_, itemrevs1_.TEXT04 as TEXT21_6_1_, itemrevs1_.TEXT05 as TEXT22_6_1_, itemrevs1_.LIST01 as LIST23_6_1_, itemrevs1_.LIST03 as LIST24_6_1_, itemrevs1_.LIST04 as LIST25_6_1_, itemrevs1_.LIST05 as LIST26_6_1_, itemrevs1_.LIST06 as LIST27_6_1_, itemrevs1_.LIST07 as LIST28_6_1_, itemrevs1_.LIST08 as LIST29_6_1_, itemrevs1_.LIST09 as LIST30_6_1_, itemrevs1_.LIST10 as LIST31_6_1_, itemrevs1_.RELEASED as RELEASED6_1_, itemrevs1_.INCORPORATED as INCORPO33_6_1_, itemrevs1_.LATEST_FLAG as LATEST34_6_1_, itemrevs1_.RELEASE_DATE as RELEASE35_6_1_, itemrevs1_.DATE06 as DATE36_6_1_, itemrevs1_.DATE07 as DATE37_6_1_, itemrevs1_.DATE08 as DATE38_6_1_, itemrevs1_.DATE09 as DATE39_6_1_, itemrevs1_.DATE10 as DATE40_6_1_, itemrevs1_.DATE11 as DATE41_6_1_, itemrevs1_.DATE12 as DATE42_6_1_, itemrevs1_.DATE13 as DATE43_6_1_, itemrevs1_.DATE14 as DATE44_6_1_, itemrevs1_.DATE15 as DATE45_6_1_, itemrevs1_.DATE16 as DATE46_6_1_, itemrevs1_.DATE17 as DATE47_6_1_, itemrevs1_.DATE18 as DATE48_6_1_, itemrevs1_.DATE19 as DATE49_6_1_, itemrevs1_.DATE20 as DATE50_6_1_, itemrevs1_.LIST11 as LIST51_6_1_, itemrevs1_.LIST12 as LIST52_6_1_, itemrevs1_.LIST13 as LIST53_6_1_, itemrevs1_.LIST14 as LIST54_6_1_, itemrevs1_.LIST15 as LIST55_6_1_, itemrevs1_.LIST16 as LIST56_6_1_, itemrevs1_.LIST17 as LIST57_6_1_, itemrevs1_.LIST18 as LIST58_6_1_, itemrevs1_.LIST19 as LIST59_6_1_, itemrevs1_.LIST20 as LIST60_6_1_, itemrevs1_.LIST21 as LIST61_6_1_, itemrevs1_.LIST22 as LIST62_6_1_, itemrevs1_.LIST23 as LIST63_6_1_, itemrevs1_.LIST24 as LIST64_6_1_, itemrevs1_.LIST25 as LIST65_6_1_, itemrevs1_.FLAGS as FLAGS6_1_, itemrevs1_.TEXT06 as TEXT67_6_1_, itemrevs1_.TEXT07 as TEXT68_6_1_, itemrevs1_.TEXT08 as TEXT69_6_1_, itemrevs1_.TEXT09 as TEXT70_6_1_, itemrevs1_.TEXT10 as TEXT71_6_1_, itemrevs1_.TEXT11 as TEXT72_6_1_, itemrevs1_.TEXT12 as TEXT73_6_1_, itemrevs1_.TEXT13 as TEXT74_6_1_, itemrevs1_.TEXT14 as TEXT75_6_1_, itemrevs1_.TEXT15 as TEXT76_6_1_, itemrevs1_.SITE as SITE6_1_, itemrevs1_.NUMERIC01 as NUMERIC78_6_1_, itemrevs1_.NUMERIC02 as NUMERIC79_6_1_, itemrevs1_.NUMERIC03 as NUMERIC80_6_1_, itemrevs1_.NUMERIC04 as NUMERIC81_6_1_, itemrevs1_.NUMERIC05 as NUMERIC82_6_1_, itemrevs1_.DESCRIPTION as DESCRIP83_6_1_, itemrevs1_.MONEYVALUE01 as MONEYVALUE84_6_1_, itemrevs1_.MONEYVALUE02 as MONEYVALUE85_6_1_, itemrevs1_.MONEYVALUE03 as MONEYVALUE86_6_1_, itemrevs1_.MONEYVALUE04 as MONEYVALUE87_6_1_, itemrevs1_.MONEYVALUE05 as MONEYVALUE88_6_1_, itemrevs1_.MONEYCURRENCY01 as MONEYCU89_6_1_, itemrevs1_.MONEYCURRENCY02 as MONEYCU90_6_1_, itemrevs1_.MONEYCURRENCY03 as MONEYCU91_6_1_, itemrevs1_.MONEYCURRENCY04 as MONEYCU92_6_1_, itemrevs1_.MONEYCURRENCY05 as MONEYCU93_6_1_, itemrevs1_.WEIGHT as WEIGHT6_1_, itemrevs1_.NORMALIZED_WEIGHT as NORMALIZED95_6_1_, itemrevs1_.WEIGHT_UOM as WEIGHT96_6_1_, itemrevs1_.NOTES01 as NOTES97_6_1_, itemrevs1_.ITEM as ITEM6_1_, itemrevs1_.RELEASE_TYPE as RELEASE99_6_1_, itemrevs1_.OLD_RELEASE_TYPE as OLD100_6_1_, itemrevs1_.CHANGE as CHANGE6_1_, itemrevs1_.LOC01 as LOC102_6_1_, itemrevs1_.LOC02 as LOC103_6_1_, itemrevs1_.LOC03 as LOC104_6_1_, itemrevs1_.LOC04 as LOC105_6_1_, itemrevs1_.LOC05 as LOC106_6_1_, itemrevs1_.LIST02 as LIST107_6_1_, itemrevs1_.ITEM as ITEM0__, itemrevs1_.ID as ID0__ from agile.ITEM agileitem0_, agile.REV itemrevs1_ where agileitem0_.ID=itemrevs1_.ITEM and (itemrevs1_.REV_NUMBER in (?)) and (agileitem0_.ITEM_NUMBER in (?))

Debug level Hibernate log excerpt:
15:01:55,733 DEBUG HQLQueryPlan:148 - find: from AgileItem i join fetch i.itemRevs r where r.revNumber in (?) and i.itemNumber in (?)
15:01:55,749 DEBUG QueryParameters:256 - parameters: [X1, X1, X1, X1, 25-V-1, MP25-V-1, 20-V2, 25-V]
15:01:55,749 DEBUG QueryParameters:262 - named parameters: {}
15:01:55,764 DEBUG AbstractBatcher:311 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
15:01:56,654 DEBUG StringType:79 - binding 'X1, X1, X1, X1' to parameter: 1
15:01:56,670 DEBUG StringType:79 - binding '25-V-1, MP25-V-1, 20-V2, 25-V]' to parameter: 2
15:01:56,779 DEBUG AbstractBatcher:327 - about to open ResultSet (open ResultSets: 0, globally: 0)
15:01:56,795 DEBUG Loader:682 - processing result set
15:01:56,810 DEBUG Loader:709 - done processing result set (0 rows)[/code]

lsacco · **Posted:** Thu Jul 13, 2006 10:16 pm

Here's the output with an attempt to try to put the apostrophe's in the right spot:

Result:

Code:

13:04,880 DEBUG HQLQueryPlan:148 - find: from AgileItem i join fetch i.itemRevs r where r.revNumber in (?) and i.itemNumber in (?)
13:04,896 DEBUG QueryParameters:256 - parameters: ['X1', 'X1', 'X1', 'X1', '25-V2553-1', 'MP25-V2553-1', '20-V2553', '25-V2553']
*** SQL STATEMENT THE SAME AS LAST POST ***
13:04,896 DEBUG AbstractBatcher:424 - preparing statement
13:05,083 DEBUG StringType:79 - binding ''X1', 'X1', 'X1', 'X1'' to parameter: 1
13:05,083 DEBUG StringType:79 - binding ''25-V2553-1', 'MP25-V2553-1', '20-V2553', '25-V2553'' to parameter: 2
13:05,177 DEBUG AbstractBatcher:327 - about to open ResultSet (open ResultSets: 0, globally: 0)
13:05,177 DEBUG Loader:682 - processing result set
13:05,177 DEBUG Loader:709 - done processing result set (0 rows)

Code:

Code:

    private String createINParam(String[] strs) {
        StringBuffer sb = new StringBuffer(512);
        for (int i = 0; i < strs.length; i++) {
            String str = strs[i];
            if (strs.length == 1)
                sb.append(str);
            else if (i == 0)
                sb.append("'" +str + "'");
            else if (i + 1 == strs.length)
                sb.append("'" + str);
            else
                sb.append("'" + str + "'");
            if (i + 1 < strs.length)
                sb.append(", ");
            else 
                sb.append("'");
        }
        System.out.println("IN PARMS = " + sb.toString());
        return sb.toString();
    }

bonnyr · **Posted:** Fri Jul 14, 2006 8:04 am

Lou,

You should avoid performing String manipulation for queries. Instead
you can use the setParameter method which accepts an Object[] as the
value. This way Hibernate will expand the set of objects automatically
for you and format them properly without you needing to worry about
quotes etc.
Here is an excerpt from Query.java:

Code:

   /**
    * Bind multiple values to a named query parameter. This is useful for binding
    * a list of values to an expression such as <tt>foo.bar in (:value_list)</tt>.
    * @param name the name of the parameter
    * @param vals a collection of values to list
    * @param type the Hibernate type of the values
    */
   public Query setParameterList(String name, Collection vals, Type type) throws HibernateException;

Hope this helps

lsacco · **Posted:** Fri Jul 14, 2006 5:00 pm

Bonnie,

Thanks for the helpful info. That helped improved my underlying DAO API too!

Best,
Lou