InvitationMapper接口
package com.sy.mapper;
import com.sy.model.Invitation;
import com.sy.provider.InvitationProvider;
import org.apache.ibatis.annotations.*;
import java.util.List;
public interface InvitationMapper {
@Insert("INSERT into invitation(title,summary,author) VALUES (#{title},#{summary},#{author})")
@Options(useGeneratedKeys = true,keyProperty = "id",keyColumn = "id",resultSets ="invitation" )
Integer insert(Invitation invitation);
@Delete("DELETE FROM invitation where id=#{id}")
Integer delete(Integer id);
@UpdateProvider(method = "updataSql",type = InvitationProvider.class)
Integer update(Invitation invitation);
@Select("SELECT *FROM invitation LIMIT #{page},#{pageSize}")
List<Invitation> selectAll(@Param("page") int page,@Param("pageSize") int pageSize);
@SelectProvider(method = "selectSql",type = InvitationProvider.class)
List<Invitation> selectByLike(@Param("keywords") String keywords,@Param("page") int page,@Param("pageSize") int pageSize);
@Select("SELECT count(*)from invitation")
Integer selectAllCount();
@SelectProvider(method = "selectCountSql",type = InvitationProvider.class)
Integer selectCountByLike(Invitation invitation);
}
Provider实现类
package com.sy.provider;
import com.sy.model.Invitation;
import org.apache.ibatis.jdbc.SQL;
public class InvitationProvider {
public static String updataSql(final Invitation invitation){
SQL sql=new SQL();
sql=sql.UPDATE("invitation");
if (invitation.getTitle()!=null&&!"".equals(invitation.getTitle())){
sql=sql.SET("title=#{title}");
}
if (invitation.getSummary()!=null&&!"".equals(invitation.getSummary())){
sql=sql.SET("summary=#{summary}");
}
if (invitation.getAuthor()!=null&&!"".equals(invitation.getAuthor())){
sql=sql.SET("author=#{author}");
}
sql=sql.WHERE("id=#{id}");
return sql.toString();
}
public static String selectSql(final Invitation invitation){
SQL sql=new SQL();
sql=sql.SELECT("*");
sql=sql.FROM("invitation");
if (invitation.getTitle()!=null&&!"".equals(invitation.getTitle())){
sql=sql.WHERE("title like concat('%',#{keywords},'%') LIMIT #{page},#{pageSize}");
}
return sql.toString();
}
public static String selectCountSql(final Invitation invitation){
SQL sql=new SQL();
sql=sql.SELECT("count(*)");
sql=sql.FROM("invitation");
if (invitation.getTitle()!=null&&!"".equals(invitation.getTitle())){
sql=sql.WHERE("title like concat('%',#{title},'%')");
}
if (invitation.getSummary()!=null&&!"".equals(invitation.getSummary())){
sql=sql.WHERE("summary like concat('%',#{summary},'%')");
}
if (invitation.getAuthor()!=null&&!"".equals(invitation.getAuthor())){
sql=sql.WHERE("author like concat('%',#{author},'%')");
}
return sql.toString();
}
}
xml配置写法
多级嵌套
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sy.mapper.DownloadCategoryMapper">
<resultMap id="baseResultMap" type="com.sy.model.DownloadCategory">
<id property="id" column="id"/>
<result property="categoryname" column="categoryname"/>
<result property="pid" column="pid"/>
<collection property="children" ofType="com.sy.model.DownloadCategory"
select="selectByPid" column="id"></collection>
</resultMap>
<select id="selcetRoot" resultMap="baseResultMap">
SELECT *FROM downloadcategory where pid=0
</select>
<select id="selectByPid" resultMap="baseResultMap">
SELECT *FROM downloadcategory where pid=#{pid}
</select>
<select id="selectByid" resultMap="baseResultMap">
SELECT *FROM downloadcategory where id=#{id}
</select>
</mapper>
关联一个对象
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sy.mapper.DownloadreplyMapper">
<resultMap id="baseResultMap" type="com.sy.model.Downloadreply">
<id property="id" column="id"/>
<result property="content" column="content"/>
<result property="createtime" column="createtime"/>
<result property="appraise" column="appraise"/>
<result property="userid" column="userid"/>
<result property="dowid" column="dowid"/>
<association property="user" javaType="com.sy.model.User">
<id property="userId" column="userid"/>
<result property="username" column="username"/>
<result property="headImg" column="headImg"/>
</association>
</resultMap>
<!--查询所有评论-->
<select id="selectByDowid" resultMap="baseResultMap">
select `downloadreply`.*,`user`.username ,`user`.headImg from `csdn`.`downloadreply`
LEFT JOIN `csdn`.`user` on `downloadreply`.userid=`user`.userid
where dowid=#{dowid}
ORDER BY createtime DESC LIMIT #{page},#{pageSize}
</select>
<!--添加评论-->
<insert id="insert" parameterType="com.sy.model.Downloadreply">
INSERT INTO `csdn`.`downloadreply` ( `userid`, `content`, `createtime`, `appraise`, `dowid`) VALUES ( #{userid}, #{content}, NOW(), #{appraise}, #{dowid});
</insert>
<!--查询评论数-->
<select id="selectcountByDowid" resultType="java.lang.Integer">
select count(*) from `csdn`.`downloadreply`
where dowid=#{dowid}
</select>
<select id="selectAllByDowid" resultType="com.sy.model.Downloadreply">
select *from `csdn`.`downloadreply`
where dowid=#{dowid}
</select>
</mapper>
关联一个集合
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sy.mapper.UploadMapper">
<resultMap id="baseResultMap" type="com.sy.model.Upload">
<id property="id" column="id"/>
<result property="title" column="title"/>
<result property="createtime" column="createtime"/>
<result property="appraise" column="appraise"/>
<result property="userid" column="userid"/>
<result property="size" column="size"/>
<result property="price" column="price"/>
<result property="status" column="status"/>
<result property="categoryid" column="categoryid"/>
<result property="categoryid2" column="categoryid2"/>
<result property="downloadCount" column="downloadCount"/>
<result property="leixin" column="leixin"/>
<result property="src" column="src"/>
<result property="intro" column="intro"/>
<result property="replyCount" column="replyCount"/>
<association property="user" javaType="com.sy.model.User">
<id property="userId" column="userId"/>
<result property="username" column="username"/>
<result property="headImg" column="headImg"/>
</association>
</resultMap>
<!--查询所有(条件)-->
<select id="selectAll" resultMap="baseResultMap">
select `upload`.*,`user`.username,`user`.headImg from `csdn`.`upload`
LEFT JOIN `csdn`.`user` on `upload`.userid=`user`.userid
<trim prefix="where" prefixOverrides="and | or">
<if test="userid !=null and userid !=''">
and upload.userid=#{userid}
</if>
<if test="title !=null and title !=''">
and title like concat('%',#{title},'%')
</if>
<if test="categoryid !=null and categoryid !=''">
and categoryid = #{categoryid}
</if>
<if test="categoryid2 !=null and categoryid2 !=''">
and categoryid2 = #{categoryid2}
</if>
<if test="leixin !=null and leixin !=''">
and leixin like concat('%',#{leixin},'%')
</if>
<if test="status !=null and status !=''">
and status =#{status}
</if>
</trim>
ORDER BY createtime DESC LIMIT #{page},#{pageSize}
</select>
<!--按id查询-->
<select id="selectById" resultMap="baseResultMap">
select `upload`.*,`user`.username,`user`.headImg from `csdn`.`upload`
LEFT JOIN `csdn`.`user` on `upload`.userid=`user`.userid
where id=#{id}
</select>
<!--查找资源数-->
<select id="selectAllCount" resultType="java.lang.Integer">
select COUNT(*) from `csdn`.`upload`
<trim prefix="where" prefixOverrides="and | or">
<if test="userid !=null and userid !=''">
and userid=#{userid}
</if>
<if test="title !=null and title !=''">
and title like concat('%',#{title},'%')
</if>
<if test="categoryid !=null and categoryid !=''">
and categoryid = #{categoryid}
</if>
<if test="categoryid2 !=null and categoryid2 !=''">
and categoryid2 = #{categoryid2}
</if>
<if test="leixin !=null and leixin !=''">
and leixin like concat('%',#{leixin},'%')
</if>
<if test="status !=null and status !=''">
and status =#{status}
</if>
</trim>
</select>
<!--新增资源-->
<insert id="insert" parameterType="com.sy.model.Upload">
INSERT INTO `csdn`.`upload` (`userid`, `title`, `createtime`, `appraise`, `size`, `price`, `status`, `categoryid`, `categoryid2`, `downloadCount`, `leixin`, `src`,`intro`) VALUES
(#{userid}, #{title}, NOW(), #{appraise}, #{size}, #{price}, 1,#{categoryid},#{categoryid2}, #{downloadCount}, #{leixin},#{src},#{intro});
</insert>
<!--更新回复数-->
<update id="updataReplyCount" parameterType="java.lang.Integer">
UPDATE upload
<set>
<if test="replyCount !=null and replyCount !=''">
replyCount=#{replyCount},
</if>
<if test="appraise !=null and appraise !=''">
appraise=#{appraise}
</if>
</set>
where id=#{id}
</update>
</mapper>