6.SpringBoot连接数据库

SpringBoot3

給妳一生緈諨

1341人浏览 · 2025-04-26 01:08:26

給妳一生緈諨 · 2025-04-26 01:08:26 发布

六、连接数据库

1、jdbcTemplate连接数据库

6.1.1、连接单个数据库

(1)、项目结构

在这里插入图片描述

(2)、pom文件

<?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>3.4.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>com.longdidi</groupId>
    <artifactId>springboot-06-001</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <properties>
        <java.version>21</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>

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

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
        </dependency>

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

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

</project>

(3)、配置数据源

spring.datasource.url=jdbc:mysql://localhost:3306/springboot
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

(4)、添加实体类

package com.longdidi.jdbc;

import lombok.Data;

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

(5)、添加接口

package com.longdidi.jdbc;

public interface UserService {
    // 写入数据
    int saveStudent();
}

(6)、添加接口实现

package com.longdidi.jdbc;

import jakarta.annotation.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Service
public class UserServiceImpl implements UserService {
    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public int saveStudent() {
        String name = "张三";
        Integer age = 12;
        // 执行写入
        int row = jdbcTemplate.update("INSERT INTO t_user(name,age)VALUES(?,?);", "李四", 12);
        // 返回结果
        return row;
    }

}

(7)、添加测试类

package com.longdidi.jdbc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    /**
     * 新增数据
     */
    @RequestMapping("/save")
    public String save() {
        int row = userService.saveStudent();
        // 判断结果
        if (row == -1) {
            return "新增失败";
        } else {
            return "新增成功";
        }
    }
}

(8)、测试访问

在这里插入图片描述

6.1.2、连接多个数据库

6.1.2.1、项目结构

在这里插入图片描述

6.1.2.2、脚本文件

test1数据库

