Spring Boot+mybatis集成数据库访问
1、整合druid数据源
1.1 单数据源配置
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.10</version> </dependency>
@Configuration public class DruidConfig { @ConfigurationProperties(prefix = "spring.datasource") @Bean public DataSource druid(){ return new DruidDataSource(); } //配置Druid的监控 //1、配置一个管理后台的Servlet @Bean public ServletRegistrationBean statViewServlet(){ ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*"); Map<String,String> initParams = new HashMap<>(); initParams.put("loginUsername","admin"); initParams.put("loginPassword","123456"); initParams.put("allow","");//默认就是允许所有访问 initParams.put("deny","192.168.15.21"); bean.setInitParameters(initParams); return bean; } //2、配置一个web监控的filter @Bean public FilterRegistrationBean webStatFilter(){ FilterRegistrationBean bean = new FilterRegistrationBean(); bean.setFilter(new WebStatFilter()); Map<String,String> initParams = new HashMap<>(); initParams.put("exclusions","*.js,*.css,/druid/*"); bean.setInitParameters(initParams); bean.setUrlPatterns(Arrays.asList("/*")); return bean; } }
spring.datasource.druid.initial-size=10
spring.datasource.druid.min-idle=10
spring.datasource.druid.max-active=15
spring.datasource.druid.filters=stat,wall,log4j2
spring.datasource.druid.filter.config.enabled=true
spring.datasource.druid.filter.stat.enabled=true
spring.datasource.druid.filter.stat.db-type=mysql
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=2000
spring.datasource.druid.filter.wall.enabled=true
spring.datasource.druid.filter.wall.db-type=mysql
spring.datasource.druid.filter.wall.config.delete-allow=true
spring.datasource.druid.filter.wall.config.drop-table-allow=false
spring.datasource.druid.filter.log4j2.enabled=true
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=admin@zt3000
spring.datasource.druid.stat-view-servlet.use-global-data-source-stat=true
spring.datasource.druid.web-stat-filter.enabled=true
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
spring.datasource.druid.aop-patterns=com.xxxxxxxxxxx.bmp.dao.*
spring.datasource.druid.driver-class-name=com.mysql.jdbc.Driver
1.2 多数据源配置
spring:
# 数据源配置
datasource:
ds1: #数据源1
driver-class-name: com.mysql.jdbc.Driver # mysql的驱动你可以配置别的关系型数据库
url: jdbc:mysql://ip:3306/db1 #数据源地址
username: root # 用户名
password: root # 密码
ds2: # 数据源2
driver-class-name: com.mysql.jdbc.Driver # mysql的驱动你可以配置别的关系型数据库
url: jdbc:mysql://ip:3307/db2#数据源地址
username: root # 用户名
password: root # 密码
/** * 多数据源配置 */ @Configuration public class DataSourceConfig { //主数据源配置 ds1数据源 @Primary @Bean(name = "ds1DataSourceProperties") @ConfigurationProperties(prefix = "spring.datasource.ds1") public DataSourceProperties ds1DataSourceProperties() { return new DataSourceProperties(); } //主数据源 ds1数据源 @Primary @Bean(name = "ds1DataSource") public DataSource ds1DataSource(@Qualifier("ds1DataSourceProperties") DataSourceProperties dataSourceProperties) { return dataSourceProperties.initializeDataSourceBuilder().build(); } //第二个ds2数据源配置 @Bean(name = "ds2DataSourceProperties") @ConfigurationProperties(prefix = "spring.datasource.ds2") public DataSourceProperties ds2DataSourceProperties() { return new DataSourceProperties(); } //第二个ds2数据源 @Bean("ds2DataSource") public DataSource ds2DataSource(@Qualifier("ds2DataSourceProperties") DataSourceProperties dataSourceProperties) { return dataSourceProperties.initializeDataSourceBuilder().build(); } }
2、整合Mybatis
2.1 简单整合
2.1.1 添加依赖
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.0.0</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.10</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.28</version> <scope>runtime</scope> </dependency>
2.1.2 配置数据源
spring.datasource.url=jdbc:mysql:///testdb?useUnicode=true&characterEncoding=utf-8 spring.datasource.username=root spring.datasource.password=root spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
2.1.3 创建Mapper （基于注解）
public interface UserMapper { @Select("select * from user") List<User> getAllUsers(); @Results({ @Result(property = "id", column = "id"), @Result(property = "username", column = "u"), @Result(property = "address", column = "a") }) @Select("select username as u,address as a,id as id from user where id=#{id}") User getUserById(Long id); @Select("select * from user where username like concat('%',#{name},'%')") List<User> getUsersByName(String name); @Insert({"insert into user(username,address) values(#{username},#{address})"}) @SelectKey(statement = "select last_insert_id()", keyProperty = "id", before = false, resultType = Integer.class) Integer addUser(User user); @Update("update user set username=#{username},address=#{address} where id=#{id}") Integer updateUserById(User user); @Delete("delete from user where id=#{id}") Integer deleteUserById(Integer id); }
2.1.4 创建Mapper (基于XML文件)
public interface UserMapper { List<User> getAllUser(); Integer addUser(User user); Integer updateUserById(User user); Integer deleteUserById(Integer id); }
<?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.boot.mybatis.mapper.UserMapper"> <select id="getAllUser" resultType="org.sang.mybatis.model.User"> select * from t_user; </select> <insert id="addUser" parameterType="org.sang.mybatis.model.User"> insert into user (username,address) values (#{username},#{address}); </insert> <update id="updateUserById" parameterType="org.sang.mybatis.model.User"> update user set username=#{username},address=#{address} where id=#{id} </update> <delete id="deleteUserById"> delete from user where id=#{id} </delete> </mapper>
2.1.5 Mapper配置文件位置
2.1.5.1 放到UserMapper类同级
必须配置资源插件，因为boot默认只会加载resources的目录文件
<build> <resources> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include> </includes> </resource> <resource> <directory>src/main/resources</directory> </resource> </resources> </build>
2.1.5.1 放到resources文件夹中指定的目录
在application.properties中告诉mybatis去哪里扫描mapper：
mybatis.mapper-locations=classpath:mapper/*.xml
2.2 逆向工程
2.2.1 添加逆向工程插件
<plugin> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-maven-plugin</artifactId> <version>1.4.0</version> <configuration> <configurationFile>src/main/resources/generatorConfig.xml</configurationFile> <verbose>true</verbose> <overwrite>true</overwrite> </configuration> </plugin>
2.2.2 指定逆向工程配置文件
<?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> <classPathEntry location="D:\installed\devtools\maven\repository\mysql\mysql-connector-java\5.1.38\mysql-connector-java-5.1.38.jar"/> <context id="mySQL" targetRuntime="MyBatis3"> <commentGenerator> <property name="suppressDate" value="true"/> <property name="suppressAllComments" value="true"/> </commentGenerator> <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://localhost:3306/2pc" userId="root" password="123"> </jdbcConnection> <javaTypeResolver> <property name="forceBigDecimals" value="false"/> </javaTypeResolver> <!--实体生成的配置--> <javaModelGenerator targetPackage="com.mmren.edu.domain" targetProject=".\src\main\java"> <property name="enableSubPackages" value="true"/> <property name="trimStrings" value="true"/> </javaModelGenerator> <!--mapper接口生成配置--> <sqlMapGenerator targetPackage="com.mmren.edu.mapper" targetProject=".\src\main\java"> <property name="enableSubPackages" value="true"/> </sqlMapGenerator> <!--mapper对应的映射文件生成配置--> <javaClientGenerator type="XMLMAPPER" targetPackage="com.mmren.edu.mapper" targetProject=".\src\main\java"> <property name="enableSubPackages" value="true"/> </javaClientGenerator> <!--表对应实体名称的配置--> <table tableName="t_order" domainObjectName="OrderInfo" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"/> </context> </generatorConfiguration>
生成实体，mapper，mapper配置文件
2.3 通用Mapper
通用Mapper就是为了解决单表增删改查，基于Mybatis的插件。开发人员不需要编写SQL，不需要在DAO中增加方法，只要写好实体类，就能支持相应的增删改查方法。
2.3.1 添加依赖
<dependency> <groupId>tk.mybatis</groupId> <artifactId>mapper-spring-boot-starter</artifactId> <version>2.1.5</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.38</version> </dependency>
2.3.2 添加实体
@Table(name="t_user") // 配置实体映射的表名 public class User implements Serializable { private static final long serialVersionUID = -37900582537861695L; @Id // 必须配置不配置，如果使用通过ID查询的时候会把所有字段当作条件进行查询 private Integer userId; private String userName; private Integer userAge; private Date userBirth; private Date createTime; private Date updateTime; public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public Integer getUserAge() { return userAge; } public void setUserAge(Integer userAge) { this.userAge = userAge; } public Date getUserBirth() { return userBirth; } public void setUserBirth(Date userBirth) { this.userBirth = userBirth; } public Date getCreateTime() { return createTime; } public void setCreateTime(Date createTime) { this.createTime = createTime; } public Date getUpdateTime() { return updateTime; } public void setUpdateTime(Date updateTime) { this.updateTime = updateTime; } }
2.3.3 配置Mapper
@Component @org.apache.ibatis.annotations.Mapper public interface UserMapper extends Mapper<User> { }
2.3.4 配置数据源
spring: datasource: driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql:///test1?useSSL=false username: root password: 123 mybatis: configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
2.3.5 编写测试用例
public class MapperTests extends DemoApplicationTests { @Autowired private UserMapper userMapper; ///////////////////////// 查询 测试 ///////////////////// // 测试根据ID查询，必须在注解字段标记@Id @Test public void testSelectById() throws Exception { User user = userMapper.selectByPrimaryKey(30001); convertToJson(user); } // 测试根据指定的列进行查询 @Test public void testSelectByColumns() throws Exception { // 创建查询条件对象 Example example = new Example(User.class); example.and().andEqualTo("userName","牧码人") .andEqualTo("updateTime","2020-04-21 23:42:13.0"); /* Example.Criteria criteria = example.createCriteria(); criteria.andEqualTo("userName","牧码人") .andEqualTo("updateTime","2020-04-21 23:42:13.0");*/ User user = userMapper.selectOneByExample(example); convertToJson(user); } // 测试模糊查询后排序 @Test public void testSelectByColumnsForLike() throws Exception { // 创建查询条件对象 Example example = new Example(User.class); example.and().andLike("userName","%人%") .andBetween("userAge",1,100); example.orderBy("userId").desc(); /* Example.Criteria criteria = example.createCriteria(); criteria.andEqualTo("userName","牧码人") .andEqualTo("updateTime","2020-04-21 23:42:13.0");*/ List<User> users = userMapper.selectByExample(example); convertToJson(users); } // 自定义SQL语句查询 @Test public void testSelectByCustomSql() throws Exception { // 创建查询条件对象 Example example = new Example(User.class); example.and().andLike("userName","%人%") .andBetween("userAge",1,100) // 自定义拼接的SQL .andCondition("create_time=","2020-04-22 00:13:58") // 自定义SQL语句 .andCondition("update_time='2020-04-22 00:13:58'"); example.orderBy("userId").desc(); List<User> users = userMapper.selectByExample(example); convertToJson(users); } // 分页查询(全部) @Test public void testSelectByPage() throws Exception { RowBounds rowBounds = new RowBounds(((1-1)*2),2); List<User> users = userMapper.selectByRowBounds(new User(), rowBounds); convertToJson(users); } // 分页查询(条件) @Test public void testSelectByPageForCondition() throws Exception { Example example = new Example(User.class); example.and().andLike("userName","%人%") .andBetween("userAge",1,100); example.orderBy("userId").desc(); RowBounds rowBounds = new RowBounds(((1-1)*2),2); List<User> users = userMapper.selectByExampleAndRowBounds(example, rowBounds); convertToJson(users); } ////////////////////////// 添加测试 //////////////////////// // 插入所有字段 @Value("${random.int[10000,99999]}") private Integer userId; @Test public void testInsert() throws Exception { User user = new User(); user.setUserAge(30); user.setUserBirth(new Date()); user.setUserId(40001); user.setUserName("Gerry"); int row = userMapper.insert(user); System.out.println(row > 0 ? "成功" : "失败"); } // 插入选择的字段 @Test public void testInsertSelective() throws Exception { User user = new User(); user.setUserAge(30); user.setUserBirth(new Date()); user.setUserId(userId); user.setUserName("Gerry"); int row = userMapper.insertSelective(user); System.out.println(row > 0 ? "成功" : "失败"); } ////////////////////////////// 更新操作 ////////////////////////// // 根据主键更新所有的字段 @Test public void testUpdateById() throws Exception { // 根据ID查询信息 User user = new User(); user.setUserId(71636); user.setUserName("王五11"); int row = userMapper.updateByPrimaryKey(user); System.out.println(row > 0 ? "成功" : "失败"); } // 根据主键更新指定的字段 @Test public void testUpdateSelectiveById() throws Exception { // 根据ID查询信息 User user = userMapper.selectByPrimaryKey(71636); user.setUserName("王五"); int row = userMapper.updateByPrimaryKeySelective(user); System.out.println(row > 0 ? "成功" : "失败"); } // 根据条件更新所有的字段 @Test public void testUpdateByExample() throws Exception { // 创建查询条件对象 Example example = new Example(User.class); example.and().andEqualTo("userName","牧码人") .andEqualTo("crateTime","2020-04-21 23:42:13"); int row = userMapper.updateByExample(new User(), example); System.out.println(row > 0 ? "成功" : "失败"); } // 根据条件更新指定的字段 @Test public void testUpdateSelectiveByExample() throws Exception { // 创建查询条件对象 Example example = new Example(User.class); example.and().andEqualTo("userName","张三") .andEqualTo("createTime","2020-04-21 23:41:37"); User user = new User(); user.setUserName("gerry new"); int row = userMapper.updateByExampleSelective(user, example); System.out.println(row > 0 ? "成功" : "失败"); } public void convertToJson(Object obj) { System.out.println("result===>"+JSON.toJSONString(obj, true)); } ///////////////////////////// 测试删除操作 // 根据主键删除记录 @Test public void testDeleteById() throws Exception { int row = userMapper.deleteByPrimaryKey(30001); System.out.println(row > 0 ? "成功" : "失败"); } // 指定条件删除 @Test public void testDeleteByExample() throws Exception { Example example = new Example(User.class); example.and().andIsNull("userAge"); int row = userMapper.deleteByExample(example); System.out.println(row > 0 ? "成功" : "失败"); } // 指定对象删除 @Test public void testDelete() throws Exception { User user = new User(); user.setUserName("gerry"); user.setUserId(32118); int row = userMapper.delete(user); System.out.println(row > 0 ? "成功" : "失败"); } }
2.4 Mybatis-Plus
MyBatis-Plus（简称 MP）是一个 MyBatis 的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。
特性
-
无侵入：只做增强不做改变，引入它不会对现有工程产生影响，如丝般顺滑；
-
损耗小：启动即会自动注入基本 CURD，性能基本无损耗，直接面向对象操作；
-
强大的 CRUD 操作：内置通用 Mapper、通用 Service，仅仅通过少量配置即可实现单表大部分 CRUD 操作，更有强大的条件构造器，满足各类使用需求；
-
支持 Lambda 形式调用：通过 Lambda 表达式，方便的编写各类查询条件，无需再担心字段写错；
-
支持主键自动生成：支持多达 4 种主键策略（内含分布式唯一 ID 生成器 - Sequence），可自由配置，完美解决主键问题；
-
支持 ActiveRecord 模式：支持 ActiveRecord 形式调用，实体类只需继承 Model 类即可进行强大的 CRUD 操作；
-
支持自定义全局通用操作：支持全局通用方法注入（ Write once, use anywhere ）；
-
内置代码生成器：采用代码或者 Maven 插件可快速生成 Mapper 、 Model 、 Service 、 Controller 层代码，支持模板引擎，更有超多自定义配置等您来使用；
-
内置分页插件：基于 MyBatis 物理分页，开发者无需关心具体操作，配置好插件之后，写分页等同于普通 List 查询；
-
分页插件支持多种数据库：支持 MySQL、MariaDB、Oracle、DB2、H2、HSQL、SQLite、Postgre、SQLServer 等多种数据库；
-
内置性能分析插件：可输出 Sql 语句以及其执行时间，建议开发测试时启用该功能，能快速揪出慢查询；
-
内置全局拦截插件：提供全表 delete 、 update 操作智能分析阻断，也可自定义拦截规则，预防误操作。
2.4.1 添加依赖
<!--mybatis-plus依赖--> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.3.1</version> </dependency>
2.4.2 添加实体
public class User implements Serializable { private static final long serialVersionUID = -37900582537861695L; private Integer userId; private String userName; private Integer userAge; private Date userBirth; private Date createTime; private Date updateTime; public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public Integer getUserAge() { return userAge; } public void setUserAge(Integer userAge) { this.userAge = userAge; } public Date getUserBirth() { return userBirth; } public void setUserBirth(Date userBirth) { this.userBirth = userBirth; } public Date getCreateTime() { return createTime; } public void setCreateTime(Date createTime) { this.createTime = createTime; } public Date getUpdateTime() { return updateTime; } public void setUpdateTime(Date updateTime) { this.updateTime = updateTime; } }
2.4.3 添加配置
spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql:///test1?useSSL=false&serverTimezone=GMT%2B8 username: root password: 123 mybatis-plus: configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl map-underscore-to-camel-case: true
2.4.4 配置Mapper
@Mapper @Component public interface UserMapper extends BaseMapper<User> { }
2.4.5 配置Service
public interface UserService extends IService<User> { }
@Service public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService { }
启动分页
@Configuration public class PageConfig { @Bean public PaginationInterceptor paginationInterceptor() { return new PaginationInterceptor(); } }
2.4.6 编写测试用例
public class MpApplicationTests extends DemoMpApplicationTests { @Autowired private UserService userService; @Value("${random.int[10000,99999]}") private Integer userId; @Test public void testSaveUser() throws Exception { User user = new User(); user.setUserId(userId); user.setUserName("MP测试3"); user.setUserAge(20); user.setUserBirth(new Date()); boolean save = userService.save(user); System.out.println(save ? "成功" : "失败"); } @Test public void testSelectAll() throws Exception { List<User> list = userService.list(); convertToJson(list); } @Test public void testSelectByCondition() throws Exception { QueryWrapper<User> userQueryWrapper = new QueryWrapper<>(); //userQueryWrapper.eq("user_name","MP测试1"); userQueryWrapper.lambda().eq(User::getUserName,"Map测试"); List<User> list = userService.list(userQueryWrapper); convertToJson(list); } @Test public void testSelectByPage() throws Exception { Page<User> userPage = new Page<>(1, 2); Page<User> page = userService .page(userPage,new QueryWrapper<User>().lambda().like(User::getUserName,"测试")); convertToJson(page); } public void convertToJson(Object obj) { System.out.println("result===>"+ JSON.toJSONString(obj, true)); } }
微信搜索“HUC思梦”关注我吧，关注有惊喜，不定时有免费资源分享！
