springboot配置數(shù)據(jù)源

Spring Framework 為 SQL 數(shù)據(jù)庫(kù)提供了廣泛的支持。從直接使用 JdbcTemplate 進(jìn)行 JDBC 訪問(wèn)到完全的對(duì)象關(guān)系映射（object relational mapping）技術(shù)，比如 Hibernate。Spring Data 提供了更多級(jí)別的功能，直接從接口創(chuàng)建的 Repository 實(shí)現(xiàn)，并使用了約定從方法名生成查詢。

1、JDBC

1、創(chuàng)建項(xiàng)目，導(dǎo)入需要的依賴

 ? ? ? ?<dependency>
 ? ? ? ? ? ?<groupId>org.springframework.boot</groupId>
 ? ? ? ? ? ?<artifactId>spring-boot-starter-jdbc</artifactId>
 ? ? ? ?</dependency>
        <dependency>
 ? ? ? ? ? ?<groupId>mysql</groupId>
 ? ? ? ? ? ?<artifactId>mysql-connector-java</artifactId>
 ? ? ? ? ? ?<scope>runtime</scope>
 ? ? ? ?</dependency>

2、配置數(shù)據(jù)源

spring:
  datasource:
 ?  username: root
 ?  password: 123456
 ?  url: jdbc:mysql://192.168.85.111:3306/sakila?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8
 ?  driver-class-name: com.mysql.jdbc.Driver

3、測(cè)試類(lèi)代碼

package com.mashibing;
?
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 DataApplicationTests {
?
 ? ?@Autowired
 ? ?DataSource dataSource;
?
 ? ?@Test
 ? ?void contextLoads() throws SQLException {
 ? ? ? ?System.out.println(dataSource.getClass());
 ? ? ? ?Connection connection = dataSource.getConnection();
 ? ? ? ?System.out.println(connection);
 ? ? ? ?connection.close();
 ?  }
?
}
//可以看到默認(rèn)配置的數(shù)據(jù)源為class com.zaxxer.hikari.HikariDataSource，我們沒(méi)有經(jīng)過(guò)任何配置，說(shuō)明springboot默認(rèn)情況下支持的就是這種數(shù)據(jù)源，可以在DataSourceProperties.java文件中查看具體的屬性配置

4、crud操作

1、有了數(shù)據(jù)源(com.zaxxer.hikari.HikariDataSource)，然后可以拿到數(shù)據(jù)庫(kù)連接(java.sql.Connection)，有了連接，就可以使用連接和原生的 JDBC 語(yǔ)句來(lái)操作數(shù)據(jù)庫(kù)

2、即使不使用第三方第數(shù)據(jù)庫(kù)操作框架，如 MyBatis等，Spring 本身也對(duì)原生的JDBC 做了輕量級(jí)的封裝，即 org.springframework.jdbc.core.JdbcTemplate。

3、數(shù)據(jù)庫(kù)操作的所有 CRUD 方法都在 JdbcTemplate 中。

4、Spring Boot 不僅提供了默認(rèn)的數(shù)據(jù)源，同時(shí)默認(rèn)已經(jīng)配置好了 JdbcTemplate 放在了容器中，程序員只需自己注入即可使用

5、JdbcTemplate 的自動(dòng)配置原理是依賴 org.springframework.boot.autoconfigure.jdbc 包下的 org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration 類(lèi)

package com.mashibing.contoller;
?
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 {
?
 ? ?@Autowired
 ? ?JdbcTemplate jdbcTemplate;
?
 ? ?@GetMapping("/emplist")
 ? ?public List<Map<String,Object>> empList(){
 ? ? ? ?String sql = "select * from emp";
 ? ? ? ?List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
 ? ? ? ?return maps;
 ?  }
?
 ? ?@GetMapping("/addEmp")
 ? ?public String addUser(){
 ? ? ? ?String sql = "insert into emp(empno,ename) values(1111,'zhangsan')";
 ? ? ? ?jdbcTemplate.update(sql);
 ? ? ? ?return "success";
 ?  }
?
 ? ?@GetMapping("/updateEmp/{id}")
 ? ?public String updateEmp(@PathVariable("id") Integer id){
 ? ? ? ?String sql = "update emp set ename=? where empno = "+id;
 ? ? ? ?String name = "list";
 ? ? ? ?jdbcTemplate.update(sql,name);
 ? ? ? ?return "update success";
 ?  }
?
 ? ?@GetMapping("/deleteEmp/{id}")
 ? ?public String deleteEmp(@PathVariable("id")Integer id){
 ? ? ? ?String sql = "delete from emp where empno = "+id;
 ? ? ? ?jdbcTemplate.update(sql);
 ? ? ? ?return "delete success";
 ?  }
}

