Dynamically sort SQL result

Question

Dynamically sort SQL result

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

First of all, Working specifically with Java in the Backend using WebServices Rest and PostgreSQL, after some professional enhancements I was directed to pass ordering and paging of my application to SQL, which would overload the application server less and the results would return in an agile way. Until then pagination was not the problem, I could use FETCH or LIMIT so I could control, but I got stuck when I went to order. Using CASE in ORDER BY in SQL it was not possible to use it dynamically to sort different typing columns. Here's the example:

SELECT
    a.idade,
    a.nome,
    a.cpf
FROM
    cadastro a
ORDER BY
(
    CASE 1 WHEN 1 THEN
        a.idade
    WHEN 2 THEN
        a.nome
    WHEN 3 THEN
        a.cpf
    END
)

This example above would not perform successfully because the columns are not of the same type. And making multiple CASE would not be cool and productive to set so many parameters in Java's Prepared. I asked in the SQL community and they directed me to make the scripts in functions and to pass ordering as a parameter, but I do not find it to be very attractive. So I thought of formatting the String before moving on to Prepared, however it is suspect to SQL Injection and I certainly do not want to risk this option.

So I ask for tips, thank you in advance!

Note: About having mentioned XY problem . I tried to do CASE in SQL as I quoted above and thought of the assumption of assembling the script in function and view, however it is not a solution that pleased my boss, type conversion would not work for all cases of system scripts, so I am looking for alternative solutions . String Concatenation before sending to PreparedStatement I also thought, but how to avoid a SQL Injection?

sql java postgresql order-by

asked by anonymous 18.09.2017 / 21:26

2 answers

How to extract all td names in order? Paging in the photo gallery

score 1 · Answer 1

You can "assemble" your query using StringBuilder :

public String montarQueryConsulta( int ordem )
{
    StringBuilder sb = new StringBuilder();

    sb.append("SELECT c.idade, c.nome, c.cpf FROM tb_cadastro AS c ORDER BY ");

    switch( ordem )
    {
        case 1:
            sb.append("c.idade");
            break;

        case 2:
            sb.append("c.nome");
            break;          

        case 3:
            sb.append("c.cpf");
            break;

        default:
            sb.append("");
    }

    return sb.toString();
}

Then, construct PreparedStatement from the mounted string:

PreparedStatement ps = conn.prepareStatement( montarQueryConsulta(2) );
ResultSet rs = ps.executeQuery();

score 0 · Answer 2

If you really want to follow this path you can simply cast (for varchar), so you would bypass the problem of the columns being of different types.

But note that using a CASE clause within a ORDER BY clause is not very performative.

Consider changing this to three queries (or maybe even to "reuse code" to create a view) and call each query according to the condition.