idea创建springboot+jdbcTemplate多数据源

最新推荐文章于 2024-05-09 14:49:10 发布

原创最新推荐文章于 2024-05-09 14:49:10 发布 · 852 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

JAVA

SpringBoot

JDBC

本文详细介绍了如何使用Spring Boot创建项目并配置多数据源，包括数据库配置、实体类、控制器、服务及DAO层的实现，以及使用JdbcTemplate进行数据库操作。同时，展示了应用 Druid 数据源和接口测试的过程。

该文章已生成可运行项目，

一、Idea创建工程

进入Idea ，file-->new->project...

Next (环境、版本号等设置)

Next （选择核心依赖组件）

Next （给新建的项目起个名字，以及设置保存地址、工作目录）

Finish，完后项目创建。

二、创建工程层级目录

config:多数据源配置目录 controller：请求入口，控制层 dao：持久层，jdbcTemplate操作数据库 pojo：数据库表对应的实体对象 service：Model层，业务逻辑、数据处理封装等 aoolocation：配置文件（服务启动、连接池、数据库、事务等）

三、数据库配置、代码文件创建

1、application.properties文件

#服务端口
server.port=9080

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=50
spring.datasource.maxWait=60000
spring.datasource.timeBetweenEvictionRunsMillis=60000
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.filters=stat,wall,log4j2
spring.datasource.useGlobalDataSourceStat=false
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
#myself_db
spring.datasource.myselfdb.url=jdbc:mysql://ip地址:3306/myself_db?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
spring.datasource.myselfdb.username=****
spring.datasource.myselfdb.password=******

#demo_db
spring.datasource.demodb.url=jdbc:mysql://ip地址:3306/demo_db?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
spring.datasource.demodb.username=****
spring.datasource.demodb.password=******

注意：使用alibaba的数据连接池，需要引入依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.14</version>
</dependency>

2、多数据源JdbcTemplate配置 DataSourceConfig.java

package com.example.demo.config;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
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.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {

    @Bean(name="myselfDbDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.myselfdb")
    public DataSource myselfDbDataSource(){
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name="demoDbDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.demodb")
    public DataSource demoDbDataSource(){
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "myselfDbJdbcTemplate")
    public NamedParameterJdbcTemplate myselfDbJdbcTemplate(@Qualifier("myselfDbDataSource") DataSource dataSource){
        return new NamedParameterJdbcTemplate(dataSource);
    }

    @Bean(name = "demoDbJdbcTemplate" )
    public NamedParameterJdbcTemplate demoDbJdbcTemplate(@Qualifier("demoDbDataSource") DataSource dataSource){
        return new NamedParameterJdbcTemplate(dataSource);
    }

}

3、数据库对应的实体类 pojo目录中

package com.example.demo.pojo.demo_db;

import lombok.Data;

@Data
public class Course {
    private Integer id;
    private String name;
    private String teacher;
    private Integer type;
    private String work;
}

package com.example.demo.pojo.myself_db;

import lombok.Data;

@Data
public class User {
    private Integer id;
    private String name;
    private Integer sex;
    private Integer age;
}

4、控制层 Controller

package com.example.demo.controller;

import com.example.demo.pojo.demo_db.Course;
import com.example.demo.service.CourseService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/demoDb/course")
public class CourseController {

    @Autowired
    private CourseService courseService;

    @RequestMapping(value = "/queryCourseList" , method = RequestMethod.GET)
    public List<Course> queryCourseList(){
        return courseService.queryCourseList();
    }

}

package com.example.demo.controller;

import com.example.demo.pojo.myself_db.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/myselfDb/user")
public class UserController {


    @Autowired
    private UserService userService;

    @RequestMapping(value = "/findUserList", method = RequestMethod.GET)
    public List<User> findUserList(){
        return userService.findUserList();
    }
}

5、service以及service实现目录

接口

package com.example.demo.service;

import com.example.demo.pojo.demo_db.Course;

import java.util.List;

public interface CourseService {

    List<Course> queryCourseList();
}

package com.example.demo.service;

import com.example.demo.pojo.myself_db.User;

import java.util.List;

public interface UserService {

    List<User> findUserList();
}

实现

package com.example.demo.service.impl;

import com.example.demo.dao.CourseDao;
import com.example.demo.pojo.demo_db.Course;
import com.example.demo.service.CourseService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class CourseServiceImpl implements CourseService {

    @Autowired
    private CourseDao courseDao;

    @Override
    public List<Course> queryCourseList() {
        return courseDao.queryCourseList();
    }
}

package com.example.demo.service.impl;

import com.example.demo.dao.UserDao;
import com.example.demo.pojo.myself_db.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDao userDao;

    @Override
    public List<User> findUserList() {
        return userDao.findUserList();
    }
}

6、dao层，操作数据库

接口

package com.example.demo.dao;

import com.example.demo.pojo.demo_db.Course;

import java.util.List;

public interface CourseDao {

    List<Course> queryCourseList();
}

package com.example.demo.dao;

import com.example.demo.pojo.myself_db.User;

import java.util.List;

public interface UserDao {

    List<User> findUserList();
}

实现

package com.example.demo.dao.impl;

import com.example.demo.dao.CourseDao;
import com.example.demo.pojo.demo_db.Course;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class CourseDaoImpl implements CourseDao {

    @Autowired
    @Qualifier("demoDbJdbcTemplate")
    private NamedParameterJdbcTemplate jdbcTemplate;

    @Override
    public List<Course> queryCourseList() {
        String sql = " select *  from course ";
        return jdbcTemplate.query(sql,new MapSqlParameterSource(),new BeanPropertyRowMapper<>(Course.class));
    }
}

package com.example.demo.dao.impl;

import com.example.demo.dao.UserDao;
import com.example.demo.pojo.myself_db.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class UserDaoImpl implements UserDao {

    @Autowired
    @Qualifier("myselfDbJdbcTemplate")
    private NamedParameterJdbcTemplate jdbcTemplate;

    @Override
    public List<User> findUserList() {

        String sql = "select * from user ";

        return jdbcTemplate.query(sql,new MapSqlParameterSource(),new BeanPropertyRowMapper<>(User.class));
    }
}