来源:https://www.bilibili.com/video/BV1V7411w7VW?p=3
上一节:https://blog.csdn.net/qq_40893824/article/details/107322689
下一节:https://blog.csdn.net/qq_40893824/article/details/107371171
目录
接上一节 Mapper
1 statement 标签
insert | 增 |
---|---|
update | 改 |
delete | 删 |
select | 查 |
2 parameterType
2.1 基本数据类型
id 查询 Account
<select id="findById" parameterType="long"
resultType="com.southwind.entity.Account">
select * from t_account where id = #{id}
</select>
2.2 String 类型
name 查询 Account
1 接口 repository/ AccountRepository 中,添加代码:
public Account findByName(String username);
2 xml文件 repository/ AccountRepository 中,添加代码:
<select id="findByName" parameterType="String" resultType="com.southwind.entity.Account">
select * from t_account where username = #{username}
</select>
![](https://i-blog.csdnimg.cn/blog_migrate/3f85361627efeaea5d4598e7ce632a84.png)
3 test/ Test2 中,添加代码:
// findByName
System.out.println(accountRepository.findByName("张三"));
sqlSession.close();
![](https://i-blog.csdnimg.cn/blog_migrate/913cbbd4ec8429ed8baae81cfff1b5a4.png)
运行:
2.3 包装类
通过 id 查询 Account
优点:数据为空,不会抛异常,基本数据 就会抛异常
1 接口 repository/ AccountRepository 中,添加代码:
public Account findById2(Long id);
2 repository/ AccountRepository.xml 中,添加代码:
<select id="findById2" parameterType="java.lang.Long" resultType="com.southwind.entity.Account">
select * from t_account where id = #{id}
</select>
![](https://i-blog.csdnimg.cn/blog_migrate/3f1b04742fece2424f2b99caa93bb7f9.png)
3 test/ Test2 中,添加代码:
// 包装类
Long id = Long.parseLong("1");
System.out.println(accountRepository.findById2(id));
sqlSession.close();
Long id = Long.parseLong("1");
不是 Long id = Long.parseLong("1L");
!
运行:
2.4 多参数
name 和 age 查询 Account
1 接口 repository/ AccountRepository 中,添加代码:
public Account findByNameAndAge(String name,int age);
2 repository/ AccountRepository.xml 中,添加代码:
<select id="findByNameAndAge" resultType="com.southwind.entity.Account">
select * from t_account where username=#{arg0} and age=#{arg1}
</select>
![](https://i-blog.csdnimg.cn/blog_migrate/e705ee06fda4f6983bea4808c5471875.png)
3 test/ Test2 中,添加代码:
// 多参数 name、age
System.out.println(accountRepository.findByNameAndAge("张三",22));
sqlSession.close();
![](https://i-blog.csdnimg.cn/blog_migrate/9b64016ea4f137c4af7b04339c27f460.png)
运行:
2.5 Java Bean
javaBean 就是实体类
<update id="update" parameterType="com.southwind.entity.Account">
update t_account set username=#{username},password=#{password},age=#{age} where id=#{id}
</update>
3 resultType
3.1 基本数据类型
统计 Account 总数
1 接口 repository/ AccountRepository 中,添加代码:
public int count();
2 repository/ AccountRepository.xml 中,添加代码:
<select id="count" resultType="int">
select count(id) from t_account
</select>
![](https://i-blog.csdnimg.cn/blog_migrate/bb908e612af61b8f6870ce5af3ec528b.png)
3 test/ Test2 中,添加代码:
// Account 的个数
System.out.println(accountRepository.count());
sqlSession.close();
![](https://i-blog.csdnimg.cn/blog_migrate/215a4fe05ea6276e6c08e2cce4946e47.png)
运行:
3.2 包装类
统计 Account 总数
1 接口 repository/ AccountRepository 中,添加代码:
public Integer count2();
2 repository/ AccountRepository.xml 中,添加代码:
<select id="count2" resultType="java.lang.Integer">
select count(id) from t_account
</select>
![](https://i-blog.csdnimg.cn/blog_migrate/260cd5c22ea71b12a0efdd60a31ae8ce.png)
3 test/ Test2 中,添加代码:
// 包装类 Account 的个数
System.out.println(accountRepository.count2());
sqlSession.close();
![](https://i-blog.csdnimg.cn/blog_migrate/af546d0e86887da58100cb4739186b2c.png)
运行:
3.3 String 类型
通过 id 查询 Account 的 name
1 接口 repository/ AccountRepository 中,添加代码:
public String findNameById(long id);
2 repository/ AccountRepository.xml 中,添加代码:
<select id="findNameById" resultType="java.lang.String">
select username from t_account where id=#{id}
</select>
![](https://i-blog.csdnimg.cn/blog_migrate/275f4445a5d0f6ba338087a7785aa980.png)
3 test/ Test2 中,添加代码:
// String 类型 通过id查name
System.out.println(accountRepository.findNameById(1L));
sqlSession.close();
![](https://i-blog.csdnimg.cn/blog_migrate/20d707de28b936728035529fea5dc678.png)
运行:
3.4 Java Bean
<select id="findById" parameterType="long" resultType="com.southwind.entity.Account">
select * from t_account where id=#{id}
</select>
4 级联查询
4.1 1对多
4.1.1 新建数据库
![](https://i-blog.csdnimg.cn/blog_migrate/f22b07b8a8aec2239a670a928c06263d.png)
学生、班级就是1对多
创班级表:
use mybatis;
create table classes(
id smallint primary key ,
name varchar(12)
)
创学生表:
use mybatis;
create table student(
id smallint primary key ,
name varchar(12) ,
cid smallint ,
foreign key(cid) references classess(id) on update cascade
)
手动添加数据后:
4.1.2 简单查询
1 entity 中,新建 实体类 Student,加入代码:
package com.southwind.entity;
import lombok.Data;
@Data
public class Student {
private long id;
private String name;
private Classes classes;
}
![](https://i-blog.csdnimg.cn/blog_migrate/c200a7926e820fe06187ccdf55648fd1.png)
2 entity 中,新建 实体类 Classes,加入代码:
package com.southwind.entity;
import lombok.Data;
import java.util.List;
@Data
public class Classes {
private long id;
private String name;
private List<Student> students;
}
![](https://i-blog.csdnimg.cn/blog_migrate/b23b791a38ad1ec6aefb45e46370ff54.png)
3 repository 中,新建 接口 StudentRepository,加入代码:
public Student findById(long id);
4 repository 中,新建 StudentRepository.xml 文件类型,加入代码(可以粘贴 AccountRepository.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.southwind.repository.StudentRepository">
<select id="findById" parameterType="long" resultType="com.southwind.entity.Student">
select s.id, s.name, c.id , c.name from student s,classes c
where s.id=#{id} and s.cid=c.id
</select>
</mapper>
![](https://i-blog.csdnimg.cn/blog_migrate/920eaa5891a67c0a4014834a07783543.png)
5 StudentRepository.xml 注册到 resources/ config.xml 中,config.xml 中加入代码:
<!-- 注册 StudentRepository.xml -->
<mapper resource="com/southwind/repository/StudentRepository.xml"></mapper>
![](https://i-blog.csdnimg.cn/blog_migrate/de1601c45590af5c2164006f16671d9f.png)
6 test 中,新建 类 Test3,加入代码:
package com.southwind.test;
import com.southwind.repository.StudentRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
public class Test3 {
public static void main(String[] args) {
InputStream inputStream = Test.class.getClassLoader().getResourceAsStream("config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
StudentRepository studentRepository = sqlSession.getMapper(StudentRepository.class);
System.out.println(studentRepository.findById(1L));
sqlSession.close();
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/57b0fe95c42ba84bee7b224687691927.png)
运行 Test3:
4.1.3 加入级联
上面看到,其查询结果 没有 classes,现在加入:
repository/ StudentRepository.xml 中,加入代码:
<resultMap id="StudentMap" type="com.southwind.entity.Student">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<association property="classes" javaType="com.southwind.entity.Classes">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
</association>
</resultMap>
resultMap="StudentMap"
其中:
<resultMap id="自定义" type="返回的类型">
处理 不能直接映射的情况
主键:<id>
其他字段:<result>
column:数据库中名字
property:要映射的 idea 中属性名字
运行 Test3:
4.1.4 反向级联
上面 Student 级联 Classes
现在 Classes 级联 Student
1 repository 中,新建 接口 ClassesRepository,加入代码:
public Classes findById(long id);
2 repository 中,新建 ClassesRepository.xml 文件类型,加入代码(可以粘贴 AccountRepository.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.southwind.repository.ClassesRepository">
<resultMap id="ClassesMap" type="com.southwind.entity.Classes">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
<collection property="students" ofType="com.southwind.entity.Student">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
</collection>
</resultMap>
<select id="findById" parameterType="long" resultMap="ClassesMap">
select s.id, s.name, c.id as cid, c.name as cname from student s,classes c
where c.id=#{id} and s.cid=c.id
</select>
</mapper>
![](https://i-blog.csdnimg.cn/blog_migrate/87c5eda65843b078fcbcdae4f9699a36.png)
3 ClassesRepository.xml 注册到 resources/ config.xml 中,config.xml 中加入代码:
<!-- 注册 ClassesRepository.xml -->
<mapper resource="com/southwind/repository/ClassesRepository.xml"></mapper>
![](https://i-blog.csdnimg.cn/blog_migrate/7a46193bb02be75807555447b315f660.png)
4 test/ Test3 中,加入代码:
ClassesRepository classesRepository = sqlSession.getMapper(ClassesRepository.class);
System.out.println(classesRepository.findById(2L));
sqlSession.close();
![](https://i-blog.csdnimg.cn/blog_migrate/540d9fbba529f8aa7fc982887c3aa438.png)
4.2 多对多
客户 和 商品
4.2.1 新建数据库
创 客户表:
use mybatis;
create table customer(
id smallint primary key ,
name varchar(12)
)
创 商品表:
use mybatis;
create table goods(
id smallint primary key ,
name varchar(20)
)
创 中间表 - 客户商品表
use mybatis;
create table customer_goods(
id smallint primary key ,
cid smallint,
foreign key(cid) references customer(id),
gid smallint,
foreign key (gid) references goods(id)
)
添加数据:
4.2.2级联
客户 到 商品
1 entity 中,新建 实体类 Customer,加入代码:
package com.southwind.entity;
import lombok.Data;
import java.util.List;
@Data
public class Customer {
private long id;
private String name;
private List<Goods> goods;
}
![](https://i-blog.csdnimg.cn/blog_migrate/8b09221f6feab80a2549ac87a3eb5727.png)
2 entity 中,新建 实体类 Goods,加入代码:
package com.southwind.entity;
import lombok.Data;
import java.util.List;
@Data
public class Goods {
private long id;
private String name;
private List<Customer> customers;
}
![](https://i-blog.csdnimg.cn/blog_migrate/08bdb46ce1a7074b88b2f34886b3974c.png)
3 repository 中,新建接口 CustomerRepository,加入代码:
public Customer findById(long id);
4 repository 中,新建 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.southwind.repository.CustomerRepository">
<resultMap id="customerMap" type="com.southwind.entity.Customer">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
<collection property="goods" ofType="com.southwind.entity.Goods">
<id column="gid" property="id"></id>
<result column="gname" property="name"></result>
</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 c.id=cg.cid and cg.gid=g.id
</select>
</mapper>
![](https://i-blog.csdnimg.cn/blog_migrate/b7fe9557846088cdd69709f5873cb979.png)
5 CustomerRepository.xml注册到 resources/ config.xml,config.xml 中添加代码:
<!-- 注册 CustomerRepository.xml -->
<mapper resource="com/southwind/repository/CustomerRepository.xml"></mapper>
![](https://i-blog.csdnimg.cn/blog_migrate/be82f34f13ae5fc9103802923c8cca04.png)
6 test/ Test3 中,添加代码:
CustomerRepository customerRepository = sqlSession.getMapper(CustomerRepository.class);
System.out.println(customerRepository.findById(1L));
sqlSession.close();
![](https://i-blog.csdnimg.cn/blog_migrate/aec5bf56be1597f7f1547af28731801f.png)
运行:
4.2.3 反相级联
商品 到 客户
1 repository 中,新建接口 GoodsRepository,加入代码:
public Goods findById(long id);
2 repository 中,新建 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.southwind.repository.GoodsRepository">
<resultMap id="goodsMap" type="com.southwind.entity.Goods">
<id column="gid" property="id"></id>
<result column="gname" property="name"></result>
<collection property="customers" ofType="com.southwind.entity.Customer">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
</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 g.id=cg.gid and cg.cid=c.id
</select>
</mapper>
![](https://i-blog.csdnimg.cn/blog_migrate/6a4124f64649bf445e3b946ed31cc70a.png)
3 GoodsRepository.xml 注册到 resources/ config.xml,config.xml 中添加代码:
<!-- 注册 GoodsRepository.xml -->
<mapper resource="com/southwind/repository/GoodsRepository.xml"></mapper>
![](https://i-blog.csdnimg.cn/blog_migrate/11eb0d8c4510602e54efbb1e39cb2453.png)
4 test/ Test3 中,加入代码:
GoodsRepository goodsRepository = sqlSession.getMapper(GoodsRepository.class);
System.out.println(goodsRepository.findById(1L));
sqlSession.close();
![](https://i-blog.csdnimg.cn/blog_migrate/0f93fd32e07f5eeca18adbe48f2a2b39.png)
运行:
上一节:https://blog.csdn.net/qq_40893824/article/details/107322689
下一节:https://blog.csdn.net/qq_40893824/article/details/107371171