微服務架構實戰篇（三）：Spring boot2.0 集成 PageHelper

簡介

該項目主要利用Spring boot2.0 +Mybatis + PageHelper實現增刪改查和分頁查詢功能，快速搭建一套和數據庫交互的項目。

• 源碼地址
• GitHub：https://github.com/yundianzixun/Spring-boot2.0-Mybatis-PageHelper
• 聯盟公眾號：IT實戰聯盟
• 我們社區：https://100boot.cn

小工具一枚，歡迎使用和Star支持，如使用過程中碰到問題，可以提出Issue，我會盡力完善該Starter

版本基礎

• Spring Boot：2.0.4
• Mybatis：3.4.5
• Druid：1.1.10
• PageHelper：4.1.6

操作步驟

第一步：下載SpringBoot2.0+Swagger2項目

• GitHub地址：https://github.com/yundianzixun/spring-boot-starter-swagger2
• 參考文檔：https://www.jianshu.com/p/61db1a6ca425
• 備註：微服務架構實戰系列每整合一箇中間件都會放到GitHub上，可根據教程一步一步學習微服務架構。

第二步：添加maven依賴（完整依賴）

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

4.0.0

com.itunion

spring-boot-starter-mybatis

0.0.1-SNAPSHOT

jar

spring-boot-starter-mybatis

Demo project for Spring Boot

org.springframework.boot

spring-boot-starter-parent

2.0.4.RELEASE

UTF-8

UTF-8

1.8

org.mybatis.spring.boot

mybatis-spring-boot-starter

1.3.2

mysql

mysql-connector-java

runtime

org.mybatis

mybatis

3.4.5

org.mybatis

mybatis-spring

1.3.1

com.github.pagehelper

pagehelper

4.1.6

com.alibaba

druid

1.1.10

org.springframework.boot

spring-boot-starter-test

io.springfox

springfox-swagger2

2.7.0

io.springfox

springfox-swagger-ui

2.7.0

org.apache.tomcat.embed

tomcat-embed-jasper

org.springframework.boot

spring-boot-starter-tomcat

org.springframework.boot

spring-boot-starter-web

demo

org.springframework.boot

spring-boot-maven-plugin

org.apache.maven.plugins

maven-war-plugin

2.1.1

false

第三步：application.properties 配置（完整配置）

server.port=8081

server.servlet.context-path=/Demo

server.tomcat.accesslog.enabled=true

druid.driver=com.mysql.jdbc.Driver

druid.url=jdbc:mysql://127.0.0.1:3306/數據庫名稱?serverTimezone=Asia/Shanghai&allowMultiQueries=true

druid.username=數據庫用戶名

druid.password=數據庫密碼

druid.init-size=1

druid.min-idel=1

druid.max-active=5

druid.login.timeout.seconds=30

druid.query.timeout.seconds=30

swagger.enable=true

第四步：增加mybatis-config.xml文件

備註

• PageHelper是分頁插件
• UserInfoMapper.xml 是我們本篇文章要實現丟用戶的增刪改查映射文件

第五步：創建數據庫連接池DataSource（DatasourceConfig.java）

package com.itunion.springbootstartermybatis.config;

import com.alibaba.druid.pool.DruidDataSource;

import org.apache.ibatis.session.SqlSessionFactory;

import org.mybatis.spring.SqlSessionFactoryBean;

import org.mybatis.spring.annotation.MapperScan;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.beans.factory.annotation.Value;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.core.io.ClassPathResource;

import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.annotation.PostConstruct;

import javax.sql.DataSource;

@Configuration

@MapperScan(basePackages = "com.itunion.springbootstartermybatis.mapper")

