select specific columns from the table and store the results to the non-entity POJO with Spring + hibernate + JPA

Question

I am using Spring 3.1.3 + hibernate-entitymanager 5.0.7 (Which contains JPA of version 2.1) + ojdbc14-10.2.0.2.jar

I want to select specific columns from the table and store the results to the non-entity POJO. I want to implement in the way how it is proposed in below link

Spring Data JPA map the result to Non-Entity POJO

My Entity class:

   @Entity
    @Data
    @Table(name = "CUSTOMER")
    @SqlResultSetMapping(name="SampleMapping",
    classes = {
     @ConstructorResult(targetClass = com.entity.Sample.class,
       columns = {@ColumnResult(name="customerId"), @ColumnResult(name="name"), @ColumnResult(name="firstName")}
     )}
    )
    public class CustomerEntity {

        @Id
        @Column(name = "CUSTOMER_ID")
        private String customerId;

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

        @Column(name = "FIRST_NAME")
        private String firstName;

       @Column(name = "LAST_NAME")
        private String lastName;

       @Column(name = "AGE")
        private String age;
    }

My Query Part:

String q = "select s.customerId, s.name, s.firstName from CustomerEntity s";
Query query = entityManager.createNativeQuery(q,"SampleMapping");
List<Sample> resultsList = query.getResultSet();

Sample class is simple POJO (with parameterized constructor with customerId, name and firstName) where i want to store my results of query.

Can you please advise me which versions of Spring, Hibernate, JPA, ojdbc jars I need to add in order to achieve my requirement? Many thanks in advance.

Couldn't idendify if the below exception is because of versions of jar or any incorrect declaration of annotations or query format.

Because I face few exceptions when I add the below jars:

    +- com.commons:commons-dao:jar:1.0## Heading ##-SNAPSHOT:compile
    |  +- oracle:ojdbc14:jar:10.2.0.2:compile
    |  +- org.springframework:spring-tx:jar:3.1.3.RELEASE:compile
    |  |  +- aopalliance:aopalliance:jar:1.0:compile
    |  |  \- org.springframework:spring-core:jar:3.1.3.RELEASE:compile
    |  +- org.springframework:spring-orm:jar:3.1.3.RELEASE:compile
    |  |  \- org.springframework:spring-jdbc:jar:3.1.3.RELEASE:compile
    |  +- org.hibernate:hibernate-entitymanager:jar:5.0.7.Final:compile
    |  |  +- org.hibernate:hibernate-core:jar:5.0.7.Final:compile
    |  |  |  +- antlr:antlr:jar:2.7.7:compile
    |  |  |  \- org.jboss:jandex:jar:2.0.0.Final:compile
    |  |  +- dom4j:dom4j:jar:1.6.1:compile
    |  |  +- org.hibernate.common:hibernate-commons-annotations:jar:5.0.1.Final:compile
    |  |  +- org.hibernate.javax.persistence:hibernate-jpa-2.1-api:jar:1.0.0.Final:compile
    |  |  \- org.apache.geronimo.specs:geronimo-jta_1.1_spec:jar:1.1.1:compile
    |  +- org.javassist:javassist:jar:3.14.0-GA:compile
    |  +- javax.resource:connector:jar:1.0:compile
    |  |  +- org.springframework:spring-oxm:jar:3.1.3.RELEASE:compile
    |  |     \- org.apache.httpcomponents:httpclient-cache:jar:4.2.4:compile
    |  +- org.springframework:spring-beans:jar:3.1.3.RELEASE:compile (version managed from 3.1.2.RELEASE)
    |  +- org.springframework:spring-context:jar:3.1.3.RELEASE:compile (version managed from 3.1.2.RELEASE)
    |  |  +- org.springframework:spring-expression:jar:3.1.3.RELEASE:compile
    |  |  \- org.springframework:spring-asm:jar:3.1.3.RELEASE:compile
    |  +- org.slf4j:jcl-over-slf4j:jar:1.6.4:runtime
    |  \- org.slf4j:log4j-over-slf4j:jar:1.6.4:runtime
    |  +- org.springframework:spring-webmvc:jar:3.1.3.RELEASE:compile
    |  |  \- org.springframework:spring-context-support:jar:3.1.3.RELEASE:compile
    |  +- org.springframework:spring-web:jar:3.1.3.RELEASE:compile
    |  +- org.springframework:spring-aop:jar:3.1.3.RELEASE:compile
    |  +- org.springframework.integration:spring-integration-core:jar:2.2.6.RELEASE:compile
    |  |  \- org.springframework.retry:spring-retry:jar:1.0.2.RELEASE:compile
    |  +- org.springframework.integration:spring-integration-event:jar:2.2.6.RELEASE:runtime
    |  +- org.springframework.integration:spring-integration-file:jar:2.2.6.RELEASE:runtime
    |  +- org.springframework.integration:spring-integration-http:jar:2.2.6.RELEASE:compile
    |  +- org.springframework.integration:spring-integration-xml:jar:2.2.6.RELEASE:compile
    |  |  \- org.springframework.ws:spring-xml:jar:2.1.1.RELEASE:compile
    |  +- org.springframework.integration:spring-integration-jms:jar:2.2.6.RELEASE:compile
    |  |  \- org.springframework:spring-jms:jar:3.1.3.RELEASE:compile
    |  +- org.springframework.integration:spring-integration-ws:jar:2.2.6.RELEASE:compile
    |  |  \- org.springframework.ws:spring-ws-core:jar:2.1.1.RELEASE:compile
    +- org.hibernate:hibernate-validator:jar:4.3.0.Final:compile
    +- log4j:log4j:jar:1.2.17:provided
    +- org.slf4j:slf4j-api:jar:1.7.12:compile
    +- org.jboss.logging:jboss-logging:jar:3.3.0.Final:compile
    \- org.slf4j:slf4j-simple:jar:1.7.12:compile

