Mapper查询技巧

Sql字段动态比较判断

<sql id="getUserInfoList_body">
     SELECT *　from userinfo
     <dynamic prepend="WHERE">
     　　<isNotNull prepend="AND" property="id"> a.id=#id#</isNotNull>
     　　<isNotNull prepend="AND" property="userName"> a.userName like concat('%', #userName#, '%') </isNotNull>
     　　<isNotNull prepend="AND" property="inschoolTimeEnd"> a.inschoolTime &lt; #inschoolTimeEnd# </isNotNull>
     　　<isNotNull property="roleID">
           <isEqual property="roleID" compareValue="3" prepend="AND">
            addrProvinceID in (SELECT areaID FROM co_user_area WHERE adminID = #adminID#)</isEqual>
           <isEqual property="roleID" compareValue="4" prepend="AND">
            addrCityID in (SELECT areaID FROM co_user_area WHERE adminID = #adminID#)</isEqual>
     　　</isNotNull>
     </dynamic>
     ORDER BY a.id DESC  
</sql>

sql语句返回主键id     

<insert id="">      //注意无需resultClass
…
　　<selectKey resultClass="int" keyProperty="id">
   　　SELECT @@IDENTITY AS id
　　</selectKey>
</insert>     

public Integer insert(Invoice invoice) throws DAOException {
   return (Integer)getSqlMapClientTemplate().insert("insert",invoice);
}

sql时间范围查询条件  

//一
< isNotNull prepend="AND" property="createStartDateStr"> 
　　CONVERT(varchar(100), kb.createDate , 23) &gt;= #createStartDateStr# 
</isNotNull>
<isNotNull prepend="AND" property="createEndDateStr">
　　CONVERT(varchar(100),kb.createDate,23) &lt;=#createEndDateStr# 
</isNotNull>

//二
<isNotNull prepend="AND" property="startTime">
　　kc.createDate >= #startTime# 
</isNotNull>
<isNotNull prepend="AND" property="endTime">
　　<![CDATA[kc.createDate <= #endTime#]]>
</isNotNull>  

//三
<isNotNull prepend="AND" property="startTime">
　　CONVERT(varchar(100), kb.createDate , 23) &gt;= #startTime# 
</isNotNull>
<isNotNull prepend="AND" property="endTime">
　　CONVERT(varchar(100), kb.createDate , 23) &lt;= #endTime# 
</isNotNull>

sql每月数据统计  

SELECT
    COUNT(DISTINCT kes.userID) AS examCount,
    CONVERT(nvarchar(7), kesd.createTime, 120) AS month
FROM kp_out_system kos WITH (NOLOCK)
LEFT JOIN kp_exam_score kes WITH (NOLOCK)
    ON kes.outSystemSign = kos.outSystemSign
LEFT JOIN kp_exam_score_detail kesd WITH (NOLOCK)
    ON kesd.examScoreID = kes.id AND kesd.valid = 1
    < isNotNull prepend="AND" property="startTime">  kesd.createTime  &gt;= #startTime# </isNotNull>
    <isNotNull prepend="AND" property="endTime">  kesd.createTime  &lt;= #endTime# </isNotNull> 
    WHERE kos.status = 1
    <isNotNull prepend="AND" property="outSystemSign"> kos.outSystemSign=#outSystemSign# </isNotNull>
    AND kos.outSystemSign IS NOT NULL AND kos.sysAreaID IS NOT NULL AND kesd.createTime IS NOT NULL
    GROUP BY CONVERT(nvarchar(7), kesd.createTime, 120)
    ORDER BY CONVERT(nvarchar(7), kesd.createTime, 120)

sql对sum()结果判空 

SELECT ISNULL(SUM(myTable.thesisCount),0) AS thesisCount FROM (
　　SELECT
       COUNT(kuu.id) AS thesisCount,
       ISNULL(kos.outSystemName, '无') AS outSystemName
   FROM kp_out_system kos WITH (NOLOCK)
   LEFT JOIN kp_user_upload_thesis kuu WITH (NOLOCK) ON kuu.outSystemSign = kos.outSystemSign AND kuu.valid = 1
   WHERE kos.status = 1
   　　   <isNotNull prepend="AND" property="outSystemSign"> kuu.outSystemSign = #outSystemSign# </isNotNull>
         AND kos.outSystemSign IS NOT NULL AND kos.sysAreaID IS NOT NULL
   GROUP BY ISNULL(kos.outSystemName, '无')
) AS myTable

验证某个属性是否已存在（修改信息时用）

select COUNT(*) from ... where id != #id# and ... = #...#

sql数组传参（in）

//Action代码
if (null != studyID && 2 == studyID.intValue()) {
    Integer[] studyIDs = new Integer[] { 2,3, 4 };
    userInfo.setStudyIDs(studyIDs);
} else {
    userInfo.setStudyID(studyID);
}

//Xml文件
<isNotNull prepend="AND" property="studyID">
    a.studyID=#studyID#
</isNotNull>
<isNotNull prepend="AND" property="studyIDs">
    a.studyID in
    <iterate property="studyIDs" open="(" close=")" conjunction=",">
        #studyIDs[]#
    </iterate>
</isNotNull>

Int类型拼接需要使用cast()  

SELECT (SELECT DISTINCT
           CAST(studyID AS varchar) + ','
       FROM kp_study_type
       WHERE outSystemSign = 'drivingcoach' AND valid = 1
       FOR xml PATH (''))
       AS studyIDs,
       ISNULL(
       SUM(CASE
           WHEN b.isPass = 1 THEN 1 ELSE 0
       END), 0) AS isPassCount,
       ISNULL(
       SUM(CASE
           WHEN b.isOpen = 1 OR b.isOpen = 3 THEN 1 ELSE 0
       END), 0) AS isOpenCount,
       ISNULL(
       SUM(CASE
           WHEN b.isOpen = 1 OR b.isOpen = 3 THEN 0 ELSE 1
       END), 0) AS isNotOpenCount
FROM kp_study_type a WITH (NOLOCK)
INNER JOIN kp_select_studyType b WITH (NOLOCK)
    ON a.studyID = b.studyID AND b.valid = 1
INNER JOIN kp_user_info kui WITH (NOLOCK)
    ON kui.ID = b.userID AND kui.valid = 1
LEFT JOIN (SELECT
    k.studyID,
    k.userID,
    SUM(k.studyTimes) studyTimes,
    SUM(CASE
       WHEN k.studyTimes >= k.studyTime * 60 THEN 1 ELSE 0
    END) coursefinishCount,
    COUNT(k.courseID) courseCount
FROM (SELECT
    r.studyID,
    t.userID,
    SUM(t.studyTimes) AS studyTimes,
    r.courseID,
    r.studyTime
FROM kp_studyCourse_relation r WITH (NOLOCK)
LEFT JOIN kp_course c WITH (NOLOCK)
    ON r.courseID = c.id
LEFT JOIN kp_study_course_time t WITH (NOLOCK)
    ON c.courseNumber = t.courseNumber AND t.studyID = r.studyID
WHERE r.valid = 1 AND c.valid = 1 AND t.id IS NOT NULL
GROUP BY   r.studyID,
           t.userID,
           r.courseID,
           r.studyTime) k
GROUP BY   k.studyID,
           k.userID) e
    ON a.studyID = e.studyID AND b.userID = e.userID
WHERE a.valid = 1 AND a.studyID IN (SELECT DISTINCT
    studyID
FROM kp_select_studyType
WHERE outerSystemSign = 'drivingcoach' AND valid = 1 AND isOpen = 1)

sql截取字符串 

SELECT DISTINCT
    ISNULL(SUBSTRING(areaID, 1, 6), 0) AS areaID
FROM kp_user_info
或
SELECT
TOP 10
    kui.username 
FROM kp_user_info kui WITH (NOLOCK)
LEFT JOIN dic_area dc WITH (NOLOCK)
    ON RIGHT(kui.areaID, 6) = dc.areaID

地区搜索，传参设置

if(null != provinceID && !"000000".equals(provinceID)){
    productOrder.setAreaID(provinceID);                    
}
if(null!=cityID && cityID != ""){
    //数据库存储为110000:110101，依据存储方式设置
    productOrder.setAreaID(provinceID+":"+cityID);
}

<dynamic prepend="">
    <isNotNull prepend="AND" property="areaID">
 　 　　kui.areaID like #areaID# + '%'
　　 </isNotNull>
</dynamic>

以...开头

*
FROM computer
WHERE sex = '男' AND LEFT(name, 1) IN ('李', '陈')
或
SELECT
    *
FROM computer
WHERE sex = '男'
(and name like '李__'or name like '陈__')

跨库复制表数据

//-1-
SET IDENTITY_INSERT cdel_jxjy_flat20171106..op_ip_login_control ON;
//-2-
INSERT INTO cdel_jxjy_flat20171106..op_ip_login_control (id, ip, notes, valid, creator, createTime)
SELECT * FROM cdel_jxjy_flat0725..op_ip_login_control