mybatis-sql实战总结

数据库查询到的结果集和实体类属性的映射

1.resultMap方式

将数据库的查询到的结果集一一手动映射到实体类

<resultMap id="AdAccEntrieMap" type="com.sinosoft.aes.model.account.AdAccEntrie"><result column = "SERI_NO"			property = "seriNo"/><result column = "ACC_NO"			property = "accNo"/><result column = "BATCH_NO"			property = "batchNo"/><result column = "TRADE_SERI"		property = "tradeSeri"/><result column = "LINE_ITEM_SERI"	property = "lineItemSeri"/><result column = "ENTRIE_NO"	    property = "entrieNo"/><result column = "SSTRING01"	    property = "sstring01"/><result column = "SSTRING02"	    property = "sstring02"/><result column = "SSTRING03"	    property = "sstring03"/><result column = "SSTRING04"	    property = "sstring04"/><result column = "SSTRING05"	    property = "sstring05"/><result column = "SSTRING06"	    property = "sstring06"/><result column = "SSTRING07"	    property = "sstring07"/><result column = "SSTRING08"	    property = "sstring08"/><result column = "SSTRING09"	    property = "sstring09"/><result column = "SSTRING10"	    property = "sstring10"/><result column = "SSTRING11"	    property = "sstring11"/><result column = "SSTRING12"	    property = "sstring12"/><result column = "SSTRING13"	    property = "sstring13"/><result column = "SSTRING14"	    property = "sstring14"/><result column = "SSTRING15"	    property = "sstring15"/><result column = "SSTRING16"	    property = "sstring16"/><result column = "SSTRING17"	    property = "sstring17"/><result column = "SSTRING18"	    property = "sstring18"/><result column = "SSTRING19"	    property = "sstring19"/><result column = "SSTRING20"	    property = "sstring20"/><result column = "MSTRING01"	    property = "mstring01"/><result column = "MSTRING02"	    property = "mstring02"/><result column = "MSTRING03"	    property = "mstring03"/><result column = "MSTRING04"	    property = "mstring04"/><result column = "MSTRING05"	    property = "mstring05"/><result column = "MSTRING06"	    property = "mstring06"/><result column = "MSTRING07"	    property = "mstring07"/><result column = "MSTRING08"	    property = "mstring08"/><result column = "MSTRING09"	    property = "mstring09"/><result column = "MSTRING10"	    property = "mstring10"/><result column = "MSTRING11"	    property = "mstring11"/><result column = "MSTRING12"	    property = "mstring12"/><result column = "MSTRING13"	    property = "mstring13"/><result column = "MSTRING14"	    property = "mstring14"/><result column = "MSTRING15"	    property = "mstring15"/><result column = "MSTRING16"	    property = "mstring16"/><result column = "MSTRING17"	    property = "mstring17"/><result column = "MSTRING18"	    property = "mstring18"/><result column = "MSTRING19"	    property = "mstring19"/><result column = "MSTRING20"	    property = "mstring20"/><result column = "LSTRING01"	    property = "lstring01"/><result column = "LSTRING02"	    property = "lstring02"/><result column = "LSTRING03"	    property = "lstring03"/><result column = "LSTRING04"	    property = "lstring04"/><result column = "LSTRING05"	    property = "lstring05"/><result column = "LSTRING06"	    property = "lstring06"/><result column = "LSTRING07"	    property = "lstring07"/><result column = "LSTRING08"	    property = "lstring08"/><result column = "LSTRING09"	    property = "lstring09"/><result column = "LSTRING10"	    property = "lstring10"/><result column = "INT01"	        property = "int01"/><result column = "INT02"	        property = "int02"/><result column = "INT03"	        property = "int03"/><result column = "INT04"	        property = "int04"/><result column = "INT05"	        property = "int05"/><result column = "DECIMAL01"	    property = "decimal01"/><result column = "DECIMAL02"	    property = "decimal02"/><result column = "DECIMAL03"	    property = "decimal03"/><result column = "DECIMAL04"	    property = "decimal04"/><result column = "DECIMAL05"	    property = "decimal05"/><result column = "DATE01"	        property = "date01" javaType="java.sql.Date" jdbcType="TIMESTAMP"/><result column = "DATE02"	        property = "date02"/><result column = "DATE03"	        property = "date03"/><result column = "DATE04"	        property = "date04"/><result column = "DATE05"	        property = "date05"/><result column = "DATE06"	        property = "date06"/><result column = "DATE07"	        property = "date07"/><result column = "DATE08"	        property = "date08"/><result column = "DATE09"	        property = "date09"/><result column = "DATE10"	        property = "date10"/></resultMap>//在查询语句中使用 resultMap

2.resultType方式

从数据库查询到的结果集会通过名字自动映射到实体类

