Querying for many rows with composite primary key

[Brook Heisler]

(I mistakenly submitted a partially-written version of this topic already, sorry about that).

Suppose I have a table like this:

CREATE TABLE Songs AS {
    ALBUM varchar,
    TITLE varchar,
    ...
    PRIMARY KEY (ALBUM, TITLE)
}

How can I generate a query like this using QueryDSL-SQL?

SELECT * from Songs WHERE (ALBUM, TITLE) in (('A', 'B'), ('C', 'D'));

I've tried this:

QTuple proj = Projections.tuple( QSong.song.album, QSong.song.title );

new SQLQuery<>( conn )
    .select( QSong.song )
    .from( QSong.song )
    .where( Expressions.list( QSong.song.album, QSong.song.title )
                    .in( proj.newInstance( "A", "B" ),
                        proj.newInstance( "C", "D" ) ) );

Unfortunately that resulted in malformed SQL (square brackets and no quotes):

SELECT * from Songs WHERE (ALBUM, TITLE) in ([A, B], [C, D]);

[timowest]

You will need to use Expressions.list on both sides:

QTuple proj = Projections.tuple( QSong.song.album, QSong.song.title );

new SQLQuery<>( conn )
    .select( QSong.song )
    .from( QSong.song )
    .where( Expressions.list( QSong.song.album, QSong.song.title )

                    .in( Expressions.list( Expressions.constant("A"), Expressions.constant("B") ),
                        Expressions.list( Expressions.constant("C"), Expressions.constant("D") ) ) );

I'll provide a simpler variant for the next release.

[Brook Heisler]

I've tried that as well, but it didn't compile. Expressions.constant returns an Expression<T>, which is not a subtype of SimpleExpression<?>.