Any alternatives to JDBC setArray() method?

Question

I am using this code but my JDBC driver does not support setArray() method. Is there any alternative way?

Class.forName(DRIVER);
    conn = DriverManager.getConnection(URL,USER,PSW); 
    String[] serviceIdarray = new String[serviceIdList.size()];
        for(int i=0;i<serviceIdList.size();i++){
            serviceIdarray[i]=serviceIdList.get(i);
        }
    Array serviceId  =conn.createArrayOf("VARCHAR",serviceIdarray );

    if(UsmConstants.CURRENT_RUN_VALUE.equalsIgnoreCase(runType)){
        if(userRole!=null && UsmConstants.ADMIN_UPPERCASE.equalsIgnoreCase(userRole)){
        if(businessID.equalsIgnoreCase("IT")){
            queryString = "SELECT DISTINCT avc.AV_CMPLNC_TRXN_ID as avCompId, decode(avc.AV_COMPLIANCE_FLG,'Y','Yes','N','No') as avComplianceFlag, decode(avc.AV_INSTALLED_FLG,'Y','Yes','N','No') as avInstalledFlag, avc.SCAN_DATE as scanDate, avc.RESOLVED_DATE as resolvedDate, decode(avc.OTC_STATUS,'OOT','Open On Time','CLT','Close Late','OLT','Open Late','COT','Close On Time') as otcStatus, AVC.FIRST_DETECTED_DATE as reportedDate,avc.COMMENTS as comments, sm.SERVICE_NAME as serviceName, hm.HOST_NAME as hostName, hm.HOST_IP_ADDRESS as hostIPAddress " +
                "from T_USM_SERVICE_OFFERING_MASTER som,T_USM_APPL_SRVC_OFFRNG_MAP asom,T_USM_HOST_APPL_MAP ham,T_USM_HOST_MASTER hm,T_USM_AV_COMPLIANCE_TRXN avc,T_USM_SERVICE_MASTER sm " +
                "where avc.host_id = hm.host_id " +
                "AND avc.host_id = ham.host_id " +
                "AND HAM.APPLICATION_ID = ASOM.APPLICATION_ID " +
                "AND ASOM.SERVICE_OFFERING_ID = SOM.SERVICE_OFFERING_ID " +
                "AND ASOM.END_DATE IS NULL " +
                "AND HAM.END_DATE IS NULL " +
                "AND som.service_id = sm.service_id " +
                "AND " +itCase+
                "AND sm.service_id IN(?) " +
                "AND trunc(avc.SCAN_DATE) = to_date(?) " +
                "AND sm.STATUS = 'O' AND sm.RETIRED_DATE IS NULL " +
                "AND som.STATUS = 'O' AND som.RETIRED_DATE IS NULL " +
                "AND hm.RETIRED_DATE IS NULL " +
                "order by sm.SERVICE_NAME";
        stmt = (OraclePreparedStatement)conn.prepareStatement(queryString);

        if(serviceId != null){
            stmt.setArray(1, serviceId);
        } 
        stmt.setString(1, null);
        stmt.setString(2, calendarDate); 
        rs = stmt.executeQuery();

Solution `IN (?, ?, ...)` and `setString`s programmatically, see http://stackoverflow.com/questions/3107044/preparedstatement-with-list-of-parameters-in-a-in-clause — Joop Eggen, Nov 27 '15 at 11:02
It is questionable whether a JDBC driver should even allow an IN (?) to be set with an array value. — Mark Rotteveel, Nov 27 '15 at 12:51
You've posted a huge chunk of code without really explaining it. Plus you left in some commented out lines of code, which just confuses things. Please try to present a simple and clear example so that other people can understand your goals. — APC, Nov 28 '15 at 10:46

score 0 · Answer 1 · answered Nov 27 '15 at 11:35

Only suitable to oracle db. In DB create type. create type l_varchar2 is table of varchar2(100);

and try this.

public class Array {
    public static void main(String[] args) throws SQLException, Exception {
        Connection conn = ConnectionDefinition.getOracleConnection();
        conn.getTypeMap().put("L_VARCHAR2", String[].class);
        PreparedStatement  createStatement = conn.prepareCall("select * from table(?)");
        createStatement.setArray(1, ((OracleConnection) conn).createARRAY("L_VARCHAR2", new Object[]{"AA","BB","CC"}));
        ResultSet  rs = createStatement.executeQuery();
        while (rs.next()) {               
            System.err.println(rs.getString(1));
        }
        conn.close();
    }
}

Any alternatives to JDBC setArray() method?

1 Answers1