一、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 configurationPUBLIC "-//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.addressfrom orders,userwhere 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.addressfrom orders,userwhere 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_numfrom orders,user,orderdetailwhere orders.user_id=user.idand 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);
}