/*
 Navicat Premium Dump SQL

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 80041 (8.0.41)
 Source Host           : localhost:3306
 Source Schema         : test1

 Target Server Type    : MySQL
 Target Server Version : 80041 (8.0.41)
 File Encoding         : 65001

 Date: 24/02/2025 16:11:48
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三');
INSERT INTO `user` VALUES (2, '李四');

SET FOREIGN_KEY_CHECKS = 1;

test2数据库

/*
 Navicat Premium Dump SQL

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 80041 (8.0.41)
 Source Host           : localhost:3306
 Source Schema         : test2

 Target Server Type    : MySQL
 Target Server Version : 80041 (8.0.41)
 File Encoding         : 65001

 Date: 24/02/2025 16:11:58
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `deptname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (1, '营销部');
INSERT INTO `dept` VALUES (2, '运维部');

SET FOREIGN_KEY_CHECKS = 1;

6.1.2.3、pom依赖

<?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>3.4.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>com.longdidi</groupId>
    <artifactId>springboot-06-002</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <properties>
        <java.version>21</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
        </dependency>

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

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

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

    </dependencies>

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

</project>

6.1.2.4、配置数据源

spring.datasource1.url=jdbc:mysql://localhost:3306/test1
spring.datasource1.username=root
spring.datasource1.password=root
spring.datasource1.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource2.url=jdbc:mysql://localhost:3306/test2
spring.datasource2.username=root
spring.datasource2.password=root
spring.datasource2.driver-class-name=com.mysql.cj.jdbc.Driver

6.1.2.5、数据源配置类

package com.longdidi.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
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.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {
    /**
     * 数据源1的属性读取
     *
     * @return
     */
    @Primary
    @Bean(name = "propertiesDatasourceOne")
    @Qualifier("propertiesDatasourceOne")
    @ConfigurationProperties(prefix = "spring.datasource1")
    public DataSourceProperties propertiesDatasourceOne() {
        return new DataSourceProperties();
    }

    /**
     * 数据源1
     *
     * @return
     * @paramdataSourceProperties
     */
    @Primary
    @Bean(name = "oneDataSource")
    @Qualifier("oneDataSource")
    public DataSource oneDataSource(@Qualifier("propertiesDatasourceOne") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

    /**
     * 数据源1的JDBC模板
     */
    @Bean(name = "oneJdbcTemplate")
    @Qualifier("oneJdbcTemplate")
    public JdbcTemplate oneJdbcTemplate(@Qualifier("oneDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    /**
     * 数据源2的属性读取
     *
     * @return
     */
    @Bean(name = "dataSourcePropertiesTwo")
    @Qualifier("dataSourcePropertiesTwo")
    @ConfigurationProperties(prefix = "spring.datasource2")
    public DataSourceProperties dataSourcePropertiesTwo() {
        return new DataSourceProperties();
    }

    /**
     * 数据源2
     *
     * @return
     */
    @Bean(name = "twoDataSource")
    @Qualifier("twoDataSource")
    public DataSource twoDataSource(@Qualifier("dataSourcePropertiesTwo") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

    /**
     * 数据源2的JDBC模板
     */
    @Bean(name = "twoJdbcTemplate")
    @Qualifier("twoJdbcTemplate")
    public JdbcTemplate twoJdbcTemplate(@Qualifier("twoDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

}

6.1.2.6、数据源1业务

(1)、实体类

package com.longdidi.one.entity;

import lombok.Data;

@Data
public class User {
    private int id;

    private String name;

}

(2)、业务接口

package com.longdidi.one.service;

import com.longdidi.one.entity.User;

import java.util.List;

public interface UserService {
    List<User> getUserById(int id);
}

(3)、接口实现

package com.longdidi.one.service.impl;

import com.longdidi.one.entity.User;
import com.longdidi.one.service.UserService;
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.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    @Qualifier("oneJdbcTemplate")
    protected JdbcTemplate oneJdbcTemplate;

    @Override
    public List<User> getUserById(int id) {
        List<User> list = oneJdbcTemplate.query("select * from user where id=?", new BeanPropertyRowMapper<>(User.class), id);
        return list;
    }

}

(4)、测试类

package com.longdidi.one.controller;

import com.longdidi.one.entity.User;
import com.longdidi.one.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
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
public class OneController {
    @Autowired
    private UserService userService;

    @RequestMapping(value = "getUserById/{id}", method = RequestMethod.GET)
    public String getUserById(@PathVariable int id) {
        List<User> list = userService.getUserById(id);
        if (list != null && list.size() > 0) {
            return list.toString();
        }
        return "未查询到数据";
    }
}

6.1.2.7、数据源2业务

(1)、实体类

package com.longdidi.two.entity;

import lombok.Data;

@Data
public class Dept {
    private int id;
    private String deptname;
}

(2)、业务接口

package com.longdidi.two.service;

import com.longdidi.two.entity.Dept;

import java.util.List;

public interface DeptService {
    List<Dept> getTwoDeptById(int id);
}

(3)、接口实现

package com.longdidi.two.service.impl;

import com.longdidi.two.entity.Dept;
import com.longdidi.two.service.DeptService;
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.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class DeptServiceImpl implements DeptService {
    @Autowired
    @Qualifier("twoJdbcTemplate")
    protected JdbcTemplate twoJdbcTemplate;

    @Override
    public List<Dept> getTwoDeptById(int id) {
        List<Dept> list = twoJdbcTemplate.query("select * from dept where id=?", new BeanPropertyRowMapper<>(Dept.class), id);
        return list;
    }
}

(4)、测试类

package com.longdidi.two.controller;

import com.longdidi.two.entity.Dept;
import com.longdidi.two.service.DeptService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
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
public class TwoController {

    @Autowired
    private DeptService deptService;

    @RequestMapping(value = "getTwoDeptById/{id}", method = RequestMethod.GET)
    public String getTwoDeptById(@PathVariable int id) {

        List<Dept> list = deptService.getTwoDeptById(id);
        if (list != null && list.size() > 0) {
            return list.toString();
        }
        return "未查到数据";
    }
}

6.1.2.8、测试访问

http://localhost:8080/getUserById/1

在这里插入图片描述

http://localhost:8080/getTwoDeptById/1

在这里插入图片描述

2、Mybatis连接数据库

6.2.1、连接单个数据库

6.2.1.1、项目结构

在这里插入图片描述

6.2.1.2、脚本文件

/*
 Navicat Premium Dump SQL

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 80041 (8.0.41)
 Source Host           : localhost:3306
 Source Schema         : springboot

 Target Server Type    : MySQL
 Target Server Version : 80041 (8.0.41)
 File Encoding         : 65001

 Date: 24/02/2025 16:44:31
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for vip
-- ----------------------------
DROP TABLE IF EXISTS `vip`;
CREATE TABLE `vip`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `card_number` int NULL DEFAULT NULL,
  `birth` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of vip
-- ----------------------------
INSERT INTO `vip` VALUES (1, '张三', 111, '20250101');
INSERT INTO `vip` VALUES (2, '李四', 222, '20251212');

SET FOREIGN_KEY_CHECKS = 1;

6.2.1.3、pom依赖

<?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>3.4.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.longdidi</groupId>
    <artifactId>springboot-06-003</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <properties>
        <java.version>21</java.version>
    </properties>
    <dependencies>
        <!--mybatis启动器-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--mysql驱动-->
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter-test</artifactId>
            <version>3.0.3</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

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

</project>

6.2.1.4、配置数据源

# 数据源的配置
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/springboot
spring.datasource.username=root
spring.datasource.password=root
# mybatis相关配置
# 1. 起别名
mybatis.type-aliases-package=com.longdidi.entity
# 2. 告诉springboot mapper.xml文件在哪里
mybatis.mapper-locations=classpath:/mapper/*.xml
# 3. 启用自动映射数据库表的列名和bean的属性名
mybatis.configuration.map-underscore-to-camel-case=true

6.2.1.5、实体类

package com.longdidi.entity;

import lombok.Data;

@Data
public class Vip {
    private Long id;
    private String name;
    private String cardNumber; // 数据库字段 card_number
    private String birth;
}

6.2.1.6、Mapper

Mapper.java

package com.longdidi.mapper;

import com.longdidi.entity.Vip;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface VipMapper {

    /**
     * 获取所有会员信息
     *
     * @return 会员列表
     */
    List<Vip> getVipList();
}

Mapper.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.longdidi.mapper.VipMapper">

    <select id="getVipList" resultType="Vip">
        select *
        from vip
    </select>

</mapper>

6.2.1.7、业务接口

package com.longdidi.service;


import com.longdidi.entity.Vip;

import java.util.List;

public interface VipService {

    /**
     * 查看会员列表
     *
     * @return 会员列表
     */
    List<Vip> getVipList();

}

6.2.1.8、接口实现

package com.longdidi.service.impl;

import com.longdidi.entity.Vip;
import com.longdidi.mapper.VipMapper;
import com.longdidi.service.VipService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class VipServiceImpl implements VipService {

    @Autowired
    private VipMapper vipMapper;

    @Override
    public List<Vip> getVipList() {
        return vipMapper.getVipList();
    }
}

6.2.1.9、测试类

package com.longdidi.controller;

import com.longdidi.entity.Vip;
import com.longdidi.service.VipService;
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
public class VipController {

    @Autowired
    private VipService vipService;

    @RequestMapping(value = "getVipList", method = RequestMethod.GET)
    public String getVipList() {
        List<Vip> list = vipService.getVipList();
        if (list != null && list.size() > 0) {
            return list.toString();
        }

        return "未查询到数据";
    }
}

6.2.1.10、测试访问

http://localhost:8080/getVipList

在这里插入图片描述

6.2.2、连接多个数据库

6.2.2.1、项目结构

在这里插入图片描述

6.2.2.1、脚本文件

test1数据库

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三');
INSERT INTO `user` VALUES (2, '李四');

SET FOREIGN_KEY_CHECKS = 1;

test2数据库

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `deptname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (1, '营销部');
INSERT INTO `dept` VALUES (2, '运维部');

SET FOREIGN_KEY_CHECKS = 1;

6.2.2.3、pom依赖

<?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>3.4.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>com.longdidi</groupId>
    <artifactId>springboot-06-004</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <properties>
        <java.version>21</java.version>
    </properties>

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

        <!--mybatis启动器-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--mysql驱动-->
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter-test</artifactId>
            <version>3.0.3</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

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

</project>

6.2.2.4、数据源配置类

数据源1

package com.longdidi.config;

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.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


@Configuration
@MapperScan(basePackages = "com.longdidi.mapper.db1", sqlSessionFactoryRef = "db1SqlSessionFactory")
public class DataSourceConfig1 {
    /**
     * 返回data1数据库的数据源
     *
     * @return
     */
    @Primary // 表示这个数据源是默认数据源, 这个注解必须要加，因为不加的话spring将分不清楚那个为主数据源（默认数据源）
    @Bean("db1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db1") //读取application.yml中的配置参数映射成为一个对象
    public DataSource getDb1DataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 返回data1数据库的会话工厂
     *
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Primary
    @Bean("db1SqlSessionFactory")
    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        // mapper的xml形式文件位置必须要配置，不然将报错：no statement （这种错误也可能是mapper的xml中，namespace与项目的路径不一致导致）
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/db1/*.xml"));
        return bean.getObject();
    }

    /**
     * 返回data1数据库的会话模板
     *
     * @param sqlSessionFactory
     * @return
     * @throws Exception
     */
    @Primary
    @Bean("db1SqlSessionTemplate")
    public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    /**
     * 返回data1数据库的事务
     *
     * @param ds
     * @return
     */
    @Bean(name = "db1TransactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(@Qualifier("db1DataSource") DataSource ds) {
        return new DataSourceTransactionManager(ds);
    }
}

数据源2

package com.longdidi.config;

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.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.longdidi.mapper.db2", sqlSessionFactoryRef = "db2SqlSessionFactory")
public class DataSourceConfig2 {
    /**
     * 返回data2数据库的数据源
     *
     * @return
     */
    @Bean("db2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource getDb1DataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 返回data2数据库的会话工厂
     *
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean("db2SqlSessionFactory")
    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/db2/*.xml"));
        return bean.getObject();
    }

    /**
     * 返回data2数据库的会话模板
     *
     * @param sqlSessionFactory
     * @return
     * @throws Exception
     */
    @Bean("db2SqlSessionTemplate")
    public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    /**
     * 返回data2数据库的事务
     *
     * @param ds
     * @return
     */
    @Bean(name = "db2TransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("db2DataSource") DataSource ds) {
        return new DataSourceTransactionManager(ds);
    }
}

6.2.2.5、数据源配置

server:
  port: 8080
spring:
  datasource:
    db1:
      jdbc-url: jdbc:mysql://127.0.0.1:3306/test1
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver
    db2:
      jdbc-url: jdbc:mysql://127.0.0.1:3306/test2
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver

6.2.2.6、数据源1业务

(1)、实体类

package com.longdidi.entity.db1;

import lombok.Data;

@Data
public class User {
    private int id;

    private String name;
}

(2)、DAO层

UserMapper.java

package com.longdidi.mapper.db1;

import com.longdidi.entity.db1.User;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserMapper {
    User findUserById(int id);//查找数据
}

UserMapper.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.longdidi.mapper.db1.UserMapper">
    <!-- find-->
    <select id="findUserById" parameterType="int" resultType="com.longdidi.entity.db1.User">
        SELECT *
        FROM user
        WHERE ID = #{id}
    </select>
</mapper>

(3)、业务类

package com.longdidi.service.db1;

import com.longdidi.mapper.db1.UserMapper;
import jakarta.annotation.Resource;
import org.springframework.stereotype.Service;
import com.longdidi.entity.db1.User;

@Service
public class UserService {
    @Resource
    private UserMapper userMapper;

    public User findUserById(int id) {
        return userMapper.findUserById(id);
    }
}

(4)、测试类

package com.longdidi.controller;

import com.longdidi.service.db1.UserService;
import jakarta.annotation.Resource;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.longdidi.entity.db1.User;

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

    @RequestMapping("/findUserById")
    public String findUserById() {
        User t = userService.findUserById(1);
        if (t != null) {
            return t.getName() + ";" + t.getId();
        }
        return "空数据";
    }
}

6.2.2.7、数据源2业务

(1)、实体类

package com.longdidi.entity.db2;

import lombok.Data;

@Data
public class Dept {
    private int id;

    private String deptname;

}

(2)、DAO层

DeptMapper.java

package com.longdidi.mapper.db2;

import com.longdidi.entity.db2.Dept;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface DeptMapper {
    Dept findDeptById(int id);//查找数据
}

DeptMapper.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.longdidi.mapper.db2.DeptMapper">
    <select id="findDeptById" parameterType="int" resultType="com.longdidi.entity.db2.Dept">
        SELECT *
        FROM dept
        WHERE ID = #{id}
    </select>
</mapper>

(3)、业务类

package com.longdidi.service.db2;

import com.longdidi.entity.db2.Dept;
import com.longdidi.mapper.db2.DeptMapper;
import jakarta.annotation.Resource;
import org.springframework.stereotype.Service;

@Service
public class DeptService {
    @Resource
    private DeptMapper deptMapper;

    public Dept findDeptById(int id) {
        return deptMapper.findDeptById(id);
    }
}

(4)、测试类

package com.longdidi.controller;

import com.longdidi.entity.db2.Dept;
import com.longdidi.service.db2.DeptService;
import jakarta.annotation.Resource;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class DeptController {
    @Resource
    private DeptService deptService;

    @RequestMapping("/findDeptById")
    public String findDeptById() {
        Dept t = deptService.findDeptById(1);
        if (t != null) {
            return t.getDeptname();
        }
        return "空数据";
    }
}

6.2.2.8、测试访问

http://localhost:8080/findUserById

在这里插入图片描述

http://localhost:8080/findDeptById

在这里插入图片描述

3、JPA连接数据库

6.3.1、脚本文件

test1库

/*
 Navicat Premium Dump SQL

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 80041 (8.0.41)
 Source Host           : localhost:3306
 Source Schema         : test1

 Target Server Type    : MySQL
 Target Server Version : 80041 (8.0.41)
 File Encoding         : 65001

 Date: 24/02/2025 17:12:36
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三');
INSERT INTO `user` VALUES (2, '李四');

SET FOREIGN_KEY_CHECKS = 1;

test2库

/*
 Navicat Premium Dump SQL

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 80041 (8.0.41)
 Source Host           : localhost:3306
 Source Schema         : test2

 Target Server Type    : MySQL
 Target Server Version : 80041 (8.0.41)
 File Encoding         : 65001

 Date: 24/02/2025 17:12:43
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `deptname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (1, '营销部');
INSERT INTO `dept` VALUES (2, '运维部');

SET FOREIGN_KEY_CHECKS = 1;

6.3.2、项目结构

在这里插入图片描述

6.3.3、pom依赖

<?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>3.4.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>com.longdidi</groupId>
    <artifactId>springboot-06-005</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <properties>
        <java.version>21</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--mysql驱动-->
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

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

</project>

6.3.4、数据源配置

spring.datasource1.url=jdbc:mysql://localhost:3306/test1
spring.datasource1.username=root
spring.datasource1.password=root
spring.datasource1.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource2.url=jdbc:mysql://localhost:3306/test2
spring.datasource2.username=root
spring.datasource2.password=root
spring.datasource2.driver-class-name=com.mysql.cj.jdbc.Driver

# 配置jpa
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.hbm2ddl.auto=update
#spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

6.3.5、配置数据源

(1)、数据源1配置

OneDataSourceConfig.java

package com.longdidi.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
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 javax.sql.DataSource;

@Configuration
public class OneDataSourceConfig {
    /**
     * 数据源1的属性读取
     *
     * @return
     */
    @Primary
    @Bean(name = "propertiesDatasourceOne")
    @Qualifier("propertiesDatasourceOne")
    @ConfigurationProperties(prefix = "spring.datasource1")
    public DataSourceProperties propertiesDatasourceOne() {
        return new DataSourceProperties();
    }

    /**
     * 数据源1
     *
     * @return
     * @paramdataSourceProperties
     */
    @Primary
    @Bean(name = "oneDataSource")
    @Qualifier("oneDataSource")
    public DataSource oneDataSource(@Qualifier("propertiesDatasourceOne") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

}

OneJpaConfig.java

package com.longdidi.config;

import jakarta.annotation.Resource;
import jakarta.persistence.EntityManager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

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

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactoryOne", transactionManagerRef = "transactionManagerOne", basePackages = {
        "com.longdidi.one.repository"}) // repository的目录
public class OneJpaConfig {

    @Autowired
    @Qualifier("oneDataSource")
    private DataSource oneDataSource;

    @Primary
    @Bean(name = "entityManagerOne")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryOne(builder).getObject().createEntityManager();
    }

    @Resource
    private JpaProperties jpaProperties;

    private Map<String, String> getVendorProperties() {
        return jpaProperties.getProperties();
    }

    @Primary
    @Bean(name = "entityManagerFactoryOne")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryOne(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(oneDataSource).packages("com.longdidi.one.entity")// 实体类的目录
                .persistenceUnit("onePersistenceUnit").properties(getVendorProperties()).build();
    }

    @Primary
    @Bean(name = "transactionManagerOne")
    public PlatformTransactionManager transactionManagerOne(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryOne(builder).getObject());
    }

}

