关于springboot + myabtis Mapper 接口模糊查询的解决方法

深碍√TFBOYSˉ_ 2022-10-31 12:19 159阅读 0赞

1、通过mapper接口直接写模糊查询语句

根据keyword 和 type查询

@Select(" SELECT id,title,intro,author,content,type,create_time,is_publish,modify_time,check_name " +
                " FROM article_basic " +
                " WHERE article_basic.intro LIKE '%#{keyword}%' AND article_basic.type=1 " +
                " ORDER BY is_publish DESC , create_time DESC , modify_time DESC")
        List<Article_Basic> queryListByTypeAndKeyWord(@Param("keyword") String keyword,@Param("type") int type);

这种方式写出来sql语句报错 Parameter index out of range (1 > number of parameters, which is 0)

出现的原因是：超出了索引范围

这个时候需要通过 CONCAT语句来完成

concat的一般用法主要是用于拼接的

比如：

执行语句 SELECT CONCAT('H','E','L','L','O')

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MTk4MTgx_size_16_color_FFFFFF_t_70][]

所以改成如下：

@Select(" SELECT id,title,intro,author,content,type,create_time,is_publish,modify_time,check_name " +
                " FROM article_basic " +
                " WHERE article_basic.intro LIKE CONCAT('%',#{keyword},'%') AND article_basic.type=1 " +
                " ORDER BY is_publish DESC , create_time DESC , modify_time DESC")
        List<Article_Basic> queryListByTypeAndKeyWord(@Param("keyword") String keyword,@Param("type") int type);

这样可以避免问题。

也可以通过 \#\{keyword\} 改成 $\{keyword\}   $\{xxx\} 这样不带单引号。也就是

打印出来的SQL：SELECT id,title,intro,author,content,type,create\_time,is\_publish,modify\_time,check\_name FROM article\_basic WHERE (article\_basic.title LIKE '%12%' )....................................

@Select(" SELECT id,title,intro,author,content,type,create_time,is_publish,modify_time,check_name " +
                " FROM article_basic " +
                " WHERE article_basic.intro LIKE '%${keyword}%' AND article_basic.type=1 " +
                " ORDER BY is_publish DESC , create_time DESC , modify_time DESC")
        List<Article_Basic> queryListByTypeAndKeyWord(@Param("keyword") String keyword,@Param("type") int type);

以上。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI4MTk4MTgx_size_16_color_FFFFFF_t_70]: /images/20221024/9a7d5ae08f5a45cc913dbdffa10f79f7.png