SpringBoot-整合数据库

发布时间：2020-12-15 01:16:23 所属栏目：大数据来源：网络整理

导读：Data 目录 Data 1. 配置文件 2. 测试使用 3. CRUD 4. Druid 1. 导入依赖 2. 指定数据源并配置Druid 3. 绑定Druid到spring.datasource 4. 启用后台监控 5. 设置后台监控的Filter 5. MyBatis 1. 导入依赖 2. 编写实体类 3. 编写Dao层 4. 整合Mybatis 5. 编写XX

Data

Data
- 1. 配置文件
- 2. 测试使用
- 3. CRUD
- 4. Druid
  - 1. 导入依赖
  - 2. 指定数据源并配置Druid
  - 3. 绑定Druid到spring.datasource
  - 4. 启用后台监控
  - 5. 设置后台监控的Filter
- 5. MyBatis
  - 1. 导入依赖
  - 2. 编写实体类
  - 3. 编写Dao层
  - 4. 整合Mybatis
  - 5. 编写XXXMapper.xml
  - 6. 编写controller

1. 配置文件

新建项目时,选择mysql和jdbc Driver,导入依赖

在配置文件中配置用户名,密码,url,以及Driver

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    driver-class-name: com.mysql.cj.jdbc.Driver

注意,由于SpringBoot中使用的mysql为8.0+ 版本,要求url中必须带有时区信息

serverTimezone=Asia/Shanghai

2. 测试使用

package com.wang;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

@SpringBootTest
class Springboot04DataApplicationTests {

    //只要把DataSource自动装配,我们就可以使用了
    @Autowired
    DataSource dataSource;

    @Test
    void contextLoads() throws SQLException {
        //查看默认的数据源 : hikari.HikariDataSource
        System.out.println(dataSource.getClass());

        //获得数据库连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        
        //关闭数据库连接
        connection.close();
    }

}

注意

使用自动装配绑定DataSource
利用getConnection方法调用jdbc
默认的数据源为 hikari.HikariDataSource

3. CRUD

package com.wang.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

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

@RestController
public class jdbcController {

    //XXXX Template : SpringBoot已经配置好的模板bean,拿来即用,封装好了CRUD
    //jdbc
    //redis
    @Autowired
    JdbcTemplate jdbcTemplate;

    //查询数据库的所有信息
    //没有实体类,数据库中的东西怎样获取? ==> Map
    @GetMapping("/userList")
    public List<Map<String,Object>> userList() {
        String sql = "select * from user";
        List<Map<String,Object>> list_maps = jdbcTemplate.queryForList(sql);
        return list_maps;
    }

    @GetMapping("/addUser")
    public String addUser() {
        String sql = "insert into mybatis.user(id,name,pwd) values(5,'小明','123456')";
        int update = jdbcTemplate.update(sql);
        if (update == 1) {
            return "addUser-OK";
        } else {
            return "addUser-Fail";
        }
    }

    //拼接SQL,用RestFul风格传参,要用@PathVariable注解
    @GetMapping("/updateUser/{id}")
    public String updateUser(@PathVariable("id") int id) {
        String sql = "update mybatis.user set name = ?,pwd = ? where id=" + id;

        //封装
        Object[] objects = new Object[2];
        objects[0] = "小明2";
        objects[1] = "1233211234567";

        //jdbcTemplate中的update重载了prepareStatement,直接传需要的对象即可
        int update = jdbcTemplate.update(sql,objects);
        if (update == 1) {
            return "updateUser-OK";
        } else {
            return "updateUser-Fail";
        }
    }

    @GetMapping("/deleteUser/{id}")
    public String deleteUser(@PathVariable("id") int id) {
        String sql = "delete from mybatis.user where id = ?";

        //jdbcTemplate中的update重载了prepareStatement,直接传需要的对象即可
        //int也是object,直接传入即可
        int update = jdbcTemplate.update(sql,id);
        if (update == 1) {
            return "deleteUser-OK";
        } else {
            return "deleteUser-Fail";
        }
    }

}

注意

