Mybatis菜鸟问题记录

1. XML中SQL语句不能以;结尾，否则会报错无效字符。

2.日期类型不能与空字符串比较。

<if test="updateTime != null"> 不能加 and updateTime != ''

3.type = 类名属性id =唯一标志resultMap 子元素id(<result id="startTime"/>)作为返回数据的主键 javaType 为Date jdbcType=TIMESTAMP精确到时分秒

    <resultMap type="com.boe.idm.project.model.mybatis.UserLoginInfo" id="userLoginInfo">
        <result property="startTime" column="START_TIME" javaType="Date"  jdbcType=TIMESTAMP/>
        <result property="endTime" column="END_TIME" javaType="Date"  jdbcType=Date/>
    </resultMap>

使用时指定resultMap 属性id

<select id="userLoginPagenate" parameterType="hashmap" resultMap="userLoginInfo">

若传入null 需指定jdbcType

#{END_TIME,jdbcType=TIMESTAMP}

4.collection:一对多嵌套查询

column="TIME_KEY"  TIME_KEY为父查询的列名  DATA_TIMEKEY为子查询（select="selectDefect"）的参数名

<collection property="defect" column="{DATE_TIMEKEY=TIME_KEY,PRODUCT_SIZE=PRODUCT_SIZE}" ofType="com.boe.idm.project.model.su.CfDailyYieldCode" select="selectDefect"/>

collection嵌套select

 1 <mapper namespace="com.boe.idm.project.mapper.primary.su.CfDailyYieldMapper" >
 2 <cache/>
 3 <resultMap id="yield" type="com.boe.idm.project.model.su.CfDailyYield" >
 4     <id property="timeKey" column="TIME_KEY"/>
 5     <result property="productSize" column="PRODUCT_SIZE"/>
 6     <result property="outputQty" column="OUT_GLS"/>
 7     <result property="cfPoYield" column="CUM_PO"/>
 8     <result property="cfCumYield" column="CUM_YILED"/>
 9     <collection property="scrap" column="{TIME_KEY=TIME_KEY,PRODUCT_SIZE=PRODUCT_SIZE}" ofType="com.boe.idm.project.model.su.CfDailyYieldScrap" select="selectScrap"/>
