Working with entities for native SQL when not adding persistence.xml while using Spring + Hibernate JPA

Question

Please consider the question closed. It seems my usecase does not have a solution while working with JPA 2.0. I had overlooked limitations of JPA 2.0 in this case. JPA 2.0 does not support a custom entity without using persistence.xml.

I am trying to test my application to work with Spring + Hibernate JPA while avoiding a dedicated persistence.xml. Working completely with annotation.

API I am using is Spring 4.3.3 and Hibernate JPA-2.0 on IBM WebSphere 8.0

I do not know of possible gaps for my usecase. It could be a simple miss on my part as well.

Objective is to execute native queries and map the result to a bean class. I am able to test my application with entities. JPA is able to validate mapping for them against actual database tables.

Why use native query and not write complete entity -> Queries involves multiple joins. DO not need to be include all the tables involved in the query as entity.

Part of the application context xml related to jpa looks like this

<bean id="jpaVendorApdapter" class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
    <property name="databasePlatform" value="org.hibernate.dialect.DB2Dialect"></property>
    <property name="showSql" value="true" />
</bean>

<bean id="testEntityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
  <property name="dataSource" ref="testDB2DataSource" />
  <property name="packagesToScan" value="com.app.dao" />
  <property name="jpaVendorAdapter">
     <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
  </property>
  <property name="jpaProperties">
     <props>
        <prop key="hibernate.hbm2ddl.auto">validate</prop>
        <prop key="hibernate.dialect">org.hibernate.dialect.DB2Dialect</prop>
     </props>
  </property>

Adding a simple example for my case The bean class for native query looks like below

public class OwnerDetails implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    @Id
    private String userInitials;

    private String teamCd;

    public String getUserInitials() {
        return userInitials;
    }

    public void setUserInitials(String userInitials) {
        this.userInitials= userInitials;
    }

    public String getTeamCd() {
        return teamCd;
    }

    public void setTeamCd(String teamCd) {
        this.teamCd= teamCd;
    }
}

with the query being

SELECT B.USER_INITS as userInitials, B.TEAM_CD as teamCd FROM APPTEAM A JOIN PQID B ON A.APP_OWNER_ID = B.SIGN_ON_ID WHERE A.APP_NAME = :appName

Code block to execute the native query

Query query = entityManager.createNativeQuery(queryString, className)(QUERY_STRING, OwnerDetails.class);
    query.setParameter("appName", "TESTAPP");


    OwnerDetails  result = (OwnerDetails ) query.getSingleResult();

I get below exception while trying to execute the query

Caused by: org.hibernate.MappingException: Unknown entity: com.app.dao.entity.OwnerDetails

I know this is because not including @Entity annotation But when I include the Entity annotation I get a different error

Caused by: org.hibernate.HibernateException: Missing table: OwnerDetails

......

Caused by: javax.persistence.PersistenceException: [PersistenceUnit: default] Unable to build EntityManagerFactory

I would like to know the missing link to setting up JPA while not using a persistence.xml

Update - as the API is JPA 2.0 Named Native Queries would not be supported

showSql parameter is set to true? if yes what query is printed? — Khalil M
– Khalil M, Commented Nov 11, 2016 at 0:54
and are you sure ownerDetails exists? because to me this not how an entity looks — Khalil M
– Khalil M, Commented Nov 11, 2016 at 1:05
There is not table mapped to owner detail its a native query — Acewin
– Acewin, Commented Nov 11, 2016 at 17:04

Sai Ye Yan Naing Aye · Accepted Answer · 2016-11-14 01:47:01Z

1

Try these steps.

Put @Entity annotation in your class

Put @Table annotation in your class

 @Entity
 @Table(name="YourTableName") //if you do not put name, the dafault value is your class name
 public class OwnerDetails implements Serializable {
 }

Make sure your table is exist or not.

Edit:

If OwnerDetails is not entity and you are using JPA 2.1,then you can use SqlResultSetMapping with ConstructorResult.

edited Nov 14, 2016 at 1:47

answered Nov 11, 2016 at 2:16

Sai Ye Yan Naing Aye

6,74812 gold badges49 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Acewin Over a year ago

OwnerDetails is not mapped to any table. Its a bean I want to use for a native query. Please update the answer or delete it before it is downvoted.

Sai Ye Yan Naing Aye Over a year ago

@Acewin haha. Don't worry about down vote. I don't care. I just give you my opinion. If my way is wrong then everybody can down vote feel freely. If right then it may be good for you.

Acewin Over a year ago

I had totally forgotten limitations of JPA 2.0 in this case

Khalil M · Accepted Answer · 2016-11-11 22:24:46Z

0

If you are using JPA 2.1 version make sure you have these annoation:

@NamedNativeQuery (name = "some name"
query = "SELECT B.USER_INITS as userInitials, B.TEAM_CD as teamCd FROM APPTEAM A JOIN PQID B ON A.APP_OWNER_ID = B.SIGN_ON_ID WHERE A.APP_NAME = :appName"
resulSetMapping = "OwnerDetails")

and:

@SqlResultSetMapping(name="some name", classes = {
    @ConstructorResult(targetClass = OwnerDetail.class, 
    columns = {@ColumnResult(name="userInitials"), @ColumnResult(name="teamCd")})
})

and later you just cast the result. But if you're using JPA version less than 2.1 that is not possible.

edited Nov 11, 2016 at 22:24

answered Nov 11, 2016 at 20:02

Khalil M

1,8682 gold badges25 silver badges39 bronze badges

8 Comments

Acewin Over a year ago

great sounds reasonable will test this and let you know. And where will you place SqlResultSetMapping

Khalil M Over a year ago

actually if you're using under 2.1 v you can implement something to map pojo with sql result

Acewin Over a year ago

Caused by: org.hibernate.HibernateException: Missing table: OwnerDetails. Still the same

Khalil M Over a year ago

which JPA do you have?

Acewin Over a year ago

2.1 hibernate-jpa. Problem is if I add mark my bean class as @Entity it breaks loading of entity manager. and if I remove it query execution breaks

|

Collectives™ on Stack Overflow

Working with entities for native SQL when not adding persistence.xml while using Spring + Hibernate JPA

2 Answers 2

3 Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related