原本地址:Spring Boot乾貨系列:(十一)資料儲存篇-Spring Boot整合Mybatis通用Mapper外掛
部落格地址:tengj.top/
前言
上次介紹了Spring Boot中Mybatis的簡單整合,本篇深入來結合通用Mapper、Mybatis Geneator以及分頁PageHelper來打造適合企業開發的模板框架。
正文
專案框架還是跟上一篇一樣使用Spring Boot的ace後端模板,不過最近在使用vue,所以前端引用了vue進來改寫,程式碼變得更加簡潔。
專案配置:
Spring Boot: 1.5.9.RELEASE
Maven: 3.5 Java: 1.8 Thymeleaf: 3.0.7.RELEASE Vue.js: v2.5.11
資料來源依賴
這裡我們還是使用阿里巴巴的druid來當資料庫連線池,發現這個有對應的監控介面,我們可以開啟。 druid官方文件:https://github.com/alibaba/druid/wiki/常見問題
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.19</version>
</dependency>
複製程式碼
對應的application.properties配置:
## 資料庫訪問配置
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name = com.mysql.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost:3306/spring?useUnicode=true&characterEncoding=utf-8
spring.datasource.username = root
spring.datasource.password = root
# 下面為連線池的補充設定,應用到上面所有資料來源中
# 初始化大小,最小,最大
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
# 配置獲取連線等待超時的時間
spring.datasource.maxWait=60000
# 配置間隔多久才進行一次檢測,檢測需要關閉的空閒連線,單位是毫秒
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
# 開啟PSCache,並且指定每個連線上PSCache的大小
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
# 配置監控統計攔截的filters,去掉後監控介面sql無法統計,'wall'用於防火牆
spring.datasource.filters=stat,wall,log4j
# 合併多個DruidDataSource的監控資料
#spring.datasource.useGlobalDataSourceStat=true
複製程式碼
對應的bean配置:
package com.dudu.config;
/**
* Druid配置
*
* @author dudu
* @date 2017-12-11 0:00
*/
@Configuration
public class DruidConfig {
private Logger logger = LoggerFactory.getLogger(DruidConfig.class);
@Value("${spring.datasource.url:#{null}}")
private String dbUrl;
@Value("${spring.datasource.username: #{null}}")
private String username;
@Value("${spring.datasource.password:#{null}}")
private String password;
@Value("${spring.datasource.driverClassName:#{null}}")
private String driverClassName;
@Value("${spring.datasource.initialSize:#{null}}")
private Integer initialSize;
@Value("${spring.datasource.minIdle:#{null}}")
private Integer minIdle;
@Value("${spring.datasource.maxActive:#{null}}")
private Integer maxActive;
@Value("${spring.datasource.maxWait:#{null}}")
private Integer maxWait;
@Value("${spring.datasource.timeBetweenEvictionRunsMillis:#{null}}")
private Integer timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.minEvictableIdleTimeMillis:#{null}}")
private Integer minEvictableIdleTimeMillis;
@Value("${spring.datasource.validationQuery:#{null}}")
private String validationQuery;
@Value("${spring.datasource.testWhileIdle:#{null}}")
private Boolean testWhileIdle;
@Value("${spring.datasource.testOnBorrow:#{null}}")
private Boolean testOnBorrow;
@Value("${spring.datasource.testOnReturn:#{null}}")
private Boolean testOnReturn;
@Value("${spring.datasource.poolPreparedStatements:#{null}}")
private Boolean poolPreparedStatements;
@Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize:#{null}}")
private Integer maxPoolPreparedStatementPerConnectionSize;
@Value("${spring.datasource.filters:#{null}}")
private String filters;
@Value("{spring.datasource.connectionProperties:#{null}}")
private String connectionProperties;
@Bean
@Primary
public DataSource dataSource(){
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(this.dbUrl);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setDriverClassName(driverClassName);
//configuration
if(initialSize != null) {
datasource.setInitialSize(initialSize);
}
if(minIdle != null) {
datasource.setMinIdle(minIdle);
}
if(maxActive != null) {
datasource.setMaxActive(maxActive);
}
if(maxWait != null) {
datasource.setMaxWait(maxWait);
}
if(timeBetweenEvictionRunsMillis != null) {
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
}
if(minEvictableIdleTimeMillis != null) {
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
}
if(validationQuery!=null) {
datasource.setValidationQuery(validationQuery);
}
if(testWhileIdle != null) {
datasource.setTestWhileIdle(testWhileIdle);
}
if(testOnBorrow != null) {
datasource.setTestOnBorrow(testOnBorrow);
}
if(testOnReturn != null) {
datasource.setTestOnReturn(testOnReturn);
}
if(poolPreparedStatements != null) {
datasource.setPoolPreparedStatements(poolPreparedStatements);
}
if(maxPoolPreparedStatementPerConnectionSize != null) {
datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
}
if(connectionProperties != null) {
datasource.setConnectionProperties(connectionProperties);
}
List<Filter> filters = new ArrayList<>();
filters.add(statFilter());
filters.add(wallFilter());
datasource.setProxyFilters(filters);
return datasource;
}
@Bean
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
//控制檯管理使用者,加入下面2行 進入druid後臺就需要登入
//servletRegistrationBean.addInitParameter("loginUsername", "admin");
//servletRegistrationBean.addInitParameter("loginPassword", "admin");
return servletRegistrationBean;
}
@Bean
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
filterRegistrationBean.setFilter(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
filterRegistrationBean.addInitParameter("profileEnable", "true");
return filterRegistrationBean;
}
@Bean
public StatFilter statFilter(){
StatFilter statFilter = new StatFilter();
statFilter.setLogSlowSql(true); //slowSqlMillis用來配置SQL慢的標準,執行時間超過slowSqlMillis的就是慢。
statFilter.setMergeSql(true); //SQL合併配置
statFilter.setSlowSqlMillis(1000);//slowSqlMillis的預設值為3000,也就是3秒。
return statFilter;
}
@Bean
public WallFilter wallFilter(){
WallFilter wallFilter = new WallFilter();
//允許執行多條SQL
WallConfig config = new WallConfig();
config.setMultiStatementAllow(true);
wallFilter.setConfig(config);
return wallFilter;
}
}
複製程式碼
mybatis相關依賴
<!--mybatis-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.1</version>
</dependency>
<!--通用mapper-->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>1.1.5</version>
</dependency>
<!--pagehelper 分頁外掛-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.3</version>
</dependency>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.5</version>
<dependencies>
<!--配置這個依賴主要是為了等下在配置mybatis-generator.xml的時候可以不用配置classPathEntry這樣的一個屬性,避免程式碼的耦合度太高-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.44</version>
</dependency>
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper</artifactId>
<version>3.4.0</version>
</dependency>
</dependencies>
<executions>
<execution>
<id>Generate MyBatis Artifacts</id>
<phase>package</phase>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<configuration>
<!--允許移動生成的檔案 -->
<verbose>true</verbose>
<!-- 是否覆蓋 -->
<overwrite>true</overwrite>
<!-- 自動生成的配置 -->
<configurationFile>src/main/resources/mybatis-generator.xml</configurationFile>
</configuration>
</plugin>
</plugins>
</build>
複製程式碼
上面引入了mybatis相關的一些依賴以及generator的配置,這裡generator配置檔案指向 src/main/resources/mybatis-generator.xml檔案,具體一會貼出。
對應的application.properties配置:
#指定bean所在包
mybatis.type-aliases-package=com.dudu.domain
#指定對映檔案
mybatis.mapperLocations=classpath:mapper/*.xml
#mapper
#mappers 多個介面時逗號隔開
mapper.mappers=com.dudu.util.MyMapper
mapper.not-empty=false
mapper.identity=MYSQL
#pagehelper
pagehelper.helperDialect=mysql
pagehelper.reasonable=true
pagehelper.supportMethodsArguments=true
pagehelper.params=count=countSql
複製程式碼
通用Mapper配置
通用Mapper都可以極大的方便開發人員,對單表封裝了許多通用方法,省掉自己寫增刪改查的sql。 通用Mapper外掛網址:https://github.com/abel533/Mapper
package com.dudu.util;
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;
/**
* 繼承自己的MyMapper
*
* @author
* @since 2017-06-26 21:53
*/
public interface MyMapper<T> extends Mapper<T>, MySqlMapper<T> {
//FIXME 特別注意,該介面不能被掃描到,否則會出錯
}
複製程式碼
這裡實現一個自己的介面,繼承通用的mapper,關鍵點就是這個介面不能被掃描到,不能跟dao這個存放mapper檔案放在一起。
最後在啟動類中通過MapperScan註解指定掃描的mapper路徑:
package com.dudu;
@SpringBootApplication
//啟註解事務管理
@EnableTransactionManagement // 啟註解事務管理,等同於xml配置方式的 <tx:annotation-driven />
@MapperScan(basePackages = "com.dudu.dao", markerInterface = MyMapper.class)
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
複製程式碼
MyBatis Generator配置
這裡配置一下上面提到的mybatis-generator.xml檔案,該配置檔案用來自動生成表對應的Model,Mapper以及xml,該檔案位於src/main/resources
下面
Mybatis Geneator 詳解: http://blog.csdn.net/isea533/article/details/42102297
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<!--載入配置檔案,為下面讀取資料庫資訊準備-->
<properties resource="application.properties"/>
<context id="Mysql" targetRuntime="MyBatis3Simple" defaultModelType="flat">
<plugin type="tk.mybatis.mapper.generator.MapperPlugin">
<property name="mappers" value="com.dudu.util.MyMapper" />
<!--caseSensitive預設false,當資料庫表名區分大小寫時,可以將該屬性設定為true-->
<property name="caseSensitive" v
alue="true"/>
</plugin>
<!-- 阻止生成自動註釋 -->
<commentGenerator>
<property name="javaFileEncoding" value="UTF-8"/>
<property name="suppressDate" value="true"/>
<property name="suppressAllComments" value="true"/>
</commentGenerator>
<!--資料庫連結地址賬號密碼-->
<jdbcConnection driverClass="${spring.datasource.driver-class-name}"
connectionURL="${spring.datasource.url}"
userId="${spring.datasource.username}"
password="${spring.datasource.password}">
</jdbcConnection>
<javaTypeResolver>
<property name="forceBigDecimals" value="false"/>
</javaTypeResolver>
<!--生成Model類存放位置-->
<javaModelGenerator targetPackage="com.dudu.domain" targetProject="src/main/java">
<property name="enableSubPackages" value="true"/>
<property name="trimStrings" value="true"/>
</javaModelGenerator>
<!--生成對映檔案存放位置-->
<sqlMapGenerator targetPackage="mapper" targetProject="src/main/resources">
<property name="enableSubPackages" value="true"/>
</sqlMapGenerator>
<!--生成Dao類存放位置-->
<!-- 客戶端程式碼,生成易於使用的針對Model物件和XML配置檔案 的程式碼
type="ANNOTATEDMAPPER",生成Java Model 和基於註解的Mapper物件
type="XMLMAPPER",生成SQLMap XML檔案和獨立的Mapper介面
-->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.dudu.dao" targetProject="src/main/java">
<property name="enableSubPackages" value="true"/>
</javaClientGenerator>
<!--生成對應表及類名
去掉Mybatis Generator生成的一堆 example
-->
<table tableName="LEARN_RESOURCE" domainObjectName="LearnResource" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false">
<generatedKey column="id" sqlStatement="Mysql" identity="true"/>
</table>
</context>
</generatorConfiguration>
複製程式碼
其中,我們通過<properties resource="application.properties"/>
引入了配置檔案,這樣下面指定資料來源的時候不用寫死。
其中tk.mybatis.mapper.generator.MapperPlugin很重要,用來指定通用Mapper對應的檔案,這樣我們生成的mapper都會繼承這個通用Mapper
<plugin type="tk.mybatis.mapper.generator.MapperPlugin">
<property name="mappers" value="com.dudu.util.MyMapper" />
<!--caseSensitive預設false,當資料庫表名區分大小寫時,可以將該屬性設定為true-->
<property name="caseSensitive" value="true"/>
</plugin>
複製程式碼
這樣就可以通過mybatis-generator外掛生成對應的檔案啦
如果不是IDEA開發環境也可以直接通過命令:mvn mybatis-generator:generate
自動生成的檔案如下圖所示
指令碼初始化
CREATE DATABASE /*!32312 IF NOT EXISTS*/`spring` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `spring`;
DROP TABLE IF EXISTS `learn_resource`;
CREATE TABLE `learn_resource` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`author` varchar(20) DEFAULT NULL COMMENT '作者',
`title` varchar(100) DEFAULT NULL COMMENT '描述',
`url` varchar(100) DEFAULT NULL COMMENT '地址連結',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1029 DEFAULT CHARSET=utf8;
insert into `learn_resource`(`id`,`author`,`title`,`url`) values (999,'官方SpriongBoot例子','官方SpriongBoot例子','https://github.com/spring-projects/spring-boot/tree/master/spring-boot-samples');
insert into `learn_resource`(`id`,`author`,`title`,`url`) values (1000,'龍果學院','Spring Boot 教程系列學習','http://www.roncoo.com/article/detail/124661');
insert into `learn_resource`(`id`,`author`,`title`,`url`) values (1001,'嘟嘟MD獨立部落格','Spring Boot乾貨系列','http://tengj.top/');
insert into `learn_resource`(`id`,`author`,`title`,`url`) values (1002,'後端程式設計嘟','Spring Boot視訊教程','http://www.toutiao.com/m1559096720023553/');
複製程式碼
Controller層
到此為止,基本的配置結束了,我們開始實現業務的邏輯,Controller層程式碼如下
/** 教程頁面
* Created by tengj on 2017/12/19
*/
@Controller
@RequestMapping("/learn")
public class LearnController extends AbstractController{
@Autowired
private LearnService learnService;
private Logger logger = LoggerFactory.getLogger(this.getClass());
@RequestMapping("")
public String learn(Model model){
model.addAttribute("ctx", getContextPath()+"/");
return "learn-resource";
}
/**
* 查詢教程列表
* @param page
* @return
*/
@RequestMapping(value = "/queryLeanList",method = RequestMethod.POST)
@ResponseBody
public AjaxObject queryLearnList(Page<LeanQueryLeanListReq> page){
List<LearnResource> learnList=learnService.queryLearnResouceList(page);
PageInfo<LearnResource> pageInfo =new PageInfo<LearnResource>(learnList);
return AjaxObject.ok().put("page", pageInfo);
}
/**
* 新添教程
* @param learn
*/
@RequestMapping(value = "/add",method = RequestMethod.POST)
@ResponseBody
public AjaxObject addLearn(@RequestBody LearnResource learn){
learnService.save(learn);
return AjaxObject.ok();
}
/**
* 修改教程
* @param learn
*/
@RequestMapping(value = "/update",method = RequestMethod.POST)
@ResponseBody
public AjaxObject updateLearn(@RequestBody LearnResource learn){
learnService.updateNotNull(learn);
return AjaxObject.ok();
}
/**
* 刪除教程
* @param ids
*/
@RequestMapping(value="/delete",method = RequestMethod.POST)
@ResponseBody
public AjaxObject deleteLearn(@RequestBody Long[] ids){
learnService.deleteBatch(ids);
return AjaxObject.ok();
}
}
複製程式碼
通用Service
正常情況下具體業務是每個模組的service裡面定義許多方法,然後mapper中實現。
但是博主檢視外掛文件後發現一個通用Mapper在Spring4中的最佳用法。那就是通用的Service。 具體可以檢視這裡瞭解:https://gitee.com/free/Mapper2/blob/master/wiki/mapper/4.Spring4.md
定義通用service介面
/**
* 通用介面
*/
@Service
public interface IService<T> {
T selectByKey(Object key);
int save(T entity);
int delete(Object key);
int updateAll(T entity);
int updateNotNull(T entity);
List<T> selectByExample(Object example);
//TODO 其他...
}
複製程式碼
具體實現通用介面類
/**
* 通用Service
* @param <T>
*/
public abstract class BaseService<T> implements IService<T> {
@Autowired
protected Mapper<T> mapper;
public Mapper<T> getMapper() {
return mapper;
}
@Override
public T selectByKey(Object key) {
//說明:根據主鍵欄位進行查詢,方法引數必須包含完整的主鍵屬性,查詢條件使用等號
return mapper.selectByPrimaryKey(key);
}
@Override
public int save(T entity) {
//說明:儲存一個實體,null的屬性也會儲存,不會使用資料庫預設值
return mapper.insert(entity);
}
@Override
public int delete(Object key) {
//說明:根據主鍵欄位進行刪除,方法引數必須包含完整的主鍵屬性
return mapper.deleteByPrimaryKey(key);
}
@Override
public int updateAll(T entity) {
//說明:根據主鍵更新實體全部欄位,null值會被更新
return mapper.updateByPrimaryKey(entity);
}
@Override
public int updateNotNull(T entity) {
//根據主鍵更新屬性不為null的值
return mapper.updateByPrimaryKeySelective(entity);
}
@Override
public List<T> selectByExample(Object example) {
//說明:根據Example條件進行查詢
//重點:這個查詢支援通過Example類指定查詢列,通過selectProperties方法指定查詢列
return mapper.selectByExample(example);
}
}
複製程式碼
到此基本的增刪改查通用service就寫好了,具體業務的service就直接繼承這個介面即可,也可以新增額外的方法,例如:
public interface LearnService extends IService<LearnResource>{
public List<LearnResource> queryLearnResouceList(Page<LeanQueryLeanListReq> page);
public void deleteBatch(Long[] ids);
}
複製程式碼
具體實現service
/**
* Created by tengj on 2017/4/7.
*/
@Service
public class LearnServiceImpl extends BaseService<LearnResource> implements LearnService {
@Autowired
private LearnResourceMapper learnResourceMapper;
@Override
public void deleteBatch(Long[] ids) {
Arrays.stream(ids).forEach(id->learnResourceMapper.deleteByPrimaryKey(id));
}
@Override
public List<LearnResource> queryLearnResouceList(Page<LeanQueryLeanListReq> page) {
PageHelper.startPage(page.getPage(), page.getRows());
return learnResourceMapper.queryLearnResouceList(page.getCondition());
}
}
複製程式碼
可以看到,具體LearnServiceImpl這邊就實現了2個方法,其他的都使用了通用service的,在開發上剩下了許多功夫。
Mapper相關
在自動生成的mapper檔案中實現sevice自定義的方法:
public interface LearnResourceMapper extends MyMapper<LearnResource> {
List<LearnResource> queryLearnResouceList(Map<String,Object> map);
}
複製程式碼
LearnResourceMapper.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.dudu.dao.LearnResourceMapper">
<resultMap id="BaseResultMap" type="com.dudu.domain.LearnResource">
<!--
WARNING - @mbg.generated
-->
<id column="id" jdbcType="BIGINT" property="id" />
<result column="author" jdbcType="VARCHAR" property="author" />
<result column="title" jdbcType="VARCHAR" property="title" />
<result column="url" jdbcType="VARCHAR" property="url" />
</resultMap>
<select id="queryLearnResouceList" resultType="com.dudu.domain.LearnResource">
SELECT * from learn_resource where 1=1
<if test="author != null and author!= ''">
and author like CONCAT('%',#{author},'%')
</if>
<if test="title != null and title!= ''">
and title like CONCAT('%',#{title},'%')
</if>
order by id desc
</select>
</mapper>
複製程式碼
IDEA可以安裝這個外掛,這樣就可以直接從Mapper檔案跳轉到xml了
最終專案效果如下,增刪改查分頁一個都不少:
上面提到druid有對應的監控介面,啟動專案後輸入http://localhost:8090/spring/druid 即可登入,介面效果如下
總結
到此,一套適合企業級開發的Spring Boot應用模板就好了,Mybatis+通用Mapper、Mybatis Geneator確實可以省下很多開發成本,提高效率。前端整合了vue.js,具體看原始碼。
想要檢視更多Spring Boot乾貨教程,可前往:Spring Boot乾貨系列總綱
# 原始碼下載 ( ̄︶ ̄)↗[相關示例完整程式碼] - chapter11==》Spring Boot乾貨系列:(十一)資料儲存篇-Spring Boot整合Mybatis通用Mapper外掛
想要ace模板原始碼的話,在博主公眾號回覆關鍵字:ace
一直覺得自己寫的不是技術,而是情懷,一篇篇文章是自己這一路走來的痕跡。靠專業技能的成功是最具可複製性的,希望我的這條路能讓你少走彎路,希望我能幫你抹去知識的蒙塵,希望我能幫你理清知識的脈絡,希望未來技術之巔上有你也有我。