JDBC操作多表----- 一对一

JDBC操作多表，表之间的关系还有一种是一对一关系，这种一对一关系为主从关系，即一个表是依赖以另一个表而存在的，下面以Person和身份证两个对象为例来进行说明和演示。通常每个人都有一个身份证，而且必须是唯一一个身份证，每个身份证指定唯一一个人的身份。这时人和身份证就是一一对应关系，同时人是主，身份证是从。脱离了人，身份证便失去意义，不允许一个身份证指向一个不存在的人。

下面是sql语言描述Person和身份证CardId

create table person(
 		id int primary key,
 		name varchar(20),
 		age int
 	);

create table cardId(
 		id int primary key,
 		address varchar(100),
 		constraint id_FK foreign key(id) references person(id)
 	);

Person对象描述：

public class Person {
	private String name;
	private int age;
	private int id;
	private CardId cardId;
	
	public Person(){
		
	}
	public Person(String name, int age, int id) {
		super();
		this.name = name;
		this.age = age;
		this.id = id;
	}
	/**
	 * @return the name
	 */
	public String getName() {
		return name;
	}
	/**
	 * @param name the name to set
	 */
	public void setName(String name) {
		this.name = name;
	}
	/**
	 * @return the age
	 */
	public int getAge() {
		return age;
	}
	/**
	 * @param age the age to set
	 */
	public void setAge(int age) {
		this.age = age;
	}
	/**
	 * @return the id
	 */
	public int getId() {
		return id;
	}
	/**
	 * @param id the id to set
	 */
	public void setId(int id) {
		this.id = id;
	}
	/**
	 * @return the cardId
	 */
	public CardId getCardId() {
		return cardId;
	}
	/**
	 * @param cardId the cardId to set
	 */
	public void setCardId(CardId cardId) {
		this.cardId = cardId;
	}
}

//身份证CardId对象描述

public class CardId {
	private int id;
	private String address;
	
	public CardId(){
		
	}
	public CardId(int id, String address) {
		super();
		this.id = id;
		this.address = address;
	}
	/**
	 * @return the id
	 */
	public int getId() {
		return id;
	}
	/**
	 * @param id the id to set
	 */
	public void setId(int id) {
		this.id = id;
	}
	/**
	 * @return the address
	 */
	public String getAddress() {
		return address;
	}
	/**
	 * @param address the address to set
	 */
	public void setAddress(String address) {
		this.address = address;
	}
}

//PersonDao

public interface PersonDao {

	void add(Person p) throws SQLException;

	Person find(int id) throws SQLException;

}

//CardIdDaoImpl实现类

public class CardIdDaoImpl implements CardIdDao {
	/* (non-Javadoc)
	 * @see cn.itcast.dao.impl.CardIdDao#add(cn.itcast.domain.CardId)
	 */
	public void add(CardId card) throws SQLException{
		QueryRunner qr = new QueryRunner();
		String sql = "insert into cardId(id, address) values(?,?)";
		Object[] params = {card.getId(), card.getAddress()};
		qr.update(JdbcDbUtils.getConnection(), sql, params);
	}
	
	/* (non-Javadoc)
	 * @see cn.itcast.dao.impl.CardIdDao#find(int)
	 */
	public CardId find(int id) throws SQLException{
		QueryRunner qr = new QueryRunner();
		String sql = "select * from CardId where id=?";
		Object[] param = {id};
		
		return (CardId) qr.query(JdbcDbUtils.getConnection(), sql, new BeanHandler(CardId.class), param);
	}
}

//PersonDaoImpl实现类

public class PersonDaoImpl implements PersonDao {
	/* (non-Javadoc)
	 * @see cn.itcast.dao.impl.PersonDao#add(cn.itcast.domain.Person)
	 */
	public void add(Person p) throws SQLException{
		QueryRunner qr = new QueryRunner();
		String sql = "insert into person(id, name, age) values(?,?,?)";
		Object[] params = {p.getId(), p.getName(), p.getAge()};
		qr.update(JdbcDbUtils.getConnection(), sql, params);
		
		//获取身份证，存入到cardId表中
		CardId card = p.getCardId();
		sql = "insert into cardId(id, address) values(?,?)";
		params = new Object[]{card.getId(), card.getAddress()};
		qr.update(JdbcDbUtils.getConnection(), sql, params);
	}
	
	/* (non-Javadoc)
	 * @see cn.itcast.dao.impl.PersonDao#find(int)
	 */
	public Person find(int id) throws SQLException{
		QueryRunner qr = new QueryRunner();
		String sql = "select * from person where id=?";
		Object[] param = {id};
			
		Person p=(Person)qr.query(JdbcDbUtils.getConnection(), sql, new BeanHandler(Person.class), param);
		
		//获取身份证号
		sql = "select * from cardId where id=?";
		CardId card = (CardId)qr.query(JdbcDbUtils.getConnection(), sql, new BeanHandler(CardId.class), param);
		p.setCardId(card);
		return p;
	}
}

//测试类

@Test
	public void personAddTest() {
		try {
			JdbcDbUtils.startTransaction();
			Person p = new Person("wuliang",30, 111);
			CardId card = new CardId(p.getId(), "jiangxi");
			p.setCardId(card);
			
			PersonDao pDao = new PersonDaoImpl();
			pDao.add(p);
			JdbcDbUtils.commit();
		} catch (Exception e) {
			e.printStackTrace();
			JdbcDbUtils.rollback();
		} finally {
			JdbcDbUtils.release();
		}
	}
	
	@Test
	public void find(){
		try {
			JdbcDbUtils.startTransaction();
			int id = 111;
			PersonDao pDao = new PersonDaoImpl();
			Person p = pDao.find(id);
			System.out.println("身份证号:"+p.getId());
			System.out.println("姓名:"+p.getName());
			System.out.println("年龄"+p.getAge());
			System.out.println("地址"+p.getCardId().getAddress());
			JdbcDbUtils.commit();
		} catch (Exception e) {
			e.printStackTrace();
			JdbcDbUtils.rollback();
		} finally {
			JdbcDbUtils.release();
		}
	}