Mybatis 模糊查询那点事

最新推荐文章于 2024-04-07 12:08:24 发布

程序员路同学

最新推荐文章于 2024-04-07 12:08:24 发布

阅读量120

点赞数

分类专栏： MyBatis 文章标签： mybatis

鹿少年

本文链接：https://blog.csdn.net/huanglu0314/article/details/113561799

版权

MyBatis 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

实体类：

@Data
public class IdCard {
    private Integer id;
    private String code;


}

@Data
public class Person {
    private Integer id;
    private String name;
    private Integer age;
    private String sex;

    /**
     * 个人证号ID
     */
    private IdCard card;

}

直接使用like：

    <resultMap id="PersonAndIdCard" type="mybatis.pojo.Person" >
        <id property="id" column="id" />
        <result property="name" column="name" />
        <result property="age" column="age" />
        <result property="sex" column="sex" />
        <association property="card" javaType="mybatis.pojo.IdCard">
            <id property="id" column="cardId" />
            <result property="code" column="code" />
        </association>
    </resultMap>

  <select id="findPersonByName"  parameterType="String"  resultMap="PersonAndIdCard">
        SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code
	    from t_person p,t_idcard idcard
	    where p.card_id=idcard.id
	    and p.name like '%${value}%'
    </select>

这里要注意：

执行sql语句：

2021-02-02 16:04:06 [ main:628 ] - [ DEBUG ] org.apache.ibatis.transaction.jdbc.JdbcTransaction.setDesiredAutoCommit(JdbcTransaction.java:101) Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@44a3ec6b]
2021-02-02 16:04:07 [ main:631 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==>  Preparing: SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code from t_person p,t_idcard idcard where p.card_id=idcard.id and p.name like '%张%' 
2021-02-02 16:04:07 [ main:683 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters: 
2021-02-02 16:04:07 [ main:705 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <==      Total: 2

换成#的情况下：

sql:

2021-02-02 16:05:23 [ main:626 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==>  Preparing: SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code from t_person p,t_idcard idcard where p.card_id=idcard.id and p.name like '%?%'

可以看出使用#和使用$区别

但是这样使用会出错：

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='value', mode=IN, javaType=class java.lang.String, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
### The error may exist in mybatis/mapper/PersonMapper.xml
### The error may involve mybatis.mapper.PersonMapper.findPersonByNameByLike-Inline
### The error occurred while setting parameters
### SQL: SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code      from t_person p,t_idcard idcard      where p.card_id=idcard.id      and p.name like '%?%'
### Cause: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='value', mode=IN, javaType=class java.lang.String, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

使用concat这里只能在Mysql条件下使用：

sql执行如下：

2021-02-02 15:57:58 [ main:648 ] - [ DEBUG ] org.apache.ibatis.transaction.jdbc.JdbcTransaction.setDesiredAutoCommit(JdbcTransaction.java:101) Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@43a0cee9]
2021-02-02 15:57:58 [ main:655 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==>  Preparing: SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code from t_person p,t_idcard idcard where p.card_id=idcard.id and p.name like concat('%',?,'%') 
2021-02-02 15:57:58 [ main:719 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters: 张(String)
2021-02-02 15:57:58 [ main:752 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <==      Total: 2

同样把#换成$符

执行sql:

2021-02-02 16:13:06 [ main:688 ] - [ DEBUG ] org.apache.ibatis.transaction.jdbc.JdbcTransaction.setDesiredAutoCommit(JdbcTransaction.java:101) Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@44a3ec6b]
2021-02-02 16:13:06 [ main:691 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==>  Preparing: SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code from t_person p,t_idcard idcard where p.card_id=idcard.id and p.name like concat('%',张,'%') 
2021-02-02 16:13:06 [ main:731 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters:

这里是直接把模糊查询的字符直接拼接过去了，它并没有进行字符传转换

这个时候的sql查询如下：

2021-02-02 16:16:57 [ main:730 ] - [ DEBUG ] org.apache.ibatis.transaction.jdbc.JdbcTransaction.setDesiredAutoCommit(JdbcTransaction.java:101) Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7e9a5fbe]
2021-02-02 16:16:57 [ main:733 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==>  Preparing: SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code from t_person p,t_idcard idcard where p.card_id=idcard.id and p.name like concat('%','张','%') 
2021-02-02 16:16:57 [ main:781 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters: 
2021-02-02 16:16:57 [ main:807 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <==      Total: 2

可以看到执行时是每问题的，因为我们手动给他添加引号了

使用bind:

<select id="findPersonByNameWithBound"  parameterType="mybatis.pojo.Person"  resultMap="PersonAndIdCard">
        <bind name="name" value="'%'+_parameter.getName()+'%'"/>
        
        SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code
	    from t_person p,t_idcard idcard
	    where p.card_id=idcard.id
	    and p.name like  #{name}
    </select>

执行的·sql语句：

2021-02-02 16:32:07 [ main:631 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==>  Preparing: SELECT p.id,p.age,p.name,p.sex,p.card_Id as cardId,idcard.code from t_person p,t_idcard idcard where p.card_id=idcard.id and p.name like ? 
2021-02-02 16:32:07 [ main:675 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters: %张%(String)
2021-02-02 16:32:07 [ main:700 ] - [ DEBUG ] org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <==      Total: 2

以上就是mybatis 模糊查询的一些问题