CODEWITHSUNDEEP
javamysql
501 - not implemented
501 - not implemented

Reputation: 2698

Query MySQL with multiple identifier

I have a MySQL table with more than a million rows like this:

id (BIGINT) | id_other (INT)
24334501    | 20123
24334501    | 20324
24334501    | 20111
24334500    | 20123
24334500    | 20324
24334510    | 20111
....

From this table, I want to build a map from a list of ids like this:

 id_other -> count of id

my query is: "select * from lsh where id = ?"

To perform those queries, I created an index for the column ìd

Now I want to get a list of all id_other from a list of id. Currently I have this code:

for (Long id : ids{ // ids has a size between 2000 and 8000
                statement.setLong(1, id);

                ResultSet rs = statement.executeQuery();

                while(rs.next()) {
                    int idOther = rs.getInt("id_other");
                    if(!map.containsKey(idOther)){
                        map.put(idOther, new AtomicInteger(0));
                    }
                    map.get(idOther).incrementAndGet();
                }
            }

any ideas how to perform this?

UPDATE: Now I have this query: select id_other, count(*) FROM lsh WHERE id ? GROUP BY id_other . I execute the query with: Array array = connection.createArrayOf("BIGINT", values.toArray()); statement.setArray(1, array); final ResultSet rs = statement.executeQuery();

But now I get this exception for connection.createArrayOf: java.sql.SQLFeatureNotSupportedException

thank you

Upvotes: 0

Views: 170

Answers (1)

Kostas Mitsarakis
Kostas Mitsarakis

Reputation: 4747

I think you want something like the following (SQL Fiddle):

SELECT id_other, COUNT(*) AS total_ids , GROUP_CONCAT(id) AS list_id
FROM lsh
WHERE id_other IN (20111, 20123, 20324)
GROUP BY id_other
ORDER BY id_other;

Result:

id_other    total_ids   list_id
20111       2           24334501,24334510
20123       2           24334500,24334501
20324       2           24334500,24334501

Upvotes: 2

Related Questions