7、关联查询

关联查询

一对一、一对多、多对一、多对多
MyBatis:多对一、多对多的本质就是一对多的变化

一对一

a、业务扩展类

新建一个c类,继承类属性多的a类,然后在c类里面加入b类的属性,则c类拥有a、b类的属性,即业务扩展类

<select id="queryPersonByIdWithOneToOne" parameterType="int" resultType="c">
	select a.*,b.* from person a join personCard b on a.cardId=b.cardId where a.id=#{id}
</select>
b、resultMap
//mysql
mysql> alter table person add cardId int;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table person add constraint fk_person_card_cardid foreign key(cardId) references personCard(cardId);
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0
//Person.java
public class Person {
	private int id;
	private String name;
	private int age;
	private boolean sex;
	private Address address;
	private PersonCard personCard;
	public Person() {
	}
	public Person(int id, String name, int age ,boolean sex) {
		this.id = id;
		this.name = name;
		this.age = age;
		this.sex = sex;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public boolean isSex() {
		return sex;
	}
	public void setSex(boolean sex) {
		this.sex = sex;
	}
	public Address getAddress() {
		return address;
	}
	public void setAddress(Address address) {
		this.address = address;
	}
	public PersonCard getPersonCard() {
		return personCard;
	}
	public void setPersonCard(PersonCard personCard) {
		this.personCard = personCard;
	}
	@Override
	public String toString() {
		return this.id+"-"+this.name+"-"+this.age+"-"+this.sex+"-"+this.personCard.getCardId()+"-"+this.personCard.getCardInfo();
	}
}
//PersonCard.java
public class PersonCard {
	private int cardId;
	private String cardInfo;
	public int getCardId() {
		return cardId;
	}
	public void setCardId(int cardId) {
		this.cardId = cardId;
	}
	public String getCardInfo() {
		return cardInfo;
	}
	public void setCardInfo(String cardInfo) {
		this.cardInfo = cardInfo;
	}	
}

i、通过属性成员将两个类建立起联系
例如:在person类中加private PersonCard personCard;属性
ii、在personMapper.xml中写sql

<select id="queryPersonByIdWithOneToOne" parameterType="int" resultMap="Person_PersonCard">
	select p.*,c.* from person p join personCard c on p.cardId=c.cardId where p.id=#{id}
</select>

<resultMap type="Person" id="Person_PersonCard">
    <!-- 分为主键id和非主键result -->
    <id property="id" column="id" />
    <result property="name" column="name" />
    <result property="age" column="age" />
    <!-- jdbcType的值要大写 -->
    <result property="sex" column="sex" javaType="boolean" jdbcType="INTEGER" />
    <!-- 一对一时,对象成员使用association映射 -->
    <association property="personCard" javaType="PersonCard">
       <id property="cardId" column="cardId" />
       <result property="cardInfo" column="cardInfo" />
    </association>
</resultMap>
//PersonMapper.java
import org.lanqiao.entity.Person;
//操作mybatis的接口
public interface PersonMapper {
	/* 1、方法名和mapper.xml文件中标签的id相同
	 * 2、方法的输入参数和mapper.xml文件中标签的parameterType一致
	 * 3、方法的返回值和mapper.xml文件中标签的resultType一致
	 * 4、接口里的方法默认public abstract,所以可以省略 
	 */
	Person queryPersonByIdWithOneToOne(int id);
}
//测试类方法
public static void queryPersonByIdWithOneToOne(int id) throws IOException {
	//加载Mybatis配置文件(为了访问数据库)
	Reader reader = Resources.getResourceAsReader("config.xml");
	//可以在build中修改config.xml中developments的默认值
	SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader,"shishi");
	//session - connection
	SqlSession session = sessionFactory.openSession();
	PersonMapper personMapper=session.getMapper(PersonMapper.class);
	//接口中的方法--》SQL语句
	Person person=personMapper.queryPersonByIdWithOneToOne(id);
	System.out.println(person);
	session.close();
}

一对多

//PersonClass.java
import java.util.List;
public class PersonClass {
	private int classId;
	private String className;
	//增加学生属性(使Person类和PersonClass类建立起关联)
	private List<Person> persons;
	public int getClassId() {
		return classId;
	}
	public void setClassId(int classId) {
		this.classId = classId;
	}
	public String getClassName() {
		return className;
	}
	public void setClassName(String className) {
		this.className = className;
	}
	public List<Person> getPersons() {
		return persons;
	}
	public void setPersons(List<Person> persons) {
		this.persons = persons;
	}
	@Override
	public String toString() {
		return this.classId+"-"+this.className+"-"+this.getPersons();
	}
}
//PersonMapper.xml
<!-- 一对多 -->
<select id="queryClassAndPersons" parameterType="int" resultMap="Class_Person">
	select c.*,p.* from person p join personClass c on p.classId=c.classId where p.classId=#{classId}
</select>

<resultMap type="PersonClass" id="Class_Person">
    <!-- 分为主键id和非主键result -->
    <id property="classId" column="classId" />
    <result property="className" column="className" />
    <!-- jdbcType的值要大写 -->
    <!-- 配置成员属性学生,一对多使用collection -->
    <!-- ofType的值为属性的元素类型 -->
    <collection property="persons" ofType="Person">
        <id property="id" column="id" />
	    <result property="name" column="name" />
	    <result property="age" column="age" />
	    <result property="sex" column="sex" javaType="boolean" jdbcType="INTEGER" />
	    <association property="personCard" javaType="PersonCard">
	       <id property="cardId" column="cardId" />
	       <result property="cardInfo" column="cardInfo" />
	    </association>
    </collection>
</resultMap>
//PersonMapper.java
import org.lanqiao.entity.PersonClass;
//操作mybatis的接口
public interface PersonMapper {
	/* 1、方法名和mapper.xml文件中标签的id相同
	 * 2、方法的输入参数和mapper.xml文件中标签的parameterType一致
	 * 3、方法的返回值和mapper.xml文件中标签的resultType一致
	 * 4、接口里的方法默认public abstract,所以可以省略 
	 */
	PersonClass queryClassAndPersons(int classId);
}
//测试类的方法
public static void queryClassAndPersons(int id) throws IOException {
	//加载Mybatis配置文件(为了访问数据库)
	Reader reader = Resources.getResourceAsReader("config.xml");
	//可以在build中修改config.xml中developments的默认值
	SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader,"shishi");
	//session - connection
	SqlSession session = sessionFactory.openSession();
	PersonMapper personMapper=session.getMapper(PersonMapper.class);
	//接口中的方法--》SQL语句
	PersonClass personClass=personMapper.queryClassAndPersons(id);
	System.out.println(personClass);
	session.close();
}
发布了26 篇原创文章 · 获赞 0 · 访问量 445
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览