MyBatis·下
一、properties 属性
加载配置文件,实现连接数据库的信息,和mybatis主配置文件分离,实现连接数据库的数据独立维护。 注意:properties标记加载优先级高.
1.外部的属性文件
地址:src/main/resources/db.properties
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/mybatis
jdbc.username=root
jdbc.password=888888
2.MyBatis 配置文件
文件地址:src/main/resources/SqlMapConfig.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"></properties>👈
<!--数据库连接相关配置-->
<environments default="development">
<environment id="development">
<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>
<!--关联映射配置文件-->
<mappers>
<mapper resource="UserMapper.xml"></mapper>
</mappers>
</configuration>
二、Mybatis主配置文件
1.MyBatis 配置文件
文件地址:src/main/resources/SqlMapConfig.xml
typeAliases :类型命名
配置别名,在映射文件中多次出现重复的类地址,可以通过别名设置来简化写法,实现复用。
<configuration>
<properties resource="db.properties"></properties>
<typeAliases>
<!--为单个类设置别名-->
<typeAlias type="org.example.pojo.User" alias="user"></typeAlias>-->
<!--批量设置别名-->
<package name="org.example.pojo"/>
</typeAliases>
<!--数据库连接相关配置-->
<!--...-->
<!--关联映射配置文件-->
<!--...-->
</configuration>
2.映射文件
文件地址:src/main/resources/UserMapper.xml
替换原org,example.jojo.User。
三、MyBatis映射配置文件
1.配置依赖
打开maven主配置文件,地址:pom.xml
<build>
<resources>
<!--编译mapper的xml文件-->
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
</resource>
</resources>
</build>
2.MyBatis配置文件
文件地址:src/main/resources/SqlMapConfig.xml
<!--关联映射配置文件-->
<mappers>
<!--此时UserMapper.xml文件在resource下-->
<!--UserMapper文件在org.example.mapper下-->
<mapper resource="UserMapper.xml"></mapper>
<!--配置映射接口,要求映射文件和映射接口在同一目录下-->
<!--此时UserMapper和UserMapper.xml同在org.example.mapper下-->
<mapper class="org.example.mapper.UserMapper"></mapper>
<!--批量配置接口-->
<package name="org.example.mapper"/>
</mappers>
四、条件查询
PO(持久对象) 在数据访问层(DAO 层)中与数据库表进行映射和持久化。
public class User {
private int id;
private String username;
private String sex;
private Date birthday;
private String address;
👆get、set、toString方法
}
//以继承父类的方法添加个人属性
public class UserCustom extends User{...}
VO(值对象) 在表示层(Controller 层)中用于展示数据。
//前端数据交互
package org.example.pojo;
public class UserQueryVO {
private UserCustom userCustom;
public UserCustom getUserCustom() {
return userCustom;
}
public void setUserCustom(UserCustom userCustom) {
this.userCustom = userCustom;
}
}
1.添加条件
文件地址:src/main/java/org/example/mapper/UserMapper.xml
<!--用户信息综合查询-->
<select id="findUserList" parameterType="org.example.pojo.UserQueryVO" resultType="org.example.pojo.UserCustom">
select * from user where user.sex=#{userCustom.sex} and user.username like '%${userCustom.username}%'
</select>
👇
<select id="findUserList" parameterType="org.example.pojo.UserQueryVO" resultType="org.example.pojo.UserCustom">
select * from user
<where>
<if test="userCustom!=null">
<if test="userCustom.sex!=null and userCustom.sex!=''">
and user.sex=#{userCustom.sex}
</if>
<if test="userCustom.username!=null and userCustom.username!=''">
and user.username like '%${userCustom.username}%'
</if>
</if>
</where>
</select>
👇
<mapper namespace="org.example.mapper.UserMapper">
<sql id ='query_user_where'>
<if test="userCustom!=null">
<if test="userCustom.sex!=null and userCustom.sex!=''">
and user.sex=#{userCustom.sex}
</if>
<if test="userCustom.username!=null and userCustom.username!=''">
and user.username like '%${userCustom.username}%'
</if>
</if>
</sql>
<select id="findUserList" parameterType="org.example.pojo.UserQueryVO" resultType="org.example.pojo.UserCustom">
select
<include refid="selectColumn"></include>
from user
<where>
<include refid="query_user_where"></include>
</where>
</select>
</mapper>
2.替换条件
<select id="selectUserById" parameterType="int" resultType="user">
select id,username,sex,birthday,address from user where id=#{id}
</select>
👇
<sql id="selectColumn">
id,username,sex,birthday,address
</sql>
<select id="selectUserById" parameterType="int" resultType="user">
select
<include refid="selectColumn"></include>
from user where id=#{id}
</select>
3.属性名和列明不一致处理
<!-- resultMap,当对象的属性名和列明不一致时,使用resultMap输出映射进行配置-->
<resultMap id="userResultMap" type="org.example.pojo.User">
<!-- id用于匹配主键,result用于匹配非主键,column表示查询的列名,property表示匹配的对象的属性名-->
<id column="id_" property="id"></id>
<result column="username_" property="username"></result>
<result column="sex_" property="sex"></result>
</resultMap>
<select id="findUserByIdResultMap" parameterType="int" resultMap="userResultMap">
select id id_,username username_,sex sex_ from user where id=#{id}
</select>
4.接口与测试
接口:src/main/java/org/example/mapper/UserMapper.java
public User findUserByIdResultMap(int id);
测试: src/test/java/UserMapperTest.java
@Test
public void findUserByIdResultMapTest() throws IOException {
String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
User user = sqlSession.getMapper(UserMapper.class).findUserByIdResultMap(1);
System.out.println(user);
}
五、多表关联
1.一对一关联查询
需求:查询订单信息,关联查询创建订单的用户信息
主表:订单表
关联表:用户表
public class Orders {
private int id;
private int user_id;
private String number;
private Date create_time;
private String note;
private User user;
}
public class OrdersCustom extends Orders{
private String username;
private String sex;
private String address;
}
1.resultType
输出映射,可把结果集封装到某个对象/类上
映射文件:src/main/java/org/example/mapper/UserMapper.xml
<!--查询订单信息,关联查询创建订单的用户信息-->
<select id="findOrdersUser" resultType="org.example.pojo.OrdersCustom">
select orders.*,user.username,user.sex,user.birthday,user.address
from orders,user
where orders.user_id = user.id
</select>
接口:src/main/java/org/example/mapper/UserMapper.java
List<OrdersCustom> findOrdersUser();
测试:src/test/java/UserMapperTest.java
@Test
public void findOrdersUserTest() throws IOException {
String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
List<OrdersCustom> list = sqlSession.getMapper(UserMapper.class).findOrdersUser();
System.out.println(list);
}
2.resultMap
映射文件:src/main/java/org/example/mapper/UserMapper.xml
<!-- 查询订单信息,关联查询创建订单的用户信息,使用resultMap输出映射实现-->
<resultMap id="OrdersUserResultMap" type="org.example.pojo.Orders">
<!--先匹配订单信息-->
<id column="id" property="id"></id>
<result column="user_id" property="user_id"></result>
<result column="number" property="number"></result>
<result column="createtime" property="createtime"></result>
<result column="note" property="note"></result>
<!--后匹配用户信息-->
<association property="user" javaType="org.example.pojo.User">
<id column="user_id" property="id"></id>
<result column="username" property="username"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
</association>
</resultMap>
<select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
select orders.*,user.username,user.sex,user.birthday,user.address
from orders,user
where orders.user_id = user.id
</select>
接口:src/main/java/org/example/mapper/UserMapper.java
List<Orders> findOrdersUserResultMap();
测试:src/test/java/UserMapperTest.java
@Test
public void findOrdersUserResultMapTest() throws IOException {
String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
List<Orders> orders = sqlSession.getMapper(UserMapper.class).findOrdersUserResultMap();
System.out.println(orders);
}
2.一对多关联查询
需求:查询订单及订单明细信息
主表:订单表
关联表:订单明细表
resultMap
1.SQL语句
-- 查询订单及订单明细信息
select orders.*,user.username,user.sex,user.address,
orderdetail.id orderdetail_id,orderdetail.items_id,orderdetail.items_num
from orders,user,orderdetail
where orders.user_id=user.id
and orderdetail.orders_id=orders.id
2.映射类
public class Orderdetail {
private int id;
private int orders_id;
private int items_id;
private int items_num;
}
public class Orders {
private int id;
private int user_id;
private String number;
private Date createtime;
private String note;
private User user;
private List<Orderdetail> orderdetails;👈//添加get、set、toString方法
}
3.映射文件
<!--查询订单及订单明细信息-->
<resultMap id="OrdersUserAndOrderdetailResultMap" type="org.example.pojo.Orders" extends="OrdersUserResultMap">
<!--一对多匹配-->
<collection property="orderdetails" ofType="org.example.pojo.Orderdetail">
<id column="orderdetail_id" property="id"></id>
<result column="items_id" property="items_id"></result>
<result column="items_num" property="items_num"></result>
</collection>
</resultMap>
<select id="findOrdersUserAndOrderdetailResultMap" resultMap="OrdersUserAndOrderdetailResultMap">
select orders.*,user.username,user.sex,user.address,
orderdetail.id orderdetail_id,orderdetail.items_id,orderdetail.items_num
from orders,user,orderdetail
where orders.user_id=user.id
and orderdetail.orders_id=orders.id
</select>
4.接口
List<Orders> findOrdersUserAndOrderdetailResultMap();
5. 测试
@Test
public void findOrdersUserAndOrderdetailResultMapTest() throws IOException {
String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
List<Orders> orders = sqlSession.getMapper(UserMapper.class).findOrdersUserAndOrderdetailResultMap();
System.out.println(orders);
}