Does h2 have a query/clause similar to the WHERE IN in MySQL?

Question

My code currently goes as follows:

public List<DeviceOrganizationMetadataHolder> getChildrenByParentId(List<String> parentIds) throws DeviceOrganizationDAOException {
        List<DeviceOrganizationMetadataHolder> children = new ArrayList<>();
        Connection conn;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        DeviceOrganizationMetadataHolder deviceMetadataHolder;
        String[] data = parentIds.toArray(new String[parentIds.size()]);
        try {
            conn = this.getConnection();
            String sql = "SELECT * FROM DEVICE_ORGANIZATION_MAP WHERE DEVICE_PARENT IN (?)";
            stmt = conn.prepareStatement(sql);
            data = parentIds.toArray(data);
            stmt.setObject(1, data);
            rs = stmt.executeQuery();
            while (rs.next()) {
                deviceMetadataHolder = this.loadOrganization(rs);
                children.add(deviceMetadataHolder);
            }
        } catch (SQLException e) {
            throw new DeviceOrganizationDAOException("Error occurred for device list with while retrieving children.", e);
        } finally {
            DeviceManagementDAOUtil.cleanupResources(stmt, rs);
            return children;
        }
}

However even though in the unit tests I try to pass an array with parentIds, the return remains null. What I can gauge from this is one of the following:

The array data isn't getting properly read, therefore the output is coming as null.
WHERE IN is not supported by h2 or else there is a different implementation that needs to be used instead.

Where am I going wrong in this?

EDIT - There was a similar duplicate question that was tagged. While it suggested using a StringBuilder and a loop, I was looking for an answer stating how it could be done in a cleaner way using the query itself.

Possible duplicate of [PreparedStatement with list of parameters in a IN clause](https://stackoverflow.com/questions/3107044/preparedstatement-with-list-of-parameters-in-a-in-clause) — Boris the Spider, Dec 13 '17 at 08:29

score 1 · Answer 1 · answered Dec 13 '17 at 10:30

1

Try setting the parameter as a list instead of an array, ie replace

stmt.setObject(1, data);

with

stmt.setObject(1, Arrays.asList(data));

answered Dec 13 '17 at 10:30

Per Huss

4,755
12
29

score 0 · Answer 2 · answered Dec 13 '17 at 11:01

Figured it out.

There was an issue posted on the h2database GitHub about this exact problem. Followed the suggested edits and it worked!

Code after edits is as follows:

public List<DeviceOrganizationMetadataHolder> getChildrenByParentId(List<String> parentIds) throws DeviceOrganizationDAOException {
        List<DeviceOrganizationMetadataHolder> children = new ArrayList<>();
        Connection conn;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        DeviceOrganizationMetadataHolder deviceMetadataHolder;
        Object[] data = parentIds.toArray();
        try {
            conn = this.getConnection();
            String sql = "SELECT * FROM DEVICE_ORGANIZATION_MAP WHERE DEVICE_PARENT IN (SELECT * FROM TABLE(x VARCHAR = ?))";
            stmt = conn.prepareStatement(sql);
            stmt.setObject(1, data);
            rs = stmt.executeQuery();
            while (rs.next()) {
                deviceMetadataHolder = this.loadOrganization(rs);
                children.add(deviceMetadataHolder);
            }
        } catch (SQLException e) {
            throw new DeviceOrganizationDAOException("Error occurred for device list with while retrieving children.", e);
        } finally {
            DeviceManagementDAOUtil.cleanupResources(stmt, rs);
            return children;
        }
}

As you can see, I've used an Object array for data instead and added an additional query inside the main query. Followed the instructions given in the GitHub issue to a tee and it worked flawlessly.

Does h2 have a query/clause similar to the WHERE IN in MySQL?

2 Answers2