10     <collection property="top1" column="{DATE_TIMEKEY=TIME_KEY,PRODUCT_SIZE=PRODUCT_SIZE}" ofType="com.boe.idm.project.model.su.CfDailyYieldCode" select="selectTop1"/>
11     <collection property="top2" column="{DATE_TIMEKEY=TIME_KEY,PRODUCT_SIZE=PRODUCT_SIZE}" ofType="com.boe.idm.project.model.su.CfDailyYieldCode" select="selectTop2"/>
12     <collection property="top3" column="{DATE_TIMEKEY=TIME_KEY,PRODUCT_SIZE=PRODUCT_SIZE}" ofType="com.boe.idm.project.model.su.CfDailyYieldCode" select="selectTop3"/>
13     <collection property="top4" column="{DATE_TIMEKEY=TIME_KEY,PRODUCT_SIZE=PRODUCT_SIZE}" ofType="com.boe.idm.project.model.su.CfDailyYieldCode" select="selectTop4"/>
14     <collection property="top5" column="{DATE_TIMEKEY=TIME_KEY,PRODUCT_SIZE=PRODUCT_SIZE}" ofType="com.boe.idm.project.model.su.CfDailyYieldCode" select="selectTop5"/>
15 </resultMap>
16 <select id="selectAll" resultMap="yield" resultType="com.boe.idm.project.model.su.CfDailyYield" parameterType="hashmap">
17     SELECT B.TIME_KEY,B.PRODUCT_SIZE,B.OUT_GLS,B.CUM_SCRAP,TO_CHAR(ROUND(B.CUM_SCRAP/B.OUT_GLS,4)*100,'fm9999999999990.0099')||'%' AS scrapRatio,B.CUM_PO||'%' AS CUM_PO,B.CUM_YILED||'%'AS CUM_YILED
18     FROM  CF_DAILY_YIELD B
19     where B.OUT_PNL <![CDATA[<> 0]]>
20     <if test="productSize != null and productSize !=''">
21         and PRODUCT_SIZE=#{productSize}
22     </if>
23 </select>
24 <select id="selectTop1" resultType="com.boe.idm.project.model.su.CfDailyYieldCode">
25     SELECT A.CODE_DESC AS DEFECTNAME,TO_CHAR(ROUND(A.RATIO,4)*100,'fm9999999999990.0099')||'%' AS DEFECTRATIO
26     FROM CF_DAILY_YIELD_CODE A
27     WHERE
28     A.DATE_TIMEKEY=#{DATE_TIMEKEY}
29     AND A.PRODUCT_SIZE=#{PRODUCT_SIZE}
30     AND A.NUM=1
31 </select>
32 <select id="selectTop2" resultType="com.boe.idm.project.model.su.CfDailyYieldCode">
33     SELECT A.CODE_DESC AS DEFECTNAME,TO_CHAR(ROUND(A.RATIO,4)*100,'fm9999999999990.0099')||'%' AS DEFECTRATIO
34     FROM CF_DAILY_YIELD_CODE A
35     WHERE
36     A.DATE_TIMEKEY=#{DATE_TIMEKEY}
37     AND A.PRODUCT_SIZE=#{PRODUCT_SIZE}
38     AND A.NUM=2
39 </select>
40 <select id="selectTop3" resultType="com.boe.idm.project.model.su.CfDailyYieldCode">
41     SELECT A.CODE_DESC AS DEFECTNAME,TO_CHAR(ROUND(A.RATIO,4)*100,'fm9999999999990.0099')||'%' AS DEFECTRATIO
42     FROM CF_DAILY_YIELD_CODE A
43     WHERE
44     A.DATE_TIMEKEY=#{DATE_TIMEKEY}
45     AND A.PRODUCT_SIZE=#{PRODUCT_SIZE}
46     AND A.NUM=3
47 </select>
48 <select id="selectTop4" resultType="com.boe.idm.project.model.su.CfDailyYieldCode">
49     SELECT A.CODE_DESC AS DEFECTNAME,TO_CHAR(ROUND(A.RATIO,4)*100,'fm9999999999990.0099')||'%' AS DEFECTRATIO
50     FROM CF_DAILY_YIELD_CODE A
51     WHERE
52     A.DATE_TIMEKEY=#{DATE_TIMEKEY}
53     AND A.PRODUCT_SIZE=#{PRODUCT_SIZE}
54     AND A.NUM=4
55 </select>
56 <select id="selectTop5" resultType="com.boe.idm.project.model.su.CfDailyYieldCode">
57     SELECT A.CODE_DESC AS DEFECTNAME,TO_CHAR(ROUND(A.RATIO,4)*100,'fm9999999999990.0099')||'%' AS DEFECTRATIO
58     FROM CF_DAILY_YIELD_CODE A
59     WHERE
60     A.DATE_TIMEKEY=#{DATE_TIMEKEY}
61     AND A.PRODUCT_SIZE=#{PRODUCT_SIZE}
62     AND A.NUM=5
63 </select>
64 <select id="selectScrap" resultType="com.boe.idm.project.model.su.CfDailyYieldScrap">
65     SELECT CUM_SCRAP AS QTY,TO_CHAR(ROUND(CUM_SCRAP/OUT_GLS,4)*100,'fm9999999999990.0099')||'%' AS RATIO
66     FROM CF_DAILY_YIELD
67     WHERE
68     TIME_KEY=#{TIME_KEY}
69     AND PRODUCT_SIZE=#{PRODUCT_SIZE}
70 </select>
71 </mapper>

collection select

collection不嵌套select 指定result

 1 <mapper namespace="com.boe.idm.project.mapper.primary.su.CfDailyYieldMapper" >
 2     <cache/>
 3     <resultMap id="yield" type="com.boe.idm.project.model.su.CfDailyYield" >
 4         <id property="timeKey" column="TIME_KEY"/>
 5         <result property="productSize" column="PRODUCT_SIZE"/>
 6         <result property="outputQty" column="OUT_GLS"/>
 7         <result property="cfPoYield" column="CUM_PO"/>
 8         <result property="cfCumYield" column="CUM_YILED"/>
 9         <result property="cfXRatio" column="cfXRatio"/>
