GBase8s+Mybatis之模糊查询

可以使用如下的方式进行sql模糊查询

        <!--gbase8s,mysql-->
        select * from t_user where username like '%${username}%'
        <!--mysql中可以，但是gbase8s的concat函数只支持传入两个参数-->
        select * from t_user where username like concat('%',#{username},'%')
        <!-- gbase8s -->
        select * from t_user where username like '%' || #{username} || '%'
        <!--mysql-->
        select * from t_user where username like "%"#{username}"%"
复制

SelectByLikeMapper.java接口类

package com.gbasedbt.Mybatis.mapper;

import com.gbasedbt.Mybatis.entities.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface SelectByLikeMapper {

    /**
     * 根据用户名模糊查询用户信息
     */
    List<User> getUserByLike(@Param("username") String username);
}
复制

SelectByLikeMapper.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">

<mapper namespace="com.gbasedbt.Mybatis.mapper.SelectByLikeMapper">
<!--    List<User> getUserByLike(@Param("username") String username);-->
    <select id="getUserByLike" resultType="User">
        <!--gbase8s,mysql-->
        <!-- select * from t_user where username like '%${username}%'-->
        <!--mysql中可以，但是gbase8s的concat函数只支持传入两个参数-->
        <!-- select * from t_user where username like concat('%',#{username},'%')  -->
        <!-- gbase8s -->
        select * from t_user where username like '%' || #{username} || '%'
        <!--mysql-->
        <!--select * from t_user where username like "%"#{username}"%"-->
    </select>

</mapper>
复制

TestSelectByLike.测试类

import com.gbasedbt.Mybatis.entities.User;
import com.gbasedbt.Mybatis.mapper.SelectByLikeMapper;
import com.gbasedbt.Mybatis.utils.SqlSessionUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class TestSelectByLike {
    @Test
    public void selectByLike() {
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SelectByLikeMapper mapper = sqlSession.getMapper(SelectByLikeMapper.class);
        List<User> users = mapper.getUserByLike("to");

        users.forEach(System.out::println);

    }
}
复制

输出结果

2025-03-24 22:08:26  [main]  [com.gbasedbt.Mybatis.mapper.SelectByLikeMapper.getUserByLike]  [ DEBUG ] - ==>  Preparing: select * from t_user where username like '%' || ? || '%'
2025-03-24 22:08:26  [main]  [com.gbasedbt.Mybatis.mapper.SelectByLikeMapper.getUserByLike]  [ DEBUG ] - ==> Parameters: to(String)
2025-03-24 22:08:26  [main]  [com.gbasedbt.Mybatis.mapper.SelectByLikeMapper.getUserByLike]  [ DEBUG ] - <==      Total: 2
User{id=1, username='tom', password='123456', age=34, sex='1', email='123456@qq.com'}
User{id=3, username='tom', password='123456', age=23, sex='1', email='123@123.com'}
复制