SpringBoot整合JDBC

SpringBoot整合JDBC

九月 09, 2020

数据库建表语句

sql文件在resources文件目录下。

创建项目，导入依赖

添加以下启动器：
在这里插入图片描述
同时添加以下Maven依赖：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<dependency>
  <groupId>log4j</groupId>
  <artifactId>log4j</artifactId>
  <version>1.2.17</version>
</dependency>
<!-- 添加数据源 -->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
  <version>1.1.21</version>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <scope>runtime</scope>
</dependency>
<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
  <optional>true</optional>
</dependency>

编写application.yml文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
spring:
  datasource:
    username: root
    password: root
    # ?serverTimezone=UTC解决时区的报错
    url: jdbc:mysql://localhost:3306/oldou?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource # 使用druid数据源


    #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
    # 打开PSCache，并且指定每个连接上PSCache的大小
    poolPreparedStatements: true
    max-open-prepared-statements: 20
    max-pool-prepared-statement-per-connection-size: 20
    #配置监控统计拦截的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

书写DAO层代码

新建一个com.oldou.dao.UserDao

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

@Repository
public class UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 保存用户
     */
    public void save(User user){
        String sql = "insert into user (id,name,pwd) values(?,?,?)";
        jdbcTemplate.update(sql,user.getId(),user.getName(),user.getPwd());
    }

    /**
     * 根据ID更新用户信息
     */
    public void updateUser(User user){
        String sql = "update user set name=?,pwd=? where id=?";
        jdbcTemplate.update(sql,user.getName(),user.getPwd(),user.getId());
    }

    /**
     * 根据ID删除用户
     */
    public void delete(int id){
        String sql = "delete from user where id = ?";
        jdbcTemplate.update(sql,id);
    }

    /**
     * 查询全部用户
     */
    public List<Map<String, Object>> QueryAll(){
        String sql = "select * from user";
        return jdbcTemplate.queryForList(sql);
    }
    /**
     * 根据ID查询用户
     */
    public User QueryUserByID(int id){
        String sql = "select * from user where id = ?";
        return jdbcTemplate.queryForObject(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setPwd(rs.getString("pwd"));
                return user;
            }
        },id);
    }
}

编写业务层代码

新建一个com.oldou.service.Uservice接口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
public interface UserService {
    /**
     * 保存用户
     * @return
     */
    public void save(User user);
    /**
     * 根据ID删除用户
     */
    public void delete(int id);
    /**
     * 查询全部用户
     * @return
     */
    public List<Map<String, Object>> QueryAll();
    /**
     * 根据ID查询用户
     * @return
     */
    public User QueryUserByID(int id);

    /**
     * 更新用户
     */
    public void updateUser(User user);
}

com.oldou.service.impl.UserServiceImpl实现类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserDao userDao;

    @Override
    public void save(User user) {
        this.userDao.save(user);
    }

    @Override
    public void delete(int id) {
        this.userDao.delete(id);
    }

    @Override
    public List<Map<String, Object>> QueryAll() {
        return this.userDao.QueryAll();
    }

    @Override
    public User QueryUserByID(int id) {
        return this.userDao.QueryUserByID(id);
    }

    @Override
    public void updateUser(User user) {
        this.userDao.updateUser(user);
    }
}

编写Controller层

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
@RestController
public class UserController {
    @Autowired
    private UserService userService;
    //查询user表中所有数据
    //List 中的1个 Map 对应数据库的 1行数据
    //Map 中的 key 对应数据库的字段名，value 对应数据库的字段值
    @GetMapping("/list")
    public List<Map<String, Object>> queryAllUser(){
        List<Map<String, Object>> list = userService.QueryAll();
        return list;
    }
    //根据ID查询用户
    @GetMapping("/query/{id}")
    public User queryUserByID(@PathVariable("id") int id){
        User user = userService.QueryUserByID(id);
        return user;
    }
    //根据ID删除用户
    @RequestMapping("/delete/{id}")
    public String deleteUser(@PathVariable("id") int id){
        userService.delete(id);
        return "删除用户成功！！";
    }

    //增加用户
    @RequestMapping("/add")
    public String addUser(){
        userService.save(new User(6,"ppp","1232"));
        return "增加用户成功";
    }

    //根据ID更新用户
    @RequestMapping("/update/{id}")
    public String updateUser(@PathVariable("id") int id){
        userService.updateUser(new User(id,"ppp","999"));
        return "更新用户成功";
    }
}

测试

启动项目，访问
localhost:8080/list 查询全部用户信息
localhost:8080/query/1 查询id为1的用户
localhost:8080/add 增加用户
localhost:8080/update/1 修改id为1的用户
localhost:8080/delete/1 删除id为1的用户

拓展

配置Druid数据源监控

Druid 数据源具有监控的功能，并提供了一个 web 界面方便用户查看，类似安装 路由器 时，人家也提供了一个默认的 web 页面。

所以第一步需要设置 Druid 的后台管理页面，比如 登录账号、密码 等；配置后台管理；

新建一个com.oldou.config.DuridConfig

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
package com.oldou.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
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;
import java.util.Map;

@Configuration
public class DuridConfig {
     /* 将yml文件和这个文件绑定起来
       将自定义的 Druid数据源添加到容器中，不再让 Spring Boot 自动创建
       绑定全局配置文件中的 druid 数据源属性到 com.alibaba.druid.pool.DruidDataSource从而让它们生效
       @ConfigurationProperties(prefix = "spring.datasource")：作用就是将 全局配置文件中
       前缀为 spring.datasource的属性值注入到 com.alibaba.druid.pool.DruidDataSource 的同名参数中
     */
     @ConfigurationProperties(prefix = "spring.datasource")
     @Bean
     public DataSource druidDataSource(){
         return new DruidDataSource();
     }

    /** 后台监控    这个就相当于web.xml文件  ServletRegistrationBean
     * 配置 Druid 监控管理后台的Servlet；     DynamicRegistrationBean
     * 内置 Servlet 容器时没有web.xml文件，所以使用 Spring Boot 的注册 Servlet 方式
     * 替代web.xml的方法：在ServletRegistrationBean中new一个新的方法就可以了
     */
    @Bean
    public ServletRegistrationBean statViewServlet(){
        //配置进入后台监控的路径(固定代码)
        ServletRegistrationBean<StatViewServlet> bean =
                new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
        //后台有人登录，需要账号密码(固定)
        Map<String, String> initParameters = new HashMap<>();

        //添加配置--注意：登录的Key是固定的 loginUsername  loginPassword
        initParameters.put("loginUsername","admin");
        initParameters.put("loginPassword","123456");

        //允许谁可以访问--如果为空表示谁都可以，localhost表示本机，IP地址
        initParameters.put("allow","");
        //initParams.put("allow", "localhost")：表示只有本机可以访问
        //initParams.put("allow", "")：为空或者为null时，表示允许所有访问
        //禁止谁能访问 initParameters.put("oldou","192.168.15.131");

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

    /** 配置过滤器 filter   可以去查看WebStatFilter下有哪些东西可以配置
     */
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

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

        //以下不进行统计
        initParameters.put("exclusions","*.js,*.css,/druid/*");

        bean.setInitParameters(initParameters);
        return bean;
    }

}

访问：http://localhost:8080/druid/login.html
在这里插入图片描述
使用设置的密码登录：admin 123456
在这里插入图片描述
在里面我们就可以实时的监控每条sql的执行信息等等。