(2)、数据源2配置

TwoDataSourceConfig.java

package com.longdidi.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
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 TwoDataSourceConfig {

    /**
     * 数据源2的属性读取
     *
     * @return
     */
    @Bean(name = "dataSourcePropertiesTwo")
    @Qualifier("dataSourcePropertiesTwo")
    @ConfigurationProperties(prefix = "spring.datasource2")
    public DataSourceProperties dataSourcePropertiesTwo() {
        return new DataSourceProperties();
    }

    /**
     * 数据源2
     *
     * @return
     */
    @Bean(name = "twoDataSource")
    @Qualifier("twoDataSource")
    public DataSource twoDataSource(@Qualifier("dataSourcePropertiesTwo") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

}

TwoJpaConfig.java

package com.longdidi.config;

import jakarta.annotation.Resource;
import jakarta.persistence.EntityManager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

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

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactoryTwo", transactionManagerRef = "transactionManagerTwo", basePackages = {
        "com.longdidi.two.repository"}) // repository的目录
public class TwoJpaConfig {
    @Autowired
    @Qualifier("twoDataSource")
    private DataSource twoDataSource;

    @Resource
    private JpaProperties jpaProperties;

    private Map<String, String> getVendorProperties() {
        return jpaProperties.getProperties();
    }

    @Bean(name = "entityManagerFactoryTwo")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryTwo(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(twoDataSource).packages("com.longdidi.two.entity")// 实体类的目录
                .persistenceUnit("twoPersistenceUnit").properties(getVendorProperties()).build();
    }

