CODEWITHSUNDEEP
javamysqljdbc
Radu
Radu

Reputation: 1104

JDBC 4 order by clause

Can I have a prepared statement with the following query:

select * from table as t order by ? ? limit ?,?

where the second "?" would be asc or desc.

Upvotes: 0

Views: 1273

Answers (3)

Andreas
Andreas

Reputation: 1193

If the combinations of columns and sorting directions are limited and the statements not overly complex you could simulate it with union. But this is not very elegant. The statements have to be there n-times and the parameter markers must be there n-times also. If you use a framework like springs jdbc-template you could name the parameters and there is no danger of counting the position of the parameters wrong.

select * from table where ? = 'col1' and ? = 'asc' order by col1 asc
union all
select * from table where ? = 'col1' and ? = 'desc' order by col1 desc
union all
select * from table where ? = 'col2' and ? = 'asc' order by col2 asc
union all
select * from table where ? = 'col2' and ? = 'desc' order by col2 desc
...

Upvotes: 0

Tim Büthe
Tim Büthe

Reputation: 63744

It is not possible, see here:

Within the statement, “?” characters can be used as parameter markers to indicate where data values are to be bound to the query later when you execute it. The “?” characters should not be enclosed within quotation marks, even if you intend to bind them to string values. Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

MySQL documentation

Upvotes: 6

duffymo
duffymo

Reputation: 308763

Trying it is indeed the best thing. I don't believe you can bind table or column names, only parameters. So my answer is "no", you cannot. But the best way to answer such a thing is to try it and see. It'll be faster and more definitive than asking here.

Upvotes: 1

Related Questions