But I got resulted in below exception in the below line

List<Sample> resultsList = query.getResultSet();

Exception:

 org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
           at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:635) ~[spring-orm-3.1.3.RELEASE.jar:3.1.3.RELEASE]
           at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:104) ~[spring-orm-3.1.3.RELEASE.jar:3.1.3.RELEASE]
           at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:403) ~[spring-orm-3.1.3.RELEASE.jar:3.1.3.RELEASE]
           at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:58) ~[spring-tx-3.1.3.RELEASE.jar:3.1.3.RELEASE]
           at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213) ~[spring-tx-3.1.3.RELEASE.jar:3.1.3.RELEASE]
           at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:163) ~[spring-tx-3.1.3.RELEASE.jar:3.1.3.RELEASE]
           at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.1.3.RELEASE.jar:3.1.3.RELEASE]
           at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) ~[spring-aop-3.1.3.RELEASE.jar:3.1.3.RELEASE]
           at com.common.dao.SampleServiceImpl.sampleMethod(SrvImplemet.java:123) ~[commons-dao-1.0-SNAPSHOT.jar:na]

      Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
           at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.loader.Loader.getResultSet(Loader.java:2116) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1899) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1875) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.loader.Loader.doQuery(Loader.java:919) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.loader.Loader.doList(Loader.java:2611) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.loader.Loader.doList(Loader.java:2594) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2423) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.loader.Loader.list(Loader.java:2418) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:336) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1967) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:322) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:125) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606) ~[hibernate-entitymanager-5.0.7.Final.jar:5.0.7.Final]
           at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483) ~[hibernate-entitymanager-5.0.7.Final.jar:5.0.7.Final]
           at com.common.dao.SampleDaompl.sampleMethod.SampleDaoImpl.getData(SampleDaoImpl.java:16) ~[commons-dao-1.0-SNAPSHOT.jar:na]
           at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_51]
           at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_51]
           at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_51]
           at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_51]
           at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:319) ~[spring-aop-3.1.3.RELEASE.jar:3.1.3.RELEASE]
           at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.1.3.RELEASE.jar:3.1.3.RELEASE]
           at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) ~[spring-aop-3.1.3.RELEASE.jar:3.1.3.RELEASE]
           at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155) ~[spring-tx-3.1.3.RELEASE.jar:3.1.3.RELEASE]
           ... 191 common frames omitted
    Caused by: java.sql.SQLException: ORA-00942: table or view does not exist

           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) ~[ojdbc14-10.2.0.2.jar:Oracle JDBC Driver version - "10.2.0.1.0"]
           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) ~[ojdbc14-10.2.0.2.jar:Oracle JDBC Driver version - "10.2.0.1.0"]
           at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288) ~[ojdbc14-10.2.0.2.jar:Oracle JDBC Driver version - "10.2.0.1.0"]
           at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743) ~[ojdbc14-10.2.0.2.jar:Oracle JDBC Driver version - "10.2.0.1.0"]
           at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216) ~[ojdbc14-10.2.0.2.jar:Oracle JDBC Driver version - "10.2.0.1.0"]
           at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799) ~[ojdbc14-10.2.0.2.jar:Oracle JDBC Driver version - "10.2.0.1.0"]
           at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039) ~[ojdbc14-10.2.0.2.jar:Oracle JDBC Driver version - "10.2.0.1.0"]
           at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839) ~[ojdbc14-10.2.0.2.jar:Oracle JDBC Driver version - "10.2.0.1.0"]
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132) ~[ojdbc14-10.2.0.2.jar:Oracle JDBC Driver version - "10.2.0.1.0"]
           at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285) ~[ojdbc14-10.2.0.2.jar:Oracle JDBC Driver version - "10.2.0.1.0"]
           at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329) ~[ojdbc14-10.2.0.2.jar:Oracle JDBC Driver version - "10.2.0.1.0"]
           at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93) ~[commons-dbcp-1.2.2.jar:1.2.2]
           at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70) ~[hibernate-core-5.0.7.Final.jar:5.0.7.Final]
           ... 215 common frames omitted

