SpringBoot2.4.5集成日志、Mybatis、Druid实现数据库连接池和监控

最新推荐文章于 2024-04-28 21:55:57 发布

JayPan2008

最新推荐文章于 2024-04-28 21:55:57 发布

阅读量547

点赞数 2

分类专栏： SpringBoot Mybatis 文章标签： SpringBoot mybatis Druid 日志

本文链接：https://blog.youkuaiyun.com/binzai325/article/details/116796471

版权

SpringBoot 同时被 2 个专栏收录

2 篇文章

订阅专栏

Mybatis

1 篇文章

订阅专栏

本文介绍了如何在Spring Boot 2.4.5项目中整合MyBatis 2.1.1和Oracle数据库，包括自动扫描DAO、Druid配置、Mapper注解和分页插件。还涵盖了日志配置、服务与控制器实现以及测试步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

开发工具：IDEA
jdk 版本：1.8
springboot 版本：2.4.5

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.4.5</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.example</groupId>
	<artifactId>restservice</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>restservice</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<!--mybatis依赖 -->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.1.1</version>
		</dependency>
		<!--alibaba连接池依赖-->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>1.1.17</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/com.oracle/ojdbc6 -->
        <!--解决java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver-->
		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc6</artifactId>
			<version>12.1.0.1-atlassian-hosted</version>
		</dependency>
        <!--Druid集成日志组件-->
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
		</dependency>
		<!--分页依赖-->
		<dependency>
			<groupId>com.github.pagehelper</groupId>
			<artifactId>pagehelper-spring-boot-starter</artifactId>
			<version>1.2.5</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

目录结构

增加下面注解，以自动扫描 dao 目录，避免每个 dao 都手动加@Mapper注解。

@MapperScan("com.example.mybatis.dao")

SpringBootApplication启动时会默认扫描主类当前包及子包，如果需要扫描主类当前包外的其他包或不扫描当前包下的特定包或类，可通过下列属性实现：

@ComponentScan("com.example")

启动类代码如下：

@SpringBootApplication
@MapperScan("com.example.mybatis.dao")
@ComponentScan("com.example")
public class RestserviceApplication {

	public static void main(String[] args) {
		SpringApplication.run(RestserviceApplication.class, args);
	}

}

修改 application.yml,配置项目，代码如下：

