springboot 数据源配置 yml springboot配置mybatis数据源

转载

hackernew 2024-03-22 10:55:05

文章标签 java mybatis springboot gradle spring 文章分类 架构后端开发

采用阿里的Druid做线程池；

首先在项目的build.gradle或pom文件中增加mysql或oracle的依赖

//druid连接池
compile ("com.alibaba:druid-spring-boot-starter:1.1.10")
//oracle
compile ("com.oracle:ojdbc14:10.2.0.3.0")
//mysql
compile ("mysql:mysql-connector-java:8.0.12")
//Mybatis 依赖
compile ("org.mybatis.spring.boot:mybatis-spring-boot-starter:1.3.1")

在项目中的启动配置文件中增加数据库连接信息，这里以yml为例，properties同理；

spring:
  datasource:
    druid:
      name: project
      url: jdbc:mysql://10.211.55.3:3306/project
      username: oracle
      password: 123456
      type: com.alibaba.druid.pool.DruidDataSource
      driver-calss: com.mysql.jdbc.Driver
      #属性类型是字符串，通过别名的方式配置扩展插件常用的插件有：监控统计用的filter:stat 日志用的filter:log4j 防御sql注入的filter:wall
      filters: stat
      #初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
      initialSize: 5
      #最小连接池数量
      minIdle: 5
      #最大连接池数量
      maxActive: 10
      #有两个含义：1) Destroy线程会检测连接的间隔时间 2) testWhileIdle的判断依据，详细看testWhileIdle属性的说明
      timeBetweenEvictionRunsMillis: 20000
      #配置一个连接在池中最小生存的时间，单位是毫秒
      minEvictableIdleTimeMillis: 300000
      #用来检测连接是否有效的sql，要求是一个查询语句 如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会其作用。
      validationQuery: SELECT '1' FROM DUAL
      #建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis 执行validationQuery检测连接是否有效
      testWhileIdle: true
      #申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
      testOnBorrow: false
      #归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
      testOnReturn: false
      #要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。 在Druid中，不会存在Oracle下PSCache占用内存过多的问题 可以把这个数值配置大一些，比如说100
      maxOpenPreparedStatements: 100
      #关闭 abanded 连接时输出错误日志
      logAbandoned: true

启动类中增加事物的开启

@EnableTransactionManagement启用事务管理

设置数据源配置

package com.project.core.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
 * @author lantao 
 * @Title: DataSourseConfiguration
 * @ProjectName project
 * @date 2018/9/13 17:46
 * @Description: druid 数据库连接池 配置类
 */
@Configuration
//设置扫描 DAO层
@MapperScan(basePackages = {DataSourseConfiguration.MAPPER_PACKAGE})
public class DataSourseConfiguration {

    public static final String MAPPER_PACKAGE = "com.project.core.mapper";

    public static final String MAPPER_XML_PACKAGE = "classpath:mapper/*.xml";

    public static final String MYBATIS_BEAN_PACKAGE = "com.project.core.bean";

    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.druid")
    @Bean(name = "dataSource")
    public DataSource dataSourceConfig() {
        DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
        return dataSource;
    }

    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSourceConfig());

        //设置扫描 mybatis-config.xml
        sqlSessionFactoryBean.setConfigLocation(null);

        //设置扫描mapper.xml
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        Resource[] resources = resolver.getResources(MAPPER_XML_PACKAGE);
        sqlSessionFactoryBean.setMapperLocations(resources);

        //设置扫描实体类
        sqlSessionFactoryBean.setTypeAliasesPackage(MYBATIS_BEAN_PACKAGE);

        return sqlSessionFactoryBean.getObject();
    }


    @Primary
    @Bean(name = "sqlSessionTemplate")
    public SqlSessionTemplate popSqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Primary
    @Bean(name = "transactionManager")
    public DataSourceTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dataSourceConfig());
    }
}

测试：

Controller

package com.project.api.controller;

import com.project.core.service.UserService;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * @author lantao 
 * @Title: TestController
 * @ProjectName project
 * @date 2018/9/12 14:30
 * @Description: TODO
 */
@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/getUser")
    @ApiOperation(value = "获取用户信息", notes = "获取用户信息")
    public Object getUesr() {
        return userService.selUserInfoForAll();
    }


    @GetMapping("/getName/{userName}")
    @ApiOperation(value = "获取用户名", notes = "获取用户名")
    public Object getName(@PathVariable("userName") String userName) {
        return userService.selUserInfo(userName);
    }

    @DeleteMapping("/delUser/{userName}")
    @ApiOperation(value = "删除用户信息", notes = "删除用户信息")
    public String delUser(@PathVariable("userName") String userName) {
        try {
            userService.deleteUser(userName);
        } catch (Exception e) {
            return "失败";
        }
        return "成功";
    }
}

Service

package com.project.core.service;

import com.project.core.bean.UserBean;

import java.util.List;

/**
 * @author lantao
 * @Title: UserService
 * @ProjectName project
 * @date 2018/9/18 13:53
 * @Description: TODO
 */
public interface UserService {

    List<UserBean> selUserInfoForAll();

    UserBean selUserInfo(String userName);

    void deleteUser(String userName);
}

ServiceImpl

package com.project.core.service.impl;

import com.project.core.bean.UserBean;
import com.project.core.mapper.UserMapper;
import com.project.core.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author lantao
 * @Title: UserServiceImpl
 * @ProjectName project
 * @date 2018/9/18 13:56
 * @Description: TODO
 */
@Service
public class UserServiceImpl implements UserService{

    @Autowired
    private UserMapper userMapper;

    @Override
    public List<UserBean> selUserInfoForAll() {
        return userMapper.selUserInfoForAll();
    }

    @Override
    public UserBean selUserInfo(String userName) {
        return userMapper.selUserInfo(userName);
    }

    @Override
    public void deleteUser(String userName) {
        userMapper.deleteUser(userName);
    }
}

Mapper

package com.project.core.mapper;

import com.project.core.bean.UserBean;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author lantao
 * @Title: UserMapper
 * @ProjectName project
 * @date 2018/9/18 13:56
 * @Description: TODO
 */
@Repository
public interface UserMapper {

    List<UserBean> selUserInfoForAll();

    void deleteUser(@Param(value = "userName") String userName);

    UserBean selUserInfo(@Param(value = "userName") String userName);
}

<?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.project.core.mapper.UserMapper">

    <resultMap id="userBean" type="com.project.core.bean.UserBean">
        <result column="NAME" property="name"/>
        <result column="SEX" property="sex"/>
        <result column="AGE" property="age"/>
        <result column="USER_NAME" property="userName"/>
        <result column="PASS_WORD" property="passWd"/>
    </resultMap>

    <select id="selUserInfoForAll"  resultMap="userBean">
        SELECT * FROM USER_INFO
    </select>

    <select id="selUserInfo"  parameterType="java.lang.String" resultMap="userBean">
        SELECT * FROM USER_INFO t WHERE USER_NAME = #{userName}
    </select>

    <delete id="deleteUser" parameterType="java.lang.String">
        DELETE FROM USER_INFO  WHERE USER_NAME = #{userName}
    </delete>
</mapper>