    @Bean(name = "entityManagerTwo")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryTwo(builder).getObject().createEntityManager();
    }

    @Bean(name = "transactionManagerTwo")
    public PlatformTransactionManager transactionManagerTwo(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryTwo(builder).getObject());
    }

}

6.3.6、数据源1业务

(1)、测试接口

package com.longdidi.one.controller;

import com.longdidi.one.entity.User;
import com.longdidi.one.service.OneJpaService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class OneJpaController {
    @Autowired
    private OneJpaService oneJpaService;

    @RequestMapping("getOneJpaUserById/{id}")
    public String getOneJpaUserById(@PathVariable int id) {
        User user = oneJpaService.getOneJpaUserById(id);
        if (user != null) {
            return user.toString();
        }
        return "没有数据";
    }
}

(2)、实体类

package com.longdidi.one.entity;


import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.Data;

@Entity
@Data
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
}

(3)、DAO层

package com.longdidi.one.repository;

import com.longdidi.one.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

@Repository
public interface UserRepository extends JpaRepository<User, Integer> {
    @Query(value = "select * from user where id=?", nativeQuery = true)
    User getOneJpaUserById(int id);
}

(4)、业务接口

接口

package com.longdidi.one.service;

import com.longdidi.one.entity.User;

public interface OneJpaService {
    User getOneJpaUserById(int id);
}

