1.MyBatis环境搭建
1.创建工程
2.引入相关依赖(pom.xml)
3.创建数据库链接信息配置文件(db.properties)
4.创建MyBatis核心配置文件(mybatis-config.xml)
5.创建log4j.xml的配置文件
1.1创建工程
1.2 引入相关依赖
在OneToOne中引入相关依赖
<?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>
<parent>
<groupId>com.biem</groupId>
<artifactId>mybatis-4ch</artifactId>
<version>1.0-SNAPSHOT</version>
</parent>
<artifactId>OneToOne</artifactId>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.18</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.12</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.0</version>
</dependency>
</dependencies>
</project>
1.3 创建数据库链接信息配置文件(db.properties)
mysql.driver=com.mysql.cj.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false
mysql.username=root
mysql.password=123
1.4 创建MyBatis核心配置文件(MybatisConfig.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="db.properties"/>
<typeAliases>
<package name="com.biem.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<!-- 数据库连接相关配置 ,db.properties文件中的内容-->
<dataSource type="POOLED">
<property name="driver" value="${mysql.driver}"/>
<property name="url" value="${mysql.url}"/>
<property name="username" value="${mysql.username}"/>
<property name="password" value="${mysql.password}"/>
</dataSource>
</environment>
</environments>
<!-- mapping文件路径配置 -->
<mappers>
<package name="com.biem.mapper"/>
</mappers>
</configuration>
1.5 创建log4j.xml的配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
<param name="Encoding" value="UTF-8"/>
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \n"/>
</layout>
</appender>
<logger name="java.sql">
<level value="debug"/>
</logger>
<logger name="org.apache.ibatis">
<level value="info"/>
</logger>
<root>
<level value="debug"/>
<appender-ref ref="STDOUT"/>
</root>
</log4j:configuration>
2.一对一查询
2.1 数据库准备
USE mybatis;
# 创建一个名称为tb_idcard的表
CREATE TABLE tb_idcard(
id INT PRIMARY KEY AUTO_INCREMENT,
CODE VARCHAR(18)
);
# 插入2条数据
INSERT INTO tb_idcard(CODE) VALUES('152221198711020624');
INSERT INTO tb_idcard(CODE) VALUES('152201199008150317');
# 创建一个名称为tb_person的表
CREATE TABLE tb_person(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32),
age INT,
sex VARCHAR(8),
card_id INT UNIQUE,
FOREIGN KEY(card_id) REFERENCES tb_idcard(id)
);
# 插入2条数据
INSERT INTO tb_person(name,age,sex,card_id) VALUES('Rose',22,'女',1);
INSERT INTO tb_person(name,age,sex,card_id) VALUES('jack',23,'男',2);
2.2 实体类
2.2.1 IdCard.java
package com.biem.pojo;
import lombok.*;
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class IdCard {
private Integer id;
private String code;
}
2.2.2 Person.java
package com.biem.pojo;
import lombok.*;
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Person {
private Integer id;
private String name;
private Integer age;
private String sex;
private IdCard card;
}
2.3 用户配置接口
2.3.1 IdCardMapper.java
package com.biem.mapper;
public interface IdCardMapper{
}
2.3.2 Person.java
package com.biem.mapper;
public interface PersonMapper {
}
2.4 用户配置文件
2.4.1 IdCardMapper.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.biem.mapper.IdCardMapper">
</mapper>
2.4.2 PersonMapper.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.biem.mapper.PersonMapper">
</mapper>
2.5 解决方法
2.5.1 嵌套查询方式
2.5.1.1 IdCardMapper.java
package com.biem.mapper;
import com.biem.pojo.IdCard;
public interface IdCardMapper {
public IdCard findCodeById(Integer id);
}
2.5.1.2 PersonMapper.java
package com.biem.mapper;
import com.biem.pojo.Person;
public interface PersonMapper {
public Person findPersonById(Integer id);
}
2.5.1.3 IdCardMapper.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.biem.mapper.IdCardMapper">
<!--public IdCard findCodeById(Integer id);-->
<select id="findCodeById" parameterType="Integer" resultType="IdCard">
select * from tb_idcard where id=#{id}
</select>
</mapper>
2.5.1.4 PersonMapper.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.biem.mapper.PersonMapper">
<!-- public Person findPersonById(Integer id); -->
<resultMap id="IdCardWithPersonResult" type="Person">
<id property="id" column="id"></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.biem.mapper.IdCardMapper.findCodeById"/>
</resultMap>
<select id="findPersonById" parameterType="Integer" resultMap="IdCardWithPersonResult">
select * from tb_person where id = #{id}
</select>
</mapper>
2.5.1.5 PersonTest.java
package com.biem.test;
import com.biem.mapper.PersonMapper;
import com.biem.pojo.Person;
import com.biem.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
public class PersonTest {
@Test
public void testFindPersonById(){
SqlSession session = MyBatisUtil.openSession();
PersonMapper mapper = session.getMapper(PersonMapper.class);
Person person = mapper.findPersonById(1);
System.out.println(person);
session.close();
}
}
2.5.1.6 测试结果
3.一对多查询
问题:查询用户以及关联的订单,一个用户有多个订单
3.1 数据库准备
USE mybatis;
# 创建一个名称为tb_user的表
CREATE TABLE tb_user (
id int(32) PRIMARY KEY AUTO_INCREMENT,
username varchar(32),
address varchar(256)
);
# 插入3条数据
INSERT INTO tb_user VALUES ('1', '小明', '北京');
INSERT INTO tb_user VALUES ('2', '李华', '上海');
INSERT INTO tb_user VALUES ('3', '李刚', '上海');
# 创建一个名称为tb_orders的表
CREATE TABLE tb_orders (
id int(32) PRIMARY KEY AUTO_INCREMENT,
number varchar(32) NOT NULL,
user_id int(32) NOT NULL,
FOREIGN KEY(user_id) REFERENCES tb_user(id)
);
# 插入3条数据
INSERT INTO tb_orders VALUES ('1', '1000011', '1');
INSERT INTO tb_orders VALUES ('2', '1000012', '1');
INSERT INTO tb_orders VALUES ('3', '1000013', '2');
3.2 实体类
3.2.1 Orders.java
package com.biem.pojo;
import lombok.*;
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Orders {
private Integer id;
private String number;
private Integer userId;
}
3.2.2 Users.java
package com.biem.pojo;
import lombok.*;
import java.util.List;
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Users {
private Integer id;
private String username;
private String address;
private List<Orders> ordersList;
}
3.3 用户配置接口
3.3.1 OrdersMapper.java
package com.biem.mapper;
public interface OrdersMapper {
}
3.3.2 UsersMapper.java
package com.biem.mapper;
public interface UsersMapper {
}
3.4 用户配置文件
3.4.1 OrdersMapper.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.biem.mapper.OrdersMapper">
</mapper>
3.4.2 UsersMapper.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.biem.mapper.UsersMapper">
</mapper>
3.5 解决办法
3.5.1 嵌套查询方式
3.5.1.1 OrdersMapper.java
package com.biem.mapper;
public interface OrdersMapper {
public List<Orders> selectOrders(Integer uid);
}
3.5.1.2 UsersMapper.java
package com.biem.mapper;
import com.biem.pojo.User;
public interface UserMapper {
public User findUserById(int id);
}
3.5.1.3 OrdersMapper.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.biem.mapper.OrdersMapper">
<!-- public List<Orders> selectOrders(Integer user_id); -->
<select id="selectOrders" parameterType="Integer" resultType="Orders">
select * from tb_orders where user_id=#{userId}
</select>
</mapper>
3.5.1.4 UsersMapper.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.biem.mapper.UsersMapper">
<resultMap id="usersWithOrders1" type="Users">
<id property="id" column="id"></id>
<result property="username" column="username"/>
<result property="address" column="address"/>
<!--由于查询老师中有一个属性为所有学生对象集合,所一使用collection标签进行嵌套,
方法的返回值是List,所以javaType使用ArrayList来接收,而list中泛型约束类型放的
是orders对象所以使用ofType为orderst来进行映射,使用select进行子查询,并用column
将查询出的id字段传给子查询需要的id-->
<!--此处javaType可以省略-->
<collection property="ordersList" javaType="ArrayList" ofType="orders" select="com.biem.mapper.OrdersMapper.selectOrders" column="id"/>
</resultMap>
<!-- public Users findUserById(Integer id); -->
<select id="findUserById" parameterType="Integer" resultMap="usersWithOrders1">
select * from tb_user where id = #{id}
</select>
</mapper>
3.5.1.5 UsersTest.java
import org.junit.Test;
public class UsersTest {
@Test
public void testFindUserById(){
SqlSession session = MyBatisUtil.openSession();
UsersMapper mapper = session.getMapper(UsersMapper.class);
Users user = mapper.findUserById(1);
System.out.println("user = " + user);
session.close();
}
}
3.5.1.6 测试结果
3.5.2 嵌套结果方式
3.5.2.1 UsersMapper.java添加
public Users findUserByIdWithNestedResults(Integer id);
3.5.2.2 UsersMapper.xml添加
<!-- public Users findUserByIdWithNestedResults(Integer id); -->
<!-- 一对多:查看某一个用户及关联的订单信息,
注意:当关联查询出来的列名相同时,则需要使用别名区分 -->
<select id="findUserByIdWithNestedResults" parameterType="Integer" resultMap="UsersWithOrdersResult" >
select u.*, o.id as orders_id , o.number, o.user_id
from tb_user u, tb_orders o
where u.id = o.user_id and u.id=#{id}
</select>
<resultMap id="UsersWithOrdersResult" type="Users">
<id property="id" column="id"></id>
<result property="username" column="username"/>
<result property="address" column="address"/>
<collection property="ordersList" ofType="Orders">
<id property="id" column="orders_id"></id>
<result property="number" column="number"/>
<result property="userId" column="user_id"/>
</collection>
</resultMap>
3.5.2.3 UsersTest.java添加
@Test
public void testFindUserByIdWithNestedResults(){
SqlSession session = MyBatisUtil.openSession();
UsersMapper mapper = session.getMapper(UsersMapper.class);
Users user = mapper.findUserByIdWithNestedResults(1);
System.out.println("user = " + user);
session.close();
}
3.5.2.4 测试结果