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: gyang.shines@gmail.com
* @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: gyang.shines@gmail.com
 * @version 创建时间：2017年12月14日 下午5:08:17 
 * 类说明 
 */
@Repository("userDAO")
public class UserDAO {

	@Autowired
	private UserMapper userMapper;

	public int insertItem(Map<String, Object> map) throws Exception {
		try {
			userMapper.insertItem(map);
			return 1;
		} catch (Exception e) {
			// TODO: handle exception
			e.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 exception
			e.printStackTrace();
			return 0;
		}
		
	}

	public int updateItemFuzzy(Map<String, Object> map) throws Exception{
		try {
			userMapper.updateItemFuzzy(map);
			return 1;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}
	}

	public int deleteItem(Map<String, Object> map) throws Exception{
		try {
			userMapper.deleteItem(map);
			return 1;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}
	}

	public int deleteItemFuzzy(Map<String, Object> map) throws Exception{
		try {
			userMapper.deleteItemFuzzy(map);
			return 1;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}
	}

}