10         <collection property="scrap"  ofType="com.boe.idm.project.model.su.CfDailyYieldScrap" >
11             <result property="qty" column="CUM_SCRAP"/>
12             <result property="ratio" column="SCRAP_RATIO"/>
13         </collection>
14         <collection property="top1" ofType="com.boe.idm.project.model.su.CfDailyYieldCode">
15             <result property="defectName" column="TOP1DEFECT"/>
16             <result property="defectRatio" column="TOP1RATIO"/>
17         </collection>
18         <collection property="top2" ofType="com.boe.idm.project.model.su.CfDailyYieldCode">
19             <result property="defectName" column="TOP2DEFECT"/>
20             <result property="defectRatio" column="TOP2RATIO"/>
21         </collection>
22         <collection property="top3" ofType="com.boe.idm.project.model.su.CfDailyYieldCode">
23             <result property="defectName" column="TOP3DEFECT"/>
24             <result property="defectRatio" column="TOP3RATIO"/>
25         </collection>
26         <collection property="top4" ofType="com.boe.idm.project.model.su.CfDailyYieldCode">
27             <result property="defectName" column="TOP4DEFECT"/>
28             <result property="defectRatio" column="TOP4RATIO"/>
29         </collection>
30         <collection property="top5" ofType="com.boe.idm.project.model.su.CfDailyYieldCode">
31             <result property="defectName" column="TOP5DEFECT"/>
32             <result property="defectRatio" column="TOP5RATIO"/>
33         </collection>
34     </resultMap>
35     <select id="selectAll" resultMap="yield" resultType="com.boe.idm.project.model.su.CfDailyYield" parameterType="hashmap">
36         SELECT * FROM (SELECT B.TIME_KEY,B.PRODUCT_SIZE,B.OUT_GLS,B.CUM_PO||'%' AS CUM_PO,B.CUM_X||'%' AS cfXRatio,B.CUM_YILED||'%'AS CUM_YILED,CUM_SCRAP,TO_CHAR(ROUND(CUM_SCRAP/OUT_GLS,4)*100,'fm9999999999990.0099')||'%' AS SCRAP_RATIO,
37         B.TOP1DEFECT,TO_CHAR(ROUND(B.TOP1RATIO,4)*100,'fm9999999999990.0099')||'%' AS TOP1RATIO,
38         B.TOP2DEFECT,TO_CHAR(ROUND(B.TOP2RATIO,4)*100,'fm9999999999990.0099')||'%' AS TOP2RATIO,
39         B.TOP3DEFECT,TO_CHAR(ROUND(B.TOP3RATIO,4)*100,'fm9999999999990.0099')||'%' AS TOP3RATIO,
40         B.TOP4DEFECT,TO_CHAR(ROUND(B.TOP4RATIO,4)*100,'fm9999999999990.0099')||'%' AS TOP4RATIO,
41         B.TOP5DEFECT,TO_CHAR(ROUND(B.TOP5RATIO,4)*100,'fm9999999999990.0099')||'%' AS TOP5RATIO,
42         CASE
43         WHEN B.TIME_KEY='2019' THEN 1
44         WHEN LENGTH(B.TIME_KEY)=7 THEN 2
45         WHEN SUBSTR(B.TIME_KEY,3,1)='W' THEN 3
46         ELSE 4
47         END AS SEQ
48         FROM  CF_DAILY_YIELD B
49         where B.OUT_PNL <![CDATA[<> 0]]>
50         <if test="productSize != null and productSize !=''">
51             and PRODUCT_SIZE=#{productSize}
52         </if>
53         )
54         ORDER BY SEQ,TIME_KEY
55     </select>
56 </mapper>

collection result

5.selectKey实现插入或删除（需要在类定义keyProperty属性）

    <insert id="lineAbnormal"  parameterType="com.boe.idm.project.model.su.RptCfDailyMovement" >

        <selectKey keyProperty="lineQty" resultType="int" order="BEFORE">
            select count(*) as lineQty from CF_LINE_ABNORMAL where TIMEKEY=#{timeKey} and line=#{lineId}
        </selectKey>

        <if test="lineQty > 0">
            update CF_LINE_ABNORMAL set PRODUCT=#{productId,jdbcType=VARCHAR},ABNORMAL=#{lineException,jdbcType=VARCHAR},PLAN=#{planQty,jdbcType=INTEGER},TECHNOLOGY=#{layer,jdbcType=VARCHAR}
            where TIMEKEY=#{timeKey} and line=#{lineId}
        </if>
        <if test="lineQty==0">
            INSERT INTO CF_LINE_ABNORMAL(TIMEKEY,LINE,PRODUCT,ABNORMAL,PLAN,TECHNOLOGY) VALUES(#{timeKey},#{lineId},#{productId,jdbcType=VARCHAR},#{lineException,jdbcType=VARCHAR},#{planQty,jdbcType=INTEGER},#{layer,jdbcType=VARCHAR})
        </if>

    </insert>

insert or update

6.批量提交

    @Resource
    private SqlSessionTemplate sqlSessionTemplate;
    SqlSession sqlSession =sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH,false);
    CellRepairMonitorMapper cellRepairMonitorMapper2 = sqlSession.getMapper(CellRepairMonitorMapper.class);
    public void test() throws Exception{
        cellRepairMonitorList =cellRepairMonitorMapper2.getList();
        try{
            for(CellRepairMonitor cellRepairMonitor:cellRepairMonitorList){
                cellRepairMonitorMapper2.setFlag(cellRepairMonitor);
            }
            sqlSession.commit();
            sqlSession.clearCache();
        }catch (Exception e){
            sqlSession.rollback();

        }finally {
            sqlSession.close();
        }
    }

autoCommit=false

7.mapper.xml 不同parameter type处理

//简单数据类型Integer、String等，使用_parameter表示参数
<if test='_parameter=="D"'>
//自定义对象，判断属性直接用属性名,map使用key名，判断对象用_parameter
<if test='_parameter != null'>
<if test="name != null">或者<if test="#{name} != null"

posted on 2019-03-04 19:33 malloc+ 阅读(173) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

@malloc

导航

公告

Mybatis菜鸟问题记录