I am trying to do an update using @update annotation. The query triggers fine without any exceptions but method returns 0 every time (0 row updated). No update is happening in the db. and same query is working fine from SQLdeveloper tool. Using Oracle db.
@Update(
"UPDATE extra.EMMT SET CASE_STATUS = #{updateBean.CASE_STATUS}, CASE_STATUS_TimeStmp = #{updateBean.CASE_STATUS_TimeStmp} WHERE T_TimeStmp >= #{updateBean.LAST_T_TimeStmp} AND T_TimeStmp <= #{updateBean.T_TimeStmp} AND T_NO = #{updateBean.T_NO} AND EM_NO = #{updateBean.EM_NO}"
)
public long update(@Param("updateBean") EMMT updateBean);
"EMMT updateBean" is class has same members as the columns the table EMMT.
and I also tried creating two different sessions one for update and other is for insert, but didn't help much.
Session config.
@Bean(name = "updatesession")
public SqlSessionFactory sqlSessionFactoryupdate() throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(dataSource);
SqlSessionFactory sqlSessionFactory = factoryBean.getObject();
sqlSessionFactory.getConfiguration().setJdbcTypeForNull(JdbcType.NULL);
sqlSessionFactory.getConfiguration().setDefaultStatementTimeout(15);
sqlSessionFactory.getConfiguration().addMappers("com.xyz.myapp.mapper");
return sqlSessionFactory;
}
Using Mybatis-spring - 2.2.0
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
Any help would be apricated. Thanks.
EXAMPLE - configuration for sqlSession is given above.
bean class.
class justForUpdate {
String CASE_STATUS;
String EM_NO ;
Timestamp T_TimeStmp;
Long T_NO ;
Timestamp CASE_STATUS_TimeStmp;
Timestamp LAST_T_TimeStmp;
}
service class
updateservice {
@Autowired
private SqlSessionFactory sessions;
public void work() {
//obj of justForUpdate = auth
//or can pass list of justForUpdate objs.
try(SqlSession session = sessions.openSession(true)){
Update_mapper upd = session.getMapper(Update_mapper.class);
long val = upd.update(auth);
System.out.print(">>>>>>>>> "+val);
}
}
}
Update_Mapper
@Mapper
public interface Update_mapper {
@Update(
"UPDATE extra.EMMT SET CASE_STATUS = #{updateBean.CASE_STATUS}, CASE_STATUS_TimeStmp = #{updateBean.CASE_STATUS_TimeStmp} WHERE T_TimeStmp >= #{updateBean.LAST_T_TimeStmp} AND T_TimeStmp <= #{updateBean.T_TimeStmp} AND T_NO = #{updateBean.T_NO} AND EM_NO = #{updateBean.EM_NO}"
)
public long update(@Param("updateBean") EMMT updateBean);
}
For anyone else who might bump into this same stupid problem.
the quick fix was using "TRIM()" in my query with column name. Some columns were defined as 40 bytes. hence containing numbers of spaces.
@Update(
"UPDATE extra.EMMT SET CASE_STATUS = #{updateBean.CASE_STATUS}, CASE_STATUS_TimeStmp = #{updateBean.CASE_STATUS_TimeStmp} WHERE T_TimeStmp >= #{updateBean.LAST_T_TimeStmp} AND T_TimeStmp <= #{updateBean.T_TimeStmp} AND TRIM(T_NO) = #{updateBean.T_NO} AND TRIM(EM_NO) = #{updateBean.EM_NO}")
public long update(@Param("updateBean") EMMT updateBean);
}