接口实现

package com.longdidi.one.service.impl;

import com.longdidi.one.entity.User;
import com.longdidi.one.repository.UserRepository;
import com.longdidi.one.service.OneJpaService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class OneJpaServiceImpl implements OneJpaService {

    @Autowired
    private UserRepository userRepository;

    @Override
    public User getOneJpaUserById(int id) {
        return userRepository.getOneJpaUserById(id);
    }
}

6.3.7、数据源2业务

(1)、测试接口

package com.longdidi.two.controller;

import com.longdidi.two.entity.Dept;
import com.longdidi.two.service.TwoJpaService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class TwoJpaController {

    @Autowired
    private TwoJpaService twoJpaService;

    @RequestMapping("getTwoJpaDeptById/{id}")
    public String getTwoJpaDeptById(@PathVariable int id) {

        Dept dept = twoJpaService.getTwoJpaDeptById(id);
        if (dept != null) {
            return dept.toString();
        }
        return "没有数据";
    }

}

(2)、实体类

package com.longdidi.two.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.Data;


@Entity
@Data
public class Dept {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    private String deptname;

}

(3)、DAO层

package com.longdidi.two.repository;

import com.longdidi.two.entity.Dept;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

@Repository
public interface DeptRepository extends JpaRepository<Dept, Integer> {
    @Query(value = "select * from dept where id=?", nativeQuery = true)
    Dept getTwoJpaDeptById(int id);
}

