<insert id=”insertByTrim”
parameterType=”com.jiyun.beans.Employee”>

sql,sqlserver2008

sql小语句总结(自娱自乐)

简单的sqlwhereif查询 <select id=”getListByWhere”
parameterType=”com.jiyun.beans.Employee” resultMap=”haha”> select *
from employee <where> <if test=”id != null and id != ””>
and id = #{id} </if> <if test=”name != null and name !=
””> and emp_name = #{name} </if> </where>
</select> sql—foreach语句 <select id=”getListByForeach”
parameterType=”list” resultMap=”haha”> select * from employee where
id in <foreach collection=”list” open=”(” close=”)” item=”a”
separator=”,”> #{a} </foreach> </select> sql—片段
<sql id=”single”> where id=#{id}; </sql> <select
id=”getEmployee” resultMap=”haha” parameterType=”int”> select * from
employee <include refid=”single”></include> </select>
sql—修改trim字段(作用最佳强劲) <update id=”updateByTrim”
parameterType=”com.jiyun.beans.Employee”> update employee <trim
prefix=”set” suffixOverrides=”,”> <if test=”name != null and name
!= ””> emp_name = #{name}, </if> <if test=”age != null
and age != ””> emp_age = #{age}, </if> </trim> where
id=#{id}; </update>   <insert id=”insertByTrim”
parameterType=”com.jiyun.beans.Employee”> insert into employee
<trim prefix=”(” suffix=”)” suffixOverrides=”,”> <if test=”name
!= null and name != ””> emp_name, </if> <if test=”age !=
null and age != ””> emp_age, </if> </trim> <trim
prefix=”values(” suffix=”)” suffixOverrides=”,”> <if test=”name !=
null and name != ””> #{name}, </if> <if test=”age != null
and age != ””> #{age}, </if> </trim> </insert>  
sql语句1对多多种(说白了能够将代码复制一下) <mapper
namespace=”com.jiyun.mapper.DeptMapper”> <!–
使用resultMap实行映射 –> <resultMap type=”com.jiyun.beans.Dept”
id=”haha”> <id property=”id” column=”id”/> <result
property=”deptName” column=”dept_name”/> <!–
要代表1对多,要求用到collection标签 –> <!–
property是集聚的名字,ofType是群集成分的泛型
column把dept和employee关联起来,column表示用一的1端哪个列去关联多的壹端
select写一条额外的sql语句,用有些mapper接口的艺术的全类名代表–>
<collection property=”list” ofType=”com.jiyun.beans.Staff”
column=”id”
select=”com.jiyun.mapper.StaffMapper.getStaffByDeptId”></collection>
</resultMap> <!– 依据id查询三个机构 –> <select
id=”getDeptById” parameterType=”int” resultMap=”haha”> select * from
dept where id = #{id}; </select> </mapper>   Select * From
offence_bean Where DATE_FORMAT(new_date,’%m-%d’) >= ’04-20′ and
DATE_FORMAT(new_date,’%m-%d’) <= ’04-21′   select * from
offence_bean where offence_city=’运城’ and new_date<‘2018-1-20′
and new_date>’2007-3-30’   select q.* from (SELECT a.*,money as m
FROM (SELECT * FROM customers ORDER BY money DESC)a)q where m = 666;

sql小语句总计( 自娱自乐 )
容易的sqlwhereif查询 select id=”getListByWhere”
parameterType=”com.jiyun.beans.Employee” resultMap=”haha”select *
fr…

and id = #{id}

<if test=”name != null and name != ””>

sql语句一对多连串(说白了能够将代码复制一下)

</if>

<trim prefix=”set” suffixOverrides=”,”>

emp_age = #{age},

</select>

</if>

<id property=”id” column=”id”/>

</trim>

<if test=”name != null and name != ””>

<!– 依照id查询3个机关 –>

</update>

 

</insert>

<select id=”getEmployee” resultMap=”haha” parameterType=”int”>

<collection property=”list” ofType=”com.jiyun.beans.Staff”
column=”id”
select=”com.jiyun.mapper.StaffMapper.getStaffByDeptId”></collection>

<if test=”age != null and age != ””>

sql—片段

<if test=”name != null and name != ””>

column把dept和employee关联起来,column表示用1的一端哪个列去关联多的一端

<if test=”age != null and age != ””>

</if>

<update id=”updateByTrim”
parameterType=”com.jiyun.beans.Employee”>

select * from employee where id in

</where>

<!– 要代表一对多,须求用到collection标签 –>

where id=#{id};

<resultMap type=”com.jiyun.beans.Dept” id=”haha”>

简单的sqlwhereif查询

<select id=”getListByWhere” parameterType=”com.jiyun.beans.Employee”
resultMap=”haha”>

</select>

select * from employee

</if>

<trim prefix=”values(” suffix=”)” suffixOverrides=”,”>

#{a}

</if>

<if test=”age != null and age != ””>

<select id=”getDeptById” parameterType=”int” resultMap=”haha”>

select q.* from (SELECT a.*,money as m FROM (SELECT * FROM customers
ORDER BY money DESC)a)q where m = 666;

and emp_name = #{name}

sql小语句总计(自娱自乐

图片 1