一:查询和插入
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.bw30.wechat.menu.mapper.TrafficRedInfoMapper" >
<resultMap id="BaseResultMap" type="com.bw30.wechat.menu.model.Bw30TrafficRedInfo" >
<id column="id" property="id" jdbcType="VARCHAR" />
<result column="trafficred_huodong_id" property="trafficred_huodong_id" jdbcType="INTEGER" />
<result column="trafficred_creater" property="trafficred_creater" jdbcType="VARCHAR" />
<result column="creater_nickname" property="creater_nickname" jdbcType="VARCHAR" />
<result column="trafficred_order_id" property="trafficred_order_id" jdbcType="INTEGER" />
<result column="trafficred_num" property="trafficred_num" jdbcType="INTEGER" />
<result column="trafficred_sy_num" property="trafficred_sy_num" jdbcType="INTEGER" />
<result column="trafficred_size" property="trafficred_size" jdbcType="INTEGER" />
<result column="trafficred_sy_size" property="trafficred_sy_size" jdbcType="INTEGER" />
<result column="trafficred_expiry_date" property="trafficred_expiry_date" jdbcType="DATE" />
<result column="createtime" property="createtime" jdbcType="TIMESTAMP" />
</resultMap>
<sql id="Base_Column_List" >
id,
trafficred_huodong_id,
trafficred_creater,
creater_nickname,
trafficred_order_id,
trafficred_num,
trafficred_sy_num,
trafficred_size,
trafficred_sy_size
trafficred_expiry_date,
createtime
</sql>
<select id="findById" resultMap="BaseResultMap" parameterType="java.lang.String" >
select
<include refid="Base_Column_List" />
from wechat_trafficredinfo
where id = #{id,jdbcType=VARCHAR}
</select> <insert id="insert" parameterType="com.bw30.wechat.menu.model.Bw30TrafficRedInfo" >
insert into wechat_trafficredinfo (id,
trafficred_huodong_id,
trafficred_creater,
creater_nickname,
trafficred_order_id,
trafficred_num,
trafficred_sy_num,
trafficred_size,
trafficred_sy_size,
trafficred_expiry_date)
values (
#{id,jdbcType=VARCHAR},
#{trafficred_huodong_id,jdbcType=INTEGER},
#{trafficred_creater,jdbcType=VARCHAR},
#{creater_nickname,jdbcType=VARCHAR},
#{trafficred_order_id,jdbcType=INTEGER},
#{trafficred_num,jdbcType=INTEGER},
#{trafficred_sy_num,jdbcType=INTEGER},
#{trafficred_size,jdbcType=INTEGER},
#{trafficred_sy_size,jdbcType=INTEGER},
#{trafficred_expiry_date,jdbcType=DATE}
)
</insert> <select id="findByActivityIdAndOpenid" resultType="BaseResultMap" >
select <include refid="Base_Column_List" />
from wechat_trafficredinfo
where trafficred_huodong_id = #{trafficred_huodong_id,jdbcType=INTEGER} and trafficred_creater=#{trafficred_creater,jdbcType=VARCHAR}
</select> </mapper>
二:批量插入和更新
<insert id="insertPlanOf13thData" parameterType="java.util.List">
insert into
edi_data(tjYear,
recStatus,
cityId,
cityName,
zoneId,
zoneName,
planOf13th,
datatype,
tmInsert
)
values
<foreach collection="list" item="obj" index="index" separator=",">
(#{obj.tjYear},10,#{obj.cityId},#{obj.cityName},#{obj.zoneId},#{obj.zoneName},#{obj.planOf13th},#{obj.datatype},sysdate())
</foreach>
</insert>
<update id="updateFinanceSheet2Data" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update edi_data
<set>
expOfLastYear=#{item.expOfLastYear},
expOfThisYear=#{item.expOfThisYear}
</set>
where tjYear = #{item.tjYear} and recStatus=10 and
cityId=#{item.cityId} and zoneId=#{item.zoneId} and datatype=#{item.datatype}
</foreach>
</update>
三:插入后返回自增长的主键值
<insert id="insertAndGetId" useGeneratedKeys="true" keyProperty="userId" parameterType="com.ws.mapper.User">
insert into user(userName,password,comment)
values(#{userName},#{password},#{comment})
</insert>
说明:
useGeneratedKeys="true" 表示给主键设置自增长
keyProperty="userId" 表示将自增长后的Id赋值给实体类中的userId字段。
parameterType="com.chenzhou.mybatis.User" 这个属性指向传递的参数实体类