mybatis:
  #对应实体类路径
  type-aliases-package: com.example.mybatis.entity
  #对应mapper映射文件路径
  mapper-locations: classpath:mapper/*.xml
  configuration:
    log-impl: org.apache.ibatis.logging.slf4j.Slf4jImpl
    map-underscore-to-camel-case: true #使用驼峰命名法转换字段，避免手写resultMap，解决实体为null的问题

#pagehelper物理分页配置
pagehelper:
  helper-dialect: oracle
  reasonable: true
  support-methods-arguments: true
  params: count=countSql
  returnPageInfo: check

server:
  port: 8081
  servlet:
    encoding:
      charset: utf-8
      enabled: true

spring:
  datasource:
    name: oracleTest
    type: com.alibaba.druid.pool.DruidDataSource
    #druid连接池相关配置
    druid:
      #监控拦截统计的filters
      filters: stat,wall,log4j #配置扩展插件，常用的插件有=>stat:监控统计  log4j:日志  wall:防御sql注入
      driver-class-name: oracle.jdbc.driver.OracleDriver
      url: jdbc:oracle:thin:@192.168.21.245:1521:orcl
      username: UGTA_JW
      password: gta123
      #配置初始化大小，最小，最大
      initial-size: 1
      min-idle: 1
      max-active: 20
      #获取连接等待超时时间
      max-wait: 6000
      #间隔多久检测一次需要关闭的空闲连接
      time-between-eviction-runs-millis: 60000
      #一个连接在池中的最小生存时间
      min-evictable-idle-time-millis: 300000
      #打开PSCache，并指定每个连接上PSCache的大小。oracle设置为true，mysql设置为false。分库分表设置较多推荐设置
      pool-prepared-statements: false
      max-pool-prepared-statement-per-connection-size: 20
      filter:
        stat:
          log-slow-sql: true
          slow-sql-millis: 2000
        wall:
          config:
            multi-statement-allow: true #解决sql injection violation, multi-statement not allow错误
      #StatViewServlet配置
      stat-view-servlet:
        enabled: true
      use-global-data-source-stat: true

首先创建数据表，sql 语句如下：

CREATE TABLE TSYS_USER_TEST
(  
   USER_ID NVARCHAR2(50) NOT NULL ENABLE, 
   ACCOUNT_NAME NVARCHAR2(150) , 
   ACCOUNT_PASSWORD NVARCHAR2(100),
   AGE INTEGER
)

然后在 entity 包中创建实体类 User.java

package com.example.mybatis.entity;

import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

@Data
public class User {
    private String userId; //人员Id

    private Integer age; //年齡

    private String accountName; //账号

    private String accountPassword; //密码
}

在 dao 包下创建 UserDao.java

package com.example.mybatis.dao;

import com.example.mybatis.entity.User;

import java.util.List;

public interface UserDao {
    //插入用户
    int insert(User user);
    //根据id查询
    User selectById(String id);
    //查询所有
    List<User> selectAll();
}

在 mapper 文件夹下创建 UserMapper.xml,具体的 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.example.mybatis.dao.UserDao">
    <sql id="BASE_TABLE">
        TSYS_USER_TEST
    </sql>
    <sql id="BASE_COLUMN">
        USER_ID,ACCOUNT_NAME,ACCOUNT_PASSWORD,AGE
    </sql>

    <insert id="insert" parameterType="com.example.mybatis.entity.User">
        INSERT INTO <include refid="BASE_TABLE"/>
        <trim prefix="(" suffix=")" suffixOverrides=",">
            USER_ID,ACCOUNT_NAME,ACCOUNT_PASSWORD,
            <if test="age!=null">
                AGE
            </if>
        </trim>
        <trim prefix=" VALUES(" suffix=")" suffixOverrides=",">
            #{userId},#{accountName},#{accountPassword},
            <if test="age!=null">
                #{age,jdbcType=INTEGER}
            </if>
        </trim>
    </insert>

    <select id="selectById" resultType="com.example.mybatis.entity.User">
        select
        <include refid="BASE_COLUMN"/>
        from
        <include refid="BASE_TABLE"/>
        where USER_ID=#{id}
    </select>

    <select id="selectAll" resultType="com.example.mybatis.entity.User">
        select
        <include refid="BASE_COLUMN"/>
        from
        <include refid="BASE_TABLE"/>
    </select>

    <!--配置map-underscore-to-camel-case: true，使用驼峰命名法转换字段，避免手写resultMap，解决实体为null的问题-->

log4j.properties，配置使日志打印到Console窗口

log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

编写 UserService.java,代码如下：

package com.example.mybatis.service;

import com.example.mybatis.dao.UserDao;
import com.example.mybatis.entity.User;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public class UserService {
    @Autowired
    private UserDao userDao; //此處波浪線警示,不影響運行

    public User getByUserId(String id){
        User user = userDao.selectById(id);
        return user;
    }
    //获取全部用户
    public List<User> getAll(){
        List<User> users = userDao.selectAll();
        return users;
    }
    //测试分页
    public PageInfo<User> getAll(int pageNum, int pageSize){
        PageHelper.startPage(pageNum,pageSize);
        List<User> users = userDao.selectAll();
        System.out.println(users.size());
        PageInfo<User> result = new PageInfo<>(users);
        return result;
    }

    public int insert(User user){
        return userDao.insert(user);
    }
}

编写 UserController.java

package com.example.restservice;

import com.example.mybatis.entity.User;
import com.example.mybatis.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
public class UserController {
	@Autowired
	private UserService userService;

	@GetMapping("/user/{userId}")
	public User getUser(@PathVariable String userId){
		return userService.getByUserId(userId);
	}

	@GetMapping("/user")
	public List<User> getAll(){
		return userService.getAll();
	}

	@GetMapping("/user/page/{pageNum}")
	public Object getPage(@PathVariable int pageNum,
						  @RequestParam(name = "pageSize",required = false,defaultValue = "10") int pageSize){
		return userService.getAll(pageNum,pageSize);
	}

	@PostMapping("/user")
	public Object addOne(User user){
		userService.insert(user);
		return user;
	}
}

至此，大功告成，即可运行，使用PostMan进行测试！