目录
一. resultMap
1. 使用场景:
(1)当数据库列名和java类中的属性名不同时,可⽤ resultMap 配置映射 (下列代码中有举例)；
(2)在单表查询和多表查询中可以使⽤, resultMap 映射并查询数据
2. 查询映射:
(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">
<!-- 专门写SQL语句的文件-->
<mapper namespace="mybatispro.dao.AdminDao"><!--接口的地址-->
<!--resultMap标签:对数据库中的列名和java类中的属性名进行映射 常用在多表-->
<resultMap id="adminMap" type="Admin">
<!--数据库列名(column) java类中的属性名(property)-->
<id column="id" property="id"/><!--映射主键使用id标签-->
<result column="account" property="account"/>
<result column="password" property="password"/>
<result column="gender" property="gender"/>
</resultMap>
<select id="findAdmins" parameterType="string" resultMap="adminMap">
select id,account,age as adminAge from admin order by ${col} desc
</select>
</mapper>注意: 只有数据库列名和java类中的属性名相同时才会自动映射
而使用resultMap相当于手动匹配,即使名称不一样也会映射
使用as起别名让二者名称相同,也可以自动映射
(2)多表查询映射
a. 在学生表里查专业
注意看图 , 图很重要 !
在查询时,会遇到多表关联,例如 在查询学生信息表时,会关联专业表(id,major,info) , 课程表(id,class,info)...当关联的数据很多时,在创建类的时候,需要定义的属性(专业id,专业major,专业info,课程id,课程class,课程info)就会很多,为了减少冗余,我们可以把每个数据表看成一个对象,这个对象里面包含了各自的属性
package mybatispro.model;
public class Student {
private int id;
private int num;
private String name ;
private String gender;
private String phone;
private Major major;//学生关联专业
/*关联表中的数据,在设计类时,不建议把关联类中的属性,在本类中再定义一遍,减少冗余
private Major major;
private String mname;*/
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public Major getMajor() {
return major;
}
public void setMajor(Major major) {
this.major = major;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", num=" + num +
", name='" + name + '\'' +
", gender='" + gender + '\'' +
", phone='" + phone + '\'' +
", major=" + major +
'}';
}
}
package mybatispro.dao;
import mybatispro.model.Student;
import java.util.ArrayList;
public interface StudentDao {
Student findStudentById(int id);
ArrayList<Student> findStudents();
}
<?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">
<!-- 专门写SQL语句的文件-->
<mapper namespace="mybatispro.dao.StudentDao">
<!-- 多表关联查询时,需要自定义映射关系 -->
<resultMap id="studentMap" type="Student">
<id property="id" column="id"/>
<result property="num" column="num"/>
<result property="name" column="name"/>
<result property="gender" column="gender"/>
<result property="phone" column="phone"/>
<!-- 关联表数据映射-->
<association property="major" javaType="Major"><!--association相当于创建一个major对象,这个对象里面封装了major的所有属性-->
<!-- property(java类中的属性名) javaType(属性名对应的类型名) column(数据库列名)-->
<id column="mid" property="id"/>
<result column="mname" property="name"/>
</association>
</resultMap>
<!--查所有学生信息-->
<select id="findStudents" resultMap="studentMap">
select
s.id,s.num,s.name,s.gender,s.phone,m.id as mid ,m.name as mname
from student s inner join major m on s.majorid= m.id
</select>
<!--查单个学生信息 通过id-->
<select id="findStudentById" parameterType="int" resultMap="studentMap">
select
s.id,s.num,s.name,s.gender,s.phone,m.id as mid,m.name as mname
from student s inner join major m on s.majorid= m.id
where s.id =#{id}
</select>
</mapper>package mybatispro.test;
import mybatispro.dao.StudentDao;
import mybatispro.model.Student;
import mybatispro.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import java.io.IOException;
import java.util.ArrayList;
public class TestStudent {
public static void main(String[] args) throws IOException {
SqlSession sqlSession = MybatisUtil.getSqlSession();
StudentDao studentDao =sqlSession.getMapper(StudentDao.class);
//查单个学生信息
Student student = studentDao.findStudentById(3);
System.out.println(student);
//获取学生信息
System.out.println(student.getId());
System.out.println(student.getName());
//获取专业信息,需要先获得专业对象
System.out.println(student.getMajor().getName());
System.out.println(student.getMajor().getId());
/*查所有学生信息
ArrayList<Student> students = studentDao.findStudents();
System.out.println(students);*/
sqlSession.close();
}
}
b. 在专业表里查学生
注意看图 , 图很重要 !
<?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">
<!-- 专门写SQL语句的文件-->
<mapper namespace="mybatispro.dao.MajorDao">
<resultMap id="majorMap" type="Major">
<id column="id" property="id"/>
<result column="name" property="name"/>
<!--用来接收多个对象,将多个对象封装到集合中-->
<collection property="students" javaType="arrayList" ofType="Student">
<id column="sid" property="id"/>
<result column="sname" property="name"/>
<result column="num" property="num"/>
</collection>
</resultMap>
<select id="findMajorById" parameterType="int" resultMap="majorMap">
select
m.id,
m.name,
s.id as sid,
s.name as sname,
s.num
from
major m
inner join student s
on m.id = s.majorid
where m.id = #{id}
</select>
</mapper>package mybatispro.dao;
import mybatispro.model.Major;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
public interface MajorDao {
Major findMajorById(int id);
/*当SQL语句很简单时,就不需要写在对应的xml文件当中,直接在接口中使用@Insert等注解标签就行
复杂的额SQL建议鞋子xml文件中*/
@Insert("insert into major(name) value(#{name})")
int insertMajor(Major major);
@Delete("delete from major where id=#{id}")
int deleteMajor(int id);
}
package mybatispro.model;
import java.util.ArrayList;
public class Major {
private int id;
private String name;
private ArrayList<Student> students; //一个专业对应(关联)多个学生
public ArrayList<Student> getStudents() {
return students;
}
public void setStudents(ArrayList<Student> students) {
this.students = students;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Major{" +
"id=" + id +
", name='" + name + '\'' +
", students=" + students +
'}';
}
}
package mybatispro.test;
import mybatispro.dao.MajorDao;
import mybatispro.model.Major;
import mybatispro.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import java.io.IOException;
public class TestMajor {
public static void main(String[] args) throws IOException {
SqlSession sqlSession = MybatisUtil.getSqlSession();
MajorDao majorDao = sqlSession.getMapper(MajorDao.class);
/* Major major = majorDao.findMajorById(1);
System.out.println(major);*/
/*简单语句---添加
Major major = new Major();
major.setName("数学");
majorDao.insertMajor(major);*/
//简单语句---删除
majorDao.deleteMajor(6);
sqlSession.commit();
sqlSession.close();
}
}
二. 其他注意事项
1. 插件下载
使用这个插件点左侧的鸟可以在dao和对应的mapper之间快速定位切换,提高效率
插件也可以从自己的电脑硬盘上导入,选择自己下载好的插件
2. #{ } 和 ${ }的区别
#{变量} 使用预编译的方式,先在SQL中占位,之后再赋值,可以防止SQL注入,是安全的 一般传递的是值(账号 密码 姓名)
${变量} 直接将值拼接到SQL中,容易出现SQL注入现象,不安全 写法上需要加单引号 account = $'{account}' 一般传递的是列名,在某些 列排序时(价格 时间...) 比较方便 order by ${col} desc