MyBatis多表查询
多表查询:
一对多,多对多 时:使用 <collection> ofType; oftype:表示集合中存储的数据类型
一对一,多对一 时:使用<association> javaType;
核心配置文件mybatis.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="wenjian.properties"></properties>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<typeAliases>
<typeAlias type="com.qust.pojo.Student" alias="student"/>
<package name="com.qust.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC">
<property name="..." value="..."/>
</transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/hw"/>
<property name="username" value="${user}"/>
<property name="password" value="admin"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/qust/mapper/StudentMapper.xml"/>
<mapper resource="com/qust/mapper/ClassesMapper.xml"/>
</mappers>
</configuration>
(一)不需要添加任何jar包 可以打印出 数据库查询详细运行过程
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
(二)两种起别名的方式 注:最好不要一起使用
第一种 :将 com.qust.pojo.Student 全类名 起别名为 student
第二种:将 com.qust.pojo 给包里面所有类进行起别名 为类名(无论大小写都可以使用类型)
<typeAliases> <typeAlias type="com.qust.pojo.Student" alias="student"/> <package name="com.qust.pojo"/> </typeAliases>
(三)下面配置创建读取.properties文件进行数据库四大组件 获值(下面的配置 一定要写在最前面,因为必须jvm以运行文件就得读取,否则数据库驱动无法使用) 使用${}获值
<properties resource="wenjian.properties"></properties>
下面用班级,学生进行举列子
对于学生而言 一个学生只有一个班级 或者说 多个学生有一个班级,因此就是一对一,多对一
一对一,多对一例子:
package com.qust.pojo;
public class Student {
private Integer sid;
private String sname;
private String password;
private Classes classes;
@Override
public String toString() {
return "Student{" +
"sid=" + sid +
", sname='" + sname + '\'' +
", password='" + password + '\'' +
", classes=" + classes +
'}';
}
public Classes getClasses() {
return classes;
}
public void setClasses(Classes classes) {
this.classes = classes;
}
public Student() {
}
public Student(Integer sid, String sname, String password,Classes classes) {
this.sid = sid;
this.sname = sname;
this.password = password;
this.classes = classes;
}
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
StudentMapper接口
package com.qust.mapper;
import com.qust.pojo.Student;
import java.util.List;
public interface StudentMapper {
Student studentOne(int sid);
}
StudentMapper.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.qust.mapper.StudentMapper">
<resultMap id="studentMap" type="com.qust.pojo.Student">
<id column="sid" property="sid"></id>
<result column="sname" property="sname"></result>
<association property="classes" javaType="com.qust.pojo.Classes">
<id column="cid" property="cid"></id>
<result column="cname" property="cname"></result>
</association>
</resultMap>
<select id="studentOne" parameterType="int" resultMap="studentMap">
SELECT s.sid,s.sname,c.cname FROM student s,classes c WHERE s.sid=#{sid} AND s.sid = c.cid
</select>
</mapper>
多对一、多对多 例子:
Classes类:
package com.qust.pojo;
import java.util.List;
public class Classes {
private int cid;
private String cname;
private List<Student> student;
@Override
public String toString() {
return "Classes{" +
"cid=" + cid +
", cname='" + cname + '\'' +
", student=" + student +
'}';
}
public int getCid() {
return cid;
}
public void setCid(int cid) {
this.cid = cid;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public List<Student> getStudent() {
return student;
}
public void setStudent(List<Student> student) {
this.student = student;
}
}
ClassMapper接口:
package com.qust.mapper;
import com.qust.pojo.Classes;
public interface ClassesMapper {
Classes classes(int cid);
}
ClassMapper.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.qust.mapper.ClassesMapper">
<resultMap id="classMap" type="classes">
<id column="cid" property="cid"></id>
<result column="cname" property="cname"></result>
<collection property="student" ofType="STUDENT">
<id property="sid" column="sid"></id>
<result column="sname" property="sname"></result>
</collection>
</resultMap>
<select id="classes" parameterType="int" resultMap="classMap">
SELECT * FROM classes c,student s WHERE c.cid=#{cid} AND c.cid=s.cid
</select>
</mapper>
知识点
${}与#{}区别
${} 时:{}里面表示真正的数据 表示值
这是 就相当于 c.cid = cid ${sid} 就表示大括号里的值就是真实值
#{} 是一个占位符 ,相当于 prestanment中的占位符。只负责传值。