引言
在国内大部分Java项目的持久层使用的还是Mybatis,是国内的主流持久层技术框架。与Hibernate相比,它是一个半自动化的框架,容易上手,学习成本低,最重要的是方便开发人员实现定制化的sql。
笔者即将要学习的SpringCloud开源开源项目的持久层使用的ORM框架也是Mybatis,所以有必要整几篇有关Mybatis的文章帮助小伙伴们在SpringBoot项目的基础上快速整合Mybatis持久层框架。
1 Mybatis 简介
Java POJO ( Plain Old Java Object
,普通的 Java 对象)映射成数据库中的记录2 Mybatis配置
MyBatis
是一 个基于SqlSessionFactory
构建的框架 。对于SqlSessionFactory
,它的作用 是生
SqlSession
接口对象,这个接口对象是 MyBatis
操作的核心,而在 MyBatis Spring
的结合中甚至可
以“擦除”这个对象,使其在代码中“消失”,这样做的意义是重大的,因为 SqSession
是个功能性的代码,“擦除”它之后,就剩下了业务代码,这样就可以使得代码更具可读性 因为 SqlSessionFactory
的作用是单一 的,只是为了创建核心接口 SqI Session ,所以在 MyBatis 应用的生命 周期中理当只存 SqlSessionFactory 对象,并且往往会使用单例模式 而构建 SqlSessionFactory
是通过配置类(Configuration
)来完成的,因此对于 mybatis-spring-boot-starter
,它会给予我们在配置 文件( application.properties)进行 Configuration
配置的相关内容 下面先来看看 Configuration
可以 配置哪些内容,如下图所示:
http://www.mybatis.org/mybatis-3/zh/ configuration.html#settings
在没有与Spring集成时,需要在项目中引入mybatis的jar包
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>x.x.x</version>
</dependency>
2.1 使用XML配置获取
SqlSessionFactory
实例
String resource = "org/mybatis/example/mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
mybatis-config.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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="org/mybatis/example/BlogMapper.xml"/>
</mappers>
</configuration>
以上数据源属性值使用了占位符,注意configuration标签上的文档声明为mybatis配置文件的统一验证文档格式
2.2 使用Java代码获取
SqlSessionFactory
实例
//获取数据源
DataSource dataSource = new DruidDataSource();
dataSource.configFromPropety(properties);
//构建事务工厂
TransactionFactory transactionFactory =
new JdbcTransactionFactory();
//构建Environmen
Environment environment =
new Environment("development", transactionFactory, dataSource);
//构建配置类Configuration实例
Configuration configuration = new Configuration(environment);
//添加映射类
configuration.addMapper(BlogMapper.class);
//获取SqlSessionFactory实例
SqlSessionFactory sqlSessionFactory =
new SqlSessionFactoryBuilder().build(configuration);
2.3 从SqlSessionFactory中获取SqlSession
SqlSession session = sqlSessionFactory.openSession();
try {
Blog blog = session.selectOne(
"org.mybatis.example.BlogMapper.selectBlog", 101);
} finally {
//每次操作完必须关闭数据库会话
session.close();
}
Mapper文件内容如下:
<?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="org.mybatis.example.BlogMapper">
<select id="selectBlog" resultType="Blog">
select * from Blog where id = #{id}
</select>
</mapper>
Mybatis3使用命名空间namespace来区分不同的dao接口,注意mapper标签上的文档声明为mybatis3映射文件的统一文档验证格式
SqlSession#selectOne是通过SqlSession直接操作Dao接口对应的数据库查询方法,第一个参数必须是带全类名的dao方法,后面的参数为dao方法参数
也可间接通过Mapper类操作,示例如下:
//先通过SqlSession拿到映射类BlogMapper(这个类是通过JDK动态代理增强接口后的类),
//然后再通过BlogMapper调用对应的查询方法
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog = mapper.selectBlog(101);
映射文件中查询也可以通过再Mapper接口上对应的方法上添加注解的方式替换,只是这种方式无法满足复杂的sql查询
package org.mybatis.example;
public interface BlogMapper {
@Select("SELECT * FROM blog WHERE id = #{id}")
Blog selectBlog(int id);
}
2.4 Mybatis映射文件中常用的标签
<insert />:
插入标签,代表一条insert sql语句
<update />
: 更新标签,代表一条update sql语句
<select /> :
查询标签,代表一条查询sql语句
<delete />
: 删除标签,代表一条删除sql语句
<sql />
: 能被重复引用的sql片段,id为唯一表示符号
<include />
: 通过refid="id"引用sql片段
<foreach />
: 用于遍历List场景下的动态sql
<where />
: 转成sql后能够去除多余and
关键字的where
语句
<trim />
: 转成sql后能去除多余的前缀或后缀,如去除动态update sql语句中多余的逗号分隔符
<if test="condition" />
: 单分支条件判断
<choose />
: 用于多分支条件判断,<choose />
标签里面还可以嵌套<choose />
,一般结合<when />和<otherwise />
两个标签在动态sql中一起使用
其用法如下:
<choose>
<when test="condition1">分支sql语句1</when>
<when test="condition2">分支sql语句2</when>
<otherwise>分支sql语句3</otherwise>
</choose>
以上insert、update、select 和 update
标签中都必须有id属性,其值与dao接口中的方法名保持一致;
parameterType
代表入参类型,resultType 和 resultMap
代表出参类型。好了,Mybatis映射文件中的标签就介绍这么多,具体用法请看下面的项目实战类型
3 Mybatis与Spring的整合
MyBatis
社区为了整合 Spring 自己开发了相应的开发包,因此 Spring Boot
中,我们可以依赖 MyBatis 社区提供的 starter 例如, Maven 加入依赖的包,如代码如下所示:
<dependency>
<groupId>org.mybatis.spring.boot<groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
从包名可以看到, mybatis-spring-boot-starter 是由 MyBatis 社区开发的,但是无论如何都要先了解MyBatis 的配置和基础的内容
3.1 Mybatis在SpringBoot项目中的常用配置
#定义 Mapper XML 路径
mybatis.mapper-locations=classpath:/mapper/*Mapper.xml
#定义别名扫描的包,需要在扫描包对应的实体类上添加@Alias
mybatis.type-aliases-package=com.mybatis.demo.pojo
#mybatis配置文件位置,当项目的mybatis配置比较复杂时可以使用单独的配置文件
mybatis.config-location=calsspath:mybatis-config.xml
#mybatis拦截器插件,多个拦截器使用逗号分隔
mybatis.configuration.interceptors=com.mybatis.demo.ExampleInterceptor
#对大字段使用贪婪加载
mybatis.configuration.aggressive-lazy-loading=true
#对大字段使用懒加载;aggressive-lazy-loading与lazy-loading-enabled只需要配置一个即可
mybatis.configuration.lazy-loading-enabled=true
#数据库表字段下划线映射到实体类时自动转驼峰命名
mybatis.configuration.map-underscore-to-camel-case=true
3.2 使用IDEA创建SpringBoot项目
项目搭建条件
项目maven依赖
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.0.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.mybatis</groupId>
<artifactId>mybatis-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<!-- spring-web-mvc模块依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- mybatis整合spring-boot模块依赖-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!---阿里德鲁伊数据源依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.5</version>
</dependency>
<!--mysql连接驱动依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--spring-web-test模块依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<!--maven编译和打包spring-boot项目插件-->
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
项目结构
启动类
package com.mybatis.demo;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
//使用MapperScan注解指定要扫描的数据库访问接口包
@MapperScan("com.mybatis.demo.dao")
public class MybatisApplication {
public static void main(String[] args) {
SpringApplication.run(MybatisApplication.class, args);
}
}
应用配置文件
application.properties
#服务器端口号
server.port=8088
#servlet上下文
server.servlet.context-path=/mybatis
#激活环境
spring.profiles.active=dev
#mybatis映射文件位置
mybatis.mapper-locations=classpath:/mapper/*Dao.xml
#mybatis实体类扫描包
mybatis.type-aliases-package=com.mybatis.demo.pojo
application-dev.properties
#数据源配置
spring.datasource.name=test
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=UTF8
spring.datasource.username=robbot
spring.datasource.password=robbot1234
spring.datasource.filters=stat
spring.datasource.maxActivce=20
spring.datasource.initialSize=1
spring.datasource.maxWait=60000
spring.datasource.minIdle=1
spring.datasource.timeBetweenEvictionRunsMillis=60000
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=select 1 from dual
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true
spring.datasource.maxOpenPreparedStatements=20
#配置日志根级别
logging.level.root=INFO
#配置spring的日志级别
logging.level.org.springframework=WARN
#配置mybatis的日志级别
logging.level.org.mybatis=INFO
3.3 数据准备
使用navicat连接Mysql数据库后新建一个product_info
,建表脚本如下:
use test;
create table product_info(
prod_code varchar(20) comment '产品代码',
prod_name varchar(50) not null comment '产品名称',
prod_big_type varchar(20) not null comment '产品大类',
prod_small_type varchar(20) not null comment '产品小类',
created_by varchar(50) default 'system' comment '创建人',
created_time TIMESTAMP default now() comment '创建时间',
last_updated_by varchar(50) default 'system' comment '最后修改人',
last_updated_time TIMESTAMP default now() comment '最后修改时间',
PRIMARY key(prod_code)
)engine=InnoDB default CHARSET=utf8;
完成建表后执行下面的sql脚本插入若干条数据:
insert into product_info(prod_code,prod_name,prod_big_type,prod_small_type,created_by,last_updated_by)
values('GM1001','鹏华基金公募产品1号','公募','股票型','x_heshengfu','x_heshengfu');
insert into product_info(prod_code,prod_name,prod_big_type,prod_small_type,created_by,last_updated_by)
values('GM1002','鹏华基金公募产品2号','公募','股票型','x_heshengfu','x_heshengfu');
insert into product_info(prod_code,prod_name,prod_big_type,prod_small_type,created_by,last_updated_by)
values('GM1003','鹏华基金公募产品3号','公募','混合型','x_heshengfu','x_heshengfu');
insert into product_info(prod_code,prod_name,prod_big_type,prod_small_type,created_by,last_updated_by)
values('GM1004','鹏华基金公募产品3号','公募','混合型','x_heshengfu','x_heshengfu');
insert into product_info(prod_code,prod_name,prod_big_type,prod_small_type,created_by,last_updated_by)
values('ZH1001','鹏华基金专户产品1号','专户','专户一对一','x_heshengfu','x_heshengfu');
insert into product_info(prod_code,prod_name,prod_big_type,prod_small_type,created_by,last_updated_by)
values('ZH1002','鹏华基金专户产品2号','专户','专户一对一','x_heshengfu','x_heshengfu');
insert into product_info(prod_code,prod_name,prod_big_type,prod_small_type,created_by,last_updated_by)
values('ZH1003','鹏华基金专户产品3号','专户','专户一对多','x_heshengfu','x_heshengfu');
insert into product_info(prod_code,prod_name,prod_big_type,prod_small_type,created_by,last_updated_by)
values('ZH1004','鹏华基金专户产品4号','专户','专户一对多','x_heshengfu','x_heshengfu');
insert into product_info(prod_code,prod_name,prod_big_type,prod_small_type,created_by,last_updated_by)
values('SB1001','鹏华基金社保产品1号','社保','社保','x_heshengfu','x_heshengfu');
insert into product_info(prod_code,prod_name,prod_big_type,prod_small_type,created_by,last_updated_by)
values('SB1002','鹏华基金社保产品2号','社保','社保','x_heshengfu','x_heshengfu');
insert into product_info(prod_code,prod_name,prod_big_type,prod_small_type,created_by,last_updated_by)
values('YL1001','鹏华基金养老产品1号','养老','养老','x_heshengfu','x_heshengfu');
insert into product_info(prod_code,prod_name,prod_big_type,prod_small_type,created_by,last_updated_by)
values('YL1002','鹏华基金养老产品2号','养老','养老','x_heshengfu','x_heshengfu');
commit;
3.4 dao层编码
ProductInfoDao.java
package com.mybatis.demo.dao;
import com.mybatis.demo.pojo.ProductInfo;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public interface ProductInfoDao {
//添加单个产品
int addProduct(ProductInfo productInfo);
//批量添加产品
int batchAddProduct(List<ProductInfo> productInfoList);
//更新产品
int updateProduct(ProductInfo productInfo);
//通过产品码查询产品
ProductInfo queryByProdCode(String productCode);
//通过产品名称查询产品
ProductInfo queryByProdName(String prodName);
//通过产品类型查找产品
List<ProductInfo> queryByProdType(@Param("bigType")String bigType, @Param("smallType")String smallType);
//删除单个产品
int deleteProduct(String prodCode);
//批量删除产品
int batchDeleteProduct(List<String> prodCodes);
}
ProductInfo类详细信息如下
package com.mybatis.demo.pojo;
import org.apache.ibatis.type.Alias;
import java.io.Serializable;
@Alias("ProductInfo")
public class ProductInfo implements Serializable {
private String prodCode;
private String prodName;
private String prodBigType;
private String prodSmallType;
private String createdBy;
private String createdTime;
private String lastUpdatedBy;
private String lastUpdatedTime;
//......省略getter和setter方法
}
ProductInfoDao.xml
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.demo.dao.ProductInfoDao">
<!--使用resultMap作为查询语句的返回对象,column表示表的列名,property表示实体类的属性名-->
<resultMap id="productInfoMap" type="ProductInfo">
<result column="prod_code" property="prodCode"/>
<result column="prod_name" property="prodName"/>
<result column="prod_big_type" property="prodBigType"/>
<result column="prod_small_type" property="prodSmallType"/>
<result column="created_by" property="createdBy"/>
<result column="created_time" property="createdTime"/>
<result column="last_updated_by" property="lastUpdatedBy" />
<result column="last_updated_time" property="lastUpdatedTime"/>
</resultMap>
<!--插入单条记录sql-->
<insert id="addProduct" parameterType="ProductInfo">
insert into product_info(
prod_code, prod_name,
prod_big_type, prod_small_type,
created_by, last_updated_by)
values(#{prodCode},#{prodName},
#{prodBigType},#{prodSmallType},
#{createdBy},#{lastUpdatedBy})
</insert>
<!--批量插入多条记录-->
<insert id="batchAddProduct" parameterType="java.util.List">
insert into product_info(
prod_code, prod_name,
prod_big_type, prod_small_type,
created_by, last_updated_by)
<foreach collection="list" item="item" separator="union">
select #{item.prodCode},#{item.prodName},
#{item.prodBigType},#{item.prodSmallType},
#{item.createdBy},#{item.lastUpdatedBy}
from dual
</foreach>
</insert>
<!--动态更新一条记录 -->
<!--trim 标签中的prefix表示前缀,suffixOverrides表示可能重复的后缀-->
<update id="updateProduct" parameterType="ProductInfo">
update product_info
<trim prefix="set" suffixOverrides=",">
<if test="prodName!=null">
prod_name = #{prodName},
</if>
<if test="prodBigType!=null">
prod_big_type = #{prodBigType},
</if>
<if test="prodSmallType!=null">
prod_small_type = #{prodSmallType}
</if>
<if test="createdBy!=null">
created_by = #{createdBy},
</if>
<if test="lastUpdatedBy!=null">
last_updated_by = #{lastUpdatedBy},
</if>
last_updated_time = now()
</trim>
where prod_code = #{prodCode}
</update>
<!--可多次引用的sql片段-->
<sql id="selectProdInfo">
select prod_code,prod_name,
prod_big_type,prod_small_type,
created_by,created_time,last_updated_by,last_updated_time
from product_info
</sql>
<!--根据产品码查询单条记录-->
<select id="queryByProdCode" parameterType="java.lang.String" resultMap="productInfoMap">
<include refid="selectProdInfo" />
where prod_code = #{prodCode}
</select>
<!--根据产品名查询单条记录-->
<select id="queryByProdName" parameterType="java.lang.String" resultMap="productInfoMap">
<include refid="selectProdInfo" />
where prod_name=#{prodName}
</select>
<!--根据产品类型查询多条记录-->
<select id="queryByProdType" resultMap="productInfoMap">
<include refid="selectProdInfo" />
where 1=1
<if test="bigType!=null">AND prod_big_type= #{bigType}</if>
<if test="smallType!=null"> AND prod_small_type= #{smallType}</if>
</select>
<!--根据一个产品码删除一条记录-->
<delete id="deleteProduct" parameterType="java.lang.String">
delete from product_info
where prod_code = #{prodCode}
</delete>
<!--根据产品码集合删除多条记录-->
<delete id="batchDeleteProduct" parameterType="java.util.List">
delete from product_info
where prod_code in
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</delete>
</mapper>
3.5 service层编码 ProductInfoService.java
package com.mybatis.demo.service;
import com.mybatis.demo.pojo.ProductInfo;
import java.util.List;
public interface ProductInfoService {
int saveProduct(ProductInfo productInfo) throws Exception;
int batchSaveProduct(List<ProductInfo> productInfos) throws Exception;
int updateProduct(ProductInfo productInfo) throws Exception;
ProductInfo queryByProdCode(String productCode);
ProductInfo queryByProdName(String prodName);
List<ProductInfo> queryByProdType(String bigType, String smallType);
int deleteProduct(String prodCode) throws Exception;
int batchDeleteProduct(List<String> prodCodes) throws Exception;
}
ProductInfoServiceImpl.java
package com.mybatis.demo.service.impl;
import com.mybatis.demo.dao.ProductInfoDao;
import com.mybatis.demo.pojo.ProductInfo;
import com.mybatis.demo.service.ProductInfoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
public class ProductInfoServiceImpl implements ProductInfoService {
@Autowired
private ProductInfoDao productInfoDao;
@Override
@Transactional(rollbackFor = Exception.class)
public int saveProduct(ProductInfo productInfo) throws Exception {
return productInfoDao.addProduct(productInfo);
}
@Override
@Transactional(rollbackFor = Exception.class)
public int batchSaveProduct(List<ProductInfo> productInfos) throws Exception {
return productInfoDao.batchAddProduct(productInfos);
}
@Override
@Transactional(rollbackFor = Exception.class)
public int updateProduct(ProductInfo productInfo) throws Exception {
return productInfoDao.updateProduct(productInfo);
}
@Override
public ProductInfo queryByProdCode(String productCode) {
return productInfoDao.queryByProdCode(productCode);
}
@Override
public ProductInfo queryByProdName(String prodName) {
return productInfoDao.queryByProdName(prodName);
}
@Override
public List<ProductInfo> queryByProdType(String bigType, String smallType) {
return productInfoDao.queryByProdType(bigType,smallType);
}
@Override
@Transactional(rollbackFor = Exception.class)
public int deleteProduct(String prodCode) throws Exception {
return productInfoDao.deleteProduct(prodCode);
}
@Override
@Transactional(rollbackFor = Exception.class)
public int batchDeleteProduct(List<String> prodCodes) throws Exception {
return productInfoDao.batchDeleteProduct(prodCodes);
}
}
3.6 Controller层编码
ProductInfoController
package com.mybatis.demo.controller;
import com.mybatis.demo.pojo.CommonResult;
import com.mybatis.demo.pojo.ProductInfo;
import com.mybatis.demo.service.ProductInfoService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
@RequestMapping("/productInfo")
public class ProductInfoController {
private static final Logger logger = LoggerFactory.getLogger(ProductInfoController.class);
@Autowired
private ProductInfoService productInfoService;
/**
* 添加单个产品接口,@RequestBody注解表示入参为请求体参数
*/
@PostMapping("/v1/product")
public CommonResult<Integer> saveSingleProduct(@RequestBody ProductInfo productInfo){
CommonResult<Integer> commonResult = new CommonResult<>();
try {
int result = productInfoService.saveProduct(productInfo);
commonResult.setData(result);
} catch (Exception e) {
commonResult.setMessage("inner server error occured");
commonResult.setCode(500);
commonResult.setData(0);
logger.error("save product failed,caused by: "+e.getMessage());
}
return commonResult;
}
/**
*批量添加多个产品接口
*/
@PostMapping("/v1/products")
public CommonResult<Integer> batchSaveProducts(@RequestBody List<ProductInfo> productInfos){
CommonResult<Integer> commonResult = new CommonResult<>();
try {
int result = productInfoService.batchSaveProduct(productInfos);
commonResult.setData(result);
} catch (Exception e) {
commonResult.setData(0);
commonResult.setCode(500);
commonResult.setMessage("inner server error occured");
logger.error("batch save products failed,caused by: "+e.getMessage());
}
return commonResult;
}
/**
* 修改单个产品接口
*/
@PutMapping("/v1/product")
public CommonResult<Integer> updateProductInfo(@RequestBody ProductInfo productInfo){
CommonResult<Integer> commonResult = new CommonResult<>();
try {
int result = productInfoService.updateProduct(productInfo);
commonResult.setData(result);
} catch (Exception e) {
commonResult.setData(0);
commonResult.setCode(500);
commonResult.setMessage("inner server error occured");
logger.error("update product failed,caused by: "+e.getMessage());
}
return commonResult;
}
/**
*根据产品码查询单个产品接口
*/
@GetMapping("/v1/product")
public CommonResult<ProductInfo> queryProductByCode(@RequestParam("prodCode") String prodCode){
CommonResult<ProductInfo> commonResult = new CommonResult<>();
ProductInfo productInfo = productInfoService.queryByProdCode(prodCode);
commonResult.setData(productInfo);
return commonResult;
}
/**
*根据产品码名称查询单个产品接口
*/
@GetMapping("/v1/products")
public CommonResult<ProductInfo> queryProductsByName(@RequestParam("prodName") String prodName){
CommonResult<ProductInfo> commonResult = new CommonResult<>();
ProductInfo result = productInfoService.queryByProdName(prodName);
commonResult.setData(result);
return commonResult;
}
/**
*根据产品大类和小类查询多个产品接口
*@RequestParam注解中的required = false代表该参数非必须,可为空
*/
@GetMapping("/v1/products/prodType")
public CommonResult<List<ProductInfo>> queryProductsByProdType(@RequestParam("bigType")String bigType,@RequestParam(value = "smallType",required = false)String smallType){
if(bigType!=null && "".equals(bigType)){
bigType = null;
}
if(smallType!=null && "".equals(smallType)){
smallType = null;
}
logger.info("bigType={},smallType={}",bigType,smallType);
CommonResult<List<ProductInfo>> commonResult = new CommonResult<>();
List<ProductInfo> result = productInfoService.queryByProdType(bigType,smallType);
commonResult.setData(result);
return commonResult;
}
/**
* 根据单个产品代码删除单个产品
*/
@DeleteMapping("/v1/product")
public CommonResult<Integer> deleteProduct(@RequestParam("prodCode")String prodCode){
CommonResult<Integer> commonResult = new CommonResult<>();
try {
int result = productInfoService.deleteProduct(prodCode);
commonResult.setData(result);
} catch (Exception e) {
commonResult.setData(0);
commonResult.setCode(500);
commonResult.setMessage("inner server error occured");
logger.error("delete product failed,caused by: "+e.getMessage());
}
return commonResult;
}
/**
*根据产品代码集合批量删除产品
*/
@DeleteMapping("/v1/products")
public CommonResult<Integer> batchDeleteProduct(@RequestBody List<String> prodCodes){
CommonResult<Integer> commonResult = new CommonResult<>();
try {
int result = productInfoService.batchDeleteProduct(prodCodes);
commonResult.setData(result);
} catch (Exception e) {
commonResult.setData(0);
commonResult.setCode(500);
commonResult.setMessage("inner server error occured");
logger.error("batch delete products failed,caused by: "+e.getMessage());
}
return commonResult;
}
}
CommonResult
类详细信息如下:
package com.mybatis.demo.pojo;
import java.io.Serializable;
public class CommonResult<T> implements Serializable {
private String message = "ok";
private int code = 200;
private T data;
public String getMessage() {
return message;
}
public void setMessage(String message) {
this.message = message;
}
public int getCode() {
return code;
}
public void setCode(int code) {
this.code = code;
}
public T getData() {
return data;
}
public void setData(T data) {
this.data = data;
}
}
4 效果测试
启动项目成功后即可利用postman对接口进行测试
4.1 测试根据产品名查询单个产品接口
GET http://localhost:8088/mybatis/productInfo/v1/products?prodName=鹏华基金公募产品1号
//点击Send按钮后的响应信息
{
"message": "ok",
"code": 200,
"data": {
"prodCode": "GM1001",
"prodName": "鹏华基金公募产品1号",
"prodBigType": "公募",
"prodSmallType": "股票型",
"createdBy": "x_heshengfu",
"createdTime": "2020-10-02 00:36:17",
"lastUpdatedBy": "x_heshengfu",
"lastUpdatedTime": "2020-10-02 00:36:17"
}
}
4.2 测试根据产品类型查询多个产品接口
GET http://localhost:8088/mybatis/productInfo/v1/products/prodType?bigType=公募
//点击Send按钮后的响应信息
{
"message": "ok",
"code": 200,
"data": [
{
"prodCode": "GM1001",
"prodName": "鹏华基金公募产品1号",
"prodBigType": "公募",
"prodSmallType": "股票型",
"createdBy": "x_heshengfu",
"createdTime": "2020-10-02 00:36:17",
"lastUpdatedBy": "x_heshengfu",
"lastUpdatedTime": "2020-10-02 00:36:17"
},
{
"prodCode": "GM1002",
"prodName": "鹏华基金公募产品2号",
"prodBigType": "公募",
"prodSmallType": "股票型",
"createdBy": "x_heshengfu",
"createdTime": "2020-10-02 00:36:17",
"lastUpdatedBy": "x_heshengfu",
"lastUpdatedTime": "2020-10-02 00:36:17"
},
{
"prodCode": "GM1003",
"prodName": "鹏华基金公募产品3号",
"prodBigType": "公募",
"prodSmallType": "混合型",
"createdBy": "x_heshengfu",
"createdTime": "2020-10-02 00:36:17",
"lastUpdatedBy": "x_heshengfu",
"lastUpdatedTime": "2020-10-02 00:36:17"
},
{
"prodCode": "GM1004",
"prodName": "鹏华基金公募产品4号",
"prodBigType": "公募",
"prodSmallType": "混合型",
"createdBy": "x_heshengfu",
"createdTime": "2020-10-02 00:36:17",
"lastUpdatedBy": "x_heshengfu",
"lastUpdatedTime": "2020-10-02 00:36:17"
},
{
"prodCode": "GM1005",
"prodName": "鹏华基金5号",
"prodBigType": "公募",
"prodSmallType": "指数型",
"createdBy": "x_heshengfu",
"createdTime": "2020-10-08 19:34:05",
"lastUpdatedBy": "x_heshengfu",
"lastUpdatedTime": "2020-10-08 19:34:05"
},
{
"prodCode": "GM1006",
"prodName": "鹏华基金6号",
"prodBigType": "公募",
"prodSmallType": "指数型",
"createdBy": "x_heshengfu",
"createdTime": "2020-10-08 19:34:05",
"lastUpdatedBy": "x_heshengfu",
"lastUpdatedTime": "2020-10-08 19:34:05"
},
{
"prodCode": "GM1007",
"prodName": "鹏华基金7号",
"prodBigType": "公募",
"prodSmallType": "QDII型",
"createdBy": "x_heshengfu",
"createdTime": "2020-10-08 19:34:05",
"lastUpdatedBy": "x_heshengfu",
"lastUpdatedTime": "2020-10-08 19:34:05"
},
{
"prodCode": "GM1008",
"prodName": "鹏华基金8号",
"prodBigType": "公募",
"prodSmallType": "QDII型",
"createdBy": "x_heshengfu",
"createdTime": "2020-10-08 19:34:05",
"lastUpdatedBy": "x_heshengfu",
"lastUpdatedTime": "2020-10-08 19:34:05"
},
{
"prodCode": "GM1009",
"prodName": "鹏华基金9号",
"prodBigType": "公募",
"prodSmallType": "货币型",
"createdBy": "x_heshengfu",
"createdTime": "2020-10-08 19:34:05",
"lastUpdatedBy": "x_heshengfu",
"lastUpdatedTime": "2020-10-08 19:34:05"
},
{
"prodCode": "GM1010",
"prodName": "鹏华基金10号",
"prodBigType": "公募",
"prodSmallType": "货币型",
"createdBy": "x_heshengfu",
"createdTime": "2020-10-08 19:34:05",
"lastUpdatedBy": "x_heshengfu",
"lastUpdatedTime": "2020-10-08 19:34:05"
}
]
}
注:某些最后更新时间与创建时间不一致是由于笔者在允许之前调用过更新接口导致的
4.3 测试添加单个产品接口
对于post接口,入参Body选择raw类型,格式选择Json,然后在请求参数输入框中输入Json格式的参数,最后点击Send按钮发送请求
//请求响应信息,code值为200代表接口调用成功
{
"message": "ok",
"code": 200,
"data": 1
}
4.4 测试批量添加产品接口
POST http://localhost:8088/mybatis/productInfo/v1/products
//入参:
[{
"prodCode":"SB1003",
"prodName": "社保3号",
"prodBigType": "社保",
"prodSmallType": "社保",
"createdBy": "x_heshengfu",
"lastUpdatedBy": "x_heshengfu"
},
{
"prodCode":"SB1004",
"prodName": "社保4号",
"prodBigType": "社保",
"prodSmallType": "社保",
"createdBy": "x_heshengfu",
"lastUpdatedBy": "x_heshengfu"
},
{
"prodCode":"GM1011",
"prodName": "鹏华基金公募11号产品",
"prodBigType": "公募",
"prodSmallType": "债券型",
"createdBy": "x_heshengfu",
"lastUpdatedBy": "x_heshengfu"
},
{
"prodCode":"GM1012",
"prodName": "鹏华基金公募12号产品",
"prodBigType": "公募",
"prodSmallType": "债券型",
"createdBy": "x_heshengfu",
"lastUpdatedBy": "x_heshengfu"
}]
//点击Send按钮后的响应信息,data=4代表有4条记录插入成功
{
"message": "ok",
"code": 200,
"data": 4
}
4.5 测试更新产品接口
PUT http://localhost:8088/mybatis/productInfo/v1/product
//入参
{
"prodCode": "SB1002",
"prodName": "鹏华基金社保2号",
"lastUpdatedBy": "x_heshengfu"
}
//发送Send按钮后的响应信息
{
"message": "ok",
"code": 200,
"data": 1
}
4.6 测试删除单个产品接口
DELETE http://localhost:8088/mybatis/productInfo/v1/product?prodCode=SB1004
//发送Send按钮后的响应信息
{
"message": "ok",
"code": 200,
"data": 1
}
4.7 测试批量删除产品接口
DELETE http://localhost:8088/mybatis/productInfo/v1/products
//入参:
["SB1002","SB1003"]
//发送Send按钮后的响应信息
{
"message": "ok",
"code": 200,
"data": 2
}
其他CRUD接口可照葫芦画瓢通过postman测试,如果只未来测试效果也可以在src/test/java
包下直编码测试类,只是这样做的缺点是每次运行一个测试方法只能单独测试那一个方法,笔者不是很习惯使用。
5 拓展内容
5.1 MyBatis 编程步骤
SqlSessionFactoryBuilder#build
方法创建 SqlSessionFactory
对象SqlSessionFactory
获取 SqlSession
对象SqlSession
获得 Mapper
代理对象不过在SpringBoot整合Mybatis项目中,我们只需要在application.properties
或application.yaml
等应用配置文件中提供数据源和mybatis的配置即可;
mybatis-spring-boot-starter
中的自动配置类在项目启动过程中检测到数据源的配置后会自动装配一个SqlSessionFactory
bean到Spring IOC容器;我们只需要提供数据库访问的Dao接口和对应的映射xml文件即可完成数据库访问操作
5.2
#{}
和${}
的区别
这个问题是有关Mybatis面试题中的高频考点,具体解答如下:
{} 是 Properties 文件中的变量占位符,它可以用于 XML 标签属性值和 SQL 内部,属于字符串替换。例如将 {driver} 会被静态替换为 com.mysql.jdbc.Driver :
<dataSource type="UNPOOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
</dataSource>
${}
也可以对传递进来的参数原样拼接在 SQL 中。代码如下:
<select id="getSubject3" parameterType="Integer" resultType="Subject">
SELECT * FROM subject
WHERE id = ${id}
</select>
实际场景下,不推荐这么做。因为,可能有 SQL 注入的风险
#{}
是 SQL 的参数占位符,Mybatis 会将 SQL 中的 #{}
替换为 ?
号,在 SQL 执行前会使用 PreparedStatement 的参数设置方法,按序给 SQL 的 ?
号占位符设置参数值,比如 ps.setInt(0, parameterValue)
。所以,#{}
是预编译处理,可以有效防止 SQL 注入,提高系统安全性。
另外,#{}
比${}
的取值方式更方便。例如:#{item.name}
的取值方式,为使用反射从参数对象中获取 item
对象的 name
属性值,相当于 param.getItem().getName()
5.3 MyBatis 与 Hibernate 的区别
总之,按照用户的需求在有限的资源环境下只要能做出维护性、扩展性良好的软件架构都是好架构,所以框架只有适合才是最好。简单总结如下:
实际上,MyBatis 也可以搭配自动生成代码的工具,提升开发效率,还可以使用 MyBatis-Plus 框架,已经内置常用的 SQL 操作,也是非常不错的。在接下来的文章中笔者有时间也会撰文介绍MyBatis-Plus
在项目中的使用 。
推荐阅读
[1] SpringBoot之路(一):构建你的第一个Restful Web Service
[2] SpringBoot之路(二)使用用Spring-Data-JPA访问数据库进行基本的CRUD操作
[3 ] SpringBoot之路(四)Spring-Data-Jpa中的高级应用
[5] 学习ElasticSearch的Restful Api快速掌握ES数据的增删改查
[6] SpringBoot项目整合ElasticSearch实战进阶(一)
参考阅读
[1] 杨开振著《深入浅出SpringBoot 2.x》第5章访问数据库之整合Mybatis
[2] 浅析Mybatis与Hibernate的区别与用途
(https://www.jianshu.com/p/96171e647885)
---END---