1. Mybatis基本实现
1.1 代码实现
1.1.1 步骤
第一步: 导入依赖
<dependencies>
<!--mybatis 依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.5</version>
</dependency>
<!--mysql 驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<!--junit 单元测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!-- 添加slf4j日志api -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.20</version>
</dependency>
<!-- 添加logback-classic依赖 -->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.3</version>
</dependency>
<!-- 添加logback-core依赖 -->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-core</artifactId>
<version>1.2.3</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
</dependencies>
第二步: 编写核心配置文件
<?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>
<environments default="default">
<environment id="default">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://IP:端口/数据库名?characterEncoding=utf-8&useSSL=false"/>
<property name="username" value="用户名"/>
<property name="password" value="密码"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--包扫描:mapper包名-->
<package name="mapper包名"/>
</mappers>
</configuration>
第三步: 编写接口
public interface UserMapper {
/*模糊查询*/
List<User> findByUsername(@Param("username") String username);
}
第四步: 编写sql映射文件 UserMapper.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="link.xiaomo.mybatisdemo.mapper.UserMapper">
<!--手动映射字段-->
<resultMap id="brandMap" type="link.xiaomo.demo.pojo.Brand" autoMapping="true">
<!--id不能省略-->
<id property="id" column="id" />
<!--配置名字不同的,其他的通过配置resultMap的autoMapping属性为true,自动生成-->
<result property="brandName" column="brand_name"/>
<result property="companyName" column="company_name"/>
</resultMap>
<!--字段列表-->
<sql id="brandColumns">
id, -- id
brand_name , -- 品牌名
company_name , -- 公司名
ordered, -- 排序
description, -- 描述
status -- 状态
</sql>
<!--模糊查询-->
<select id="findByUsername"?resultMap="brandMap">
select * from tb_user where username like concat('%',#{username},'%')
</select>
</mapper>
第五步: 回顾核心代码
@Before
public void before() throws IOException {
factory = new SqlSessionFactoryBuilder()
.build(Resources.getResourceAsStream("sqlMapConfig.xml"));
}
@After
public void after(){
sqlSession.commit();
sqlSession.close();
}
@Test
public void testFindAll() throws IOException {
sqlSession = factory.openSession();
//代理dao
//基于动态代理实现的
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
System.out.println(mapper.findAll()); //=>link.xiaomo.mybatisdemo.mapper.UserMapper.findAll
}
额外:日志配置文件logback.xml
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<appender name="CONSOLE" class="ch.qos.logback.core.ConsoleAppender">
<target>System.out</target>
<encoder>
<!-- 日志输出格式:%d表示日期时间,%-5level:日志级别 ,%c取类 %thread表示线程名(在哪个方法执行),
%msg:日志消息,%n是换行符-->
<pattern>%d{yyyy-MM-dd HH:mm:ss.SSS}[%-5level] %c [%thread] :%msg%n</pattern>
</encoder>
</appender>
<!-- 滚动记录文件,先将日志记录到指定文件,当符合某个条件时,将日志记录到其他文件
%logger{36}表示logger是class的全名,36表示限制最长字符
-->
<appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
<encoder>
<pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{36}-%msg%n</pattern>
<charset>utf-8</charset>
</encoder>
<!-- 日志输出路径 -->
<file>D:/xiaomo/code/logs/mybatis_demo4.log</file>
<!-- 指定日志文件拆分和压缩规则 -->
<rollingPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedRollingPolicy">
<!-- 通过指定压缩文件的名称,来确定分割文件方式 -->
<fileNamePattern>D:/xiaomo/code/logs/bak/mybatis_demo4_bak-%d{yyyy-MMdd}.log%i.gz</fileNamePattern>
<!-- 文件拆分大小 -->
<maxFileSize>1MB</maxFileSize>
</rollingPolicy>
</appender>
<!-- level:用来设计打印级别,大小写无关:TRACE,DEBUG,INFO,WARN,ERROR,ALL和OFF,默认debug
<root>可以包含零个或多个<appender-ref>元素,标识这个输出位置将会被本日志级别控制。
-->
<root level="DEBUG">
<!-- 注意:如果这里不配置关联打印位置,该位置将不会记录日志 -->
<appender-ref ref="CONSOLE"/>
<appender-ref ref="FILE"/>
</root>
</configuration>
1.1.2 动态代理的回顾
代理dao开发底层用到的是动态代理
前提条件: 我们如果使用jdk的动态代理,必须提供接口!!!
我们的代理对象是动态创建,在运行时才会创建
接口
public interface Person {
void rent();
}
实现类
public class FangDong implements Person{
@Override
public void rent() {
System.out.println("房东有房,别墅,1000出租!!!");
}
}
动态代理核心代码
public class Main {
public static void main(String[] args) {
// 房东
FangDong fangDong = new FangDong();
// 中介代理
// 动态代理
// 我们的目的 为了在不修改原有类的基础上对目标方法进行增强
Person person = (Person) Proxy.newProxyInstance(
fangDong.getClass().getClassLoader(), //类加载器
fangDong.getClass().getInterfaces(), // 继承结构
// 具体增强的逻辑
new InvocationHandler() {
@Override
public Object invoke(Object proxy, Method method,
Object[] args) throws Throwable {
// method => 目标要执行的方法
// args => 目标方法执行的时候传递的参数
Object result = method.invoke(fangDong,args); // => fangdong.rent();
System.out.println("中介额外加了2000块钱准备出租");
System.out.println("中介要收半个月的中介费");
return result;
}
}
);
person.rent();
}
}
1.2 代码优化
1.2.1 xml包扫描优化
编写 SQL 映射文件,将其在模块的
resources
目录下,并按mapper映射接口的同级创建目录。【重要!!】在resources上递归创建多级文件夹的时候,文件夹层级之间应该以/分隔。
创建以后,需要修改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>
<environments default="default">
<environment id="default">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3309/mybatis?characterEncoding=utf-8&useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!--此处改为扫描包方式-->
<mappers>
<package name="link.xiaomo.demo.mapper"/>
</mappers>
</configuration>
1.2.2 解决实体类属性和表字段不匹配
方法一:使用resultMap,手动指定每个字段的映射
<resultMap id="brandMap" type="link.xiaomo.demo.pojo.Brand" autoMapping="true">
<!--id不能省略-->
<id property="id" column="id" />
<!--配置名字不同的,其他的通过配置resultMap的autoMapping属性为true,自动生成-->
<result property="brandName" column="brand_name"/>
<result property="companyName" column="company_name"/>
</resultMap>
<!--返回值类型选为resultMap,值为上面定义的resultMap的id-->
<select id="findAll" resultMap="brandMap">
select
id, -- id
brand_name , -- 品牌名
company_name , -- 公司名
ordered, -- 排序
description, -- 描述
status -- 状态
from tb_brand
</select>
方式二:查询的时候,给查询的字段起别名
<select id="findAll" resultType="link.xiaomo.demo.pojo.Brand">
select
id, -- id
brand_name brandName, -- 品牌名
company_name companyName, -- 公司名
ordered, -- 排序
description, -- 描述
status -- 状态
from tb_brand
</select>
1.2.3 \${}和#{}区别
${}底层使用的是statement执行,相当于把参数直接通过字符串拼接的方式,拼接进Sql,所以存在Sql注入的风险。且在使用的时候,需要在\${}的外侧加上单引号( ‘
)。
#{}底层使用的是preparedStatement执行,相当于先将#{}变成占位符?
,之后在传入参数替换,替换的过程会过滤敏感字符,所以避免了sql注入的风险。在执行的时候,程序会自动在参数两侧加单引号。
1.2.4 插入数据时返回主键
在执行insert语句的时候,在xml中的insert标签添加两个属性,分别是
useGeneratedKeys="true"
和 keyProperty="id"
,分别表示“使用返回主键”和“将返回的id赋值给哪个参数”
<insert id="add" useGeneratedKeys="true" keyProperty="id" >
insert into tb_brand (brand_name,company_name,ordered,description,status)
values (#{brandName},#{companyName},#{ordered},#{description},#{status]);
</insert>
1.2.5 @Param命名参数
当Mapper接口使用多个参数的时候,应该对方法的参数使用@Param("参数名")进行修饰。例:
Brand findByCompanyNameAndBrandName(@Param("companyName") String companyName,
@Param("brandName") String brandName);
1.2.6 lombok使用
依赖中添加lombok包,通过在实体类上添加注解,可以实现在编译时自动生成代码的功能
注解 | 功能 |
---|---|
@Data | 自动生成get/set/toString/equals/hashCode方法 |
@AllArgsConstructor | 自动生成全参构造 |
@NoArgsConstructor | 自动生成无参构造 |
@Builder | 自动生成构造者模式,以实体类AAA为例: AAA aaa = AAA.builder().属性1(值1).属性2(值2)....属性n(值n).build(); |
- IDEA需要安装lombok插件才能正常使用lombok相关功能,高版本IDEA自带此插件
1.3 动态sql
【注意事项!】
-
动态sql中, 如果if判断的条件是判断某个字符串是否为空,则需要判断非null且非空字符串。if中的条件的
and
不能大写\<if test="字段 != null and 字段 != '' >\
-
不管是if还是sql中,如果需要写大于小于号,使用转义符
>
(大于),<
(小于)
1.3.1 sql标签 + include标签
用于封装SQL中重复内容,一般用于包含表的字段
<!--定义-->
<sql id="brandColumns">
id, -- id
brand_name brandName, -- 品牌名
company_name companyName, -- 公司名
ordered, -- 排序
description, -- 描述
status -- 状态
</sql>
<!--执行-->
<select id="findAll" resultType="link.xiaomo.demo.pojo.Brand">
select
<!-- 【此处引用上面sql标签的内容】-->
<include refid="brandColumns"></include>
from tb_brand
</select>
1.3.2 where标签 + if标签
用于根据条件动态加入判断提交
<select id="findByCondition" resultMap="brandMap">
select
<include refid="brandColumns" />
from tb_brand
<where>
<if test="status != null">
and status = #{status}
</if>
<if test="companyName != null and companyName != ''">
and company_name like concat('%',#{companyName},'%')
</if>
<if test="brandName != null and brandName != ''">
and brand_name like concat('%',#{brandName},'%')
</if>
</where>
</select>
1.3.3 where + choose + when + otherwise标签
用于条件几选一的情况
<select id="findByConditionSingle" resultMap="brandMap">
select
<include refid="brandColumns" />
from tb_brand
<where>
<choose>
<when test="status != null">
and status = #{status}
</when>
<when test="companyName != null and companyName != ''">
and company_name like concat('%',#{companyName},'%')
</when>
<when test="brandName != null and brandName != ''">
and brand_name like concat('%',#{brandName},'%')
</when>
<otherwise>
<!--其他情况-->
</otherwise>
</choose>
</where>
</select>
1.3.4 foreach标签
多用于 in (a,b,c) 语句
collection:给集合命名 open:开头拼接什么 close:结尾拼接什么
item:给循环的每个元素命名 separator:元素间用什么分隔
<select id="findByIdList" resultMap="brandMap">
select
<include refid="brandColumnsBase"></include>
from tb_brand
<foreach collection="list" open="where id in (" close=")" item="each" separator=",">
#{each}
</foreach>
</select>
1.3.5 set标签 + if标签
用于更新语句,根据条件动态更新
<update id="updateById">
update store
<set>
<if test='shopOwner != null and shopOwner != ""'>
shop_owner = #{shopOwner},
</if>
<if test='idNumber != null and idNumber != ""'>
id_number = #{idNumber},
</if>
<if test='name != null and name != ""'>
name = #{name},
</if>
<if test='industry != null and industry != ""'>
industry = #{industry},
</if>
<if test='area != null and area != ""'>
area = #{area},
</if>
<if test='phone != null and phone != ""'>
phone = #{phone},
</if>
<if test='status != null'>
status = #{status},
</if>
<if test='auditTime != null'>
audit_time = #{auditTime}
</if>
</set>
where id = #{id}
</update>
2. 注解开发
@Select
@Insert
@Update
@Delete
@results
2.1 注解开发MyBatis步骤
2.1.1 第一步 配置核心配置文件 sqlMapConfig.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>
<environments default="default">
<environment id="default">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED" >
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql:///mybatis?characterEncoding=utf-8&useSSL=false" />
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
<mappers>
<package name="link.xiaomo.mybatisanno.mapper"/>
</mappers>
</configuration>
2.1.2 第二步 编写UserDao
public interface UserMapper {
@Select("select * from tb_user")
public List<User> findAll();
@Select("select * from tb_user where id = #{id}")
User findById(Integer id);
@Insert("insert into tb_user values (null,#{username},#{password},#{gender},#{addr})")
void addUser(User user);
@Update("update tb_user set password=#{password} where id = #{id}")
void updateUser(User user);
@Delete("delete from tb_user where id = #{id}")
void delById(Integer id);
}
2.1.3 第三步 编写测试代码
public class UserMapperTest {
private SqlSession sqlSession;
private UserMapper userMapper;
@Before
public void before() throws Exception {
sqlSession = new SqlSessionFactoryBuilder()
.build(Resources.getResourceAsStream("sqlMapConfig.xml"))
.openSession();
userMapper = sqlSession.getMapper(UserMapper.class);
}
@After
public void after() throws Exception {
sqlSession.commit();
sqlSession.close();
}
@Test
public void testFindAll() throws IOException {
System.out.println(userMapper.findAll());
}
@Test
public void findById() {
System.out.println(userMapper.findById(1));
}
@Test
public void addUser() {
User user = User.builder()
.username("tom")
.password("admin")
.gender("男")
.addr("济南历下区")
.build();
userMapper.addUser(user);
}
@Test
public void updateUser() {
//改用户密码
User user = User.builder()
.id(5)
.password("123")
.build();
userMapper.updateUser(user);
}
@Test
public void delById() {
userMapper.delById(5);
}
}
2.2 在注解中如何手动映射列名和成员变量
参考resultMap标签
\<resultMap => @Results
\<id / result => @Result
? 主键列 @Result(id = true,property = "id",column = "id")
? 普通列 @Result(property = "brandName",column = "brand_name")
public interface BrandMapper {
/*
@Results => <resultMap
@Result => <id <result
如何区分主键和其他列
看id的值 true => 主键 false => 其他列
*/
@Results({
@Result(id = true,column = "id",property = "id"),
@Result(column = "brand_name",property ="brandName"),
@Result(column = "company_name",property ="companyName")
})
@Select("select * from tb_brand where id = #{id}")
public Brand findById(Integer id);
}
2.3 在注解开发中如何使用动态sql(自己扩展)
要在带注解的映射器接口类中使用动态 SQL,可以使用 script 元素.
public interface BrandDao {
@Update({"<script>",
"UPDATE tb_brand",
" <set>",
" <if test='brandName != null'>brand_name=#{brandName},</if>",
" <if test='companyName != null'>company_name=#{companyName},</if>",
" <if test='ordered != null'>ordered=#{ordered},</if>",
" <if test='description != null'>description=#{description}</if>",
" <if test='status != null'>status=#{status}</if>",
" </set>",
"where id=#{id}",
"</script>"})
void updateBrandById(Brand brand);
}
3. MyBatis完成xml多表开发
3.1 一对一
表的映射关系
3.1.1 方式一(2条sql方式完成查询)
目标 : 通过学生查询对应的班级信息
-- 根据id查询学生的信息
select * from student where sid = 1
-- 根据学生的cno去查询班级信息
select * from classes where cno = 上面查询的学生的班级id
接口
//StudentMapper
Student findStuAndClassesBySid(Integer sid);
//ClassesMapper
Classes findClassByStudentCno(Integer cno);
映射配置文件
<!--StudentMapper.xml-->
<resultMap id="stu2ClazzMap" type="link.xiaomo.multitable.pojo.Student" autoMapping="true">
<id property="sid" column="sid"></id>
<!--
association:当我们需要的结果是一个对象的时候我们使用association标签
1.property => mybaits执行另外一条查询结果封装到哪个对象中
2.javaType => 目标对象的数据类型
3.column => 相关的列(主键或外键)
4.select => 目标的查询
-->
<association property="clazz"
javaType="link.xiaomo.multitable.pojo.Classes"
column="cno"
select="link.xiaomo.multitable.mapper.ClassesMapper.findClassByStudentCno"/>
</resultMap>
<select id="findStuAndClassesBySid" resultMap="stu2ClazzMap">
select * from student where sid = #{sid}
</select>
<!--ClassesMapper.xml-->
<select id="findClassByStudentCno" resultType="link.xiaomo.multitable.pojo.Classes">
select * from classes where cid = #{cno}
</select>
resultMap标签: 手动映射我们表的列和java中成员变量的关系,当我们要执行多表查询时,我们就需要通过resultMap标签来手动对多表的结果进行封装
association标签: 当我们明确查询关系是一对一的情况,那么这时候我们就要使用association标签,作用就是将查询的另一方单个的数据封装到咱们成员变量中 把Classes信息封装到Student类中的classes成员变量中
association标签属性 \
property
=> Student类中定义的对象的名称 classes 注意这里的名字写的是成员变量名称
javaType
=> 我们查询到的结果封装到集合中,集合中需要接受的是什么类型
column
=> 多表关系之间的关联条件,classes表和student表产生关联的就是cno列
select
=> 我们要封装的集合数据从哪里来,需要封装的classes对象来自于那条sql
<association property="clazz"
javaType="link.xiaomo.multitable.pojo.Classes"
column="cno"
select="link.xiaomo.multitable.mapper.ClassesMapper.findClassByStudentCno"/>
测试代码
public class StudentMapperTest {
private SqlSession sqlSession;
private StudentMapper studentMapper;
@Before
public void before() throws Exception {
sqlSession = new SqlSessionFactoryBuilder()
.build(Resources.getResourceAsStream("sqlMapConfig.xml"))
.openSession();
studentMapper = sqlSession.getMapper(StudentMapper.class);
}
@After
public void after() throws Exception {
sqlSession.commit();
sqlSession.close();
}
@Test
public void findStuAndClassesBySid() {
System.out.println(studentMapper.findStuAndClassesBySid(1));
}
}
3.1.2 方式二(1条sql方式完成查询)
接口
public interface StudentMapper {
Student findStudentAndClazzBySid(Integer sid);
}
映射配置文件
<?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="link.xiaomo.demo.mapper.StudentMapper">
<resultMap id="student2ClazzMap" type="link.xiaomo.demo.pojo.Student" autoMapping="true">
<id column="sid" property="sid" />
<association property="clazz" javaType="link.xiaomo.demo.pojo.Classes" autoMapping="true">
<id column="cid" property="cid"/>
</association>
</resultMap>
<select id="findStudentAndClazzBySid" resultMap="student2ClazzMap">
select *
from student s
inner join classes c on s.cno = c.cid
where sid = #{sid}
</select>
</mapper>
测试代码
public class StudentMapperTest {
private SqlSession sqlSession;
private StudentMapper studentMapper;
@Before
public void before() throws Exception {
sqlSession = new SqlSessionFactoryBuilder()
.build(Resources.getResourceAsStream("sqlMapConfig.xml"))
.openSession();
studentMapper = sqlSession.getMapper(StudentMapper.class);
}
@After
public void after() throws Exception {
sqlSession.commit();
sqlSession.close();
}
@Test
public void testFindClassAndStu(){
Student s = studentMapper.findStudentAndClazzBySid(1);
System.out.println(s);
}
}
3.2 一对多
3.2.1 方式一(2条sql方式完成查询)
目标 : 通过班级查询对应的学生信息
select * from classes where cid = 1;
select * from student where cno = 上面的班级的id;
接口
//ClassesMapper
Classes findClassByCid(Integer cid);
//StudentMapper
List<Student> findStuListByCno(Integer cno);
映射配置文件
<!-- ClassesMapper.xml -->
<resultMap id="classes2StuMap" type="link.xiaomo.multitable.pojo.Classes" autoMapping="true">
<!--封装cid-->
<id column="cid" property="cid"></id>
<result column="cname" property="cname" />
<!--当我们设计到封装其他表的数据的时候
如果是对多的情况下 我们会使用 collection 标签来封装
property : 我们回头查询到的数据封装到谁里面 => stuList
ofType : 集合里面放的是什么类型
column : 两个表之间的连接关系(主键 / 外键)
select : 执行的sql是谁 => namespace + "." + id
-->
<collection property="stuList"
ofType="link.xiaomo.multitable.pojo.Student"
column="cid"
select="link.xiaomo.multitable.mapper.StudentMapper.findStuListByCno"
/>
</resultMap>
<!-- StudentMapper.xml -->
<select id="findStuListByCno" resultType="link.xiaomo.multitable.pojo.Student">
select * from student where cno = #{cno}
</select>
-
resultMap标签: 手动映射我们表的列和java中成员变量的关系,当我们要执行多表查询时,我们就需要通过resultMap标签来手动对多表的结果进行封装
-
collection标签: 当我们明确查询关系是一对多的情况,那么这时候我们就要使用* collection标签,作用就是将查询的另一方多个的数据封装到咱们成员变量中 **把
-
collection标签属性 \
应用在对多的场景里
property
=> Classes类中定义的集合的名称 stuList,注意这里的名字写的是成员变量名称
ofType
=> 我们查询到的结果封装到集合中,集合中需要接受的是什么类型
column
=> 多表关系之间的关联条件,classes表和student表产生关联的就是cid列
select
=> 我们要封装的集合数据从哪里来,需要封装的stuList集合来自于那条sql
<collection property="stuList"
ofType="link.xiaomo.multitable.pojo.Student"
column="cid"
select="link.xiaomo.multitable.mapper.StudentMapper.findStuListByCno"
/>
测试代码
public class ClassesMapperTest {
private SqlSession sqlSession;
private ClassesMapper classesMapper;
@Before
public void before() throws Exception {
sqlSession = new SqlSessionFactoryBuilder()
.build(Resources.getResourceAsStream("sqlMapConfig.xml"))
.openSession();
classesMapper = sqlSession.getMapper(ClassesMapper.class);
}
@After
public void after() throws Exception {
sqlSession.commit();
sqlSession.close();
}
@Test
public void testFindClassesAndStuList2() {
Classes classes = classesMapper.findClassByCid(1);
System.out.println(classes);
}
}
3.2.2 方式二 (1条sql方式完成查询)
目标 : 通过班级查询对应的学生信息
接口
public interface ClassesMapper {
Classes findClazzAndStuListByCid(@Param("cid")Integer cid);
}
映射配置文件
<?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="link.xiaomo.demo.mapper.ClassesMapper">
<resultMap id="clazz2StuMap" type="link.xiaomo.demo.pojo.Classes" autoMapping="true">
<id column="cid" property="cid"/>
<collection property="stuList" autoMapping="true"
ofType="link.xiaomo.demo.pojo.Student"
>
<id column="sid" property="sid"></id>
</collection>
</resultMap>
<select id="findClazzAndStuListByCid" resultMap="clazz2StuMap">
select *
from classes c
inner join student s on c.cid = s.cno
where c.cid = #{cid}
</select>
</mapper>
测试代码
public class ClassesMapperTest {
private SqlSession sqlSession;
private ClassesMapper classesMapper;
private StudentMapper studentMapper;
@Before
public void before() throws Exception {
sqlSession = new SqlSessionFactoryBuilder()
.build(Resources.getResourceAsStream("sqlMapConfig.xml"))
.openSession();
classesMapper = sqlSession.getMapper(ClassesMapper.class);
}
@After
public void after() throws Exception {
sqlSession.commit();
sqlSession.close();
}
@Test
public void testFindClassAndStu(){
Classes c = classesMapper.findClazzAndStuListByCid(1);
System.out.println(c);
}
}
4.3 多对多
方式一(2条sql完成查询)
目标 : 通过课程查询对应的学生信息
select * from course where cid = 1;
SELECT * FROM student WHERE sid IN (SELECT sno FROM stu_cour WHERE cno = 上面查询的学科id)
接口
public interface CourseMapper {
Course findCourseAndStuListByCid(Integer sid);
}
映射配置文件
<?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="link.xiaomo.demo.mapper.CourseMapper">
<resultMap id="clazz2StuMap" type="link.xiaomo.demo.pojo.Course" autoMapping="true">
<id column="cid" property="cid"/>
<collection property="stuList"
ofType="link.xiaomo.demo.pojo.Student"
column="cid"
select="link.xiaomo.demo.mapper.StudentMapper.findStuListByCourseId">
</collection>
</resultMap>
<select id="findCourseAndStuListByCid" resultMap="clazz2StuMap">
select *
from course
where cid = #{cid}
</select>
</mapper>
测试代码
public class CourseMapperTest {
private SqlSession sqlSession;
private ClassesMapper classesMapper;
private CourseMapper courseMapper;
@Before
public void before() throws Exception {
sqlSession = new SqlSessionFactoryBuilder()
.build(Resources.getResourceAsStream("sqlMapConfig.xml"))
.openSession();
courseMapper = sqlSession.getMapper(CourseMapper.class);
}
@After
public void after() throws Exception {
sqlSession.commit();
sqlSession.close();
}
@Test
public void testFindClassAndStu(){
Course c = courseMapper.findCourseAndStuListByCid(1);
System.out.println(c);
}
}
方式二(2条sql完成查询)
目标 : 通过课程查询对应的学生信息
select * from course t1
join stu_cour t2 on t1.cid = t2.cno
join student t3 on t2.sno = t3.sid
where t1.cid = 1
接口
public interface CourseMapper {
Course findCourseAndStuListByCid(Integer sid);
}
映射配置文件
<?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="link.xiaomo.demo.mapper.CourseMapper">
<resultMap id="clazz2StuMap" type="link.xiaomo.demo.pojo.Course" autoMapping="true">
<id column="cid" property="cid"/>
<collection property="stuList" autoMapping="true"
ofType="link.xiaomo.demo.pojo.Student"
>
<id column="sid" property="sid"></id>
</collection>
</resultMap>
<select id="findCourseAndStuListByCid" resultMap="clazz2StuMap">
select *
from course c
inner join stu_cour sc on sc.cno = c.cid
inner join student s on sc.sno = s.sid
where c.cid = #{cid}
</select>
</mapper>
测试代码
public class CourseMapperTest {
private SqlSession sqlSession;
private ClassesMapper classesMapper;
private CourseMapper courseMapper;
@Before
public void before() throws Exception {
sqlSession = new SqlSessionFactoryBuilder()
.build(Resources.getResourceAsStream("sqlMapConfig.xml"))
.openSession();
courseMapper = sqlSession.getMapper(CourseMapper.class);
}
@After
public void after() throws Exception {
sqlSession.commit();
sqlSession.close();
}
@Test
public void testFindClassAndStu(){
Course c = courseMapper.findCourseAndStuListByCid(1);
System.out.println(c);
}
}
Comments NOTHING