第五章第5节： 多表关系之双向一对一

 

双向一对一：双方实体类都有对方的对象

创建数据表

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');

实体类

1.Husband类

public class Husband { 
private int husid;
 private String husname; 
private Wife wife;
 public int getHusid() {
 return husid;
}

public void setHusid(int husid) {
 this.husid = husid; 
}

public String getHusname() { 
return husname;
 }

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

public Wife getWife() { 
return wife; 
}

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

2.Wife类

public class Wife { 
private int wifeid;
 private String wifeName;
 private 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 Husband getHusband() { 
return husband; 
}

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

创建接口

WifeDao接口

public interface WifeDao { 
//查询妻子信息(包含丈夫信息) 
public Wife getWife(int wid); 
//查询丈夫信息(包含妻子信息)
 public Husband getHus(int hid);
 }

实现类

WifeDaoImpl类

public class WifeDaoImpl implements WifeDao { 
@Override
 public Wife getWife(int wid) {
//操作数据库
 Connection connection =null;
 PreparedStatement pps =null;
 ResultSet resultSet =null;
 try {
//1.加载驱动 
Class.forName("com.mysql.cj.jdbc.Driver");
 //2.获得链接
 String userName="root";
 String passWord="123456";
 String url="jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
 connection = DriverManager.getConnection(url, userName, passWord);
 //3.定义sql,创建预状态通道(进行sql语句的发送)
 String sql="select * from wife w,husband h where w.hid=h.husid and w.wifeid=?";
pps = connection.prepareStatement(sql);
 pps.setInt(1,wid);
 //执行sql resultSet = pps.executeQuery();
 Wife wife = new Wife();
 while (resultSet.next()){
 //1.取出各自的信息 
wife.setWifeId(resultSet.getInt("wifeid")); wife.setWifeName(resultSet.getString("wifename"));
 Husband husband = new Husband(); 
husband.setHusId(resultSet.getInt("husid")); husband.setHusName(resultSet.getString("husname"));
 //2.建立妻子和丈夫之间的关系
 wife.setHusband(husband);
 }
return wife; 
} catch (ClassNotFoundException e) {
 e.printStackTrace();
 } catch (SQLException throwables) { 
throwables.printStackTrace(); 
} finally { 
try {
//5.关闭资源 
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 getHus(int hid) {
 //操作数据库
 Connection connection =null;
 PreparedStatement pps =null;
 ResultSet resultSet =null;
 try {
//1.加载驱动 
Class.forName("com.mysql.cj.jdbc.Driver");
 //2.获得链接
 String userName="root";
 String passWord="123456";
 String url="jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
 connection = DriverManager.getConnection(url, userName, passWord); 
//3.定义sql,创建预状态通道(进行sql语句的发送) 
String sql="select * from wife w,husband h where w.hid=h.husid and h.husid=?";
 pps = connection.prepareStatement(sql);
 pps.setInt(1,hid);
 //执行sql
 resultSet = pps.executeQuery();
 Husband husband = new Husband();
 while (resultSet.next()){
 //1.取出各自的信息
 Wife wife = new Wife();
 wife.setWifeId(resultSet.getInt("wifeid")); wife.setWifeName(resultSet.getString("wifename")); husband.setHusId(resultSet.getInt("husid")); husband.setHusName(resultSet.getString("husname")); //2.建立妻子和丈夫之间的关系 husband.setWife(wife);
 }
return husband;
 } catch (ClassNotFoundException e) { 
e.printStackTrace(); 
} catch (SQLException throwables) {
 throwables.printStackTrace(); 
} finally { 
try {
//5.关闭资源 
if (resultSet != null) {
 resultSet.close(); 
}
if (pps != null) {
 pps.close(); 
}
if (connection != null) {
 connection.close();
 } 
} catch (SQLException throwables) {
 throwables.printStackTrace();
 } 
}
return null; 
}
}

创建测试类

public static void main(String[] args) { 
WifeDaoImpl wifeDao = new WifeDaoImpl();
Wife wife = wifeDao.getWife(1);
System.out.println(wife.getWifeName()+","+wife.getHusband().getHusName());
Husband hus = wifeDao.getHus(1); 
System.out.println(hus.getHusName()+","+hus.getWife().getWifeName()); 
}

结果：

孙俪，邓超

邓超，孙俪

 

此文章用于记录学习笔记