How can I construct a JPQL query to select a specific column of a table given the column name?

Question

I am trying to construct a JPQL query to select a specific column of a table given the column name as input.

This is an example of what the equivalent native SQL query might look like:

SELECT bc_10year FROM dailytreasuryratesdb._entry;

I tried using a positional parameter in the query like so:

@Query("SELECT ?1 FROM Entry")
List<Entry> findEntriesByColumn(String col);

but this gives me a ConversionFailedException. After doing further research, I found that positional parameters should only be used with a WHERE clause, what other approaches could I try to this?

Answer 1

JPQL doesn't allow you to specify the columns of the projection with a parameter because SQL prepared statements cannot be used for that. However, there are other options.

Just get the whole object

You could just get the whole objects and then change that in your application code:

List<Entry> findAll();

and use it like

List<Entry> entries = entryRepository.findAll();
List<String> projected = entries
    .stream()
    .map(Entry::getWhateverYouWantToGet)
    .toList();

However, this will make you query data you don't need.

Change your application

Do you really need different queries for each attribute? You probably don't need to get all elements of a column at different places for each column. If you need to do that, you might want to change your database structure from

Id (P)	Your entry 1	Your entry 2
1	abc	def

to

Id (P)	entry name (P)	entry value
1	Your entry 1	abc
1	Your entry 2	def

where (P) means primary key/id column.
Then, you could do a query like

@Query("SELECT entryValue FROM Entry WHERE entryName = ?1")
List<String> findEntriesByColumn(String col);

The Jakarta Criteria API

The Jakarta criteria API gives you more control over your queries and allows you to create queries from Java code. You can use the EntityManager directly and create your own query:

CriteriaQuery<String> query = entityManager
    .getCriteriaBuilder()
    .createQuery(String.class);
Root<Entry> root = query.from(Entry.class);
query.select(root.<String>get(YOUR_FIELD_NAME_HERE));
List<String> results = entityManager
    .createQuery(query)
    .getResultList();

(see the example in the Javadoc of CriteriaQuery#select).

You should be able to autowire the EntityManager and YOUR_FIELD_NAME_HERE is the name of the field in your Entry/entity class you want to get.