Dynamic SQL result sorting

Question

First good afternoon, I work specifically with Java in the Back-end using Webservices Rest and Postgresql, after some professional enhancements I was instructed to pass ordering and pagination of my application to SQL, that would burden the application server less and the results would return in an agile way. So far paging was not the problem, I could use FETCH or LIMIT to be able to control it, but I reached an impasse when I went ahead to sort. Using CASE in ORDER BY in SQL could not use it dynamically to sort different typing columns. Follow 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 quoted above would not successfully execute columns that are not of the same type. And doing several CASE would not be cool and productive to set so many parameters in Java Prepared. I asked in the SQL community and they guided me to do the scripts in functions and pass sort as parameter, but I did not find this form very attractive. So I thought about String formatting before moving to Prepared, but it is susceptible to SQL Injection and I certainly do not want to risk this option.

So I ask for tips, I thank you already!

Obs.: About having mentioned XY problem. I tried to do CASE in SQL as mentioned above and thought about the hypothesis of mounting the script in Function and view, but it is not a solution that pleased my boss, conversion of types would not work for all cases of scripts system, so I am looking for alternative solutions. String concatenation before sending to Preparedstatement also thought, however how to avoid an SQL Injection?

Answer 1

You can "mount" your query using a 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();
}

And then build a PreparedStatement from the mounted string:

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

Answer 2

If you really want to go this way you can just cast (to sweep), so you would get around the problem of columns being of different types.

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

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