Problem : I am getting one exception while trying to execute a query using hibernate
org.hibernate.QueryException: Not all named parameters have been set: [] [from FunctionPointDO f where f.workPackage='work package : id1' and f.workPackageIdentifier='work : ref1' and f.startDate='03-Jan-2010' and f.goLiveDate='04-Feb-2010' and f.id!='1000100']
at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:114)
at org.hibernate.impl.AbstractQueryImpl.verifyParameters(AbstractQueryImpl.java:101)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:70)
at com.bt.fp.server.Utils.Utils.listAndCast(Unknown Source)
at com.bt.fp.server.database.dao.FunctionPointDAO.isDuplicate(Unknown Source)
at com.bt.fp.server.AddUpdateFPData.doPost(Unknown Source)
It seems that this error is due to the “:” which is used as a value in f.workPackage='work package : id1' and f.workPackageIdentifier='work : ref1'. I checked on net and hibernate considers anything starting with colon (: ) as a variable, and here id1 and ref1 are treated as variable whose value is not set anywhere. This error comes only when colon has spaces around it.
How can I escape colon. As these values are entered by users, I don’t have any control over whatever input they provide.
It is solved as below:
ReplyDeletei was setting values in query itself like
String hql = "from FunctionPointDO f where f.workPackage='"+workPackage+"' and f.workPackageIdentifier='"+workPackageId+"' and f.startDate='"+startDate+"' and f.goLiveDate='"+goLiveDate+"' and f.id!='"+sequenceNo+"'";
so when a value comes with colon in it, it is considered as hibernate variable.
So i myself used variables in query and set the value of those variables later
String hql = "from FunctionPointDO f where f.workPackage=:parWorkPackage and f.workPackageIdentifier=:parWorkPackageId and f.startDate =:parStartDate and f.goLiveDate=:parGoLiveDate and f.id!=:parSequenceNo";
Query query = session.createQuery(hql);
query.setParameter("parWorkPackage", workPackage);
query.setParameter("parWorkPackageId", workPackageId);
query.setParameter("parStartDate", startDate);
query.setParameter("parGoLiveDate", goLiveDate);
query.setParameter("parSequenceNo", sequenceNo);
This solved the problem as values are not set directly in query but later while preparing statement as in case of PreparedStatement in JDBC
The query should not contain colon, if it contains a colon, SQL treats it as a variable
Basically, the problem was the prepare a query with values having colon in it..
ReplyDeleteFor this one must used PreparedStatement of JDBC, rather than creating a String query.