Dynamically selecting table column

Question

I'm using JAVA JPA in spring boot. The scenario is my table is having around 8 columns (suppose id, A,B,C,D,E,F,G,H) from these I want 5 column as result E,F,G,H and only one from A,B,C,D which will be not fixed for every execution.

I have written like this.

@Query("Select :dynamicColName ,E,F,G,H FROM TABLEname where id= :id ") List findById(@Param("dynamicColName")String dynamicColName, @Param("id")String id );

It's not working. Here query isn't refering value of :dynamicColName as column name here.

Suggestion are highly appreciated.

Dynamic column name injection in query

score 1 · Answer 1 · answered Jul 23 '23 at 22:49

One of the easiest ways to implement dynamic queries is to use the Spring Data JPA Specification

The result will be something like this, you can choose the parameter you want to query with it.

List<PostComment> comments = postCommentRepository.findAll(
    orderByCreatedOn(
        byPost(post)
            .and(byStatus(PostComment.Status.PENDING))
            .and(byReviewLike(reviewPattern))
            .and(byVotesGreaterThanEqual(minVotes))
    )
);

more information: https://vladmihalcea.com/spring-data-jpa-specification/

score 0 · Answer 2 · answered Jul 23 '23 at 21:10

Option 1 - Workaround: You can use EntityManager:

static final String QUERY = "SELECT (%s),E,F,G,H FROM TABLEname where id=:id";        

@Autowired
EntityManager entityManager;

Query createQuery(String dynamicColName, String id) {
    String sql = String.format(QUERY, dynamicColName);
    Query query = entityManager.createNativeQuery(sql, YourTableEntityName.class);
    query .setParameter("id", id);
    return query;
}

public List<YourTableEntityName> findById(String dynamicColName, String id) {
    Query q = createQuery(dynamicColName, id);
    return q.getResultList();   
}

Option 2 - CASE block: add a condition inside the SELECT query part:

SELECT
 CASE 
    WHEN (t.id >= 1000) THEN colunm1 
    WHEN (e.id < 1000) THEN colunm2 
    ELSE colunm3 END,
 E,F,G,H
FROM TABLEname t
WHERE id=:id

The solution will looks like this:

@Query("SELECT CASE WHEN (t.id >= 1000) THEN dynamicColName1 WHEN (e.id < 1000) THEN dynamicColName2 ELSE dynamicColName3  END, E,F,G,H FROM TABLEname t WHERE id=:id ") 
List findById(@Param("id")String id );

Reference Wikipedia article: New in JPA 2.0

Option 3 - JPA Criteria API

EntityManager em = ... ; 
CriteriaBuilder queryBuilder = em.getCriteriaBuilder();
CriteriaQuery qdef = queryBuilder.createQuery();

if (someCondition1) {
    //select columnA
    qdef.select(customer.get(TableEntity_.columnA));
} else if (someCondition2) {
    //select columnB
    qdef.select(customer.get(TableEntity_.columnB));
}
// where condition with ID check.

OpenJPA: Chapter 11. JPA Criteria

Bueldung: JPA Criteria Queries

Dynamically selecting table column

2 Answers2

Option 1 - Workaround: You can use EntityManager:

Option 2 - CASE block: add a condition inside the SELECT query part:

Option 3 - JPA Criteria API