Pass varargs from Java code to SQL or PL/SQL

I need to bind at maximum 8 variables. Each one of them could be null. Is there any recommended way to achieve this? I know that开发者_如何转开发 I could simply check for null, but this seems tedious.

Additional details:

I'm going to call this sql from java code. It may be written using JPA 2.0 Criteria API, but most likely it's going to be a native query. The database is Oracle 10g, so I think I could make use of PL/SQL as well.

Edit1:

Maybe the title is a bit misleading, so I'll try to elaborate. The resulting SQL would be something like:

...

WHERE var1 = :var1

AND var2 = :var2

...

AND var = :var8

Now I need to bind parameters from java code in the way like:

nativeQuery.setParameter("var1", var1)

...

nativeQuery.setParameter("var8", var8)

Some parameters could be null, so there is no need to bind them. But I see no way I can omit them in SQL.

Edit2: I'm expecting to see SQL or PL/SQL procedure in your answers (if it's ever possible without null checking). In fact, all of these variables are of the same type. I think it's not possible to find a solution using ANSI SQL, but maybe there are some PL/SQL procedures which allow to work with varargs?

---

The use of a criteria query is appropriate in this case, because if I understood correctly, you need to construct the SQL query dynamically. If all the variables except var1 are null, the where clause would be

where var1 = :var1

and if all variables except var2 and var5 are non null you would have

where var2 = :var2 and var5 = :var5

Is that right?

If so, then do what you plan to do, and construct the query dynamically using a criteria query. Something like this must be done:

CriteriaBuilder builder = em.getCriteriaBuilder();
Predicate conjunction = builder.conjunction();
if (var1 != null) {
    conjunction = builder.and(conjunction,
                              builder.equal(root.get(MyEntity_.var1),
                                            var1));
}
if (var2 != null) {
    conjunction = builder.and(conjunction,
                              builder.equal(root.get(MyEntity_.var2),
                                            var2));
}
...
criteria.where(conjunction);

---

You don't specify the type of the objects you want to pass. So in this example I'm considering you will pass Object.

@Test(expected=IllegalArgumentException.class)
public void testMyMethod() {
    List<Object> testList = new ArrayList<Object>();
    testList.add("1");
    testList.add("2");
    testList.add(3);

    myMethod(testList);
}

public void myMethod(List<Object> limitedList) {
    final int MAX_SIZE = 2;
    if (limitedList.size() > MAX_SIZE) {
        throw new IllegalArgumentException("Size exceeded");
    }
    //my logic
}

In this example I'm passing the arguments as a List of Objects but you could use array (varargs) or another type of collection if you need to. If the client sends me more than the expected objects it will throw an IllegalArgumentException.

Also if you don't want to throw an exception you could just continue and iterate the list to bind the parameters but using the list size or MAX_SIZE as your limit. For example:

public void myMethod2(List<Object> limitedList) {
        final int MAX_SIZE = 2;

        int size = MAX_SIZE;
        if (limitedList.size() < MAX_SIZE) {
            size = limitedList.size();
        }

        //Iterate through the list
        for (int i = 0; i < size; i++) {
            Object obj = limitedList.get(i);
            //Logic to bind the obj to the criteria.            
        }
    }