一、UUID
UUID 是指Universally Unique Identifier,翻译为中文是通用唯一识别码,UUID 的目的是让分布式系统中的所有元素都能有唯一的识别信息。如此一来,每个人都可以创建不与其它人冲突的 UUID,就不需考虑数据库创建时的名称重复问题。
UUID 是由一组32位数的16进制数字所构成,是故 UUID 理论上的总数为16^32=2^128,约等于3.4 x 10^123。
也就是说若每纳秒产生1百万个 UUID,要花100亿年才会将所有 UUID 用完
public class IDUtil {
public static String genId(){
return UUID.randomUUID().toString().replaceAll("-","");
}
}
二、动态sql
数据准备
1、数据库
CREATE TABLE `blog` (
`id` varchar(50) NOT NULL COMMENT '博客id',
`title` varchar(100) NOT NULL COMMENT '博客标题',
`author` varchar(30) NOT NULL COMMENT '博客作者',
`create_time` datetime NOT NULL COMMENT '创建时间',
`views` int(30) NOT NULL COMMENT '浏览量'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2、实体类
@Data
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;
private int views;
}
3、接口和Blogmapper.xml
public interface BlogMapper {
}
<?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">
<!--namespace需要绑定一个对应的DAO/Maper接口-->
<mapper namespace="com.jia.mapper.BlogMapper">
</mapper>
4、开启下划线驼峰自动转换mybatis-config.xml配置
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
5、插入部分数据
public class InitBlog {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setId(IDUtil.genId());
blog.setTitle("title2");
blog.setAuthor("aaa");
blog.setCreateTime(new Date());
blog.setViews(200);
mapper.addBlog(blog);
sqlSession.commit();
sqlSession.close();
}
}
6、普通查询
List<Blog> selectBlog(Map<String,Object> map);
<select id="selectBlog" parameterType="map" resultType="com.jia.pojo.Blog">
select * from Blog where title = #{title} and author = #{author}
</select>
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap<String, Object> map = new HashMap<>();
map.put("title","title2");
map.put("author","aaa");
List<Blog> blogs = mapper.selectBlog(map);
System.out.println(blogs);
7、动态查询——if
//接口
List<Blog> selectBlogD(Map<String,Object> map);
//mapper.xml
<select id="selectBlogD" parameterType="map" resultType="com.jia.pojo.Blog">
select * from Blog where 1=1
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>
//测试
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap<String, Object> map = new HashMap<>();
//
map.put("title","title2");
map.put("author","aaa");
List<Blog> blogsList = mapper.selectBlogD(map);
for (Blog blog : blogsList) {
System.out.println(blog);
}
}
8、choose、when、otherwise
<select id="selectBlogD" parameterType="map" resultType="com.jia.pojo.Blog">
select * from Blog
<where>
选择一个实现
<choose>
<when test="title!=null">
title = #{title}
</when>
<when test="author!=null">
author = #{author}
</when>
上面两个都不满足,实现otherwise
<otherwise>
views = #{views}
</otherwise>
</choose>
</where>
</select>
9、where(自动添加删除where、and和or)
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
<select id="selectBlogD" parameterType="map" resultType="com.jia.pojo.Blog">
select * from Blog
<where>
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
10、Set(自动添加set、删除额外的,)
set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号
<update id="updateBlog" parameterType="map">
update blog
<set>
<if test="title!=null">
title = #{title},
</if>
<if test="author!=null">
author = #{author}
</if>
</set>
<where>
<if test="id!=null">
id = #{id}
</if>
</where>
</update>
11、trim(定制where和set)
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
<trim prefix="SET" suffixOverrides=",">
...
</trim>
12、foreach(循环拼接)
List<Blog> selectBlogForeache(Map<String,Object> map);
<select id="selectBlogForeache" parameterType="map" resultType="com.jia.pojo.Blog">
select * from Blog
<where>
<foreach item="id" collection="ids" open="(" close=")" separator="or">
id = #{id}
</foreach>
</where>
</select>
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap<String, Object> map = new HashMap<>();
ArrayList<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(3);
ids.add(3);
map.put("ids",ids);
List<Blog> blogs = mapper.selectBlogForeache(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
}
13、sql片段(对一下共有的脚本片段进行抽取)
<sql id="select_or_title_or_author_othorwise_views">
<choose>
<when test="title!=null">
title = #{title}
</when>
<when test="author!=null">
author = #{author}
</when>
<otherwise>
views = #{views}
</otherwise>
</choose>
</sql>
使用include进行拼接
<select id="selectBlogD" parameterType="map" resultType="com.jia.pojo.Blog">
select * from Blog
<where>
<include refid="select_or_title_or_author_othorwise_views"></include>
</where>
</select>