springboot配置druid数据源+整合jdbcTemplate+整合myBatis

里面的例子仅供参考，不能生搬硬套哦！！！
文件夹：

1. 导入依赖

<dependency>
   <groupId>org.mybatis.spring.boot</groupId>
   <artifactId>mybatis-spring-boot-starter</artifactId>
   <version>1.3.1</version>
</dependency>

2. 配置druid数据源

2.1 导入依赖

<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
   <groupId>org.apache.commons</groupId>
   <artifactId>commons-dbcp2</artifactId>
</dependency>

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <scope>runtime</scope>
</dependency>
<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid</artifactId>
   <version>1.0.9</version>
</dependency>
<dependency>
   <groupId>org.mybatis.spring.boot</groupId>
   <artifactId>mybatis-spring-boot-starter</artifactId>
   <version>1.3.1</version>
</dependency>

2.2 配置application.yml文件

spring:
# 配置数据源信息
  datasource:
# 配置数据源类型
    type: com.zaxxer.hikari.HikariDataSource
# 配置连接数据库信息
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test
    username: root
    password: ****

在默认情况下， 数据库连接可以使用DataSource池进行自动配置

• 如果Hikari可用， Springboot将使用它。
• 如果Commons DBCP2可用， 我们将使用它。

我们可以自己指定数据源配置，通过type来选取使用哪种数据源

修改spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/boot_demo
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

创建数据源注册类

@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource dataSource(){
        return new DruidDataSource();
    }
}

以下可以不做
配置druid运行期监控

@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource dataSource(){
        return new DruidDataSource();
    }

    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(),
                "/druid/*");
        Map<String,String> initParams = new HashMap<>();
        initParams.put("loginUsername","root");
        initParams.put("loginPassword","root");
        initParams.put("allow","");//默认就是允许所有访问
        initParams.put("deny","192.168.15.21");
        bean.setInitParameters(initParams);
        return bean;
    }

    //2、配置一个web监控的filter
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean;
        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;
    }
}

打开监控页面
http://localhost:8080/druid

3. 建表

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tx_user
-- ----------------------------
DROP TABLE IF EXISTS `tx_user`;
CREATE TABLE `tx_user` (
  `username` varchar(10) DEFAULT NULL,
  `userId` int(10) NOT NULL,
  `password` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建Controller

@Controller
public class TestController {
    @Autowired
    JdbcTemplate jdbcTemplate;

    @ResponseBody
    @RequestMapping("/query")
    public List<Map<String, Object>> query(){
        List<Map<String, Object>> maps = jdbcTemplate.queryForList("SELECT * FROM tx_user");
        return maps;
    }

}

运行访问:：

4. 编写实体类

举例子
创建实体类

public class TxPerson {

    private int pid;

    private String pname;

    private String addr;

    private int gender;

    private Date birth;

}

创建mapper

@Mapper
public interface TxPersonMapper {

    @Select("select * from tx_person")
    public List<TxPerson> getPersons();


    @Select("select * from tx_person t where t.pid = #{id}")
    public TxPerson getPersonById(int id);

    @Options(useGeneratedKeys =true, keyProperty = "pid")
    @Insert("insert into tx_person(pid, pname, addr,gender, birth)" +
            " values(#{pid}, #{pname}, #{addr},#{gender}, #{birth})")
    public void insert(TxPerson person);

    @Delete("delete from tx_person where pid = #{id}")
    public void update(int id);

}

5. 测试

编写测试类

@RunWith(SpringRunner.class)
@SpringBootTest
public class TestSQL {
    @Autowired
    UserxMapper userxMapper;
    @Autowired
    ApplicationContext context;

    @Test
    public void contextloads() throws SQLException {
        DataSource bean = (DataSource) context.getBean("dataSource");
        System.out.println(bean);
    }

    @Test
    public void testMybatis(){
        Userx u = userxMapper.getPersonById(1);
        System.out.println(u);
    }


}

第一个测试：

第二个：

6. 创建sqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
</configuration>

创建映射文件

<?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.springbootdaily2.mapper.UserxMapper">
    <insert id="insert">
        insert into userx(id, username, birthday,sex, address) values(#{id}, #{username}, #{birthday},#{sex}, #{address})
    </insert>
    <delete id="deleteById" parameterType="integer">
        delete from userx where id = #{id}
    </delete>

    <select id="getPersons" resultType="com.example.springbootdaily2.model.Userx">
        select * from userx
    </select>
    <select id="getPersonById" resultType="com.example.springbootdaily2.model.Userx" parameterType="integer">
        select * from userx t where t.id = #{id}
    </select>
</mapper>

在application.yaml中配置mybatis的信息

mybatis:
  config-location: classpath:sqlMapConfig.xml
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.example.springbootdaily2.model

测试：

@Test
  public void getPerson(){
      List<Userx> userxList = userxMapper.getPersons();
      for(int i = 0; i < userxList.size();i++){
          System.out.println(userxList.get(i));
      }
  }

运行结果：

Userx{id=1, username='老王', birthday=Tue Feb 27 17:47:08 CST 2018, sex=男, address='北京'}
Userx{id=2, username='熊大', birthday=Fri Mar 02 15:09:37 CST 2018, sex=女, address='上海'}
Userx{id=3, username='熊二', birthday=Sun Mar 04 11:34:34 CST 2018, sex=女, address='深圳'}

7. 心得

springboot真的好用，基本上啥也不用，真他喵的好用！！！但是最重要的是要理解底层逻辑。