No results "In" clause in PreparedStatement with 2 objects

Question

I passed a lot of time to find the solution but I can't still.

I found some solution about how to pass "IN" elements into PreparedStatement, but if there is any other solution I'd be glad to see that, but, anyway I have

RowMapper<Map<BigInteger, Status>> mapper = new QueryDescriptionById.RowMapperDescription();

@Override
public Multimap<BigInteger, Status> findObject(BigInteger[] ids) {
    StringBuilder builder = new StringBuilder();
    for (Object id : ids) {
        builder.append("?,");
    }
    String statement = SQL + builder.deleteCharAt(builder.length() - 1).toString() + ")";

    Multimap<BigInteger, Status> multimap = ArrayListMultimap.create();
    intoMap(multimap, getJdbcTemplate().query(statement, ps -> {
        int index = 1;
        for (BigInteger id : ids) {
            ps.setInt(index++, id.intValue());
        }
    }, mapper));
    return multimap;
 }

private void intoMap(Multimap<BigInteger, Status> multimap, List<Map<BigInteger, Status>> list) {
    list.forEach(map -> map.forEach((multimap::put)));
}

class RowMapperDescription implements RowMapper {
        @Override
        public Map<BigInteger, Status> mapRow(ResultSet rs, int rowNum) throws SQLException {
            Status status = new Status();
            Map<BigInteger, Status> map = new HashMap<>();
            BigInteger id = rs.getBigDecimal("ID").toBigInteger();
            BigInteger parentId = rs.getBigDecimal("PARENT_ID").toBigInteger();
            status.setId(id);
            status.setDescription(rs.getString("attempt_status_text"));
            map.put(parentId, status);
            return map;
        }
    }

But it works well while I pass in BigInteger[] only 1 element but when there are 2 or more it doesn't into my mapper.

Why ?

P.S

Instead RowMapper I've tried to use ResultSet like

return getJdbcTemplate().query(SQL, ids, resultSet -> {
            Multimap<BigInteger, Status> multimap = ArrayListMultimap.create();
            int rowNum = 0;
            while (resultSet.next()) {
                System.out.println("rownum = " + rowNum);
                mapper.mapRow(resultSet, rowNum++).forEach(multimap::put);
            }
            return multimap;
        })

But in the console wasn't any lines which mean that resultSet hasn't next()

P.S.S

My SQL is where hard so I put only a short version. It looks like

private String SQL = "select el1,el2 from mytable where el3 in(";

I've tried instead in("; and in :ID and in = :ID and in (:ID) and in (?) and some other variants which I don't remember (-:

EDITED

Actually, I don't understand, why that works

public QueryDescriptionBySmsId(DataSource dataSource) {
    super(dataSource, SQL);
    logger.debug("Created QueryStatus");
    declareParameter(new SqlParameter("ID", Types.NUMERIC));
    compile();
    logger.debug("Created QueryStatus");
}

@Override
public Multimap<BigInteger, Status> query(Set<BigInteger> ids) {
       Map<String, Set> paramMap = Collections.singletonMap("ID", smsIds);
       List l = executeByNamedParam(paramMap); // it returns a lot of elements which I am looking for
}

But that doesn't work

 @Override
 public Multimap<BigInteger, SmsStatus> query(Set<BigInteger> sids) {

        Map<String, Set> paramMap = Collections.singletonMap("ID", ids);
        return getJdbcTemplate().query(SQL,  resultSet -> {
            int rowNum = 0;
            Multimap<BigInteger, SmsStatus> multimap = 
 ArrayListMultimap.create();
            while (resultSet.next()) {
                mapper.mapRow(resultSet, rowNum++).forEach(multimap::put);
            }
            return multimap;
        },paramMap);
 }

It shows an error

SQL state [99999]; error code [17004]; Invalid column type

What does the resulting query look like? It seems as if the part in `SQL` already contains the opening parenthesis ... — Thomas, Jul 04 '19 at 12:26
Then this could work: https://stackoverflow.com/q/45914325/14955. Also https://stackoverflow.com/a/17842297/14955 — Thilo, Jul 04 '19 at 12:35
@Thilo, It good links, But I forgot to say I use Spring and I haven't `Connection` or `Statement`. I can reach on `getJdbsTemplate()` ...... I [found](https://stackoverflow.com/a/8428360/9719337) — Dred, Jul 04 '19 at 12:38
@Thilo, it was first that I've tried :-) Now, I try your last post in my program, several minutes till compilation — Dred, Jul 04 '19 at 12:46
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/195996/discussion-between-dred-and-thilo). — Dred, Jul 04 '19 at 12:55
@Thilo I make Object[] ids = `Array array = connection.createArrayOf("NUMERIC", new Object[]{"18739395","23343912"}); ` And get SQLState =99999 java.sql.SQLException: Unsupported function. Instead `NUMERIC` i've tried `number` too. — Dred, Jul 04 '19 at 13:49

score 0 · Accepted Answer · answered Jul 05 '19 at 07:53

I've changed my code like that and it works now, but I don't like that solution

public class QueryDescriptionById extends SqlQuery implements IQueryDescriptionById {

RowMapper<Map<BigInteger, Status>> mapper = new RowMapperDescription();

public QueryDescriptionById(DataSource dataSource) {
    super(dataSource, SQL);
}

public Multimap<BigInteger, SmsStatus> query(Set<BigInteger> ids) {

    Map<String, Set> paramMap = Collections.singletonMap("ID", ids);
    NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());

    return template.query(SQL, paramMap, resultSet -> {
        int rowNum = 0;
        Multimap<BigInteger, Status> multimap = ArrayListMultimap.create();
        while (resultSet.next()) {
            mapper.mapRow(resultSet, rowNum++).forEach(multimap::put);
        }
        return multimap;
    });
}
.........
.........
.........
}

What don't you like about it? You are directly setting your list of numbers into a single query parameter. Isn't that the ideal solution? — Thilo, Jul 05 '19 at 08:35
@Thilo, because my class extends `SqlQuery` and in other classes which were created before me it was realised with something like `declareParameter(new SqlParameter("ID", Types.NUMERIC)); compile(); Map params = new HashMap(); params.put("ID", cnrId); return executeByNamedParam(params);` Also, I mean, that I use my `SQL` statement twice, in constructor and in my `query(Set)` method. Its bad — Dred, Jul 05 '19 at 08:50

No results "In" clause in PreparedStatement with 2 objects

1 Answers1