<select id="getSubjectCodeCombobox" resultType="com.sinosoft.aes.model.engine.AesDataMapping">select BUS_VALUE value,BUS_NAME remarkfrom aes_bus_aes_mappingwhere CODE_ID = 'dtsjkmqslx'</select>

动态条件查询

动态条件查询总结
注意点：
1.可以用where 1=1 也可以用 防止出现后面没有条件的情况
2.模糊查询用concat拼接
A.SSTRING10 like concat(#{adAccEntrie.sstring10},‘%’)
3. 对于前端传来的日期参数，使用STR_TO_DATE来转为统一的格式
STR_TO_DATE( #{adAccEntrie.date01}, ‘%Y-%m-%d’)

4.对于String类型的字段，不但要判断是不是null，还要判断是不是空字符串””.
对于集合类型的字段，如List，不但要判断是不是null，还需要判断是不是空的集合，用size判断就行。
对于Date类型的字段，只需要判断是不是null就行了。

//String类型怎么判断拼接
<if test="adAccEntrie.sstring08 != null and adAccEntrie.sstring08 != ''">AND A.SSTRING08 = #{adAccEntrie.sstring08}<!--来源系统-->
</if>
//集合类型字段判断怎么拼接                   
<if test="ids != null and ids.size() > 0">AND ID IN<foreach collection="ids" item="id" open="(" close=")" separator=",">#{id}</foreach>
</if>
//Date类型字段判断怎么拼接
<if test="adAccEntrie.date01 != null and adAccEntrie.date02  != null">AND A.DATE01 BETWEEN STR_TO_DATE( #{adAccEntrie.date01}, '%Y-%m-%d') AND STR_TO_DATE( #{adAccEntrie.date02}, '%Y-%m-%d' )<!--记账日期--></if>

5.如果只查一个表 select *就足够了，但是如果有多个表select A.*就能查出指定表的所有字段了。

<!--    账户明细查询--><select id="getAccEntrieListByParam" resultMap="AdAccEntrieMap">SELECT A.*FROM Ad_Acc_Entrie A<where><if test="adAccEntrie.sstring08 != null and adAccEntrie.sstring08 != ''">AND A.SSTRING08 = #{adAccEntrie.sstring08}<!--来源系统--></if><if test="adAccEntrie.date01 != null and adAccEntrie.date02  != null">AND A.DATE01 BETWEEN STR_TO_DATE( #{adAccEntrie.date01}, '%Y-%m-%d') AND STR_TO_DATE( #{adAccEntrie.date02}, '%Y-%m-%d' )<!--记账日期--></if><if test="adAccEntrie.sstring01 != null and adAccEntrie.sstring01 != ''">AND A.SSTRING01 = #{adAccEntrie.sstring01}<!--借贷标识--></if><if test="adAccEntrie.mstring11 != null and adAccEntrie.mstring11 != ''">AND A.MSTRING11 = #{adAccEntrie.mstring11}<!--管理机构--></if><if test="adAccEntrie.sstring10 != null and adAccEntrie.sstring10 !=''">AND A.SSTRING10 like concat(#{adAccEntrie.sstring10},'%')<!--业务项目类型--></if><if test="adAccEntrie.lstring01 != null and adAccEntrie.lstring01 !=''">AND A.LSTRING01 like concat(#{adAccEntrie.lstring01},'%')<!--凭证类别--></if><if test="adAccEntrie.mstring07 != null and adAccEntrie.mstring07 != ''">AND A.MSTRING07 = #{adAccEntrie.mstring07}<!--业务号码--></if><if test="adAccEntrie.sstring17 != null and adAccEntrie.sstring17 != ''">AND A.SSTRING17 = #{adAccEntrie.sstring17}<!--业务场景编码--></if><if test="adAccEntrie.mstring02 != null and adAccEntrie.mstring02 != ''">AND A.MSTRING02 = #{adAccEntrie.mstring02}<!--科目代码--></if></where>limit #{ph.page},#{ph.rows}</select>

怎么List遍历集合进行拼接？

最终会拼接成 (元素1，元素2，元素3，元素4，元素5，…,元素n)

<if test="ids != null and ids.size() > 0">AND ID IN<foreach collection="ids" item="id" open="(" close=")" separator=",">#{id}</foreach>
</if>

一个select的sql语句可以有哪些属性

id CDATA #REQUIRED(必须的)
parameterMap CDATA #IMPLIED (隐含的) 多个参数
parameterType CDATA #IMPLIED (隐含的) 一个参数
resultMap CDATA #IMPLIED (隐含的) 数据库查询到的结果集被我们一一手动映射到实体类
resultType CDATA #IMPLIED (隐含的) 数据库查询到的结果集自动通过名字和实体类映射，匹配的才映射，不匹配的没有值