• 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic