This week's giveaway is in the Programmer Certification (OCPJP) forum.
We're giving away four copies of Java Mock Exams (software) and have David Mayer on-line!
See this thread for details.
Win a copy of Java Mock Exams (software) this week in the Programmer Certification (OCPJP) forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

JPA named Query

 
Pranav Kristam
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

SELECT * FROM INM_C_SUPPLIER_INFO e , INM_CERTIFICATE p WHERE e.sachnr ='7160790' and e.sachnr = p.partnum and e.lief_nr= substr(p.supplier, 0, 6)

I am new to JPA and looking some help in writing JPA named query for the above sql statement.

The below one is not completed and till now I am trying like this :

@NamedQueries({ @NamedQuery(name = InmetroSupplierEntity.QUERY_FOR_SNR, query = "SELECT e FROM "

+ InmetroSupplierEntity.NAME + " e , " + InmetroCertifBasisEntity.NAME + " p WHERE e.snr = :"

+ InmetroSupplierEntity.PARAM_SNR + " AND e.snr = p.partnum" , hints = { @QueryHint(name = TopLinkQueryHints.REFRESH, value = HintValues.TRUE) }) })


Thanks
 
Pranav Kristam
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My application connects with Oracle and Junit connects with HSQL database. I am using in built SUBSTRING function in the named query which is working fine with Oracle database but not with HSQL database (2.2.4 version). To ensure I have tested by passing a hard coded value instead of SUBSTRING and it works fine with HSQL.

Hard coded named query

@NamedQueries({ @NamedQuery(name = InmetroSupplierEntity.QUERY_FOR_SNR, query = "SELECT e FROM "
+ InmetroSupplierEntity.NAME + " e , " + InmetroCertifBasisEntity.NAME
+ " p WHERE e.snr = p.partnum AND e.snr = :" + InmetroSupplierEntity.PARAM_SNR
+ " AND e.supplierNumber = '135245'", hints = { @QueryHint(name = TopLinkQueryHints.REFRESH, value = HintValues.TRUE) }) })

Actual named query

@NamedQueries({ @NamedQuery(name = InmetroSupplierEntity.QUERY_FOR_SNR, query = "SELECT e FROM "
+ InmetroSupplierEntity.NAME + " e , " + InmetroCertifBasisEntity.NAME
+ " p WHERE e.snr = p.partnum AND e.snr = :" + InmetroSupplierEntity.PARAM_SNR
+ " AND e.supplierNumber = SUBSTRING(p.supplier,0,6)", hints = { @QueryHint(name = TopLinkQueryHints.REFRESH, value = HintValues.TRUE) }) })

It seems to be there is some limitations with SUBSTRING function in HSQL and can you please provide some inputs in these named query to make it work with both Oracle and HSQL.
 
Happiness is not a goal ... it's a by-product of a life well lived - Eleanor Roosevelt. Tiny ad:
the new thread boost feature: great for the advertiser and smooth for the coderanch user
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!