纵有疾风起
人生不言弃

Spring Boot+mybatis集成数据库访问


1、整合druid数据源

1.1 单数据源配置

<dependency>   <groupId>com.alibaba</groupId>   <artifactId>druid-spring-boot-starter</artifactId>   <version>1.1.10</version></dependency>
@Configurationpublic 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 # 密码
 
/** * 多数据源配置 */@Configurationpublic 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-8spring.datasource.username=rootspring.datasource.password=rootspring.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>

Spring Boot+mybatis集成数据库访问插图

 

 

生成实体,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.Mapperpublic 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: 123mybatis:  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: 123mybatis-plus:  configuration:    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl    map-underscore-to-camel-case: true

 

2.4.4 配置Mapper
@Mapper@Componentpublic interface UserMapper extends BaseMapper<User> {    } 

 

2.4.5 配置Service
public interface UserService extends IService<User> {​}
 
@Servicepublic class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {​}
 

启动分页

@Configurationpublic 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));    }}

 

文章转载于:https://www.cnblogs.com/lm970585581/p/12804497.html

原著是一个有趣的人,若有侵权,请通知删除

未经允许不得转载:起风网 » Spring Boot+mybatis集成数据库访问

分享到: 生成海报
avatar

评论 抢沙发

评论前必须登录!

立即登录   注册

切换注册

登录

忘记密码 ?

切换登录

注册

我们将发送一封验证邮件至你的邮箱, 请正确填写以完成账号注册和激活