Java笔记之Mybatis(三):模糊查询
1.t_student表新增数据
USE keeper; INSERT INTO `t_student` VALUES (null, '张无忌', 25); INSERT INTO `t_student` VALUES (null, '张三丰', 100); INSERT INTO `t_student` VALUES (null, '赵云', 27); INSERT INTO `t_student` VALUES (null, '赵三', 24);
2.修改Student.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.mybatis.demo.pojo"> <!-- 查询所有的Student信息 --> <select id="getAllStudent" resultType="Student"> select student_id, student_name, student_age from t_student </select> <!-- 根据ID查询Student的信息 --> <select id="getStudentById" parameterType="int" resultType="Student"> select * from t_student where student_id=#{value} </select> <!-- 新增Student信息 --> <insert id="addStudent" parameterType="Student"> insert into t_student values( null, #{studentName}, #{studentAge} ) </insert> <!-- 根据ID删除指定的Student信息 --> <delete id="deleteStudentById" parameterType="int"> delete from t_student where student_id=#{value} </delete> <!-- 根据ID更新Student信息 --> <update id="updateStudentById" parameterType="Student"> update t_student set student_name=#{studentName}, student_age=#{studentAge} where student_id=#{studentId} </update> <!-- 根据学生名字模糊查询学生信息 --> <select id="getStudentByName" parameterType="string" resultType="Student"> select * from t_student where student_name like concat('%',#{value},'%') </select> </mapper>
3.在com.mybatis.demo.test下新建MybatisTest2类
package com.mybatis.demo.test; import java.io.IOException; import java.io.InputStream; import java.util.List; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import com.mybatis.demo.pojo.Student; public class MybatisTest2 { public static void main(String[] args) throws IOException { //加载mybatis的配置文件 InputStream inputStream = Resources.getResourceAsStream("mybatisConfig.xml"); //获取SqlSessionFactory对象 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //使用SqlSessionFactory对象创建SqlSession对象 SqlSession sqlSession = sqlSessionFactory.openSession(); List<Student> students1 = sqlSession.selectList("getStudentByName", "张"); for (Student student : students1) { System.out.println(student.getStudentId()+"\t"+student.getStudentName()+"\t"+student.getStudentAge()); } System.out.println("======华丽的分割线======"); List<Student> students2 = sqlSession.selectList("getStudentByName", "三"); for (Student student : students2) { System.out.println(student.getStudentId()+"\t"+student.getStudentName()+"\t"+student.getStudentAge()); } } }
4.运行MybatisTest2类,结果如下:
7 张无忌 25 8 张三丰 100 ======华丽的分割线====== 8 张三丰 100 10 赵三 24
5.总结
(1).mysql数据库中模糊查询使用concat函数,将%与查询参数拼接起来,例如该例中的查询条件student_name like concat('%',#{value},'%')
(2).oracle数据库中则使用||连接%与查询参数,例如本例中的查询条件将改成student_name like '%'||#{value}||'%'