springboot+mybatis+druid整合笔记

逃离我推掉我的手 2022-08-31 07:27 162阅读 0赞

> 原创不易，转载请注明出处

### 文章目录 ###

*   *   *  1.创建springboot项目
         *  2.整合mybatis与druid
         *   *  2.1依赖
             *  2.2 配置
         *  3.写一个demo试试
         *   *  3.1 表结构与实体
             *  3.2 mapper.xml编写
             *  3.3 dao编写
             *  3.4 service与controller编写
             *  3.4 测试
         *  4. druid starter整合
         *   *  4.1 druid starter
             *  4.2 配置

--------------------

### 1.创建springboot项目 ###

创建springboot项目不用多说，一般就用idea自带的spring initializer创建，当然你也可以去`start.spring.io` spring官网创建好，将项目下载下来导入到idea中（一般不这么干）  
我们这里就用idea自带的spring initializer创建，勾勾看看选择哪个starter依赖，我这里就选了个web的starter。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l1YW5zaGFuZ3NoZW5naHVv_size_16_color_FFFFFF_t_70]  
这里就不详细介绍了，就是选starter，下一步的事情。

### 2.整合mybatis与druid ###

#### 2.1依赖 ####

整合mybatis与druid需要 mybatis的一个starter依赖，druid依赖，mysql驱动依赖，jdbc依赖，这些就够了  
我这里把需要的依赖信息列出来

<dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
       <groupId>org.mybatis.spring.boot</groupId>
       <artifactId>mybatis-spring-boot-starter</artifactId>
       <version>2.1.4</version>
    </dependency>
    <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <scope>runtime</scope>
    </dependency>
    <dependency>
       <groupId>com.alibaba</groupId>
       <artifactId>druid</artifactId>
       <version>1.1.6</version>
    </dependency>
    <dependency>
       <groupId>ognl</groupId>
       <artifactId>ognl</artifactId>
       <version>3.2.15</version>
       <scope>compile</scope>
       <optional>true</optional>
    </dependency>
    <dependency>
       <groupId>org.javassist</groupId>
       <artifactId>javassist</artifactId>
       <version>3.27.0-GA</version>
       <scope>compile</scope>
       <optional>true</optional>
    </dependency>

我这里springboot版本是最新版（2.5.2）。

#### 2.2 配置 ####

我这里mybatis的配置就一项，告诉系统我的mapper.xml在哪个位置

mybatis.mapper-locations=classpath:mapper/*.xml

数据源配置

spring.datasource.type= com.alibaba.druid.pool.DruidDataSource
    spring.datasource.url= jdbc:mysql://127.0.0.1:3306/mybatis_test?useUnicode=true&characterEncoding=utf-8
    spring.datasource.username= root
    spring.datasource.password= root
    spring.datasource.driverClassName= com.mysql.jdbc.Driver
    spring.datasource.initialSize= 1
    spring.datasource.minIdle= 1
    spring.datasource.maxActive= 50
    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  
    spring.datasource.poolPreparedStatements= true 
    spring.datasource.maxPoolPreparedStatementPerConnectionSize= 20
    spring.datasource.filters= stat,wall,log4j
    spring.datasource.connectionProperties= druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

这里整合数据源的话，由于没有用druid的starter，需要自己手动创建下，也就是整个配置类

@Configuration
    public class DruidDataSourceConfig {   
       
    	/** * druid数据库连接池配置 */
        @Value("${spring.datasource.url}")
        private String dbUrl;  
        @Value("${spring.datasource.username}")
        private String username;  
        @Value("${spring.datasource.password}")
        private String password;  
        @Value("${spring.datasource.driverClassName}")
        private String driverClassName;  
        @Value("${spring.datasource.initialSize}")
        private int initialSize;  
        @Value("${spring.datasource.minIdle}")
        private int minIdle;  
        @Value("${spring.datasource.maxActive}")
        private int maxActive;  
        @Value("${spring.datasource.maxWait}")
        private int maxWait;  
        @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
        private int timeBetweenEvictionRunsMillis;  
        @Value("${spring.datasource.minEvictableIdleTimeMillis}")
        private int minEvictableIdleTimeMillis;  
        @Value("${spring.datasource.validationQuery}")
        private String validationQuery;  
        @Value("${spring.datasource.testWhileIdle}")
        private boolean testWhileIdle;  
        @Value("${spring.datasource.testOnBorrow}")
        private boolean testOnBorrow;  
        @Value("${spring.datasource.testOnReturn}")
        private boolean testOnReturn;  
        @Value("${spring.datasource.poolPreparedStatements}")
        private boolean poolPreparedStatements;  
        @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
        private int maxPoolPreparedStatementPerConnectionSize;  
        @Value("${spring.datasource.filters}")
        private String filters;  
        @Value("{spring.datasource.connectionProperties}")
        private String connectionProperties;  
        
        /** * 创建druid数据库连接池bean * @return */
        @Bean
        @Primary
        public DataSource dataSource(){   
            DruidDataSource datasource = new DruidDataSource();  
            datasource.setUrl(this.dbUrl);  
            datasource.setUsername(username);  
            datasource.setPassword(password);  
            datasource.setDriverClassName(driverClassName);  
            datasource.setInitialSize(initialSize);  
            datasource.setMinIdle(minIdle);  
            datasource.setMaxActive(maxActive);  
            datasource.setMaxWait(maxWait);          
            datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);  
            datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);  
            datasource.setValidationQuery(validationQuery);  
            datasource.setTestWhileIdle(testWhileIdle);  
            datasource.setTestOnBorrow(testOnBorrow);  
            datasource.setTestOnReturn(testOnReturn);  
            datasource.setPoolPreparedStatements(poolPreparedStatements);  
            datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);  
            
            try {   
                datasource.setFilters(filters);  
            } catch (SQLException e) {   
                e.printStackTrace();
            }  
            
            datasource.setConnectionProperties(connectionProperties);  
              
            return datasource;  
        }
        
    }

