MyBatis3一对一关系映射
程序员文章站
2022-07-13 16:15:05
...
1. MyBatis3关系映射,一对一关系
2. 使用mybatis查询一对一关系关联表的方式
3. 使用mybatis查询关联表
创建t_address表 create table t_address ( id int(11) NOT NULL AUTO_INCREMENT, sheng varchar(20) DEFAULT NULL, shi varchar(20) DEFAULT NULL, qu varchar(20) DEFAULT NULL, PRIMARY KEY(id) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; 插入数据 insert into t_address(id, sheng, shi, qu) values(1, '北京市', '北京市', '东城区'); insert into t_address(id, sheng, shi, qu) values(2, '河北省', '石家庄市', '新华区'); 在t_student表增加addressId关联字段 alter table t_student add addressId INT(11); 如果使用外键(当前情况不需要): alter table t_student add FOREIGN KEY fk_student_address(addressId) REFERENCES t_address(id); alter table t_student drop foreign key t_student_ibfk_1; alter table t_student drop index fk_student_address;
2. 使用mybatis查询一对一关系关联表的方式
1. 不推荐 <resultMap type="Student" id="StudentAddressResult"> <id property="id" column="id"/> <result property="name" column="name"/> <result property="age" column="age"/> <result property="address.id" column="addressId"/> <result property="address.sheng" column="sheng"/> <result property="address.shi" column="shi"/> <result property="address.qu" column="qu"/> </resultMap> 2. 不推荐 <resultMap type="Address" id="AddressResult"> <result property="id" column="id"/> <result property="sheng" column="sheng"/> <result property="shi" column="shi"/> <result property="qu" column="qu"/> </resultMap> <resultMap type="Student" id="StudentAddressResult"> <id property="id" column="id"/> <result property="name" column="name"/> <result property="age" column="age"/> <association property="address" resultMap="AddressResult"/> </resultMap> 3. 不推荐 <resultMap type="Student" id="StudentAddressResult"> <id property="id" column="id"/> <result property="name" column="name"/> <result property="age" column="age"/> <association property="address" javaType="Address"> <result property="id" column="id"/> <result property="sheng" column="sheng"/> <result property="shi" column="shi"/> <result property="qu" column="qu"/> </association> </resultMap> 4. 推荐(column对应的是sql表中的外键) StudentMapper.xml <resultMap type="Student" id="StudentAddressResult"> <id property="id" column="id"/> <result property="name" column="name"/> <result property="age" column="age"/> <association property="address" column="addressId" select="com.andrew.mappers.AddressMapper.findById"></association> </resultMap> AddressMapper.xml <resultMap type="Address" id="AddressResult"> <result property="id" column="id"/> <result property="sheng" column="sheng"/> <result property="shi" column="shi"/> <result property="qu" column="qu"/> </resultMap> <select id="findById" parameterType="Integer" resultType="Address"> select * from t_address where id=#{id} </select>
3. 使用mybatis查询关联表
package com.andrew.model; public class Address { private Integer id; private String sheng; private String shi; private String qu; @Override public String toString() { return "Address [id=" + id + ", sheng=" + sheng + ", shi=" + shi + ", qu=" + qu + "]"; } // getter and setter }
package com.andrew.model; public class Student { private Integer id; private String name; private Integer age; private Address address; public Student() { super(); } public Student(String name, Integer age) { super(); this.name = name; this.age = age; } public Student(Integer id, String name, Integer age) { super(); this.id = id; this.name = name; this.age = age; } @Override public String toString() { return "Student [id=" + id + ", name=" + name + ", age=" + age + ", address=" + address + "]"; } // getter and setter }
package com.andrew.mappers; import com.andrew.model.Address; public interface AddressMapper { public Address findById(Integer id); }
<?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.andrew.mappers.AddressMapper"> <resultMap type="Address" id="AddressResult"> <result property="id" column="id"/> <result property="sheng" column="sheng"/> <result property="shi" column="shi"/> <result property="qu" column="qu"/> </resultMap> <select id="findById" parameterType="Integer" resultType="Address"> select * from t_address where id=#{id} </select> </mapper>
package com.andrew.mappers; import java.util.List; import com.andrew.model.Student; public interface StudentMapper { public int add(Student student); public int update(Student student); public int delete(Integer id); public Student findById(Integer id); public List<Student> find(); public Student findStudentWithAddress(Integer id); }
<?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.andrew.mappers.StudentMapper"> <resultMap type="Student" id="StudentResult"> <id property="id" column="id"/> <result property="name" column="name"/> <result property="age" column="age"/> </resultMap> <resultMap type="Student" id="StudentAddressResult"> <id property="id" column="id"/> <result property="name" column="name"/> <result property="age" column="age"/> <association property="address" column="addressId" select="com.andrew.mappers.AddressMapper.findById"></association> </resultMap> <insert id="add" parameterType="Student"> insert into t_student values(null, #{name}, #{age}) </insert> <update id="update" parameterType="Student"> update t_student set name=#{name},age=#{age} where id=#{id} </update> <delete id="delete" parameterType="Integer"> delete from t_student where id=#{id} </delete> <select id="findById" parameterType="Integer" resultType="Student"> select * from t_student where id=#{id} </select> <select id="find" resultMap="StudentAddressResult"> select * from t_student </select> <select id="findStudentWithAddress" resultMap="StudentAddressResult" parameterType="Integer"> select * from t_student t1,t_address t2 where t1.addressId=t2.id and t1.id=#{id} </select> </mapper>
package com.andrew.service; import org.apache.ibatis.session.SqlSession; import org.apache.log4j.Logger; import org.junit.After; import org.junit.Before; import org.junit.Test; import com.andrew.mappers.StudentMapper; import com.andrew.model.Student; import com.andrew.util.SqlSessionFactoryUtil; public class StudentAddressTest { private static Logger logger=Logger.getLogger(StudentTest.class); private SqlSession sqlSession=null; private StudentMapper studentMapper=null; @Before public void setUp() throws Exception { sqlSession = SqlSessionFactoryUtil.openSession(); studentMapper = sqlSession.getMapper(StudentMapper.class); } @After public void tearDown() throws Exception { sqlSession.close(); } @Test public void testFindStudentWithAddress() { logger.info("查询学生和地址"); Student student = studentMapper.findStudentWithAddress(2); System.out.println(student); } } 运行结果: Student [id=2, name=李四, age=20, address=Address [id=2, sheng=河北省, shi=石家庄市, qu=新华区]]
上一篇: MyBatis3一对多关系映射
下一篇: MyBatis3缓存与分页