欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mybatis多条件查询及动态sql

程序员文章站 2024-03-06 08:09:49
...

看代码,项目见码云

<?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.xdl.dao.XdlUserMapper">


	<!--sql片段,作用是被引用-->
	<sql id="xdlUser">select * from user1</sql>
	<select id="findAll" resultType="com.xdl.entity.XdlUser">
	/*	使用include标签引用*/
		<include refid="xdlUser"></include>
	</select>

	<select id="findByCondit" resultType="com.xdl.entity.XdlUser">
		select * from user1
		where
		<choose>
			<when test="id != null">
				id = #{id}
			</when>
			<when test="username != null and username!= ''">
				username = #{username}
			</when>
			<otherwise>
				passwd = #{passwd}
			</otherwise>
		</choose>

		<!--<trim prefix="where" suffixOverrides="and">
			<if test="id != null">
				 id = #{id} and
			</if>

			<if test="username != null">
				username = #{username} and
			</if>

			<if test="passwd != null">
				passwd = #{passwd}  and
			</if>
		</trim>-->

	</select>

	<update id="update" parameterType="com.xdl.entity.XdlUser">
		update user1
		<trim prefix="set" suffixOverrides=",">

			<if test="username != null">
				username = #{username} ,
			</if>

			<if test="passwd != null">
				passwd = #{passwd}
			</if>
		</trim>
		<where>
			<if test="id != null">
				id = #{id}
			</if>
		</where>
	</update>

	<insert id="add" parameterType="com.xdl.entity.XdlUser" useGeneratedKeys="true" keyProperty="id">
		insert into user1(username,passwd) values (
        <choose>
			<when test="passwd == '333'">'man'</when>
			<when test="passwd == '222'">'女'</when>
			<otherwise>'not all'</otherwise>
		</choose>
		)
	</insert>

	<delete id="delMore" >
		delete from user1 where id in (${value })
	</delete>

	<select id="findByList" resultType="com.xdl.entity.XdlUser">
		select * from user1 where
		/*如果传入是集合则collection里添list,数组则array*/
		<foreach collection="array" item="id" open="(" close=")" separator="or">
			id = #{id}
		</foreach>
	</select>

	<delete id="delByList">
		delete from user1 where id in
		<foreach collection="array" item="id" open="(" close=")" separator=",">
			#{id}
		</foreach>
	</delete>

	<insert id="insertUser">
		insert into user1 values
		<foreach collection="array" item="user" separator=",">
			(null,#{user.username},#{user.passwd})
		</foreach>
	</insert>

	<update id="updateMore">
		<foreach collection="array" item="user">
			/*注意update操作时,一定要在jdbc后面加allowMultiQueries=true,即jdbc:mysql://localhost:3306/test?allowMultiQueries=true*/
			update user1 set username = #{user.username},passwd = #{user.passwd} where id = #{user.id};
		</foreach>
	</update>
</mapper>

相关标签: 学习 mysql