引入Config和Mapper的 DTD文件
引入ConfigDTD文件
- -//mybatis.org//DTD Config 3.0//EN
- http://mybatis.org/dtd/mybatis-3-config.dtd
- -//mybatis.org//DTD mapper 3.0//EN
- http://mybatis.org/dtd/mybatis-3-mapper.dtd
MyBatis起别名
Mybatis的表之间的关系
Mybatis中的单项多对一(那联合表查询和插入为例子)
实战运用场景:看需求 具体问题具体分析
<?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.XXX.xql.Dao.IStudentDao">
<!-- 手动进行查询结果映射 id=下面映射查询的resultType -->
<resultMap id="MyStudent" type="Student">
<!-- 主属性 --><!-- column字段名 property属性名 --><!-- 主属性一个 -->
<id column="stu_id" property="stuId"/>
<!-- 普通属性 --><!-- column字段名 property属性名 --><!-- 普通属性一个或者多个 -->
<result column="stu_name" property="stuName"/>
<!-- 关系属性 对象类型(‘一’) --><!-- property关系属性名javaType关系属性类型 -->
<association property="clz" javaType="Clazz">
<!-- 主属性 --><!-- column字段名 property属性名 -->
<id column="clazz_id" property="clazzId"/>
<!-- 普通属性 --><!-- column字段名 property属性名 --><!-- 普通属性一个或者多个 -->
<result column="clazz_name" property="clazzName"/>
<!-- 关系属性 对象类型(‘一’) --><!-- property关系属性名javaType关系属性类型 -->
<!-- 还可以继续 写关系属性 -->
</association>
</resultMap>
<!-- 查一个根据id查学生班级姓名班级号 -->
<!-- id=接口中的方法名字 parameterType参数类型一个写多个不写也可以写,在后面 resultType返回值类型 -->
<select id="finOneStudent" parameterType="java.lang.Integer" resultMap="MyStudent">
select stu_id,stu_name,clazz_id from student176 sd left join clazz176 cz
on sd.clz_id = cz.clazz_id
where stu_id=#{id}
</select>
<!-- 插入一个学生对象 --> <!-- int也是用的人家底层封装别名 -->
<insert id="InsertStudent" parameterType="Student">
<selectKey resultType="int" keyProperty="stuId" order="BEFORE">
select student176_seq.nextval from dual
</selectKey>
insert into student176 values(#{stuId},#{stuName},#{clz.clazzId})
</insert>
</mapper>
实体类
package com.XXX.xql.Entity;
import java.io.Serializable;
/**
* @category 班级表
* @author 许清磊
*
*/
public class Clazz implements Serializable {
private Integer clazzId;
private String clazzName;
/**
* 定义程序序列化ID
*/
private static final long serialVersionUID = 1L;
public Clazz() {
super();
}
public Clazz(Integer clazzId, String clazzName) {
super();
this.clazzId = clazzId;
this.clazzName = clazzName;
}
public Integer getClazzId() {
return clazzId;
}
public void setClazzId(Integer clazzId) {
this.clazzId = clazzId;
}
public String getClazzName() {
return clazzName;
}
public void setClazzName(String clazzName) {
this.clazzName = clazzName;
}
public static long getSerialversionuid() {
return serialVersionUID;
}
@Override
public String toString() {
return "Student [clazzId=" + clazzId + ", clazzName=" + clazzName + "]";
}
}
package com.XXX.xql.Entity;
import java.io.Serializable;
/**
* @category 学生表 外键关联班级表ID
* @author 许清磊
* 多的一方学生是
*/
public class Student implements Serializable {
private Integer stuId; //主属性 对应数据库表中的主键 一个
private String stuName; //普通属性 一个或者多个
private Clazz clz; //关系属性 对应数据库中的外键 一个或者多个
// stu_id number(7) primary key,
// stu_name varchar2(15) not null,
// clz_id number(7) references clazz176(clazz_id)
/**
* 定义程序序列化ID
*/
private static final long serialVersionUID = 1L;
public Student() {
super();
}
public Student(Integer stuId, String stuName, Clazz clz) {
super();
this.stuId = stuId;
this.stuName = stuName;
this.clz = clz;
}
public Integer getStuId() {
return stuId;
}
public void setStuId(Integer stuId) {
this.stuId = stuId;
}
public String getStuName() {
return stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public Clazz getClz() {
return clz;
}
public void setClz(Clazz clz) {
this.clz = clz;
}
public static long getSerialversionuid() {
return serialVersionUID;
}
@Override
public String toString() {
return "Student [stuId=" + stuId + ", stuName=" + stuName + ", clz="
+ clz + "]";
}
}
具体还是根据实际需求为准 不可盲目以为一对放哪一方
2.表的一对多
需求分析:
<?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.XXX.xql.Dao.IClazzDao">
<!-- resultMap查询结果手动映射 -->
<resultMap type="com.XXX.xql.Entity.Clazz" id="Myclass">
<!-- 主属性 -->
<id column="clazz_id" property="clazzId"/>
<!-- 普通方法 -->
<result column="clazz_name" property="clazzName"/>
<!-- 关系属性(集合多的一方)ofType集合元素类型 -->
<collection property="stuList" ofType="com.XXX.xql.Entity.Student">
<!-- 普通方法 -->
<result column="stu_name" property="stuName"/>
</collection>
</resultMap>
<!-- 根据班级名查询工班级的信息(编号班级名姓名) -->
<!-- id是方法名parameterType参数类型resultMap结果手动映射 -->
<select id="SelectOneClazz" parameterType="int" resultMap="Myclass">
select clazz_id,clazz_name,stu_name from clazz176 cz left join student176 sd on cz.clazz_id = sd.clz_id
where clazz_id =#{id}
</select>
</mapper>
一对一
<?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.MyBatisFour.xql.Dao.ICardDao">
<!-- 插入一个银行卡对象 -->
<insert id="insertCard" parameterType="Card">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
select card176_seq.nextval from dual
</selectKey>
insert into card176 values(#{id},#{cardNumber},#{address},#{date})
</insert>
</mapper>
在第一次插入卡时候序列给id赋值 了所以后面我们可以取到
<?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.MyBatisFour.xql.Dao.IPersonDao">
<!-- 自定义结果手动映射 -->
<resultMap type="Person" id="MyPerson">
<!-- 主属性 -->
<id column="person_id" property="personId"/>
<!-- 普通属性 -->
<result column="person_name" property="personName"/>
<result column="mobile" property="mobile"/>
<!-- 关系属性 -->
<association property="card" javaType="Card">
<!-- 主属性 -->
<id column="card_id" property="id"/>
<!-- 普通属性 -->
<result column="card_number" property="cardNumber"/>
<result column="address" property="address"/>
<result column="expire" property="date"/>
</association>
</resultMap>
<!-- 根据id查一个 -->
<select id="selectOne" parameterType="int" resultMap="MyPerson">
select person_id,person_name,mobile,card_id,card_number,address,expire from
person176 p left join card176 c on p.card_id=c.id where person_id=#{id}
</select>
<!-- 插入一个Person对象 -->
<insert id="insertCard" parameterType="Person">
<!-- 序列keyProperty对象主属性名 -->
<selectKey keyProperty="personId" resultType="int" order="BEFORE">
select person176_seq.nextval from dual
</selectKey>
insert into person176 values(#{personId},#{personName},#{mobile},#{card.id})
</insert>
</mapper>
多对多
<?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.MyBatisFive.xql.Dao.IStudentDao">
<!-- 自定义手动结果映射ORM -->
<resultMap type="Student" id="MyCourse">
<id column="stu_id" property="stuId"/>
<result column="stu_name" property="stuName"/>
<collection property="courseList" ofType="Course">
<result column="cour_name" property="courName"/>
</collection>
</resultMap>
<!-- 根据ID查看一个学生选了多少门课 -->
<select id="selectOne" parameterType="int" resultMap="MyCourse">
select s.stu_id,s.stu_name,o.cour_name from students176 s left join sc176 c on s.stu_id=c.sid
left join course176 o on c.cid = o.cour_id
where stu_id=#{stuId}
</select>
</mapper>
子查询
动态SQL
局部Sql的复用
where标签
<!-- 模糊查询 -->
<select id="SelectCard" resultType="com.MyBatisFour.xql.Entity.Card">
select id,address,expire,card_number as cardNumber from card176
<where>
<if test="cardNumber!=null and cardNumber!=''">
and card_number like #{cardNumber}
</if>
<if test="address!=null and address!=''">
and address like #{address}
</if>
</where>
</select>
<!-- 模糊查询 -->
<select id="SelectCard" resultType="com.MyBatisFour.xql.Entity.Card">
select id,address,expire,card_number as cardNumber from card176
<trim prefix="where" prefixOverrides="and|or">
<if test="cardNumber!=null and cardNumber!=''">
and card_number like #{cardNumber}
</if>
<if test="address!=null and address!=''">
and address like #{address}
</if>
</trim>
</select>
trim标签
set标签
<!-- 修改指定字段信息 -->
<update id="UpdateCard" parameterType="Card">
update card176
<set>
<if test="cardNumber!=null and cardNumber!=''">
card_number=#{cardNumber},
</if>
<if test="address!=null and address!=''">
address = #{address},
</if>
</set>
where id =#{id}
</update>
<!-- 修改指定字段信息 -->
<update id="UpdateCard" parameterType="Card">
update card176
<trim prefix="set" prefixOverrides=",">
<if test="cardNumber!=null and cardNumber!=''">
card_number=#{cardNumber},
</if>
<if test="address!=null and address!=''">
address = #{address},
</if>
</trim>
where id =#{id}
</update>
foreach标签
<!-- 批量删除 -->
<delete id="DeleteCard" parameterType="java.lang.Integer">
delete from card176 where id in
<foreach collection="list" item="i" open="(" separator="," close=")">
#{i}
</foreach>
</delete>