CODEWITHSUNDEEP
javasqlsqlj
NielsInc
NielsInc

Reputation: 426

SQLJ IN-statement support

Is it possible to use the SQL IN statement using SQLJ?

I have tried to use the IN statement in combination with a List<String> or String[] to represent a collection of variables, but SQLJ says it can't parse the object.

Parameter object type is invalid for requested conversion.

This is my query in Native SQL: 

SELECT * 
FROM CARS_TBL 
WHERE CAR_BRAND IN ('AUDI', 'PEUGEOT');

In a Hibernate environment I would use the following query to achieve the result: 

List<String> brandList = new ArrayList<String>();
String hql = "SELECT car FROM Car car WHERE car.carBrand IN (:brandList);"

However, I have to use SQLJ and in the Oracle SQLJ documentation, it appears there is no Type support for Arrays or Collections to use in this way.

The SQLJ error is thrown in the following part of the generated code:

__sJT_stmt.setObject(2, brandList);

It tries to set the collection/array as an object, but since there is no support for List/array, an exception is thrown.

Do you know how I could achieve the same result as the native & hibernate query using SQLJ knowing that I could have a variable amount of "Car brands"?

Upvotes: 3

Views: 1084

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

Given that this cannot really be done with JDBC directly, and given that Oracle SQLJ doesn't natively support TABLE or VARRAY types, you could be successful by tricking ojdbc into accepting the following:

String[] brandList = ...
ArrayDescriptor d = 
    ArrayDescriptor.createDescriptor("ORA_MINING_VARCHAR2_NT", con);
ARRAY array = new ARRAY(d, con, brandList);

#sql {
    SELECT * FROM car WHERE car.carBrand IN (
        SELECT COLUMN_VALUE FROM TABLE(:array)
    );"
}

Where ORA_MINING_VARCHAR2_NT is a table type that is likely to be present on your Oracle installation. You can obviously replace it with something more appropriate.

See also: pass array to oracle procedure. Of course, there are other ways to embed SQL in Java, if SQLJ is not a must...

Upvotes: 2

Related Questions