XXXX Template : SpringBoot已经配置好的模板bean,封装好了CRUD
除了查询,都用update语句
jdbcTemplate中的update重载了prepareStatement,直接传需要的对象即可
查询用query
没有实体类,数据库中的东西怎样获取? ==> Map ==> List中存放Map,Map中以K-V形式储存数据库中的字段名和值,字段名为String类型,值为Object类型

4. Druid

1. 导入依赖

<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.24</version>
</dependency>

2. 指定数据源并配置Druid

在配置文件中指定type并配置Druid

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource

    #Spring Boot 默认是不注入这些属性值的，需要自己绑定
    #druid 数据源专有配置
    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:监控统计、log4j：日志记录、wall：防御sql注入
    #如果允许时报错  java.lang.ClassNotFoundException: org.apache.log4j.Priority
    #则导入 log4j 依赖即可，Maven 地址：https://mvnrepository.com/artifact/log4j/log4j
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

3. 绑定Druid到spring.datasource

package com.wang.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.util.HashMap;

@Configuration
public class DruidConfig {

    //将自定义的Bean与spring.datasource绑定
    @ConfigurationProperties(prefix = "spring.datasource")
    //将DruidDataSource注册到Bean
    @Bean
    public DataSource druidDataSource() {
        return new DruidDataSource();
    }
}

注意

@ConfigurationProperties 绑定配置,后面可以指定前缀

4. 启用后台监控

package com.wang.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.util.HashMap;

@Configuration
public class DruidConfig {

    //将自定义的Bean与spring.datasource绑定
    @ConfigurationProperties(prefix = "spring.datasource")
    //将DruidDataSource注册到Bean
    @Bean
    public DataSource druidDataSource() {
        return new DruidDataSource();
    }

    //后台监控,访问 /druid 即可,固定写法的代码
    //因为SpringBoot 内置了servlet容器,所以没有web.xml,替代方法: ServletRegistrationBean
    @Bean
    public ServletRegistrationBean statViewServlet() {
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(),"/druid/*");

        //后台需要有人登录,账号密码配置
        HashMap<String,String> initParameters = new HashMap<>();
        //增加配置
        //登录的key 是固定的 loginUsername loginPassword
        initParameters.put("loginUsername","admin");
        initParameters.put("loginPassword","123456");

        //允许谁可以访问,值为空则所有人都可以访问
        initParameters.put("allow","");

        //IP白名单
        //initParameters.put("allow","192.168.1.12,127.0.0.1");
        //IP黑名单
        //initParameters.put("deny","192.168.4.23");
        //是否能够重置数据
        initParameters.put("resetEnable","false");

        bean.setInitParameters(initParameters);   //设置初始化参数

        return bean;
    }

}

key如下

public static final String SESSION_USER_KEY    = "druid-user";
public static final String PARAM_NAME_USERNAME = "loginUsername";
public static final String PARAM_NAME_PASSWORD = "loginPassword";
public static final String PARAM_NAME_ALLOW    = "allow";
public static final String PARAM_NAME_DENY     = "deny";
public static final String PARAM_REMOTE_ADDR   = "remoteAddress";

使用log4j,要在resources下配置log4j.properties

#将等级为DEBUG的日志信息输出到console和file这两个目的地，console和file的定义在下面的代码
log4j.rootLogger=DEBUG,console,file

#控制台输出的相关设置
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

#文件输出的相关设置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/wang.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

注意

JavaConfig中使用方法进行配置后,一定要注册Bean
通过 ServletRegistrationBean bean = new ServletRegistrationBean<>(new StatViewServlet(),"/druid/*"); 配置stat并设置路径
我们要将stat注册到web,xml,因为SpringBoot 内置了servlet容器,替代方法: ServletRegistrationBean
以键值对的方式(map)放到setInitParameters方法中
key是固定的,不要乱写

5. 设置后台监控的Filter

