SpringBoot +MSSQL
____SpringBoot +MSSQL________________________________________________________________________________
https://www.cnblogs.com/wang-yaz/p/9561188.html ******************这篇最重要
4.从事务管理器中选择一个事务，在方法上加@Transactional(value = "mysqlTransactionManager",rollbackFor = Exception.class)
背景：原来一直都是使用mysql数据库，在application.properties 中配置数据库信息
spring.datasource.url=jdbc:mysql://xxxx/test
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=xxx
spring.datasource.password=xxx
这样spring boot会自动化配置，我们用spring boot 约定的配置，现在由于业务的需要，另加一个数据源sqlServer。下面是具体步骤以及遇到的一系列问题。
一、导入依赖
1 pom.xml文件： 2 <dependency> 3 <groupId>com.microsoft.sqlserver</groupId> 4 <artifactId>mssql-jdbc</artifactId> 5 <version>6.4.0.jre8</version> 6 <scope>runtime</scope> 7 </dependency>
二.在application.properties中配置
1 spring.datasource.second.jdbc-url=jdbc:sqlserver://xxxxx:1433;DatabaseName=test 2 spring.datasource.second.username=xxx 3 spring.datasource.second.password=xxxx 4 spring.datasource.second.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver 5 6 注意1：连接数据库的方式不一样，mysql是/test ,sqlServer是;DatabaseName=test 7 spring.datasource.url=jdbc:mysql://xxxx/test 8 spring.datasource.second.jdbc-url=jdbc:sqlserver://xxxxx:1433;DatabaseName=test
三.配置sqlServer数据源
1 package com.ieou.qmt.common;
2 import org.apache.ibatis.session.SqlSessionFactory;
3 import org.mybatis.spring.SqlSessionFactoryBean;
4 import org.springframework.beans.factory.annotation.Qualifier;
5 import org.springframework.boot.context.properties.ConfigurationProperties;
6 import org.springframework.boot.jdbc.DataSourceBuilder;
7 import org.springframework.context.annotation.Bean;
8 import org.springframework.context.annotation.Configuration;
9 import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
10 import org.springframework.core.io.support.ResourcePatternResolver;
11 import org.springframework.jdbc.core.JdbcTemplate;
12 import org.springframework.jdbc.datasource.DataSourceTransactionManager;
13 import org.springframework.transaction.PlatformTransactionManager;
14 import javax.sql.DataSource;
15
16 @Configuration
17 public class SqlServerDataSourceConfig {
18
19 @Bean(name = "sqlServerDataSource")
20 @Qualifier("sqlServerDataSource")
21 @ConfigurationProperties(prefix="spring.datasource.second")
22 public DataSource getMyDataSource(){
23 return DataSourceBuilder.create().build();
24 }
25
26 @Bean(name = "secondaryJdbcTemplate")
27 public JdbcTemplate secondaryJdbcTemplate(
28 @Qualifier("sqlServerDataSource") DataSource dataSource) {
29 return new JdbcTemplate(dataSource);
30 }
31 }
总结：
配置到这里就可以使用JdbcTemplate来操作sqlServer了，（mysql是spring boot的自动化配置，sqlServer是我们手动配置的）只要在类中注入即可
例如：（JdbcTemplate 的用法自行百度）
public class IEOUMallServiceImpl implements IEOUMallService{
@Autowired
@Qualifier("secondaryJdbcTemplate")
protected JdbcTemplate jdbcTemplate2;
}
四.以上配置完成后在执行带有@Transactional 事务的接口时会发现报错：
1 "org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 2 3 'org.springframework.transaction.PlatformTransactionManager' available\n\tat 4 5 org.springframework.beans.factory.support.DefaultListableBeanFactory.getBean(DefaultListableBeanFactory.java:347)\n\tat 6 7 org.springframework.beans.factory.support.DefaultListableBeanFactory.getBean(DefaultListableBeanFactory.java:334)\n\tat 8 9 org.springframework.transaction.interceptor.TransactionAspectSupport.determineTransactionManage
所以：我们必须手动分别配置mysql与sqlServer
1.配置文件：application.properties 需要改为如下：
1 spring.datasource.first.jdbc-url=jdbc:mysql://xxxx:3306/test 2 （这里要是jdbc-url，不然会报jdbcUrl is required with driverClassName的错误） 3 spring.datasource.first.type=com.alibaba.druid.pool.DruidDataSource 4 spring.datasource.first.driver-class-name=com.mysql.jdbc.Driver 5 spring.datasource.first.username=xxx 6 spring.datasource.first.password=xxx 7 8 spring.datasource.second.jdbc-url=jdbc:sqlserver://xxxx:1433;DatabaseName=test 9 spring.datasource.second.username=xxx 10 spring.datasource.second.password=xxx 11 spring.datasource.second.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
2.原来的SqlServerDataSourceConfig改为如下：
1 package com.ieou.qmt.common;
2
3 import org.apache.ibatis.session.SqlSessionFactory;
4 import org.mybatis.spring.SqlSessionFactoryBean;
5 import org.springframework.beans.factory.annotation.Qualifier;
6 import org.springframework.boot.context.properties.ConfigurationProperties;
7 import org.springframework.boot.jdbc.DataSourceBuilder;
8 import org.springframework.context.annotation.Bean;
9 import org.springframework.context.annotation.Configuration;
10 import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
11 import org.springframework.core.io.support.ResourcePatternResolver;
12 import org.springframework.jdbc.core.JdbcTemplate;
13 import org.springframework.jdbc.datasource.DataSourceTransactionManager;
14 import org.springframework.transaction.PlatformTransactionManager;
15
16 import javax.sql.DataSource;
17
18 @Configuration
19 public class SqlServerDataSourceConfig {
20
21 private static final String MAPPER_PATH = "classpath:mybatis/mapping/mapper/*.xml";
22
23 private static final String ENTITY_PACKAGE = "com.ieou.qmt.mapper";
24
25 @Bean(name = "sqlServerDataSource")
26 @Qualifier("sqlServerDataSource")
27 @ConfigurationProperties(prefix="spring.datasource.second")
28 public DataSource getMyDataSource(){
29 return DataSourceBuilder.create().build();
30 }
31
32 @Bean(name = "secondaryJdbcTemplate")
33 public JdbcTemplate secondaryJdbcTemplate(
34 @Qualifier("sqlServerDataSource") DataSource dataSource) {
35 return new JdbcTemplate(dataSource);
36 }
37
38 @Bean(name = "second.SqlSessionTemplate")
39 public SqlSessionFactory devSqlSessionFactory(
40 @Qualifier("sqlServerDataSource") DataSource ddataSource)
41 throws Exception {
42 final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
43 sessionFactory.setDataSource(ddataSource);
44 ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
45 sessionFactory.setMapperLocations(resolver.getResources(MAPPER_PATH));
46 sessionFactory.setTypeAliasesPackage(ENTITY_PACKAGE);
47 return sessionFactory.getObject();
48 }
49
50 @Bean
51 public PlatformTransactionManager sqlServerTransactionManager(@Qualifier("sqlServerDataSource") DataSource sqlServerDataSource)
52 {
53 return new DataSourceTransactionManager(sqlServerDataSource);
54 }
55
56 }
3.新建mysql配置文件如下：
1 package com.ieou.qmt.common;
2
3 import org.apache.ibatis.session.SqlSessionFactory;
4 import org.mybatis.spring.SqlSessionFactoryBean;
5 import org.springframework.beans.factory.annotation.Qualifier;
6 import org.springframework.boot.context.properties.ConfigurationProperties;
7 import org.springframework.boot.jdbc.DataSourceBuilder;
8 import org.springframework.context.annotation.Bean;
9 import org.springframework.context.annotation.Configuration;
10 import org.springframework.context.annotation.Primary;
11 import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
12 import org.springframework.core.io.support.ResourcePatternResolver;
13 import org.springframework.jdbc.datasource.DataSourceTransactionManager;
14 import org.springframework.transaction.PlatformTransactionManager;
15
16 import javax.sql.DataSource;
17
18 @Configuration
19 public class MysqlDataSourceConfig {
20
21 private static final String MAPPER_PATH = "classpath:mybatis/mapping/*.xml";
22
23 private static final String ENTITY_PACKAGE = "com.ieou.qmt.mapper";
24
25
26 @Bean(name = "dataSource")
27 @Primary
28 @Qualifier("dataSource")
29 @ConfigurationProperties(prefix="spring.datasource.first")
30 public DataSource getMyDataSource(){
31 return DataSourceBuilder.create().build();
32 }
33
34 @Bean(name = "first.SqlSessionTemplate")
35 @Primary
36 public SqlSessionFactory devSqlSessionFactory(
37 @Qualifier("dataSource") DataSource ddataSource)
38 throws Exception {
39 final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
40 sessionFactory.setDataSource(ddataSource);
41 ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
42 sessionFactory.setMapperLocations(resolver.getResources(MAPPER_PATH));
43 sessionFactory.setTypeAliasesPackage(ENTITY_PACKAGE);
44 return sessionFactory.getObject();
45 }
46
47 @Bean
48 @Primary
49 public PlatformTransactionManager mysqlTransactionManager(@Qualifier("dataSource") DataSource mysqlDataSource)
50 {
51 return new DataSourceTransactionManager(mysqlDataSource);
52 }
53 }
4.从事务管理器中选择一个事务，在方法上加@Transactional(value = "mysqlTransactionManager",rollbackFor = Exception.class)
至此：配置完毕，如有不足或不对的地方，请补充。
hello world!!!
分类: spring boot
_____________________________________________________________________________________________________
https://www.cnblogs.com/memoryXudy/p/7767741.html
基本项目框架搭建 sqlserver druid配置
1. 我的连接池采用的是阿里云的druid的连接池，工具是IDEA 框架是springboot+maven
以下是我的项目框架结构
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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>cn.xudy.sqlservice</groupId>
<artifactId>StorageSqlService</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.4.RELEASE</version>
<relativePath/>
</parent>
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.25</version>
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>sqljdbc4</artifactId>
<version>4.0</version>
</dependency>
<!-- Spring Boot JDBC -->
<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>
2. .properties 配置
server.port=8011 druid.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver druid.url=jdbc:sqlserver://localhost:1433;DatabaseName=test druid.username=sa druid.password=123456
3. SystemConfig 配置
package cn.xudy.group.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
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 org.springframework.stereotype.Component;
import org.springframework.web.servlet.config.annotation.CorsRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;
import javax.sql.DataSource;
/**
* Created by Ulegal on 2017/8/19.
*/
@Configuration
public class SystemConfig {
@Bean(name = "dataSource")
@Qualifier(value = "dataSource")
@Primary
@ConfigurationProperties(prefix = "druid")
public DataSource dataSource() {
return DataSourceBuilder.create().type(DruidDataSource.class).build();
}
/**
* 跨域
* @return
*/
@Bean
public WebMvcConfigurer corsConfigurer() {
return new WebMvcConfigurerAdapter() {
@Override
public void addCorsMappings(CorsRegistry registry) {
registry.addMapping("/**");
}
};
}
}
4. dao数据层测试
@Repository
public class StorageDaoImpl implements StorageDao{
@Autowired
JdbcTemplate jdbcTemplate;
@Override
public List<Map<String, Object>> getInfo() {
// 传统方法
List<Map<String,Object>> list = new ArrayList<Map<String, Object>>();
String sql = "SELECT * FROM " + "Table_1" +";";
list = jdbcTemplate.queryForList(sql);
System.out.println("-------------"+list);
return list;
}
搞定
浙公网安备 33010602011771号