<?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
接口同名且在同一包下(编译后)。Mybatis
内置全限定类名别名。
通过对 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();
}
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);
}
}
首先在 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
首先在 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);
}
}
}
-------------------------闪电打下来时,你必须在场-------------------------
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。