一、Mybatis 配置文件 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 标签必须为 configuration 标签的第一个子标签-->
<properties resource="jdbc.properties"></properties>
<typeAliases>
<!--给指定单个全限定类名起别名-->
<!--<typeAlias type="com.idol.pojo.Product" alias="product"/>-->
<!--给指定包下的所有全限定类名起别名,别名在使用时不区分大小写-->
<package name="com.idol.pojo"/>
</typeAliases>
<!--配置 Mybatis 插件-->
<plugins>
<plugin interceptor="com.idol.plugin.MyPlugin">
<property name="name" value="Run MyPlugin~~~~~~~~~~~"/>
</plugin>
</plugins>
<!-- 配置系统环境 default 属性对应 environment 标签的 id 属性的值 -->
<environments default="development">
<!-- 可配置多个 environment 标签 -->
<environment id="development">
<!-- type属性的值:JDBC 表示数据库事务交由 JDBC 管理 -->
<transactionManager type="JDBC"></transactionManager>
<!--type 属性的值 POOLED 表示数据库连接资源采用数据库连接池管理-->
<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 配置文件-->
<!--<mapper resource="ProductMapper.xml"></mapper>-->
<!--扫描 dao 包下的所有配置文件-->
<package name="com.idol.dao"/>
</mappers>
</configuration>
要点:
- 如果数据库配置是通过
properties
标签文件进行加载,则该标签必须为configuration
下出现的第一个子标签。 - 为了减少
xxxMapper.xml
文件中经常配置POJO
类全限定类名的繁琐,可用typeAliases
标签进行全限定类名的别名设置。typeAlias
设置单个类,package
对指定包下的所有类进行设置。 - 配置
Mybatis
插件时,plugins
标签必须在typeAlias
标签后environments
标签前出现。 - 在通过
mappers
标签引入mapper
配置文件时,如果使用包扫描的方式,则必须保证mapper
文件与dao
接口同名且在同一包下(编译后)。
5. Mybatis
内置全限定类名别名。
二、动态 SQL 用法
通过对 product
表进行 CRUD
操作,来梳理 Mybatis
动态标签 <if>、<where>、<foreach>、<trim>、<set>、<choose>、<when>、<otherwise>
的用法。
ProductMapper.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.idol.dao.ProductMapper">
<!--自定义数据库字段与实体类属性对应关系-->
<resultMap id="ExampleMap" type="product">
<id column="T_id" property="id" javaType="int" jdbcType="INTEGER"></id>
<result column="T_name" property="name" javaType="string" jdbcType="VARCHAR"></result>
<result column="T_price" property="price" javaType="double" jdbcType="FLOAT"></result>
<result column="T_type" property="type" javaType="string" jdbcType="VARCHAR"></result>
</resultMap>
<!--定义模板 SQL-->
<sql id="BaseSql">
select id, name, price, type from products
</sql>
<!--查询所有商品。自定义 resultMap 用法-->
<select id="findAll" resultMap="ExampleMap">
select id as T_id, name as T_name, price as T_price, type as T_type from products
</select>
<!--根据条件查询单个商品。自定义 SQL 模板用法;where 与 if 标签的搭配使用-->
<select id="findOne" parameterType="product" resultType="product">
<include refid="BaseSql"></include>
<!--where 可以去除其后第一个 and 字符串-->
<where>
<if test="id != null">
and id=#{id}
</if>
<if test="name != null">
and name=#{name}
</if>
</where>
</select>
<!--查询指定 id 数组中的产品信息。 foreach 标签用法-->
<select id="findByIdArr" parameterType="list" resultType="product">
<include refid="BaseSql"></include>
<where>
<!--判断参数长度是否为空。集合用 .size(),数组用 .length-->
<if test="array.length < 1">
1=2
</if>
<!--collection 属性的值有 list, array, Map 元素的 key-->
<foreach collection="array" item="id" open="id in (" close=")" separator=",">
#{id}
</foreach>
</where>
</select>
<!--添加商品。trim 标签用法-->
<insert id="add" parameterType="product">
insert into products
<trim prefix="values(" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id},
</if>
<if test="name != null">
#{name},
</if>
<if test="price != null">
#{price},
</if>
<if test="type != null">
#{type},
</if>
</trim>
</insert>
<!--更新商品。 set、choose、when、otherwise 标签用法-->
<update id="modify" parameterType="product">
update products
<set>
<if test="name != null">
name=#{name},
</if>
<if test="price != null">
price=#{price},
</if>
<if test="type != null">
type=#{type},
</if>
</set>
<where>
<choose>
<when test="id != null">
id=#{id}
</when>
<otherwise>
1=2
</otherwise>
</choose>
</where>
</update>
</mapper>
三、配置文件:一对一查询
OrderMapper.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.idol.dao.OrderMapper">
<!-- 一对一查询 -->
<resultMap id="BasePojo" type="order">
<id column="o_id" property="id" javaType="int" jdbcType="INTEGER"></id>
<result column="ordertime" property="ordertime" javaType="string" jdbcType="VARCHAR"></result>
<result column="total" property="total" javaType="double" jdbcType="DOUBLE"></result>
<association property="customer" javaType="customer">
<id column="u_id" property="id" javaType="int" jdbcType="INTEGER"></id>
<result column="username" property="username" javaType="string" jdbcType="VARCHAR"></result>
<result column="password" property="password" javaType="string" jdbcType="VARCHAR"></result>
<result column="birthday" property="birthday" javaType="string" jdbcType="VARCHAR"></result>
</association>
</resultMap>
<select id="findOne" parameterType="int" resultMap="BasePojo">
SELECT
o.id AS o_id,
o.ordertime,
o.total,
c.id AS u_id,
c.username,
c.`password`,
c.birthday
FROM
orders AS o
INNER JOIN customer AS c ON o.uid = c.id
AND o.id = #{id}
</select>
</mapper>
四、配置文件:一对多查询
CustomerMapper.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.idol.dao.CustomerMapper">
<!-- 一对多查询 -->
<resultMap id="BasePojo" type="customer">
<id column="u_id" property="id" javaType="int" jdbcType="INTEGER"></id>
<result column="username" property="username" javaType="string" jdbcType="VARCHAR"></result>
<result column="password" property="password" javaType="string" jdbcType="VARCHAR"></result>
<result column="birthday" property="birthday" javaType="string" jdbcType="VARCHAR"></result>
<collection property="orders" ofType="order">
<id column="o_id" property="id" javaType="int" jdbcType="INTEGER"></id>
<result column="ordertime" property="ordertime" javaType="string" jdbcType="VARCHAR"></result>
<result column="total" property="total" javaType="double" jdbcType="DOUBLE"></result>
</collection>
</resultMap>
<select id="findOne" parameterType="int" resultMap="BasePojo">
SELECT
c.id AS u_id,
c.username,
c.`password`,
c.birthday,
o.id AS o_id,
o.ordertime,
o.total
FROM
customer AS c
INNER JOIN orders AS o ON o.uid = c.id
AND c.id=#{id}
</select>
</mapper>
五、配置文件:多对多查询
UserMapper.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.idol.dao.UserMapper">
<!-- 多对多查询 -->
<resultMap id="BasePojo" type="user">
<id column="u_id" property="id" javaType="int" jdbcType="INTEGER"></id>
<result column="name" property="name" javaType="string" jdbcType="VARCHAR"></result>
<collection property="roleList" ofType="role">
<id column="r_id" property="id" javaType="int" jdbcType="INTEGER"></id>
<result column="rolename" property="rolename" javaType="string" jdbcType="VARCHAR"></result>
<result column="roleDesc" property="roleDesc" javaType="string" jdbcType="VARCHAR"></result>
</collection>
</resultMap>
<select id="findUserAndRole" parameterType="int" resultMap="BasePojo">
SELECT
u.id AS u_id,
u.`name`,
r.id AS r_id,
r.rolename,
r.roleDesc
FROM
sys_user_role ur
INNER JOIN `user` u ON ur.userid = u.id
AND u.id = #{id}
LEFT JOIN sys_role r ON r.id = ur.roleid
</select>
</mapper>
个人理解:多对多是数据库层面表与表之前的逻辑关系的划分,在代码层面与一对多查询没有太大的差别。且可以将多对多理解为特殊的一对多关系。例如常见的多对多场景为:用户与角色。其就可看作是通过中间表维护的两个一对多的关系,即一个用户对应多个权限,一个权限也可对应多个用户。
六、注解:一对一查询
import com.idol.pojo.Order;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.util.List;
/**
* @author Supreme_Sir
* @version 1.0
* @className IOrderDao
* @description
* @date 2020/10/6 6:58
**/
public interface OrderMapper {
/**
* 注解方式 一对一
*/
@Results({
@Result(column = "id", property = "id"),
@Result(column = "ordertime", property = "ordertime"),
@Result(column = "total", property = "total"),
/*
column 属性的值为:传入下一次查询的条件
property 属性的值为:Customer 对象在 Order 对象中的属性名
@One 中的 select 属性的值为:子查询的 statementID,即子查询的全限定类名.方法名
*/
@Result(column = "uid", property = "customer", one = @One(select = "com.idol.annotation.dao.CustomerMapper.findCustomerByID"))
})
@Select("select id, ordertime, total, uid from orders where id=#{id}")
Order findOne(Integer id);
@Select("select id, ordertime, total, uid from orders where uid=#{uid}")
List<Order> selectOrderByUid(Integer uid);
}
七、注解:一对多查询
import com.idol.pojo.Customer;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.util.List;
/**
* @author Supreme_Sir
* @version 1.0
* @className CustomerMapper
* @description
* @date 2020/10/6 8:07
**/
public interface CustomerMapper {
/**
* 注解方式 一对多
*/
@Results({
@Result(column = "id", property = "id"),
@Result(column = "username", property = "username"),
@Result(column = "password", property = "password"),
@Result(column = "birthday", property = "birthday"),
/*
column 属性的值为:传入下一次查询的条件
property 属性的值为:Order 对象在 Customer 对象中的属性名
javaType 属性的值为:orders 属性的类型
@Many 中的 select 属性的值为:子查询的 statementID,即子查询的全限定类名.方法名
*/
@Result(column = "id", property = "orders", javaType = List.class, many = @Many(
select = "com.idol.annotation.dao.OrderMapper.selectOrderByUid"
)),
})
@Select("select id, username, password, birthday from customer where id=#{id}")
Customer findOne(Integer id);
@Select("select id, username, password, birthday from customer where id=#{id}")
Customer findCustomerByID(Integer id);
}
八、注解:多对多查询
import com.idol.pojo.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
/**
* @author Supreme_Sir
* @version 1.0
* @className UserMapper
* @description
* @date 2020/10/6 8:36
**/
public interface UserMapper {
/***
* 注解方式 多对多
*/
@Select("select id, name from user where id=#{userID}")
@Results({
@Result(column = "id", property = "id"),
@Result(column = "name", property = "name"),
/*
column 属性的值为:传入下一次查询的条件
property 属性的值为:Role 对象在 User 对象中的属性名
javaType 属性的值为:roleList 属性的类型
@Many 中的 select 属性的值为:子查询的 statementID,即子查询的全限定类名.方法名
*/
@Result(column = "id", property = "roleList", javaType = List.class, many = @Many(
select = "com.idol.annotation.dao.RoleMapper.findRoleByUid"
))
})
User findUserAndRole(Integer userID);
/**
* 注解方式增加
*/
@Insert("insert into user values(#{id}, #{name})")
void insertUser(User user);
/**
* 注解方式删除
*/
@Delete("delete from user where id=#{id}")
void deleteUser(Integer id);
/**
* 注解方式更新
*/
@Update("update user set name=#{name} where id=#{id}")
void updateUser(User user);
/**
* 注解方式查询
*/
@Select("select id, name from user")
List<User> selectAllUser();
}
九、自定义 Mybatis 插件
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.plugin.*;
import java.sql.Connection;
import java.util.Properties;
/**
* @author Supreme_Sir
* @version 1.0
* @className MyPlugin
* @description
* @date 2020/10/6 16:51
**/
@Intercepts({
/*
type:要拦截的处理器
method:被拦截处理器的方法名
args:被拦截方法的参数类型
*/
@Signature(type = StatementHandler.class,
method = "prepare",
args = {Connection.class, Integer.class})
})
public class MyPlugin implements Interceptor {
@Override
// 拦截方法:只要被拦截的目标对象的目标方法被执行,该方法就会执行
public Object intercept(Invocation invocation) throws Throwable {
System.out.println("拦截到了目标方法,并对原方法进行了增强。");
// 让原方法执行
return invocation.proceed();
}
@Override
// 为当前拦截器生成代理,存入拦截器链中
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
// 获取 sqlMapConfig.xml 配置文件中的属性
public void setProperties(Properties properties) {
System.out.println("获取到的配置文件的参数是:" + properties);
}
}
十、PageHelper 分页插件用法
首先在 pom
文件中新增 PageHelper
工具类坐标。
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.8</version>
</dependency>
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>1.2</version>
</dependency>
然后在 sqlMapConfig.xml
中添加 PageHelper
插件。
<plugins>
<plugin interceptor="com.idol.plugin.MyPlugin">
<property name="name" value="Run MyPlugin~~~~~~~~~~~"/>
</plugin>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!-- 3.* 版本需指定数据库方言 -->
<!-- 设置数据库类型 Oracle,Mysql,MariaDB,SQLite,Hsqldb,PostgreSQL六种数据库 -->
<!-- 由于本项目中使用的是 5.1.8 版本的 pagehelper 所以无需进行数据库方言设置 -->
<!-- <property name="dialect" value="mysql"/> -->
</plugin>
</plugins>
注意:
PageHelper
5.* 版本的拦截器为com.github.pagehelper.PageInterceptor
,3.* 版本的拦截器为com.github.pagehelper.PageHelper
。PageHelper
5.* 版本无需配置数据库方言,3.* 版本需配置数据库方言。
最后 PageHelper
的使用示例:
@Test
public void pagehelperTest() {
PageHelper.startPage(1, 2);
PageInfo<Product> pageInfo = new PageInfo<Product>(productDao.findAll());
List<Product> products = pageInfo.getList();
for (Product product : products) {
System.out.println(product);
}
System.out.println("总条数:"+pageInfo.getTotal());
System.out.println("总页数:"+pageInfo. getPages ());
System.out.println("当前页:"+pageInfo. getPageNum());
System.out.println("每页显万长度:"+pageInfo.getPageSize());
System.out.println("是否第一页:"+pageInfo.isIsFirstPage());
System.out.println("是否最后一页:"+pageInfo.isIsLastPage());
}
输出结果:
Product{id=1, name='键盘', price=30.0, type='电脑耗材'}
Product{id=2, name='眼镜', price=400.0, type='生活用品'}
总条数:11
总页数:6
当前页:1
每页显万长度:2
是否第一页:true
是否最后一页:false
十一、通用 Mapper 用法
首先在 pom
文件中新增 mapper
工具类坐标。
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper</artifactId>
<version>3.1.2</version>
</dependency>
然后配置通用 Mapper
插件
<plugin interceptor="tk.mybatis.mapper.mapperhelper.MapperInterceptor">
<!-- 通用Mapper接口,多个通用接口用逗号隔开 -->
<property name="mappers" value="tk.mybatis.mapper.common.Mapper"/>
</plugin>
接着创建 POJO
和 DAO
接口对象
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
/**
* @author Supreme_Sir
* @version 1.0
* @className Product
* @description
* @date 2020/10/6 21:49
**/
@Table(name = "products")
public class Product {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String name;
private Double price;
private String type;
// Getter and Setter
}
import com.idol.mapper.pojo.Product;
import tk.mybatis.mapper.common.Mapper;
/**
* @author Supreme_Sir
* @version 1.0
* @className ProductDao
* @description
* @date 2020/10/6 21:52
**/
public interface ProductDao extends Mapper<Product> {
}
最后,通用 Mapper
的用法示例
import com.idol.mapper.dao.ProductDao;
import com.idol.mapper.pojo.Product;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import tk.mybatis.mapper.entity.Example;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* @author Supreme_Sir
* @version 1.0
* @className MapperTest
* @description
* @date 2020/10/6 22:45
**/
public class MapperTest {
private ProductDao mapper;
@Before
public void ready() throws IOException {
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
mapper = sqlSession.getMapper(ProductDao.class);
}
@Test
// 通用 Mapper 用法示例: 查询所有
public void mapperSelectAllTest() {
List<Product> products = mapper.select(null);
for (Product product : products) {
System.out.println(product);
}
}
@Test
// 通用 Mapper 用法示例: 根据ID查询
public void mapperSelectOneTest() {
Product product = mapper.selectOne(new Product(1, null, null, null));
System.out.println(product);
}
@Test
// 通用 Mapper 用法示例: 插入记录
public void mapperInsertTest() {
mapper.insert(new Product(12, "裤子", 100d, "生活用品"));
}
@Test
// 通用 Mapper 用法示例: 删除记录
public void mapperDeleteTest() {
mapper.delete(new Product(12, "裤子", 100d, "生活用品"));
}
@Test
// 通用 Mapper 用法示例:更新记录
public void mapperUpdateTest() {
mapper.updateByPrimaryKey(new Product(11, "裤子", 100d, "生活用品"));
}
@Test
// 通用 Mapper用法示例:条件查询
public void mapperExampleTest() {
Example example = new Example(Product.class);
example.createCriteria().andEqualTo("type", "生活用品");
List<Product> products = mapper.selectByExample(example);
for (Product product : products) {
System.out.println(product);
}
}
}
源码
-------------------------闪电打下来时,你必须在场-------------------------