mybaits----多表查询实现

来源：互联网发布：网络道德缺失原因分析编辑：程序博客网时间：2024/05/23 23:31

本篇博客知识点：
1.一对一查询
2.一对多查询
3.多对多查询

一对一查询
以person和 card .人和身份证一对一为例
Person.java

package cn.hncu.domain.oneToone;public class Person {    private String pid;    private String pname;    private Card card;     public String getPname() {        return pname;    }    public void setPname(String pname) {        this.pname = pname;    }    public String getPid() {        return pid;    }    public void setPid(String pid) {        this.pid = pid;    }    public Card getCard() {        return card;    }    public void setCard(Card card) {        this.card = card;    }    @Override    public String toString() {        return "Person [pid=" + pid + ", pname=" + pname + ", card=" + card                + "]";    }}

card.java

package cn.hncu.domain.oneToone;public class Card {    private String card_id;    private String card_gov;    private Person person;    public String getCard_id() {        return card_id;    }    public void setCard_id(String card_id) {        this.card_id = card_id;    }    public String getCard_gov() {        return card_gov;    }    public void setCard_gov(String card_gov) {        this.card_gov = card_gov;    }    public Person getPerson() {        return person;    }    public void setPerson(Person person) {        this.person = person;    }    @Override    public String toString() {        return "Card [card_id=" + card_id + ", card_gov=" + card_gov + "]";    }}

对应的数据库表

CREATE TABLE persons(   pid VARCHAR(32) PRIMARY KEY,   pname VARCHAR(30));CREATE TABLE card(   card_id VARCHAR(32) PRIMARY KEY,   card_gov VARCHAR(30),   pid VARCHAR(32) unique,   CONSTRAINT card_fk FOREIGN KEY(pid) REFERENCES persons(pid));

核心查询 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"><!-- 说明数据库定义的表示persons --><mapper namespace="persons">    <!-- OneToOne -->    <select id="oneToone" resultMap="ps">        select p.pid as pid, p.pname as pname,c.card_id as cardId,c.card_gov as cardGov                from persons p inner join card c on p.pid = c.pid     </select>    <resultMap type="cn.hncu.domain.oneToone.Person" id="ps">        <id property="pid" javaType="string" column="pid" jdbcType="VARCHAR"/>        <result property="pname" javaType="string" column="pname" jdbcType="VARCHAR"/>        <association property="card" javaType="cn.hncu.domain.oneToone.Card">            <id property="card_id" javaType="string" column="cardId" jdbcType="VARCHAR"/>            <result property="card_gov" javaType="string" column="cardGov" jdbcType="VARCHAR" />        </association>    </resultMap></mapper>

最最最主要的在于自定义类型的resultMap的封装，也就是SQL语句查询出来的结果集的封装
这里写图片描述
用java代码查询出来的结果集

一对多查询
以person和car为例，一个人可以拥有多辆车
person.java

package cn.hncu.domain.oneTomany;import java.util.ArrayList;import java.util.List;/** * @author<a href="mailto:953801304@qq.com">胡龙华</a> * @version 2017-8-28  上午11:14:55 * @fileName Person.java */public class Person {    private String pid;    private String pname;    private List<Car> cars = new ArrayList<Car>();    public String getPid() {        return pid;    }    public void setPid(String pid) {        this.pid = pid;    }    public String getPname() {        return pname;    }    public void setPname(String pname) {        this.pname = pname;    }    public List<Car> getCars() {        return cars;    }    public void setCars(List<Car> cars) {        this.cars = cars;    }    @Override    public String toString() {        return "Person [pid=" + pid + ", pname=" + pname + ", cars=" + cars                + "]";    }}

car.java

package cn.hncu.domain.oneTomany;/** * @author<a href="mailto:953801304@qq.com">胡龙华</a> * @version 2017-8-28  上午9:34:01 * @fileName Car.java */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 + "]";    }}

对应的数据库表

CREATE TABLE persons(   pid VARCHAR(32) PRIMARY KEY,   pname VARCHAR(30));CREATE TABLE cars(   id VARCHAR(32) PRIMARY KEY,   NAME VARCHAR(30),   price NUMERIC(10,2),   pid VARCHAR(32),   CONSTRAINT car_fk FOREIGN KEY(pid) REFERENCES persons(pid));