(4)、业务接口

接口

package com.longdidi.two.service;


import com.longdidi.two.entity.Dept;

public interface TwoJpaService {
    Dept getTwoJpaDeptById(int id);
}

接口实现

package com.longdidi.two.service.impl;

import com.longdidi.two.entity.Dept;
import com.longdidi.two.repository.DeptRepository;
import com.longdidi.two.service.TwoJpaService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class TwoJpaServiceImpl implements TwoJpaService {

    @Autowired
    private DeptRepository deptRepository;

    @Override
    public Dept getTwoJpaDeptById(int id) {
        return deptRepository.getTwoJpaDeptById(id);
    }

}

6.3.8、测试

http://localhost:8080/getOneJdbcUserList

在这里插入图片描述

http://localhost:8080/getTwoJpaDeptById/1

在这里插入图片描述

4、多库多框架整合

6.4.1、项目结构

在这里插入图片描述

6.4.2、脚本文件

test1库

/*
 Navicat Premium Dump SQL

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 80041 (8.0.41)
 Source Host           : localhost:3306
 Source Schema         : test1

 Target Server Type    : MySQL
 Target Server Version : 80041 (8.0.41)
 File Encoding         : 65001

 Date: 24/02/2025 17:12:36
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三');
INSERT INTO `user` VALUES (2, '李四');

SET FOREIGN_KEY_CHECKS = 1;

test2库

/*
 Navicat Premium Dump SQL

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 80041 (8.0.41)
 Source Host           : localhost:3306
 Source Schema         : test2

 Target Server Type    : MySQL
 Target Server Version : 80041 (8.0.41)
 File Encoding         : 65001

 Date: 24/02/2025 17:12:43
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `deptname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (1, '营销部');
INSERT INTO `dept` VALUES (2, '运维部');

SET FOREIGN_KEY_CHECKS = 1;

6.4.3、pom依赖

<?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>3.4.3</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.longdidi</groupId>
    <artifactId>springboot-06-006</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <properties>
        <java.version>21</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--mysql驱动-->
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

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

</project>

6.4.4、数据源配置

spring.datasource1.url=jdbc:mysql://localhost:3306/test1
spring.datasource1.username=root
spring.datasource1.password=root
spring.datasource1.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource2.url=jdbc:mysql://localhost:3306/test2
spring.datasource2.username=root
spring.datasource2.password=root
spring.datasource2.driver-class-name=com.mysql.cj.jdbc.Driver

# 配置jpa
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.hbm2ddl.auto=update

6.4.5、配置数据源

DataSourceConfig.java

package com.longdidi.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
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.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {
    /**
     * 数据源1的属性读取
     *
     * @return
     */
    @Primary
    @Bean(name = "propertiesDatasourceOne")
    @Qualifier("propertiesDatasourceOne")
    @ConfigurationProperties(prefix = "spring.datasource1")
    public DataSourceProperties propertiesDatasourceOne() {
        return new DataSourceProperties();
    }

    /**
     * 数据源1
     *
     * @return
     * @paramdataSourceProperties
     */
    @Primary
    @Bean(name = "oneDataSource")
    @Qualifier("oneDataSource")
    public DataSource oneDataSource(@Qualifier("propertiesDatasourceOne") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

    /**
     * 数据源1的JDBC模板
     */
    @Bean(name = "oneJdbcTemplate")
    @Qualifier("oneJdbcTemplate")
    public JdbcTemplate oneJdbcTemplate(@Qualifier("oneDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    /**
     * 数据源2的属性读取
     *
     * @return
     */
    @Bean(name = "dataSourcePropertiesTwo")
    @Qualifier("dataSourcePropertiesTwo")
    @ConfigurationProperties(prefix = "spring.datasource2")
    public DataSourceProperties dataSourcePropertiesTwo() {
        return new DataSourceProperties();
    }

    /**
     * 数据源2
     *
     * @return
     */
    @Bean(name = "twoDataSource")
    @Qualifier("twoDataSource")
    public DataSource twoDataSource(@Qualifier("dataSourcePropertiesTwo") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

    /**
     * 数据源2的JDBC模板
     */
    @Bean(name = "twoJdbcTemplate")
    @Qualifier("twoJdbcTemplate")
    public JdbcTemplate twoJdbcTemplate(@Qualifier("twoDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

}

OneJpaConfig.java

package com.longdidi.config;

import jakarta.annotation.Resource;
import jakarta.persistence.EntityManager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

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

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactoryOne", transactionManagerRef = "transactionManagerOne", basePackages = {
        "com.longdidi.one.repository"}) // repository的目录
public class OneJpaConfig {

    @Autowired
    @Qualifier("oneDataSource")
    private DataSource oneDataSource;

    @Primary
    @Bean(name = "entityManagerOne")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryOne(builder).getObject().createEntityManager();
    }

    @Resource
    private JpaProperties jpaProperties;

    private Map<String, String> getVendorProperties() {
        return jpaProperties.getProperties();
    }

    @Primary
    @Bean(name = "entityManagerFactoryOne")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryOne(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(oneDataSource).packages("com.longdidi.one.entity")// 实体类的目录
                .persistenceUnit("onePersistenceUnit").properties(getVendorProperties()).build();
    }

    @Primary
    @Bean(name = "transactionManagerOne")
    public PlatformTransactionManager transactionManagerOne(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryOne(builder).getObject());
    }

}

TwoJpaConfig.java

package com.longdidi.config;

import jakarta.annotation.Resource;
import jakarta.persistence.EntityManager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

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

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactoryTwo", transactionManagerRef = "transactionManagerTwo", basePackages = {
        "com.longdidi.two.repository"}) // repository的目录
