第七章续版，mapper.xml，这是重点，一对多，一对一，动态SQL，foreach...

时间：2024-12-04 08:26:03

mapper,mapper.xml

1.单表查询

<select id="接口名" parameterType="参数类型，单个参数时写对应类型，两个及以上用Map" resultType="返回值类型，查询必须写">
	这里写SQL语句
</select>
Map时测试类写法
Map<String, Object> map = new HashMap<>();
        map.put("goodsName", "牛奶");
        map.put("id", 1);
Goods goods = goodsMapper.selectGoodsByNameAndId(map);

1.查询

 User getUserById(int id);
   <select id="getUserById" parameterType="int" resultType="com.hz.pojo.User">
        select id, name, pwd from `user` where id = #{id} limit 1
    </select>
2.新增
int addUser(User user);
<insert id="addUser" parameterType="com.hz.pojo.User">
        insert into `user`(id, name, pwd) values (#{id},#{name},#{pwd})
    </insert>

3.删除
int deleteUser(int id);
<delete id="deleteUser" parameterType="int">
        delete from `user` where id = #{id}
    </delete>
4.修改
 int updateUser(User user);  
 <update id="updateUser" parameterType="com.hz.pojo.User">
        update `user` set name=#{name},pwd=#{pwd} where id = #{id}
    </update>

Map

接口
 int addUser2(Map<String, Object> map);
 实现
  <insert id="addUser2" parameterType="map">
        insert into `user`(id, name, pwd) values (#{userId},#{userName},#{password})
    </insert>
测试
        @Test
    public void addUser2() {
        SqlSession sqlSession = null;
        try {
            Map<String, Object> map = new HashMap<>();
            map.put("userId", 5);
            map.put("userName", "hz");
            map.put("password", "123456");

            sqlSession = MybatisUtils.getSqlSession();
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            int num = userMapper.addUser2(map);
            if (num > 0) {
                System.out.println("插入成功！");
            }
            sqlSession.commit();
        } finally {
            if (sqlSession != null) {
                sqlSession.close();
            }
        }
    }

2.1一对一

Mapper.xml里内容
<resultMap id="随意起，后面用到注意保持一致" type="用到的实体类的包名到类名">
	<result property="实体类的列名" column="查询语句返回的列的名字"></result>
	<association property="这里是将类当作属性的对应属性名" javaType="对应实体类的包名到类名">
		<result property="实体类的列名" column="查询语句返回的列的名字"></result>
	</association >
</resultMap>
<select id="这是mapper里需要绑定的方法名" resultMap="上面随意起的id名">
	这里写SQL语句
</select>

2.2一对多


<resultMap id="随意起，后面用到注意保持一致" type="用到的实体类的包名到类名">
	<result property="实体类的列名" column="查询语句返回的列的名字"></result>
	<collection property="这里是将类当作属性的对应属性名"ofType="对应实体类的包名到类名">
		<result property="实体类的列名" column="查询语句返回的列的名字"></result>
	</collection >
</resultMap>
<select id="这是mapper里需要绑定的方法名" resultMap="上面随意起的id名">
	这里写SQL语句
</select>
另一种写法
<resultMap id="myMap" type="用到的类的包名到类名">
	<result property="与实体类对应" column="跟查询结果返回的列对应"/>
</resultMap>

<resultMap id="usedMap" type="用到的类的包名到类名">
	<result property="与实体类对应" column="跟查询结果返回的列对应"/>
	<collection property="被当作属性放在实体类的属性名" ofType="myMap用到的实体类名" tesultMap="myMap"/>
</resultMap>
<select id="这是mapper里需要绑定的方法名" resultMap="usedMap">
	这里写SQL语句
</select>

3.1自增主键

useGeneratedKeys="true" keyProperty="id"
获取只需要在调用方法时用对象点属性即可

4.动态sql

4.1查询
<selsct id="方法名" resultType="返回值类型">
	select * from 
	<where>
		<if test="name!=null and name!='' ">
			and name=#{name}
		</if>
	</where>

</select>
4.2修改
第一种trim实现
<update id="方法名" parameterType="参数类型">
	update 表名 
	<trim prefix="set" suffixOverrides=",">
		<if test="name!=null and name!='' ">
			name=#{name},
		</if>
		<if test="hobby!=null and hobby!='' ">
			hobby=#{hobby},
		</if>
	</trim>
	where id=#{id}
</update >
第二种set实现
<update id="接口名" parameterType="参数类型">
	update 表名
	<set>
		<if test="name!=null and name!='' ">
			name=#{name},
		</if>
	</set>
	where id=#{id}
</update>
4.3新增
<insert id="接口名" parameterType="参数类型，包名到类名">
	insert into 表名
	<trim prefix="(" suffix=")" suffixOverrider=",">
		<if test="name!=null and name!='' ">
			name,
		</if>
	<trim>
		values
	<trim prefix="(" suffix=")" suffixOverrider=",">
		<if test="name!=null and name!='' ">
			#{name},
		</if>
	</trim>
</insert>

SQL片段

将一些功能的部分抽取出来，方便复用

<!-- 1.创建模板 -->
<sql id="customSql">
        select id,customerName,birthday,phone,level,sum
</sql>
<select id="selectCustom" resultType="bean.Custom">
<!-- 2.使用模板 -->
        <include refid="customSql"/> from custom
        <where>
            <if test="customerName !=null and customerName!=''">
                and customerName like concat('%',#{customerName},'%')
            </if>
            <if test="phone !=null and phone!=''">
            and phone like concat('%',#{phone},'%')
            </if>
        </where>
</select>
foreach
<!-- 定义接口 -->
 List<Custom> selectCustomByIds(@Param("ids")List<Integer> ids);
<!-- mapper.xml -->
<sql id="customSql">
        select id,customerName,birthday,phone,level,sum
    </sql>
<select id="selectCustomByIds" resultType="bean.Custom">
        <include refid="customSql"></include>
        from custom where id in
        <!-- 下面的意思是将参数ids每次拿出一个放在cusId，以'（'开始遍历结束后以'）'结束 -->
        <foreach collection="ids" item="cusId" open="(" close=")" separator=",">
            #{cusId}
        </foreach>
    </select>
//当参数是对象时
int addListCustom(@Param("Listcustom") List<Custom> Listcustom);
    <insert id="addListCustom">
        insert into custom(customerName,phone) values
        <foreach collection="Listcustom" item="cus"  separator=",">
            (#{cus.customerName},#{cus.phone})
        </foreach>
    </insert>

choose

<!--  满足name后即使phone满足也不会拼在sql上，执行的只会是其中一个，都不满足执行otherwise-->
    <select id="selectCustomChoose" resultType="com.j4061.springmybatis.bean.Custom">
        <include refid="customSql"></include>
        from custom
        <where>
            <choose>
                <when test="customerName !=null and customerName!=''">
                    and customerName like concat('%',#{customerName},'%')
                </when>
                <when test="phone !=null and phone!=''">
                    and phone like concat('%',#{phone},'%')
                </when>
                <otherwise>
                    and 1=1
                </otherwise>
            </choose>
        </where>
    </select>

相关文章