springboot整合数据层

一、数据源自动管理

1.引入jdbc的依赖和springboot的应用场景

<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>
    <version>5.1.26</version>
    <scope>runtime</scope>
</dependency>

2.使用yaml配置

数据库连接的参数不再放在xml配置文件里了，而是放在yaml配置文件里

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

(1)如果Hikari可用， Springboot将使用它。

(2)如果Commons DBCP2可用， 我们将使用它。

spring:
  datasource:
    username: root
    password: 2020
    url: jdbc:mysql://localhost:3306/springboot01
    driver-class-name: com.mysql.jdbc.Driver
    type: com.zaxxer.hikari.HikariDataSource # 数据库连接池的类型，现在用的是springboot自带的默认的连接池
    # type: org.apache.commons.dbcp2.BasicDataSource   # DBCP2

二、配置druid数据源

1.引入druid的依赖

使用druid要引入druid的依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.0.9</version>
</dependency>
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.15</version>
</dependency>

2.修改连接池类型

type: com.alibaba.druid.pool.DruidDataSource

3.创建数据源注册类

yaml配置文件里只是指定了用户名、密码、url，要把它配置给一个datasource对象，创建一个配置类Configuration，放在启动类同包或其子包下，@Bean:返回一个对象交给spring去管理，@ConfigurationProperties:设置他的前缀是spring.datasource

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
public class DruidConfig {

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

4.配置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;
    }
}

这步一直报红：

因为没有引入web的依赖：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

引入之后就不报红了

5.打开监控页面

http://localhost:8080/druid

输入用户名(root)和密码(root)后可以看到一个包含详细连接池信息和 SQL 查询统计的界面

三、springboot整合jdbcTemplate

1.在数据源建表

SET FOREIGN_KEY_CHECKS=0;

 

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

-- Table structure for tx_user

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

DROP TABLE IF EXISTS `tx_user`;

CREATE TABLE `tx_user` (
  `userId` int(11) DEFAULT NULL,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.controller

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.List;
import java.util.Map;

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

3.启动springboot

启动springboot，访问http://localhost:8080/query

四、springboot整合mybatis注解版

1.导入mybatis的starter

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

2.步骤

(1)配置数据源相关属性

(2)数据库建表

(3)创建JavaBean

import java.util.Date;

public class TxPerson {
    private int pid;

    private String pname;

    private String addr;

    private int gender;

    private Date birth;

    public TxPerson() {
    }

    public TxPerson(int pid, String pname, String addr, int gender, Date birth) {
        this.pid = pid;
        this.pname = pname;
        this.addr = addr;
        this.gender = gender;
        this.birth = birth;
    }

    public int getPid() {
        return pid;
    }

    public void setPid(int pid) {
        this.pid = pid;
    }

    public String getPname() {
        return pname;
    }

    public void setPname(String pname) {
        this.pname = pname;
    }

    public String getAddr() {
        return addr;
    }

    public void setAddr(String addr) {
        this.addr = addr;
    }

    public int getGender() {
        return gender;
    }

    public void setGender(int gender) {
        this.gender = gender;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    @Override
    public String toString() {
        return "TxPerson{" +
                "pid=" + pid +
                ", pname='" + pname + '\'' +
                ", addr='" + addr + '\'' +
                ", gender=" + gender +
                ", birth=" + birth +
                '}';
    }
}

(4)创建mapper

这里使用了@Mapper注解

import com.qcby.springboot08.model.TxPerson;
import org.apache.ibatis.annotations.*;

import java.util.List;

@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)测试类

@Autowired
TxPersonMapper txPersonMapper;

/*
* 根据id查询
* */
@Test
public void getPersonById(){
    TxPerson person = txPersonMapper.getPersonById(1);
    System.out.println(person);
}

运行：

(6)解决驼峰模式和数据库中下划线不能映射的问题

这样运行出来pAddr为null

开启下划线到驼峰命名的自动映射

import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MybatisConfig {

    @Bean
    public ConfigurationCustomizer getCustomizer(){
        return new ConfigurationCustomizer() {
            @Override
            public void customize(org.apache.ibatis.session.Configuration configuration) {
                // 开启下划线到驼峰命名的自动映射
                configuration.setMapUnderscoreToCamelCase(true);
            }
        };
    }
}

再运行：

解决了！

(7)可以在mybatis的接口上不加@Mapper注解，通过扫描器注解来扫描

刚才是在mapper接口上加了@Mapper注解，也可以不加，通过扫描器注解来扫描

这样也可以运行成功：

五、springboot整合mybatis配置文件

如果不想用注解形式，可以用配置文件方式

1.创建映射文件PersonMapper.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.qcby.springboot08.mapper.TxPersonMapper">
    <select id="getPersons" resultType="TxPerson">
        select * from tx_person
    </select>
</mapper>

2.在application.yaml中配置mybatis的信息

mybatis:
  mapper-locations: classpath:mapper/*.xml #对应mapper映射xml文件所在路径
  type-aliases-package: com.qcby.springboot08.model #对应实体类路径

3.测试类

/*
* 查询所有
* */
@Test
public void getPersons(){
    List<TxPerson> persons = txPersonMapper.getPersons();
    for(TxPerson person : persons){
        System.out.println(person);
    }
}

运行：