多表之间的联系:
注:本文章的演示都是在上篇文章项目的基础上进行的,所以有的代码变动不大的就不再重复贴了,以免太过冗余。点明一点:每写一个映射文件都要配置在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