• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate: getById query

 
Samar Land
Ranch Hand
Posts: 60
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello All,
I'm trying to run this query using hibernate
The query:

Dao code:


service


In Controller I'm passing the list to Jsp as list to display the appearances as dropdown list


I'm getting the dropdown list empty. shouldn't be an error in my JSP since it's working with getAllAppearance() method.

well, I'm confused, should I pass the driverId or the appearanceId or none? I've tried them all and get the same result

Also I should mention, it's a manyTomany relationship between driver and appearance
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Samar Land wrote:well, I'm confused, should I pass the driverId or the appearanceId or none? I've tried them all and get the same result

You know your data better than us, so you should know if the driverId and/or appearanceId should be passed as a parameter

Trying them all and getting the same result makes sense with the code you shared, because in your query you are not using a driverId or appearanceId at all. And when you execute the query, you are also not setting the driverId or appearanceId as a parameter. Based on the method name getAppearanceByDriverId you'll need a driverId and your query should look likeAnd then your dao code would look like

Hope it helps!
Kind regards,
Roel
 
Samar Land
Ranch Hand
Posts: 60
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your replaying, It helps me to understand more,

By Running the query below, it gets me the result I want in the DB



Based on the method name getAppearanceByDriverId

thinking more about it, The name should be (getDriverAppearances)

So, Do I still need to set parameter in Dao based on this query? if so, How should I do so without passing the parameter?

 
Samar Land
Ranch Hand
Posts: 60
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Getting this exception

org.hibernate.hql.internal.ast.QuerySyntaxException: expecting IDENT, found '*' near line 1, column 10 [SELECT a.* FROM appearance a, appearance_driver_xref av WHERE a.appearance_id = av.appearance_id And av.driver_id = d.driver_id]
 
Samar Land
Ranch Hand
Posts: 60
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Reading more about the error I'm getting I found that I have to rename the table name in the HQL to be used the class name instead

But not sure about the appearance_driver_xref table since I don't have a class for it. I'm just mapping the table:


Code in Dao

The exception I'm getting:


org.hibernate.hql.internal.ast.QuerySyntaxException: appearance_driver_xref is not mapped [SELECT a FROM com.menards.racing.integration.vo.Appearance a, appearance_driver_xref av WHERE a.appearance_id = av.appearance_id And av.driver_id = d.driver_id]
org.hibernate.hql.internal.ast.QuerySyntaxException.generateQueryException(QuerySyntaxException.java:96)
org.hibernate.QueryException.wrapWithQueryString(QueryException.java:120)
org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:234)
org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158)
org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:126)
org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:88)
org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:190)

Reference topic:
http://www.coderanch.com/t/415121/ORM/databases/HIBERNATE-hql-ast-QuerySyntaxException-table


 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Samar Land wrote:So, Do I still need to set parameter in Dao based on this query? if so, How should I do so without passing the parameter?

The only thing you have done so far is posting some code snippets (luckily the appropriate ones and limited to just a few lines, so for that).

But you have not mentioned the most important thing: what do you want to achieve? Which results are you looking for? We can't know what you are trying to achieve. So if you don't know it yourself and want to get some good advice, you'll have to TellTheDetails about what you are trying to do here. Are you going for all appearances of one specific driver (based on a driver id)? Do you want a list of all appearances with the driver info included? Or do you want for each driver a list of all its appearances?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Samar Land wrote:Reading more about the error I'm getting I found that I have to rename the table name in the HQL to be used the class name instead

But not sure about the appearance_driver_xref table since I don't have a class for it. I'm just mapping the table:

If you decide to use plain SQL, you can use a native query which allows you to use the join table appearance_driver_xref. But if you want to use JPQL (or HQL), you can only use entities and their attributes (otherwise you'll get an exception as you experienced). So you can't use the join table appearance_driver_xref in your JPQL query because it's not an entity (as you already stated correctly).

For example, imagine two entities Appearance and DriverThen you'll need a query similar to this one to get all appearances for a given driver id (as you can see no mention of the join table appearance_driver_xref).

Hope it helps!
Kind regards,
Roel
 
Samar Land
Ranch Hand
Posts: 60
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're right ..
I should explain more,

I do have two different appearance (driver_vehicle) saveing the data to same table(Appearance)
So, what I'm trying to do is pull the driver appearance without the vehicle one.


I have a form contains appearance dropdown list, and by selecting the appearance, the other fields will show up to edit them,
I was getting all the appearances that I saved into the appearances table and It was working correctly. using this code in the DAO


now I'm trying to get just the driver appearance instead, to pass it into my dropdown list.

In SQL quires, I got the result but I still having issue in HQL



 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Samar Land wrote:In SQL quires, I got the result but I still having issue in HQL

And which SQL query give you the desired result?
 
Samar Land
Ranch Hand
Posts: 60
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Roel for being help me

for appearance driver


and appearance vehicle





 
Samar Land
Ranch Hand
Posts: 60
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


trying this out, I got an Exception about not setting the parameter when I hit the JSP page


org.hibernate.QueryException: Not all named parameters have been set: [driverId] [SELECT a FROM Appearance a JOIN a.listOfDrivers d WHERE d.driverId = :driverId]
org.hibernate.internal.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:401)
org.hibernate.internal.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:385)
org.hibernate.internal.QueryImpl.list(QueryImpl.java:99)



How will I set the parameter?


when I tried this: I got nullPointerException(ofcourse)


I Also tried to pass the driverId as paramater


 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Samar Land wrote:So, what I'm trying to do is pull the driver appearance without the vehicle one.

If I understand correctly. You have a driver table and a vehicle table. Both can have appearances which are saved in the appearance table. Because both are many-to-many relationships you'll have two additional tables: one to store the driver-appearance relationship and another one to store the vehicle-appearance relationship.
For this query you want to list all appearances from the driver-appearance table. So it should list all appearances linked with the driver table (so no appearances linked with the vehicle table should be listed).

Correct?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Samar Land wrote:How will I set the parameter?

Setting the parameter is only required if you want the appearances for one single specific driver. If you are looking for all appearances related to any driver, setting the parameter is not required at all.

Samar Land wrote:when I tried this: I got nullPointerException(ofcourse)

You'll get the NullPointerException probably because the driver id is of type Long (the primitive wrapper class, not the primitive data type). And when you create a new driver, the default value is null and when null is unboxed to long (the primitive data type), you'll get a NullPointerException.
 
Samar Land
Ranch Hand
Posts: 60
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
For this query you want to list all appearances from the driver-appearance table. So it should list all appearances linked with the driver table (so no appearances linked with the vehicle table should be listed).
Correct?

100% that what I want to do
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Samar Land wrote:100% that what I want to do

Great! Then we need to do just one thing: getting the appropriate JPQL/HQL query I have never created a query like that, so it might be some trial and error with a few failed attempts along the road.

Could you try executing this query
 
Samar Land
Ranch Hand
Posts: 60
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


It worked from the first try ;)!
Thanks a lot Roel! you really helped me a lot to understand more than I was planning to do in this topic.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Samar Land wrote:It worked from the first try ;)!

Yay!

Samar Land wrote:Thanks a lot Roel! you really helped me a lot to understand more than I was planning to do in this topic.

Glad to hear you now have a better understanding of these concepts.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic