Mybatis学习第二天
学习内容:
- mybatis的CRUD操作
- mybatis的关系映射
Mybatis的CRUD操作
再mybatis中,CRUD变的非常简单,我们可以通过Mapper代理模式来进行CRUD操作,我们甚至可以省略书写CRUD的过程,只用关注于SQL语句
这里只举一个查询所有操作的例子:
1.映射文件:
<?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.Dao.IUserDao">
<!--查找所有的用户-->
<select id="findAll" resultType="User">
SELECT * FROM user
</select>
</mapper>
2.实体类:
import java.io.Serializable;
import java.util.Date;
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
3.Dao层接口
public interface IUserDao {
List<User> findAll();
}
4.测试代码:
public class Mybatis01 {
//4.创建代理对象
IUserDao dao;
SqlSession sqlSession;
@Before
public void setup() throws IOException {
//1.加载配置文件
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2.创建sqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//3.生产sqlSession对象
sqlSession = sqlSessionFactory.openSession();
dao = sqlSession.getMapper(IUserDao.class);
in.close();
}
@Test
public void test() {
List<User> users = dao.findAll();
for (User user : users) {
System.out.println(user);
}
}
@After
public void close() {
sqlSession.close();
}
}
执行结果如下:
2019-10-09 09:24:47,384 549 [ main] DEBUG com.offcn.Dao.IUserDao.findAll - ==> Preparing: SELECT * FROM user
2019-10-09 09:24:47,414 579 [ main] DEBUG com.offcn.Dao.IUserDao.findAll - ==> Parameters:
2019-10-09 09:24:47,464 629 [ main] DEBUG com.offcn.Dao.IUserDao.findAll - <== Total: 6
User{id=1, username='阿水', birthday=Tue May 02 00:00:00 CST 2017, sex='女', address='山西太原'}
User{id=5, username='李晓雨', birthday=Thu Dec 13 00:00:00 CST 2018, sex='男', address='山西大同'}
User{id=6, username='ningque', birthday=Sun Sep 09 00:00:00 CDT 1990, sex='1', address='shanxi'}
User{id=16, username='阿辉', birthday=Thu Sep 13 00:00:00 CDT 1990, sex='女', address='露露安'}
User{id=17, username='zgf35310', birthday=Sun Sep 09 00:00:00 CDT 1990, sex='0', address='事实上'}
User{id=19, username='zgf35310', birthday=Sun Sep 09 00:00:00 CDT 1990, sex='1', address='事实上'}
2019-10-09 09:24:47,465 630 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@3aeaafa6]
2019-10-09 09:24:47,466 631 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@3aeaafa6]
2019-10-09 09:24:47,466 631 [ main] DEBUG source.pooled.PooledDataSource - Returned connection 988458918 to pool.
Mybatis的关系映射
在我们日常处理的数据的时候,经常会有两或两个以上的表进行协同操作,所以这里就要用到mybatis的关系映射。
关系映射分为 1对1,1对多,多对多。
1对1的关系:
例如下图的两张表:
1.用户信息表
2.身份证信息表
这两张表之间存在1对1的关系,一个用户对应一个身份证号,用户表中的用户id和身份证信息表中的per_fk对应。
我们想在查询用户信息的时候同时查询到身份证号,我们就可以书写sql语句:
SELECT user.*,card.num from user,card where user.id = card.per_fk;
查询结果如图:
我们在这次的查询中,查询的结果即包含用户表中的全部信息,也包含身份证信息表中的部分信息,那我们该如何存储这些信息到实体类中呢?
首先我们想到的就是在User类中,增加一条属性为int num的属性,这样就可以把身份证号存储到用户表中,这个操作固然可行,可是这样我们就不是对应了表之间的关系,而是单纯的按照取出的数据的列来进行封装实体类。
为了解决这个问题,我们可以通过手动映射,建立出互相包含的实体,将表于表之间的关系转换为实体与实体之间的关系。
所以,我们先创建出身份证表的实体类:
public class Card {
private Integer id;
private String name;
private String num;
private long perFk;
@Override
public String toString() {
return "Card{" +
"id=" + id +
", name='" + name + '\'' +
", num='" + num + '\'' +
", perFk=" + perFk +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getNum() {
return num;
}
public void setNum(String num) {
this.num = num;
}
public long getPerFk() {
return perFk;
}
public void setPerFk(long perFk) {
this.perFk = perFk;
}
}
并且在User实体类中添加一条Card类型的属性:
private Card card;
//同时添加get,set方法
public Card getCard() {
return card;
}
public void setCard(Card card) {
this.card = card;
}
接下来就是书写我们的映射文件,来处理如何把查询到的num列的属性存储到User实体类的Card属性中
手动映射的第一种形式:
<!--一对一的关系映射-->
<resultMap id="UserAndCard" type="user">
<id property="id" column="id" />
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
<association property="card" javaType="card">
<result property="num" column="num"/>
</association>
</resultMap>
<select id="findOneToOne1" resultMap="UserAndCard">
SELECT user.*,card.num from user,card where user.id = card.per_fk;
</select>
association标签的作用:
说白了我们只关注两个点
1.property:指的就是你实体类中的存储相关数据的属性名
2.javaType:存储到哪个实体类中
手动映射的第二种形式:
<resultMap id="findNum" type="card">
<result property="num" column="num"/>
</resultMap>
<resultMap id="findUserAndCard" type="user">
<id property="id" column="id" />
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
</resultMap>
<resultMap id="UserAndCard2" type="user" extends="findUserAndCard">
<association property="card" javaType="card" resultMap="findNum"/>
</resultMap>
<select id="findOneToOne2" resultMap="UserAndCard2">
SELECT user.*,card.num from user,card where user.id = card.per_fk;
</select>
和第一种没有太大的区别,只不过是把association中的内容单独拎出去了,封装成了一个resultMap,然后再association标签中再次调用。
手动映射的第三种形式:
这种形式就很麻烦了,也叫做分步查询的形式,并且这种的查询方式的结果和前两种都不同,先查询出所有的用户的信息,封装成一个list集合,再根据所有用户的id查询身份证表,把查到的信息和用户表一一对应,如果查不到就是空值了。
根据上述思路,我们来书写一下:
首先查询所有的用户信息:
<resultMap id="UserAndCard3" type="user">
<id property="id" column="id" />
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
</resultMap>
<select id="findOneToOne3" resultMap="UserAndCard3">
SELECT * FROM user
</select>
查询到了所有的用户信息之后就要查询所有用户的身份证信息,存储到用户信息中
<association property="card" javaType="card" column="id" select="com.offcn.Dao.IUserDao.getUserByCard"/>
<select id="getUserByCard" parameterType="int" resultType="card">
select * from card where per_fk=#{uid}
</select>
最后组合在一起
<resultMap id="UserAndCard3" type="user">
<!--映射完毕后开始查询Card中的信息,再次封装到User中-->
<id property="id" column="id" />
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
<association property="card" javaType="card" column="id" select="com.offcn.Dao.IUserDao.getUserByCard"/>
</resultMap>
<select id="findOneToOne3" resultMap="UserAndCard3">
SELECT * FROM user
</select>
<select id="getUserByCard" parameterType="int" resultType="card">
select * from card where per_fk=#{uid}
</select>
注意: association 中 property代表数据赋值给实体类中的属性名称,column代表将何值传递给下一条sql语句作为参数进行查找。
查询结果:
-------------这是一个From findOneToOne3-------------
User{id=1, username='阿水', birthday=Tue May 02 00:00:00 CST 2017, sex='女', address='山西太原'}
Card{id=1, name='ddd', num='140622100987636343', perFk=0}
-------------这是一个From findOneToOne3-------------
User{id=5, username='李晓雨', birthday=Thu Dec 13 00:00:00 CST 2018, sex='男', address='山西大同'}
Card{id=2, name='fff', num='109876543211234567', perFk=0}
-------------这是一个From findOneToOne3-------------
User{id=6, username='ningque', birthday=Sun Sep 09 00:00:00 CDT 1990, sex='1', address='shanxi'}
null
-------------这是一个From findOneToOne3-------------
User{id=16, username='阿辉', birthday=Thu Sep 13 00:00:00 CDT 1990, sex='女', address='露露安'}
null
-------------这是一个From findOneToOne3-------------
User{id=17, username='zgf35310', birthday=Sun Sep 09 00:00:00 CDT 1990, sex='0', address='事实上'}
null
-------------这是一个From findOneToOne3-------------
User{id=19, username='zgf35310', birthday=Sun Sep 09 00:00:00 CDT 1990, sex='1', address='事实上'}
null
2019-10-09 20:25:06,481 756 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@462d5aee]
2019-10-09 20:25:06,482 757 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@462d5aee]
2019-10-09 20:25:06,482 757 [ main] DEBUG source.pooled.PooledDataSource - Returned connection 1177377518 to pool.
前两个查询的结果:
-------------这是一个From findOneToOne2-------------
User{id=1, username='阿水', birthday=Tue May 02 00:00:00 CST 2017, sex='女', address='山西太原'}
Card{id=null, name='null', num='140622100987636343', perFk=0}
-------------这是一个From findOneToOne2-------------
User{id=5, username='李晓雨', birthday=Thu Dec 13 00:00:00 CST 2018, sex='男', address='山西大同'}
Card{id=null, name='null', num='109876543211234567', perFk=0}
2019-10-09 20:29:55,779 741 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@462d5aee]
2019-10-09 20:29:55,780 742 [ main] DEBUG ansaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@462d5aee]
2019-10-09 20:29:55,780 742 [ main] DEBUG source.pooled.PooledDataSource - Returned connection 1177377518 to pool.
我们可以明显的发现,前两种和sql语句的查询结果相同,只把匹配的上的信息查询出来了,而第三种则是把所有的信息都查询出了,没有身份证信息的则为null。
一对多
一对多和一对一其实道理相同,只不过是把一条用户信息的id所能查询到的所有的订单信息都封装为多个实体类,并且把这个实体类存储在List集合中,在映射文件中使用Collection标签来处理。
先上表:
订单表
用户表
一对多同样有三种形式
第一种
<resultMap id="UserAndOrder1" type="user" extends="findUserAndCard">
<collection property="orders" ofType="order">
<result property="userId" column="userId"/>
<result property="number" column="number"/>
<result property="note" column="note"/>
<result property="createTime" column="createTime"/>
</collection>
</resultMap>
<select id="findOneToMany1" resultMap="UserAndOrder1">
SELECT u.*,o.* FROM user u,orders o WHERE u.id = o.userId;
/*select u.*,o.number,o.note from user u,orders o where u.id=o.userId*/
</select>
第二种
<resultMap id="findOrders" type="order">
<result property="userId" column="userId"/>
<result property="number" column="number"/>
<result property="note" column="note"/>
<result property="createTime" column="createTime"/>
</resultMap>
<resultMap id="UserAndOrder2" type="user" extends="findUserAndCard">as
<collection property="orders" ofType="order" resultMap="findOrders"/>
</resultMap>
<select id="findOneToMany2" resultMap="UserAndOrder2">
SELECT u.*,o.* FROM user u,orders o WHERE u.id = o.userId;
</select>
第三种
<resultMap id="UserAndOrder3" type="user" extends="findUserAndCard">
<collection property="orders" ofType="order" column="id" select="com.offcn.Dao.IUserDao.findOrdersById"/>
</resultMap>
<select id="findOneToMany3" resultMap="UserAndOrder3">
SELECT * FROM USER
</select>
<select id="findOrdersById" resultType="order" parameterType="int">
SELECT * FROM ORDERS WHERE USERID = #{UID}
</select>
执行结果就省略了,其实一对多和一对一非常相似,仅仅是将association 标签替换为 collection标签,标签中将javaType改为ofType。
多对多
多对多其实和一对多也类似,只不过多对多要添加一张中间表,中间表联系两张表的多对多关系,废话少说,上表
items表
orders表
orderdetail中间表
通过这三张表我们可以看出:
所以我们现在关键的是要把这三张表联合起来,查询出结果我们可以通过左外连接书写的sql语句来把三张表联系在一起
select o.id as oid, o.note,i.* from orders o
left outer join orderdetail od on o.id = od.ordersId
left outer join items i on od.itemsId = i.id;
测试查询结果如图:
既然可以查询出结果,也就是说这两张表联系在了一起,那么我们就可以在实体类中添加两方的关系,两方既然是多对多的关系,那么就可以在Order实体类中添加一个泛型为Item的集合,在Item的实体类中,添加一个泛型为Order的集合。
//Order实体类中添加:
private List<Item> items;
public List<Item> getItems() {
return items;
}
public void setItems(List<Item> items) {
this.items = items;
}
//List实体类中添加:
private List<Order> orders;
public List<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
}
实体类配置完毕,映射文件的思路就很简单了,把表中的映射完毕后,再使用Collection标签把其他表中的映射到集合中就好了
<resultMap id="ItemOrder" type="item">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="price" column="price"/>
<result property="pic" column="pic"/>
<result property="createtime" column="createtime"/>
<result property="detail" column="detail"/>
<collection property="orders" ofType="order">
<id property="id" column="oid"/>
<result property="note" column="note"/>
</collection>
</resultMap>
<select id="Many2Many2" resultMap="ItemOrder">
select i.*,o.id as oid, o.note as note from orders o
left outer join orderdetail od on o.id = od.ordersId
left outer join items i on od.itemsId = i.id;
</select>
<resultMap id="OrderItem" type="order">
<id column="oid" property="id"/>
<result column="userId" property="userId"/>
<result column="number" property="number"/>
<result column="note" property="note"/>
<result column="createTime" property="createTime"/>
<collection property="items" ofType="item">
<result column="name" property="name"/>
<result column="price" property="price"/>
<result column="pic" property="pic"/>
<result column="createtime" property="createtime"/>
<result column="detail" property="detail"/>
</collection>
</resultMap>
<select id="ManyToMany" resultMap="OrderItem">
select i.*,o.id as oid, o.note as note from orders o
left outer join orderdetail od on o.id = od.ordersId
left outer join items i on od.itemsId = i.id;
</select>
执行结果:
Order–Item
Item–Order
结果没问题,这些就是mybatis的关系映射中的三种关系。喜欢请点赞谢谢!