Gemeinschaft Lernen Tools-Bibliothek Freizeit
suchen
Deutsch
Heim > Datenbank > MySQL-Tutorial > Hauptteil

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

WBOY
Freigeben： 2016-06-07 14:58:01
Original
1211 Leute haben es durchsucht

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>
Nach dem Login kopieren
Nach dem Login kopieren
<?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>
Nach dem Login kopieren
Nach dem Login kopieren
Verwandte Etiketten：
mysql oracle 文件 映射
Quelle：php.cn
Vorheriger Artikel：MS SQL 数据分页(通用) Nächster Artikel：可以提取表的结构信息
Erklärung dieser Website
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
Neueste Artikel des Autors
Aktuelle Ausgaben
function_exists() kann die benutzerdefinierte Funktion nicht ermitteln Funktionstest () {Verwendung der Verwendung durch -Durch -Durch -Durch -Durch -Durch -Durc...
Aus 2024-04-29 11:01:01
0
2
2017
So zeigen Sie die mobile Version von Google Chrome an Hallo Lehrer, wie kann ich Google Chrome in eine mobile Version umwandeln?
Aus 2024-04-23 00:22:19
0
11
2186
Das untergeordnete Fenster bedient das übergeordnete Fenster, aber die Ausgabe antwortet nicht. Die ersten beiden Sätze sind ausführbar, der letzte Satz jedoch nicht.
Aus 2024-04-19 15:37:47
0
1
1842
Im übergeordneten Fenster erfolgt keine Ausgabe document.onclick = function(){ window.opener.document.write('Ich bin die Ausgabe des unter...
Aus 2024-04-18 23:52:34
0
1
1733
Wo gibt es die Kursunterlagen zum CSS-Mindmapping? Kursunterlagen
Aus 2024-04-16 10:10:18
0
0
1755
verwandte Themen
Mehr>
Beliebte Empfehlungen
Beliebte Tutorials
Mehr>
Verwandte Tutorials
Beliebte Empfehlungen
Aktuelle Kurse
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage