Passing column name as a param using the placeholder jpa named query returning incorrect data

Question

Instead of column values i am getting the column name in a list from below code

for example

should get [12.4,54.6,65.7,88.7,44.8]

but getting [onsite,onsite,onsite,onsite,onsite]

DAO:

public List<String> getLocation(String location,String roles, String title,String tier,Integer level) {
        
        List<String> result = new ArrayList<>();
        try {
                                
              Query query = entityManager.createNamedQuery("getLocation");
              query.setParameter("location", location);//location value onsite
              query.setParameter("title",title); 
              query.setParameter("roles",roles);
              query.setParameter("tier", tier);
              query.setParameter("level", level);
              result = query.getResultList(); 
        }
        catch(Exception e) {
            logger.error(e.getMessage());
        }
         return result;
    }

Entity class:

@Entity

@NamedNativeQueries({
    
    @NamedNativeQuery(name = "getLocation", 
    query = "select :location FROM myschema.test  where title=:title and roles=:roles and level=:level and tier=:tier"
      )
    
    })
@Table(name = "test", schema = "mySchema")
public class TestEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;
    
    
    @Column(name = "name")
    private String name;

    @Column(name = "title")
    private String title;

    @Column(name = "roles")
    private String roles;

    @Column(name = "level")
    private Integer level;
    
    @Column(name = "tier")
    private String tier;
    
    @Column(name = "diversity")
    private String diversity;
    
    @Column(name = "onsite")
    private Double onsite;

    @Column(name = "offsite")
    private Double offsite;
}

Service code:

List dbLocations = supplierDao.getLocation("onsite", "job role","job title","tier1","8");

jpa Named query is returning list of colum name instead of values

Does this answer your question? [Spring Data JPA - Pass column name and value as parameters](https://stackoverflow.com/questions/47407043/spring-data-jpa-pass-column-name-and-value-as-parameters) — Nowhere Man, Aug 24 '20 at 06:45
your query send to DB is something like select 'onsite' from myschema.test where xxx — andy, Aug 24 '20 at 07:47
plz understand my requirement i want to pass column name dynamically as a param — abhinav kumar, Aug 24 '20 at 10:58
@abhinavkumar not in native query also using placeholder I think because parameter replaced as string literal either you just concat as string(`"Select" +" column"....`) — Eklavya, Aug 24 '20 at 14:14

score 0 · Answer 1 · answered Aug 24 '20 at 14:01

0

Give them the same name every where, whenever you are using them. If you are using a html file to retrieve these data, then also. The name ambiguity may be the reason to this problem.

answered Aug 24 '20 at 14:01

Shubham Ray

23
6

score 0 · Accepted Answer · answered Aug 30 '20 at 11:18

I have tried this already in my project.It won't work that way .Better what you can do is create a method at dao layer similar to getLocation for getting each column and the same you can call swith case on each method based on the column name passed.

Passing column name as a param using the placeholder jpa named query returning incorrect data

2 Answers2