2、自定義數(shù)據(jù)源DruidDataSource

通過(guò)源碼查看DataSourceAutoConfiguration.java

@Configuration(proxyBeanMethods = false)
@ConditionalOnClass({ DataSource.class, EmbeddedDatabaseType.class })
@EnableConfigurationProperties(DataSourceProperties.class)
@Import({ DataSourcePoolMetadataProvidersConfiguration.class, DataSourceInitializationConfiguration.class })
public class DataSourceAutoConfiguration {
?
    @Configuration(proxyBeanMethods = false)
    @Conditional(EmbeddedDatabaseCondition.class)
    @ConditionalOnMissingBean({ DataSource.class, XADataSource.class })
    @Import(EmbeddedDataSourceConfiguration.class)
    protected static class EmbeddedDatabaseConfiguration {
?
    }
?
    @Configuration(proxyBeanMethods = false)
    @Conditional(PooledDataSourceCondition.class)
    @ConditionalOnMissingBean({ DataSource.class, XADataSource.class })
    @Import({ DataSourceConfiguration.Hikari.class, DataSourceConfiguration.Tomcat.class,
            DataSourceConfiguration.Dbcp2.class, DataSourceConfiguration.Generic.class,
            DataSourceJmxConfiguration.class })
    protected static class PooledDataSourceConfiguration {
?
    }
?
    /**
     * {@link AnyNestedCondition} that checks that either {@code spring.datasource.type}
     * is set or {@link PooledDataSourceAvailableCondition} applies.
     */
    static class PooledDataSourceCondition extends AnyNestedCondition {
?
        PooledDataSourceCondition() {
            super(ConfigurationPhase.PARSE_CONFIGURATION);
        }
?
        @ConditionalOnProperty(prefix = "spring.datasource", name = "type")
        static class ExplicitType {
?
        }
?
        @Conditional(PooledDataSourceAvailableCondition.class)
        static class PooledDataSourceAvailable {
?
        }
?
    }

1、添加druid的maven配置

<dependency>
 ? ?<groupId>com.alibaba</groupId>
 ? ?<artifactId>druid</artifactId>
 ? ?<version>1.1.12</version>
</dependency>

2、添加數(shù)據(jù)源的配置

spring:
  datasource:
 ?  username: root
 ?  password: 123456
 ?  url: jdbc:mysql://192.168.85.111:3306/demo?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8
 ?  driver-class-name: com.mysql.jdbc.Driver
 ?  type: com.alibaba.druid.pool.DruidDataSource

3、測(cè)試發(fā)現(xiàn)數(shù)據(jù)源已經(jīng)更改

4、druid是數(shù)據(jù)庫(kù)連接池，可以添加druid的獨(dú)有配置

spring:
  datasource:
 ?  username: root
 ?  password: 123456
 ?  url: jdbc:mysql://192.168.85.111:3306/demo?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8
 ?  driver-class-name: com.mysql.jdbc.Driver
 ?  type: com.alibaba.druid.pool.DruidDataSource
 ? ?#Spring Boot 默認(rèn)是不注入這些屬性值的，需要自己綁定
 ? ?#druid 數(shù)據(jù)源專有配置
 ?  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
?
 ? ?#配置監(jiān)控統(tǒng)計(jì)攔截的filters，stat:監(jiān)控統(tǒng)計(jì)、log4j：日志記錄、wall：防御sql注入
 ? ?#如果允許時(shí)報(bào)錯(cuò)  java.lang.ClassNotFoundException: org.apache.log4j.Priority
 ? ?#則導(dǎo)入 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

測(cè)試類(lèi)，發(fā)現(xiàn)配置的參數(shù)沒(méi)有生效

package com.mashibing;

import com.alibaba.druid.pool.DruidDataSource;
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 DataApplicationTests {

    @Autowired
    DataSource dataSource;

    @Test
    void contextLoads() throws SQLException {
        System.out.println(dataSource.getClass());
        Connection connection = dataSource.getConnection();
        System.out.println(connection);

        DruidDataSource druidDataSource = (DruidDataSource)dataSource;
        System.out.println(druidDataSource.getMaxActive());
        System.out.println(druidDataSource.getInitialSize());
        connection.close();
    }

}

需要定義druidDatasource的配置類(lèi)，綁定參數(shù)

package com.mashibing.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
public class DruidConfig {
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druidDataSource(){
        return new DruidDataSource();
    }
}

Druid數(shù)據(jù)源還具有監(jiān)控的功能，并提供了一個(gè)web界面方便用戶進(jìn)行查看。

加入log4j的日志依賴

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

向DruidConfig中添加代碼，配置druid監(jiān)控管理臺(tái)的servlet

package com.mashibing.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.servlet.Servlet;
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 druidDataSource(){
        return new DruidDataSource();
    }

