mybatis入门

xml映射

<insert id="insertAuthor">
  insert into Author (id,username,password,email,bio)
  values (#{id},#{username},#{password},#{email},#{bio})
</insert>
 
<update id="updateAuthor">
  update Author set
    username = #{username},
    password = #{password},
    email = #{email},
    bio = #{bio}
  where id = #{id}
</update>
 
<delete id="deleteAuthor">
  delete from Author where id = #{id}
</delete>
 

结果集配置

• mapper

User selectUserById(int id);
 

• xml

<!--用于实体类属性和表中字段不一致时-->
<resultMap type="user" id="userMap">
        <id column="id" property="id"/>
        <result column="age" property="age"/>
        <result column="user_name" property="userName"/>
        <result column="sex" property="sex"/>
        <result column="birthday" property="birthday"/>
</resultMap>
 
<select id="selectUserById" parameterType="java.lang.Integer" resultMap="userMap" >
        select * from t_user where id = #{id}
</select>
 

动态sql

//符合条件则拼接上if标签的sql语句
<if test="title != null"></if>
 
//switch case
<choose>
  <!--符合条件则会拼接上when标签中的语句-->
  <when test="title != null"></when>
  <!--不需要条件都会拼接上该标签中的语句-->
  <otherwise></otherwise>
</choose>
 
//where会在符合条件时自动添加上where 并去除子句中多余的 and 或 or
<where>
  <if test="title != null">
    AND title like #{title}
  </if>
</where>
 
//set可用于动态包含需要更新的列，忽略其它不更新的列
update Author
   <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
   </set>
where id=#{id}
 
//该标签自定义 添加的sql语句 过滤的sql语句；如下表示在需要时会自动在sql语句中添加set
<trim prefix="SET" suffixOverrides=",">
  ...
</trim>
 
//foreach对集合进行遍历
<insert id="add">
  insert into t_user(user_name, sex, age) values
    <!--批量插入数据时，该标签指定 集合  遍历对象 去除多余分隔符-->
  <foreach collection="list" item="u"  separator=",">
    (#{u.userName}, #{u.sex}, #{u.age})
  </foreach>
</insert>
 
//sql可以用来定义可重用的 SQL 代码片段，以便在其它语句中使用。 参数可以静态地（在加载的时候）确定下来，并且可以在不同的 include 元素中定义不同的参数值
<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>
//使用时在xml映射sql中添加该id的标签即可重用代码片段
<include refid="userColumns">
 

视图封装

在视图层vo文件夹下编写封装类

• 例如封装用户表和博客表为BlogUserVo
  
  

public class BlogUserVO {
	private Integer uId;
	private String phone;
	private String password;
	private String nickname;
	private String headImg;
	private String personalSign;
	private Integer levelId;
	private Integer bId;  // 主键
	private String blogType;
	private String blogTitle;
	private String blogPic;
	private String blogContent;
	private Date createTime;
	private Date updateTime;
	private String isDel;
	private String isTop;
	private String status;
	private String blogPreface;	
        /**
         * getter setter
         */
 
}
 

• mapper

/**
 * 根据用户获取博客
 * @param bup   
 * BlogUserParam类型是视图层vo中封装的数据类型
 */
List<BlogUserVO> getUserBlog(BlogUserParam bup) throws MapperException;
 

• 映射文件

<!--根据用户获取博客  传入的参数类型和返回的参数类型 为视图层封装的类型-->
<select id="getUserBlog"
	parameterType="top.yfly.blog.vo.BlogUserParam"
	resultType="top.yfly.blog.vo.BlogUserVO">
	select
	<include refid="selbu"></include>
	from t_blog b,
	t_user_info ui, t_user u
	where b.user_id = ui.user_id
	and
	b.user_id = u.id and ui.user_id = u.id
	and b.user_id = #{userId}
	<if test="blogType != null">
		and b.blog_type = #{blogType}
	</if>
	<if test="blogTitle != null">
		and b.blog_tile like concat('%',#{blogTitle} ,'%')
	</if>
	<if test="inputTime != null">
		order by b.create_time
	</if>
	<if test="praisedCount != null">
		order by ui.praised_count desc
	</if>
	<if test="accessCount != null">
		order by ui.access_count desc
	</if>
	<if test="pageNum !=null and pageSize != null">
		limit #{pageNum}, #{pageSize}
	</if>
</select>
 

映射注解

@Insert("insert into t_user(user_name,password, sex, age, birthday, head_url), value(#{userName}, #{password}, #{sex}, #{age}, #{birthday}, #{headUrl})")
int add(User user);
 
@UpdateProvider(type = UserProvider.class, method = "update")
int update(User user);
 
@Delete("delete from t_user where id=#{id}")
int del(int id);
 
@Select("select * from t_user where id=#{id}")
User getOne(int id);
 

sql语句构建器

@SelectProvider(value = BlogMapperProvide.class, method = "getPage")
List<Blog> getPage(@Param("title")String title, @Param("page")int page, @Param("size")int size);
/**
 * 复杂sql语句的构建器 
 * 方式一：内部类
 */
class BlogMapperProvider {
	
	public static String getPage(@Param("title")String title, @Param("page")int page, @Param("size")int size) {
		return new SQL() {{
			SELECT("*");
			FROM("blog");
			if(title != null) {
				WHERE("title like concat('%', #{title} , '%')");
			}
			if(page > 0 && size > 0) {
				LIMIT("#{page}, #{size}");
			}
		}}.toString();
	}
}
 

• 或者新建一个外部类进行构建

/**
 * 方式二：外部公开的类
 */
public class BlogMapperProvide {
 
	public String getPage(@Param("title")String title, @Param("page")int page, @Param("size")int size) {
		return new SQL() {{
			SELECT("*");
			FROM("blog");
			if(title != null) {
				WHERE("title like concat('%', #{title} , '%')");
			}
			if(page > 0 && size > 0) {
				LIMIT("#{page}, #{size}");
			}
		}}.toString();
	}
 
}
 

• Fluent 风格

public String insertPersonSql() {
  String sql = new SQL()
    .INSERT_INTO("PERSON")
    .VALUES("ID, FIRST_NAME", "#{id}, #{firstName}")
    .VALUES("LAST_NAME", "#{lastName}")
    .toString();
  return sql;
}