Mybatis介绍(一)
这里介绍的mybatis比较简单, 我做为一个初学者, 记录下个人在学习中方法, 如果那里出错, 希望读者朋友们见谅.
首先这里介绍一下我们下面用的表结构：
author表是保存了作者的个人信息, 因为我们在这里做测试, 所以就简单的定义几个字段.
blog表是保存谁写了博客的内容, 这里也是几个简单的字段.
comment表是保存对哪篇博客评论, 也是几个简单的字段.
注意: 这三张表的id都是自增型, 你也可以做其他的改变, 这里是为了方便.
下面给出了几张表格创建的sql语句:
CREATE TABLE `author` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(5) NULL DEFAULT NULL, `email` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `country` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `blog` ( `id` int(11) NOT NULL AUTO_INCREMENT, `authorid` int(11) NULL DEFAULT NULL, `title` varchar(35) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `mainbody` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL, `creattime` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `comment` ( `id` int(11) NOT NULL AUTO_INCREMENT, `blogid` int(11) NULL DEFAULT NULL, `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL, `creattime` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) );
使用maven+springmvc+mabatis+spring搭建web环境, 可以参考博客: https://www.cnblogs.com/jay36/p/7762448.html, 这里就不详细的介绍了.
这里主要介绍mybatis的用法, 首先使用Mybatis Generator生成pojo/dao/mapping三个文件, 即实体类、DAO接口和Mapping映射文件.
可以参考博客: https://blog.csdn.net/zhshulin/article/details/23912615, 下面就开始简单介绍Mybatis的使用.
1. 看一下三个表pojo(Plain Ordinary Java Object), 即实体类
package com.springdemo.pojo; public class Author { private Integer id; private String name; private Integer age; private String email; private String country; /* getting and setting function */ } package com.springdemo.pojo; public class Blog { private Integer id; private Integer authorid; private String title; private String creattime; private String mainbody; /* getting and setting function */ } package com.springdemo.pojo; public class Comment { private Integer id; private Integer blogid; private String creattime; private String content; /* getting and setting function */ }
2. 再看一下三个表的dao(Data Access Object), 即dao接口
package com.springdemo.dao public interface AuthorMapper { int deleteByPrimaryKey(Integer id); int insert(Author record); int insertSelective(Author record); Author selectByPrimaryKey(Integer id); int updateByPrimaryKeySelective(Author record); int updateByPrimaryKey(Author record); } package com.springdemo.dao public interface BlogMapper { int deleteByPrimaryKey(Integer id); int insert(Blog record); int insertSelective(Blog record); Blog selectByPrimaryKey(Integer id); int updateByPrimaryKeySelective(Blog record); int updateByPrimaryKeyWithBLOBs(Blog record); int updateByPrimaryKey(Blog record); } package com.springdemo.dao public interface CommentMapper { int deleteByPrimaryKey(Integer id); int insert(Comment record); int insertSelective(Comment record); Comment selectByPrimaryKey(Integer id); int updateByPrimaryKeySelective(Comment record); int updateByPrimaryKeyWithBLOBs(Comment record); int updateByPrimaryKey(Comment record); }
3. 再看一下三个表的mapping, 即Mapping映射
<?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.springdemo.dao.AuthorMapper" > <resultMap id="BaseResultMap" type="com.springdemo.pojo.Author" > <id column="id" property="id" jdbcType="INTEGER" /> <result column="name" property="name" jdbcType="VARCHAR" /> <result column="age" property="age" jdbcType="INTEGER" /> <result column="email" property="email" jdbcType="VARCHAR" /> <result column="country" property="country" jdbcType="VARCHAR" /> </resultMap> <sql id="Base_Column_List" > id, name, age, email, country </sql> <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" > select <include refid="Base_Column_List" /> from author where id = #{id,jdbcType=INTEGER} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" > delete from author where id = #{id,jdbcType=INTEGER} </delete> <insert id="insert" parameterType="com.springdemo.pojo.Author" > insert into author (id, name, age, email, country) values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}, #{email,jdbcType=VARCHAR}, #{country,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" parameterType="com.springdemo.pojo.Author" > insert into author <trim prefix="(" suffix=")" suffixOverrides="," > <if test="id != null" > id, </if> <if test="name != null" > name, </if> <if test="age != null" > age, </if> <if test="email != null" > email, </if> <if test="country != null" > country, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides="," > <if test="id != null" > #{id,jdbcType=INTEGER}, </if> <if test="name != null" > #{name,jdbcType=VARCHAR}, </if> <if test="age != null" > #{age,jdbcType=INTEGER}, </if> <if test="email != null" > #{email,jdbcType=VARCHAR}, </if> <if test="country != null" > #{country,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="com.springdemo.pojo.Author" > update author <set > <if test="name != null" > name = #{name,jdbcType=VARCHAR}, </if> <if test="age != null" > age = #{age,jdbcType=INTEGER}, </if> <if test="email != null" > email = #{email,jdbcType=VARCHAR}, </if> <if test="country != null" > country = #{country,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=INTEGER} </update> <update id="updateByPrimaryKey" parameterType="com.springdemo.pojo.Author" > update author set name = #{name,jdbcType=VARCHAR}, age = #{age,jdbcType=INTEGER}, email = #{email,jdbcType=VARCHAR}, country = #{country,jdbcType=VARCHAR} where id = #{id,jdbcType=INTEGER} </update> </mapper> <?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.springdemo.dao.BlogMapper" > <resultMap id="BaseResultMap" type="com.springdemo.pojo.Blog" > <id column="id" property="id" jdbcType="INTEGER" /> <result column="authorid" property="authorid" jdbcType="INTEGER" /> <result column="title" property="title" jdbcType="VARCHAR" /> <result column="creattime" property="creattime" jdbcType="VARCHAR" /> </resultMap> <resultMap id="ResultMapWithBLOBs" type="com.springdemo.pojo.Blog" extends="BaseResultMap" > <result column="mainbody" property="mainbody" jdbcType="LONGVARCHAR" /> </resultMap> <sql id="Base_Column_List" > id, authorid, title, creattime </sql> <sql id="Blob_Column_List" > mainbody </sql> <select id="selectByPrimaryKey" resultMap="ResultMapWithBLOBs" parameterType="java.lang.Integer" > select <include refid="Base_Column_List" /> , <include refid="Blob_Column_List" /> from blog where id = #{id,jdbcType=INTEGER} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" > delete from blog where id = #{id,jdbcType=INTEGER} </delete> <insert id="insert" parameterType="com.springdemo.pojo.Blog" > insert into blog (id, authorid, title, creattime, mainbody) values (#{id,jdbcType=INTEGER}, #{authorid,jdbcType=INTEGER}, #{title,jdbcType=VARCHAR}, #{creattime,jdbcType=VARCHAR}, #{mainbody,jdbcType=LONGVARCHAR}) </insert> <insert id="insertSelective" parameterType="com.springdemo.pojo.Blog" > insert into blog <trim prefix="(" suffix=")" suffixOverrides="," > <if test="id != null" > id, </if> <if test="authorid != null" > authorid, </if> <if test="title != null" > title, </if> <if test="creattime != null" > creattime, </if> <if test="mainbody != null" > mainbody, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides="," > <if test="id != null" > #{id,jdbcType=INTEGER}, </if> <if test="authorid != null" > #{authorid,jdbcType=INTEGER}, </if> <if test="title != null" > #{title,jdbcType=VARCHAR}, </if> <if test="creattime != null" > #{creattime,jdbcType=VARCHAR}, </if> <if test="mainbody != null" > #{mainbody,jdbcType=LONGVARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="com.springdemo.pojo.Blog" > update blog <set > <if test="authorid != null" > authorid = #{authorid,jdbcType=INTEGER}, </if> <if test="title != null" > title = #{title,jdbcType=VARCHAR}, </if> <if test="creattime != null" > creattime = #{creattime,jdbcType=VARCHAR}, </if> <if test="mainbody != null" > mainbody = #{mainbody,jdbcType=LONGVARCHAR}, </if> </set> where id = #{id,jdbcType=INTEGER} </update> <update id="updateByPrimaryKeyWithBLOBs" parameterType="com.springdemo.pojo.Blog" > update blog set authorid = #{authorid,jdbcType=INTEGER}, title = #{title,jdbcType=VARCHAR}, creattime = #{creattime,jdbcType=VARCHAR}, mainbody = #{mainbody,jdbcType=LONGVARCHAR} where id = #{id,jdbcType=INTEGER} </update> <update id="updateByPrimaryKey" parameterType="com.springdemo.pojo.Blog" > update blog set authorid = #{authorid,jdbcType=INTEGER}, title = #{title,jdbcType=VARCHAR}, creattime = #{creattime,jdbcType=VARCHAR} where id = #{id,jdbcType=INTEGER} </update> </mapper> <?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.springdemo.dao.CommentMapper" > <resultMap id="BaseResultMap" type="com.springdemo.pojo.Comment" > <id column="id" property="id" jdbcType="INTEGER" /> <result column="blogid" property="blogid" jdbcType="INTEGER" /> <result column="creattime" property="creattime" jdbcType="VARCHAR" /> </resultMap> <resultMap id="ResultMapWithBLOBs" type="com.springdemo.pojo.Comment" extends="BaseResultMap" > <result column="content" property="content" jdbcType="LONGVARCHAR" /> </resultMap> <sql id="Base_Column_List" > id, blogid, creattime </sql> <sql id="Blob_Column_List" > content </sql> <select id="selectByPrimaryKey" resultMap="ResultMapWithBLOBs" parameterType="java.lang.Integer" > select <include refid="Base_Column_List" /> , <include refid="Blob_Column_List" /> from comment where id = #{id,jdbcType=INTEGER} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" > delete from comment where id = #{id,jdbcType=INTEGER} </delete> <insert id="insert" parameterType="com.springdemo.pojo.Comment" > insert into comment (id, blogid, creattime, content) values (#{id,jdbcType=INTEGER}, #{blogid,jdbcType=INTEGER}, #{creattime,jdbcType=VARCHAR}, #{content,jdbcType=LONGVARCHAR}) </insert> <insert id="insertSelective" parameterType="com.springdemo.pojo.Comment" > insert into comment <trim prefix="(" suffix=")" suffixOverrides="," > <if test="id != null" > id, </if> <if test="blogid != null" > blogid, </if> <if test="creattime != null" > creattime, </if> <if test="content != null" > content, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides="," > <if test="id != null" > #{id,jdbcType=INTEGER}, </if> <if test="blogid != null" > #{blogid,jdbcType=INTEGER}, </if> <if test="creattime != null" > #{creattime,jdbcType=VARCHAR}, </if> <if test="content != null" > #{content,jdbcType=LONGVARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="com.springdemo.pojo.Comment" > update comment <set > <if test="blogid != null" > blogid = #{blogid,jdbcType=INTEGER}, </if> <if test="creattime != null" > creattime = #{creattime,jdbcType=VARCHAR}, </if> <if test="content != null" > content = #{content,jdbcType=LONGVARCHAR}, </if> </set> where id = #{id,jdbcType=INTEGER} </update> <update id="updateByPrimaryKeyWithBLOBs" parameterType="com.springdemo.pojo.Comment" > update comment set blogid = #{blogid,jdbcType=INTEGER}, creattime = #{creattime,jdbcType=VARCHAR}, content = #{content,jdbcType=LONGVARCHAR} where id = #{id,jdbcType=INTEGER} </update> <update id="updateByPrimaryKey" parameterType="com.springdemo.pojo.Comment" > update comment set blogid = #{blogid,jdbcType=INTEGER}, creattime = #{creattime,jdbcType=VARCHAR} where id = #{id,jdbcType=INTEGER} </update> </mapper>
mybatis的使用, 关键在于要要写好mapping映射, 使用工具生成的是最简单的curd的方法, 现在我们来增加一个我们自定义的方法.
首先在mapping的文件中写一个映射, 我们就在BlogMapper.xml文件中写,代码如下:
<?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.springdemo.dao.BlogMapper" > ...... <insert id="insertBlogByList" useGeneratedKeys="true" keyProperty="id" parameterType="java.util.List"> insert into blog (authorid, title, creattime, mainbody) values <foreach item="item" collection="list" separator=","> (#{item.authorid,jdbcType=INTEGER}, #{item.title,jdbcType=VARCHAR}, #{item.creattime,jdbcType=VARCHAR}, #{item.mainbody,jdbcType=LONGVARCHAR}) </foreach> </insert> ...... </mapper>
这段xml的代码主要就是批量插入数据, 而且id是自增长的, 数据以list的形式传入进来, 然后循环插入到blog表中.
然后我们要在BlogMapper.java的dao中加一个接口, 代码如下:
package com.springdemo.dao; public interface BlogMapper { ...... int insertBlogByList(List<Blog> listBlog); ...... }
注意这里的接口名字要和我们之前在xml中加入的id值相同才行, 因为这样mybatis才会映射.
即在BlogMapper.xml中写的insert语句id是insertBlogByList,
那么这里的定义的接口必须也是这个, 而且参数的类型也必须一样, 这一点切记。
最后我们要在controller中去调用(因为这里我是用springmvc写的,所有用mvc思想去写调用,你也可以随便写一个方法去调用, 这里给出我的调用方法):
//下面是BlogController.java的代码 package com.springdemo.controller; @Controller @RequestMapping(value = "/blog") public class BlogController { private static final Logger LOG = LogManager.getLogger(); @Autowired private BlogService blogService; //这里是把前端的值获取到,然后再存入到数据库中, 最后返回状态 @RequestMapping(value="/insert", method = RequestMethod.POST) public void insertBlog(HttpServletRequest request, HttpServletResponse response) { String status = "{\"status\":\"failure\"}"; int authorid = Integer.parseInt(request.getParameter("authorid")); String title = String.valueOf(request.getParameter("title")); String mainbody = String.valueOf(request.getParameter("mainbody")); SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date now = new Date(); String creattime = df.format(now); List<Blog> listBlog = new ArrayList<Blog>(); Blog blog = new Blog(); blog.setAuthorid(authorid); blog.setTitle(title); blog.setMainbody(mainbody); blog.setCreattime(creattime); listBlog.add(blog); int affectRow = blogService.insertBlogByList(listBlog); if (affectRow != 0) { status = "{\"status\":\"success\"}"; } response.setContentType("text/html;UTF-8"); try (PrintWriter writer = response.getWriter();) { writer.write(status); writer.flush(); } catch (IOException e) { LOG.error(e.getMessage(), e); } } } ---------------------------------------------------------- // 下面是BlogService.java的代码 package com.springdemo.service; import java.util.List;
// 这里不需要继承BlogMapper接口, 通过spring注解直接使用 @Service(value="blogService") public class BlogService { @Resource private BlogMapper blogMapper; public int insertBlogByList(List<Blog> listBlog) { int affectRow = 0; affectRow = this.blogMapper.insertBlogByList(listBlog); return affectRow; } }
这样就简单的完成了在xml中建立sql语句, 然后通过映射转换到java中, 最后去执行.
待续......
努力做一个伪程序员, 不管道路有多么的艰苦......
