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>
结果集配置
User selectUserById(int id);
<!--用于实体类属性和表中字段不一致时-->
<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
*/
}
/**
* 根据用户获取博客
* @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();
}
}
public String insertPersonSql() {
String sql = new SQL()
.INSERT_INTO("PERSON")
.VALUES("ID, FIRST_NAME", "#{id}, #{firstName}")
.VALUES("LAST_NAME", "#{lastName}")
.toString();
return sql;
}