public class TwoJpaConfig {
    @Autowired
    @Qualifier("twoDataSource")
    private DataSource twoDataSource;

    @Resource
    private JpaProperties jpaProperties;

    private Map<String, String> getVendorProperties() {
        return jpaProperties.getProperties();
    }

    @Bean(name = "entityManagerFactoryTwo")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryTwo(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(twoDataSource).packages("com.longdidi.two.entity")// 实体类的目录
                .persistenceUnit("twoPersistenceUnit").properties(getVendorProperties()).build();
    }

    @Bean(name = "entityManagerTwo")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryTwo(builder).getObject().createEntityManager();
    }

    @Bean(name = "transactionManagerTwo")
    public PlatformTransactionManager transactionManagerTwo(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryTwo(builder).getObject());
    }

}

6.4.6、数据源1业务

6.4.6.1、测试接口

JDBC测试接口

package com.longdidi.one.controller;

import com.longdidi.one.entity.User;
import com.longdidi.one.service.OneJdbcService;
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
public class OneJdbcController {
    @Autowired
    private OneJdbcService oneJdbcService;

    @RequestMapping(value = "getOneJdbcUserList", method = RequestMethod.GET)
    public String getOneJdbcUserList() {
        List<User> list = oneJdbcService.getOneJdbcUserList();
        if (list != null && list.size() > 0) {
            return list.toString();
        }
        return "没有数据";
    }

}

JPA测试接口

package com.longdidi.one.controller;

import com.longdidi.one.entity.User;
import com.longdidi.one.service.OneJpaService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class OneJpaController {
    @Autowired
    private OneJpaService oneJpaService;

    @RequestMapping("getOneJpaUserById/{id}")
    public String getOneJpaUserById(@PathVariable int id) {
        User user = oneJpaService.getOneJpaUserById(id);
        if (user != null) {
            return user.toString();
        }
        return "没有数据";
    }
}

6.4.6.2、实体类

package com.longdidi.one.entity;


import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.Data;

@Entity
@Data
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
}

6.4.6.3、DAO层

package com.longdidi.one.repository;

import com.longdidi.one.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

