1.parameterType(输入类型)
(1) 传递简单类型(比如int,我们可以直接进行使用)
public Student getStudentById(Integer studentId);
<select id="getStudentById" parameterType="int" resultType="student">
select * from tblstudent where studentId = #{studentId}
</select>
(2) 传递pojo对象
public Student getStudentByS(Student student);
<select id="getStudentByS" parameterType="student" resultType="student">
select * from tblstudent where studentId = #{studentId}
</select>
(3) 传递pojo包装对象
查询条件可能是综合的查询条件,不仅包括用户查询条件还包括其它的查询条件(比如查询用户信息的时候,将用户购买商品信息也作为查询条件),这时可以使用包装对象传递输入参数。
包装对象:Pojo类中的一个属性是另外一个pojo。
public Student getStudentByBean(StudentBean sb);
<select id="getStudentByBean" parameterType="studentBean" resultType="student">
<!-- select * from tblstudent where studentId = #{student.studentId} -->
select * from tblstudent where studentId = #{studentId}
</select>
2.resultType(输出类型)
(1) 输出简单类型(例如查询查询学生信息记录数,需要的输出类型为int型)
public int selectUserCount();
<select id="selectUserCount" resultType="int">
select count(1) from tblstudent
</select>
(不管是输出的pojo单个对象还是一个list列表,在mapper.xml中resultType指定的类型是一样的。如下)
(2) 输出pojo对象
public Student getSByName(String studentName);
<select id="getSByName" parameterType="String" resultType="student">
<!-- select * from tblstudent where studentName LIKE #{studentName} -->
<!--select * from tblstudent where studentName LIKE "%${value}%" -->
</select>
(3) 输出pojo列表
/**
* 根据学生姓名模糊查询学生信息
* @param studentName
* @return
*/
public List<Student> getStudentByName(String studentName);
<!-- 根据学生姓名模糊查询学生信息 -->
<select id="getStudentByName" parameterType="String" resultType="student">
<!-- select * from tblstudent where studentName LIKE "%"#{studentName}"%" -->
<!-- select * from tblstudent where studentName LIKE #{studentName} -->
<!-- select * from tblstudent where studentName LIKE "%${value}%" -->
<!-- select * from tblstudent where studentName LIKE concat(concat('%',#{studentName}),'%') -->
select * from tblstudent where studentName LIKE concat('%',#{studentName},'%')
</select>
3.resultMap
resultType可以指定将查询结果映射为pojo,但需要pojo的属性名和sql查询的列名一致方可映射成功。
如果sql查询字段名和pojo的属性名不一致,可以通过resultMap将字段名和属性名作一个对应关系 ,resultMap实质上还需要将查询结果映射到pojo对象中。
resultMap可以实现将查询结果映射为复杂类型的pojo,比如在查询结果映射对象中包括pojo和list实现一对一查询和一对多查询。
package cn.xxs.mapper;
import java.util.List;
import cn.xxs.bean.StudentBean;
import cn.xxs.pojo.Student;
public interface UserMapper {
/**
* 插入学生数据
* @param user
*/
public int addStudent(Student student);
/**
* 通过学生编号查询
* @param id
* @return
*/
public Student selectStudentById(Integer studentId);
/**
* 多条件查询用户信息
* @param student
* @return
*/
public List<Student> selectStudent(Student student);
}
<?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">
<!-- namespace 命名 #{}:点位符,相当于jdbc的?-->
<mapper namespace="cn.xxs.mapper.UserMapper">
<!-- sql片段 -->
<sql id="student_sql">
studentId,studentName,age,studentNo,birthDay
</sql>
<!-- id: sql中语句的唯一标识
parameterType :入参的数据类型
resultType:返回结果的数据类型
-->
<!-- 插入学生数据 -->
<select id="addStudent" parameterType="int" resultType="student">
insert into tblstudent(studentId,studentName,age,studentNo,birthDay)
values(#{studentId},#{studentName},#{age},#{studentNo},#{birthDay})
</select>
<resultMap type="student" id="student_map">
<id column="studentId" property="id"/>
<result column="studentName" property="studentName"/>
<result column="age" property="age"/>
<result column="studentNo" property="studentNo"/>
<result column="birthDay" property="birthDay"/>
</resultMap>
<select id="selectStudentById" parameterType="int" resultType="student" resultMap="student_map">
select
<include refid="student_sql"></include>
from tblstudent
where studentId = #{id}
</select>
<select id="selectStudent" parameterType="int" resultType="student" resultMap="student_map">
select
<include refid="student_sql"></include>
from tblstudent
<where>
<if test="studentName != null and studentName != '' ">
and studentName LIKE concat('%',#{studentName},'%')
</if>
<if test="age != null and age != '' ">
and age LIKE concat('%',#{age},'%')
</if>
</where>
</select>
</mapper>