Write SQL Query where Column Name is VARIABLE(Column name will be taken dynamically from a loop) usi-CodePudding

Using Spring Data JPA, how to write SQL Select Queries, where Column Name is DYNAMIC(name of column used can vary. We have taken Column Name in Loop), Column Name should be picked from Loop and as per Column name picked, we need fetch result

I can provide more information if required

SELECT DISTINCT ?1 FROM TABLEName where rowStatus=0

Here ?1 represents Column Name which will vary(and selected according value in loop

CodePudding user response：

Name the method as u need and use the @Query annotation to specify the query.

@Query("SELECT DISTINCT ?1 FROM TABLEName where rowStatus=0")
List<MyClass> findByColumnName(String columnName);

CodePudding user response：

This doesn't really work with the Spring Data JPA query mechanisms.

@Query("SELECT DISTINCT ?1 FROM TABLEName where rowStatus=0")
List<MyClass> findByColumnName(String columnName);

Will only return the value passed as an argument, not the value of the column of that name.

If the list of acceptable column names is limited you could use a CASE statement:

CASE
    WHEN ?1 = 'name' THEN name
    WHEN ?1 = 'address' THEN address
    // ...
    ELSE result
END;

You can use this in both SQL and JPQL.

Some SQL dialects also have a function to turn a character string into an identifier name. You could search for that for your preferred database.