我的博客(YeBlog)后端使用的ORM框架是Mybatis,因为博客的逻辑不复杂,使用Spring data JPA开发会更高效一点。不过对于自己写着玩的项目来说,开发效率也没有那么重要了。
Mybatis的使用方式
Mybatis的使用方式一般有两种,一种是基于注解,一种是基于XML.就我个人而言,更喜欢基于注解。但是XML也有它的优势.
- 基于注解
- 可以很方便地写SQL, 不需要额外维护一个XML文件,少了XML繁杂的标签语法
- 可以快速地对表进行逻辑关联
- 在涉及到判断和集合遍历的时候要借助script标签来写跟XML一样的SQL
- 基于XML
- Mapper接口类更简洁,查看接口时更直观
- 需要维护接口类和实现接口方法的XML
基于注解使用Mybatis
操作SQL的四个基本注解
- @Insert 插入
可以配合使用 @SelectKey 来获取插入的ID,在使用自增ID的时候很管用
@Insert("insert into t_label(name, create_time) values (#{name}, NOW())")
@SelectKey(statement = "SELECT LAST_INSERT_ID()", before = false, keyProperty = "id", resultType = int.class)
int add(Label label);
- @Delete 删除
@Delete("delete from t_label where id = #{id}")
int deleteById(Integer id);
- @Update 更新
@Update("update t_article set title = #{title}, abstract_text = #{abstractText}, keywords = #{keywords}, category_id = #{categoryId}, update_time = now() " +
"where id = #{id}")
int updateArticle(Article article);
- @Select 查询
@Select("select * from t_article_comment where id = #{id}")
Comment getById(Integer id);
script标签拼接复杂SQL
如果你做一些批量操作或者判断,就需要用到script标签
@Update("<script>" +
"update t_article_comment set status = #{status} shere id IN (" +
"<foreach collection='ids' item='id' index='index' separator=','>" +
"#{id}" +
"</foreach>" +
")" +
"</script>")
int batchUpdateStatus(List<Integer> ids, Integer status);
@Result对查询结果进行逻辑关联
看一个例子
@Select("select id, title, category_id, views, status, abstract_text, keywords, update_time from t_article where id = #{id}")
@Results(id="ArticleHtml", value = {
@Result(column = "id", property = "id", id = true),
@Result(column = "id", property = "contentHtml", javaType = String.class, one = @One(select = "com.yeyeck.yeblog.mapper.ArticleContentMapper.getHtmlById")),
@Result(column = "id", property = "labels", javaType = List.class, many = @Many(select = "com.yeyeck.yeblog.mapper.LabelMapper.getByArticleId")),
@Result(column = "id", property = "countComments", javaType = Integer.class, one = @One(select = "com.yeyeck.yeblog.mapper.CommentMapper.countCommentsByArticleId")),
@Result(column = "category_id", property = "categoryId"),
@Result(column = "category_id", property = "category", javaType = String.class, one = @One(select = "com.yeyeck.yeblog.mapper.CategoryMapper.getNameById"))
})
Article getArticleHtmlById(Integer id);
- 首先看一下我的Article类
// Article.java
import lombok.Data;
import java.util.List;
@Data
public class Article extends BasePojo {
private Integer categoryId; // 类目id
private String title; // 文章标题
private String abstractText; // 摘要
private String keywords; // SEO 关键字
private String contentMd; // 文章内容 MD格式
private String contentHtml; // 文章内容 HTML格式
private Integer views; // 浏览量
private Integer status; // 文章状态, 1: 已发布; 0:未发布(草稿)
private Integer countComments; // 评论量
private List<Comment> comments; // 评论列表
private List<Label> labels; // 标签列表
private List<Integer> labelIds; // 标签 id
private String category;
}
// BasePojo.java
import lombok.Data;
import java.util.Date;
@Data
public class BasePojo {
private int id; // 表主键
private Date createTime; // 记录插入时间
private Date updateTime; // 最后更新时间
}
在这个Article类中,id,createTime,updateTime,categoryId,title,abstractText,keywords,views,status是在同一张表t_article的。其他的字段都在不同的表里,比如Label在t_label,category在t_category,contentHtml和contentMd 在t_article_content里。
所以我们需要在代码层面上建立逻辑关联,这就是@Result的作用
以下面一条为例
@Result(column = "id", property = "contentHtml", javaType = String.class, one = @One(select = "com.yeyeck.yeblog.mapper.ArticleContentMapper.getHtmlById"))
这个注解的意思是,需要使用的关联数据是id,调用的方法是com.yeyeck.yeblog.mapper.ArticleContentMapper.getHtmlById,使用id的值作为调用该方法的参数,one=@One表示是一对一的关系
再看一个例子
@Result(column = "id", property = "labels", javaType = List.class, many = @Many(select = "com.yeyeck.yeblog.mapper.LabelMapper.getByArticleId"))
这里使用的many=@Many表示这是一个一对多的关系,返回结果是List
#{value}和${value}的区别
- #{value}表示这是一个PreparedStatement的SQL参数
- ${value}表示这就是一个字符串拼接
@Select("select id, title, views, abstract_text, update_time, create_time " +
"from t_article where status = 1 AND ${filterName} = #{filterValue} order by ${orderParam} ${orderType} limit #{start}, #{countPerPage}")
@Results(id="abstractArticle", value = {
@Result(column = "id", property = "id", id = true),
@Result(column = "id", property = "countComments", javaType = Integer.class, one = @One(select = "com.yeyeck.yeblog.mapper.CommentMapper.countCommentsByArticleId")),
@Result(column = "id", property = "labels", javaType = List.class, many = @Many(select = "com.yeyeck.yeblog.mapper.LabelMapper.getByArticleId"))
})
List<Article> selectArticlesForPage(Page<Article> page);
这里的将数据表字段名作为参数传到SQL里,但它不是PreparedStatement里替代’?'的,所以使用的是${}
@Select("select id, title, views, abstract_text, update_time, create_time " +
"from t_article where status = 1 AND ${filterName} = #{filterValue} order by ${orderParam} ${orderType} limit #{start}, #{countPerPage}")