MyBatis 关联映射
数据库准备
SELECT
*
FROM
person
LEFT JOIN idcard ON person.id = idcard.id
仔细区分上下区别
SELECT
*
FROM
person
JOIN idcard USING ( id ) //建议记住这种方法 using 关键字 别忘记括号() 答应我好嘛
自己做个小实验 仔细体会他们的区别
第一组实验
SELECT
*
FROM
person
LEFT JOIN idcard ON person.id = idcard.id
AND person.id =1
SELECT
*
FROM
person
LEFT JOIN idcard ON person.id = idcard.id
where person.id =1
第二组实验
SELECT
*
FROM
person
inner JOIN idcard ON person.id = idcard.id
and person.id =1
SELECT
*
FROM
person
inner JOIN idcard ON person.id = idcard.id
WHERE person.id =1
一对一关联映射
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.china</groupId>
<artifactId>CheckIncidenceMapping</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>5.1.2.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-web -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>5.1.2.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-oxm -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-oxm</artifactId>
<version>5.1.2.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.1.2.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.1.2.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>5.1.2.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.1.2.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context-support -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>5.1.2.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-aop -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>5.1.2.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-orm -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>5.1.2.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-test -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.1.2.RELEASE</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.13</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/jstl/jstl -->
<dependency>
<groupId>jstl</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.53</version>
</dependency>
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-api -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.25</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-log4j12 -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.25</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.codehaus.jackson/jackson-mapper-asl -->
<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-mapper-asl</artifactId>
<version>1.9.13</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-logging/commons-logging -->
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.5.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-pool2 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-pool2</artifactId>
<version>2.6.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.12</version>
</dependency>
</dependencies>
</project>
pojo,util文件夹 IdCard ,Person,MybatisUtils
package com.shanghai.pojo;
public class IdCard {
private Integer id;
private String code;
public IdCard() {
}
@Override
public String toString() {
return "IdCard{" +
"id=" + id +
", code='" + code + '\'' +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public IdCard(Integer id, String code) {
this.id = id;
this.code = code;
}
}
-------------------------------------------------------------------------------------------
package com.shanghai.pojo;
public class Person {
private Integer id;
private String name;
private Integer age;
private String sex;
private IdCard card;
@Override
public String toString() {
return "Person{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", sex='" + sex + '\'' +
", card=" + card +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public IdCard getCard() {
return card;
}
public void setCard(IdCard card) {
this.card = card;
}
public Person() {
}
public Person(Integer id, String name, Integer age, String sex, IdCard card) {
this.id = id;
this.name = name;
this.age = age;
this.sex = sex;
this.card = card;
}
}
-----------------------------------------------------------------------------
package com.shanghai.utils;
import java.io.Reader;
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 MybatisUtils {
public static SqlSessionFactory sqlSessionFactory = null;
// 初始化SqlSessionFactory对象
static{
try {
//使用MyBatis提供的Resources类加载mybatis的配置文件
Reader reader = Resources.getResourceAsReader("mybatis/mybatis_config.xml");
//构建sqlSession的工厂
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取SqlSession对象的静态方法
public static SqlSession getSession(){
return sqlSessionFactory.openSession();
}
}
数据库文件夹
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC
jdbc.username=root
jdbc.password=root
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="com.shanghai.pojo.IdCard">
<select id="findCodeById" parameterType="Integer" resultType="IdCard" >
/*根据id查询证件信息*/
select * from idcard where id=#{id}
</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="com.shanghai.pojo.Person">
<!--嵌套查询:通过执行另外一条SQL映射语句来返回预期的特殊类型-->
<select id="findPersonById" parameterType="Integer" resultMap="IdCardWithPersonResult" >
select * from person where id=#{id}
</select>
<resultMap id="IdCardWithPersonResult" type="Person">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<!--一对一:association使用select属性引入另外一条SQL语句-->
<association property="card" column="card_id" javaType="IdCard"
select="com.shanghai.pojo.IdCard.findCodeById"/>
</resultMap>
</mapper>
mybatis 文件夹
<?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="databases/db.properties"/>
<!--使用扫描包的形式定义别名-->
<typeAliases>
<package name="com.shanghai.pojo"/>
</typeAliases>
<!--配置环境，默认的环境id为mysq1-->
<environments default="mysql">
<!--配置id为mysq1的数据库环境-->
<environment id="mysql">
<!--使用JDBC的事务管理-->
<transactionManager type="JDBC"/>
<!--数据库连接池-->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--配置Mapper的位置-->
<mappers>
<mapper resource="mapper/idCardMapper.xml"/>
<mapper resource="mapper/PersonMapper.xml"/>
</mappers>
</configuration>
日志文件
log4j.rootLogger = ERROR,stdout
log4j.logger.com.shanghai.pojo = DEBUG
log4j.appender.stdout = org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target = System.out
log4j.appender.stdout.layout = org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern = [%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} method:%l%n%m%n
log4j.appender.D = org.apache.log4j.DailyRollingFileAppender
log4j.appender.D.File = E://logs/log.log
log4j.appender.D.Append = true
log4j.appender.D.Threshold = DEBUG
log4j.appender.D.layout = org.apache.log4j.PatternLayout
log4j.appender.D.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss} [ %t:%r ] - [ %p ] %m%n
log4j.appender.E = org.apache.log4j.DailyRollingFileAppender
log4j.appender.E.File =E://logs/error.log
log4j.appender.E.Append = true
log4j.appender.E.Threshold = ERROR
log4j.appender.E.layout = org.apache.log4j.PatternLayout
log4j.appender.E.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss} [ %t:%r ] - [ %p ] %m%n
测试类
package test;
import com.shanghai.pojo.Person;
import com.shanghai.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
public class MybatisAssociatedTest {
//一对一
@Test
public void findPersonByIdTest(){
//1.通过工具类生成SqlSession对象
SqlSession session= MybatisUtils.getSession();
//2.使用MyBatis嵌套查询的方式查询id为1的人的信息
Person person=session.selectOne("com.shanghai.pojo.Person.findPersonById",1);
//3.输出查询结果信息
System.out.println(person);
//4.关闭SqlSession
session.close();
}
}
改进版本
PersonMapper.xml 中添加
<!--第二种方法-->
<select id="findPersonById2" parameterType="Integer" resultMap="IdCardWithPersonResult2" >
select *
from person t1
left join idcard t2
on t1.card_id=t2.id
where t1.id=#{id}
</select>
<resultMap id="IdCardWithPersonResult2" type="Person">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<association property="card" javaType="IdCard">
<id property="id" column="card_id"/>
<result property="code" column="code"/>
</association>
</resultMap>
在测试类中添加
@Test
public void findPersonByIdTest2(){
SqlSession session=MybatisUtils.getSession();
Person person=session.selectOne("com.shanghai.pojo.Person.findPersonById2",2);
System.out.println(person);
session.close();
}
posted on 2018-12-25 23:18 Indian_Mysore 阅读(235) 评论(0) 编辑 收藏 举报
