一个mybatis配置文件的例子

关于Mybatis sql语句的例子，记下来方便下次使用

<?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.boya.cust.bcu.mappercore.BCuCustInfoCoreMapper">
<sql id="column">
t.ID id,
t.CUST_NO custNo,
t.CUST_TYPE custType,
t.WORK_JOB workJob,
t.CUST_NAME custName,
t.SEX sex,
t.ETHNIC ethnic,
t.PHONE_NO phoneNo,
t.LAST_APPLY_DATE lastApplyDate,
t.MARRIAGE marriage,
t.CERT_NO certNo,
t.CERT_ORG certOrg,
t.CERT_VALID_DATE certValidDate,
t.REG_PROV regProv,
t.REG_CITY regCity,
t.REG_AREA regArea,
t.REG_ADDR regAddr,
t.REMARK remark,
t.IS_TEL_MARKE isTelMarke,
t.TEL_PHONE_NO telPhoneNo,
t.INST_DATE instDate,
t.INST_USER_NO instUserNo,
t.UPDT_DATE updtDate
</sql>
<select id="get" parameterType="java.util.Map" resultType="com.boya.app.cust.bcu.model.BCuCustInfo">
SELECT
<include refid="column"/>
FROM B_CU_CUST_INFO t
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="id != null and id != ''">
AND t.ID = #{id}
</if>
<if test="custNo != null and custNo != ''">
AND t.CUST_NO = #{custNo}
</if>
<if test="custType != null and custType != ''">
AND t.CUST_TYPE = #{custType}
</if>
<if test="workJob != null and workJob != ''">
AND t.WORK_JOB = #{workJob}
</if>
<if test="custName != null and custName != ''">
AND t.CUST_NAME = #{custName}
</if>
<if test="sex != null and sex != ''">
AND t.SEX = #{sex}
</if>
<if test="ethnic != null and ethnic != ''">
AND t.ETHNIC = #{ethnic}
</if>
<if test="phoneNo != null and phoneNo != ''">
AND t.PHONE_NO = #{phoneNo}
</if>
<if test="lastApplyDate != null and lastApplyDate != ''">
AND t.LAST_APPLY_DATE = #{lastApplyDate}
</if>
<if test="marriage != null and marriage != ''">
AND t.MARRIAGE = #{marriage}
</if>
<if test="certNo != null and certNo != ''">
AND t.CERT_NO = #{certNo}
</if>
<if test="certOrg != null and certOrg != ''">
AND t.CERT_ORG = #{certOrg}
</if>
<if test="certValidDate != null and certValidDate != ''">
AND t.CERT_VALID_DATE = #{certValidDate}
</if>
<if test="regProv != null and regProv != ''">
AND t.REG_PROV = #{regProv}
</if>
<if test="regCity != null and regCity != ''">
AND t.REG_CITY = #{regCity}
</if>
<if test="regArea != null and regArea != ''">
AND t.REG_AREA = #{regArea}
</if>
<if test="regAddr != null and regAddr != ''">
AND t.REG_ADDR = #{regAddr}
</if>
<if test="remark != null and remark != ''">
AND t.REMARK = #{remark}
</if>
<if test="isTelMarke != null and isTelMarke != ''">
AND t.IS_TEL_MARKE = #{isTelMarke}
</if>
<if test="telPhoneNo != null and telPhoneNo != ''">
AND t.TEL_PHONE_NO = #{telPhoneNo}
</if>
<if test="instDate != null and instDate != ''">
AND t.INST_DATE = #{instDate}
</if>
<if test="instUserNo != null and instUserNo != ''">
AND t.INST_USER_NO = #{instUserNo}
</if>
<if test="updtDate != null and updtDate != ''">
AND t.UPDT_DATE = #{updtDate}
</if>
</trim>
</select>
<select id="getList" parameterType="java.util.Map" resultType="com.boya.app.cust.bcu.model.BCuCustInfo">
SELECT
<include refid="column"/>
FROM B_CU_CUST_INFO t
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="id != null and id != ''">
AND t.ID = #{id}
</if>
<if test="custNo != null and custNo != ''">
AND t.CUST_NO = #{custNo}
</if>
<if test="custType != null and custType != ''">
AND t.CUST_TYPE = #{custType}
</if>
<if test="workJob != null and workJob != ''">
AND t.WORK_JOB = #{workJob}
</if>
<if test="custName != null and custName != ''">
AND t.CUST_NAME = #{custName}
</if>
<if test="sex != null and sex != ''">
AND t.SEX = #{sex}
</if>
<if test="ethnic != null and ethnic != ''">
AND t.ETHNIC = #{ethnic}
</if>
<if test="phoneNo != null and phoneNo != ''">
AND t.PHONE_NO = #{phoneNo}
</if>
<if test="lastApplyDate != null and lastApplyDate != ''">
AND t.LAST_APPLY_DATE = #{lastApplyDate}
</if>
<if test="marriage != null and marriage != ''">
AND t.MARRIAGE = #{marriage}
</if>
<if test="certNo != null and certNo != ''">
AND t.CERT_NO = #{certNo}
</if>
<if test="certOrg != null and certOrg != ''">
AND t.CERT_ORG = #{certOrg}
</if>
<if test="certValidDate != null and certValidDate != ''">
AND t.CERT_VALID_DATE = #{certValidDate}
</if>
<if test="regProv != null and regProv != ''">
AND t.REG_PROV = #{regProv}
</if>
<if test="regCity != null and regCity != ''">
AND t.REG_CITY = #{regCity}
</if>
<if test="regArea != null and regArea != ''">
AND t.REG_AREA = #{regArea}
</if>
<if test="regAddr != null and regAddr != ''">
AND t.REG_ADDR = #{regAddr}
</if>
<if test="remark != null and remark != ''">
AND t.REMARK = #{remark}
</if>
<if test="isTelMarke != null and isTelMarke != ''">
AND t.IS_TEL_MARKE = #{isTelMarke}
</if>
<if test="telPhoneNo != null and telPhoneNo != ''">
AND t.TEL_PHONE_NO = #{telPhoneNo}
</if>
<if test="instDate != null and instDate != ''">
AND t.INST_DATE = #{instDate}
</if>
<if test="instUserNo != null and instUserNo != ''">
AND t.INST_USER_NO = #{instUserNo}
</if>
<if test="updtDate != null and updtDate != ''">
AND t.UPDT_DATE = #{updtDate}
</if>
</trim>
</select>
<insert id="insert" parameterType="com.boya.app.cust.bcu.model.BCuCustInfo">
insert into B_CU_CUST_INFO
(
ID,
CUST_NO,
CUST_TYPE,
WORK_JOB,
CUST_NAME,
SEX,
ETHNIC,
PHONE_NO,
LAST_APPLY_DATE,
MARRIAGE,
CERT_NO,
CERT_ORG,
CERT_VALID_DATE,
REG_PROV,
REG_CITY,
REG_AREA,
REG_ADDR,
REMARK,
IS_TEL_MARKE,
TEL_PHONE_NO,
INST_DATE,
INST_USER_NO,
UPDT_DATE
)
values
(
#{id},
#{custNo},
#{custType},
#{workJob},
#{custName},
#{sex},
#{ethnic},
#{phoneNo},
#{lastApplyDate},
#{marriage},
#{certNo},
#{certOrg},
#{certValidDate},
#{regProv},
#{regCity},
#{regArea},
#{regAddr},
#{remark},
#{isTelMarke},
#{telPhoneNo},
#{instDate},
#{instUserNo},
#{updtDate}
)
</insert>
<update id="updateByPrimaryKey" parameterType="java.util.Map">
UPDATE B_CU_CUST_INFO
<trim prefix="SET" suffixOverrides=",">
<if test="custType != null">
CUST_TYPE = #{custType},
</if>
<if test="workJob != null">
WORK_JOB = #{workJob},
</if>
<if test="custName != null">
CUST_NAME = #{custName},
</if>
<if test="sex != null">
SEX = #{sex},
</if>
<if test="ethnic != null">
ETHNIC = #{ethnic},
</if>
<if test="phoneNo != null">
PHONE_NO = #{phoneNo},
</if>
<if test="lastApplyDate != null">
LAST_APPLY_DATE = #{lastApplyDate},
</if>
<if test="marriage != null">
MARRIAGE = #{marriage},
</if>
<if test="certNo != null">
CERT_NO = #{certNo},
</if>
<if test="certOrg != null">
CERT_ORG = #{certOrg},
</if>
<if test="certValidDate != null">
CERT_VALID_DATE = #{certValidDate},
</if>
<if test="regProv != null">
REG_PROV = #{regProv},
</if>
<if test="regCity != null">
REG_CITY = #{regCity},
</if>
<if test="regArea != null">
REG_AREA = #{regArea},
</if>
<if test="regAddr != null">
REG_ADDR = #{regAddr},
</if>
<if test="remark != null">
REMARK = #{remark},
</if>
<if test="isTelMarke != null">
IS_TEL_MARKE = #{isTelMarke},
</if>
<if test="telPhoneNo != null">
TEL_PHONE_NO = #{telPhoneNo},
</if>
<if test="instDate != null">
INST_DATE = #{instDate},
</if>
<if test="instUserNo != null">
INST_USER_NO = #{instUserNo},
</if>
<if test="updtDate != null">
UPDT_DATE = #{updtDate},
</if>
</trim>
WHERE CUST_NO=#{custNo}
</update>
<delete id="deleteByPrimaryKey" parameterType="java.lang.String">
DELETE FROM B_CU_CUST_INFO WHERE CUST_NO=#{custNo}
</delete>
<delete id="deleteList" parameterType="java.util.List">
DELETE FROM B_CU_CUST_INFO WHERE CUST_NO IN
<foreach collection="array" item="custNo"   open="(" separator="," close=")">
#{custNo}
</foreach>
</delete>
<delete id="deleteByCertNo" parameterType="java.lang.String">
DELETE FROM B_CU_CUST_INFO WHERE CERT_NO=#{certNo}
</delete>
<select id="getByPrimaryKey" parameterType="java.lang.String" resultType="com.boya.app.cust.bcu.model.BCuCustInfo">
SELECT
<include refid="column"/>
FROM B_CU_CUST_INFO t
WHERE CUST_NO=#{custNo}
</select>
</mapper>