Spring写sql语句的两种方法

优点：书写方便，不需要再新建一个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);

    // 如果使用条件，则需要在最外层加上，表示脚本
    @Update({
            " "
    })
    int updateStatus(String ticket,int status);

}

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 selectDiscussPosts(int userId,int offset,int limit);

    
    int selectDiscussPostRows(@Param("userId") int userId);

}

.xml文件：

    
    
        id,user_id,title,content,type,status,create_time,comment_count,score
    

    
    
        select 
        from discuss_post
        where status!=2
        and user_id = #{userId}
        order by type desc , create_time desc
        limit #{offset},#{limit}
    

    
        select count(id)
        from discuss_post
        where status!=2
        and user_id = #{userId}