Hi,
I'm new to hibernate( Java). I've a problem while querying the database for fetching the records based on criteria.
For this flow I’m using following technologies
a) hibernate(loading n communication from db)
b) xml (config n external file to load query statements)
c) POJO for implementation (querying and storing in cache )and
d) Oracle coherence ( cache to access and to update data)
The basic Idea is to load set of records from db on regular intervals and do crud operations on objects in cache before persisting them back to db. I’m able to create objects by fetching all records from database but selecting records from database based on condition (like update a record based on customer_id key) is throwing error.
code:
package localDatbase
public class QueryImpl implements java.io.serializable {
public void load(arg0){
/* configuring session */
Configuration cfg = new Configuration();
SessionFactory factory = cfg.configure().buildSessionFactory();
.
// loading query statement from external configuration (XML) file by passing the runtime parameter of key to the table
qQueryStatment = loadQueryStatement(arg0);
// qQueryStatment = "select * from CUSTOMER_PRODUCT where 1=1 AND CUSTOMER_PRODUCT_ID =?"
Query query = session.createSQLQuery(qQueryStatment);
Boolean argType = false
arType = setKeyType(arg0.toString())
if (argType == true) {
argStringValue = arg0.toString();
System.out.println(query. setString (1, argNumValue).toString());
// SQLQueryImpl("select * from CUSTOMER_PRODUCT where 1=1 AND CUSTOMER_PRODUCT_ID =?")
query.setParameter("first",argStringValue );
}
private boolean setKeyType(String keyValue) {
try{
String tempKeyValue = keyValue;
Double ckDbl = Double.valueOf(tempKeyValue);
return false;
}
catch (Exception e) {
return true;
}
}
String loadQueryStatement(){
Thread.currentThread().getContextClassLoader().getResourceAsStream("LoadSchema.xml");
xml = XmlObject.Factory.parse(Thread.currentThread().getContextClassLoader().getResourceAsStream("LoadSchema.xml"),validateOptions);
.
if (bBeanType.getLoadQuery()== null){
System.out.println("loadQuery is not working");
}
else{
String qQueryStatment = bBeanType.getLoadQuery().getMainQuery().trim(); // Select *from Coustomer_product where 1=1
if (qQueryStatment != null) {
.
.
String appendQueryStatement = qQueryConditionArr[countList].getConditionClause(); // fetches respective condtion statement from xml file
qQueryStatment = qQueryStatment + " " + appendQueryStatement ;
// qQueryStatment = "select * from CUSTOMER_PRODUCT where 1=1 AND CUSTOMER_PRODUCT_ID =?"
return qQueryStatment;
}


LoadSchema.xml
<xs:beanList xmlns:xs="http://dell.com/it/services/sdr/load/config/LoadSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://dell.com/it/services/sdr/load/config/LoadSchema.xsd ">
<xs:bean>
<xs:className> localDatbase.CustomerProduct </xs:className>
<xs:tableName>SDR.CUSTOMER_PRODUCT</xs:tableName>
<xs:loadAllQuery><![CDATA[select * from (SELECT * FROM CUSTOMER_PRODUCT partition(#PName) A WHERE shipped_date between add_months(sysdate,-1*7*12) and sysdate) as of SCN &SCN]]></xs:loadAllQuery>
<xs:loadQuery>
<xs:mainQuery>"select * from CUSTOMER_PRODUCT where 1=1 </xs:mainQuery>
<xs:conditionList>
<xs:condition>
<xs:conditionName>CONDITION_CUSTOMER_PRODUCT</xs:conditionName>
<xs:conditionClause> AND CUSTOMER_PRODUCT_ID =?"</xs:conditionClause>
</xs:condition>
</xs:conditionList>
</xs:loadQuery>
<xs:Key>getCustomerProductId</xs:Key>
</xs:bean>

error stack

localDatbase.QueryImpl.load Error in database(custom code) : - java.lang.IllegalArgumentException: Positional parameter does not exist: 1 in query: "select * from CUSTOMER_PRODUCT where 1=1 AND CUSTOMER_PRODUCT_ID =?"
2011-08-13 21:39:19.428/34.401 Oracle Coherence GE 3.6.1.0 <Info> (thread=DistributedCache, member=1): java.lang.IllegalArgumentException: Positional parameter does not exist: 1 in query: "select * from CUSTOMER_PRODUCT where 1=1 AND CUSTOMER_PRODUCT_ID =?"
at
org.hibernate.impl.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:382)
at org.hibernate.impl.AbstractQueryImpl.setBigDecimal(AbstractQueryImpl.java:705)
at com.dell.it.services.sdr.loader.SDRCacheInitialLoadImpl.load(QueryImpl.java:223)
tried and tested all possible ways of named parameters. But no luck.
any help is highly appreciable.

Thanks in advance.

P.S. all comments provided between code is output on that checkpoint on code.