    @Bean
    public ServletRegistrationBean druidServletRegistrationBean(){
        ServletRegistrationBean<Servlet> servletRegistrationBean = new ServletRegistrationBean<>(new StatViewServlet(),"/druid/*");
        Map<String,String> initParams = new HashMap<>();
        initParams.put("loginUsername","admin");
        initParams.put("loginPassword","123456");
        //后臺(tái)允許誰(shuí)可以訪問(wèn)
        //initParams.put("allow", "localhost")：表示只有本機(jī)可以訪問(wèn)
        //initParams.put("allow", "")：為空或者為null時(shí)，表示允許所有訪問(wèn)
        initParams.put("allow","");
        //deny：Druid 后臺(tái)拒絕誰(shuí)訪問(wèn)
        //initParams.put("msb", "192.168.1.20");表示禁止此ip訪問(wèn)

        servletRegistrationBean.setInitParameters(initParams);
        return servletRegistrationBean;
    }

    //配置 Druid 監(jiān)控 之  web 監(jiān)控的 filter
    //WebStatFilter：用于配置Web和Druid數(shù)據(jù)源之間的管理關(guān)聯(lián)監(jiān)控統(tǒng)計(jì)
    @Bean
    public FilterRegistrationBean webStatFilter() {
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

        //exclusions：設(shè)置哪些請(qǐng)求進(jìn)行過(guò)濾排除掉，從而不進(jìn)行統(tǒng)計(jì)
        Map<String, String> initParams = new HashMap<>();
        initParams.put("exclusions", "*.js,*.css,/druid/*");
        bean.setInitParameters(initParams);

        //"/*" 表示過(guò)濾所有請(qǐng)求
        bean.setUrlPatterns(Arrays.asList("/*"));
        return bean;
    }
}

3、springboot配置多數(shù)據(jù)源并動(dòng)態(tài)切換

DataSource是和線程綁定的，動(dòng)態(tài)數(shù)據(jù)源的配置主要是通過(guò)繼承AbstractRoutingDataSource類(lèi)實(shí)現(xiàn)的，實(shí)現(xiàn)在AbstractRoutingDataSource類(lèi)中的 protected Object determineCurrentLookupKey()方法來(lái)獲取數(shù)據(jù)源，所以我們需要先創(chuàng)建一個(gè)多線程線程數(shù)據(jù)隔離的類(lèi)來(lái)存放DataSource，然后在determineCurrentLookupKey()方法中通過(guò)這個(gè)類(lèi)獲取當(dāng)前線程的DataSource，在AbstractRoutingDataSource類(lèi)中，DataSource是通過(guò)Key-value的方式保存的，我們可以通過(guò)ThreadLocal來(lái)保存Key，從而實(shí)現(xiàn)數(shù)據(jù)源的動(dòng)態(tài)切換。

