5.MyBatis Select

Posted on 2019-10-16 Edited on 2023-02-16 In MyBatis

1. selectOne()

selectOne()方法只能返回1条或者0条记录

StudentDaoImpl

import java.util.List;

import org.apache.ibatis.session.SqlSession;

public class StudentDaoImpl implements StudentDao{

	@Override
	public Student selectStudentById(Long id) {
		SqlSession session=SqlSessionUtil.getSession();
		Student student=session.selectOne("selectStudentById", id);
		SqlSessionUtil.close(session);
		return student;
	}
}

StudentDao.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="test">

	<!-- 
		select 执行数据库的查询
		属性
			resultType或者resultMap 必须拥有其中一个
			resultType:	指定查询结果的一条记录的封装体对象，这个可以填写实体类的全限定名或者是在主配置文件中自定义的别名
			使用了resultType后，MyBatis会使用默认的封装规则，将显示列所对应的数据，封装到resultType所指定的类中与显示列同名的属性中，这种规则要求显示列名必须与实体类的属性名相同
	 -->

    <!--
        传入的参数为基本数据类型，#{xxx}，任意填写
    -->
	<select id="selectStudentById" resultType="student">
		select * from student where id=#{xxx}
	</select>
</mapper>

2. selectList()

selectList(): 查询多条记录

import java.util.List;

import org.apache.ibatis.session.SqlSession;

public class StudentDaoImpl implements StudentDao{

	@Override
	public List<Student> selectStudentAll() {
		SqlSession session=SqlSessionUtil.getSession();
		List<Student> list=session.selectList("selectStudentAll");
		SqlSessionUtil.close(session);
		return list;
	}
}

StudentDao.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="test">
	<!-- 
		调用selectList方法，MyBatis会将查询结果的每一条记录封装到Student对象中，然后将多个Student对象add到List集合中进行返回
	 -->
	<select id="selectStudentAll" resultType="student">
		select * from student
	</select>
</mapper>

3. 查询多条记录返回Map

import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;

public class StudentDaoImpl implements StudentDao{

	@Override
	public Map<String, Student> selectStudentAll() {
		SqlSession session=SqlSessionUtil.getSession();
		//以name列的值作为返回的map集合的key
		Map<String, Student> map=session.selectMap("selectStudentAll", "name");
		SqlSessionUtil.close(session);
		return map;
	}	
}

StudentDao.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="test">	
	<!-- 
		调用selectMap()方法，MyBatis会将每条记录的数据封装到resultType所指定的Student类中，并将对象逐一添加到Map集合中，并使用selectMap的参数2所指定的列名对应的值作为Map集合的key
	 -->
	<select id="selectStudentAll" resultType="student">
		select * from student
	</select>
</mapper>

返回值

{ , , ,}

4. 模糊查询

StudentDaoImpl.java

import java.util.List;

import org.apache.ibatis.session.SqlSession;

public class StudentDaoImpl implements StudentDao{
	public List<Student> selectStudentLikeName1(String name) {
		SqlSession session=SqlSessionUtil.getSession();
		List<Student> list=session.selectList("selectStudentLikeName", name);
		SqlSessionUtil.close(session);
		return list;
	}
}

StudentDao.xml

1.使用数据库提供的字符串拼接函数，使用#{}预编译形式实现模糊查询

<?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="test">
	<select id="selectStudentLikeName" resultType="student">
		select * from student where name like concat('%',#{xxx},'%')
	</select>
</mapper>

2.直接使用预编译实现模糊查询(推荐使用)

注意：#{}的前后必须拥有空格，否则查询失败

StudentDao.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="test">
	<select id="selectStudentLikeName" resultType="student">
		select * from student where name like '%' #{name} '%'
	</select>
</mapper>

3.${}：单纯的字符串拼接

如果参数对象为基本数据格式，那么只能填写value，如果参数对象为实体类，则需要填写属性名

注: 由于${}是字符串拼接，没有使用预编译，因此会导致SQL注入攻击，并且执行效率低，因此我们不推荐使用

StudentDao.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="test">
	<!-- ${value}: 固定写法 -->
	<select id="selectStudentLikeName" resultType="student">
		select * from student where name like '%${value}%'
	</select>
</mapper>

#{}:

预编译
基本数据类型#{xxx},任意名称

${}

字符串拼接
基本数据类型${value}
执行效率低，容易导致SQL注入攻击

5. 使用Map集合作为查询条件

StudentDaoImpl.java

import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;

public class StudentDaoImpl implements StudentDao{

	@Override
	public List<Student> selectStudentByMap(Map map) {
		SqlSession session=SqlSessionUtil.getSession();
		List<Student> list=session.selectList("selectStudentByMap", map);
		SqlSessionUtil.close(session);
		return list;
	}

	public static void main(String[] args) {
		StudentDao dao=new StudentDaoImpl();
		Map map=new HashMap();
		map.put("name", "a");
		System.out.println(dao.selectStudentByMap(map));
		
	}
}

StudentDao.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="test">

	<!-- 
		当参数对象为Map集合时，#{map集合的key值}
	 -->
	<select id="selectStudentByMap" resultType="student">
		select * from student where name like '%' #{name} '%'
	</select>
</mapper>

#{param}

属性名
xxx,任意
map的key