<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.pj.project4sp.public4mapper.PublicMapper">

	<!-- ======================== 一些工具方法 ======================== -->
	
	<!-- 返回上一句SQL插入的自增主键值 -->
	<select id="getPrimarykey" resultType="long">
		SELECT @@identity 
	</select>
	
	
	
	<!-- ======================== 新增SQL相关 ======================== -->
	
	
	
	<!-- ======================== 删除SQL相关 ======================== -->
	
	<!-- 删除一条记录，指定表名，id -->
	<delete id="deleteById">
		delete from ${tableName} where id = #{id}
	</delete>
	
	<!-- 根据指定列指定值删除一条记录，// 参数: 表名、条件列表、条件列值  -->
	<delete id="deleteBy">
		delete from ${tableName} where ${whereName} = #{whereValue} 
	</delete>
	
	<!-- 删除一条记录，指定表名，id列表 -->
	<delete id="deleteByIds">
		delete from ${tableName} 
		where id in 
		<foreach collection="ids" item="id" open="(" separator="," close=")">
			#{id}
		</foreach>
	</delete>
	
	<!-- 根据指定列指定值删除多条记录  -->
	<delete id="deleteByWhereList">
		delete from ${tableName} 
		where ${whereName} in 
		<foreach collection="whereList" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
	</delete>
	
	
	<!-- ======================== 修改SQL相关 ======================== -->
	
	<!-- 指定表的指定字段加num（可以小于0 ）,根据指定id -->
	<update id="columnAdd">
		update ${tableName} set 
		${columnName} = IFNULL(${columnName}, 0) + #{num} 
		where id = #{id};
	</update>
	
	<!-- 指定表的指定字段增加指定值，可以为负值  -->
	<update id="columnAddByIds">
		update ${tableName} set 
		${columnName} = ${columnName} + #{num} 
		where id in
		<foreach collection="ids" item="id" open="(" separator="," close=")">
			#{id}
		</foreach>
	</update>

	<!-- 指定表的指定字段更新为指定值,根据指定id -->
	<update id="updateColumnById">
		update ${tableName} set 
		${columnName} = #{value} 
		where id = #{id} 
	</update>

	<!-- 指定表的指定字段更新为指定值,根据指定id列表 -->
	<update id="updateColumnByIds">
		update ${tableName} set 
		${columnName} = #{value} 
		where id in 
		<foreach collection="ids" item="id" open="(" separator="," close=")">
			#{id}
		</foreach>
	</update>

	<!-- 指定表的指定字段更新为指定值, 根据指定列的指定值 -->
	<update id="updateColumnBy">
		update ${tableName} set 
		${columnName} = #{columnValue} 
		where ${whereName} = #{whereValue} 
	</update>
	
	<!-- 指定表的指定字段SoMap集合更新为指定值,根据指定id   -->
	<update id="updateBySoMapById">
		update ${tableName} set 
		<foreach collection="soMap.entrySet()" item="value"  index="key" separator=",">
			${key} = #{value} 
		</foreach>
		where id = #{id} 
	</update>
	
	<!-- 指定表的指定字段SoMap集合更新为指定值,根据指定id   -->
	<update id="updateBySoMapBy">
		update ${tableName} set 
		<foreach collection="soMap.entrySet()" item="value"  index="key" separator=",">
			${key} = #{value}
		</foreach>
		where ${whereName} = #{whereValue} 
	</update>
	
	
	<!-- ======================== 查询SQL相关 ======================== -->

	<!-- 获取指定表的指定字段值,根据id值 -->
	<select id="getColumnById" resultType="String">
		select ${columnName} from ${tableName} 
		where id = #{id}
	</select>

	<!-- 获取指定表的指定字段值,并转化为long,根据id值  -->
	<select id="getColumnByIdToLong" resultType="long">
		select ${columnName} from ${tableName} 
		where id = #{id}
	</select>

	<!-- 获取指定表的指定字段值,根据指定条件(whereName=whereValue) -->
	<select id="getColumnByWhere" resultType="String">
		select ${columnName} from ${tableName} 
		where ${whereName} = #{whereValue} 
	</select>

	<!-- 获取指定表的id字段,根据指定条件(whereName=whereValue) -->
	<select id="getIdByWhere" resultType="long">
		select id from ${tableName} 
		where ${whereName} = #{whereValue} 
	</select>

	<!-- 获取指定表的指定字段值列表,并转化为long, 根据指定条件(whereName=whereValue) -->
	<select id="getColumnListToLongByWhere" resultType="long">
		select ${columnName} from ${tableName} 
		where ${whereName} = #{whereValue} 
	</select>

	<!-- 获取指定表的count数据,根据指定条件(whereName=whereValue) -->
	<select id="getCountBy" resultType="long">
		select count(*) from ${tableName}
		where ${whereName} = #{whereValue}
	</select>


	<!-- ======================== 查询集合SQL相关 ======================== -->

	<!-- 获取指定表的全部字段全部数据 -->
	<select id="getListMap" resultType="somap">
		select * from ${tableName} 
	</select>

	<!-- 获取指定表的全部字段全部数据转化为Map, 根据指定条件(whereName=whereValue) -->
	<select id="getListMapByWhere" resultType="somap">
		select * from ${tableName} 
		where ${whereName} = #{whereValue} 
	</select>

	<!-- 获取指定表的全部字段全部数据转化为Map, 根据指定条件(id=id) -->
	<select id="getListMapById" resultType="somap">
		select * from ${tableName} 
		where id = #{id} 
	</select>

	

</mapper>