MyBatis之多表之间的联系与缓存

多表之间的联系：

注：本文章的演示都是在上篇文章项目的基础上进行的，所以有的代码变动不大的就不再重复贴了，以免太过冗余。点明一点：每写一个映射文件都要配置在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)@Testpublic void test1() throws SQLException{//查询哪些人有哪些车-----innor joinSqlSession 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;}@Overridepublic 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;}@Overridepublic 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 cpricefrom person p inner join car c on p.pid =c.pid</select>

数据库查询结果：

 

控制台查询结果：

 

一对多：查询每个人的车辆信息-----left join

@Testpublic void test2() throws SQLException{//查询每个人的车辆信息-----left joinSqlSession 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>

数据库：

控制台：

 

一对一：一个人对应一张身份证

@Testpublic void test3() throws SQLException{//查询每个人的车辆信息-----left joinSqlSession 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 pnamefrom 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.写映射文件@Testpublic void test4() throws SQLException{//查询哪些人有哪些角色-----inner joinSqlSession 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 rnamefrom 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;}@Overridepublic 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;}@Overridepublic String toString() {return "Role [id=" + id + ", name=" + name + "]";}}

数据库：

 

控制台：

 

mybatis缓存技术演示

//※※mybatis缓存技术演示：//※※注意：被放入缓存的值对象必须实现serizable接口@Testpublic 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("----------------------------");//第二个sessionSqlSession 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 personSELECT p.pid AS pid,p.pname AS pname,c.id AS cid,c.name AS cname,c.price AS cpriceFROM person p INNER JOIN car c ON p.pid =c.pidSELECT p.pid AS pid,p.pname AS pname,c.id AS cid,c.name AS cname,c.price AS cpriceFROM person p LEFT JOIN car c ON p.pid =c.pidCREATE 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 pnameFROM cards c INNER JOIN person p ON c.pid=p.pidCREATE 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 rnameFROM users u INNER JOIN userrole ur ON u.id=ur.uid INNER JOIN roles r ON ur.rid=r.id