0

I have a query that is not producing any results even though there is definitely data in the underlying database corresponding with the query. The query is being run by hibernate, and I want to validate it by running the query directly in mysql. Can someone help me translate from hibernate to SQL so that I can see if the query produces any results?

Here is the hibernate query that the eclipse console says is being run: 

select
 drugword0_.name as name1_12_2_,
 concepts1_.word as word1_12_4_,
 drugconcep2_.rxcui as rxcui2_13_4_,
 drugconcep2_.rxcui as rxcui1_10_0_,
 atoms3_.rxcui as rxcui3_10_5_,
 atoms3_.rxaui as rxaui1_39_5_,
 atoms3_.rxaui as rxaui1_39_1_,
 atoms3_.rxcui as rxcui3_39_1_,
 atoms3_.str as str2_39_1_ 
from drugwords drugword0_
 left outer join drugwordsconsoJunction concepts1_ on drugword0_.name=concepts1_.word
 left outer join drugconcepts drugconcep2_ on concepts1_.rxcui=drugconcep2_.rxcui
 left outer join rxnconso atoms3_ on drugconcep2_.rxcui=atoms3_.rxcui where drugword0_.name=?

How do I convert that hibernate query into working syntax for a sql query of a MySQL database? And why do you think my hibernate query is not returning any results? Are the hibernate mappings set up incorrectly?

To help people understand what hibernate is starting with, I am posting some code from my spring mvc hibernate app. NOTE: To keep this posting brief, I have uploaded some relevant code to a file sharing site. You can view the code by clicking on the following links:
The code for the DrugWord entity is at this link.
The code for the DrugConcept entity is at this link.
The code for the DrugAtom entity is at this link.
The code to create the underlying data tables in MySQL is at this link.
The code to populate the underlying data tables is at this link.
The data for one of the tables is at this link.
The data for the other table is at this link. (This is a big file, may take a few moments to load.)

To help people visualize the underlying data, I am including a print screen of the top 2 results of queries showing data in the underlying tables as follows:

Community
  • 1
  • 1
CodeMed
  • 10,926
  • 68
  • 193
  • 335

1 Answers1

0

ORMs sometimes have a rather unique view on how to translate their query language to SQL, which may lead to various issues ranging from slow queries to odd or absent results.

Therefore I'd advise you to activate the query log (see here or here), that will be the SQL that actually gets sent to the server.

Community
  • 1
  • 1
fvu
  • 31,838
  • 5
  • 60
  • 78
  • Thank you for taking the time to answer. The links you sent do not explain key steps for someone who has never used the technologies before. Are you able to write out explicit instructions for someone who is new to hibernate and MySQL? – CodeMed Mar 26 '14 at 21:42
  • Actually both linked answers also contain links to the Hibernate help where configuring logging is explained: http://docs.jboss.org/hibernate/core/4.1/manual/en-US/html/ch03.html#configuration-logging ... – fvu Mar 26 '14 at 21:46