Mybatis parameter processing

Parameter processing

Single parameter

adopt#{parameter name}Take out the parameter value

The interface is defined as follows

public User getUserByUserName(String userName);

Mapper corresponding to the interface The XML definition is as follows

<select id="getUserByUserName"
        parameterType="String"
        resultType="com.example.mybatis.entity.User">
    select
    id, username, user_email userEmail, user_city userCity, age
    from user
    where username = #{username}
</select>

Multiple parameters

Mybatis will do special processing and encapsulate multiple parameters into a map,keybyparam1, param2,..., paramN, value isvalue1,value2,...valueN, you can#{param1}To getvalue1The value of,#{paramN}To getvalueNValue of.value1,value2,...valueNIs the value we passed through the method.

The interface is defined as follows

public User getUserByIdAndUserName(Integer id, String username);

Mapper corresponding to the interface The XML definition is as follows. The two parameters are integer type and string type respectively. The types are inconsistent. In this case, the parametertype can be omitted.

<select id="getUserByIdAndUserName"
        resultType="com.example.mybatis.entity.User">
    select
    id, username, user_email userEmail, user_city userCity, age
    from user
    where id = #{param1}
    and username = #{param2}
</select>

It should be noted here that two parameters are mapped through #{Param1} and #{param2}, and so on. If there is a third parameter, use #{param3} to map.

Named parameters

When mybatis encapsulates parameters, the user specifies the key of the map

The interface is defined as follows

public User getUserByIdAndUserName(@Param("id")Integer id, @Param("username")String username);

Mapper corresponding to the interface The XML definition is as follows

<select id="getUserByIdAndUserName"
        resultType="com.example.mybatis.entity.User">
    select
    id, username, user_email userEmail, user_city userCity, age
    from user
    where id = #{id}
    and username = #{username}
</select>

POJO

It’s too troublesome to write multiple parameters one by one. At this time, we can encapsulate the parameter list and take the encapsulated object as the value of parametertype.

The interface is defined as follows

public User getUserByIdAndUserName(User user);

Mapper corresponding to the interface The XML definition is as follows

Named parameter: Specifies the key of the map when encapsulating parameters,

<select id="getUserByIdAndUserName"
        parameterType="com.example.mybatis.entity.User"
        resultType="com.example.mybatis.entity.User">
    select
    id, username, user_email userEmail, user_city userCity, age
    from user
    where id = #{id}
    and username = #{username}
</select>

The difference from multiple parameters is that this is through#{property name}To map the specific attribute values of the parameter object.

Sometimes, for convenience, you can directly use map to transfer data, but the use of map will make the readability of the code very poor. This method is generally not recommended.

exceptional case

If the parameter type is collection (list, set) or array, mybatis will also handle it specially

If it is of collection type, the key is collection; If it is of type list, the key is list

public int addUserList(List<User> userList);

Mapper corresponding to the interface The XML definition is as follows

<insert id="addUserList"
        parameterType="com.example.mybatis.entity.User"
        useGeneratedKeys="true"
        keyProperty="id">
    insert into user
    (username, user_email, user_city, age)
    values
    <foreach item="item"  collection="collection" separator=",">
        (#{item.username}, #{item.userEmail}, #{item.userCity}, #{item.age})
    </foreach>
</insert>

If it is an array type, the key is array

The interface is defined as follows

public int addUserList(User[] userList);

Mapper corresponding to the interface The XML definition is as follows

<insert id="addUserList"
        parameterType="com.example.mybatis.entity.User"
        useGeneratedKeys="true"
        keyProperty="id">
    insert into user
    (username, user_email, user_city, age)
    values
    <foreach item="item"  collection="array" separator=",">
        (#{item.username}, #{item.userEmail}, #{item.userCity}, #{item.age})
    </foreach>
</insert>

#Difference between {} and ${}

#{}It is to set parameters into SQL statements in the form of precompiled to prevent SQL injection,${}If the extracted value is directly assembled in the SQL statement, there will be security problems.

But sometimes you just want to insert a non escaped string directly into the SQL statement. For example, like order by, you can use it this way

ORDER BY ${columnName}

Mybatis does not modify or escape strings here.

String replacement is very useful when the metadata (such as table name or column name) in SQL statement is generated dynamically. For example, if you want to delete from the table through any columnselectYou do not need to write data as follows:

@Select("select * from user where id = #{id}")
User findById(@Param("id") long id);

@Select("select * from user where name = #{name}")
User findByName(@Param("name") String name);

@Select("select * from user where email = #{email}")
User findByEmail(@Param("email") String email);

You can write only one method:

@Select("select * from user where ${column} = #{value}")
User findByColumn(@Param("column") String column, @Param("value") String value);

among${column}Will be replaced directly, and#{value}Will be used?Pretreatment. Therefore, you can achieve the above functions as follows:

User userOfId1 = userMapper.findByColumn("id", 1L);
User userOfNameKid = userMapper.findByColumn("name", "kid");
User userOfEmail = userMapper.findByColumn("email", "[email protected]");

This method is also applicable to the case of replacing table names.

#{} specify the JDBC type

You only need to specify the JDBC type on columns that may perform inserts, updates, and deletions and allow null values. This is a JDBC requirement, not a mybatis requirement.

For possible use scenarios in the future, mybatis supports the following JDBC types through the built-in JDBC type enumeration types.

BIT	FLOAT	CHAR	TIMESTAMP	OTHER	UNDEFINED
TINYINT	REAL	VARCHAR	BINARY	BLOB	NVARCHAR
SMALLINT	DOUBLE	LONGVARCHAR	VARBINARY	CLOB	NCHAR
INTEGER	NUMERIC	DATE	LONGVARBINARY	BOOLEAN	NCLOB
BIGINT	DECIMAL	TIME	NULL	CURSOR	ARRAY

In our datanullSome databases may not recognize mybatis pairsnullDefault processing for. For example, Oracle, mybatis for allnullAre mapped to native JDBCOTHERType, Oracle can’t handle it correctly, but MySQL can handle it.

This work adoptsCC agreement, reprint must indicate the author and the link to this article