update using createSQLQuery problem

thinkspace · **Joined:** Mon Oct 30, 2006 2:31 am **Posts:** 8

Hi everyone,

i am having a problem updating my database, below are my codes:

my mapping:

Code:

<hibernate-mapping auto-import="true" default-lazy="true">

   <class name="net.nationalpayment.pps.model.RecentItem" table="USR_MRU">
      <composite-id name="id" class="net.nationalpayment.pps.model.RecentItemId" unsaved-value="undefined">
         <key-property name="userId"  column="UMR_USRID" type="string" length="30"/>
         <key-property name="itemDate" column="UMR_DT" type="timestamp" length="6"/>
      </composite-id>            
      <property name="itemValue" column="UMR_MRUVAL" type="string" length="30"/>
      <property name="itemLink" column="UMR_MRULINK" type="string" length="255"/>
      <property name="type" column="UMR_TYPE" type="string" length="255"/>
      
      <property name="auditInfo" type="net.nationalpayment.pps.model.AuditInfoUserType">
            <column name="LAST_UPDATED_ON"/>
            <column name="LAST_UPDATED_BY"/>
            <column name="CREATED_DATE"/>
            <column name="CREATED_BY"/>
        </property>
   </class>

</hibernate-mapping>

java code in dao:

Code:

Session session = this.getSessionFactory().openSession();
String queryStr = "update USR_MRU set UMR_DT=to_date('"+ this.convertDateToString(recentItem.getId().getItemDate()) +"', 'yyyy-mm-dd hh24:mi:ss'), UMR_TYPE='"+recentItem.getType()+"' where UMR_USRID='"+recentItem.getId().getUserId()+"' and UMR_MRUVAL='"+recentItem.getItemValue()+"'";

try{            
   session.createSQLQuery(queryStr);
}finally{
   session.close();
}

private String convertDateToString(java.util.Date date){
      java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
      return sdf.format(date);
   }

i used hibernate 3.1.3, spring and oracle db. my problem is it doesn't update the record in the database. does anyone encountered and solved this issue? please share your thought. thanks in advance.

regards,
thinkspace

thinkspace · **Joined:** Mon Oct 30, 2006 2:31 am **Posts:** 8

can anyone give some ideas, tips or suggestions? help greatly appreciated.

regards,
thinkspace

user_who_lost_his_account · **Joined:** Fri Jul 27, 2007 4:39 am **Posts:** 7

It would help, if you indicated the kind of error you have received (error message, stack trace ...).

If you only execute SQL directly, I cannot understand what you use the mapping for. In order to call an HQL statement you can use createQuery (without SQL) instead. Actually, there is one statement missing in your code: you should executed the statement after creating it. In this case, you should call executeUpdate().

thinkspace · **Joined:** Mon Oct 30, 2006 2:31 am **Posts:** 8

i wonder why you guys don't give any feedback coz the answer is so simple. i figure it out. tnx for your time viewing my thread.

regards,
thinkspace

satsranchuser · **Posted:** Fri Jul 27, 2007 11:59 am

Can you check the AutoCommit Parameter.

thinkspace · **Joined:** Mon Oct 30, 2006 2:31 am **Posts:** 8

user_who_lost_his_accountuser_who_lost_his_account, your right. what i did is:

Code:

String queryStr = "update RecentItem set id.itemDate=to_date('"+ this.convertDateToString(recentItem.getId().getItemDate()) +"', 'yyyy-mm-dd hh24:mi:ss'), type='"+existingItem.getType()+"' where id.userId='"+existingItem.getId().getUserId()+"' and itemValue='"+existingItem.getItemValue()+"'";
         
try{
    this.getSession().createQuery(queryStr).executeUpdate();
}catch(Exception e){
   e.printStackTrace();
}
this.getSession.flush();

and it works fine now.

tnx,
thinkspace