public class DatasourceConfig {

private static Logger log = LoggerFactory.getLogger(DatasourceConfig.class);

@Value("${druid.driver}")

private String driverClassName;

@Value("${druid.url}")

private String url;

@Value("${druid.username}")

private String username;

@Value("${druid.password}")

private String password;

@Value("${druid.init-size}")

private int initSize;

@Value("${druid.min-idel}")

private int minIdel;

@Value("${druid.max-active}")

private int maxActive;

@Value("${druid.login.timeout.seconds}")

private int loginTimeoutSeconds;

@Value("${druid.query.timeout.seconds}")

private int queryTimeoutSeconds;

@Bean

public DataSource dataSource() {

DruidDataSource ds = new DruidDataSource();

ds.setDriverClassName(driverClassName);

ds.setUrl(url);

ds.setUsername(username);

ds.setPassword(password);

ds.setInitialSize(initSize);

ds.setMinIdle(minIdel);

ds.setMaxActive(maxActive);

ds.setLoginTimeout(loginTimeoutSeconds);

ds.setQueryTimeout(queryTimeoutSeconds);

return ds;

}

@Bean

public SqlSessionFactory sqlSessionFactory() throws Exception {

final SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();

sqlSessionFactory.setDataSource(dataSource());

sqlSessionFactory.setConfigLocation(new ClassPathResource("mybatis-config.xml"));

sqlSessionFactory.setFailFast(true);

return sqlSessionFactory.getObject();

}

public DataSourceTransactionManager dataSourceTransactionManager() {

log.debug("> transactionManager");

return new DataSourceTransactionManager(dataSource());

}

@PostConstruct

public void postConstruct() {

log.info("jdbc settings={}", this);

}

}

第六步：創建UserInfoMapper.xml

id, user_name, password, sex, content, create_date, update_date

select

from user_info

where id = #{id,jdbcType=INTEGER}

delete from user_info

where id = #{id,jdbcType=INTEGER}

insert into user_info (id, user_name, password,

sex, content, create_date,

update_date)

