aspose file tools*
The moose likes Object Relational Mapping and the fly likes createNativeQuery() JPA question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "createNativeQuery() JPA question" Watch "createNativeQuery() JPA question" New topic
Author

createNativeQuery() JPA question

Matthew Vahedi
Greenhorn

Joined: Jan 05, 2012
Posts: 16
Hi,

A "student" table with student_name, student_id, student_age columns.

I want to run a command in native SQL language ( JPQL not an option ).

Sql command: select student_name from student

The above command does not select all the columns hence I can't use Student entity object as my result Class.

Can you please tell me what is the solution to this problem? ( I tried the following code but I receive: org.hibernate.MappingException: Unknown entity

Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 10113
    
165

Query studentResult= em.createNativeQuery(qs,Object[].class);
List<Object[]> students= studentResult.getResultList();


Have you tried:



[My Blog] [JavaRanch Journal]
Matthew Vahedi
Greenhorn

Joined: Jan 05, 2012
Posts: 16
Thanks Jaikiran. I will try it and will come back with the result.
Matthew Vahedi
Greenhorn

Joined: Jan 05, 2012
Posts: 16
Hi,
This is a Select statement, hence I need to use a result mapping class in the createNativeQuery() method. what that mapping class should look like?


Have you tried:


Matthew Vahedi
Greenhorn

Joined: Jan 05, 2012
Posts: 16
Well,

I just managed to solve the problem, so, I write down the solution here for those who may refer to this post in the future.

Problem:

I wanted to query a table using native sql language ( instead of JPQL ), the problem was that I wanted to query my table on selective columns not all columns.

solution:

You must create a custom Entity class that only contains your selective fields ( not all fields of your table because in that case you will receive "invalid column name" exception ). One important thing to remember is that you must include a primary key in your custom entity class (this is mandatory).

Cheers,
Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 10113
    
165

I don't think you need a dummy entity for any of this. What happened when you tried the code that I posted in my previous reply? I could have tried it myself but I don't have the necessary setup to quickly try this.

Matthew Vahedi
Greenhorn

Joined: Jan 05, 2012
Posts: 16
Hi,

I receive this exception: NumberFormatException for input string "student_name".

Fields in my student table: student_id, student_name, student_age

My query statement: select student_id, student_name from student


Jaikiran Pai wrote:I don't think you need a dummy entity for any of this. What happened when you tried the code that I posted in my previous reply? I could have tried it myself but I don't have the necessary setup to quickly try this.



Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 10113
    
165

Can you post the entire exception stacktrace? That exception is an indication that the query is working fine but somewhere something is incorrectly using student_name as a number.
Matthew Vahedi
Greenhorn

Joined: Jan 05, 2012
Posts: 16
Jaikiran Pai wrote:Can you post the entire exception stacktrace? That exception is an indication that the query is working fine but somewhere something is incorrectly using student_name as a number.


Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 10113
    
165

ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[default-host].[/STUDENT].[mvc-dispatcher]] (http-localhost-127.0.0.1-8080-3) Servlet.service() for servlet mvc-dispatcher threw exception: java.lang.NumberFormatException: For input string: "student_name"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) [rt.jar:1.7.0_40]
at java.lang.Integer.parseInt(Integer.java:492) [rt.jar:1.7.0_40]
at java.lang.Integer.parseInt(Integer.java:527) [rt.jar:1.7.0_40]
at javax.el.ArrayELResolver.toInteger(ArrayELResolver.java:375) [jboss-el-api_2.2_spec-1.0.0.Final.jar:1.0.0.Final]
at javax.el.ArrayELResolver.getValue(ArrayELResolver.java:195) [jboss-el-api_2.2_spec-1.0.0.Final.jar:1.0.0.Final]
at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:175) [jboss-el-api_2.2_spec-1.0.0.Final.jar:1.0.0.Final]
at org.apache.el.parser.AstValue.getValue(AstValue.java:169) [jbossweb-7.0.13.Final.jar:]
at org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:189) [jbossweb-7.0.13.Final.jar:]
at org.apache.jasper.runtime.PageContextImpl.proprietaryEvaluate(PageContextImpl.java:935) [jbossweb-7.0.13.Final.jar:]
at org.apache.jsp.WEB_002dINF.pages.studentList_jsp._jspx_meth_c_005fforEach_005f0(studentList_jsp.java:235)
at org.apache.jsp.WEB_002dINF.pages.studentList_jsp._jspService(studentList_jsp.java:112)


So it really isn't a problem in JPA or Hibernate. Whatever value has been fetched is being displayed in that JSP and it appears that the JSP is written such that it is expecting the string literal "student_name" to be a number, which obviously is wrong. So the fix needs to be done in the JSP.
Matthew Vahedi
Greenhorn

Joined: Jan 05, 2012
Posts: 16
Jaikiran Pai wrote:
So it really isn't a problem in JPA or Hibernate. Whatever value has been fetched is being displayed in that JSP and it appears that the JSP is written such that it is expecting the string literal "student_name" to be a number, which obviously is wrong. So the fix needs to be done in the JSP.


Hi,
Let me write down some of the codes, I couldn't find out what may be wrong in my JSP.
The data type of "student_id" in the table is NUMBER and I guess this is the source of problem, but then the question is how can I use it properly?

My Controller:



My DAO:


My JSP:

Matthew Vahedi
Greenhorn

Joined: Jan 05, 2012
Posts: 16
if I only select the "student_name" I will receive the following error message:

javax.el.PropertyNotFoundException: The class 'java.lang.String' does not have the property 'student_name'.

And if I only select "student_id" which is also my primary key, it throws the following exception:

The class 'java.math.BigDecimal' does not have the property 'student_id'.


I think the definition of .createNativeQuery() method doesn't allow query without entity classes! it's weird. when I use a dummy entity class there is no problem.

Any idea?
Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1649
    
    7

Hmm that does not look right...

you are selecting name and id and expecting a List<String> back?

Why even use a native query at all? This can be done with JPQL



If you wanted to use a native query it would be the same except you would substitute the first line with the one below



This query is so simple that there is not even a difference in query syntax. The point is you can do projection queries with JPQL you don't have to select an entity or use a native query.

Alternatively you can create a little class with the 2 fields you want and use the new operator in the query to create that object to pass around.


I don't see why you cant just fetch the whole Student and put that object or just the fields you want on the model in the controller though. What are you buying yourself by selecting only the name, and id?


[How To Ask Questions][Read before you PM me]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: createNativeQuery() JPA question