1、修改配置文件類(lèi)

spring:
  datasource:
    local:
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://localhost:3306/demo?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8
    remote:
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
      jdbc-url: jdbc:mysql://192.168.85.111:3306/demo?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8

2、創(chuàng)建數(shù)據(jù)源枚舉類(lèi)

package com.mashibing.mult;

public enum DataSourceType {
    REMOTE,
    LOCAL
}

3、數(shù)據(jù)源切換處理

創(chuàng)建一個(gè)數(shù)據(jù)源切換處理類(lèi)，有對(duì)數(shù)據(jù)源變量的獲取、設(shè)置和情況的方法，其中threadlocal用于保存某個(gè)線程共享變量。

package com.mashibing.mult;

public class DynamicDataSourceContextHolder {

    /**
     * 使用ThreadLocal維護(hù)變量，ThreadLocal為每個(gè)使用該變量的線程提供獨(dú)立的變量副本，
     *  所以每一個(gè)線程都可以獨(dú)立地改變自己的副本，而不會(huì)影響其它線程所對(duì)應(yīng)的副本。
     */
    private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();

    /**
     * 設(shè)置數(shù)據(jù)源變量
     * @param dataSourceType
     */
    public static void setDataSourceType(String dataSourceType){
        System.out.printf("切換到{%s}數(shù)據(jù)源", dataSourceType);
        CONTEXT_HOLDER.set(dataSourceType);
    }

    /**
     * 獲取數(shù)據(jù)源變量
     * @return
     */
    public static String getDataSourceType(){
        return CONTEXT_HOLDER.get();
    }

    /**
     * 清空數(shù)據(jù)源變量
     */
    public static void clearDataSourceType(){
        CONTEXT_HOLDER.remove();
    }
}

4、繼承AbstractRoutingDataSource

動(dòng)態(tài)切換數(shù)據(jù)源主要依靠AbstractRoutingDataSource。創(chuàng)建一個(gè)AbstractRoutingDataSource的子類(lèi)，重寫(xiě)determineCurrentLookupKey方法，用于決定使用哪一個(gè)數(shù)據(jù)源。這里主要用到AbstractRoutingDataSource的兩個(gè)屬性defaultTargetDataSource和targetDataSources。defaultTargetDataSource默認(rèn)目標(biāo)數(shù)據(jù)源，targetDataSources（map類(lèi)型）存放用來(lái)切換的數(shù)據(jù)源。

package com.mashibing.mult;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

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

public class DynamicDataSource extends AbstractRoutingDataSource {

    public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
        super.setDefaultTargetDataSource(defaultTargetDataSource);
        super.setTargetDataSources(targetDataSources);
        // afterPropertiesSet()方法調(diào)用時(shí)用來(lái)將targetDataSources的屬性寫(xiě)入resolvedDataSources中的
        super.afterPropertiesSet();
    }

    /**
     * 根據(jù)Key獲取數(shù)據(jù)源的信息
     *
     * @return
     */
    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceContextHolder.getDataSourceType();
    }
}

5、注入數(shù)據(jù)源

package com.mashibing.mult;

import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

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

@Configuration
public class DataSourceConfig {
    @Bean
    @ConfigurationProperties("spring.datasource.remote")
    public DataSource remoteDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.local")
    public DataSource localDataSource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean(name = "dynamicDataSource")
    @Primary
    public DynamicDataSource dataSource(DataSource remoteDataSource, DataSource localDataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceType.REMOTE.name(), remoteDataSource);
        targetDataSources.put(DataSourceType.LOCAL.name(), localDataSource);
        return new DynamicDataSource(remoteDataSource, targetDataSources);
    }
}

6、自定義多數(shù)據(jù)源切換注解

設(shè)置攔截?cái)?shù)據(jù)源的注解，可以設(shè)置在具體的類(lèi)上，或者在具體的方法上

package com.mashibing.mult;

