MyBatis框架的主要配置文件:mybatis-config.xml文件和Mapper.xml文件。
一、Mapper.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.wind.repository.UserRepository">
<resultMap id="userMap" type="com.wind.entity.UserEntity">
<id column="Id" property="id"/>
<result column="Name" property="name"/>
<result column="Age" property="age"/>
<result column="Salary" property="salary"/>
<result column="Sex" property="sex"/>
<result column="Status" property="status"/>
<result column="AddTime" property="addTime"/>
<result column="UpdateTime" property="updateTime"/>
</resultMap>
<sql id="sql_select">
select Id, Name, Age, Salary, Sex, Status, AddTime, UpdateTime from RUN_User
</sql>
</mapper>
1、statement标签:
select、update、delete、insert标签,分别对应查询、更新、删除、新增操作
2、parameterType标签:方法入参类型
(1)基本数据类型:
<select id="queryUser" parameterType="long" resultMap="userMap">
<include refid="sql_select"/>
where status = 1 and id = #{userId}
</select>
(2)String数据类型:
<select id="queryUserByName" parameterType="string" resultMap="userMap">
<include refid="sql_select"/>
where status = 1 and name = #{name}
</select>
(3)包装类,通过id查询:
<select id="queryUserById" parameterType="java.lang.Long" resultMap="userMap">
<include refid="sql_select"/>
where status = 1 and id = #{userId}
</select>
(4)JavaBean参数:
<update id="updateUser" parameterType="userEntity">
update RUN_User set name = #{userEntity.name}, age = #{userEntity.age}, salary = #{userEntity.salary}, sex = #{userEntity.sex}, status = 1
where id = #{userEntity.id}
</update>
3、resultType标签:方法出参类型
(1)基本数据类型:
<select id="queryCount" resultType="int">
select count(id) from RUN_User where status = 1
</select>
(2)包装类型:
<select id="queryCount" resultType="java.lang.Integer">
select count(id) from RUN_User where status = 1
</select>
(3)String类型:
<select id="findNameById" parameterType="int" resultType="java.lang.String">
select name from RUN_User where id = #{id}
</select>
(4)JavaBean类型:
<select id="queryUserById" parameterType="java.lang.Long" resultMap="userMap">
<include refid="sql_select"/>
where status = 1 and id = #{userId}
</select>
4、级联查询
4.1 建表:班级表与学生表
CREATE TABLE `RUN_Class` (
`Id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID(班级)',
`ClassName` varchar(256) NOT NULL COMMENT '班级名称',
`Status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '有效=1,无效=-1',
`AddTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
`UpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB COMMENT='班级信息表';
CREATE TABLE `RUN_Student` (
`Id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID(学号)',
`Name` varchar(256) NOT NULL DEFAULT '' COMMENT '姓名',
`ClassId` int(11) NOT NULL COMMENT '班级',
`Status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '有效=1,无效=-1',
`AddTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
`UpdateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB COMMENT='学生信息表';
4.2 创建实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class ClassEntity implements Serializable {
private static final long serialVersionUID = -3337804323389088625L;
private int id; //班级ID
private String className; //班级名称
private int status; //是否有效(1:有效,-1:无效)
private String addTime; //添加时间
private String updateTime; //更新时间
private List<StudentEntity> studentEntities; //该班级中有哪些学生
}
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class StudentEntity implements Serializable {
private static final long serialVersionUID = -7497520016303190017L;
private int id; //学号
private String name; //姓名
private int classId; //班级
private int status; //是否有效(1:有效,-1:无效)
private String addTime; //添加时间
private String updateTime; //更新时间
private ClassEntity classEntity; //该学生属于哪个班级
}
4.3 写DAO层接口
public interface StudentRepository {
StudentEntity queryStudent(@Param("id") int id);
StudentEntity queryStudentWithClass(@Param("id") int id);
}
4.4 写Mapper.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.wind.repository.StudentRepository">
<resultMap id="studentMap" type="com.wind.entity.StudentEntity">
<result column="Id" property="id"/>
<result column="Name" property="name"/>
<result column="ClassId" property="classId"/>
<result column="Status" property="status"/>
<result column="AddTime" property="addTime"/>
<result column="UpdateTime" property="updateTime"/>
</resultMap>
<resultMap id="studentMap2" type="com.wind.entity.StudentEntity">
<result column="Id" property="id"/>
<result column="Name" property="name"/>
<result column="ClassId" property="classId"/>
<result column="Status" property="status"/>
<result column="AddTime" property="addTime"/>
<result column="UpdateTime" property="updateTime"/>
<association property="classEntity" javaType="classEntity">
<id column="cId" property="id"/>
<result column="cClassName" property="className"/>
<result column="cStatus" property="status"/>
</association>
</resultMap>
<sql id="sql_select">
select Id, Name, ClassId, Status, AddTime, UpdateTime from RUN_Student
</sql>
<select id="queryStudent" parameterType="int" resultMap="studentMap">
<include refid="sql_select"/>
where id = #{id} and status = 1
</select>
<select id="queryStudentWithClass" parameterType="int" resultMap="studentMap2">
select r.Id, r.Name, r.ClassId, r.Status, r.AddTime, r.UpdateTime, c.id as cid, c.ClassName as cClassName, c.Status as cStatus
from RUN_Student r join RUN_Class c on r.classId = c.id
where r.id = #{id}
</select>
</mapper>
4.5 把Mapper.xml文件注册到MyBatis配置文件中
<?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>
<typeAliases>
<package name="com.wind.entity"/>
</typeAliases>
<!--配置mybatis运行环境-->
<environments default="development">
<environment id="development">
<!--配置JDBC事务管理-->
<transactionManager type="JDBC"></transactionManager>
<!--配置POOLED类型的JDBC数据源连接池-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url"
value="jdbc:mysql://localhost:3306/RUNOOB?useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="admin0001112"/>
</dataSource>
</environment>
</environments>
<!--注册mapper文件-->
<mappers>
<mapper resource="com/entity/mapper/UserMapper.xml"/>
<mapper resource="com/entity/mapper/UserRepository.xml"/>
<mapper resource="com/entity/mapper/StudentRepository.xml"/>
</mappers>
</configuration>
4.6 一对多查询测试(从一到多):不带java对象
public class UserTest3 {
public static void main(String[] args) {
//加载MyBatis配置文件
InputStream inputStream = UserTest3.class.getClassLoader().getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
//获取实现接口的代理对象
StudentRepository studentRepository = sqlSession.getMapper(StudentRepository.class);
StudentEntity studentEntity = studentRepository.queryStudent(2);
System.out.println(studentEntity);
sqlSession.close();
}
}
4.7 一对多查询测试(从一到多):带java对象
4.8 一对多查询测试(从多到一)
public interface ClassRepository {
ClassEntity queryClassByClassId(@Param("id") int 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.wind.repository.ClassRepository">
<resultMap id="classStudentMap" type="com.wind.entity.ClassEntity">
<result column="Id" property="id"/>
<result column="ClassName" property="className"/>
<result column="Status" property="status"/>
<result column="AddTime" property="addTime"/>
<result column="UpdateTime" property="updateTime"/>
<collection property="studentEntities" ofType="com.wind.entity.StudentEntity">
<result column="sId" property="id"/>
<result column="sName" property="name"/>
<result column="sClassId" property="classId"/>
<result column="sStatus" property="status"/>
<result column="sAddTime" property="addTime"/>
<result column="sUpdateTime" property="updateTime"/>
</collection>
</resultMap>
<sql id="sql_select_join_student">
select c.Id, c.ClassName, c.Status, c.AddTime, c.UpdateTime,
s.Id as sId, s.Name as sName, s.ClassId as sClassId, s.Status as sStatus, s.AddTime as sAddTime, s.UpdateTime as sUpdateTime
from RUN_Class c join RUN_Student s on c.Id = s.classId
</sql>
<select id="queryClassByClassId" parameterType="int" resultMap="classStudentMap">
<include refid="sql_select_join_student"/>
where c.id = #{id} and c.status = 1 and s.status =1
</select>
</mapper>
public class UserTest3 {
public static void main(String[] args) {
//加载MyBatis配置文件
InputStream inputStream = UserTest3.class.getClassLoader().getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
//获取实现接口的代理对象
ClassRepository classRepository = sqlSession.getMapper(ClassRepository.class);
ClassEntity classEntity = classRepository.queryClassByClassId(3);
System.out.println(classEntity);
sqlSession.close();
}
}
4.9 多对多查询:
(1)实体类 顾客:Customer。实体类 商品:Goods。
@Data
public class Customer {
private long id;
private String name;
private List<Goods> goods;
}
@Data
public class Goods {
private long id;
private String name;
private List<Customer> customers;
}
(2)中间表:用来存储顾客和商品之间的关系
@Data
public class Custom_Goods {
private long id;
private long cid;
private long gid;
}
(3)顾客接口:CustomerRepository
public interface CustomerRepository {
public Customer findById(long id);
}
(4)顾客SQL语句:CustomerRepository.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.wind.repository.CustomerRepository">
<resultMap id="customerMap" type="customer">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
<collection property="goods" ofType="goods">
<id column="gid" property="id"/>
<result column="gname" property="name"/>
</collection>
</resultMap>
<select id="findById" parameterType="long" resultMap="customerMap">
select c.id cid,c.name cname,g.id gid,g.name gname from customer c,goods
g,customer_goods cg where c.id = #{id} and cg.cid = c.id and cg.gid = g.id
</select>
</mapper>
(5)商品接口:GoodsRepository
public interface GoodsRepository {
public Goods findById(long id);
}
(6)商品SQL语句:GoodsRepository.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.wind.repository.GoodsRepository">
<resultMap id="goodsMap" type="gGoods">
<id column="gid" property="id"></id>
<result column="gname" property="name"></result>
<collection property="customers" ofType="customer">
<id column="cid" property="id"/>
<result column="cname" property="name"/>
</collection>
</resultMap>
<select id="findById" parameterType="long" resultMap="goodsMap">
select c.id cid,c.name cname,g.id gid,g.name gname from customer c,goods
g,customer_goods cg where g.id = #{id} and cg.cid = c.id and cg.gid = g.id
</select>
</mapper>