【Mybatis学习笔记】系列之二:Mybatis双项一对多关联
来源:互联网 发布:股票策略交易软件 编辑:程序博客网 时间:2024/05/02 05:55
条件:Group和Student是一对多关系
一:GroupMapper.xml内容如下
<?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="com.company.dao.IGroupDAO">
<resultMap type="Group" id="groupResultMap" > <id column="g_id" property="id"/> <result column="g_name" property="name"/> <result column="g_position" property="position"/> </resultMap> <sql id="getALl">select * from</sql> <!-- 意图想通过获得组和组中的所有student,此处相当于one2many --> <resultMap type="Group" id="getGroupAndStudents"> <id column="g_id" property="id"/> <result column="g_name" property="name"/> <result column="g_position" property="position"/> <collection property="students" ofType="Student" column="group_id"><!-- 注意此处的group_id是student表的外键 --> <id column="id" property="id"/> <result column="name" property="name"/> <result column="birth" property="birth"/> </collection> </resultMap> <select id="getById" parameterType="int" resultMap="getGroupAndStudents"> select g.g_id,g.g_name,g.g_position,s.id,s.name,s.birth ,s.group_id from g_group g left join student s on g.g_id = s.group_id where g.g_id = #{id} </select> <select id="getByIdResultMap" parameterType="_int" resultMap="groupResultMap"> select g_id ,g_name, g_position from g_group where g_id=#{id} </select> <delete id="deleteById" parameterType="_int" timeout="1000"> delete from g_group where g_id=#{id} </delete> <insert id="add" parameterType="Group"> insert into g_group(g_id, g_name, g_position) values(#{id}, #{name}, #{position}) </insert> </mapper>
二:StudentMapper.xml内容如下
<?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="com.company.dao.IStudentDAO"> <!-- mybatis缓存 --> <cache eviction="LRU" flushInterval="600000" size="1024" readOnly="false" /> <!-- sql标签用来定义一些可以被重用的sql语句或字段或片段等 --> <sql id="studentColumns">select id,name,birth from student</sql> <!-- 此处获得多对一的关系 ,但就单条记录而言却是一对一的关系,所以一对一的写法跟此相同--> <resultMap type="Student" id="getStudentAndGroup" > <id column="id" property="id"/> <result column="name" property="name"/> <result column="birth" property="birth"/> <association property="group" column="group_id" javaType="Group"> <id column="g_id" property="id"/> <result column="g_name" property="name"/> <result column="g_position" property="position"/> </association> </resultMap> <select id="many2one" resultMap="getStudentAndGroup" parameterType="int" > select s.id,s.name,s.birth,s.group_id,g.g_id,g.g_name,g.g_position from student s left join g_group g on s.group_id = g.g_id where s.id = #{id} </select> <!-- 意图是获得一个学生,并且获得该学生所属的组,跟上面的意思差不多 ,用association的select属性--> <!-- 于上面的相比个人感觉上面的效率要高些,因为上面只有一条sql语句 --> <resultMap type="Student" id="getStudentAndGroupUseSelectMap"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="birth" property="birth"/> <association property="group" column="group_id" javaType="Group" select="selectGroup" /> </resultMap> <select id="getStudentAndGroupUseSelect" resultMap="getStudentAndGroupUseSelectMap" parameterType="int"> select * from student where id = #{id} </select> <select id="selectGroup" resultType="Group" parameterType="int" flushCache="false" useCache="true"><!-- 此处实用缓存 --> select g_id as id, g_name as name, g_position as position from g_group where g_id = #{id} </select> <!-- 动态sql语句 的测试dynamic sql--> <select id="getStudentBySomeCondition" parameterType="Student" resultType="Student"> select * from student <where> <if test="id != null"> id>2 </if> <if test="name != null"> and name like '%g%' </if> </where> </select> <!-- MyBatis调用存储过程 --> <resultMap type="Student" id="studentMap"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="birth" property="birth"/> </resultMap> <select id="getAllUser" statementType="CALLABLE" > {call get_all_student(#{students ,mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=studentMap} )} </select> <!-- MyBatis向student表中插入一条数据 --> <insert id="add" parameterType="Student" keyColumn="id"> <selectKey keyProperty="id" order="BEFORE" resultType="int"> select stu_id_sequence.nextval from dual </selectKey> insert into student(id,name,birth) values(#{id},#{name},#{birth}) </insert> <!-- 根据id获得学生的信息 --> <select id="getById" parameterType="int" resultType="Student"> <include refid="studentColumns"/> where id=#{id} </select> <!-- 此处的实现方法是一个分页的原型,请查看IStudentDAOImpl.java中的调用方法 --> <select id="getAllStudent" resultMap="studentMap"> <include refid="studentColumns"/> order by id<!--此处是引用了上面预定义好的sql语句--> </select> </mapper>