SpringBoot与数据访问

SpringBoot与数据访问

1. JDBC

1.1 创建SpringBoot项目

使用Spring Initializr创建SpringBoot项目，勾选Spring Web（Web）、JDBC、MySQL三项：

生成后pom.xml是这样的：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
    <exclusions>
        <exclusion>
            <groupId>org.junit.vintage</groupId>
            <artifactId>junit-vintage-engine</artifactId>
        </exclusion>
    </exclusions>
</dependency>

1.2 修改配置

修改配置文件，这里只配置了基础的几项属性，具体的属性可以在org.springframework.boot.autoconfigure.jdbc.DataSourceProperties（SpringBoot2以上的版本）

中查看，或者说编译器一般也会带自动带有相关提示。

src/main/resources/application.yml:

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/db_user?serverTimezone=UTC
    driver-class-name: com.mysql.jdbc.Driver

@ConfigurationProperties(
    prefix = "spring.datasource"
)
public class DataSourceProperties implements BeanClassLoaderAware, InitializingBean {
    private ClassLoader classLoader;
    private String name;
    private boolean generateUniqueName;
    private Class<? extends DataSource> type;
    private String driverClassName;
    private String url;
    private String username;
    private String password;
    private String jndiName;
    private DataSourceInitializationMode initializationMode;
    private String platform;
    private List<String> schema;
    private String schemaUsername;
    private String schemaPassword;
    private List<String> data;
    private String dataUsername;
    private String dataPassword;
    private boolean continueOnError;
    private String separator;
    private Charset sqlScriptEncoding;
    private EmbeddedDatabaseConnection embeddedDatabaseConnection;
    private DataSourceProperties.Xa xa;
    private String uniqueName;
    //...
}

1.3 测试

在测试类里注入数据源并测试：

@SpringBootTest
class TestjdbcApplicationTests {

    @Autowired
    DataSource dataSource;

    @Test
    void contextLoads() throws SQLException {
        System.out.println("********"+dataSource.getClass());
        Connection connection = dataSource.getConnection();
        System.out.println("********"+connection);
        connection.close();
    }

}

输出结果如下：

********class com.zaxxer.hikari.HikariDataSource
********HikariProxyConnection@256522893 wrapping com.mysql.cj.jdbc.ConnectionImpl@8d8f754

表明已经连接成功

结论：

• 默认是用com.zaxxer.hikari.HikariDataSource作为数据源；
• 数据源的相关配置都在DataSourceProperties里面；
• 使用时直接注入JdbcTemplate使用即可

2. 整合Druid数据源

2.1 导入依赖

使用Spring Initializr创建项目，勾选Spring Web、MySQL、jdbc三项

初始化后pom文件：

        <!-- web场景启动器 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- mysql驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--jdbcTemplate -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!-- druid数据库连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.26</version>
        </dependency>

2.2 在 src/main/resources 目录下创建 druid.properties 文件

#数据库设置
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/db_user?serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=123456
#--------------------------
# 下面为连接池的补充设置，应用到上面所有数据源中
# 初始化大小，最小，最大
spring.datasource.initialSize=5
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
# 打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.poolPreparedStatements=false
#spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计
spring.datasource.filters=slf4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
#spring.datasource.useGlobalDataSourceStat=true

• 驱动名称、url、用户名、密码需要根据自身使用的数据库以及数据库版本等情况进行更改

2.3为Druid数据源创建一个配置类

@Configuration可以使SpringBoot知道这是一个配置类

@PropertySource用于指定配置文件，若不指定则SpringBoot会从全局配置文件读取配置。

导入druid数据源
@Configuration
@PropertySource(value="classpath:druid.properties")
public 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;
    }
}

2.4写一个Controller来测试一下

@Controller
public class HelloController {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @GetMapping("/hello")
    @ResponseBody
    public List<Map<String,Object>> hello(){
        List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from tb_user");
        return list;
    }
}

访问 http://localhost:8080/hello,从数据库获取数据成功

访问 http://localhost:8080/druid/

输入上面配置的用户名与密码登录：

3. 整合MyBatis

3.1 前期工作

3.1.1导入依赖

勾选web、mybatis和mysql几项，生成pom如下：

	    <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.1.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

3.1.2配置数据源相关属性（参考1或2）

驱动类名称、用户名、密码、url等几项基本属性必须配置

3.1.3 给数据库建表

CREATE DATABASE db_user;
SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `tel` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES ('伟大的开发者', '123456');
INSERT INTO `tb_user` VALUES ('二钊', '164110');

3.1.4 创建JavaBean

public class User {
    String name;
    String tel;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getTel() {
        return tel;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    public User(){

    }

    public User(String name, String tel) {
        this.name = name;
        this.tel = tel;
    }
}

3.2 注解版

使用注解编写sql,这里需要注意的是不要漏了@Mapper，否则无法加入Spring容器。当Mapper文件多的时候，每一个Mapper都需要加入@Mapper注解会很麻烦，所以可以在SpringBoot主类上加上@MapperScan(包名)注解，开启包扫描。

@Mapper
public interface UserMapper {

    @Select("select * from tb_user")
    public List<User> getAllUsers();

    @Select("select * from tb_user where name=#{name}")
    public User getUserByName(String name);

    @Delete("delete from tb_user where name=#{name}")
    public int deleteUserByName(String name);

    @Update("update tb_user set tel=#{tel} where name=#{name}")
    public int updateUser(User user);

    @Insert("insert into tb_user values(#{name},#{tel})")
    public int insertUser(User user);
}

根据实际需要自定义MyBatis的配置规则：给容器中添加一个ConfigurationCustomizer；

@org.springframework.context.annotation.Configuration
public class MyBatisConfig {

    @Bean
    public ConfigurationCustomizer configurationCustomizer(){
        return new ConfigurationCustomizer(){

            @Override
            public void customize(Configuration configuration) {
                configuration.setMapUnderscoreToCamelCase(true);
            }
        };
    }
}

3.3 配置文件版

在全局配置文件里指定mybatis的配置文件与sql映射文件的位置，其他的按照原mybatis规则去做就ok了

mybatis:
  config-location: classpath:mybatis/mybatis-config.xml 指定全局配置文件的位置
  mapper-locations: classpath:mybatis/mapper/*.xml  指定sql映射文件的位置

更多使用参照

http://www.mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/

3.4 测试

@Controller
public class UserController {

    @Autowired
    private UserMapper userMapper;

    @GetMapping("/users")
    @ResponseBody
    public List<User> users(){
        return userMapper.getAllUsers();
    }

    @GetMapping("/user/{name}")
    @ResponseBody
    public User user(@PathVariable("name")String name){
        return userMapper.getUserByName(name);
    }

    @RequestMapping("/user/del/{name}")
    @ResponseBody
    public int deluser(@PathVariable("name")String name){
        return userMapper.deleteUserByName(name);
    }

    @RequestMapping("/user/update")
    @ResponseBody
    public int updateUser(User user){
        return userMapper.updateUser(user);
    }

    @RequestMapping("/user/add")
    @ResponseBody
    public int addUser(User user){
        return userMapper.insertUser(user);
    }
}