@Repository
public interface UserRepository extends JpaRepository<User, Integer> {
    @Query(value = "select * from user where id=?", nativeQuery = true)
    User getOneJpaUserById(int id);
}

6.4.6.4、业务层

(1)、JDBC业务

OneJdbcService.java

package com.longdidi.one.service;

import com.longdidi.one.entity.User;

import java.util.List;

public interface OneJdbcService {
    List<User> getOneJdbcUserList();
}

OneJdbcServiceImpl.java

package com.longdidi.one.service.impl;

import com.longdidi.one.entity.User;
import com.longdidi.one.service.OneJdbcService;
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.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class OneJdbcServiceImpl implements OneJdbcService {

    @Autowired
    @Qualifier("oneJdbcTemplate")
    protected JdbcTemplate oneJdbcTemplate;

    @Override
    public List<User> getOneJdbcUserList() {
        List<User> list = oneJdbcTemplate.query("select * from user", new BeanPropertyRowMapper<>(User.class));
        return list;
    }

}

(2)、JPA业务

OneJpaService.java

package com.longdidi.one.service;

import com.longdidi.one.entity.User;

public interface OneJpaService {
    User getOneJpaUserById(int id);
}

OneJpaServiceImpl.java

package com.longdidi.one.service.impl;

import com.longdidi.one.entity.User;
import com.longdidi.one.repository.UserRepository;
import com.longdidi.one.service.OneJpaService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class OneJpaServiceImpl implements OneJpaService {

    @Autowired
    private UserRepository userRepository;

    @Override
    public User getOneJpaUserById(int id) {
        return userRepository.getOneJpaUserById(id);
    }
}

6.4.7、数据源2业务

6.4.7.1、测试接口

TwoJdbcController.java

package com.longdidi.two.controller;

import com.longdidi.two.entity.Dept;
import com.longdidi.two.service.TwoJdbcService;
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
public class TwoJdbcController {

    @Autowired
    private TwoJdbcService twoJdbcService;

    @RequestMapping(value = "getTwoJdbcDeptList", method = RequestMethod.GET)
    public String getTwoJdbcDeptList() {

        List<Dept> list = twoJdbcService.getTwoJdbcDeptList();
        if (list != null && list.size() > 0) {
            return list.toString();
        }
        return "未查到数据";
    }
}

TwoJpaController.java

package com.longdidi.two.controller;

import com.longdidi.two.entity.Dept;
import com.longdidi.two.service.TwoJpaService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class TwoJpaController {

    @Autowired
    private TwoJpaService twoJpaService;

    @RequestMapping("getTwoJpaDeptById/{id}")
    public String getTwoJpaDeptById(@PathVariable int id) {

        Dept dept = twoJpaService.getTwoJpaDeptById(id);
        if (dept != null) {
            return dept.toString();
        }
        return "没有数据";
    }

}

6.4.7.2、实体类

package com.longdidi.two.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.Data;


@Entity
@Data
public class Dept {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    private String deptname;

}

6.4.7.3、DAO层

package com.longdidi.two.repository;

import com.longdidi.two.entity.Dept;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

@Repository
public interface DeptRepository extends JpaRepository<Dept, Integer> {
    @Query(value = "select * from dept where id=?", nativeQuery = true)
    Dept getTwoJpaDeptById(int id);
}

6.4.7.4、业务层

(1)、JDBC业务

TwoJdbcService.java

package com.longdidi.two.service;

import com.longdidi.two.entity.Dept;

import java.util.List;

public interface TwoJdbcService {
    List<Dept> getTwoJdbcDeptList();
}

TwoJdbcServiceImpl.java

package com.longdidi.two.service.impl;

import com.longdidi.two.entity.Dept;
import com.longdidi.two.service.TwoJdbcService;
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.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class TwoJdbcServiceImpl implements TwoJdbcService {

    @Autowired
    @Qualifier("twoJdbcTemplate")
    protected JdbcTemplate twoJdbcTemplate;

    @Override
    public List<Dept> getTwoJdbcDeptList() {
        List<Dept> list = twoJdbcTemplate.query("select * from dept", new BeanPropertyRowMapper<>(Dept.class));
        return list;
    }

}

(2)、JPA业务

TwoJpaService.java

package com.longdidi.two.service;


import com.longdidi.two.entity.Dept;

public interface TwoJpaService {
    Dept getTwoJpaDeptById(int id);
}

TwoJpaServiceImpl.java

package com.longdidi.two.service.impl;

import com.longdidi.two.entity.Dept;
import com.longdidi.two.repository.DeptRepository;
import com.longdidi.two.service.TwoJpaService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class TwoJpaServiceImpl implements TwoJpaService {

    @Autowired
    private DeptRepository deptRepository;

    @Override
    public Dept getTwoJpaDeptById(int id) {
        return deptRepository.getTwoJpaDeptById(id);
    }

}