MyBatis中模糊查询

1、项目配置

1.1、项目整体架构

整体架构

1.2、添加jar包、mybatis-config.xml和log4j.properties文件

1.3、User实体类

package com.fan.entity;

public class User {
    private Integer id;
    private String name;
    private String sex;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                '}';
    }
}

1.4、UserDao接口类

package com.fan.dao;

import com.fan.entity.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserDao {
    public List<User> findByNameAndSex(
                                 @Param("uname")String name,
                                 @Param("usex") String sex);
}

2、mybatis中对于使用like来进行模糊查询的几种方式：

2.1、使用${...}

<mapper namespace="com.fan.dao.UserDao">
    <select id="findByNameAndSex" resultType="com.fan.entity.User">
        select * from users where name like '%${uname}%' and sex=#{usex}
    </select>
</mapper>

     测试类

import com.fan.dao.UserDao;
import com.fan.entity.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.Reader;
import java.util.List;

public class Test1 {
    public static void main(String[] args) {
        try {
            Reader resourceAsReader = Resources.getResourceAsReader("mybatis-config.xml");
            SqlSession sqlSession = new SqlSessionFactoryBuilder().build(resourceAsReader).openSession();
            UserDao mapper = sqlSession.getMapper(UserDao.class);
            List<User> users = mapper.findByNameAndSex("张", "男");
            for (User user:users) {
                System.out.println(user);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

     测试结果如下：

测试结果

弊端：可能会引起sql的注入，平时尽量避免使用${...}

2.2、使用#{...}

<mapper namespace="com.fan.dao.UserDao">
    <select id="findByNameAndSex" resultType="com.fan.entity.User">
         select * from users where name like "%"#{uname}"%" and sex=#{usex}
    </select>
</mapper>

     测试类

import com.fan.dao.UserDao;
import com.fan.entity.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.Reader;
import java.util.List;

public class Test1 {
    public static void main(String[] args) {
        try {
            Reader resourceAsReader = Resources.getResourceAsReader("mybatis-config.xml");
            SqlSession sqlSession = new SqlSessionFactoryBuilder().build(resourceAsReader).openSession();
            UserDao mapper = sqlSession.getMapper(UserDao.class);
            List<User> users = mapper.findByNameAndSex("张", "男");
            for (User user:users) {
                System.out.println(user);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

     测试结果如下：

测试结果

     注意：因为#{...}解析成sql语句时候，会在变量外侧自动加单引号' '，所以这里 % 需要使用双引号" "，不能使用单引号 ' '，不然会查不到任何结果。

2.3、使用CONCAT()函数连接参数形式

<mapper namespace="com.fan.dao.UserDao">
    <select id="findByNameAndSex" resultType="com.fan.entity.User">
         select * from users where name like CONCAT('%',#{uname},'%') and sex=#{usex}
    </select>
</mapper>

     测试类

import com.fan.dao.UserDao;
import com.fan.entity.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.Reader;
import java.util.List;

public class Test1 {
    public static void main(String[] args) {
        try {
            Reader resourceAsReader = Resources.getResourceAsReader("mybatis-config.xml");
            SqlSession sqlSession = new SqlSessionFactoryBuilder().build(resourceAsReader).openSession();
            UserDao mapper = sqlSession.getMapper(UserDao.class);
            List<User> users = mapper.findByNameAndSex("张", "男");
            for (User user:users) {
                System.out.println(user);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

     测试结果如下：

测试结果