Spring写sql语句的两种方法

一、在Mapper接口中加注解

优点：书写方便，不需要再新建一个xml文件

缺点：不易观察分析，当sql语句较长时，不易书写

import com.nowcoder.community.community.entity.LoginTicket;
import org.apache.ibatis.annotations.*;

@Mapper
public interface LoginTicketMapper {

    // 习惯在每个""最后加上空格，防止拼接字符串时出错
    @Insert({
            "insert into login_ticket(user_id,ticket,status,expired) ",
            "values(#{userId},#{ticket},#{status},#{expired})"
    })
    // 定义主键
    @Options(useGeneratedKeys = true,keyProperty = "id")
    int insertLoginTicket(LoginTicket loginTicket);

    @Select({
            "select id,user_id,ticket,status,expired ",
            "from login_ticket where ticket=#{ticket}"
    })
    LoginTicket selectByTicket(String ticket);

    // 如果使用<if>条件，则需要在最外层加上<script></script>，表示脚本
    @Update({
            "<script> ",
            "update login_ticket set status=#{status} where ticket=#{ticket} ",
            "<if test=\"ticket!=null\"> ",
            "and 1=1 ",
            "</if> ",
            "</script> "
    })
    int updateStatus(String ticket,int status);

}

二、在.xml配置文件中写sql

Mapper接口：

import com.nowcoder.community.community.entity.DiscussPost;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface DiscussPostMapper {

    List<DiscussPost> selectDiscussPosts(int userId,int offset,int limit);

    /**
     * @param注解用于给参数取别名
     * 如果只有一个参数，并且在<if>里使用，则必须加别名
     * @param
     * @return
     */
    int selectDiscussPostRows(@Param("userId") int userId);

}

.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">
<!--sql接口的路径-->
<mapper namespace="com.nowcoder.community.community.dao.DiscussPostMapper">
    <!--抽取查询域部分代码-->
    <sql id="selectFields">
        id,user_id,title,content,type,status,create_time,comment_count,score
    </sql>

    <!--  id为接口中的方法名，resultType为方法的返回类型，
    parameterType为参数的类型，keyProperty为定义主键(一般在insert语句中使用)  -->
    <select id="selectDiscussPosts" resultType="DiscussPost">
        select <include refid="selectFields"></include>
        from discuss_post
        where status!=2
        <if test="userId!=0">and user_id = #{userId}</if>
        order by type desc , create_time desc
        limit #{offset},#{limit}
    </select>

    <select id="selectDiscussPostRows" resultType="int">
        select count(id)
        from discuss_post
        where status!=2
        <if test="userId!=0">and user_id = #{userId}</if>
    </select>

</mapper>