目标：
使用 Spring boot+MyBatis+mysql 集成 Mapper 和 PageHelper，实现基本的增删改查
先建一个基本的 Spring Boot 项目开启 Spring Boot 参考
使用的 spring boot 版本为 2.1.6.RELEASE
1.集成MyBatis
引入mybatis-spring-boot-starter和数据库连接驱动
修改pom.xml
<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.0.1</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency>
2.Druid数据源
Druid是一个关系型数据库连接池
阿里巴巴数据库事业部出品，为监控而生的数据库连接池。https://github.com/alibaba/druid
（1）引入依赖
修改pom.xml
<!-- druid数据源驱动 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.16</version> </dependency>
（2）配置
在 src/main/resources/application.properties 里添加
# 数据库访问配置, 使用druid数据源 spring.datasource.druid.db-type=mysql spring.datasource.druid.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.druid.url = jdbc:mysql://127.0.0.1:3306/bms?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&zeroDateTimeBehavior=convertToNull spring.datasource.druid.username=root spring.datasource.druid.password=root # 连接池配置 spring.datasource.druid.initial-size=5 spring.datasource.druid.min-idle=5 spring.datasource.druid.max-active=20 # 配置获取连接等待超时的时间 spring.datasource.druid.max-wait=30000 # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 spring.datasource.druid.time-between-eviction-runs-millis=60000 # 配置一个连接在池中最小生存的时间，单位是毫秒 spring.datasource.druid.min-evictable-idle-time-millis=300000 spring.datasource.druid.validation-query= select '1' from dual spring.datasource.druid.test-while-idle=true spring.datasource.druid.test-on-borrow=false spring.datasource.druid.test-on-return=false # 打开PSCache，并且指定每个连接上PSCache的大小 spring.datasource.druid.pool-prepared-statements=true spring.datasource.druid.max-open-prepared-statements=20 spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20 # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙 spring.datasource.druid.filters=stat,wall # WebStatFilter配置 spring.datasource.druid.web-stat-filter.enabled=true spring.datasource.druid.web-stat-filter.url-pattern=/* spring.datasource.druid.web-stat-filter.exclusions='*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*' #是否启用StatFilter默认值true spring.datasource.druid.stat-view-servlet.enabled=true # 访问路径为/druid时，跳转到StatViewServlet spring.datasource.druid.stat-view-servlet.url-pattern=/druid/* #druid监控管理界面登录帐号 spring.datasource.druid.stat-view-servlet.login-username=admin #druid监控管理界面登录密码 spring.datasource.druid.stat-view-servlet.login-password=123456 spring.datasource.druid.stat-view-servlet.allow=127.0.0.1 #是否开启重置功能 druid.monitor.resetEnable=false # 配置StatFilter spring.datasource.druid.filter.stat.log-slow-sql=true
配置了Druid作为连接池，并开启了Druid的监控功能
（3）运行项目
浏览器打开http://localhost:8080/druid/login.html
输入配置的用户名admin和密码123456登录后
3.集成通用Mapper和PageHelper分页插件
可以简化工作：
通用Mapper可以简化对单表的CRUD操作
PageHelper分页插件可以自动拼接分页SQL
可以使用MyBatis Geneator来自动生成实体类，Mapper接口和Mapper xml代码
（1）引入依赖
修改pom.xml
<1>通用Mapper和PageHelper
<!-- 通用mapper --> <dependency> <groupId>tk.mybatis</groupId> <artifactId>mapper-spring-boot-starter</artifactId> <version>2.1.5</version> </dependency> <!-- pagehelper 分页插件 --> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.2.10</version> </dependency>
<2>MyBatis Geneator
<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.7</version> <dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>${mysql.version}</version> </dependency> <dependency> <groupId>tk.mybatis</groupId> <artifactId>mapper</artifactId> <version>4.1.5</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>
（2）配置
<1>配置MyBatis
修改src/main/resources/application.properties
# type-aliases扫描路径
mybatis.type-aliases-package=com.sfn.bms.system.model
# mapper xml实现扫描路径
mybatis.mapper-locations=classpath:mapper/*xml
<2>配置通用Mapper
修改src/main/resources/application.properties
mapper.mappers=com.sfn.bms.common.config.MyMapper
mapper.not-empty=false
mapper.identity=MYSQL
定义MyMapper接口
package com.sfn.bms.common.config; import tk.mybatis.mapper.common.Mapper; import tk.mybatis.mapper.common.MySqlMapper; public interface MyMapper<T> extends Mapper<T>, MySqlMapper<T> { }
注：
该接口不能被扫描到，自己定义的Mapper都需要继承这个接口
<3>配置PageHelper
修改src/main/resources/application.properties
pagehelper.helper-dialect=mysql pagehelper.reasonable=true pagehelper.support-methods-arguments=true pagehelper.params=count=countsql
<4>配置Geneator
在src/main/resources 下新建 mybatis-generator.xml
<?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> <context id="testTables" targetRuntime="MyBatis3Simple" defaultModelType="flat"> <plugin type="tk.mybatis.mapper.generator.MapperPlugin"> <!-- 该配置会使生产的Mapper自动继承MyMapper --> <property name="mappers" value="com.sfn.bms.common.config.MyMapper" /> <!-- caseSensitive默认false，当数据库表名区分大小写时，可以将该属性设置为true --> <property name="caseSensitive" value="false"/> </plugin> <!-- 阻止生成自动注释 --> <commentGenerator> <property name="javaFileEncoding" value="UTF-8"/> <property name="suppressDate" value="true"/> <property name="suppressAllComments" value="true"/> </commentGenerator> <!-- 数据库链接地址账号密码 --> <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver" connectionURL="jdbc:mysql://localhost:3306/bms?serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true" userId="root" password="root"> </jdbcConnection> <javaTypeResolver> <property name="forceBigDecimals" value="false"/> </javaTypeResolver> <!-- 生成Model类存放位置 --> <javaModelGenerator targetPackage="com.sfn.bms.system.model" 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.sfn.bms.system.mapper" targetProject="src/main/java"> <property name="enableSubPackages" value="true"/> </javaClientGenerator> <!-- 配置需要生成的表 --> <table tableName="T_USER" domainObjectName="User" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"> <generatedKey column="id" sqlStatement="mysql" identity="true"/> </table> </context> </generatorConfiguration>
（3）代码生成
run—》edit configurations
command line：mybatis-generator:generate -e
执行后输出
"C:\Program Files\Java\jdk1.8.0_151\bin\java" -Dmaven.multiModuleProjectDirectory=E:\java\project\newbms "-Dmaven.home=D:\Program Files\JetBrains\IntelliJ IDEA 2017.3.5\plugins\maven\lib\maven3" "-Dclassworlds.conf=D:\Program Files\JetBrains\IntelliJ IDEA 2017.3.5\plugins\maven\lib\maven3\bin\m2.conf" "-javaagent:D:\Program Files\JetBrains\IntelliJ IDEA 2017.3.5\lib\idea_rt.jar=8709:D:\Program Files\JetBrains\IntelliJ IDEA 2017.3.5\bin" -Dfile.encoding=UTF-8 -classpath "D:\Program Files\JetBrains\IntelliJ IDEA 2017.3.5\plugins\maven\lib\maven3\boot\plexus-classworlds-2.5.2.jar" org.codehaus.classworlds.Launcher -Didea.version=2017.3.5 mybatis-generator:generate -e
[INFO] Error stacktraces are turned on.
[INFO] Scanning for projects...
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] Building bms 0.0.1-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] --- mybatis-generator-maven-plugin:1.3.7:generate (default-cli) @ bms ---
[INFO] Connecting to the Database
[INFO] Introspecting table USER
[INFO] Generating Record class for table user
[INFO] Generating Mapper Interface for table user
[INFO] Generating SQL Map for table user
[INFO] Saving file UserMapper.xml
[INFO] Saving file User.java
[INFO] Saving file UserMapper.java
[WARNING] Existing file E:\java\project\newbms\src\main\java\com\sfn\bms\system\model\User.java was overwritten
[WARNING] Existing file E:\java\project\newbms\src\main\java\com\sfn\bms\system\mapper\UserMapper.java was overwritten
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 1.720 s
[INFO] Finished at: 2019-06-21T12:43:01+08:00
[INFO] Final Memory: 27M/398M
[INFO] ------------------------------------------------------------------------
自动生成以下代码
UserMapper
package com.sfn.bms.system.mapper; import com.sfn.bms.common.config.MyMapper; import com.sfn.bms.system.model.User; public interface UserMapper extends MyMapper<User> { }
User
package com.sfn.bms.system.model; import javax.persistence.*; public class User { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Short id; /** * 账号 */ private String account; /** * 密码 */ private String password; /** * 邮箱 */ private String email; /** * 状态 1-正常，0-禁用，-1-删除 */ private Boolean status; /** * 添加时间 */ @Column(name = "create_time") private Integer createTime; /** * 上次登陆时间 */ @Column(name = "last_login_time") private Integer lastLoginTime; /** * 上次登录IP */ @Column(name = "last_login_ip") private String lastLoginIp; /** * 登陆次数 */ @Column(name = "login_count") private Integer loginCount; /** * @return id */ public Short getId() { return id; } /** * @param id */ public void setId(Short id) { this.id = id; } /** * 获取账号 * * @return account - 账号 */ public String getAccount() { return account; } /** * 设置账号 * * @param account 账号 */ public void setAccount(String account) { this.account = account == null ? null : account.trim(); } /** * 获取密码 * * @return password - 密码 */ public String getPassword() { return password; } /** * 设置密码 * * @param password 密码 */ public void setPassword(String password) { this.password = password == null ? null : password.trim(); } /** * 获取邮箱 * * @return email - 邮箱 */ public String getEmail() { return email; } /** * 设置邮箱 * * @param email 邮箱 */ public void setEmail(String email) { this.email = email == null ? null : email.trim(); } /** * 获取状态 1-正常，0-禁用，-1-删除 * * @return status - 状态 1-正常，0-禁用，-1-删除 */ public Boolean getStatus() { return status; } /** * 设置状态 1-正常，0-禁用，-1-删除 * * @param status 状态 1-正常，0-禁用，-1-删除 */ public void setStatus(Boolean status) { this.status = status; } /** * 获取添加时间 * * @return create_time - 添加时间 */ public Integer getCreateTime() { return createTime; } /** * 设置添加时间 * * @param createTime 添加时间 */ public void setCreateTime(Integer createTime) { this.createTime = createTime; } /** * 获取上次登陆时间 * * @return last_login_time - 上次登陆时间 */ public Integer getLastLoginTime() { return lastLoginTime; } /** * 设置上次登陆时间 * * @param lastLoginTime 上次登陆时间 */ public void setLastLoginTime(Integer lastLoginTime) { this.lastLoginTime = lastLoginTime; } /** * 获取上次登录IP * * @return last_login_ip - 上次登录IP */ public String getLastLoginIp() { return lastLoginIp; } /** * 设置上次登录IP * * @param lastLoginIp 上次登录IP */ public void setLastLoginIp(String lastLoginIp) { this.lastLoginIp = lastLoginIp == null ? null : lastLoginIp.trim(); } /** * 获取登陆次数 * * @return login_count - 登陆次数 */ public Integer getLoginCount() { return loginCount; } /** * 设置登陆次数 * * @param loginCount 登陆次数 */ public void setLoginCount(Integer loginCount) { this.loginCount = loginCount; } }
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.sfn.bms.system.mapper.UserMapper"> <resultMap id="BaseResultMap" type="com.sfn.bms.system.model.User"> <!-- WARNING - @mbg.generated --> <id column="id" jdbcType="SMALLINT" property="id" /> <result column="account" jdbcType="VARCHAR" property="account" /> <result column="password" jdbcType="CHAR" property="password" /> <result column="email" jdbcType="VARCHAR" property="email" /> <result column="status" jdbcType="BIT" property="status" /> <result column="create_time" jdbcType="INTEGER" property="createTime" /> <result column="last_login_time" jdbcType="INTEGER" property="lastLoginTime" /> <result column="last_login_ip" jdbcType="VARCHAR" property="lastLoginIp" /> <result column="login_count" jdbcType="INTEGER" property="loginCount" /> </resultMap> </mapper>
（4）通用Service
通用的Service，IService定义一些通用的方法
package com.sfn.bms.common.service; import org.springframework.stereotype.Service; import java.util.List; @Service public interface IService<T> { List<T> selectAll(); T selectByKey(Object key); int save(T entity); int delete(Object key); int batchDelete(List<String> list, String property, Class<T> clazz); int updateAll(T entity); int updateNotNull(T entity); List<T> selectByExample(Object example); }
其实现类BaseService
package com.sfn.bms.common.service.impl; import com.sfn.bms.common.service.IService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.transaction.annotation.Propagation; import org.springframework.transaction.annotation.Transactional; import tk.mybatis.mapper.common.Mapper; import tk.mybatis.mapper.entity.Example; import java.util.List; @Transactional(propagation = Propagation.SUPPORTS, readOnly = true, rollbackFor = Exception.class) public abstract class BaseService<T> implements IService<T> { @Autowired protected Mapper<T> mapper; public Mapper<T> getMapper() { return mapper; } @Override public List<T> selectAll() { return mapper.selectAll(); } @Override public T selectByKey(Object key) { return mapper.selectByPrimaryKey(key); } @Override @Transactional public int save(T entity) { return mapper.insert(entity); } @Override @Transactional public int delete(Object key) { return mapper.deleteByPrimaryKey(key); } @Override @Transactional public int batchDelete(List<String> list, String property, Class<T> clazz) { Example example = new Example(clazz); example.createCriteria().andIn(property, list); return this.mapper.deleteByExample(example); } @Override @Transactional public int updateAll(T entity) { return mapper.updateByPrimaryKey(entity); } @Override @Transactional public int updateNotNull(T entity) { return mapper.updateByPrimaryKeySelective(entity); } @Override public List<T> selectByExample(Object example) { return mapper.selectByExample(example); } }
（5）在UserService中使用BaseService中的通用方法
让UserService接口继承IService接口
package com.sfn.bms.system.service; import com.sfn.bms.common.service.IService; import com.sfn.bms.system.model.User; public interface UserService extends IService<User> { }
实现类UserServiceImpl
package com.sfn.bms.system.service.impl; import com.sfn.bms.common.service.impl.BaseService; import com.sfn.bms.system.model.User; import com.sfn.bms.system.service.UserService; import org.springframework.stereotype.Repository; @Repository("userService") public class UserServiceImpl extends BaseService<User> implements UserService { }
4.实现获取User信息的api接口
（1）让Spring Boot扫描到Mapper接口
在Spring Boot入口类中加入注解 @MapperScan("com.sfn.bms.system.mapper")
package com.sfn.bms; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import tk.mybatis.spring.annotation.MapperScan; @SpringBootApplication @MapperScan("com.sfn.bms.system.mapper") public class BmsApplication { public static void main(String[] args) { SpringApplication.run(BmsApplication.class, args); } }
（2）新建UserController
package com.sfn.bms.system.controller; import com.github.pagehelper.PageHelper; import com.github.pagehelper.PageInfo; import com.sfn.bms.system.model.User; import com.sfn.bms.system.service.UserService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.ResponseBody; import java.util.List; @Controller public class UserController { @Autowired UserService userService; @GetMapping("/query") @ResponseBody public List<User> getUserList(){ PageHelper.startPage(1,5); List<User> list = userService.selectAll(); PageInfo<User> pageInfo = new PageInfo<User>(list); List<User> rs = pageInfo.getList(); return rs; } }
运行项目
访问 http://localhost:8080/query
Druid监控
相关代码 地址
扫一扫
525
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
