Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

createNativeQuery() JPA question

 
Matthew Vahedi
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 10447
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Query studentResult= em.createNativeQuery(qs,Object[].class);
List<Object[]> students= studentResult.getResultList();


Have you tried:


 
Matthew Vahedi
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jaikiran. I will try it and will come back with the result.
 
Matthew Vahedi
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 10447
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 10447
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 10447
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1682
7
Android IntelliJ IDE Linux Mac OS X Spring
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic