devbox
花生_TL007
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

springboot-mybatis整合多数据源之sqlserver_springboot 整合sqlserver 连接池

作者：花生_TL007 | 2024-05-01 23:01:22

赞

踩

springboot 整合sqlserver 连接池

环境： intellij idea 2017.1.4 + spring boot 2.0

代码文档结构图：

注：此代码中的db1,db2为mysql数据源相关，db3为sqlserver数据源

2 数据库配置相关类

先在application.properties 文件中添加数据库的配置

  1. #spring.datasource.driverClassName=com.mysql.jdbc.Driver
  2. #spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
  3. #spring.datasource.username=root
  4. #spring.datasource.password=
  5. #mybatis.mapper-locations: classpath:mapper/*.xml
  8. ## db1 database
  9. spring.datasource.db1.jdbc-url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
  10. spring.datasource.db1.username=root
  11. spring.datasource.db1.password=
  12. spring.datasource.db1.driver-class-name=com.mysql.jdbc.Driver
  13. ## db2 database
  14. spring.datasource.db2.jdbc-url=jdbc:mysql://localhost:3306/news?useUnicode=true&characterEncoding=UTF-8
  15. spring.datasource.db2.username=root
  16. spring.datasource.db2.password=
  17. spring.datasource.db2.driver-class-name=com.mysql.jdbc.Driver
  18. ## db3 database
  19. spring.datasource.db3.jdbc-url=jdbc:sqlserver://ip:port;DatabaseName=dbname
  20. spring.datasource.db3.username=dbaccountname
  21. spring.datasource.db3.password=dbaccountpassowrd
  22. spring.datasource.db3.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
  24. ## Redis数据库索引（默认为0）
  25. spring.redis.database=0  
  26. # Redis服务器地址
  27. spring.redis.host=127.0.0.1  
  28. # Redis服务器连接端口
  29. spring.redis.port=6379  
  30. # Redis服务器连接密码（默认为空）
  31. spring.redis.password=
  32. # 连接池最大连接数（使用负值表示没有限制）
  33. spring.redis.pool.max-active=200  
  34. # 连接池最大阻塞等待时间（使用负值表示没有限制）
  35. spring.redis.pool.max-wait=-1  
  36. # 连接池中的最大空闲连接
  37. spring.redis.pool.max-idle=10 
  38. # 连接池中的最小空闲连接
  39. spring.redis.pool.min-idle=0  
  40. # 连接超时时间（毫秒）
  41. spring.redis.timeout=3000

在datasouce目录下增加DataSourceConfig3文件,DataSourceConfig1,DataSourceConfig2略(资源代码里有),如下：

  1. package com.neo.datasource;
  2.  
  3.  
  4. import org.apache.ibatis.session.SqlSessionFactory;
  5. import org.mybatis.spring.SqlSessionFactoryBean;
  6. import org.mybatis.spring.annotation.MapperScan;
  7. import org.springframework.beans.factory.annotation.Qualifier;
  8. import org.springframework.boot.context.properties.ConfigurationProperties;
  9. import org.springframework.boot.jdbc.DataSourceBuilder;
  10. import org.springframework.context.annotation.Bean;
  11. import org.springframework.context.annotation.Configuration;
  12. import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
  13. import org.springframework.core.io.support.ResourcePatternResolver;
  14. import org.springframework.jdbc.core.JdbcTemplate;
  15. import org.springframework.jdbc.datasource.DataSourceTransactionManager;
  16. import org.springframework.transaction.PlatformTransactionManager;
  17.  
  18. import javax.sql.DataSource;
  19.  
  20. @Configuration
  21. @MapperScan(basePackages = "com.neo.mapper.db3", sqlSessionFactoryRef = "db3SqlSessionTemplate")
  22. public class DataSourceConfig3 {
  23.  
  24.     @Bean(name = "sqlServerDataSource")
  25.     @Qualifier("sqlServerDataSource")
  26.     @ConfigurationProperties(prefix="spring.datasource.db3")
  27.     public DataSource getMyDataSource(){
  28.         return DataSourceBuilder.create().build();
  29.     }
  30.  
  31.     @Bean(name = "secondaryJdbcTemplate")
  32.     public JdbcTemplate secondaryJdbcTemplate(
  33.             @Qualifier("sqlServerDataSource") DataSource dataSource) {
  34.         return new JdbcTemplate(dataSource);
  35.     }
  36.  
  37.     private static final String MAPPER_PATH = "classpath:mapper/db3/*.xml";
  38.  
  39.     private static final String ENTITY_PACKAGE = "com.neo.mapper.db3";
  40.     @Bean(name = "db3SqlSessionTemplate")
  41.     public SqlSessionFactory devSqlSessionFactory(
  42.             @Qualifier("sqlServerDataSource") DataSource ddataSource)
  43.             throws Exception {
  44.         final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
  45.         sessionFactory.setDataSource(ddataSource);
  46.         ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
  47.         sessionFactory.setMapperLocations(resolver.getResources(MAPPER_PATH));
  48.         sessionFactory.setTypeAliasesPackage(ENTITY_PACKAGE);
  49.         return sessionFactory.getObject();
  50.     }
  51.  
  52.     @Bean
  53.     public PlatformTransactionManager sqlServerTransactionManager(@Qualifier("sqlServerDataSource") DataSource sqlServerDataSource)
  54.     {
  55.         return new DataSourceTransactionManager(sqlServerDataSource);
  56.     }
  57.  
  58. }

实体表操作相关类文件

entity

  1. package com.neo.entity.db3;
  2.  
  3. import org.springframework.stereotype.Component;
  4.  
  5.  
  6. @Component
  7. public class City {
  8.     private int id;
  9.  
  10.     public int getId() {
  11.         return id;
  12.     }
  13.  
  14.     public void setId(int id) {
  15.         this.id = id;
  16.     }
  17.  
  18.     public String getName() {
  19.         return Name;
  20.     }
  21.  
  22.     public void setName(String name) {
  23.         Name = name;
  24.     }
  25.  
  26.     public int getParentId() {
  27.         return ParentId;
  28.     }
  29.  
  30.     public void setParentId(int parentId) {
  31.         ParentId = parentId;
  32.     }
  33.  
  34.     public int getSort() {
  35.         return Sort;
  36.     }
  37.  
  38.     public void setSort(int sort) {
  39.         Sort = sort;
  40.     }
  41.  
  42.     private String Name;
  43.     private int ParentId;
  44.     private  int Sort;
  45.  
  46.  
  47. }

resource/mapper/db3/city.xml

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://www.mybatis.org/dtd/mybatis-3-mapper.dtd">
  3. <mapper namespace="com.neo.mapper.db3.CityMapper">
  4.     <select id="getCity" resultType="com.neo.entity.db3.City">
  5.         select * from T_City
  6.         <where>
  7.             <if test="name != null">
  8.                 Name=#{name}
  9.             </if>
  10.  
  11.             <if test="id!= null">
  12.                 and id=#{id}
  13.             </if>
  14.         </where>
  15.     </select>
  16.     <delete id="deleteCity" parameterType="Integer">
  17.         delete from T_City where id =#{id}
  18.     </delete>
  19.     <insert id="addCity" parameterType="com.neo.entity.db3.City">
  20.     insert into T_City(Name,ParentId,Sort)values(#{Name},#{ParentId},#{Sort})
  21. </insert>
  22.     <update id="updateCity" parameterType="com.neo.entity.db3.City">
  23.         update T_City
  24.         <set>
  25.             <if test = "name != null">
  26.                 city.name = #{name},
  27.             </if>
  28.         </set>
  29.         where id = #{id}
  30.     </update>
  31. </mapper>

mapper/db3/CityMapper

  1. package com.neo.mapper.db3;
  2.  
  3. import com.neo.entity.db3.City;
  4. import org.apache.ibatis.annotations.Mapper;
  5.  
  6. import java.util.List;
  7.  
  8.  
  9. @Mapper
  10. public interface CityMapper {
  11.     //获取列表
  12.     public List<City> getCity() throws Exception;
  13.     //根据id删除
  14.     public void deleteCity(int id)throws Exception;
  15.     //新增
  16.     public void addCity(City city)throws Exception;
  17.  
  18.     //修改信息
  19.     //public void updateCity(City city) throws Exception;
  20.  
  21. }

CityService:

  1. package com.neo.service;
  2.  
  3. import com.neo.entity.db3.City;
  4.  
  5. import java.util.List;
  6.  
  7.  
  8. public interface CityService {
  9.     //显示所有用户
  10.     public List<City> getCity()throws Exception;
  11.     //根据id删除用户
  12.     public void deleteCity(int id)throws Exception;
  13.     //新增用户
  14.     public void addCity(City city)throws Exception;
  15. }
  1. package com.neo.service;
  2.  
  3. import com.neo.entity.db3.City;
  4. import com.neo.mapper.db3.CityMapper;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.stereotype.Service;
  7.  
  8. import java.util.List;
  9.  
  10.  
  11. @Service
  12. public class CityServiceImpl implements CityService {
  13.  
  14.     @Autowired
  15.     private CityMapper cityMapper;
  16.     @Override
  17.     public List<City> getCity() throws Exception {
  18.         return cityMapper.getCity();
  19.     }
  20.     //根据id删除用户
  21.     @Override
  22.     public void deleteCity(int id) throws Exception {
  23.         cityMapper.deleteCity(id);
  24.     }
  25.     //新增用户
  26.     @Override
  27.     public void addCity(City city) throws Exception {
  28.         cityMapper.addCity(city);
  29.     }
  30. }

controller

  1. package com.neo.controller;
  2.  
  3. import com.neo.entity.db3.City;
  4. import com.neo.service.CityService;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.web.bind.annotation.RequestMapping;
  7. import org.springframework.web.bind.annotation.RestController;
  8.  
  9. import java.util.List;
  10.  
  11.  
  12. @RestController
  13. public class CityController {
  14.  
  15.     @Autowired
  16.     private CityService cityService;
  17.     @Autowired
  18.     private City city;
  19.     //显示用户
  20.     @RequestMapping("city/list")
  21.     public List<City> index() throws Exception {
  22.         return cityService.getCity();
  23.     }
  24.  
  25.     //增加用户
  26.     @RequestMapping("city/add")
  27.     public String addCity() throws Exception {
  28.         city.setName("test省份");
  29.         city.setParentId(0);
  30.         city.setSort(0);
  31.         cityService.addCity(city);
  32.         return "增加";
  33.     }
  34. }

pom.xml如下：

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  3. 	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  4. 	<modelVersion>4.0.0</modelVersion>
  5.  
  6. 	<groupId>com.neo</groupId>
  7. 	<artifactId>spring-boot-hello</artifactId>
  8. 	<version>1.0</version>
  9. 	<packaging>jar</packaging>
  10.  
  11. 	<name>spring-boot-hello</name>
  12. 	<description>Demo project for Spring Boot</description>
  13.  
  14. 	<parent>
  15. 		<groupId>org.springframework.boot</groupId>
  16. 		<artifactId>spring-boot-starter-parent</artifactId>
  17. 		<version>2.0.0.RELEASE</version>
  18. 	</parent>
  19.  
  20. 	<properties>
  21. 		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  22. 		<java.version>1.8</java.version>
  23. 	</properties>
  24.  
  25. 	<dependencies>
  26. 	     <dependency>
  27. 	        <groupId>org.springframework.boot</groupId>
  28. 	        <artifactId>spring-boot-starter-web</artifactId>
  29. 	    </dependency>
  30. 		<dependency>
  31. 			<groupId>org.springframework.boot</groupId>
  32. 			<artifactId>spring-boot-starter-test</artifactId>
  33. 			<scope>test</scope>
  34. 		</dependency>
  35.  
  36. 		<dependency>
  37. 			<groupId>org.springframework.boot</groupId>
  38. 			<artifactId>spring-boot-starter</artifactId>
  39. 		</dependency>
  40.  
  41. 		<dependency>
  42. 			<groupId>org.mybatis.spring.boot</groupId>
  43. 			<artifactId>mybatis-spring-boot-starter</artifactId>
  44. 			<version>1.3.2</version>
  45. 		</dependency>
  46. 		<dependency>
  47. 			<groupId>mysql</groupId>
  48. 			<artifactId>mysql-connector-java</artifactId>
  49. 		</dependency>
  50. 		<dependency>
  51. 			<groupId>junit</groupId>
  52. 			<artifactId>junit</artifactId>
  53. 		</dependency>
  54. 		<dependency>
  55. 			<groupId>org.springframework.boot</groupId>
  56. 			<artifactId>spring-boot-starter-data-redis</artifactId>
  57. 		</dependency>
  58. 		<dependency>
  59. 			<groupId>com.microsoft.sqlserver</groupId>
  60. 			<artifactId>mssql-jdbc</artifactId>
  61. 			<version>6.4.0.jre8</version>
  62. 			<scope>runtime</scope>
  63. 		</dependency>
  64. 	</dependencies>
  65. 	
  66. 	<build>
  67. 		<plugins>
  68. 			<plugin>
  69. 				<groupId>org.springframework.boot</groupId>
  70. 				<artifactId>spring-boot-maven-plugin</artifactId>
  71. 			</plugin>
  72. 		</plugins>
  73. 	</build>
  74. 	
  75.  
  76. </project>

至些所有的文件都ok了，编译运行 

http://localhost:8080/city/list

http://localhost:8080/list

都正常显示，如果数据库表里没有数据，可以用add方法先添加。

附完整测试项目包地址： https://download.csdn.net/download/huwei2003/11131140

--- end ---
 

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/花生_TL007/article/detail/520940
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号