双向多表查询之一对一

例如：一个丈夫对应一个妻子

1.创建丈夫表和妻子表

#丈夫表
CREATE TABLE `husband` ( `husid` int(11) NOT NULL AUTO_INCREMENT, 
`husname` varchar(255) DEFAULT NULL, PRIMARY KEY (`husid`) )
ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; 
INSERT INTO `husband` VALUES ('1', '邓超'); 
DROP TABLE IF EXISTS `wife`; 
#妻子表
CREATE TABLE `wife` ( `wifeid` int(11) NOT NULL AUTO_INCREMENT, 
`wifename` varchar(255) DEFAULT NULL, `hid` int(11) DEFAULT NULL, 
PRIMARY KEY (`wifeid`), UNIQUE KEY `uq_wife_hid` (`hid`) ) 
ENGINE=InnoDB AUTO_INCREMENT=2 
DEFAULT CHARSET=utf8; 
INSERT INTO `wife` VALUES ('1', '孙俪', '1');

查询如下

SELECT * from wife;
SELECT * FROM husband;

2.创建实体类
Wife类和Husband类
注意：因为要实现双向一对一。所以在每个实体类中需要创建对应对象
及在Wife中要创建Husband

private Husband husband;

同理在Husband中也要创建Wife

 private Wife wife;

Wife类

package bean;

public class Wife {
    private  int wifeid;
    private  String wifename;
    private  int hid;
    private Husband husband;

    public Husband getHusband() {
        return husband;
    }

    public void setHusband(Husband husband) {
        this.husband = husband;
    }

    public int getWifeid() {
        return wifeid;
    }

    public void setWifeid(int wifeid) {
        this.wifeid = wifeid;
    }

    public String getWifename() {
        return wifename;
    }

    public void setWifename(String wifename) {
        this.wifename = wifename;
    }

    public int getHid() {
        return hid;
    }

    public void setHid(int hid) {
        this.hid = hid;
    }
}

Husband类

package bean;

public class Husband {
    private int hid;
    private String husname;
    private Wife wife;

    public Wife getWife() {
        return wife;
    }

    public void setWife(Wife wife) {
        this.wife = wife;
    }

    public int getHid() {
        return hid;
    }

    public void setHid(int hid) {
        this.hid = hid;
    }

    public String getHusname() {
        return husname;
    }

    public void setHusname(String husname) {
        this.husname = husname;
    }
}

3.选则妻子或丈夫任意一个建立接口
WifeDao接口
在接口中定义两个方法：
1、查询丈夫信息（包含妻子信息）
2、查询妻子信息（包含丈夫信息）

package dao;
//双向一对一
import bean.Husband;
import bean.Wife;

public interface WifeDao {
    //定义两个方法
    //1.查询妻子信息（包含丈夫信息）
    public Wife getWife(int wid);

    //2.查询丈夫信息（包含妻子信息）
    public Husband getHusband(int hid);
}

4.定义实现类WifeDaoImpl来实现接口

package dao.impl;

import bean.Husband;
import bean.Wife;
import dao.WifeDao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class WifeDaoImpl implements WifeDao {
    @Override
    public Wife getWife(int wid) {
        //操作数据库
        Connection connection = null;
        PreparedStatement pps = null;
        ResultSet resultSet = null;
        //1.加载驱动
        try {

            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.连接数据库
            String username = "root";
            String password = "123456";
            String url = "jdbc:mysql://localhost:3306/hhd2?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, username, password);
            //3.sql,创建预状态通道
            String sql = "select * from wife w, husband h where w.hid=h.husid and w.wifeid=?";
            pps = connection.prepareStatement(sql);
            //占位,给占位赋值，下标从1开始
            pps.setInt(1, wid);

            //4.执行
            resultSet = pps.executeQuery();
            Wife wife = new Wife();
            //5.获取结果信息
            while (resultSet.next()) {//判断是否有下一条数据
                /*
                结果信息包括妻子和丈夫，各自获取各自的信息
                 */

                //wife信息
                wife.setWifeid(resultSet.getInt("wifeid"));
                wife.setWifename(resultSet.getString("wifename"));

                //丈夫信息
                Husband husband = new Husband();
                husband.setHid(resultSet.getInt("husid"));
                husband.setHusname(resultSet.getString("husname"));
                //建立丈夫和妻子的关系students.add(student);
                wife.setHusband(husband);//妻子里面包含丈夫
            }
            return wife;

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.关闭资源
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


        return null;
    }

    @Override
    public Husband getHusband(int hid) {
        //操作数据库
        Connection connection = null;
        PreparedStatement pps = null;
        ResultSet resultSet = null;
        //1.加载驱动
        try {

            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.连接数据库
            String username = "root";
            String password = "123456";
            String url = "jdbc:mysql://localhost:3306/hhd2?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, username, password);
            //3.sql,创建预状态通道
            String sql = "select * from wife w, husband h where w.hid=h.husid and h.husid=?";
            pps = connection.prepareStatement(sql);
            //占位,给占位赋值，下标从1开始
            pps.setInt(1,hid);

            //4.执行
            resultSet = pps.executeQuery();
            Husband husband = new Husband();
            //5.获取结果信息
            while (resultSet.next()) {//判断是否有下一条数据
                /*
                结果信息包括妻子和丈夫，各自获取各自的信息
                 */

                //丈夫信息
                husband.setHid(resultSet.getInt("husid"));
                husband.setHusname(resultSet.getString("husname"));

                //妻子信息
                Wife wife = new Wife();
                wife.setWifeid(resultSet.getInt("wifeid"));
                wife.setWifename(resultSet.getString("wifename"));
                //建立丈夫和妻子的关系students.add(student);
                husband.setWife(wife);//丈夫里面包含妻子
            }
            return husband;

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.关闭资源
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


        return null;
    }
}

5、测试类

package test;

import bean.Husband;
import bean.Wife;
import dao.impl.WifeDaoImpl;

public class Demo1 {
    public static void main(String[] args) {
        WifeDaoImpl wifeDao = new WifeDaoImpl();
        //1.通过妻子查询丈夫
        Wife wife = wifeDao.getWife(1);
        System.out.println(wife.getWifename()+","+wife.getHusband().getHusname());
        //2.通过丈夫查询妻子
        Husband husband = wifeDao.getHusband(1);
        System.out.println(husband.getHusname()+","+husband.getWife().getWifename());

    }
}

6.查询结果