多表之间的联系:
注:本文章的演示都是在上篇文章项目的基础上进行的,所以有的代码变动不大的就不再重复贴了,以免太过冗余。点明一点:每写一个映射文件都要配置在mybatis-config.xml 的 <mappers>中
一对多:查询哪些人有哪些车
Demo3.java
- package cn.hncu.demo;
- import java.sql.SQLException;
- import java.util.List;
- import java.util.Set;
- import org.apache.ibatis.session.SqlSession;
- import org.junit.Test;
- import cn.hncu.domain.Card;
- import cn.hncu.domain.Person;
- import cn.hncu.domain.Role;
- import cn.hncu.domain.User;
- import cn.hncu.utils.SqlSessionUtils;
- //演示表与表之间的关系:一对一,一对多和多对多
- public class Demo3 {
- //一对多:一个人(person)多辆车(car)
- @Test
- public void test1() throws SQLException{
- //查询哪些人有哪些车-----innor join
- SqlSession s=SqlSessionUtils.getSqlSession();
- List<Person> persons=s.selectList("persons.person1");
- s.close();
- for(Person person:persons)
- System.out.println("person: "+person);
- }
Person.java
- package cn.hncu.domain;
- import java.util.ArrayList;
- import java.util.List;
- //一方
- public class Person {
- private String id;
- private String name;
- //建一个集合表示多方
- private List<Car> cars=new ArrayList<Car>();
- //为了实现表之间关系中的“一对一”,在此必须添加一个对方的值对象
- private Card card;
- public String getId() {
- return id;
- }
- public void setId(String id) {
- this.id = id;
- }
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public List<Car> getCars() {
- return cars;
- }
- public void setCars(List<Car> cars) {
- this.cars = cars;
- }
- public Card getCard() {
- return card;
- }
- public void setCard(Card card) {
- this.card = card;
- }
- @Override
- public String toString() {
- return "Person [id=" + id + ", name=" + name + ", cars=" + cars + "]";
- }
- }
Car.java
- package cn.hncu.domain;
- //多方
- public class Car {
- private String id;
- private String name;
- private Double price;
- //声明一个值对象表示一方
- private Person person;
- public String getId() {
- return id;
- }
- public void setId(String id) {
- this.id = id;
- }
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public Double getPrice() {
- return price;
- }
- public void setPrice(Double price) {
- this.price = price;
- }
- public Person getPerson() {
- return person;
- }
- public void setPerson(Person person) {
- this.person = person;
- }
- @Override
- public String toString() {
- return "Car [id=" + id + ", name=" + name + ", price=" + price + "]";
- }
- }
Person.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="persons">
- <!-- 一对多:inner join-->
- <!-- 自定义复杂类型用resultMap -->
- <resultMap type="cn.hncu.domain.Person" id="ps">
- <id property="id" column="pid"/><!-- 使用id是为了更加精确,使用result也可 -->
- <result property="name" column="pname"/>
- <!-- 一方中的多方集合(这里的property用car值对象的变量,column用下面select语句的查询结果的表头) -->
- <collection property="cars" javaType="cn.hncu.domain.Car">
- <id property="id" column="cid"/>
- <result property="name" column="cname" javaType="string" jdbcType="VARCHAR"/>
- <result property="price" column="cprice" javaType="_double" jdbcType="NUMERIC"/>
- </collection>
- </resultMap>
- <select id="person1" resultMap="ps">
- select p.pid as pid,p.pname as pname,c.id as cid,c.name as cname,c.price as cprice
- from person p inner join car c on p.pid =c.pid
- </select>
数据库查询结果:
控制台查询结果:
一对多:查询每个人的车辆信息-----left join
- @Test
- public void test2() throws SQLException{
- //查询每个人的车辆信息-----left join
- SqlSession s=SqlSessionUtils.getSqlSession();
- List<Person> persons=s.selectList("persons.person2");
- s.close();
- for(Person person:persons)
- System.out.println("person: "+person);
- }
- <!-- 一对多:按照上面的方法用left join即可,这里学习一下嵌套查询 -->
- <select id="person2" resultMap="ps2">
- select pid,pname from person
- </select>
- <resultMap type="cn.hncu.domain.Person" id="ps2">
- <id property="id" column="pid"/>
- <result property="name" column="pname"/>
- <collection property="cars" column="pid" select="cars1"></collection>
- </resultMap>
- <select id="cars1" resultType="cn.hncu.domain.Car" parameterType="string">
- select * from car where pid=#{value}
- </select>
数据库:
控制台:
一对一:一个人对应一张身份证
- @Test
- public void test3() throws SQLException{
- //查询每个人的车辆信息-----left join
- SqlSession s=SqlSessionUtils.getSqlSession();
- List<Card> cards=s.selectList("persons.card1");
- s.close();
- for(Card card:cards)
- System.out.println("card: "+card);
- }
- <!-- 一对一演示:一张身份证对应一个人 -->
- <select id="card1" resultMap="c1">
- select c.card_id as id,c.card_gov as gov,p.pid as pid,p.pname as pname
- from cards c inner join person p on c.pid=p.pid
- </select>
- <resultMap type="cn.hncu.domain.Card" id="c1">
- <!-- 以指定构造方法来初始化对象 -->
- <constructor>
- <idArg column="id" javaType="string" jdbcType="VARCHAR"/>
- </constructor>
- <result property="gov" column="gov" javaType="string" jdbcType="VARCHAR"/>
- <association property="person" javaType="cn.hncu.domain.Person">
- <result property="id" column="pid"/>
- <result property="name" column="pname"/>
- <collection property="cars" select="cars1" column="pid"></collection>
- </association>
- </resultMap>
- </mapper>
数据库:
控制台:
多对多:人---角色的关系
- //多对多:人---角色的关系 开发步骤:1.建数据库表 2.写值对象(体现表之间关系) 3.写调用的java代码(在业务流程不熟悉的情况下,可先从需求下手) 4.写映射文件
- @Test
- public void test4() throws SQLException{
- //查询哪些人有哪些角色-----inner join
- SqlSession s=SqlSessionUtils.getSqlSession();
- List<User> users=s.selectList("roles.user");
- s.close();
- for(User user:users)
- System.out.println("user: "+user);
- for(User u:users){
- String name=u.getName();
- Set<Role> roles=u.getRoles();
- String r="";
- for(Role role:roles){
- r+=role.getName()+",";
- }
- System.out.println(name+","+r);
- }
- }
Role.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="roles">
- <!-- 如果当前映射文件中的所有操作都要使用(二级)缓存,只需加<cache/>标记即可 -->
- <cache/>
- <!-- 多对多:采用 inner join -->
- <select id="user" resultMap="us">
- select u.id as id , u.name as name , u.pwd as pwd , r.id as rid , r.name as rname
- from users u inner join userrole ur on u.id=ur.uid inner join roles r on ur.rid=r.id
- </select>
- <resultMap type="cn.hncu.domain.User" id="us">
- <id property="id" column="id"/>
- <result property="name" column="name" javaType="string" jdbcType="VARCHAR"/>
- <result property="pwd" column="pwd"/>
- <collection property="roles" javaType="cn.hncu.domain.Role">
- <id property="id" column="rid"/>
- <result property="name" column="rname"/>
- </collection>
- </resultMap>
两个值对象:
User.java
- package cn.hncu.domain;
- import java.util.HashSet;
- import java.util.Set;
- public class User {
- private String id;
- private String name;
- private String pwd;
- //专为多对多建立一个 保存对方的集合--用List或Set都可以
- private Set<Role> roles=new HashSet<Role>();
- public String getId() {
- return id;
- }
- public void setId(String id) {
- this.id = id;
- }
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public String getPwd() {
- return pwd;
- }
- public void setPwd(String pwd) {
- this.pwd = pwd;
- }
- public Set<Role> getRoles() {
- return roles;
- }
- public void setRoles(Set<Role> roles) {
- this.roles = roles;
- }
- @Override
- public String toString() {
- return "User [id=" + id + ", name=" + name + ", pwd=" + pwd
- + ", roles=" + roles + "]";
- }
- }
Role.java
- package cn.hncu.domain;
- import java.io.Serializable;
- import java.util.ArrayList;
- import java.util.List;
- public class Role implements Serializable{
- private String id;
- private String name;
- //专为多对多建立一个保存对方的集合 -----用list或set 都可以
- private List<User> users=new ArrayList<User>();
- public String getId() {
- return id;
- }
- public void setId(String id) {
- this.id = id;
- }
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public List<User> getUsers() {
- return users;
- }
- public void setUsers(List<User> users) {
- this.users = users;
- }
- @Override
- public String toString() {
- return "Role [id=" + id + ", name=" + name + "]";
- }
- }
数据库:
控制台:
mybatis缓存技术演示
- //※※mybatis缓存技术演示:
- //※※注意:被放入缓存的值对象必须实现serizable接口
- @Test
- public void caseDemo() throws SQLException{
- //同一个session用的是一级缓存
- SqlSession ss=SqlSessionUtils.getSqlSession();
- System.out.println("ss: "+ss);
- Role role=ss.selectOne("roles.cacheDemo","R001");//查单
- System.out.println(role.hashCode());
- Role role2=ss.selectOne("roles.cacheDemo","R001");//查单
- System.out.println(role2.hashCode());
- ss.close();
- System.out.println("----------------------------");
- //第二个session
- SqlSession ss2=SqlSessionUtils.getSqlSession();
- System.out.println("ss2: "+ss2);
- Role role22=ss2.selectOne("roles.cacheDemo","R001");//查单
- System.out.println(role22.hashCode());
- Role role222=ss2.selectOne("roles.cacheDemo","R001");//查单
- System.out.println(role222.hashCode());
- }
- }
- <!-- 下面的查询操作专用于演示缓存 useCache可指定某条语句不使用缓存-->
- <select id="cacheDemo" useCache="false" resultType="cn.hncu.domain.Role" parameterType="string">
- select * from roles where id=#{value}
- </select>
- </mapper>
不带缓存:
带缓存:
这里对缓存再个人总结一下:
1. 缓存有一级和二级缓存。SqlSession自己带有一级缓存,所有同一个缓存得到的对象的hash地址是相同的,不同的session拿到的对象hash地址不同。
2. mybatis(hibernate也一样)有二级缓存。mytbatis中默认情况下是没有开启缓存的,当不同session查询时,都要去数据库中去查询,效率降低。
当开启mybatis缓存时,session第一次查询时没有缓存,会到数据库中查询,并将查询结果放到mybatis二级缓存和本session缓存中,当再存查询时,会到本session缓存池中寻找,找到即返回结果。
所以带缓存的图中,两次的hashCode值是一样的。 而当第二个session去查询时,到mybatis的二级缓存池中找到结果,就会把结果克隆一份结果返回,并且不把结果放进本session缓存池中,再次查询时,依然到mybatis的二级缓存池中中克隆结果。所以两次结果的hashCode值不同。
3. 只有session到数据库中查询,才会把查询结果放进session的缓存池中。
贴出一下我用mysql建的表格的代码,以供日后参考:
- ALTER TABLE car
- ADD CONSTRAINT stud_fk FOREIGN KEY(pid) REFERENCES person(pid);
- INSERT INTO person(pid,pname) VALUES("P001","Jack");
- INSERT INTO person(pid,pname) VALUES("P002","Rose");
- INSERT INTO person(pid,pname) VALUES("P003","Tom");
- INSERT INTO car(id,NAME,price,pid) VALUES("C001","Benz",100,"P001");
- INSERT INTO car(id,NAME,price,pid) VALUES("C002","BMW",150,"P001");
- INSERT INTO car(id,NAME,price,pid) VALUES("C003","QQ",10,"P003");
- DROP TABLE person;
- DROP TABLE car;
- CREATE TABLE car(
- id VARCHAR(32) PRIMARY KEY,
- NAME VARCHAR(128),
- price NUMERIC,
- pid VARCHAR(32)
- );
- SELECT pid,pname FROM person
- SELECT p.pid AS pid,p.pname AS pname,c.id AS cid,c.name AS cname,c.price AS cprice
- FROM person p INNER JOIN car c ON p.pid =c.pid
- SELECT p.pid AS pid,p.pname AS pname,c.id AS cid,c.name AS cname,c.price AS cprice
- FROM person p LEFT JOIN car c ON p.pid =c.pid
- CREATE TABLE cards(
- card_id VARCHAR(32) PRIMARY KEY,
- card_gov VARCHAR(128),
- pid VARCHAR(32)
- )
- ALTER TABLE cards
- ADD CONSTRAINT fk_pid FOREIGN KEY (pid) REFERENCES person(pid);
- INSERT INTO cards(card_id,card_gov,pid) VALUES("C001","湖南长沙","P001");
- INSERT INTO cards(card_id,card_gov,pid) VALUES("C002","湖南益阳","P002");
- INSERT INTO cards(card_id,card_gov,pid) VALUES("C003","北京朝阳","P003");
- SELECT c.card_id AS id,c.card_gov AS gov,p.pid AS pid,p.pname AS pname
- FROM cards c INNER JOIN person p ON c.pid=p.pid
- CREATE TABLE roles(
- id VARCHAR(32) PRIMARY KEY,
- NAME VARCHAR(128)
- )
- INSERT INTO roles(id,NAME) VALUES('R001','教师');
- INSERT INTO roles(id,NAME) VALUES('R002','学生');
- INSERT INTO roles(id,NAME) VALUES('R003','后勤');
- INSERT INTO roles(id,NAME) VALUES('R004','暂无');
- CREATE TABLE userRole(
- uid VARCHAR(32),
- rid VARCHAR(32)
- )
- ALTER TABLE userrole
- ADD CONSTRAINT fk_uid FOREIGN KEY (uid) REFERENCES users(id);
- ALTER TABLE userrole
- ADD CONSTRAINT fk_rid FOREIGN KEY (rid) REFERENCES roles(id);
- INSERT INTO userrole(uid,rid) VALUES('1',"R002");
- INSERT INTO userrole(uid,rid) VALUES('1',"R003");
- INSERT INTO userrole(uid,rid) VALUES('2',"R002");
- INSERT INTO userrole(uid,rid) VALUES('3',"R004");
- INSERT INTO userrole(uid,rid) VALUES('4',"R001");
- SELECT u.id AS id , u.name AS NAME , u.pwd AS pwd , r.id AS rid , r.name AS rname
- FROM users u INNER JOIN userrole ur ON u.id=ur.uid INNER JOIN roles r ON ur.rid=r.id