1. 动态SQL - XML
1.1 if
1.2 choose、when、otherwise
等价于 if{] else if{] else{} — 最后只能有一个条件成立
1.3 where、if、 或者 trim、if
prefix: 前缀符号必须加
suffix: 后缀符号必须加
prefixOverrides: 如果prefix后面的符号是 prefixOverrides,则会把该prefixOverrides符号删除
suffixOverrides: 如果suffix前面的符号是 suffixOverrides,则会把suffixOverrides符号删除
两者等价
1.4 set、if
两者等价
1.5 foreach - 遍历集合、数组 - 自动判断容器是否null,size>0
数组型 - 如果传参不是包装类,collection里面写 传参的类型
list容器型 - 如果传参不是包装类,collection里面写 传参的类型
<mapper namespace="top.linruchang.dao.UserDao">
<!-- 这里传的是包装类参数 -->
<select id="findByIds" resultType="user" parameterType="QueryParameter">
select * from ums_user
<where>
<if test="id != null">
and id = #{id}
</if>
<!-- test里面的变量名只能使用 属性字段名 -->
<if test="ids != null and ids.size>0">
<foreach collection="ids" item="id" open="and id in (" close=")" separator=",">
#{id}
</foreach>
</if>
</where>
</select>
</mapper>
1.6 sql、include 语句重用
<sql id="defaultUms_user">
select * from ums_user
</sql>
<select id="findByIds2" resultType="user" parameterType="java.util.List">
<include refid="defaultUms_user"></include>
<where>
<foreach collection="list" item="id" open="and id in (" close=")" separator=",">
#{id}
</foreach>
</where>
</select>
1.7 自增长
当插入新列时,MySQL的主键id设定自增长,将id返回并赋值给user.id
<insert id="add" parameterType="user" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
insert into user(name, loginName) values(#{name}, #{loginName})
</insert>
2. 映射关系
2.1 一对多 - 产品类型与产品的关系
2.1.1 数据准备
表结构
实体类
@Data
public class PmsProduct {
Integer id;
Integer type_id;
Integer brand_id;
String name;
String specifications;
double current_price;
double history_price;
String special_information;
String img_url;
}
@Data
public class PmsProductType {
String id;
String name;
List<PmsProduct> products;
}
2.1.2 单条语句 - 表连接
映射文件
<resultMap id="typeMap" type="top.linruchang.domain.PmsProductType">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<collection property="products" ofType="top.linruchang.domain.PmsProduct">
<id property="id" column="product_id" ></id>
<result property="type_id" column="id"></result>
<result property="brand_id" column="brand_id"></result>
<result property="name" column="product_name"></result>
<result property="specifications" column="specifications"></result>
<result property="current_price" column="current_price"></result>
<result property="history_price" column="history_price"></result>
<result property="special_information" column="special_information"></result>
<result property="img_url" column="img_url"></result>
</collection>
</resultMap>
<select id="findById" resultMap="typeMap" parameterType="int">
select
pms_product_type.* ,
pms_product.id product_id,
pms_product.brand_id brand_id,
pms_product.current_price current_price,
pms_product.history_price history_price,
pms_product.img_url img_url,
pms_product.name product_name,
pms_product.special_information special_information,
pms_product.specifications specifications
from pms_product RIGHT JOIN pms_product_type
on pms_product_type.id = pms_product.type_id
where pms_product_type.id = 8;
</select>
2.1.3 查询单表时,发送SQL语句查询另一个表 - 不进行表连接
typeMapper.xml
<resultMap id="typeMap2" type="top.linruchang.domain.PmsProductType">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<!--oftype定义元素类型,javaType定义products属性字段类型,column,发送sqll的值, select发送的sql语句 namespace+id-->
<collection property="products" column="id" ofType="PmsProduct" javaType="ArrayList" select="top.linruchang.dao.ProductDao.findByTypeId"></collection>
</resultMap>
<select id="findById2" resultMap="typeMap2" parameterType="int">
select * from pms_product_type where id = #{type_id};
</select>
productMapper.xml
<mapper namespace="top.linruchang.dao.ProductDao">
<select id="findByTypeId" resultType="PmsProduct" parameterType="int">
select * from pms_product where type_id = #{type_id};
</select>
</mapper>
typeDao.java
public interface TypeDao {
public PmsProductType findById2(Integer id);
}
AppTest.java
public class AppTest {
InputStream is;
SqlSessionFactory factory;
SqlSession sqlSession;
@Before
public void prepare() throws IOException {
// 加载myvatis全局配置文件
is = Resources.getResourceAsStream("mybatisConfig.xml");
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
// 根据配置文件进行初始化 数据库连接工厂
factory = builder.build(is);
}
@Test
public void mybatis() throws IOException {
// 获取数据库连接对象
sqlSession = factory.openSession();
// 利用反射进行封装dao层 - 该两条语句等于上面一条语句
TypeDao typeDaoImpl = sqlSession.getMapper(TypeDao.class);
PmsProductType type = typeDaoImpl.findById2(1);
System.out.println(type);
}
@After
public void end() throws IOException {
//关闭资源
sqlSession.close();
is.close();
}
}
2.2 一对一、多对一 → 订单与用户的关系
多对一 可看成 一对一的关系 → 因为一个订单对象只属于一个用户对象
2.2.1 数据准备
表结构
实体类
User.java
public class User {
Integer id;
Integer role_id;
String loginName;
String password;
String nickname;
String headImg;
}
OmsOrder.java
public class OmsOrder {
Integer id;
Integer user_id;
Integer transactionId;
short order_status;
short pay_status;
Integer total_money;
Date create_time;
User user;
}
OrderDao.java
public interface OrderDao {
public OmsOrder findByOrderId(Integer order_id);
public OmsOrder findByOrderId2(Integer order_id);
}
2.2.2 单条语句 - 表连接
OrderMapper.xml
<resultMap id="omsOrderMap2" type="omsOrder">
<id column="id" property="id"></id>
<result column="user_id" property="user_id"></result>
<result column="transactionId" property="transactionId"></result>
<result column="order_status" property="order_status"></result>
<result column="pay_status" property="pay_status"></result>
<result column="total_money" property="total_money"></result>
<result column="create_time" property="create_time"></result>
<association property="user" javaType="user">
<id property="id" column="user_id"></id>
<result property="role_id" column="role_id"></result>
<result property="loginName" column="loginName"></result>
<result property="password" column="password"></result>
<result property="nickname" column="nickname"></result>
<result property="headImg" column="headImg"></result>
</association>
</resultMap>
<select id="findByOrderId2" resultMap="omsOrderMap2">
select
oms_order_master.*,
ums_user.role_id role_id,
ums_user.loginName loginName,
ums_user.password password,
ums_user.nickname nickname,
ums_user.headImg headImg
from oms_order_master LEFT JOIN ums_user
on oms_order_master.user_id = ums_user.id
where oms_order_master.user_id = #{order_id} ;
</select>
2.1.3 查询单表时,发送SQL语句查询另一个表 - 不进行表连接
OrderMapper.xml
<resultMap id="omsOrderMap" type="omsOrder">
<id column="id" property="id"></id>
<result column="user_id" property="user_id"></result>
<result column="transactionId" property="transactionId"></result>
<result column="order_status" property="order_status"></result>
<result column="pay_status" property="pay_status"></result>
<result column="total_money" property="total_money"></result>
<result column="create_time" property="create_time"></result>
<association property="user" column="user_id" javaType="user" select="top.linruchang.dao.UserDao.findById">
</association>
</resultMap>
<select id="findByOrderId" resultMap="omsOrderMap">
select * from oms_order_master where id = #{order_id}
</select>
userMapper.xml
<mapper namespace="top.linruchang.dao.UserDao">
<select id="findById" resultType="user">
select * from ums_user where id = #{id}
</select>
</mapper>
2.3 多对多
利用中间表进行映射关系 - 减少冗余
3. MyBatis配置策略
3.1 懒加载
MyBatisConfig.xml - 懒加载配置
<settings>
<!-- 缓存启动 sqlSession级别 -->
<setting name="cacheEnabled" value="true"/>
<!-- 懒加载开关 启动 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 按需加载 - 关联的sql懒运行 - 懒加载 -->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
测试 - 当查询type时,会额外发送SQL语句查询Product数据关联
配置懒加载执行情况
未配置懒加载执行情况
3.2 缓存
3.2.1 一级缓存 - 相同对象
SqlSession级别缓存,默认自动开启:当调用SqlSession的 update、insert、delete、commit、close这些行为时都会刷新清空一级缓存
MyBatis全局配置 - 开启懒加载以及一级缓存
typeMapper映射文件
测试 - 一级缓存已经配置
测试 - 一级缓存没有配置
3.2.2 二级缓存 - 只存对象里面的属性值,而不是对象
SqlSessionFactory(连接池)级别的缓存:同一个“连接池”创建的SqlSession共享缓存
配置步骤