Mybatis + Oracle CRUD

Columns

需要查询的列

Xml

代码语言：javascript复制

<sql id="ztTerminalProducerColumns">
	a.id AS "id",
	a.terminal_id AS "terminalId",
	a.name AS "name",
	a.link_man AS "linkMan",
	a.telphone AS "telphone",
	a.address AS "address",
	a.emailbox AS "emailbox",
	a.orgunitid AS "orgunitid",
	a.unifiedid AS "unifiedid",
	a.business_license AS "businessLicense",
	a.create_date AS "createDate",
	a.create_by AS "createBy.id",
	a.update_date AS "updateDate",
	a.update_by AS "updateBy.id",
	a.remarks AS "remarks",
	a.del_flag AS "delFlag"
</sql>

Joins

需要加入的表

Xml

代码语言：javascript复制

<sql id="ztTerminalProducerJoins">
</sql>

SELECT

get

获取单条记录

Xml

代码语言：javascript复制

<select id="get" resultType="ZtTerminalProducer" >
	SELECT 
		<include refid="ztTerminalProducerColumns"/>
	FROM zt_terminal_producer a
		<include refid="ztTerminalProducerJoins"/>
	WHERE a.id = #{id}
</select>

findList

获取多条记录

Xml

代码语言：javascript复制

<select id="findList" resultType="ZtTerminalProducer" >
	SELECT 
		<include refid="ztTerminalProducerColumns"/>
	FROM zt_terminal_producer a
		<include refid="ztTerminalProducerJoins"/>
	<where>
		a.del_flag = #{DEL_FLAG_NORMAL}
		<if test="terminalId != null and terminalId != ''">
			AND a.terminal_id = #{terminalId}
		</if>
		<if test="name != null and name != ''">
			AND a.name LIKE 
				<if test="dbName == 'oracle'">'%'||#{name}||'%'</if>
				<if test="dbName == 'mssql'">'%' #{name} '%'</if>
				<if test="dbName == 'mysql'">concat('%',#{name},'%')</if>
		</if>
	</where>
	<choose>
		<when test="page !=null and page.orderBy != null and page.orderBy != ''">
			ORDER BY ${page.orderBy}
		</when>
		<otherwise>
			ORDER BY a.update_date DESC
		</otherwise>
	</choose>
</select>

findAllList

获取多条记录

Xml

代码语言：javascript复制

<select id="findAllList" resultType="ZtTerminalProducer" >
	SELECT 
		<include refid="ztTerminalProducerColumns"/>
	FROM zt_terminal_producer a
	<include refid="ztTerminalProducerJoins"/>
	<where>
		a.del_flag = #{DEL_FLAG_NORMAL}
	</where>		
	<choose>
		<when test="page !=null and page.orderBy != null and page.orderBy != ''">
			ORDER BY ${page.orderBy}
		</when>
		<otherwise>
			ORDER BY a.update_date DESC
		</otherwise>
	</choose>
</select>

${}

${}可变参数，有注入风险，推荐#{}

Xml

代码语言：javascript复制

<!-- 根据实体名称和字段名称和字段值获取唯一记录 -->
<select id="findUniqueByProperty" resultType="ZtTerminalProducer" statementType="STATEMENT">
	select * FROM zt_terminal_producer  where ${propertyName} = '${value}'
</select>

INSERT

普通新增

Xml

代码语言：javascript复制

<insert id="insert">
	INSERT INTO zt_terminal_producer(
		id,
		terminal_id,
		name,
		link_man,
		telphone,
		address,
		emailbox,
		orgunitid,
		unifiedid,
		business_license,
		create_date,
		create_by,
		update_date,
		update_by,
		remarks,
		del_flag
	) VALUES (
		#{id},
		#{terminalId},
		#{name},
		#{linkMan},
		#{telphone},
		#{address},
		#{emailbox},
		#{orgunitid},
		#{unifiedid},
		#{businessLicense},
		#{createDate},
		#{createBy.id},
		#{updateDate},
		#{updateBy.id},
		#{remarks},
		#{delFlag}
	)
</insert>

insertBatch

批量新增

Java

代码语言：javascript复制

/**
 * 批量新增
 */
void insertBatch(@Param("ztTerminalProducerList") List<ZtTerminalProducer> ztTerminalProducerList);

Xml

代码语言：javascript复制

   <insert id="insertBatch" parameterType="com.jeeplus.modules.ztfx.entity.ZtTerminalProducer">
	INSERT INTO zt_terminal_producer(
		id,
		terminal_id,
		name,
		link_man,
		telphone,
		address,
		emailbox,
		orgunitid,
		unifiedid,
		business_license,
		create_date,
		create_by,
		update_date,
		update_by,
		remarks,
		del_flag
	)
	<foreach collection="ztTerminalProducerList" item="tp" separator="UNION ALL">
		SELECT
			#{tp.id},
			#{tp.terminalId},
			#{tp.name},
			#{tp.linkMan},
			#{tp.telphone},
			#{tp.address},
			#{tp.emailbox},
			#{tp.orgunitid},
			#{tp.unifiedid},
			#{tp.businessLicense},
			#{tp.createDate},
			#{tp.createBy.id},
			#{tp.updateDate},
			#{tp.updateBy.id},
			#{tp.remarks},
			#{tp.delFlag}
		FROM DUAL
	</foreach>
</insert>

UPDATE

普通更新

Xml

代码语言：javascript复制

   <update id="update">
	UPDATE zt_terminal_producer SET 	
		terminal_id = #{terminalId},
		name = #{name},
		link_man = #{linkMan},
		telphone = #{telphone},
		address = #{address},
		emailbox = #{emailbox},
		orgunitid = #{orgunitid},
		unifiedid = #{unifiedid},
		business_license = #{businessLicense},
		update_date = #{updateDate},
		update_by = #{updateBy.id},
		remarks = #{remarks}
	WHERE id = #{id}
</update>

updateBatch

批量更新 - 1

Java

代码语言：javascript复制

/**
 * 根据id批量更新is_check标识
 */
void updateIsCheckByIds(@Param("ids") List<String> ids);

Xml

代码语言：javascript复制

<!-- 批量更新 -->
<update id="updateIsCheckByIds">
	UPDATE zt_position_hist SET
		is_check = 1
	WHERE id in 
	<foreach collection="ids" item="id" open="(" separator="," close=")">
		#{id}
	</foreach>
</update>

批量更新 - 2

Java

代码语言：javascript复制

int updateNewStock(@Param("list") List<GreenBeanMsg> list);

Xml

代码语言：javascript复制

<update id="updateNewStock" parameterType="java.util.List">
    <foreach collection="list" item="bean" index="index" open="begin" close=";end;" separator=";">
        UPDATE green_beans
        <set>
            stock = #{bean.stock}
        </set>
        <where>
            beanUid = #{bean.beanUid}
        </where>
    </foreach>
</update>

注意：

open="begin" close=";end;"，如果不加这个，无法识别 ;

DELETE

物理删除或逻辑删除

Xml

代码语言：javascript复制

<!--物理删除-->
<update id="delete">
	DELETE FROM zt_terminal_producer
	WHERE id = #{id}
</update>

<!--逻辑删除-->
<update id="deleteByLogic">
	UPDATE zt_terminal_producer SET 
		del_flag = #{DEL_FLAG_DELETE}
	WHERE id = #{id}
</update>

xml java 批量计算 sql

0 人点赞