ibatis sql基础

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>