SQL optimization options in Java

Question

Let's say I have a basic query like:

SELECT a, b, c FROM x WHERE y=[Z]

This query [Z]is a "variable" with various values ​​entered into the query.

Now consider the situation when we want to make the same query with two different known values [Z], for example, Z1and Z2. We can make two separate requests:

SELECT a, b, c FROM x WHERE y=Z1

SELECT a, b, c FROM x WHERE y=Z2

Or maybe we can programmatically create another query, for example:

SELECT a, b, c FROM x WHERE y in (Z1, Z2)

Now we have only one query (1 <2), but query construction and decomposition of the result set are a little more complicated, because we no longer perform simple simple queries.

Questions:

• What is the name of this optimization? (Is it worth it?)
• How can this be implemented from a Java application?
  • Do existing Java ORM technologies help?

Answer 1

?

, "" , , .

( ?)

:

• ,
• ; ... IN ( ... ),
• JDBC ..

.

Java?

"": -)

Java ORM?

ORM, , () Hibernate HQL , .

Answer 2

RDBMS IN , .

Y , . .

, WHERE IN . IN, .

IN .

Y , . .

Answer 3

, ( ) , ( plain JDBC) IN.

Answer 4

, JDBC:

// Assuming values is an int[] and conn is a java.sql.Connection
// Also uses Apache Commons StringUtils

StringBuilder query = new StringBuilder("SELECT a, b, c FROM x WHERE y IN (");

query.append(StringUtils.join(Collections.nCopies(values.length, "?"), ',');
query.append(")");

PreparedStatement stmt = conn.prepareStatement(query.toString());

for (int i = 0; i < values.length; i++) {
    stmt.setInt(i + 1, values[i]);
}

stmt.execute();
// Get results after this

. . , , .

Answer 5

, "in" ( blah in (1, 5, 10)) " blah = 1 OR blah = 5 OR blah = 10". , , , Apache Torque, , "in". ( .)

, OR, .

, ORM , . , .

Although decomposing a combined result set from a single query can be more complicated than processing a single result, it is probably much simpler than trying to combine two result sets from two queries. And probably much faster if a lot of duplicates are involved.