import java.lang.annotation.*;

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSource {
    /**
     * 切換數(shù)據(jù)源名稱
     */
    DataSourceType value() default DataSourceType.REMOTE;
}

7、AOP攔截類(lèi)的實(shí)現(xiàn)

通過(guò)攔截上面的注解，在其執(zhí)行之前處理設(shè)置當(dāng)前執(zhí)行SQL的數(shù)據(jù)源的信息，CONTEXT_HOLDER.set(dataSourceType)這里的數(shù)據(jù)源信息從我們?cè)O(shè)置的注解上面獲取信息，如果沒(méi)有設(shè)置就是用默認(rèn)的數(shù)據(jù)源的信息。

package com.mashibing.mult;

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;

@Aspect
@Order(1)
@Component
public class DataSourceAspect {

    @Pointcut("@annotation(com.mashibing.mult.DataSource)")
    public void dsPointCut() {

    }

    @Around("dsPointCut()")
    public Object around(ProceedingJoinPoint point) throws Throwable {
        MethodSignature signature = (MethodSignature) point.getSignature();
        Method method = signature.getMethod();
        DataSource dataSource = method.getAnnotation(DataSource.class);
        if (dataSource != null) {
            DynamicDataSourceContextHolder.setDataSourceType(dataSource.value().name());
        }
        try {
            return point.proceed();
        } finally {
            // 銷(xiāo)毀數(shù)據(jù)源 在執(zhí)行方法之后
            DynamicDataSourceContextHolder.clearDataSourceType();
        }
    }
}

8、使用切換數(shù)據(jù)源注解

package com.mashibing.mult;

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

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


@RestController
public class EmpController {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @GetMapping("/local")
    @DataSource(value = DataSourceType.LOCAL)
    public List<Map<String, Object>> local(){
        List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from emp");
        return maps;
    }
    @GetMapping("/remote")
    @DataSource(value = DataSourceType.REMOTE)
    public List<Map<String, Object>> remote(){
        List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from emp");
        return maps;
    }

}

9、在啟動(dòng)項(xiàng)目的過(guò)程中會(huì)發(fā)生循環(huán)依賴的問(wèn)題，直接修改啟動(dòng)類(lèi)即可

package com.mashibing;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
public class SpringbootDataApplication {

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

4、springboot整合mybatis

1、導(dǎo)入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>2.1.1</version>
</dependency>

2、配置數(shù)據(jù)源

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://192.168.85.111:3306/demo?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8
    driver-class-name: com.mysql.jdbc.Driver

3、測(cè)試類(lèi)

package com.mashibing;

import com.alibaba.druid.pool.DruidDataSource;
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 DataApplicationTests {

    @Autowired
    DataSource dataSource;

    @Test
    void contextLoads() throws SQLException {
        System.out.println(dataSource.getClass());
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        System.out.println(connection.getMetaData().getURL());

        connection.close();
    }
}

4、創(chuàng)建實(shí)體類(lèi)

package com.mashibing.entity;

import java.sql.Date;
import java.util.Objects;

public class Emp {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Double sal;
    private Double comm;
    private Integer deptno;

    public Emp() {
    }

    public Emp(Integer empno, String ename) {
        this.empno = empno;
        this.ename = ename;
    }

    public Emp(Integer empno, String ename, String job, Integer mgr, Date hiredate, Double sal, Double comm, Integer deptno) {
        this.empno = empno;
        this.ename = ename;
        this.job = job;
        this.mgr = mgr;
        this.hiredate = hiredate;
        this.sal = sal;
        this.comm = comm;
        this.deptno = deptno;
    }

    public Integer getEmpno() {
        return empno;
    }

