[FIXED] Parameter 'status' not found. Available parameters are [0, 1, param1, param2] in Mybatis and Spring

Issue

I'm using mybatis and spring, i want to compare 2 date time and then update data into database

my service and mapper:

 @Override
 public void insertOrUpdateStatusOfSendSMS(BigDecimal smsId, String status) {
     smsBrandingListMapper.insertOrUpdateStatusOfSendSMS(smsId, status);
 }

this is my code (controller class):

smsBrandingList = smsService.selectListSMSBranding(params);
DateTimeFormatter dtFormatter = DateTimeFormatter.ofPattern("dd-MM-yyyy HH:mm");

    for(int i=0 ; i< smsBrandingList.size() ; i++) {

        System.out.println(smsBrandingList.get(i).get("scheduled"));
        System.out.println(smsBrandingList.get(i).get("smsId"));

        BigDecimal smsId = (BigDecimal) smsBrandingList.get(i).get("smsId");
        System.out.println(smsId);

        String smsSendStatusScheduled = (String) smsBrandingList.get(i).get("scheduled");
        LocalDateTime localDateTime = LocalDateTime.parse(smsSendStatusScheduled , dtFormatter) ;

        // get current time now
        LocalDateTime currentTimeNow = LocalDateTime.now();

        System.out.println(localDateTime);
        System.out.println(currentTimeNow);


        if (currentTimeNow.isEqual(localDateTime) ) {

            String status = "Sent";
            smsService.insertOrUpdateStatusOfSendSMS(smsId, status);
            logger.info("CompareTo data and Date -> {}" + SMSConstant.STATUS_SENT);
        }

        else if (currentTimeNow.isAfter(localDateTime) ) {

            String status = "Sent";

            smsService.insertOrUpdateStatusOfSendSMS(smsId, status);
            logger.info("CompareTo data and Date -> {}" + SMSConstant.STATUS_SENT);
        }

        else if (currentTimeNow.isBefore(localDateTime) ) {

            String status = "Waiting";

            smsService.insertOrUpdateStatusOfSendSMS(smsId, status);
            logger.info("CompareTo data and Date -> {}" + SMSConstant.STATUS_WAITING);
        }

        else {
            String status = "Unknown";
            logger.error("CompareTo data and Date -> {3}" + status);
            throw new IllegalStateException("CompareTo data and Date -> {3}" + SMSConstant.STATUS_UNKNOWN);
        }

this is my query:

<update id="insertOrUpdateStatusOfSendSMS" parameterType="Map">
 UPDATE MSC0053M
    SET STATUS = #{status}
    WHERE SMS_ID = #{smsId}
</update>

When i compare date time, base on condition of if, it will update status by smsId, but i run and debug it happens an exception following as:

2021-08-03 18:09:37,707 ERROR [com.coway.trust.config.handler.GlobalExceptionHandler] [defaultException]ex : {}
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter 'status' not found. Available parameters are [0, 1, param1, param2]
  at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:76) ~[mybatis-spring-1.2.3.jar:1.2.3]
  at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:399) ~[mybatis-spring-1.2.3.jar:1.2.3]
  at com.myservice.SMSBrandingListServiceImpl.insertOrUpdateStatusOfSendSMS(SMSBrandingListServiceImpl.java:90) ~[classes/:?]
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_144]
  at com.myservice.SMSBrandingListServiceImpl.insertOrUpdateStatusOfSendSMS(SMSBrandingListServiceImpl.java:90) ~[classes/:?]
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_144]

How I can fix the problem ?

Solution

You used parameterType of sqlmap as Map, so you have to pass parameter of mapper's method with Map. But you didn't.

As @planben commented, without passing map as mapper's parameter, you should define <sql />, but it's too complicated.

Please try code below.

@Override
 public void insertOrUpdateStatusOfSendSMS(BigDecimal smsId, String status) {

     Map<String, Object> mp = Maps.newHashMap();

     mp.put("smsId", smsId);
     mp.put("status", status);

     smsBrandingListMapper.insertOrUpdateStatusOfSendSMS(mp);
 }

Answered By - coding monster
Answer Checked By - Robin (JavaFixing Admin)