使用typealias元素自定义对象
<?xml version="1.0" encoding="utf-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="project">
<typeAlias alias="Project" type="com.voiinnov.drbl.project.bean.ProjectBean"/>
<!-- 查询所有项目-->
<select id="pageQuery" parameterClass="map" resultClass="java.util.HashMap">
<![CDATA[
SELECT T1.ID,
T1.PRJTEST_CODE,
T1.PRJTEST_NAME,
T1.PRJTEST_STATUS,
T2.CUSTEST_NAME PRJOECTEST_CUSTNAME,
T3. NAME PRJTEST_PLATE_NAME,
T4.FLAG PRJTEST_MODEL1_NAME,
T5.PRODUCTEST_NAME PRJTEST_PROPERTY_NAME,
T6.NAME PRJTEST_PM_NAME,
T7.NAME CREATOR_NAME,
TO_CHAR(T1.CREATE_TIME, 'yyyy-MM-dd') CREATE_DATE
FROM TEST_PRJTEST_PROJECT T1
LEFT JOIN TEST_CUSTEST_CUSTOMER T2 ON T1.CUSTEST_ID = T2.CUSTEST_ID
LEFT JOIN TEST_SYS_LINKAGECONFIG T3 ON T3.ID = T1.PRJTEST_PLATE1
LEFT JOIN TEST_DATA_DICTIONARY T4 ON (
T4. CODE = T1.PRJTEST_TYPE
AND T4.TYPE = #PRJTEST_TYPE#
)
LEFT JOIN TEST_PRODUCTEST_PROCONFIG T5 ON (
T5.ID = T1.FACTORING_TYPE
)
LEFT JOIN TEST_USER_USER T6 ON T6.ID = T1.PRJTEST_PM
LEFT JOIN TEST_USER_USER T7 ON T7.ID = T1.CREATE_ID
WHERE T1.STATUS > -2
]]>
<isNotEmpty prepend="" property="searchContent">
<![CDATA[
AND (T1.PRJTEST_CODE LIKE '%$searchContent$%'
OR T1.PRJTEST_NAME LIKE '%$searchContent$%'
OR T2.CUSTEST_NAME LIKE '%$searchContent$%'
OR T7.NAME LIKE '%$searchContent$%'
)
]]>
</isNotEmpty>
<isEmpty property="orderType">
<![CDATA[ORDER BY T1.CREATE_TIME DESC ]]>
</isEmpty>
<isNotEmpty property="orderType">
<![CDATA[ORDER BY $orderType$]]>
</isNotEmpty>
</select>
<!-- 查询所有客户档案资料配置数量 -->
<select id="pageQuery_count" parameterClass="java.util.HashMap" resultClass="java.lang.Integer">
<![CDATA[
SELECT COUNT(T1.ID)
FROM TEST_PRJTEST_PROJECT T1
LEFT JOIN TEST_CUSTEST_CUSTOMER T2 ON T1.CUSTEST_ID = T2.CUSTEST_ID
LEFT JOIN TEST_SYS_LINKAGECONFIG T3 ON T3.ID = T1.PRJTEST_PLATE1
LEFT JOIN TEST_DATA_DICTIONARY T4 ON (
T4. CODE = T1.PRJTEST_TYPE
AND T4.TYPE = #PRJTEST_TYPE#
)
LEFT JOIN TEST_DATA_DICTIONARY T5 ON (
T5. CODE = T1.FACTORING_TYPE
AND T5.TYPE = #FACTORING_TYPE#
)
LEFT JOIN TEST_USER_USER T6 ON T6.ID = T1.PRJTEST_PM
LEFT JOIN TEST_USER_USER T7 ON T7.ID = T1.CREATE_ID
WHERE T1.STATUS > -2
]]>
<isNotEmpty prepend="" property="searchContent">
<![CDATA[
AND (T1.PRJTEST_CODE LIKE '%$searchContent$%'
OR T1.PRJTEST_NAME LIKE '%$searchContent$%'
OR T2.CUSTEST_NAME LIKE '%$searchContent$%'
OR T7.NAME LIKE '%$searchContent$%'
)
]]>
</isNotEmpty>
</select>
<!-- 查看页面查询项目全信息 -->
<select id="queryProjectFullInfo" parameterClass="map" resultClass="Project">
SELECT T1.ID,
T1.PRJTEST_CODE,
T1.PRJTEST_NAME,
T1.PRJTEST_PM,
T3.NAME PRJTEST_PM_NAME,
T1.PM_DEPT,
T4.DEPTEST_NAME PM_DEPTEST_NAME,
T1.PRJTEST_PLATE1,
T5.NAME PRJTEST_PLATE1_NAME,
T1.PRJTEST_PLATE2,
T6.NAME PRJTEST_PLATE2_NAME,
fn_rebuild_UserName(T1.PRJTEST_AM) PRJTEST_AM_NAME,
T1.FACTORING_TYPE,
T7.PRODUCTEST_NAME FACTORING_TYPE_NAME,
T1.PRJTEST_TYPE,
T8.FLAG PRJTEST_TYPE_NAME,
T1.CUSTEST_CODE,
T1.CUSTEST_ID,
T9.CUSTEST_NAME CUSTEST_NAME,
T1.CO_AGENCY,
T1.CREDITEST_WAY,
T1.PRODUCTEST_INFO,
T1.TRADE_CDT,
T1.PRJTEST_STATUS,
T1.AUDITPASS_TIME,
T1.RECE_WAY,
T1.VERSION
FROM TEST_PRJTEST_PROJECT T1
LEFT JOIN TEST_USER_USER T3 ON T3.ID = T1.PRJTEST_PM
LEFT JOIN TEST_DEPTEST_DEPARTMENT T4 ON T4.ID = T1.PM_DEPT
LEFT JOIN TEST_SYS_LINKAGECONFIG T5 ON T5.ID = T1.PRJTEST_PLATE1
LEFT JOIN TEST_SYS_LINKAGECONFIG T6 ON T6.ID = T1.PRJTEST_PLATE2
LEFT JOIN TEST_PRODUCTEST_PROCONFIG T7 ON T7.ID = T1.FACTORING_TYPE
LEFT JOIN TEST_DATA_DICTIONARY T8 ON T8.CODE = T1.PRJTEST_TYPE AND T8.TYPE=#DTYPE1#
LEFT JOIN TEST_CUSTEST_CUSTOMER T9 ON T9.CUSTEST_ID = T1.CUSTEST_ID
WHERE T1.ID = #ID#
</select>
<insert id="insert" parameterClass="Project">
<selectKey resultClass="java.lang.Integer" keyProperty="ID">
<![CDATA[SELECT SEQ_PRJTEST_PROJECT.NEXTVAL AS ID FROM DUAL ]]>
</selectKey>
<![CDATA[
INSERT INTO TEST_PRJTEST_PROJECT
( ID,
PRJTEST_CODE,
PRJTEST_NAME,
PRJTEST_PM,
PM_DEPT,
PRJTEST_PLATE1,
PRJTEST_PLATE2,
PRJTEST_AM,
FACTORING_TYPE,
PRJTEST_TYPE,
CUSTEST_CODE,
CUSTEST_ID,
CO_AGENCY,
CREDITEST_WAY,
PRODUCTEST_INFO,
TRADE_CDT,
PRJTEST_STATUS,
AUDITPASS_TIME,
RECE_WAY,
VERSION,
CREATE_TIME,
CREATE_ID,
MODIFY_TIME,
MODIFY_ID,
STATUS
)
VALUES (
#ID#,
#PRJTEST_CODE#,
#PRJTEST_NAME#,
#PRJTEST_PM#,
#PM_DEPT#,
#PRJTEST_PLATE1#,
#PRJTEST_PLATE2#,
#PRJTEST_AM#,
#FACTORING_TYPE#,
#PRJTEST_TYPE#,
#CUSTEST_CODE#,
#CUSTEST_ID#,
#CO_AGENCY#,
#CREDITEST_WAY#,
#PRODUCTEST_INFO#,
#TRADE_CDT#,
#PRJTEST_STATUS#,
#AUDITPASS_TIME#,
#RECE_WAY#,
#VERSION#,
SYSDATE,
#CREATE_ID#,
SYSDATE,
#MODIFY_ID#,
0
)
]]>
</insert>
<update id="update" parameterClass="Project">
UPDATE TEST_PRJTEST_PROJECT
SET
<isNotEmpty prepend=" " property="PRJTEST_NAME">PRJTEST_NAME=#PRJTEST_NAME#,</isNotEmpty>
<isNotEmpty prepend=" " property="PRJTEST_PM">PRJTEST_PM=#PRJTEST_PM#,</isNotEmpty>
<isNotEmpty prepend=" " property="PM_DEPT">PM_DEPT=#PM_DEPT#,</isNotEmpty>
<isNotEmpty prepend=" " property="PRJTEST_PLATE1">PRJTEST_PLATE1=#PRJTEST_PLATE1#,</isNotEmpty>
<isNotEmpty prepend=" " property="PRJTEST_PLATE2">PRJTEST_PLATE2=#PRJTEST_PLATE2#,</isNotEmpty>
<isNotEmpty prepend=" " property="PRJTEST_AM">PRJTEST_AM=#PRJTEST_AM#,</isNotEmpty>
<isNotEmpty prepend=" " property="FACTORING_TYPE">FACTORING_TYPE=#FACTORING_TYPE#,</isNotEmpty>
<isNotEmpty prepend=" " property="PRJTEST_TYPE">PRJTEST_TYPE=#PRJTEST_TYPE#,</isNotEmpty>
<isNotEmpty prepend=" " property="CUSTEST_CODE">CUSTEST_CODE=#CUSTEST_CODE#,</isNotEmpty>
<isNotEmpty prepend=" " property="CUSTEST_ID">CUSTEST_ID=#CUSTEST_ID#,</isNotEmpty>
<isNotEmpty prepend=" " property="CO_AGENCY">CO_AGENCY=#CO_AGENCY#,</isNotEmpty>
<isNotEmpty prepend=" " property="CREDITEST_WAY">CREDITEST_WAY=#CREDITEST_WAY#,</isNotEmpty>
<isNotEmpty prepend=" " property="PRODUCTEST_INFO">PRODUCTEST_INFO=#PRODUCTEST_INFO#,</isNotEmpty>
<isNotEmpty prepend=" " property="TRADE_CDT">TRADE_CDT=#TRADE_CDT#,</isNotEmpty>
<isNotEmpty prepend=" " property="PRJTEST_STATUS">PRJTEST_STATUS=#PRJTEST_STATUS#,</isNotEmpty>
<isNotEmpty prepend=" " property="AUDITPASS_TIME">AUDITPASS_TIME=#AUDITPASS_TIME#,</isNotEmpty>
<isNotEmpty prepend=" " property="RECE_WAY">RECE_WAY=#RECE_WAY#,</isNotEmpty>
<isNotEmpty prepend=" " property="VERSION">VERSION=#VERSION#,</isNotEmpty>
<isNotEmpty prepend=" " property="MODIFY_TIME">MODIFY_TIME=SYSDATE,</isNotEmpty>
<isNotEmpty prepend=" " property="MODIFY_ID">MODIFY_ID=#MODIFY_ID#, </isNotEmpty>
ID=#ID#
WHERE ID=#ID#
</update>
<select id="getById" parameterClass="map" resultClass="Project">
SELECT ID,
PRJTEST_CODE,
PRJTEST_NAME,
PRJTEST_PM,
PM_DEPT,
PRJTEST_PLATE1,
PRJTEST_PLATE2,
PRJTEST_AM,
FACTORING_TYPE,
PRJTEST_TYPE,
CUSTEST_CODE,
CUSTEST_ID,
CO_AGENCY,
CREDITEST_WAY,
PRODUCTEST_INFO,
TRADE_CDT,
PRJTEST_STATUS,
AUDITPASS_TIME,
RECE_WAY,
VERSION,
CREATE_TIME,
CREATE_ID,
STATUS
FROM TEST_PRJTEST_PROJECT
WHERE ID = #ID#
</select>
</sqlMap>
全部使用map的类型
<?xml version="1.0" encoding="utf-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-2.dtd">
<!--
资源配置
-->
<sqlMap namespace="permissionConfig">
<!--
查询所有角色
-->
<select id="queryRoleList" parameterClass="map" resultClass="java.util.HashMap">
<![CDATA[ SELECT ID,NAME FROM TEST_PERMISSION_ROLE WHERE STATUS = 0 ORDER BY ID ]]>
</select>
<!--
通过角色查询资源菜单(STATUS:0已分配 1未分配)
-->
<resultMap class="hashmap" id="queryPermissionByRoleIdMap">
<result property="id" column="ID"/>
<result property="name" column="NAME"/>
<result property="pId" column="PARENTEST_ID"/>
<result property="level_num" column="LEVEL_NUM"/>
</resultMap>
<select id="queryPermissionByRoleId" parameterClass="map" resultMap="queryPermissionByRoleIdMap">
SELECT *
FROM ( SELECT T1.ID ,
T1.NAME ,
NVL(T1.PARENTEST_ID,0) PARENTEST_ID,
T1.LEVEL_NUM
FROM TEST_PERMISSION_RESOURCE T1
WHERE T1.ID <isEqual property="STATUS" compareValue="1"> NOT </isEqual> IN (
SELECT RESOURCE_ID
FROM TEST_PERMISSION_RESOURCE2ROLE
WHERE ROLE_ID = #ROLE_ID#
AND STATUS = 0 )
AND T1.STATUS = 0
AND PARENTEST_ID IS NOT NULL
UNION ALL
SELECT T1.ID ,
T1.NAME ,
NVL(T1.PARENTEST_ID,0) PARENTEST_ID,
T1.LEVEL_NUM
FROM TEST_PERMISSION_RESOURCE T1
WHERE T1.STATUS = 0
AND PARENTEST_ID IS NULL
AND ID IN (
SELECT T1.PARENTEST_ID
FROM TEST_PERMISSION_RESOURCE T1
WHERE T1.ID <isEqual property="STATUS" compareValue="1"> NOT </isEqual> IN (
SELECT RESOURCE_ID
FROM TEST_PERMISSION_RESOURCE2ROLE
WHERE ROLE_ID = #ROLE_ID#
AND STATUS = 0 )
AND T1.STATUS = 0 )
) T
ORDER BY LEVEL_NUM
</select>
<!--
查询所有公司
-->
<resultMap class="hashmap" id="queryCompanyMap">
<result property="id" column="ID"/>
<result property="name" column="NAME"/>
<result property="pId" column="PARENTEST_ID"/>
<result property="pId" column="PARENTEST_ID"/>
<result property="type" column="TYPE"/>
<result property="flag" column="FLAG"/>
</resultMap>
<select id="queryAllCompany" parameterClass="map" resultMap="queryCompanyMap">
<![CDATA[
SELECT DECP_ID ID, DECP_NAME_CN NAME, PARENTEST_ID , 'DECP' TYPE , 0 FLAG
FROM TEST_DEPTEST_COMPANY
WHERE STATUS = 0
ORDER BY DECP_ID
]]>
</select>
<!--
查询所有部门
-->
<resultMap class="hashmap" id="queryDepartmentMap">
<result property="id" column="ID"/>
<result property="name" column="NAME"/>
<result property="pId" column="PARENTEST_ID"/>
<result property="parentId" column="PARENTEST_ID"/>
<result property="decpId" column="DECP_ID"/>
<result property="type" column="TYPE"/>
<result property="flag" column="FLAG"/>
</resultMap>
<select id="queryAllDepartment" parameterClass="map" resultMap="queryDepartmentMap">
<![CDATA[
SELECT ID, DEPTEST_NAME NAME, PARENTEST_ID ,DECP_ID , 'DEPT' TYPE , 0 FLAG
FROM TEST_DEPTEST_DEPARTMENT
WHERE STATUS = 0
ORDER BY ID
]]>
</select>
<!--
通过角色查询未分配员工(STATUS:0已分配 1未分配)
-->
<resultMap class="hashmap" id="queryNoUserByRoleIdMap">
<result property="id" column="ID"/>
<result property="name" column="NAME"/>
<result property="pId" column="PARENTEST_ID"/>
<result property="flag" column="FLAG"/>
</resultMap>
<select id="queryNoUserByRoleId" parameterClass="map" resultMap="queryNoUserByRoleIdMap">
SELECT ID ,
NAME ,
NVL(DEPTEST_ID,0) PARENTEST_ID,
1 FLAG
FROM TEST_USER_USER
WHERE STATUS = 0
AND ID NOT IN ( SELECT EMPLOYEE_ID
FROM TEST_USER_USER2ROLE
WHERE ROLE_ID = #ROLE_ID#
AND STATUS = 0 )
</select>
<!--
通过角色查询已分配员工
-->
<resultMap class="hashmap" id="queryUserByRoleIdMap">
<result property="id" column="ID"/>
<result property="name" column="NAME_"/>
<result property="trueName" column="NAME"/>
<result property="parentId" column="PARENTEST_ID"/>
<result property="flag" column="FLAG"/>
</resultMap>
<select id="queryUserByRoleId" parameterClass="map" resultMap="queryUserByRoleIdMap">
SELECT T1.ID ,
T1.NAME ,
CASE WHEN T2.DEPTEST_NAME IS NOT NULL THEN
T1.NAME || #POINTER# || T2.DEPTEST_NAME
ELSE T1.NAME END NAME_,
NVL(T1.DEPTEST_ID , 0) PARENTEST_ID ,
1 FLAG
FROM TEST_USER_USER T1
LEFT JOIN TEST_DEPTEST_DEPARTMENT T2 ON ( T1.DEPTEST_ID = T2.ID
AND T2.STATUS = 0 )
WHERE T1.STATUS = 0
AND T1.ID IN ( SELECT EMPLOYEE_ID
FROM TEST_USER_USER2ROLE
WHERE ROLE_ID = #ROLE_ID#
AND STATUS = 0 )
</select>
<!--
根据角色id删除已分配资源
-->
<delete id="deleteResourceByRoleId" parameterClass="map">
<![CDATA[
DELETE FROM TEST_PERMISSION_RESOURCE2ROLE WHERE ROLE_ID = #ROLE_ID#
]]>
</delete>
<!--
分配资源
-->
<insert id="createResourceToRole" parameterClass="map">
<![CDATA[
INSERT INTO TEST_PERMISSION_RESOURCE2ROLE (
ID,
ROLE_ID,
RESOURCE_ID,
STATUS,
CREATE_DATE
) VALUES (
SEQ_PERMISSION_RESOU2ROLE.NEXTVAL,
#ROLE_ID#,
#RESOURCE_ID#,
0,
SYSDATE
)
]]>
</insert>
<!--
根据角色id删除已分配人员
-->
<delete id="deleteUserByRoleId" parameterClass="map">
<![CDATA[
DELETE FROM TEST_USER_USER2ROLE WHERE ROLE_ID = #ROLE_ID#
]]>
</delete>
<!--
分配人员
-->
<insert id="createUserToRole" parameterClass="map">
<![CDATA[
INSERT INTO TEST_USER_USER2ROLE (
ID,
EMPLOYEE_ID,
ROLE_ID,
STATUS,
CREATE_DATE
) VALUES (
SEQ_USER_USER2ROLE.NEXTVAL,
#EMPLOYEE_ID#,
#ROLE_ID#,
0,
SYSDATE
)
]]>
</insert>
</sqlMap>