CODEWITHSUNDEEP
annotationsmybatismapper
hmmh
hmmh

Reputation: 57

mybatis annotation select query with parameter which is use multiple times in conditions

I have a problem with mybatis annotation query with following error.

org.apache.ibatis.binding.BindingException: Parameter 'strDateStart' not found. Available parameters are [0, 1, param1, param2]

The following is the code in the mapper class.


    `@Select("SELECT * FROM Result WHERE"AND proc_date >= '#{strDateStart}'"+
    "AND proc_date >= '#{strDateEnd}'"+
    "AND update_date <= '#{strDateStart}'"+
    "AND update_date <= '#{strDateEnd}'")
    public ArrayList<ResultDao> select(String strDateStart,String strDateEnd);`

Giving the same name with the parameters in query and args in method and can I use multiple times in conditions with that same parameter?

Upvotes: 1

Views: 4799

Answers (3)

27 hung
27 hung

Reputation: 1

I used @SelectProvider to set params Map and this work for me:

Mapper class: 

@SelectProvider(type = MyBatisSqlProvider.class, method = "getSqlString")
    public List<Map<String, Object>> select(@Param("sql") String sqlString, @Param("sqlParams") Map<String, Object> sqlParams);

Provider class: 

    String sqlString = (String) params.get(SQL_PATH);
        Map<String, Object> sqlParams = (Map<String, Object>) params.get(SQL_PARAM);

        if (sqlParams != null) {
            params.putAll(sqlParams);
        }
return sqlString;

My table:

create table TBLSAMPLE (id int primary key auto_increment, name varchar,  name1 varchar);
insert into TBLSAMPLE (name,name1) values ('test','test');
insert into TBLSAMPLE (name,name1) values ('test','test');
insert into TBLSAMPLE (name,name1) values ('test','test');
insert into TBLSAMPLE (name,name1) values ('test','test');
insert into TBLSAMPLE (name,name1) values ('qa','test');

and tested with sql : SELECT * FROM TBLSAMPLE WHERE name= #{name} AND name1= #{name}

Upvotes: 0

hmmh
hmmh

Reputation: 57

The problem was solved by
1. Delete the single quotes that surround the variables #{strDateXXXX}
2. Creating a class for that conditions as below code.


    `public class SelectConditions {
    String strDateStart;
    String strDateEnd;
    public String getStrDateStart() {
        return strDateStart;
    }
    public void setStrDateStart(String strDateStart) {
        this.strDateStart = strDateStart;
    }
    public String getStrDateEnd() {
        return strDateEnd;
    }
    public void setStrDateEnd(String strDateEnd) {
        this.strDateEnd = strDateEnd;
    }
    public SelectConditions(String strDateStart, String strDateEnd) {
        this.strDateStart = strDateStart;
        this.strDateEnd = strDateEnd;
    }`


3. Change the mapper class by passing the condition class


    `@Select("SELECT * FROM Result WHERE"AND proc_date >= #{strDateStart}"+
        "AND proc_date >= #{strDateEnd}"+
        "AND update_date <= #{strDateStart}"+
        "AND update_date <= #{strDateEnd}")
    public ArrayList select(SelectConditions conditions)`

Upvotes: 1

Ian Lim
Ian Lim

Reputation: 4274

Have you tried this instead?

@Select("SELECT * FROM Result WHERE"AND proc_date >= '#{strDateStart}'"+ "AND proc_date >= '#{strDateEnd}'"+ "AND update_date <= '#{strDateStart}'"+ "AND update_date <= '#{strDateEnd}'") public ArrayList<ResultDao> select( @Param("strDateStart") String strDateStart, @Param("strDateEnd") String strDateEnd);

Upvotes: 0

Related Questions