4.Mapper.xml Mapping File Details

Keywords: Attribute SQL Mybatis JDBC

Important attributes in tags in mybatis:

1. parameterType (input type)

First understand: #{} and ${} usage
# {} implements setting parameter values to prepareStatement preprocessing statements. In sql statements #{} represents a placeholder, that is?.
The use of placeholder {} can effectively prevent sql injection, and mybatis automatically converts java type to jdbc type without concern for the type of parameter value.
# Simple type values or pojo attribute values can be accepted, and if parameterType transmits a single simple type value, #{} brackets can be value or other names.
Unlike ${} and #{}, parameterType can be spliced into sql by ${} without jdbc type conversion, and ${} can accept simple type values or pojo attribute values. If parameterType transmits a single simple type value, ${} can only be value in parentheses.
Using ${} does not prevent sql injection, but sometimes it's very convenient to use ${} for vague queries.
About {} and ${}:
1. {} Setting the parameter value to the preprocessing statement in the prepareStatement corresponds to? ____________
2. {} prevents sql injection. If parameterType transfers a single simple type value, {} brackets can be value or other names. (Value is usually written or attribute names are used.)
3. If parameterType transports a single simple type value, ${} can only be value in parentheses.

When using #{}, simple types can be arbitrary values, usually #{value}, or field names for beans, eg: #{id}.

    <select id="findUserById" parameterType="int"
    resultType="cn.com.yves.project1.model.UserModel">
    SELECT * FROM `user` WHERE id=#{any value}
    </select>

When using ${}, the simple type can only be ${value}.

<select id="selectUserByName" parameterType="string"
    resultType="
    cn.com.yves.project1.model.UserModel ">
    select * from user where
    username like '%${value}%'
</select>
  • Transfer - Simple Type
    <select id="findUserById" parameterType="int"
    resultType="cn.com.yves.project1.model.UserModel">
    SELECT * FROM `user` WHERE id=#{any value}
    </select>
  • Transfer-pojo object
<!—transmit pojo Object-based comprehensive query for user information:Designated sex,Fuzzy name -->
<select id="findUserByUser" parameterType="cn.com.yves.project1.model.UserModel"
    resultType="cn.com.yves.project1.model.UserModel">
    select * from user where sex=#{sex} and username like
    '%${username}%'
</select>
  • Transfer-pojo wrapper object
    In development, query conditions are passed through pojo. The query conditions are comprehensive query conditions, including not only user query conditions but also other query conditions.

pojo Code:

 public class UserModel {
    private int id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    //The get and set methods are omitted here
}
public class QueryFilterModel {
    private UserModel uModel;

    // Define extended classes for UserModel
    // private UserCustomModel customModel;

    // Some other fields or javaBean s that need to be defined
    // The corresponding get and set methods are omitted here.
}

mapper.xml mapping file code:

<!-- Advanced Query Section -->
<select id="findUserByQueryFilterModel" parameterType="cn.com.yves.project1.model.QueryFilterModel"
        resultType="cn.com.yves.project1.model.UserModel">
        SELECT * FROM `user` WHERE sex=#{uModel.sex} and username LIKE
        '%${uModel.username}%'
</select>

Description: mybatis bottom obtains attribute value from pojo through ognl: {uModel.sex}, uModel is the attribute of the imported packaging object QueryFilterModel. Sex is also an attribute in the uModel object.

  • Transfer-hashmap

Interface method:

// Query multiple results based on multiple IDS
public abstract List<UserModel> findUsersByMap(HashMap<String, Object> map) throws Exception;

Mapping file:

<!-- The afferent parameter is hashmap, #Fill in the key in the map to get the corresponding value - >.
<select id="findUsersByMap" parameterType="hashmap"
    resultType="cn.com.yves.project1.model.UserModel">
    SELECT * FROM `user`
    WHERE sex=#{sex} and username=#{name}
</select>

Test code:

    // A Method of Testing hashmap Input Parameter
    @Test
    public void testFindUsersByMap() throws Exception {
        // Generating SqlSession connection
        SqlSession sqlSession = sqlFactory.openSession();

        // Generate mapper proxy object, equivalent to the implementation class of the interface
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        HashMap<String, Object> map = new HashMap<>();
        map.put("sex", "male");
        map.put("name", "yves");

        // Calling method
        List<UserModel> list = userMapper.findUsersByMap(map);

        // Close sqlSession
        sqlSession.close();
    }

2. resultType (output type)

Posted by texelate on Fri, 05 Apr 2019 20:21:30 -0700