### 3.写一个demo试试 ###

这里我们就写个demo来试试效果，随便整个user表

#### 3.1 表结构与实体 ####

CREATE TABLE `user` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `username` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

public class UserEntity implements Serializable { 
        private Integer id;
        private String userName;
        private String password;
        public Integer getId() { 
            return id;
        }
        public void setId(Integer id) { 
            this.id = id;
        }
        public String getUserName() { 
            return userName;
        }
        public void setUserName(String userName) { 
            this.userName = userName;
        }
        public String getPassword() { 
            return password;
        }
        public void setPassword(String password) { 
            this.password = password;
        }
    }

#### 3.2 mapper.xml编写 ####

UserMapper.xml 文件。放到resource目录下的mapper目录下， 正好对应我们上面配置的mapper 文件位置

<?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.example.testcanal.dao.UserDao">
        <sql id="userField">
            id,username,password
        </sql>
        <insert id="addUser" useGeneratedKeys="true" keyProperty="id">
            insert into user  (username,password)  values (#{user.userName},#{user.password})
        </insert>
    </mapper>

#### 3.3 dao编写 ####

@Mapper
    public interface UserDao { 
        boolean addUser(@Param("user") UserEntity userEntity);
    }

很简单，主要的是要@Mapper注解，不然的话不知道你是个mapper接口，也就没法给你创建代理类。

#### 3.4 service与controller编写 ####

service编写：

public interface UserService { 
        boolean add(UserEntity userEntity);
    }
    
    @Service
    public class UserServiceImpl implements UserService { 
    
        @Resource
        private UserDao userDao;
        @Override
        @Transactional
        public boolean add(UserEntity userEntity) { 
            return userDao.addUser(userEntity);
        }
    }

controller编写

@RestController
    @RequestMapping("/test")
    public class TestController { 
        @Autowired
        private UserService userService;
        @GetMapping("/user/add")
        public UserEntity add( Integer id){ 
            UserEntity userEntity=new UserEntity();
            userEntity.setUserName("xxxx");
            userEntity.setPassword("pass");
            userService.add(userEntity);
            return userEntity;
        }
    }

#### 3.4 测试 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l1YW5zaGFuZ3NoZW5naHVv_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l1YW5zaGFuZ3NoZW5naHVv_size_16_color_FFFFFF_t_70 2]  
可以看到通了，到这里就完事了

### 4. druid starter整合 ###

上面我们在整合druid的时候，就是用了druid的普通依赖，这样有个问题就是需要我们手动写配置类进行初始化数据源。  
这里我们使用druid starter来整合下，在上面基础上， 完成两步就行，1是将druid依赖换成starter依赖，2是去掉那个配置类，修改下配置文件即可

#### 4.1 druid starter ####

这里我将druid 依赖去掉了，然后加入starter依赖

<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.10</version>
    </dependency>

#### 4.2 配置 ####

application.properties里面的

这段是不用动的，唯一动的就是druid的一些个性化配置

spring.datasource.druid.initial-size=5
    spring.datasource.druid.min-idle=5
    spring.datasource.druid.maxActive=20
    spring.datasource.druid.maxWait=60000
    spring.datasource.druid.timeBetweenEvictionRunsMillis=60000
    spring.datasource.druid.minEvictableIdleTimeMillis=300000
    spring.datasource.druid.validationQuery=SELECT 1 FROM DUAL

就像这个样子， spring.datasource.druid开头的，更多的配置项及说明请看官网文档，超级详细，配置详细地址：  
https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l1YW5zaGFuZ3NoZW5naHVv_size_16_color_FFFFFF_t_70]: /images/20220829/3463f5a507e34cf79b6d8dae6350a3b7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l1YW5zaGFuZ3NoZW5naHVv_size_16_color_FFFFFF_t_70 1]: /images/20220829/51f18fb27fd345d7b1756286010ea475.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3l1YW5zaGFuZ3NoZW5naHVv_size_16_color_FFFFFF_t_70 2]: /images/20220829/44ec6d2796ad4f12a18d8f6649036db0.png