Dynamic column name in WHERE clause using MyBatis

Question

I would like to write dynamic query where column name and column value might change in WHERE clause of SELECT statement.

So far I have this:

Mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper PUBLIC
        "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="mypackage.PersonMapper">

    <resultMap id="personMap" type="Person">
        <id property="name" column="NAME"/>
        <id property="lastname" column="LASTNAME"/>
    </resultMap>

    <select id="selectByColumnName" resultType="Person" resultMap="personMap"><![CDATA[
        SELECT * FROM PERSON WHERE #{columnName} = #{columnValue}
    ]]></select>
</mapper>

Mapper interface:

public interface PersonMapper {
    List<PersonModel> selectByColumnName(
            @Param("columnName") String column, @Param("columnValue") String value
    );
}

But when I call it personMapper.selectByColumnName("NAME", "John"), it does not work and gives me empty result. If I replace my SELECT query in Mapper.xml with query where I specify column name like this :

SELECT * FROM PERSON WHERE NAME = #{columnValue}

then it works fine and returns all persons with the given dynamic columnValue.

glw · Accepted Answer · 2019-03-01 21:04:18Z

3

You have to use ${} string substitution:

    <select id="selectByColumnName" resultType="Person" resultMap="personMap"><![CDATA[
        SELECT * FROM PERSON WHERE ${columnName} = #{columnValue}
    ]]></select>

answered Mar 1, 2019 at 21:04

glw

1,6641 gold badge17 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic column name in WHERE clause using MyBatis

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related