Community Learn Tools Library Leisure
search
English
Home > Database > Mysql Tutorial > IBATIS2.0映射文件 oracle/mysql 版实现示例，功能全面且丰富

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

WBOY
Release： 2016-06-07 14:58:01
Original
1218 people have browsed it

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>
Copy after login
Copy after login
<?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>
Copy after login
Copy after login
Related labels：
mysql oracle document mapping
source：php.cn
Previous article：MS SQL 数据分页(通用) Next article：可以提取表的结构信息
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
function_exists() cannot determine the custom function Function test () {return true;} if (function_exists ('test')) {echo "test is function...
From 2024-04-29 11:01:01
0
3
2047
How to display the mobile version of Google Chrome Hello teacher, how can I change Google Chrome into a mobile version?
From 2024-04-23 00:22:19
0
11
2203
The child window operates the parent window, but the output does not respond. The first two sentences are executable, but the last sentence cannot be implemented.
From 2024-04-19 15:37:47
0
1
1861
There is no output in the parent window document.onclick = function(){ window.opener.document.write('I am the output of the child ...
From 2024-04-18 23:52:34
0
1
1746
Where is the courseware about CSS mind mapping? Courseware
From 2024-04-16 10:10:18
0
0
1768
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template