Mybatis 一对多 一对一
配置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">
<!-- 通过这个配置文件完成mybatis与数据库的连接 -->
<configuration>
<!-- 引入 database.properties 文件 -->
<properties resource="database.properties" />
<!-- 配置mybatis的log实现为LOG4J -->
<settings>
<setting name="logImpl" value="LOG4J" />
</settings>
<typeAliases>
<typeAlias type="pd.entity.Book" alias="Book" />
<typeAlias type="pd.entity.User" alias="User" />
<typeAlias type="pd.entity.Group" alias="Group" />
<typeAlias type="pd.entity.Person" alias="Person" />
<typeAlias type="pd.entity.Table" alias="Table" />
</typeAliases>
<environments default="development">
<environment id="development">
<!--配置事务管理,采用JDBC的事务管理 -->
<transactionManager type="JDBC"></transactionManager>
<!-- POOLED:mybatis自带的数据源,JNDI:基于tomcat的数据源 -->
<dataSource type="POOLED">
<property name="driver" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${user}" />
<property name="password" value="${password}" />
</dataSource>
</environment>
</environments>
<!-- 将mapper文件加入到配置文件中 -->
<mappers>
<mapper resource="pd/dao/UserMapper.xml" />
<mapper resource="pd/dao/BookMapper.xml" />
<mapper resource="pd/dao/PersonMapper.xml" />
<mapper resource="pd/dao/GroupMapper.xml" />
<mapper resource="pd/dao/TableMapper.xml" />
</mappers>
</configuration>
一对一关联
<resultMap type="本类别名" id="父类方法名">
中间是本类基本属性
<id property="id" column="r.id"></id>
<result property="userCode" column="userCode"/>
<result property="userNmae" column="userNmae"/>
</resultMap>
<resultMap type="本类别名" id="子类方法名" extends="父类resultMap的id名">
本类里的声明的属性名books本类里声明的属性类型BOOK对方命名空间.resultMap的id名
<collection property="addresses" ofType="Address" resultMap="com.bdqn.dao.getAddress"></collection>
</resultMap>
查的时候就用子类id名
多对一关联
<resultMap type="本类别名" id="方法名">
中间是本类属性
<id property="id" column="r.id"></id>
<result property="userCode" column="userCode"/>
<result property="userNmae" column="userNmae"/>
<result property="userRole" column="userRole"/>
<!-- 一对一 关联 User要多一个属性-->
本类里声明的属性名books 本类里声明的属性类型BOOK
<association property="role" javaType="role">
对方属性名
<id property="id" column="rl_id"/>
<result property="roleCode" column="roleCode"/>
<result property="roleName" column="roleName"/>
</association>
</resultMap>
代码演示
<?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="pd.dao.TestDao">
<resultMap type="Book" id="BookBasicMap">
<id property="bid" column="bid" />
<result property="bname" column="bname" />
</resultMap>
<resultMap type="Book" id="BookMap" extends="BookBasicMap">
<!--property="user"是book里面声明的 column="userid" 因为userid对应数据库User里的userid javaType="User"是pd.entity.User -->
<association property="user" column="userid" javaType="User"
resultMap="pd.dao.TestDao.UserBasicMap"></association>
</resultMap>
<!--一对一 -->
<select id="getBooks" resultMap="BookMap">
select *from t_book t1 left join
t_user t2 on t1.userid= t2.uid
</select>
<resultMap type="Book" id="BookMap2" extends="BookBasicMap">
<!--如果esultMap type="Book的property user信息想填充这个user属性请根据select= pd.dao.TestDao.getUserByUid来填充
且要有column="userid -->
<!-- column="userid 通过getUserByUid里的 select *from t_user where uid=#{uid}
填充进去 -->
<association property="user" column="userid" javaType="User"
select="pd.dao.TestDao.getUserByUid"></association>
</resultMap>
<select id="getBooks2" resultMap="BookMap2">
select *from t_book
</select>
</mapper>
<?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="pd.dao.TestDao">
<resultMap type="Group" id="GroupBasicMap">
<id property="gid" column="gid" />
<result property="gname" column="gname" />
</resultMap>
<!-- resultMap="pd.dao.TestDao.PerBasicMap是路径加id -->
<resultMap type="Group" id="GroupMap" extends="GroupBasicMap">
<collection property="personList" ofType="Person"
resultMap="pd.dao.TestDao.PerBasicMap"></collection>
</resultMap>
<select id="getGroupList" resultMap="GroupMap">
select *from t_group t1
left join t_person_group t2 on t1.gid=t2.groupid
left join t_person t3 on
t2.personid=t3.pid
</select>
</mapper>
<?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="pd.dao.TestDao">
<resultMap type="Person" id="PerBasicMap">
<id property="pid" column="pid" />
<result property="pname" column="pname" />
</resultMap>
<!-- resultMap="pd.dao.TestDao.PerBasicMap是路径加id -->
<resultMap type="Person" id="PersonMap" extends="PerBasicMap">
<collection property="groups" ofType="Group"
resultMap="pd.dao.TestDao.GroupBasicMap"></collection>
</resultMap>
<select id="getPersonList" resultMap="PersonMap">
select *from t_person t1
left join t_person_group t2 on t1.pid=t2.personid
left join t_group t3
on t2.groupid=t3.gid
</select>
</mapper>
<?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="pd.dao.TestDao">
<resultMap type="Table" id="tables">
<result property="tid" />
<result property="tname" />
</resultMap>
<select id="getTable" resultMap="tables">
select *from tablename1
</select>
<select id="getTableByTid" resultMap="tables">
select *from tablename1 where
<if test="tid!=null">
tid=#{tid}
</if>
</select>
<delete id="deleteByTid" parameterType="int">
delete from tablename1
<where>
<if test="tid!=null">
tid=#{tid}
</if>
</where>
</delete>
<insert id="insertByTid" parameterType="String">
insert into tablename1 values(#{tid,jdbcType=VARCHAR},#{tname,jdbcType=VARCHAR})
</insert>
<update id="updateByTname" parameterType="String">
update tablename1
<set>
<if test="tname!=null">
tname=#{tname}
</if>
</set>
<where>
<if test="tid!=null">
tid=#{tid}
</if>
</where>
</update>
</mapper>
<?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="pd.dao.TestDao">
<select id="getUser1" resultType="User">
select *from t_user
</select>
<resultMap type="User" id="UserBasicMap">
<id property="uid" column="uid" />
<result property="uname" column="uname" />
</resultMap>
<!-- 在一对多或者多对一column要写出来 -->
<!-- 一个用户对多本书 -->
<!--继承 UserBasicMap -->
<!--通过resultMap引入book -->
<resultMap type="User" id="UserMap" extends="UserBasicMap">
<!--对多写法 -->
<collection property="books" ofType="Book" resultMap="pd.dao.TestDao.BookBasicMap" />
</resultMap>
<!-- 原本写法
<resultMap type="pd.entity.User" id="UserBasicMap">
<id property="uid" column="uid" />
<result property="uname" column="uname" />
对多写法
<collection property="books" ofType="pd.entity.Book">
<id property="bid" column="bid" />
<result property="bname" column="bname" />
</collection>
</resultMap> -->
<!-- 一对多 -->
<select id="getUser2" resultMap="UserMap">
select *from t_user t1 left join
t_book t2 on t1.uid=t2.userid
</select>
<!--
<resultMap type="User" id="UserMap1" extends="UserBasicMap">
<association property="boos" javaType="Book"
resultMap="pd.dao.TestDao.BookBasicMap"></association>
</resultMap>
<select id="getUser3" resultMap="UserMap1">
select *from t_user t1 left join
t_book t2 on t1.bookid= t2.bid
</select> -->
<select id="getUserByUid" parameterType="String" resultType="User">
select *from t_user where uid=#{uid}
</select>
</mapper>
package pd.untils;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class CopyOfPandaUntils {
private static CopyOfPandaUntils copyOfPandaUntils;
private static SqlSessionFactory factory;
private CopyOfPandaUntils() {
InputStream in = null;
String configFile = "mybatis-config.xml";
try {
in = Resources.getResourceAsStream(configFile);
factory = new SqlSessionFactoryBuilder().build(in);
} catch (Exception e) {
e.printStackTrace();
}
}
public static synchronized CopyOfPandaUntils getInstance() {
if (copyOfPandaUntils == null) {
synchronized (CopyOfPandaUntils.class) {
if (copyOfPandaUntils == null) {
copyOfPandaUntils = new CopyOfPandaUntils();
}
}
}
return copyOfPandaUntils;
}
public SqlSession getSession() {
return factory.openSession(false);
}
public static void closeSession(SqlSession session) {
if (session != null) {
session.close();
}
}
}
package test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import pd.dao.TestDao;
import pd.entity.Book;
import pd.entity.Group;
import pd.entity.Person;
import pd.entity.Table;
import pd.entity.User;
import pd.untils.CopyOfPandaUntils;
public class Test {
public static void main(String[] args) {
SqlSession session=null;
try {
session=CopyOfPandaUntils.getInstance().getSession();
/*单表查询
List<User>list=session.getMapper(TestDao.class).getUser1();
for(User bb:list){
System.out.println(bb);
}*/
//一对多
List<User>list=session.getMapper(TestDao.class).getUser2();
System.out.println("一对多");
for(User bb:list){
System.out.println(bb.toString());
}
//一对一
List<Book>list1=session.getMapper(TestDao.class).getBooks();
System.out.println("一对一");
for(Book bb1:list1){
System.out.println(bb1.toString());
}
/*List<User>list=session.getMapper(TestDao.class).getUser3();
for(User bb:list){
System.out.println(bb.toString());
}*/
/*多对多查询
* List<Person>list=session.getMapper(TestDao.class).getPersonList();
for(Person bb:list){
System.out.println(bb.toString());
}*/
/*List<Book>list=session.getMapper(TestDao.class).getBooks2();
for(Book bb:list){
System.out.println(bb.toString());
}*/
/*单表全查
* List<Table>list=session.getMapper(TestDao.class).getTable();
for(Table bb:list){
System.out.println(bb.toString());
}*/
//根据ID查询
/* List<Table>list=session.getMapper(TestDao.class).getTableByTid("1");
for(Table bb:list){
System.out.println(bb.toString());
}*/
/*删除
* int count=session.getMapper(TestDao.class).deleteByTid("5");
System.out.println(count);*/
// 插入
/* int count=session.getMapper(TestDao.class).insertByTid("7", "liu");
System.out.println(count);*/
/*更改
* int count=session.getMapper(TestDao.class).updateByTname("cool", "5");
System.out.println(count);*/
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}finally{
CopyOfPandaUntils.closeSession(session);
}
}
}