Note: previously when I had tried to select all rows and storing the results to Entity class with the dependencies Spring 3.1.3 + hibernate-entitymanager 3.5.3 + JPA 2.0 + ojdbc14-10.2.0.2.jar, I was able to fetch the results succesfully without any exception.

Differences :

jar version hibernate-entitymanager 3.5.3 & JPA of version 2.0
Query was select *..
Entity class without the @SqlResultSetMapping annotation

Klaus Groenbaek · Answer 1 · 2017-01-06T23:49:18.843

2

If I understand you question correctly you have an existing table with the following columns

name, firstName, lastName, age (possible more columns)

And you want to create an entity so you can get data from that table.

The first thing to note is that all JPA Entities MUST have a primary key (@ID column). Second, I have never had to use @SqlResultSetMapping for this to work.

In our system we have a View that is mappe directly to an entity, there is no difference between this entity and any other entities in our system, if you didn't look in the database you would not know that there is no table (but a view) for this entity.

Edit I can see I missed part of the question. So here is what I thik you asked for.

You need to look a JPQL Constructor Expression it basically allows you to select the columns you want into a POJO of your choice, but the constructor of the POJO must be able to the exact list of arguments.

edited Jan 06 '17 at 23:49

answered Jan 06 '17 at 20:19

Klaus Groenbaek

4,820
2
15
30

Klaus Groenbaek, 1. In my code I have @ID column, missed to update in my question.Now I updated. 2. I am trying annotion SqlResultSetMapping to select specific columns and store the query results to non-entity POJO. If possible can you clarify me your second point? Thanks. – Alagammal P Jan 06 '17 at 21:01
Why do you say non-entity POJO - As long as you have a table or view, you can have an entity that model it (or part of it) – Klaus Groenbaek Jan 06 '17 at 21:10
My Entity class contains all columns of the table,. In one scenario I need to fetch few columns and in other case I need to fetch some other columns. So I thought of having non-entity POJO which contains only required columns. But Im not going to retrieve all the columns in both cases. So If I pass the query results to entity object, I presume that there will be unused columns in my entity class result. Kindly correct me If my understanding is incorrect. – Alagammal P Jan 06 '17 at 21:21
Executing the query(which selects specific columns ) will return a list of Object[] not list of Entity class. So I need to loop and get the column values and if the query results are more looping will be time consuming which will decrease the performance. That is also the reason I choose to store the results to non-entity POJO. – Alagammal P Jan 06 '17 at 22:25
Sorry, but how long have you used JPA? entityManager.createQuery("select c from CustomerEntity c", CustomerEntity.class).getResultList() will do exactly what you want. If CustomerEntity is an entity mapped to the CUSTOMER table. JPA will only load the columns defined in the entity, there is no need to tell JPA which columns to load that is what the @Column annotation on the fields are for! There is no recurement in JPA that an entity must match all the column in the database (otherwise it would be hard to add columns to the database on a running system, which we do) – Klaus Groenbaek Jan 06 '17 at 23:21
Sorry I misread your question, and have updated my answer. The idea of having an entity with some of the fields would also work, but you would have two entities that mapped to the same table. This could be confusing to the next developer. I don't know if this works with all JPA providers, but multiple Entities to one table works with EclipseLink. – Klaus Groenbaek Jan 06 '17 at 23:48

score 1 · Answer 2 · answered Jan 07 '17 at 21:02

you can use criteria and projections