    public void setEmpno(Integer empno) {
        this.empno = empno;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public Integer getMgr() {
        return mgr;
    }

    public void setMgr(Integer mgr) {
        this.mgr = mgr;
    }

    public Date getHiredate() {
        return hiredate;
    }

    public void setHiredate(Date hiredate) {
        this.hiredate = hiredate;
    }

    public Double getSal() {
        return sal;
    }

    public void setSal(Double sal) {
        this.sal = sal;
    }

    public Double getComm() {
        return comm;
    }

    public void setComm(Double comm) {
        this.comm = comm;
    }

    public Integer getDeptno() {
        return deptno;
    }

    public void setDeptno(Integer deptno) {
        this.deptno = deptno;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (!(o instanceof Emp)) return false;
        Emp emp = (Emp) o;
        return Objects.equals(empno, emp.empno) &&
                Objects.equals(ename, emp.ename) &&
                Objects.equals(job, emp.job) &&
                Objects.equals(mgr, emp.mgr) &&
                Objects.equals(hiredate, emp.hiredate) &&
                Objects.equals(sal, emp.sal) &&
                Objects.equals(comm, emp.comm) &&
                Objects.equals(deptno, emp.deptno);
    }

    @Override
    public int hashCode() {

        return Objects.hash(empno, ename, job, mgr, hiredate, sal, comm, deptno);
    }

    @Override
    public String toString() {
        return "Emp{" +
                "empno=" + empno +
                ", ename='" + ename + '\'' +
                ", job='" + job + '\'' +
                ", mgr=" + mgr +
                ", hiredate=" + hiredate +
                ", sal=" + sal +
                ", comm=" + comm +
                ", deptno=" + deptno +
                '}';
    }
}

5、配置Mapper接口類(lèi)

package com.mashibing.mapper;

import com.mashibing.entity.Emp;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface EmpMapper {

    List<Emp> selectEmp();

    Emp selectEmpById(Integer empno);

    Integer addEmp(Emp emp);

    Integer updateEmp(Emp emp);

    Integer deleteEmp(Integer empno);
}

6、在resources下創(chuàng)建Emp.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.mashibing.mapper.EmpMapper">

    <select id="selectEmp" resultType="Emp">
    select * from emp
  </select>

    <select id="selectEmpById" resultType="Emp">
    select * from emp where empno = #{empno}
    </select>

    <insert id="addEmp" parameterType="Emp">
    insert into emp (empno,ename) values (#{empno},#{ename})
    </insert>

    <update id="updateEmp" parameterType="Emp">
    update emp set ename=#{ename} where empno = #{empno}
    </update>

    <delete id="deleteEmp" parameterType="int">
    delete from emp where empno = #{empno}
</delete>
</mapper>

7、添加配置文件

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://192.168.85.111:3306/demo?serverTimezone=UTC&useUnicode=true@characterEncoding=utf-8
    driver-class-name: com.mysql.jdbc.Driver
mybatis:
  mapper-locations: classpath:mybatis/mapper/*.xml
  type-aliases-package: com.mashibing.entity

8、編寫(xiě)controller

package com.mashibing.contoller;

import com.mashibing.entity.Emp;
import com.mashibing.mapper.EmpMapper;
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 EmpController {
    @Autowired
    private EmpMapper empMapper;

    //選擇全部用戶
    @GetMapping("/selectEmp")
    public String selectEmp(){
        List<Emp> emps = empMapper.selectEmp();
        for (Emp Emp : emps) {
            System.out.println(Emp);
        }
        return "ok";
    }
    //根據(jù)id選擇用戶
    @GetMapping("/selectEmpById")
    public String selectEmpById(){
        Emp emp = empMapper.selectEmpById(1234);
        System.out.println(emp);
        return "ok";
    }
    //添加一個(gè)用戶
    @GetMapping("/addEmp")
    public String addEmp(){
        empMapper.addEmp(new Emp(1234,"heheda"));
        return "ok";
    }
    //修改一個(gè)用戶
    @GetMapping("/updateEmp")
    public String updateEmp(){
        empMapper.updateEmp(new Emp(1234,"heihei"));
        return "ok";
    }
    //根據(jù)id刪除用戶
    @GetMapping("/deleteEmp")
    public String deleteEmp(){
        empMapper.deleteEmp(1234);
        return "ok";
    }
}

9、測(cè)試即可文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-475950.html

到了這里，關(guān)于springboot配置數(shù)據(jù)源的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！