1.sqlMapConfig
<sqlMapConfig>
<properties resource="db.properties"/>
<settings
cacheModelsEnabled="true"
enhancementEnabled="true"
lazyLoadingEnabled="true"
maxRequests="128"
maxSessions="32"
maxTransactions="16"
useStatementNamespaces="true"/>
<typeAlias alias="stringBooleanHandler" type="com....common.ibatis.StringBooleanTypeHandler"/>
<typeAlias alias="integerBooleanHandler" type="com....common.ibatis.IntegerBooleanTypeHandler"/>
<sqlMap resource="ibatis/sql/statistics.xml"/>
<sqlMap resource="ibatis/sql/user.xml"/>
</sqlMapConfig>
2.命名空间和别名
<sqlMap namespace="statistics">
<typeAlias alias="websiteCnt" type="cn....tables.statistics.TrackingWebsiteCnt"/>
3.
<sql id="getOrderCondition">
<isNotEmpty prepend="ORDER BY" property="sortField">
<![CDATA[ $sortField$ $orderBy$ ]]>
</isNotEmpty>
<isEmpty prepend="ORDER BY" property="sortField">
<![CDATA[ a.created_at desc ]]>
</isEmpty>
<isGreaterThan property="page" compareValue="0">
<isGreaterThan property="pageSize" compareValue="0">
<![CDATA[ limit $pos$ , $pageSize$ ]]>
</isGreaterThan>
</isGreaterThan>
</sql>
<select id="getSubCompanyCntsList" parameterClass="subCompanyCnt" resultClass="subCompanyCnt">
SELECT a.id
, a.username userName
, a.company_id companyId
, a.name name
, a.created_at createdAt
, IFNULL(c.validMetaCount, 0) validMetaCount
, IFNULL(c.pendingMetaCount, 0) pendingMetaCount
, IFNULL(d.orderCount, 0) orderCount
, IFNULL(d.pendingOrderCount, 0) pendingOrderCount
, IFNULL(d.confirmedOrderCount, 0) confirmedOrderCount
, IFNULL(d.startOrderCount, 0) startOrderCount
, IFNULL(d.stopOrderCount, 0) stopOrderCount
, IFNULL(d.closeOrderCount, 0) closeOrderCount
, IFNULL(e.matchCount, 0) matchCount
, IFNULL(f.reportCount, 0) reportCount
FROM ${sso.database}.company_subCompany b
,${sso.database}.user a
LEFT JOIN (SELECT subCompany_id
, SUM(CASE WHEN is_valid = 'valid' THEN 1 ELSE 0 END) validMetaCount
, SUM(CASE WHEN is_valid = 'pending' THEN 1 ELSE 0 END) pendingMetaCount
FROM meta
GROUP BY subCompany_id ) c ON c.subCompany_id = a.company_id
LEFT JOIN (SELECT subCompany_id
, count(id) orderCount
, SUM(CASE WHEN order_status = 'need confirm' THEN 1 ELSE 0 END) pendingOrderCount
, SUM(CASE WHEN order_status = 'confirmed' THEN 1 ELSE 0 END) confirmedOrderCount
, SUM(CASE WHEN order_status = 'in process' and start_at > 0 and stop_at = 0 THEN 1 ELSE 0 END) startOrderCount
, SUM(CASE WHEN order_status = 'in process' and start_at = 0 and stop_at > 0 THEN 1 ELSE 0 END) stopOrderCount
, SUM(CASE WHEN order_status = 'finished' THEN 1 ELSE 0 END) closeOrderCount
FROM trackingOrder
GROUP BY subCompany_id) d ON a.company_id = d.subCompany_id
LEFT JOIN (SELECT company_id
, count(*) matchCount
FROM matchedPage where verification = 'mediadna'
GROUP BY company_id) e ON a.company_id = e.company_id
LEFT JOIN (SELECT subCompany_id
, count(*) reportCount
FROM rpo_summaryCompany
WHERE is_deleted = 'false'
GROUP BY subCompany_id) f ON a.company_id = f.subCompany_id
WHERE a.is_deleted = 'false'
AND b.company_id = #companyId#
AND b.subCompany_id = a.company_id
<isNotEmpty prepend="AND" property="searchKeyword">
a.name like '%$searchKeyword$%'
</isNotEmpty>
<include refid="getOrderCondition" />
</select>
<select id="getSubCompanyCntsList" parameterClass="subCompanyCnt" resultClass="subCompanyCnt">
SELECT a.id
, a.username userName
, a.company_id companyId
, a.name name
, a.created_at createdAt
, IFNULL(c.validMetaCount, 0) validMetaCount
, IFNULL(c.pendingMetaCount, 0) pendingMetaCount
, IFNULL(d.orderCount, 0) orderCount
, IFNULL(d.pendingOrderCount, 0) pendingOrderCount
, IFNULL(d.confirmedOrderCount, 0) confirmedOrderCount
, IFNULL(d.startOrderCount, 0) startOrderCount
, IFNULL(d.stopOrderCount, 0) stopOrderCount
, IFNULL(d.closeOrderCount, 0) closeOrderCount
, IFNULL(e.matchCount, 0) matchCount
, IFNULL(f.reportCount, 0) reportCount
FROM ${sso.database}.company_subCompany b
,${sso.database}.user a
LEFT JOIN (SELECT subCompany_id
, SUM(CASE WHEN is_valid = 'valid' THEN 1 ELSE 0 END) validMetaCount
, SUM(CASE WHEN is_valid = 'pending' THEN 1 ELSE 0 END) pendingMetaCount
FROM meta
GROUP BY subCompany_id ) c ON c.subCompany_id = a.company_id
LEFT JOIN (SELECT subCompany_id
, count(id) orderCount
, SUM(CASE WHEN order_status = 'need confirm' THEN 1 ELSE 0 END) pendingOrderCount
, SUM(CASE WHEN order_status = 'confirmed' THEN 1 ELSE 0 END) confirmedOrderCount
, SUM(CASE WHEN order_status = 'in process' and start_at > 0 and stop_at = 0 THEN 1 ELSE 0 END) startOrderCount
, SUM(CASE WHEN order_status = 'in process' and start_at = 0 and stop_at > 0 THEN 1 ELSE 0 END) stopOrderCount
, SUM(CASE WHEN order_status = 'finished' THEN 1 ELSE 0 END) closeOrderCount
FROM trackingOrder
GROUP BY subCompany_id) d ON a.company_id = d.subCompany_id
LEFT JOIN (SELECT company_id
, count(*) matchCount
FROM matchedPage where verification = 'mediadna'
GROUP BY company_id) e ON a.company_id = e.company_id
LEFT JOIN (SELECT subCompany_id
, count(*) reportCount
FROM rpo_summaryCompany
WHERE is_deleted = 'false'
GROUP BY subCompany_id) f ON a.company_id = f.subCompany_id
WHERE a.is_deleted = 'false'
AND b.company_id = #companyId#
AND b.subCompany_id = a.company_id
<isNotEmpty prepend="AND" property="searchKeyword">
a.name like '%$searchKeyword$%'
</isNotEmpty>
<include refid="getOrderCondition" />
</select>
基础
<select id="getUserByKey" resultClass="user" parameterClass="int">
<![CDATA[
select a.id
, a.company_id companyId
, a.name
, a.username userName
, a.password
, a.email
, a.phone
, a.company_name companyName
, a.address1
, a.address2
, a.signature
, a.description
, ifnull(b.role_id, 0) roleId
from user a left join user_role b on a.id = b.user_id
where a.id = #value#
]]>
</select>
<sql id="getQueryCondition">
<isNotEmpty prepend="AND" property="searchKeyword">
<![CDATA[
user.name like '%$searchKeyword$%'
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="start">
<![CDATA[
user.created_at >= #start#
]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="end">
<![CDATA[
user.created_at < date_add(#end#,INTERVAL 1 day)
]]>
</isNotEmpty>
</sql>
<sql id="getOrderCondition">
<isNotEmpty prepend="order by" property="user.sortField">
<![CDATA[ $user.sortField$ ]]>
</isNotEmpty>
<isEmpty prepend="order by" property="user.sortField">
<![CDATA[ user.created_at ]]>
</isEmpty>
<isNotEmpty property="user.orderBy">
<![CDATA[ $user.orderBy$ ]]>
</isNotEmpty>
<isGreaterThan property="user.page" compareValue="0">
<isGreaterThan property="user.pageSize" compareValue="0">
<![CDATA[ limit $user.pos$ , $user.pageSize$ ]]>
</isGreaterThan>
</isGreaterThan>
</sql>
<select id="getUserByUserName" resultClass="user" parameterClass="string">
<![CDATA[
SELECT
a.id
, a.company_id companyId
, a.username userName
, a.name
, a.password
, a.user_type userType
FROM user a
WHERE a.username = #userName#
AND a.is_deleted = 'false'
AND a.company_id NOT IN (SELECT b.subCompany_id FROM company_subCompany b)
]]>
</select>
<select id="getUserByKey" resultClass="user" parameterClass="int">
<![CDATA[
select a.id
, a.company_id companyId
, a.name
, a.username userName
, a.password
, a.email
, a.phone
, a.company_name companyName
, a.address1
, a.address2
, a.signature
, a.description
, ifnull(b.role_id, 0) roleId
from user a left join user_role b on a.id = b.user_id
where a.id = #value#
]]>
</select>