收获满满的一天啊,接下来是一个多对多的关系查询,多对多也就是一对多的进化版。
1.先在数据库弄几个表
- 这是在数据库新建的三个表
- 顾客表:
- 商品表:
- 顾客_商品表(用来连接两张表之间的关系的,cid gid 都是外键):
2.然后开始动手:
贴上文件目录 (越来越长了):
首先 还是先创建 实体类
- Customer类:
package com.southwind.entity;
import lombok.Data;
import java.util.List;
@Data
public class Customer {
private int id ;
private String name;
private List<Goods> goods;
}
- Goods类
package com.southwind.entity;
import lombok.Data;
import java.util.List;
@Data
public class Goods {
private int id;
private String name;
private List<Customer> customers;
}
然后开始操作:
1.先是利用顾客id查询购买的商品,
- 先写 CustomerRepository接口
package com.southwind.repository;
import com.southwind.entity.Customer;
public interface CustomerRepository {
public Customer findById(int id);
}
- 然后是CustomerRepositoryMapper.xml
文件
<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.southwind.repository.CustomerRepository">
<!-- 多对多的查询 -->
<select id="findById" parameterType="int" resultMap="customerMap">
select c.id cid,c.name cname,g.id gid,g.name gname
from customer as c,goods as g,customer_goods as cg
where c.id = #{id} and c.id = cg.cid and g.id = cg.gid
</select>
<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"/>
<result column="gname" property="name"/>
</collection>
</resultMap>
</mapper>
- 测试类Test_5:
package com.southwind.test;
import com.southwind.repository.CustomerRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
public class Test_5 {
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();
CustomerRepository customerRepository = sqlSession.getMapper(CustomerRepository.class);
System.out.println(customerRepository.findById(1));
sqlSession.close();
}
}
- 贴个查询结果图出来
2.既然有了顾客查询商品,那应该也有商品查询顾客。
- GoodsRepository 接口
package com.southwind.repository;
import com.southwind.entity.Goods;
public interface GoodsRepository {
public Goods findById(int id);
}
- GoodsRepositoryMapper.xml
<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.southwind.repository.GoodsRepository">
<!-- 多对多的关系查询 -->
<select id="findById" parameterType="int" resultMap="goodsMap">
select c.id cid,c.name cname,g.id gid,g.name gname
from customer as c,goods as g,customer_goods as cg
where g.id = #{id} and c.id = cg.cid and g.id = cg.gid
</select>
<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"/>
<result column="cname" property="name"/>
</collection>
</resultMap>
</mapper>
- 来个Test_6测试类
package com.southwind.test;
import com.southwind.repository.GoodsRepository;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
public class Test_6 {
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();
GoodsRepository goodsRepository = sqlSession.getMapper(GoodsRepository.class);
System.out.println(goodsRepository.findById(3));
sqlSession.close();
}
}
- 贴个结果图
3.小总结
- 从第一篇博客到现在,学习了MyBatis的简单查询,多个参数查询,一对多查询,多对一查询,多对多查询,基本的方法就是那样,套路也是那样。
- 自己总结了一下:先定义一个实体类对应数据库的表,接着创建一个接口是要对数据库操作的方法,然后对应这个接口来一个XML文件,里面是封装查询数据库的方法还有映射,然后用一个测试类来测试接受数据。千万别忘了在大的XML文件中注册这个小的XML,否则用不了。
- Java是一个神奇的东西,运行没有BUG你会觉得很好,很爽,当你发现报错时,你的头是大的。好好加油,明天继续学!