mybatis动态sql
注意点:
- set 最后没有","
- 传入map 或者传入对象类型 都会自动映射
- foreach本质还是拼接sql语句
- 复杂的时候 if set这些(trime)也要结合特殊标记来使用
- 本案例中,出现create_time和createTime名称不一致的情况,教学视频使用
<setting name="mapUnderscoreToCamelCase" value="true"/>
自动识别驼峰命名,这里使用的是查询使用as,set使用 create_time=#{createTime},这些很灵活,只要知道每个东西就行
代码范例:
BlogMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.dao.BlogMapper">
<select id="getBlogList" resultType="Blog">
select id,tittle,author,create_time as createTime,views from blog
</select>
<select id="getBlogListIF" parameterType="map" resultType="Blog">
select id,tittle,author,create_time as createTime,views from blog
<where>
<if test="id!=null">
and id=#{id}
</if>
<if test="tittle!=null">
and tittle=#{tittle}
</if>
<if test="author!=null">
and author=#{author}
</if>
<if test="views!=null">
and views=#{views}
</if>
</where>
</select>
<update id="updateBlogSET" parameterType="map">
update blog
<set>
<if test="tittle != null">
tittle = #{tittle},
</if>
<if test="tittle != null">
tittle = #{tittle},
</if>
<if test="views != null">
views = #{views},
</if>
<if test="createTime != null">
create_time = #{createTime},
</if>
<if test="author != null">
author = #{author}
</if>
</set>
where id = #{id};
</update>
<update id="updateBlogSETByBlog" parameterType="Blog">
update blog
<set>
<if test="tittle != null">
tittle = #{tittle},
</if>
<if test="tittle != null">
tittle = #{tittle},
</if>
<if test="views != null">
views = #{views},
</if>
<if test="createTime != null">
create_time = #{createTime},
</if>
<if test="author != null">
author = #{author}
</if>
</set>
where id = #{id};
</update>
<select id="selectBlogForeach" parameterType="map" resultType="blog">
select * from blog
<where>
<!--
collection:指定输入对象中的集合属性
item:每次遍历生成的对象
open:开始遍历时的拼接字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串
select * from blog where 1=1 and (id=1 or id=2 or id=3)
-->
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id=#{id}
</foreach>
</where>
</select>
</mapper>
BlogMapper.java
package com.mybatis.dao;
import com.mybatis.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
public List<Blog> getBlogList();
public List<Blog> getBlogListIF(Map map);
public void updateBlogSET(Map map);
public void updateBlogSETByBlog(Blog blog);
public List<Blog> selectBlogForeach(Map map);
}
测试 MyTest.java
import com.mybatis.dao.BlogMapper;
import com.mybatis.pojo.Blog;
import org.apache.ibatis.session.SqlSession;
import utils.MybatisUtil;
import java.util.*;
public class MyTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
List<Blog> blogList = mapper.getBlogList();
// Map map = new HashMap();
// map.put("id","1");
// List<Blog> blogList = mapper.getBlogListIF(map);
//传入对象
// Blog blog = new Blog();
// blog.setId(1);
// blog.setAuthor("sunzhong");
// blog.setCreateTime(new Date());
// blog.setTittle("west blog");
// blog.setViews(123456);
// mapper.updateBlogSETByBlog(blog);
//传入map
// Map map = new HashMap();
// map.put("id",1);
// map.put("tittle","west blog");
// map.put("author","sunzhong");
// map.put("createTime",new Date());
// map.put("views",123456);
// mapper.updateBlogSET(map);
//for each
HashMap map = new HashMap();
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
ids.add(3);
map.put("ids",ids);
List<Blog> blogsList = mapper.selectBlogForeach(map);
for (Blog blog : blogList) {
System.out.println(blog);
}
sqlSession.commit();
sqlSession.close();
}
}
今天有些匆忙,出去玩了。 明天更新Mybatis缓存。