IBATIS2.0映射文件oracle/mysql版实现示例，功能全面且丰富

	
	
 
 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
 	
 	
	
	
	
	AND HUGS_ASSET_CODE LIKE '%$HUGS_ASSET_CODE$%'
	
	
	AND HUGS_NAME LIKE '%$HUGS_NAME$%'
	
	
	AND HUGS_DEPT_NAME LIKE '%$HUGS_DEPT_NAME$%'
	
	
	AND HUGS_USER_NAME LIKE '%$HUGS_USER_NAME$%'
	
	
	AND HUGS_USE_RESON LIKE '%$HUGS_USE_RESON$%'
	
	
	AND HUGS_COMPANY = #HUGS_COMPANY#
	
	
 	
	
	
	SELECT COUNT(*) FROM HU_GOODS
	
	 
 	
	
	
	
	
	SELECT * FROM
	(
	SELECT
	A .*, ROWNUM r
	FROM
	(
	SELECT 
	FROM HU_GOODS
	
	
	
	ORDER BY $sortField$ $sortOrder$
	
	
	) A
	WHERE
	ROWNUM #end#
	) B
	WHERE r ]]> #start#
	
	
	FROM HU_GOODS
	 
	 
	 
	ORDER BY $sortField$ $sortOrder$
	 
 	
	LIMIT #start#,#end#
	 -->
	
	
	
	
	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
	
	 
	 	
	 ORDER BY
	 GOODS.HUGS_ID ASC


	
	
	
	
	
	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 LAST_INSERT_ID()
	
	 -->
	
	 
 SELECT HU_GOODS_SQ.NEXTVAL AS VALUE FROM DUAL 
 	 
	 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#
	)
	
	
	
	
	
	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#
	
	
	
	
	
	
	SELECT COUNT(HG.HUGS_ID) AS COUNT FROM HU_GOODS HG WHERE
	HG.HUGS_ASSET_CODE=#HUGS_ASSET_CODE#
	
	
	
	SELECT HG.HUGS_ID FROM HU_GOODS HG WHERE
	HG.HUGS_ASSET_CODE=#HUGS_ASSET_CODE#
	
	
	
	
	
	
	
 
 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
 	
 	
	
	
	
	AND HUGI_GOODS_ID = #HUGI_GOODS_ID#
	
	
	AND HUGI_DEPT_NAME LIKE '%$HUGI_DEPT_NAME$%'
	
	
	AND HUGI_USER_NAME LIKE '%$HUGI_USER_NAME$%'
	
	
	AND HUGI_USE_RESON LIKE '%$HUGI_USE_RESON$%'
		
	
 	
	
	
	SELECT COUNT(*) FROM HU_GOODS_ITEM
	
	 
 	
	
	
	
	
	SELECT * FROM
	(
	SELECT
	A .*, ROWNUM r
	FROM
	(

	SELECT 
	FROM HU_GOODS_ITEM
	
	
	
	ORDER BY $sortField$ $sortOrder$
	
	

	) A
	WHERE
	ROWNUM #end#
	) B
	WHERE r ]]> #start#
	
	
	FROM HU_GOODS_ITEM
	 
	 
	 
	ORDER BY $sortField$ $sortOrder$
	 
 	
	LIMIT #start#,#end#
	 -->
	
	
	
	
	
	 
 SELECT HU_GOODS_ITEM_SQ.NEXTVAL AS VALUE FROM DUAL 
 	 
 	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)
 	
 	
	SELECT LAST_INSERT_ID() AS HUGI_ID
	
	 -->
	
	
	
	DELETE FROM HU_GOODS_ITEM WHERE HUGI_ID =#HUGI_ID#
	

	
	
 
 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
 	
 	
	
	
	
	AND HUGS_ASSET_CODE LIKE '%$HUGS_ASSET_CODE$%'
	
	
	AND HUGS_NAME LIKE '%$HUGS_NAME$%'
	
	
	AND HUGS_DEPT_NAME LIKE '%$HUGS_DEPT_NAME$%'
	
	
	AND HUGS_USER_NAME LIKE '%$HUGS_USER_NAME$%'
	
	
	AND HUGS_USE_RESON LIKE '%$HUGS_USE_RESON$%'
	
	
	AND HUGS_COMPANY = #HUGS_COMPANY#
	
	
 	
	
	
	SELECT COUNT(*) FROM HU_GOODS
	
	 
 	
	
	
	
	
	SELECT * FROM
	(
	SELECT
	A .*, ROWNUM r
	FROM
	(
	SELECT 
	FROM HU_GOODS
	
	
	
	ORDER BY $sortField$ $sortOrder$
	
	
	) A
	WHERE
	ROWNUM #end#
	) B
	WHERE r ]]> #start#
	
	
	FROM HU_GOODS
	 
	 
	 
	ORDER BY $sortField$ $sortOrder$
	 
 	
	LIMIT #start#,#end#
	 -->
	
	
	
	
	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
	
	 
	 	
	 ORDER BY
	 GOODS.HUGS_ID ASC


	
	
	
	
	
	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 LAST_INSERT_ID()
	
	 -->
	
	 
 SELECT HU_GOODS_SQ.NEXTVAL AS VALUE FROM DUAL 
 	 
	 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#
	)
	
	
	
	
	
	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#
	
	
	
	
	
	
	SELECT COUNT(HG.HUGS_ID) AS COUNT FROM HU_GOODS HG WHERE
	HG.HUGS_ASSET_CODE=#HUGS_ASSET_CODE#
	
	
	
	SELECT HG.HUGS_ID FROM HU_GOODS HG WHERE
	HG.HUGS_ASSET_CODE=#HUGS_ASSET_CODE#
	
	
	
	
	
	
	
 
 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
 	
 	
	
	
	
	AND HUGI_GOODS_ID = #HUGI_GOODS_ID#
	
	
	AND HUGI_DEPT_NAME LIKE '%$HUGI_DEPT_NAME$%'
	
	
	AND HUGI_USER_NAME LIKE '%$HUGI_USER_NAME$%'
	
	
	AND HUGI_USE_RESON LIKE '%$HUGI_USE_RESON$%'
		
	
 	
	
	
	SELECT COUNT(*) FROM HU_GOODS_ITEM
	
	 
 	
	
	
	
	
	SELECT * FROM
	(
	SELECT
	A .*, ROWNUM r
	FROM
	(

	SELECT 
	FROM HU_GOODS_ITEM
	
	
	
	ORDER BY $sortField$ $sortOrder$
	
	

	) A
	WHERE
	ROWNUM #end#
	) B
	WHERE r ]]> #start#
	
	
	FROM HU_GOODS_ITEM
	 
	 
	 
	ORDER BY $sortField$ $sortOrder$
	 
 	
	LIMIT #start#,#end#
	 -->
	
	
	
	
	
	 
 SELECT HU_GOODS_ITEM_SQ.NEXTVAL AS VALUE FROM DUAL 
 	 
 	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)
 	
 	
	SELECT LAST_INSERT_ID() AS HUGI_ID
	
	 -->
	
	
	
	DELETE FROM HU_GOODS_ITEM WHERE HUGI_ID =#HUGI_ID#