MyBatis动态语句及利用Map类来传递参数

之前通过自定义的bo、po、vo类以及写多个相应的resultmap来接受数据库的数据。在程序前后端接口有改动时，往往会因为这些bo、po、vo类不满足要求，要牵一发而动全身。现在，通过动态的MyBatis以及把参数写成map类型，并用map来接受数据库的数据，可以极大地简化程序，增强代码的复用性。

附代码如下:

mapper层的UserMapper.xml，及UserMapper.java

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.terabits.mapper.UserMapper"><!-- sql片段对应表名,id属性值任意 --><sql id="tb"><!-- 去掉最后一个, --><trim suffixOverrides=","><if test="tablename == 'test1' ">${tablename}</if><if test="tablename == 'test2'">${tablename}</if><if test="tablename == 'test3'">${tablename}</if><if test="tablename == 'test4'">${tablename}</if><if test="tablename == 'test5'">${tablename}</if></trim></sql><!-- sql片段对应选取准则,id属性值任意 --><sql id="criteria"><where><if test=" ID!=-1 ">and ID=#{ID}</if><if test="Name !=null and Name !='' ">and Name = #{Name}</if><if test="Tel !=null and Tel !='' ">and Tel = #{Tel}</if><if test="Balance!=-1">and Balance = #{Balance}</if><if test="timestamp !=null and timestamp !='' ">and timestamp = #{timestamp}</if></where></sql><!-- sql片段对应选取准则,id属性值任意 --><sql id="fuzzyCriteria"><where><if test=" ID!=-1 ">and ID like CONCAT('%',#{ID},'%') </if><if test="Name !=null and Name !='' ">and Name like CONCAT('%',#{Name},'%') </if><if test="Tel !=null and Tel !='' ">and Tel like CONCAT('%',#{Tel},'%') </if><if test="Balance!=-1">and Balance like CONCAT('%',#{Balance},'%')</if><if test="timestamp !=null and timestamp !='' ">and timestamp = like CONCAT('%',#{timestamp},'%')</if></where></sql><!-- sql片段对应字段名,id属性值任意 --><sql id="key"><!-- 去掉最后一个, --><trim suffixOverrides=","><if test=" ID!=-1 ">ID,</if><if test="Name !=null and Name !='' ">Name,</if><if test="Tel !=null and Tel !='' ">Tel,</if><if test="Balance!=-1">Balance,</if><if test="timestamp !=null and timestamp !='' ">timestamp,</if></trim></sql><!-- sql片段对应?,id属性值任意 --><sql id="value"><!-- 去掉最后一个, --><trim suffixOverrides=","><if test=" ID!=-1 ">#{ID},</if><if test="Name !=null and Name !='' ">#{Name},</if><if test="Tel !=null and Tel !='' ">#{Tel},</if><if test="Balance!=-1">#{Balance},</if><if test="timestamp !=null and timestamp !='' ">#{timestamp},</if></trim></sql><!-- sql片段对应选取准则,id属性值任意 --><sql id="change"><if test=" IDchange!=-1 ">ID=#{IDchange},</if><if test="Namechange !=null and Namechange !='' ">Name = #{Namechange},</if><if test="Telchange !=null and Telchange !='' ">Tel = #{Telchange},</if><if test="Balancechange!=-1">Balance = #{Balancechange},</if><if test="timestampchange !=null and timestampchange !='' ">timestamp = #{timestampchange},</if></sql><insert id="insertItem" parameterType="map" useGeneratedKeys="true">insert into <include refid="tb"/>(<include refid="key" />)values(<include refid="value" />)</insert><select id="selectItem" parameterType="map" resultType="map">select *from<include refid="tb"/><include refid="criteria"/>ORDER BY `id` DESC LIMIT #{offset}, #{limit}</select><select id="selectItemFuzzy" parameterType="map" resultType="map">select *from <include refid="tb"/><include refid="fuzzyCriteria"/>ORDER BY `id` DESC LIMIT #{offset}, #{limit}</select><update id="updateItem" parameterType="map">update <include refid="tb"/><set><include refid="change"/></set><include refid="criteria"/></update><update id="updateItemFuzzy" parameterType="map">update <include refid="tb"/><set><include refid="change"/></set><include refid="fuzzyCriteria"/></update><delete id="deleteItem">delete from<include refid="tb"/><include refid="criteria"/></delete><delete id="deleteItemFuzzy">delete from<include refid="tb"/><include refid="fuzzyCriteria"/></delete></mapper>

UserMapper.java

package com.terabits.mapper;/** * @author 作者Vladimir E-mail: [e-mail]* @version 创建时间：2017年12月14日 下午5:09:18 * 类说明 */import java.util.List;import java.util.Map;public interface UserMapper {public int insertItem(Map<String, Object> map) throws Exception;public List<Map<String, Object>> selectItem(Map<String, Object> map) throws Exception;public List<Map<String, Object>> selectItemFuzzy(Map<String, Object> map) throws Exception;public int updateItem(Map<String, Object> map) throws Exception;public int updateItemFuzzy(Map<String, Object> map) throws Exception;public int deleteItem(Map<String, Object> map) throws Exception;public int deleteItemFuzzy(Map<String, Object> map) throws Exception;}

DAO层：

package com.terabits.dao;import java.util.List;import java.util.Map;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Repository;import com.terabits.mapper.UserMapper;/**  * @author 作者Vladimir E-mail: [e-mail] * @version 创建时间：2017年12月14日 下午5:08:17  * 类说明  */@Repository("userDAO")public class UserDAO {@Autowiredprivate UserMapper userMapper;public int insertItem(Map<String, Object> map) throws Exception {try {userMapper.insertItem(map);return 1;} catch (Exception e) {// TODO: handle exceptione.printStackTrace();return 0;}}public List<Map<String, Object>> selectItem(Map<String, Object> map) throws Exception{return userMapper.selectItem(map);}public List<Map<String, Object>> selectItemFuzzy(Map<String, Object> map) throws Exception{return userMapper.selectItemFuzzy(map);}public int updateItem(Map<String, Object> map) throws Exception{try {userMapper.updateItem(map);return 1;} catch (Exception e) {// TODO: handle exceptione.printStackTrace();return 0;}}public int updateItemFuzzy(Map<String, Object> map) throws Exception{try {userMapper.updateItemFuzzy(map);return 1;} catch (Exception e) {// TODO: handle exceptione.printStackTrace();return 0;}}public int deleteItem(Map<String, Object> map) throws Exception{try {userMapper.deleteItem(map);return 1;} catch (Exception e) {// TODO: handle exceptione.printStackTrace();return 0;}}public int deleteItemFuzzy(Map<String, Object> map) throws Exception{try {userMapper.deleteItemFuzzy(map);return 1;} catch (Exception e) {// TODO: handle exceptione.printStackTrace();return 0;}}}