springbot数据层开发

1 Springboot数据层开发

1.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>
   <scope>runtime</scope>
</dependency>

让我们使用yaml方式配置，创建application.yaml

spring:
  datasource:
    username: root
    password: 2020
    url: jdbc:mysql://localhost:3306/boot_demo
    driver-class-name: com.mysql.jdbc.Driver
    type: com.zaxxer.hikari.HikariDataSource

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

· 如果Hikari可用， Springboot将使用它。

· 如果Commons DBCP2可用， 我们将使用它。

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

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/boot_demo
    driver-class-name: com.mysql.jdbc.Driver
    type: com.zaxxer.hikari.HikariDataSource
   # type: org.apache.commons.dbcp2.BasicDataSource

1.2配置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>

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

在application.yaml中加入

spring:
  datasource:
    username: root
    password: 2020
    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

1.3springboot整合jdbcTemplate

在数据源建表

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

}

启动springboot访问

http://localhost:8080/query

Springboot中提供了JdbcTemplateAutoConfiguration的自动配置

org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration,

JdbcTemplateAutoConfiguration源码：

1.4Springboot整合mybatis注解版

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

步骤：

1）、配置数据源相关属性（见上一节Druid）

2）、给数据库建表

3）、创建JavaBean

public class TxPerson {


    private int pid;

    private String pname;

    private String addr;

    private int gender;

    private Date birth;

4）创建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);

}

单元测试

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

@Configuration
public class MybatisConfig {

    @Bean
    public ConfigurationCustomizer getCustomizer(){
        return new ConfigurationCustomizer() {
            @Override
            public void customize(org.apache.ibatis.session.Configuration configuration) {
                configuration.setMapUnderscoreToCamelCase(true);
            }
        };
    }
}

查询结果

我们同样可以在mybatis的接口上不加@Mapper注解，通过扫描器注解来扫描

Mapper接口存放在cn.tx.mapper下

1.5 Springboot整合mybatis配置文件

创建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>

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

在application.yaml中配置mybatis的信息

mybatis:
  config-location: classpath:mybatis/sqlMapConfig.xml
  mapper-locations: classpath:mybatis/mapper/*.xml
  type-aliases-package: cn.tx.springboot.jdbc_demo1