IBATIS2.0映射文件 oracle/mysql 版实现示例，功能全面且丰富
- WBOYOriginal
- 2016-06-07 14:58:011210Durchsuche
IBATIS2.0映射文件oracle/mysql版实现示例，功能全面且丰富,开发参考可以提高一定效率，还不错。呵呵。 实现的功能 1.保存物资项目。 2.保存物资变更记录集合。 3.查询处理返回内容字段。 iBATIS Oracle MySQL ?xml version="1.0" encoding="UTF-8" standalon
IBATIS2.0映射文件 oracle/mysql 版实现示例，功能全面且丰富,开发参考可以提高一定效率，还不错。呵呵。实现的功能
1.保存物资项目。
2.保存物资变更记录集合。
3.查询处理返回内容字段。 iBATIS Oracle MySQL
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="HuGoods">
<!-- hu_goods -->
<sql id="Goods_Base_Column_List" >
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Fri Dec 12 11:53:06 CST 2014.
-->
HUGS_ID, HUGS_NAME, HUGS_BRAND, HUGS_VERSION, HUGS_PRODUCT_DATE, HUGS_ASSET_CODE,
HUGS_PRICE, HUGS_DEPT_ID, HUGS_DEPT_NAME, HUGS_USER_ID, HUGS_USER_NAME, HUGS_USE_RESON,
HUGS_USE_STATUS, HUGS_DESCR, HUGS_STATUS, HUGS_CREATOR, HUGS_CREATE_TIME, HUGS_UPDATE,
HUGS_UPDATE_TIME,HUGS_COMPANY
</sql>
<!-- where条件 -->
<sql id="Goods_Where_Clause" >
<dynamic prepend="WHERE HUGS_STATUS = 1">
<isNotEmpty property="HUGS_ASSET_CODE">
AND HUGS_ASSET_CODE LIKE '%$HUGS_ASSET_CODE$%'
</isNotEmpty>
<isNotEmpty property="HUGS_NAME">
AND HUGS_NAME LIKE '%$HUGS_NAME$%'
</isNotEmpty>
<isNotEmpty property="HUGS_DEPT_NAME">
AND HUGS_DEPT_NAME LIKE '%$HUGS_DEPT_NAME$%'
</isNotEmpty>
<isNotEmpty property="HUGS_USER_NAME">
AND HUGS_USER_NAME LIKE '%$HUGS_USER_NAME$%'
</isNotEmpty>
<isNotEmpty property="HUGS_USE_RESON">
AND HUGS_USE_RESON LIKE '%$HUGS_USE_RESON$%'
</isNotEmpty>
<isNotEmpty property="HUGS_COMPANY">
AND HUGS_COMPANY = #HUGS_COMPANY#
</isNotEmpty>
</dynamic>
</sql>
<!-- 物资总数 -->
<select id="getGoodsListCnt" resultClass="java.lang.Integer"
parameterClass="java.util.HashMap">
SELECT COUNT(*) FROM HU_GOODS
<isParameterPresent >
<include refid="HuGoods.Goods_Where_Clause" />
</isParameterPresent>
</select>
<!-- 物资分页集合查询 -->
<select id="getGoodsPageList" resultClass="java.util.HashMap"
parameterClass="java.util.HashMap">
<!-- oracle version -->
SELECT * FROM
(
SELECT
A .*, ROWNUM r
FROM
(
SELECT <include refid="HuGoods.Goods_Base_Column_List" />
FROM HU_GOODS
<isParameterPresent >
<include refid="HuGoods.Goods_Where_Clause" />
<isNotEmpty property="sortField">
ORDER BY $sortField$ $sortOrder$
</isNotEmpty>
</isParameterPresent>
) A
WHERE
ROWNUM <![CDATA[ <= ]]> #end#
) B
WHERE r <![CDATA[>]]> #start#
<!-- mysql version -->
<!--
SELECT <include refid="HuGoods.Goods_Base_Column_List" />
FROM HU_GOODS
<isParameterPresent >
<include refid="HuGoods.Goods_Where_Clause" />
<isNotEmpty property="sortField">
ORDER BY $sortField$ $sortOrder$
</isNotEmpty>
</isParameterPresent>
LIMIT #start#,#end#
-->
</select>
<!-- 物资EXLS导出数据 -->
<select id="getGoodsEXLSList" resultClass="java.util.HashMap"
parameterClass="java.util.HashMap">
<!-- oracle version -->
SELECT
(
SELECT
WM_CONCAT (
EM.HUGI_DESCR || ' ' ||'变更时间：'||TO_CHAR(EM.HUGI_CHANGE_DATE,'YYYY-MM-DD')
)
FROM
HU_GOODS_ITEM EM
WHERE
EM.HUGI_GOODS_ID = GOODS.HUGS_ID
) AS ITEMS,
GOODS.HUGS_NAME,
GOODS.HUGS_BRAND,
GOODS.HUGS_VERSION,
GOODS.HUGS_PRODUCT_DATE,
GOODS.HUGS_ASSET_CODE,
GOODS.HUGS_PRICE,
GOODS.HUGS_DEPT_NAME,
GOODS.HUGS_USER_NAME,
GOODS.HUGS_USE_RESON,
GOODS.HUGS_DESCR,
GOODS.HUGS_COMPANY,
CASE GOODS.HUGS_USE_STATUS
WHEN 1 THEN
'使用中'
WHEN 2 THEN
'在库'
ELSE
'作废'
END HUGS_USE_STATUS
,CASE GOODS.HUGS_CHECK_STATUS
WHEN 0 THEN
'尚未盘点'
WHEN -1 THEN
'问题物资'
ELSE
'盘点无误'
END HUGS_CHECK_STATUS
FROM
HU_GOODS GOODS
<isParameterPresent >
<include refid="HuGoods.Goods_Where_Clause" />
</isParameterPresent>
ORDER BY
GOODS.HUGS_ID ASC
<!-- mysql version -->
<!--
SELECT
GROUP_CONCAT(
CONCAT(
"变更：",
CONVERT (
IFNULL(EM.HUGI_USER_ID, ""),
CHAR
),
"_",
IFNULL(EM.HUGI_USER_NAME, ""),
"_",
IFNULL(EM.HUGI_DEPT_NAME, ""),
"_",
IFNULL(EM.HUGI_DESCR, "")
)
) AS ITEMS,
GOODS.HUGS_NAME,
GOODS.HUGS_BRAND,
GOODS.HUGS_VERSION,
GOODS.HUGS_PRODUCT_DATE,
GOODS.HUGS_ASSET_CODE,
GOODS.HUGS_PRICE,
GOODS.HUGS_DEPT_NAME,
GOODS.HUGS_USER_NAME,
GOODS.HUGS_USE_RESON,
GOODS.HUGS_DESCR,
CASE GOODS.HUGS_USE_STATUS
WHEN '1' THEN
'使用中'
WHEN '2' THEN
'在库'
ELSE
'作废'
END HUGS_USE_STATUS
FROM
HU_GOODS GOODS
LEFT JOIN HU_GOODS_ITEM EM ON EM.HUGI_GOODS_ID = GOODS.HUGS_ID
GROUP BY
GOODS.HUGS_ID
ORDER BY
GOODS.HUGS_ID ASC
-->
</select>
<!-- 查询物资公司种类 -->
<select id="getGoodsCompany" resultClass="java.util.HashMap"
parameterClass="java.util.HashMap">
SELECT OG.HUGS_COMPANY FROM HU_GOODS OG
WHERE OG.HUGS_ID IN(
SELECT MAX(GOOD.HUGS_ID) FROM HU_GOODS GOOD
GROUP BY GOOD.HUGS_COMPANY
)
ORDER BY OG.HUGS_ID ASC
</select>
<!-- 添加物资 -->
<insert id="addGoods" parameterClass="java.util.HashMap">
<!-- mysql version -->
<!--
INSERT INTO HU_GOODS (
`HUGS_ID`,
`HUGS_NAME`,
`HUGS_BRAND`,
`HUGS_VERSION`,
`HUGS_PRODUCT_DATE`,
`HUGS_ASSET_CODE`,
`HUGS_PRICE`,
`HUGS_DEPT_ID`,
`HUGS_DEPT_NAME`,
`HUGS_USER_ID`,
`HUGS_USER_NAME`,
`HUGS_USE_RESON`,
`HUGS_USE_STATUS`,
`HUGS_DESCR`,
`HUGS_STATUS`,
`HUGS_CREATOR`,
`HUGS_CREATE_TIME`,
`HUGS_UPDATE`,
`HUGS_UPDATE_TIME`
)
VALUES
(
NULL,
#HUGS_NAME#, #HUGS_BRAND#,
#HUGS_VERSION#,#HUGS_PRODUCT_DATE#,#HUGS_ASSET_CODE#,#HUGS_PRICE#,#HUGS_DEPT_ID#,#HUGS_DEPT_NAME#,#HUGS_USER_ID#,#HUGS_USER_NAME#,#HUGS_USE_RESON#,#HUGS_USE_STATUS#,#HUGS_DESCR#,#HUGS_STATUS#,#HUGS_CREATOR#,SYSDATE(),
#HUGS_UPDATE#,
SYSDATE()
);
<selectKey resultClass="java.lang.Integer" keyProperty="HUGS_ID">
SELECT LAST_INSERT_ID()
</selectKey>
-->
<!-- oracle version -->
<selectKey resultClass="java.lang.Integer" keyProperty="HUGS_ID" type="pre">
SELECT HU_GOODS_SQ.NEXTVAL AS VALUE FROM DUAL
</selectKey>
INSERT INTO HU_GOODS (
HUGS_ID,
HUGS_NAME,
HUGS_BRAND,
HUGS_VERSION,
HUGS_PRODUCT_DATE,
HUGS_ASSET_CODE,
HUGS_PRICE,
HUGS_DEPT_ID,
HUGS_DEPT_NAME,
HUGS_USER_ID,
HUGS_USER_NAME,
HUGS_USE_RESON,
HUGS_USE_STATUS,
HUGS_DESCR,
HUGS_STATUS,
HUGS_CREATOR,
HUGS_CREATE_TIME,
HUGS_UPDATE,
HUGS_UPDATE_TIME,
HUGS_COMPANY
)
VALUES
(
#HUGS_ID#,
#HUGS_NAME#, #HUGS_BRAND#,
#HUGS_VERSION#,#HUGS_PRODUCT_DATE#,#HUGS_ASSET_CODE#,#HUGS_PRICE#,#HUGS_DEPT_ID#,#HUGS_DEPT_NAME#,#HUGS_USER_ID#,#HUGS_USER_NAME#,#HUGS_USE_RESON#,#HUGS_USE_STATUS#,#HUGS_DESCR#,#HUGS_STATUS#,#HUGS_CREATOR#,SYSDATE,
#HUGS_UPDATE#,
SYSDATE,
#HUGS_COMPANY#
)
</insert>
<!-- 更新物资 -->
<update id="updateGoods" parameterClass="java.util.HashMap">
<!-- oracle version -->
UPDATE HU_GOODS SET
HUGS_NAME=#HUGS_NAME#, HUGS_BRAND=#HUGS_BRAND#,
HUGS_VERSION=#HUGS_VERSION#,HUGS_PRODUCT_DATE=#HUGS_PRODUCT_DATE#,HUGS_PRICE=#HUGS_PRICE#,HUGS_DEPT_ID=#HUGS_DEPT_ID#,HUGS_DEPT_NAME=#HUGS_DEPT_NAME#,HUGS_USER_ID=#HUGS_USER_ID#,HUGS_USER_NAME=#HUGS_USER_NAME#,HUGS_USE_RESON=#HUGS_USE_RESON#,HUGS_USE_STATUS=#HUGS_USE_STATUS#,HUGS_DESCR=#HUGS_DESCR#,HUGS_STATUS=#HUGS_STATUS#,HUGS_CREATOR=#HUGS_CREATOR#,HUGS_UPDATE_TIME=SYSDATE
where HUGS_ASSET_CODE=#HUGS_ASSET_CODE#
<!-- mysql version -->
<!--
UPDATE HU_GOODS SET
HUGS_NAME=#HUGS_NAME#, HUGS_BRAND=#HUGS_BRAND#,
HUGS_VERSION=#HUGS_VERSION#,HUGS_PRODUCT_DATE=#HUGS_PRODUCT_DATE#,HUGS_PRICE=#HUGS_PRICE#,HUGS_DEPT_ID=#HUGS_DEPT_ID#,HUGS_DEPT_NAME=#HUGS_DEPT_NAME#,HUGS_USER_ID=#HUGS_USER_ID#,HUGS_USER_NAME=#HUGS_USER_NAME#,HUGS_USE_RESON=#HUGS_USE_RESON#,HUGS_USE_STATUS=#HUGS_USE_STATUS#,HUGS_DESCR=#HUGS_DESCR#,HUGS_STATUS=#HUGS_STATUS#,HUGS_CREATOR=#HUGS_CREATOR#,HUGS_UPDATE_TIME=SYSDATE()
where HUGS_ASSET_CODE=#HUGS_ASSET_CODE#
-->
</update>
<!-- 查询固定资产编码数量 -->
<select id="selectHugsAssetCodeCount" parameterClass="java.util.HashMap"
resultClass="int">
SELECT COUNT(HG.HUGS_ID) AS COUNT FROM HU_GOODS HG WHERE
HG.HUGS_ASSET_CODE=#HUGS_ASSET_CODE#
</select>
<!-- 由固定资产编码查询对应的记录id集合 -->
<select id="selectGoodsIdListByHugsAssetCode" parameterClass="java.util.HashMap" resultClass="java.util.HashMap">
SELECT HG.HUGS_ID FROM HU_GOODS HG WHERE
HG.HUGS_ASSET_CODE=#HUGS_ASSET_CODE#
</select>
<!-- ################################################################################# -->
<!-- HU_GOODS_ITEM -->
<!-- 变更项列集合 -->
<sql id="Goods_Item_Base_Column_List" >
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Fri Dec 12 11:53:06 CST 2014.
-->
HUGI_ID, HUGI_GOODS_ID, HUGI_DEPT_ID, HUGI_DEPT_NAME, HUGI_USER_ID, HUGI_USER_NAME,
HUGI_USE_RESON, HUGI_USE_STATUS, HUGI_DESCR, HUGI_STATUS, HUGI_CREATOR, HUGI_CREATE_TIME,
HUGI_UPDATE, HUGI_UPDATE_TIME
</sql>
<!-- where条件 -->
<sql id="Goods_Item_Where_Clause" >
<dynamic prepend="WHERE 1=1">
<isNotEmpty property="HUGI_GOODS_ID">
AND HUGI_GOODS_ID = #HUGI_GOODS_ID#
</isNotEmpty>
<isNotEmpty property="HUGI_DEPT_NAME">
AND HUGI_DEPT_NAME LIKE '%$HUGI_DEPT_NAME$%'
</isNotEmpty>
<isNotEmpty property="HUGI_USER_NAME">
AND HUGI_USER_NAME LIKE '%$HUGI_USER_NAME$%'
</isNotEmpty>
<isNotEmpty property="HUGI_USE_RESON">
AND HUGI_USE_RESON LIKE '%$HUGI_USE_RESON$%'
</isNotEmpty>
</dynamic>
</sql>
<!-- 物资变更项总数 -->
<select id="getGoodsItemListCnt" resultClass="java.lang.Integer"
parameterClass="java.util.HashMap">
SELECT COUNT(*) FROM HU_GOODS_ITEM
<isParameterPresent >
<include refid="HuGoods.Goods_Item_Where_Clause" />
</isParameterPresent>
</select>
<!-- 物资变更项分页集合查询 -->
<select id="getGoodsItemPageList" resultClass="java.util.HashMap"
parameterClass="java.util.HashMap">
<!-- oracle version -->
SELECT * FROM
(
SELECT
A .*, ROWNUM r
FROM
(
SELECT <include refid="HuGoods.Goods_Item_Base_Column_List" />
FROM HU_GOODS_ITEM
<isParameterPresent >
<include refid="HuGoods.Goods_Item_Where_Clause" />
<isNotEmpty property="sortField">
ORDER BY $sortField$ $sortOrder$
</isNotEmpty>
</isParameterPresent>
) A
WHERE
ROWNUM <![CDATA[ <= ]]> #end#
) B
WHERE r <![CDATA[>]]> #start#
<!-- mysql version -->
<!--
SELECT <include refid="HuGoods.Goods_Item_Base_Column_List" />
FROM HU_GOODS_ITEM
<isParameterPresent >
<include refid="HuGoods.Goods_Item_Where_Clause" />
<isNotEmpty property="sortField">
ORDER BY $sortField$ $sortOrder$
</isNotEmpty>
</isParameterPresent>
LIMIT #start#,#end#
-->
</select>
<!-- 添加记录明细 -->
<insert id="addGoodsItem" parameterClass="java.util.HashMap">
<!-- oracle version -->
<selectKey resultClass="java.lang.Integer" keyProperty="HUGI_ID" type="pre">
SELECT HU_GOODS_ITEM_SQ.NEXTVAL AS VALUE FROM DUAL
</selectKey>
INSERT INTO HU_GOODS_ITEM (HUGI_ID,HUGI_GOODS_ID, HUGI_DEPT_ID, HUGI_DEPT_NAME,
HUGI_USER_ID, HUGI_USER_NAME, HUGI_USE_RESON, HUGI_USE_STATUS,
HUGI_DESCR, HUGI_STATUS, HUGI_CREATOR, HUGI_CREATE_TIME,
HUGI_UPDATE, HUGI_UPDATE_TIME)
VALUES (#HUGI_ID#,#HUGI_GOODS_ID#, #HUGI_DEPT_ID#, #HUGI_DEPT_NAME#,
#HUGI_USER_ID#, #HUGI_USER_NAME#, #HUGI_USE_RESON#, #HUGI_USE_STATUS#,
#HUGI_DESCR#, #HUGI_STATUS#, #HUGI_CREATOR#, SYSDATE,
#HUGI_UPDATE#, SYSDATE)
<!-- mysql version -->
<!--
INSERT INTO HU_GOODS_ITEM (HUGI_GOODS_ID, HUGI_DEPT_ID, HUGI_DEPT_NAME,
HUGI_USER_ID, HUGI_USER_NAME, HUGI_USE_RESON, HUGI_USE_STATUS,
HUGI_DESCR, HUGI_STATUS, HUGI_CREATOR, HUGI_CREATE_TIME,
HUGI_UPDATE, HUGI_UPDATE_TIME)
VALUES (#HUGI_GOODS_ID:INTEGER#, #HUGI_DEPT_ID:VARCHAR#, #HUGI_DEPT_NAME:VARCHAR#,
#HUGI_USER_ID:INTEGER#, #HUGI_USER_NAME:VARCHAR#, #HUGI_USE_RESON:VARCHAR#, #HUGI_USE_STATUS:INTEGER#,
#HUGI_DESCR:VARCHAR#, #HUGI_STATUS:INTEGER#, #HUGI_CREATOR:VARCHAR#, SYSDATE(),
#HUGI_UPDATE:VARCHAR#, SYSDATE())
<selectKey resultClass="java.lang.Integer" keyProperty="HUGI_ID">
SELECT LAST_INSERT_ID() AS HUGI_ID
</selectKey>
-->
</insert>
<!-- 删除物资变更明细 -->
<delete id="deleteGoodsItem" parameterClass="java.util.HashMap">
DELETE FROM HU_GOODS_ITEM WHERE HUGI_ID =#HUGI_ID#
</delete>
</sqlMap>
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="HuGoods">
<!-- hu_goods -->
<sql id="Goods_Base_Column_List" >
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Fri Dec 12 11:53:06 CST 2014.
-->
HUGS_ID, HUGS_NAME, HUGS_BRAND, HUGS_VERSION, HUGS_PRODUCT_DATE, HUGS_ASSET_CODE,
HUGS_PRICE, HUGS_DEPT_ID, HUGS_DEPT_NAME, HUGS_USER_ID, HUGS_USER_NAME, HUGS_USE_RESON,
HUGS_USE_STATUS, HUGS_DESCR, HUGS_STATUS, HUGS_CREATOR, HUGS_CREATE_TIME, HUGS_UPDATE,
HUGS_UPDATE_TIME,HUGS_COMPANY
</sql>
<!-- where条件 -->
<sql id="Goods_Where_Clause" >
<dynamic prepend="WHERE HUGS_STATUS = 1">
<isNotEmpty property="HUGS_ASSET_CODE">
AND HUGS_ASSET_CODE LIKE '%$HUGS_ASSET_CODE$%'
</isNotEmpty>
<isNotEmpty property="HUGS_NAME">
AND HUGS_NAME LIKE '%$HUGS_NAME$%'
</isNotEmpty>
<isNotEmpty property="HUGS_DEPT_NAME">
AND HUGS_DEPT_NAME LIKE '%$HUGS_DEPT_NAME$%'
</isNotEmpty>
<isNotEmpty property="HUGS_USER_NAME">
AND HUGS_USER_NAME LIKE '%$HUGS_USER_NAME$%'
</isNotEmpty>
<isNotEmpty property="HUGS_USE_RESON">
AND HUGS_USE_RESON LIKE '%$HUGS_USE_RESON$%'
</isNotEmpty>
<isNotEmpty property="HUGS_COMPANY">
AND HUGS_COMPANY = #HUGS_COMPANY#
</isNotEmpty>
</dynamic>
</sql>
<!-- 物资总数 -->
<select id="getGoodsListCnt" resultClass="java.lang.Integer"
parameterClass="java.util.HashMap">
SELECT COUNT(*) FROM HU_GOODS
<isParameterPresent >
<include refid="HuGoods.Goods_Where_Clause" />
</isParameterPresent>
</select>
<!-- 物资分页集合查询 -->
<select id="getGoodsPageList" resultClass="java.util.HashMap"
parameterClass="java.util.HashMap">
<!-- oracle version -->
SELECT * FROM
(
SELECT
A .*, ROWNUM r
FROM
(
SELECT <include refid="HuGoods.Goods_Base_Column_List" />
FROM HU_GOODS
<isParameterPresent >
<include refid="HuGoods.Goods_Where_Clause" />
<isNotEmpty property="sortField">
ORDER BY $sortField$ $sortOrder$
</isNotEmpty>
</isParameterPresent>
) A
WHERE
ROWNUM <![CDATA[ <= ]]> #end#
) B
WHERE r <![CDATA[>]]> #start#
<!-- mysql version -->
<!--
SELECT <include refid="HuGoods.Goods_Base_Column_List" />
FROM HU_GOODS
<isParameterPresent >
<include refid="HuGoods.Goods_Where_Clause" />
<isNotEmpty property="sortField">
ORDER BY $sortField$ $sortOrder$
</isNotEmpty>
</isParameterPresent>
LIMIT #start#,#end#
-->
</select>
<!-- 物资EXLS导出数据 -->
<select id="getGoodsEXLSList" resultClass="java.util.HashMap"
parameterClass="java.util.HashMap">
<!-- oracle version -->
SELECT
(
SELECT
WM_CONCAT (
EM.HUGI_DESCR || ' ' ||'变更时间：'||TO_CHAR(EM.HUGI_CHANGE_DATE,'YYYY-MM-DD')
)
FROM
HU_GOODS_ITEM EM
WHERE
EM.HUGI_GOODS_ID = GOODS.HUGS_ID
) AS ITEMS,
GOODS.HUGS_NAME,
GOODS.HUGS_BRAND,
GOODS.HUGS_VERSION,
GOODS.HUGS_PRODUCT_DATE,
GOODS.HUGS_ASSET_CODE,
GOODS.HUGS_PRICE,
GOODS.HUGS_DEPT_NAME,
GOODS.HUGS_USER_NAME,
GOODS.HUGS_USE_RESON,
GOODS.HUGS_DESCR,
GOODS.HUGS_COMPANY,
CASE GOODS.HUGS_USE_STATUS
WHEN 1 THEN
'使用中'
WHEN 2 THEN
'在库'
ELSE
'作废'
END HUGS_USE_STATUS
,CASE GOODS.HUGS_CHECK_STATUS
WHEN 0 THEN
'尚未盘点'
WHEN -1 THEN
'问题物资'
ELSE
'盘点无误'
END HUGS_CHECK_STATUS
FROM
HU_GOODS GOODS
<isParameterPresent >
<include refid="HuGoods.Goods_Where_Clause" />
</isParameterPresent>
ORDER BY
GOODS.HUGS_ID ASC
<!-- mysql version -->
<!--
SELECT
GROUP_CONCAT(
CONCAT(
"变更：",
CONVERT (
IFNULL(EM.HUGI_USER_ID, ""),
CHAR
),
"_",
IFNULL(EM.HUGI_USER_NAME, ""),
"_",
IFNULL(EM.HUGI_DEPT_NAME, ""),
"_",
IFNULL(EM.HUGI_DESCR, "")
)
) AS ITEMS,
GOODS.HUGS_NAME,
GOODS.HUGS_BRAND,
GOODS.HUGS_VERSION,
GOODS.HUGS_PRODUCT_DATE,
GOODS.HUGS_ASSET_CODE,
GOODS.HUGS_PRICE,
GOODS.HUGS_DEPT_NAME,
GOODS.HUGS_USER_NAME,
GOODS.HUGS_USE_RESON,
GOODS.HUGS_DESCR,
CASE GOODS.HUGS_USE_STATUS
WHEN '1' THEN
'使用中'
WHEN '2' THEN
'在库'
ELSE
'作废'
END HUGS_USE_STATUS
FROM
HU_GOODS GOODS
LEFT JOIN HU_GOODS_ITEM EM ON EM.HUGI_GOODS_ID = GOODS.HUGS_ID
GROUP BY
GOODS.HUGS_ID
ORDER BY
GOODS.HUGS_ID ASC
-->
</select>
<!-- 查询物资公司种类 -->
<select id="getGoodsCompany" resultClass="java.util.HashMap"
parameterClass="java.util.HashMap">
SELECT OG.HUGS_COMPANY FROM HU_GOODS OG
WHERE OG.HUGS_ID IN(
SELECT MAX(GOOD.HUGS_ID) FROM HU_GOODS GOOD
GROUP BY GOOD.HUGS_COMPANY
)
ORDER BY OG.HUGS_ID ASC
</select>
<!-- 添加物资 -->
<insert id="addGoods" parameterClass="java.util.HashMap">
<!-- mysql version -->
<!--
INSERT INTO HU_GOODS (
`HUGS_ID`,
`HUGS_NAME`,
`HUGS_BRAND`,
`HUGS_VERSION`,
`HUGS_PRODUCT_DATE`,
`HUGS_ASSET_CODE`,
`HUGS_PRICE`,
`HUGS_DEPT_ID`,
`HUGS_DEPT_NAME`,
`HUGS_USER_ID`,
`HUGS_USER_NAME`,
`HUGS_USE_RESON`,
`HUGS_USE_STATUS`,
`HUGS_DESCR`,
`HUGS_STATUS`,
`HUGS_CREATOR`,
`HUGS_CREATE_TIME`,
`HUGS_UPDATE`,
`HUGS_UPDATE_TIME`
)
VALUES
(
NULL,
#HUGS_NAME#, #HUGS_BRAND#,
#HUGS_VERSION#,#HUGS_PRODUCT_DATE#,#HUGS_ASSET_CODE#,#HUGS_PRICE#,#HUGS_DEPT_ID#,#HUGS_DEPT_NAME#,#HUGS_USER_ID#,#HUGS_USER_NAME#,#HUGS_USE_RESON#,#HUGS_USE_STATUS#,#HUGS_DESCR#,#HUGS_STATUS#,#HUGS_CREATOR#,SYSDATE(),
#HUGS_UPDATE#,
SYSDATE()
);
<selectKey resultClass="java.lang.Integer" keyProperty="HUGS_ID">
SELECT LAST_INSERT_ID()
</selectKey>
-->
<!-- oracle version -->
<selectKey resultClass="java.lang.Integer" keyProperty="HUGS_ID" type="pre">
SELECT HU_GOODS_SQ.NEXTVAL AS VALUE FROM DUAL
</selectKey>
INSERT INTO HU_GOODS (
HUGS_ID,
HUGS_NAME,
HUGS_BRAND,
HUGS_VERSION,
HUGS_PRODUCT_DATE,
HUGS_ASSET_CODE,
HUGS_PRICE,
HUGS_DEPT_ID,
HUGS_DEPT_NAME,
HUGS_USER_ID,
HUGS_USER_NAME,
HUGS_USE_RESON,
HUGS_USE_STATUS,
HUGS_DESCR,
HUGS_STATUS,
HUGS_CREATOR,
HUGS_CREATE_TIME,
HUGS_UPDATE,
HUGS_UPDATE_TIME,
HUGS_COMPANY
)
VALUES
(
#HUGS_ID#,
#HUGS_NAME#, #HUGS_BRAND#,
#HUGS_VERSION#,#HUGS_PRODUCT_DATE#,#HUGS_ASSET_CODE#,#HUGS_PRICE#,#HUGS_DEPT_ID#,#HUGS_DEPT_NAME#,#HUGS_USER_ID#,#HUGS_USER_NAME#,#HUGS_USE_RESON#,#HUGS_USE_STATUS#,#HUGS_DESCR#,#HUGS_STATUS#,#HUGS_CREATOR#,SYSDATE,
#HUGS_UPDATE#,
SYSDATE,
#HUGS_COMPANY#
)
</insert>
<!-- 更新物资 -->
<update id="updateGoods" parameterClass="java.util.HashMap">
<!-- oracle version -->
UPDATE HU_GOODS SET
HUGS_NAME=#HUGS_NAME#, HUGS_BRAND=#HUGS_BRAND#,
HUGS_VERSION=#HUGS_VERSION#,HUGS_PRODUCT_DATE=#HUGS_PRODUCT_DATE#,HUGS_PRICE=#HUGS_PRICE#,HUGS_DEPT_ID=#HUGS_DEPT_ID#,HUGS_DEPT_NAME=#HUGS_DEPT_NAME#,HUGS_USER_ID=#HUGS_USER_ID#,HUGS_USER_NAME=#HUGS_USER_NAME#,HUGS_USE_RESON=#HUGS_USE_RESON#,HUGS_USE_STATUS=#HUGS_USE_STATUS#,HUGS_DESCR=#HUGS_DESCR#,HUGS_STATUS=#HUGS_STATUS#,HUGS_CREATOR=#HUGS_CREATOR#,HUGS_UPDATE_TIME=SYSDATE
where HUGS_ASSET_CODE=#HUGS_ASSET_CODE#
<!-- mysql version -->
<!--
UPDATE HU_GOODS SET
HUGS_NAME=#HUGS_NAME#, HUGS_BRAND=#HUGS_BRAND#,
HUGS_VERSION=#HUGS_VERSION#,HUGS_PRODUCT_DATE=#HUGS_PRODUCT_DATE#,HUGS_PRICE=#HUGS_PRICE#,HUGS_DEPT_ID=#HUGS_DEPT_ID#,HUGS_DEPT_NAME=#HUGS_DEPT_NAME#,HUGS_USER_ID=#HUGS_USER_ID#,HUGS_USER_NAME=#HUGS_USER_NAME#,HUGS_USE_RESON=#HUGS_USE_RESON#,HUGS_USE_STATUS=#HUGS_USE_STATUS#,HUGS_DESCR=#HUGS_DESCR#,HUGS_STATUS=#HUGS_STATUS#,HUGS_CREATOR=#HUGS_CREATOR#,HUGS_UPDATE_TIME=SYSDATE()
where HUGS_ASSET_CODE=#HUGS_ASSET_CODE#
-->
</update>
<!-- 查询固定资产编码数量 -->
<select id="selectHugsAssetCodeCount" parameterClass="java.util.HashMap"
resultClass="int">
SELECT COUNT(HG.HUGS_ID) AS COUNT FROM HU_GOODS HG WHERE
HG.HUGS_ASSET_CODE=#HUGS_ASSET_CODE#
</select>
<!-- 由固定资产编码查询对应的记录id集合 -->
<select id="selectGoodsIdListByHugsAssetCode" parameterClass="java.util.HashMap" resultClass="java.util.HashMap">
SELECT HG.HUGS_ID FROM HU_GOODS HG WHERE
HG.HUGS_ASSET_CODE=#HUGS_ASSET_CODE#
</select>
<!-- ################################################################################# -->
<!-- HU_GOODS_ITEM -->
<!-- 变更项列集合 -->
<sql id="Goods_Item_Base_Column_List" >
<!--
WARNING - @mbggenerated
This element is automatically generated by MyBatis Generator, do not modify.
This element was generated on Fri Dec 12 11:53:06 CST 2014.
-->
HUGI_ID, HUGI_GOODS_ID, HUGI_DEPT_ID, HUGI_DEPT_NAME, HUGI_USER_ID, HUGI_USER_NAME,
HUGI_USE_RESON, HUGI_USE_STATUS, HUGI_DESCR, HUGI_STATUS, HUGI_CREATOR, HUGI_CREATE_TIME,
HUGI_UPDATE, HUGI_UPDATE_TIME
</sql>
<!-- where条件 -->
<sql id="Goods_Item_Where_Clause" >
<dynamic prepend="WHERE 1=1">
<isNotEmpty property="HUGI_GOODS_ID">
AND HUGI_GOODS_ID = #HUGI_GOODS_ID#
</isNotEmpty>
<isNotEmpty property="HUGI_DEPT_NAME">
AND HUGI_DEPT_NAME LIKE '%$HUGI_DEPT_NAME$%'
</isNotEmpty>
<isNotEmpty property="HUGI_USER_NAME">
AND HUGI_USER_NAME LIKE '%$HUGI_USER_NAME$%'
</isNotEmpty>
<isNotEmpty property="HUGI_USE_RESON">
AND HUGI_USE_RESON LIKE '%$HUGI_USE_RESON$%'
</isNotEmpty>
</dynamic>
</sql>
<!-- 物资变更项总数 -->
<select id="getGoodsItemListCnt" resultClass="java.lang.Integer"
parameterClass="java.util.HashMap">
SELECT COUNT(*) FROM HU_GOODS_ITEM
<isParameterPresent >
<include refid="HuGoods.Goods_Item_Where_Clause" />
</isParameterPresent>
</select>
<!-- 物资变更项分页集合查询 -->
<select id="getGoodsItemPageList" resultClass="java.util.HashMap"
parameterClass="java.util.HashMap">
<!-- oracle version -->
SELECT * FROM
(
SELECT
A .*, ROWNUM r
FROM
(
SELECT <include refid="HuGoods.Goods_Item_Base_Column_List" />
FROM HU_GOODS_ITEM
<isParameterPresent >
<include refid="HuGoods.Goods_Item_Where_Clause" />
<isNotEmpty property="sortField">
ORDER BY $sortField$ $sortOrder$
</isNotEmpty>
</isParameterPresent>
) A
WHERE
ROWNUM <![CDATA[ <= ]]> #end#
) B
WHERE r <![CDATA[>]]> #start#
<!-- mysql version -->
<!--
SELECT <include refid="HuGoods.Goods_Item_Base_Column_List" />
FROM HU_GOODS_ITEM
<isParameterPresent >
<include refid="HuGoods.Goods_Item_Where_Clause" />
<isNotEmpty property="sortField">
ORDER BY $sortField$ $sortOrder$
</isNotEmpty>
</isParameterPresent>
LIMIT #start#,#end#
-->
</select>
<!-- 添加记录明细 -->
<insert id="addGoodsItem" parameterClass="java.util.HashMap">
<!-- oracle version -->
<selectKey resultClass="java.lang.Integer" keyProperty="HUGI_ID" type="pre">
SELECT HU_GOODS_ITEM_SQ.NEXTVAL AS VALUE FROM DUAL
</selectKey>
INSERT INTO HU_GOODS_ITEM (HUGI_ID,HUGI_GOODS_ID, HUGI_DEPT_ID, HUGI_DEPT_NAME,
HUGI_USER_ID, HUGI_USER_NAME, HUGI_USE_RESON, HUGI_USE_STATUS,
HUGI_DESCR, HUGI_STATUS, HUGI_CREATOR, HUGI_CREATE_TIME,
HUGI_UPDATE, HUGI_UPDATE_TIME)
VALUES (#HUGI_ID#,#HUGI_GOODS_ID#, #HUGI_DEPT_ID#, #HUGI_DEPT_NAME#,
#HUGI_USER_ID#, #HUGI_USER_NAME#, #HUGI_USE_RESON#, #HUGI_USE_STATUS#,
#HUGI_DESCR#, #HUGI_STATUS#, #HUGI_CREATOR#, SYSDATE,
#HUGI_UPDATE#, SYSDATE)
<!-- mysql version -->
<!--
INSERT INTO HU_GOODS_ITEM (HUGI_GOODS_ID, HUGI_DEPT_ID, HUGI_DEPT_NAME,
HUGI_USER_ID, HUGI_USER_NAME, HUGI_USE_RESON, HUGI_USE_STATUS,
HUGI_DESCR, HUGI_STATUS, HUGI_CREATOR, HUGI_CREATE_TIME,
HUGI_UPDATE, HUGI_UPDATE_TIME)
VALUES (#HUGI_GOODS_ID:INTEGER#, #HUGI_DEPT_ID:VARCHAR#, #HUGI_DEPT_NAME:VARCHAR#,
#HUGI_USER_ID:INTEGER#, #HUGI_USER_NAME:VARCHAR#, #HUGI_USE_RESON:VARCHAR#, #HUGI_USE_STATUS:INTEGER#,
#HUGI_DESCR:VARCHAR#, #HUGI_STATUS:INTEGER#, #HUGI_CREATOR:VARCHAR#, SYSDATE(),
#HUGI_UPDATE:VARCHAR#, SYSDATE())
<selectKey resultClass="java.lang.Integer" keyProperty="HUGI_ID">
SELECT LAST_INSERT_ID() AS HUGI_ID
</selectKey>
-->
</insert>
<!-- 删除物资变更明细 -->
<delete id="deleteGoodsItem" parameterClass="java.util.HashMap">
DELETE FROM HU_GOODS_ITEM WHERE HUGI_ID =#HUGI_ID#
</delete>
</sqlMap>
Stellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel：MS SQL 数据分页(通用)Nächster Artikel：可以提取表的结构信息