5、Mybatis实现一对一关联表查询

本文主要是介绍5、Mybatis实现一对一关联表查询，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

本章节主要实现两个一对一关联表的查询，例如表PERSON

表PHONE

上述两个表一对一关联，一个人只能有一部手机。例如查询id为1的那个人的手机价格，sql 查询语句为：

SELECT * FROM person p1, phone p2 WHERE p1.phone_brand = p2.phone_brand AND p1.id=1

下面通过Mybaits实现此一对一关联表查询，主要通过两种方式来实现：封装查询和嵌套查询

一、封装查询

封装查询把两个表格封装在一条sql语句中进行查询

SELECT * FROM person p1, phone p2 WHERE p1.phone_brand = p2.phone_brand AND p1.id=1

1、首先创建表对应的实体类Person和Phone
Person类：

package com.lzj.mybaits.test1;public class Person {private int id;private String name;private Phone phone;/*一定要加一个空的初始化方法，否则mybaits会报错没有空的初始化方法*/public Person() {super();}public Person(int id, String name, Phone phone) {super();this.id = id;this.name = name;this.phone = phone;}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 Phone getPhone() {return phone;}public void setPhone(Phone phone) {this.phone = phone;}@Overridepublic String toString() {return "Person [id=" + id + ", name=" + name + ", phone=" + phone + "]";}}

Phone类：

package com.lzj.mybaits.test1;public class Phone {private String brand;private float price;/*一定要加一个无参构造函数*/public Phone() {super();}public Phone(String brand, float price) {super();this.brand = brand;this.price = price;}public String getBrand() {return brand;}public void setBrand(String brand) {this.brand = brand;}public float getPrice() {return price;}public void setPrice(float price) {this.price = price;}@Overridepublic String toString() {return "Phone [brand=" + brand + ", price=" + price + "]";}}

注意：创建的对应表的两个实体类一定要有无参的构造函数，mybaits时默认用无参的构造函数进行初始化对象。

2、定义sql 的映射文件personMapper.xml

3、定义mybaits的配置文件conf.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration><typeAliases><package name="com.lzj.mybaits.test1" /></typeAliases><environments default="development"><environment id="development"><transactionManager type="JDBC" /><dataSource type="POOLED"><!-- 配置数据源 --><property name="driver" value="com.mysql.jdbc.Driver" /><property name="url" value="jdbc:mysql://localhost:3306/lzj_database" /><property name="username" value="root" /><property name="password" value="lzjlzj" /></dataSource></environment></environments><!-- 指定sql的映射文件，如果有多个，都可以加进来 --><mappers><mapper resource="com/lzj/mybaits/test1/personMapper.xml" /></mappers></configuration>

4、创建测试类

package com.lzj.mybaits.test1;
import java.io.InputStream;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;public class MybaitsTest {public static void main(String[] args) throws Exception {String resource = "conf.xml";InputStream in = MybaitsTest.class.getClassLoader().getResourceAsStream(resource);SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);/*自动提交事务*/SqlSession session = factory.openSession(true);String statement = "com.lzj.mybaits.test1.userMapper.getPerson";Person person = session.selectOne(statement, 1);System.out.println(person);session.close();}}

运行测试类，输出结果

Person [id=0, name=null, phone=Phone [brand=iphone, price=6000.0]]

二、嵌套查询

嵌套查询就是先查询一个表格，然后在第一次查询结果中再进行第二个表格查询。还是以上面的sql语句为例

select * from PERSON where id=1;//查询phone_brand为iphone
select * from PHONE where phone_brand='iphone'

只需要把personMapper.xml配置文件改成如下形式

小调用getPerson2对应的sql语句，把查询到的phone_brand在传入到getPhone继续调用第二条sql语句。运行测试类，输出结果同上。

这篇关于5、Mybatis实现一对一关联表查询的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---