//filter
//我们要注册filter,同样的,使用 FilterRegistrationBean 注册
@Bean
public FilterRegistrationBean webStatFilter() {
    FilterRegistrationBean bean = new FilterRegistrationBean();

    bean.setFilter(new WebStatFilter());

    //可以过滤哪些请求
    HashMap<String,String> initParameters = new HashMap<>();

    //这些东西不进行统计
    initParameters.put("exclusions","*.js,*.css,/druid/*");

    bean.setInitParameters(initParameters);

    return bean;
}

key如下

public final static String PARAM_NAME_PROFILE_ENABLE         = "profileEnable";
public final static String PARAM_NAME_SESSION_STAT_ENABLE    = "sessionStatEnable";
public final static String PARAM_NAME_SESSION_STAT_MAX_COUNT = "sessionStatMaxCount";
public static final String PARAM_NAME_EXCLUSIONS             = "exclusions";
public static final String PARAM_NAME_PRINCIPAL_SESSION_NAME = "principalSessionName";
public static final String PARAM_NAME_PRINCIPAL_COOKIE_NAME  = "principalCookieName";
public static final String PARAM_NAME_REAL_IP_HEADER         = "realIpHeader";

注意

与stat类似,我们需要注册filter,使用FilterRegistrationBean
不要忘了注册Bean

5. MyBatis

1. 导入依赖

mybatis-spring-boot-starter

<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.3</version>
</dependency>

2. 编写实体类

package com.wang.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private int id;
    private String name;
    private String pwd;
}

3. 编写Dao层

package com.wang.mapper;

import com.wang.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;

//这个注解表示了这是一个MyBatis的Mapper类
//也可以在启动类是加@MapperScan("")扫描包
@Mapper
//这个注解表示了它是Dao层
@Repository
public interface UserMapper {

    List<User> queryUserList();

    User queryUserById(int id);

    int addUser(User user);

    int updateUser(User user);

    int deleteUser(int id);
}

注意

@Mapper
- 这个注解表示了这是一个MyBatis的Mapper类
- 也可以在启动类是加@MapperScan("")扫描包
@Repository
- 这个注解表示了它是Dao层
以上两个注解缺一不可

4. 整合Mybatis

在配置文件中添加

# 整合Mybatis
mybatis:
  type-aliases-package: com.wang.pojo
  mapper-locations: classpath:MyBatis/mapper/*.xml

设置别名以及制定XXXMapper.xml的路径

注意

路径设置中,classpath: 后没有 /

5. 编写XXXMapper.xml

在配置文件对应的路径下(resources路径下)新建文件夹

UserMapper.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.wang.mapper.UserMapper">

    <select id="queryUserList" resultType="User">
        select *
        from user;
    </select>

    <select id="queryUserById" resultType="User">
        select *
        from user
        where id = #{id};
    </select>

    <insert id="addUser" parameterType="User">
        insert into user (id,pwd)
        VALUES (#{id},#{name},#{pwd});
    </insert>

    <update id="updateUser" parameterType="User">
        update user
        set name = #{name},pwd  = #{pwd}
        where id = #{id};
    </update>

    <delete id="deleteUser" parameterType="_int">
        delete
        from user
        where id = #{id};
    </delete>


</mapper>

6. 编写controller

package com.wang.controller;

import com.wang.mapper.UserMapper;
import com.wang.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class UserController {

    @Autowired
    private UserMapper userMapper;

    @GetMapping("/userList")
    public List<User> queryUserList() {
        List<User> userList = userMapper.queryUserList();
        return userList;
    }

    @GetMapping("/addUser")
    public String addUser() {
        int i = userMapper.addUser(new User(6,"小明","123456"));
        return i == 1 ? "ok" : "fail";
    }

    @GetMapping("/updateUser")
    public String updateUser() {
        int i = userMapper.updateUser(new User(6,"小明2","654321"));
        return i == 1 ? "ok" : "fail";
    }

    @GetMapping("/deleteUser")
    public String deleteUser() {
        int i = userMapper.deleteUser(6);
        return i == 1 ? "ok" : "fail";
    }
}

注意

在SpringBoot中,所有的引入要使用自动装配@AutoWired,这里引入Dao的接口,实际项目中应该引入Service层的类(此处偷懒没写)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!