values (#{id,jdbcType=INTEGER}, #{userName,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR},

#{sex,jdbcType=VARCHAR}, #{content,jdbcType=VARCHAR}, #{createDate,jdbcType=TIMESTAMP},

#{updateDate,jdbcType=TIMESTAMP})

insert into user_info

id,

user_name,

password,

sex,

content,

create_date,

update_date,

#{id,jdbcType=INTEGER},

#{userName,jdbcType=VARCHAR},

#{password,jdbcType=VARCHAR},

#{sex,jdbcType=VARCHAR},

#{content,jdbcType=VARCHAR},

#{createDate,jdbcType=TIMESTAMP},

#{updateDate,jdbcType=TIMESTAMP},

update user_info

user_name = #{userName,jdbcType=VARCHAR},

password = #{password,jdbcType=VARCHAR},

sex = #{sex,jdbcType=VARCHAR},

content = #{content,jdbcType=VARCHAR},

create_date = #{createDate,jdbcType=TIMESTAMP},

update_date = #{updateDate,jdbcType=TIMESTAMP},

where id = #{id,jdbcType=INTEGER}

update user_info

set user_name = #{userName,jdbcType=VARCHAR},

password = #{password,jdbcType=VARCHAR},

sex = #{sex,jdbcType=VARCHAR},

content = #{content,jdbcType=VARCHAR},

create_date = #{createDate,jdbcType=TIMESTAMP},

update_date = #{updateDate,jdbcType=TIMESTAMP}

where id = #{id,jdbcType=INTEGER}

select

id, user_name as userName, password, sex, content, create_date as createDate, update_date as updateDate

from user_info

• 實現了對用戶表的增刪改查和批量查詢sql語句

第七步：編寫UserInfo實體類

package com.itunion.springbootstartermybatis.model;

import java.util.Date;

public class UserInfo {

private Integer id;

private String userName;

private String password;

private String sex;

private String content;

private Date createDate;

private Date updateDate;

此處省略get/set方法

}

第八步：編寫UserInfoMapper

package com.itunion.springbootstartermybatis.mapper;

import com.itunion.springbootstartermybatis.model.UserInfo;

import com.itunion.springbootstartermybatis.web.dto.UserInfoReqListDto;

import com.itunion.springbootstartermybatis.web.dto.UserInfoDto;

import java.util.List;

public interface UserInfoMapper {

int deleteByPrimaryKey(Integer id);

int insert(UserInfo record);

int insertSelective(UserInfo record);

UserInfo selectByPrimaryKey(Integer id);

int updateByPrimaryKeySelective(UserInfo record);

int updateByPrimaryKey(UserInfo record);

/**

* 獲取用戶信息集合

* @return

*/

List getUserInfoList(UserInfoReqListDto userInfoReqListDto);

}

第九步：編寫UserInfoService接口和接口實現

UserInfoService.java

package com.itunion.springbootstartermybatis.service;

import com.github.pagehelper.PageInfo;

import com.itunion.springbootstartermybatis.web.dto.UserInfoDto;

import com.itunion.springbootstartermybatis.web.dto.UserInfoReqListDto;

import java.util.List;

/**

* 用戶數據服務

* Created by lin on 2018年06月07日21:12:04

*/

public interface UserInfoService {

/**

* 新增用戶信息

*/

int addUserInfo(UserInfoDto record);

/**

* 查詢所有用戶信息

*/

List getUserInfoList();

/**

* 根據用戶ID刪除用戶信息

*/

int delUserInfoById(Integer id);

/**

* 根據用戶ID修改用戶信息

*/

int modifyUserInfoById(UserInfoDto record);

/**

* 分頁查詢

*/

PageInfo getUserInfoListPage(UserInfoReqListDto userInfoReqListDto);

}

UserInfoServiceImpl.java

package com.itunion.springbootstartermybatis.service.impl;

import com.github.pagehelper.PageHelper;

import com.github.pagehelper.PageInfo;

import com.itunion.springbootstartermybatis.mapper.UserInfoMapper;

import com.itunion.springbootstartermybatis.model.UserInfo;

import com.itunion.springbootstartermybatis.service.UserInfoService;

import com.itunion.springbootstartermybatis.web.dto.UserInfoDto;

import com.itunion.springbootstartermybatis.web.dto.UserInfoReqListDto;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.beans.BeanUtils;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.List;

/**

* 用戶信息-服務提供方

* Created by lin on 2018年06月07日21:48:13

*/

@Service

public class UserInfoServiceImpl implements UserInfoService {

private final static Logger LOGGER = LoggerFactory.getLogger(UserInfoServiceImpl.class);

@Autowired

private UserInfoMapper userInfoMapper;

@Override

public int addUserInfo(UserInfoDto record) {

LOGGER.info("進入用戶信息-服務提供方-UserInfoServiceImpl.addUserInfo[" + new SimpleDateFormat("HH:mm:ss").format(new Date()) + "]");

UserInfo userInfo = new UserInfo();

BeanUtils.copyProperties(record, userInfo);

userInfo.setUpdateDate(new Date());

return userInfoMapper.insertSelective(userInfo);

}

@Override

public List getUserInfoList() {

LOGGER.info("進入用戶信息-服務提供方-UserInfoServiceImpl.getUserInfoList[" + new SimpleDateFormat("HH:mm:ss").format(new Date()) + "]");

return userInfoMapper.getUserInfoList(null);

}

@Override

public int delUserInfoById(Integer id) {

LOGGER.info("進入用戶信息-服務提供方-UserInfoServiceImpl.delUserInfoById[" + new SimpleDateFormat("HH:mm:ss").format(new Date()) + "]");

return userInfoMapper.deleteByPrimaryKey(id);

}

@Override

public int modifyUserInfoById(UserInfoDto record) {

LOGGER.info("進入用戶信息-服務提供方-UserInfoServiceImpl.modifyUserInfoById[" + new SimpleDateFormat("HH:mm:ss").format(new Date()) + "]");

UserInfo userInfo = new UserInfo();

BeanUtils.copyProperties(record, userInfo);

userInfo.setUpdateDate(new Date());

return userInfoMapper.updateByPrimaryKeySelective(userInfo);

}

@Override

public PageInfo getUserInfoListPage(UserInfoReqListDto userInfoReqListDto) {

LOGGER.info("進入用戶信息-服務提供方-UserInfoServiceImpl.getUserInfoListPage[" + new SimpleDateFormat("HH:mm:ss").format(new Date()) + "]");

PageHelper.startPage(userInfoReqListDto.getPage(), userInfoReqListDto.getSize());

List userInfoDtos = userInfoMapper.getUserInfoList(userInfoReqListDto);

PageInfo userInfoDtoPageInfo = new PageInfo<>(userInfoDtos);

return userInfoDtoPageInfo;

}

}

第十步：編寫UserInfoController

package com.itunion.springbootstartermybatis.web.controller;

import com.github.pagehelper.PageInfo;

import com.itunion.springbootstartermybatis.common.UniformResultTemplate;

import com.itunion.springbootstartermybatis.service.UserInfoService;

import com.itunion.springbootstartermybatis.web.dto.UserInfoDto;

import com.itunion.springbootstartermybatis.web.dto.UserInfoReqListDto;

import io.swagger.annotations.Api;

import io.swagger.annotations.ApiImplicitParam;

import io.swagger.annotations.ApiOperation;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.http.MediaType;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.List;

/**

* 用戶信息-移動端消費方

* Created by lin on 2018年06月07日22:02:07

*/

@Controller

@RequestMapping("userInfo")

@Api(description = "測試移動")

public class UserInfoController {

private Logger LOGGER = LoggerFactory.getLogger(UserInfoController.class);

@Autowired

UserInfoService userInfoService;

@ApiOperation(value = "getUserInfoList", notes = "查詢所有用戶信息")

@RequestMapping(value = "getUserInfoList", method = RequestMethod.GET)

@ResponseBody

public UniformResultTemplate> getUserInfoList() {

LOGGER.info("進入用戶信息-移動端消費方-UserInfoController.getUserInfoList[" + new SimpleDateFormat("HH:mm:ss").format(new Date()) + "]");

UniformResultTemplate> uniformResultTemplate = new UniformResultTemplate<>();

uniformResultTemplate.setCode(100);

uniformResultTemplate.setMessage("查詢所有用戶信息成功！");

uniformResultTemplate.setResult(userInfoService.getUserInfoList());

return uniformResultTemplate;

}

@ApiOperation(value = "getUserInfoListPage", notes = "查詢所有用戶信息-分頁")

@ApiImplicitParam(name = "userInfoReqListDto", value = "{"page":"1","size":"2"}")

@RequestMapping(value = "getUserInfoListPage", method = RequestMethod.POST)

@ResponseBody

public UniformResultTemplate getUserInfoList(@RequestBody UserInfoReqListDto userInfoReqListDto) {

LOGGER.info("進入用戶信息-移動端消費方-UserInfoController.getUserInfoListPage[" + new SimpleDateFormat("HH:mm:ss").format(new Date()) + "]");

UniformResultTemplate uniformResultTemplate = new UniformResultTemplate<>();

uniformResultTemplate.setCode(100);

uniformResultTemplate.setMessage("查詢所有用戶信息成功分頁！");

uniformResultTemplate.setResult(userInfoService.getUserInfoListPage(userInfoReqListDto));

return uniformResultTemplate;

}

@ApiOperation(value = "addUserInfo", notes = "新增用戶信息")

@ApiImplicitParam(name = "userInfoDto", value = "{"userName":"測試用戶名","password":"000000","sex":1,"content":"這裡是IT實戰聯哦~~~"}")

@RequestMapping(value="addUserInfo", method = RequestMethod.POST)

@ResponseBody

public UniformResultTemplate addUserInfo(@RequestBody UserInfoDto userInfoDto) {

LOGGER.info("進入用戶信息-移動端消費方-UserInfoController.addUserInfo[" + new SimpleDateFormat("HH:mm:ss").format(new Date()) + "]");

UniformResultTemplate uniformResultTemplate = new UniformResultTemplate<>();

Integer num = userInfoService.addUserInfo(userInfoDto);

if(num > 0){

uniformResultTemplate.setCode(100);

uniformResultTemplate.setMessage("新增用戶信息成功！");

uniformResultTemplate.setResult(num+"");

}else{

uniformResultTemplate.setCode(400);

uniformResultTemplate.setMessage("新增用戶信息失敗！");

uniformResultTemplate.setResult(num+"");

}

return uniformResultTemplate;

}

@ApiOperation(value="delUserInfoById", notes="根據用戶ID刪除用戶信息")

@ApiImplicitParam(name = "id", value = "4" , paramType="path" , dataType="Integer")

@RequestMapping(value="delUserInfoById/{id}", method = RequestMethod.POST)

@ResponseBody

public UniformResultTemplate deleteKdgVipItem(@PathVariable Integer id) {

LOGGER.info("進入用戶信息-移動端消費方-UserInfoController.delUserInfoById[" + new SimpleDateFormat("HH:mm:ss").format(new Date()) + "]");

UniformResultTemplate uniformResultTemplate = new UniformResultTemplate<>();

Integer num = userInfoService.delUserInfoById(id);

if(num > 0){

uniformResultTemplate.setCode(100);

uniformResultTemplate.setMessage("根據用戶ID刪除用戶信息成功！");

uniformResultTemplate.setResult(num+"");

}else{

uniformResultTemplate.setCode(400);

uniformResultTemplate.setMessage("根據用戶ID刪除用戶信息失敗！");

uniformResultTemplate.setResult(num+"");

}

return uniformResultTemplate;

}

@ApiOperation(value = "modifyUserInfo", notes = "修改用戶信息")

@ApiImplicitParam(name = "userInfoDto", value = "{"id":10,"userName":"測試修改用戶名","password":55555,"sex":1,"content":"這裡是最新的IT實戰聯哦~~~"}")

@RequestMapping(value="modifyUserInfo", method = RequestMethod.POST, produces= MediaType.APPLICATION_JSON_UTF8_VALUE)

@ResponseBody

public UniformResultTemplate modifyUserInfo(@RequestBody UserInfoDto userInfoDto) {

LOGGER.info("進入用戶信息-移動端消費方-UserInfoController.modifyUserInfo[" + new SimpleDateFormat("HH:mm:ss").format(new Date()) + "]");

UniformResultTemplate uniformResultTemplate = new UniformResultTemplate<>();

Integer num = userInfoService.modifyUserInfoById(userInfoDto);

if(num > 0){

uniformResultTemplate.setCode(100);

uniformResultTemplate.setMessage("修改用戶信息成功！");

uniformResultTemplate.setResult(num+"");

}else{

uniformResultTemplate.setCode(400);

uniformResultTemplate.setMessage("修改用戶信息失敗！");

uniformResultTemplate.setResult(num+"");

}

return uniformResultTemplate;

}

}

第十一步：SpringBootApplication 啟動

package com.itunion.springbootstartermybatis;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

import org.springframework.scheduling.annotation.EnableScheduling;

import org.springframework.web.bind.annotation.RestController;

@SpringBootApplication

public class SpringBootStarterMybatisApplication {

public static void main(String[] args) {

SpringApplication.run(SpringBootStarterMybatisApplication.class, args);

}

}

第十二步：訪問Swagger接口地址

http://127.0.0.1:8080/Demo/api

如下圖所示：

• 端口號已自己配置為準

貢獻者

• IT實戰聯盟-Line
• IT實戰聯盟-咖啡

更多精彩內容可以關注“IT實戰聯盟”哦~~~

我可以安排對工具和記事本木製背景

閱讀更多 IT實戰聯盟 的文章

關鍵字: 實戰篇 GitHub XML