Criteria query = session.createCriteria(CustomerEntity.class)
    .setProjection(Projections.projectionList()
                .add(Projections.property("customerId"), "customerId")
                .add(Projections.property("name"), "name")
                .add(Projections.property("firstName"), "firstName"));

query.setResultTransformer(new AliasToBeanResultTransformer(Sample.class));

List<Sample> list = query.list();

the Sample class

public class Sample {

    private String customerId;
    private String name;
    private String firstName;

    public Sample(){
    }

    public String getCustomerId() {
        return customerId;
    }
    public void setCustomerId(String customerId) {
        this.customerId = customerId;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }


}

user2004685 · Answer 3 · 2017-01-06T19:52:33.367

0

Here is how you can do it:

List<Sample> resultList = new ArrayList<>();
ResultSet rs = query.getResultSet();

while (rs.next()) {
    ResultSetMetaData metadata = rs.getMetaData();

    for (int i = 1; i <= metadata.getColumnCount(); i++) {
        System.out.println("Column Name: " + metadata.getColumnName(i)
                + " | Value: " + rs.getObject(i));
    }

    Sample sample = new Sample();
    sample.setSomeValue(rs.getObject(1));
    sample.setSomeOtherValue(rs.getObject(2));

    resultList.add(sample);
}

Please note that here the use of ResultSetMetaData is optional. I have used it just to show how you can extract the Column Names if you want.

edited Jan 06 '17 at 19:52

answered Jan 06 '17 at 19:47

user2004685

9,548
5
37
54

Is implementing Custom Row mapper necessary only for the query which selects specific columns? Because when I used select * query and stored the the results to entity class, I never faced this exception. I was able to map to my entity clas without Custom RowMapper? But I will implement your answer and post the result. – Alagammal P Jan 06 '17 at 19:55
@AlagammalP I would prefer doing it manually and not depending too much on the framework. But you can check this post if you want to see how it is taken care by the framework: http://stackoverflow.com/questions/13012584/jpa-how-to-convert-a-native-query-result-set-to-pojo-class-collection – user2004685 Jan 06 '17 at 19:59

score 0 · Answer 4 · answered Jan 06 '17 at 21:55

Here's one way to do it.

@Entity
@Table(name = "CUSTOMER")
@NamedNativeQuery(name = "findSampleMapping", 
    query = "SELECT CUSTOMER_ID, NAME, FIRST_NAME FROM CUSTOMER where CUSTOMER_ID = ?1", resultSetMapping = "SampleMapping")
@SqlResultSetMapping(name="SampleMapping",
classes = {
 @ConstructorResult(targetClass = com.entity.Sample.class,
   columns = {@ColumnResult(name="customerId"), @ColumnResult(name="name"), @ColumnResult(name="firstName")}
 )}
)
public class CustomerEntity {

    @Id
    @Column(name = "CUSTOMER_ID")
    private String customerId;

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

    @Column(name = "FIRST_NAME")
    private String firstName;

   @Column(name = "LAST_NAME")
    private String lastName;

   @Column(name = "AGE")
    private String age;
}



public class Sample {


    private String customerId;
    private String name;
    private String firstName;

    public Sample(String customerId, String name, String firstName){
        this.customerId = customerId;
        this.name = name;
        this.firstName = firstName;
    }

    public String getCustomerId() {
        return customerId;
    }
    public void setCustomerId(String customerId) {
        this.customerId = customerId;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }


}



Query q = em.createNamedQuery("findSampleMapping");
q.setParameter(1, "1");
@SuppressWarnings("unchecked")
List<Sample> list = q.getResultList();
for (Sample row : list) {
    System.out.println(row.getCustomerId());
}

MarkOfHall, My query is not static, I mean in the where clause the conditions will get added based on some conditions. For example, sometime I will consider name into where condtion. Whereaas firstName and age in other condition. So I presume I cannot use @NamedNativeQuery at Entity level. That is why I form the query in String where I append where condition.depends on my few conditions. String q = "select s.customerId, s.name, s.firstName from CustomerEntity s"; Query query = entityManager.createNativeQuery(q,"SampleMapping"); — Alagammal P, Jan 06 '17 at 22:14
You question doesn't ask about dynamic queries. In fact, your query doesn't even have a where clause. — MarkOfHall, Jan 06 '17 at 23:04
Yes I didnt provide info about dynamic query and where cause. But I will have dynamic queries.Is there any other way to handle this? — Alagammal P, Jan 07 '17 at 20:05

select specific columns from the table and store the results to the non-entity POJO with Spring + hibernate + JPA

4 Answers4