社区
学习
工具库
AI工具
休闲
搜索
简体中文
首页 数据库 mysql教程 IBATIS2.0映射文件 oracle/mysql 版实现示例，功能全面且丰富

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Jun 07, 2016 pm 02:58 PM
mysql oracle 文件 映射

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>
登录后复制
登录后复制
本站声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序，用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

显示更多

热门文章

R.E.P.O.能量晶体解释及其做什么（黄色晶体）
3 周前 By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳图形设置
3 周前 By 尊渡假赌尊渡假赌尊渡假赌
刺客信条阴影：贝壳谜语解决方案
1 周前 By DDD
R.E.P.O.如果您听不到任何人，如何修复音频
3 周前 By 尊渡假赌尊渡假赌尊渡假赌
在哪里可以找到原子中的起重机控制钥匙卡
1 周前 By DDD
显示更多

热工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

SublimeText3汉化版

SublimeText3汉化版

中文版，非常好用

禅工作室 13.0.1

禅工作室 13.0.1

功能强大的PHP集成开发环境

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

显示更多

热门话题

gmail邮箱登陆入口在哪里
7432
15
CakePHP 教程
1359
52
steam的账户名称是什么格式
76
11
win11激活密钥永久
29
19
显示更多
Related knowledge
mysql 主键可以为 null mysql 主键可以为 null Apr 08, 2025 pm 03:03 PM

MySQL 主键不可以为空，因为主键是唯一标识数据库中每一行的关键属性，如果主键可以为空，则无法唯一标识记录，将会导致数据混乱。使用自增整型列或 UUID 作为主键时，应考虑效率和空间占用等因素，选择合适的方案。

mysql 能返回 json 吗 mysql 能返回 json 吗 Apr 08, 2025 pm 03:09 PM

MySQL 可返回 JSON 数据。JSON_EXTRACT 函数可提取字段值。对于复杂查询，可考虑使用 WHERE 子句过滤 JSON 数据，但需注意其性能影响。MySQL 对 JSON 的支持在不断增强，建议关注最新版本及功能。

mysql 可以在 android 上运行吗 mysql 可以在 android 上运行吗 Apr 08, 2025 pm 05:03 PM

MySQL无法直接在Android上运行，但可以通过以下方法间接实现：使用轻量级数据库SQLite，由Android系统自带，无需单独服务器，资源占用小，非常适合移动设备应用。远程连接MySQL服务器，通过网络连接到远程服务器上的MySQL数据库进行数据读写，但存在网络依赖性强、安全性问题和服务器成本等缺点。

MySQL 中的查询优化对于提高数据库性能至关重要，尤其是在处理大型数据集时 MySQL 中的查询优化对于提高数据库性能至关重要，尤其是在处理大型数据集时 Apr 08, 2025 pm 07:12 PM

1.使用正确的索引索引通过减少扫描的数据量来加速数据检索select*fromemployeeswherelast_name='smith';如果多次查询表的某一列，则为该列创建索引如果您或您的应用根据条件需要来自多个列的数据，则创建复合索引2.避免选择*仅选择那些需要的列，如果您选择所有不需要的列，这只会消耗更多的服务器内存并导致服务器在高负载或频率时间下变慢例如，您的表包含诸如created_at和updated_at以及时间戳之类的列，然后避免选择*，因为它们在正常情况下不需要低效查询se

mysql 能处理多个连接吗 mysql 能处理多个连接吗 Apr 08, 2025 pm 03:51 PM

MySQL能处理多个并发连接，利用多线程/多进程为每个客户端请求分配独立执行环境，确保不受干扰。但并发连接数量受系统资源、MySQL配置、查询性能、存储引擎和网络环境影响。优化需要考虑代码层面（编写高效SQL）、配置层面（调整max_connections）、硬件层面（提升服务器配置）等多方面因素。

无法以 root 身份登录 mysql 无法以 root 身份登录 mysql Apr 08, 2025 pm 04:54 PM

无法以 root 身份登录 MySQL 的原因主要在于权限问题、配置文件错误、密码不符、socket 文件问题或防火墙拦截。解决方法包括：检查配置文件中 bind-address 参数是否正确配置。查看 root 用户权限是否被修改或删除，并进行重置。验证密码是否准确无误，包括大小写和特殊字符。检查 socket 文件权限设置和路径。检查防火墙是否阻止了 MySQL 服务器的连接。

mysql 是否要付费 mysql 是否要付费 Apr 08, 2025 pm 05:36 PM

MySQL 有免费的社区版和收费的企业版。社区版可免费使用和修改，但支持有限，适合稳定性要求不高、技术能力强的应用。企业版提供全面商业支持，适合需要稳定可靠、高性能数据库且愿意为支持买单的应用。选择版本时考虑的因素包括应用关键性、预算和技术技能。没有完美的选项，只有最合适的方案，需根据具体情况谨慎选择。

如何针对高负载应用程序优化 MySQL 性能？ 如何针对高负载应用程序优化 MySQL 性能？ Apr 08, 2025 pm 06:03 PM

MySQL数据库性能优化指南在资源密集型应用中，MySQL数据库扮演着至关重要的角色，负责管理海量事务。然而，随着应用规模的扩大，数据库性能瓶颈往往成为制约因素。本文将探讨一系列行之有效的MySQL性能优化策略，确保您的应用在高负载下依然保持高效响应。我们将结合实际案例，深入讲解索引、查询优化、数据库设计以及缓存等关键技术。1.数据库架构设计优化合理的数据库架构是MySQL性能优化的基石。以下是一些核心原则：选择合适的数据类型选择最小的、符合需求的数据类型，既能节省存储空间，又能提升数据处理速度

See all articles