mybatis 的关联映射 一对一 对多

<!--创建表的sql语句-->
CREATE TABLE `person` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(24) NOT NULL COMMENT '用户名',
`pswd` varchar(16) NOT NULL COMMENT '密码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户'
CREATE TABLE `extinfo` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`personid` bigint(20) NOT NULL COMMENT '用户id',
`email` varchar(32) DEFAULT NULL COMMENT 'email',
`qq` bigint(20) DEFAULT NULL COMMENT 'QQ号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='用户扩展信息（一个用户只能有一条扩展记录）'
CREATE TABLE `address` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`personid` bigint(20) NOT NULL COMMENT '用户id',
`addr` varchar(128) DEFAULT NULL COMMENT '地址',
`zipcode` varchar(8) DEFAULT NULL COMMENT '邮编',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='地址（一个用户可以有多个地址）'
CREATE TABLE `orders` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`personid` bigint(20) NOT NULL COMMENT '用户ID',
`product` varchar(128) NOT NULL COMMENT '产品',
`num` int(11) NOT NULL COMMENT '数量',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
 




public class Person implements Serializable {
private long id;
private String name;          //用户名
private String pswd;          //密码
private Extinfo extinfo;
private List<Address> addressList=new ArrayList<Address>(0);
private List<Orders> ordersList=new ArrayList<Orders>(0);




public class Extinfo implements Serializable {
private long id;
private long personid;          //用户id
private String email;          //email
private long qq;          //QQ号




public class Address implements Serializable {
private long id;
private long personid;          //用户id
private String addr;          //地址
private String zipcode;          //邮编




public class Orders implements Serializable {
private long id;
private long personid;          //用户ID
private String product;          //产品
private int num;          //数量
 




<!--mybatis.cfg.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>
<properties resource="config/jdbc.properties"/>
<typeAliases>
<package name="mypkg.entity"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mypkg/entity/Person.xml"/>
<mapper resource="mypkg/entity/Extinfo.xml"/>
<mapper resource="mypkg/entity/Address.xml"/>
<mapper resource="mypkg/entity/Orders.xml"/>
</mappers>
</configuration>
 


<!--Person-->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="Person">
<resultMap id="rs_base" type="Person">
<id column="id" property="id"/>
<result property="name" column="name"/>
<result property="pswd" column="pswd"/>
</resultMap>




<!-- 一对一，两个一对多，存在N+1问题，不推荐使用 -->
<resultMap id="rs1" type="Person">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="pswd" column="pswd"/>
<association property="extinfo" column="id" select="Extinfo.queryByPersonid"/>
<collection property="addressList" column="id" select="Address.queryByPersonid"/>
<collection property="ordersList" column="id" select="Orders.queryByPersonid"/>
</resultMap>
<select id="find1" parameterType="string" resultType="Person" resultMap="rs1">
select * from person where name like "%"#{value}"%"
</select>




<!-- 一对一，没有N+1问题 把关联的表的映射也写入-->
<resultMap id="rs2" type="Person">
<id property="id" column="p_id"/>
<result property="name" column="name"/>
<result property="pswd" column="pswd"/>
<association property="extinfo" column="id" javaType="extinfo">
<id property="id" column="e_id"/>
<result property="personid" column="personid"/>
<result property="email" column="email"/>
<result property="qq" column="qq"/>
</association>
</resultMap>
<select id="find2" parameterType="string" resultType="Person" resultMap="rs2">
select *,p.id as p_id, e.id as e_id from person p left join extinfo e on p.id=e.personid
where p.name like "%"#{value}"%"
</select>




<!-- 一对多，没有N+1问题 把关联的表的映射也写入-->
<resultMap id="rs3" type="Person">
<id property="id" column="p_id"/>
<result property="name" column="name"/>
<result property="pswd" column="pswd"/>
<collection property="addressList" column="id" javaType="Address">
<id property="id" column="a_id"/>
<result property="personid" column="personid"/>
<result property="addr" column="addr"/>
<result property="zipcode" column="zipcode"/>
</collection>
</resultMap>
<select id="find3" parameterType="string" resultType="Person" resultMap="rs3">
select *,p.id as p_id, a.id as a_id from person p left join address a on p.id=a.personid
where p.name like "%"#{value}"%"
</select>




<!-- 一对一和一对多，没有N+1问题 -->
<resultMap id="rs4" type="Person" autoMapping="true">
<id property="id" column="p_id"/>
<result property="name" column="name"/>
<result property="pswd" column="pswd"/>
<association property="extinfo" column="id" javaType="extinfo">
<id property="id" column="e_id"/>
<result property="personid" column="e_pid"/>
<result property="email" column="email"/>
<result property="qq" column="qq"/>
</association>
<collection property="addressList" column="id" javaType="Address">
<id property="id" column="a_id"/>
<result property="personid" column="a_pid"/>
<result property="addr" column="addr"/>
<result property="zipcode" column="zipcode"/>
</collection>
</resultMap>
<select id="find4" parameterType="string" resultType="Person" resultMap="rs4">
select *,p.id as p_id, e.id as e_id, a.id as a_id,e.personid as e_pid ,a.personid as a_pid
from person p left join address a on p.id=a.personid
left join extinfo e on p.id = e.personid
where p.name like '%b%'
</select>




<!-- 一对多，2个一对多，没有N+1问题 -->
<resultMap id="rs5" type="Person" autoMapping="true">
<id property="id" column="p_id"/>
<result property="name" column="name"/>
<result property="pswd" column="pswd"/>
<association property="extinfo" column="id" javaType="extinfo">
<id property="id" column="e_id"/>
<result property="personid" column="e_pid"/>
<result property="email" column="email"/>
<result property="qq" column="qq"/>
</association>


<collection property="addressList" column="id" ofType="Address">
<id property="id" column="a_id"/>
<result property="personid" column="a_pid"/>
<result property="addr" column="addr"/>
<result property="zipcode" column="zipcode"/>
</collection>


<collection property="ordersList" column="id" ofType="Orders">
<id property="id" column="o_id"/>
<result property="personid" column="o_pid"/>
<result property="product" column="product"/>
<result property="num" column="num"/>
</collection>
</resultMap>
<select id="find5" parameterType="string" resultType="Person" resultMap="rs5">
select *,p.id as p_id, e.id as e_id, a.id as a_id,e.personid as e_pid ,a.personid as a_pid,o.id as o_id,o.personid as o_pid
from person p left join address a on p.id=a.personid
left join extinfo e on p.id = e.personid
left join orders o on p.id = o.personid
where p.name like '%b%'
</select>


<insert id="insert" parameterType="Person" useGeneratedKeys="true" keyProperty="id">
insert into person(name,pswd) values(#{name},#{pswd})
</insert>
<update id="update" parameterType="Person">
update person set name=#{name},pswd=#{pswd} where id=#{id}
</update>
<select id="load" parameterType="long" resultType="Person" resultMap="rs_base">
select * from person where id = #{value}
</select>
<delete id="delete" parameterType="long">
delete from person where id = #{value}
</delete>
<!-- 查询结果集为Map -->
<select id="load4Map" resultType="map">
select * from person
</select>
</mapper>
 


<!--Extinfo-->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="Extinfo">
<resultMap id="rs_base" type="Extinfo">
<id column="id" property="id"/>
<result property="personid" column="personid"/>
<result property="email" column="email"/>
<result property="qq" column="qq"/>
</resultMap>
<select id="queryByPersonid" parameterType="long" resultType="Extinfo" resultMap="rs_base">
select * from extinfo where personid=#{value}
</select>
</mapper>




<!--Address-->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="Address">
<resultMap id="rs_base" type="Address">
<id column="id" property="id"/>
<result property="personid" column="personid"/>
<result property="addr" column="addr"/>
<result property="zipcode" column="zipcode"/>
</resultMap>
<select id="queryByPersonid" parameterType="long" resultType="Address" resultMap="rs_base">
select * from address where personid=#{value}
</select>
</mapper>




<!--Order-->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="Orders">
<resultMap id="rs_base" type="Orders">
<id column="id" property="id"/>
<result property="personid" column="personid"/>
<result property="product" column="product"/>
<result property="num" column="num"/>
</resultMap>
<select id="queryByPersonid" parameterType="long" resultType="Orders" resultMap="rs_base">
select * from orders where personid=#{value}
</select>
</mapper>








#全局日志配置
log4j.rootLogger=debug, stdout
#包下所有类的日志级别
log4j.logger.org.apache.ibatis=debug
log4j.logger.java.sql.Connection=info, stdout
log4j.logger.java.sql.Statement=debug, stdout
log4j.logger.java.sql.PreparedStatement=debug, stdout
#日志输出到控制台
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %l  %m%n
#关闭Spring日志
log4j.category.org.springframework = OFF
 
 


  Total: 15
Person{id=1, name='张三b', pswd='111'}
Extinfo{id=1, personid=1, email='adf@as.com', qq=1231412341}
Address{id=1, personid=1, addr='adsaaaa', zipcode='2342342'}
Address{id=2, personid=1, addr='werwqfqw', zipcode='2234234'}
Address{id=3, personid=1, addr='qwefaz', zipcode='2342342'}
Orders{id=1, personid=1, product='aaa', num=2}
Person{id=2, name='李四b', pswd='222'}
Extinfo{id=2, personid=2, email='jkks@sa.com', qq=827238782}
Address{id=4, personid=2, addr='vzczsd', zipcode='13234234'}
Address{id=5, personid=2, addr='aaaaaaaaa', zipcode='2342356'}
Address{id=6, personid=2, addr='asawsd', zipcode='4564565'}
Orders{id=2, personid=2, product='bbb', num=3}
Orders{id=3, personid=2, product='ccc', num=1}
Person{id=3, name='王五b', pswd='111'}
Extinfo{id=3, personid=3, email='8238@aa.com', qq=234253234}
Address{id=7, personid=3, addr='jkhkky', zipcode='2342342'}
Orders{id=4, personid=3, product='ddd', num=3}
Orders{id=5, personid=3, product='asdf', num=21}
Person{id=5, name='ggggb', pswd='password'}
Extinfo{id=5, personid=5, email='2323423W@asd.com', qq=8992837422}
Orders{id=7, personid=5, product='zzdfa', num=232}
Orders{id=8, personid=5, product='ggg', num=66}
Person{id=15, name='testnameb', pswd='password'}
Person{id=19, name='testnameb', pswd='password'}
Process finished with exit code 0