核心的查询的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"><!-- 说明数据库定义的表示persons --><mapper namespace="persons">    <!-- 完成一对多查询，并封装出来~   -->    <!-- 方式一内连接 -->    <select id="onTomany" resultMap="persons">        SELECT  p.pid AS pid, p.pname AS pname,c.id AS cid,c.name AS cname,c.price AS cprice                 FROM persons p INNER JOIN cars c ON p.pid=c.pid    </select>    <resultMap type="cn.hncu.domain.oneTomany.Person" id="persons">        <id property="pid" javaType="string" column="pid" jdbcType="VARCHAR"/>        <result property="pname" javaType="string" column="pname" jdbcType="VARCHAR"/>        <!-- 集合属性封装  cars -->        <collection property="cars" javaType="cn.hncu.domain.oneTomany.Car" >            <id property="id" javaType="string" column="cid" jdbcType="VARCHAR"/>            <result property="name" javaType="string" column="cname" jdbcType="VARCHAR"/>            <result property="price" javaType="_double" column="cprice" jdbcType="NUMERIC"/>        </collection>    </resultMap>    <!-- 方式二 嵌套子查询 -相当于左连接了-->    <select id="onTomany2" resultMap="persons2">        select pid,pname from persons    </select>    <resultMap type="cn.hncu.domain.oneTomany.Person" id="persons2">        <id property="pid" javaType="string" column="pid" jdbcType="VARCHAR" />        <result property="pname" javaType="string" column="pname" jdbcType="VARCHAR"/>        <collection property="cars" select="selCars" column="pid"/>    </resultMap>    <select id="selCars" resultType="cn.hncu.domain.oneTomany.Car" parameterType="string">        select * from cars where pid=#{pid}    </select></mapper>

利用数据库软件查询的结果集
这里写图片描述
Java中查询的结果集

多对多查询
以user和role为例，一个用户可以有多个角色，老师，学生等，学生也可以是多个用户
User.java

package cn.hncu.domain.manyTomany;import java.io.Serializable;import java.util.HashSet;import java.util.Set;/** * @author<a href="mailto:953801304@qq.com">胡龙华</a> */public class User implements Serializable{    private String id;    private String name;    private String pwd;    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.manyTomany;import java.util.HashSet;import java.util.Set;public class Role {    private String id;    private String name;    private Set<User> users = new HashSet<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 Set<User> getUsers() {        return users;    }    public void setUsers(Set<User> users) {        this.users = users;    }    @Override    public String toString() {        return "Role [id=" + id + ", name=" + name + "]";    }}

对应的数据库3个表~

CREATE TABLE roles(   id VARCHAR(32) PRIMARY KEY,   name VARCHAR(30));CREATE TABLE roleuser(   roleid VARCHAR(32),   userid VARCHAR(32),   CONSTRAINT ru_pk PRIMARY KEY(roleid,userid),   CONSTRAINT ru_fk1 FOREIGN KEY(roleid)                                    REFERENCES roles(id),   CONSTRAINT ru_fk2 FOREIGN KEY(userid)                                    REFERENCES users(id));create table users(  id varchar(32) primary key,  name varchar(32),  pwd varchar(32));

User.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"><!-- 说明数据库定义的表示users --><mapper namespace="users">    <select id="manyTomany" resultMap="users">        select u.id as uid,u.name as uname,r.id as rid,r.name as rname                from users u inner join roleuser ru on  u.id = ru.userid                   inner join roles r on ru.roleid = r.id    </select>    <resultMap type="cn.hncu.domain.manyTomany.User" id="users">        <id property="id"  javaType="string" column="uid" jdbcType="VARCHAR"/>        <result property="name" javaType="string" column="uname" jdbcType="VARCHAR"/>        <collection property="roles" javaType="cn.hncu.domain.manyTomany.Role" >            <id property="id" javaType="string" column="rid" jdbcType="VARCHAR"/>            <result property="name" javaType="string" column="rname" jdbcType="VARCHAR"/>        </collection>    </resultMap></mapper>

数据库软件查询结果集合
这里写图片描述

java查询结果集合
这里写图片描述

阅读全文

1 0