之前通过自定义的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;
}
}
}