SpringBoot整合JDBC、Druid以及Mybatis

最新推荐文章于 2024-04-14 22:04:49 发布

你好y

最新推荐文章于 2024-04-14 22:04:49 发布

阅读量252

点赞数 1

分类专栏： SpringBoot 文章标签： java mysql spring boot

本文链接：https://blog.csdn.net/qq_43430343/article/details/119919622

版权

SpringBoot 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

本文介绍了如何在Spring Boot项目中整合JDBC、切换到Druid数据源以及集成Mybatis。内容包括配置YAML文件、测试数据库连接、使用JdbcTemplate操作数据库、配置Druid监控以及实现Mybatis的接口与Mapper XML文件。

摘要由CSDN通过智能技术生成

整合JDBC

application.yaml


spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/springboot?serverTimezone=GMT&useSSL=false
    driver-class-name: com.mysql.cj.jdbc.Driver

测试层：

package com.kuang;

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 Springboot06DataApplicationTests {

    @Autowired
    private DataSource dataSource;

    @Test
    void contextLoads() throws SQLException {
        //查看当前默认数据源
        System.out.println(dataSource.getClass());  //class com.zaxxer.hikari.HikariDataSource

        //查看数据库连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);   //HikariProxyConnection@348084146 wrapping com.mysql.cj.jdbc.ConnectionImpl@46d9aec8

        //关闭
        connection.close();

    }

}

controller

package com.kuang.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.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

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

@RestController   //==controller + responsebody
public class JDBCController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

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

    //添加
    @GetMapping("/add")
    public String addUser(){
        String sql="insert into user(username,password) values('大黄','6666')";
        jdbcTemplate.update(sql);
        return "add is ok";
    }
    //修改     @PathVariable  接收请求路径中占位符的值
    //
    @GetMapping("/update/{id}")
    public String updatelist(@PathVariable("id") int id){
        String sql="update user set  username=?,password=? where id="+id;

        //封装
        Object[] objects=new Object[2];
        objects[0]="test";
        objects[1]="1111";
         jdbcTemplate.update(sql,objects);
         return "update is ok";
    }

    //删除
    @GetMapping("/deleter/{id}")
    public String delete(@PathVariable("id") int id){
        String sql="delete from user where id="+id;
        jdbcTemplate.update(sql);
        return "delete is ok";
    }

}

整合Druid数据源

pom.xml

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

application.yaml

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/springboot?serverTimezone=GMT&useSSL=false
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    #url: jdbc:mysql://localhost:3306/springboot?serverTimezone=GMT

    #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
    #连接失败后重试次数
    connection-error-retry-attempts: 5
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

配置之后测试一下

package com.kuang;

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 Springboot06DataApplicationTests {

    @Autowired
    private DataSource dataSource;

    @Test
    void contextLoads() throws SQLException {
        //查看当前默认数据源
        System.out.println(dataSource.getClass());  //class com.zaxxer.hikari.HikariDataSource

        //查看数据库连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);   //HikariProxyConnection@348084146 wrapping com.mysql.cj.jdbc.ConnectionImpl@46d9aec8

        //关闭
        connection.close();

    }

}

Druid配置类：

package com.kuang.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.Arrays;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DruidConfig {

    //将该配置类 绑定配置文件
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druidSource(){
        return new DruidDataSource();
    }

    //后台监控 ：web.xml
    //因为SpringBoot 内置了servlet容器，所以没有web.xml,   替代方法:ServletRegistrationBean
   @Bean
    public ServletRegistrationBean staViewServlet(){
        //ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet());
       ServletRegistrationBean<StatViewServlet> bean =
               new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
        //后台需要有人登录，账号密码配置
        HashMap<String,String> initParameters=new HashMap<>();

        //增加配置  loginUsername和loginPassword是固定写法
        initParameters.put("loginUsername","admin");
        initParameters.put("loginPassword","12345");

        //允许谁可以访问 空表示任何人
        initParameters.put("allow","");

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


    }

    //配置 Druid 监控 之  web 监控的 filter
//WebStatFilter：用于配置Web和Druid数据源之间的管理关联监控统计
    @Bean
    public FilterRegistrationBean webStatFilter() {
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

        //exclusions：设置哪些请求进行过滤排除掉，从而不进行统计
        Map<String, String> initParams = new HashMap<>();
        initParams.put("exclusions", "*.js,*.css,/druid/*,/jdbc/*");
        bean.setInitParameters(initParams);

        //"/*" 表示过滤所有请求
        bean.setUrlPatterns(Arrays.asList("/*"));
        return bean;
    }
}

Mybatis

1、pom.xml

 <!--mybatis-->
        <!-- 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>1.3.2</version>
        </dependency>

2、application.yaml

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/springboot?serverTimezone=GMT&useSSL=false
    driver-class-name: com.mysql.cj.jdbc.Driver

3、测试数据库连接

package com.kuang;

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.SQLException;

@SpringBootTest
class Springboot07MybatisApplicationTests {

    @Autowired
    private DataSource dataSource;

    @Test
    void contextLoads() throws SQLException {
        //测试数据库是否连接上
        System.out.println(dataSource.getClass());
        System.out.println(dataSource.getConnection());
    }

}

4、

<dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

5、实体类

package com.kuang.pojo;


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

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private Integer id;
    private String username;
    private String password;
}

6、接口方法

package com.kuang.mapper;

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

import java.util.List;

@Mapper   //这个注解表示这是一个mybatis的mapper类
@Repository //标明是dao
public interface UserMapper {
    /*
    * 回顾之前的：在接口里面定义一个常量 int a=10; 前面默认为public static final
    * */

    List<User> findAll();

    //查询一个
    User findUserById(Integer id);
    //添加
    int addUser(User user);
    //修改
    int update(User user);
    //删除
    int deleteById(Integer id);

}

7、mapper实现接口方法

<?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.kuang.mapper.UserMapper">
    <select id="findAll" resultType="User">
    select * from user;
  </select>

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

    <insert id="addUser" parameterType="User">
        insert into user(username,password)   values(#{username},#{password});
    </insert>

   <update id="update" parameterType="User">
       update user set username=#{username},password=#{password} where id=#{id};
   </update>

    <delete id="deleteById" parameterType="Integer">
        delete from user where id=#{id};
    </delete>

</mapper>

8、controller

package com.kuang.controller;


import com.kuang.mapper.UserMapper;
import com.kuang.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
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;

@RestController
public class UserController {

    @Autowired
    private UserMapper userMapper;

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

    @GetMapping("/findUserById/{id}")
    public User findUserById(@PathVariable("id")int id){
        User userById = userMapper.findUserById(id);
        return userById;
    }


    @GetMapping("/addUser")
    public Integer addUser(User user){
        user.setUsername("aaaa");
        user.setPassword("111");
        int i = userMapper.addUser(user);
        return i;
    }

    @GetMapping("/update/{id}")
    public Integer update(@PathVariable("id")int id){
        User user = userMapper.findUserById(id);
        user.setUsername("修改");
        user.setPassword("000");
        int i = userMapper.update(user);
        return i;
    }

    @GetMapping("/deleteById/{id}")
    public Integer deleteById(@PathVariable("id")int id){
        int i = userMapper.deleteById(id);
        return i;
    }

}

你好y

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
SpringBoot整合JDBC、Druid以及Mybatis

整合JDBCapplication.yamlspring: datasource: username: root password: root url: jdbc:mysql://localhost:3306/springboot?serverTimezone=GMT&useSSL=false driver-class-name: com.mysql.cj.jdbc.Driver测试层：package com.kuang;import org.ju
复制链接

扫一扫

专栏目录