springboot 带事务暴操mybatis四数据源 oracle 达梦 mysql sqlserver
文章目录
1. 前置条件
1. jar包
- oracle的去oracle安装目录下找
- 达梦的也去安装目录下找,还有hibernate方言包
- 找到后放resource/lib下,pom件里配置导入
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>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.4.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>xyz.tangzekk</groupId>
<artifactId>data4</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>data4</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<!--<dependency>-->
<!--<groupId>org.springframework.boot</groupId>-->
<!--<artifactId>spring-boot-starter-data-jpa</artifactId>-->
<!--</dependency>-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.dm</groupId>
<artifactId>Dm7JdbcDriver</artifactId>
<version>1.7</version>
<scope>system</scope>
<systemPath>${project.basedir}/src/main/resources/lib/Dm7JdbcDriver18.jar</systemPath>
</dependency>
<dependency>
<groupId>com.dm.dialect</groupId>
<artifactId>hibernate4</artifactId>
<version>5.0</version>
<scope>system</scope>
<systemPath>${project.basedir}/src/main/resources/lib/DmDialect-for-hibernate5.0.jar</systemPath>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<!--<version>5.0</version>-->
<scope>system</scope>
<systemPath>${project.basedir}/src/main/resources/lib/ojdbc-6.jar</systemPath>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</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>
3. properties
app.datasource.oracle.driver-class-name=oracle.jdbc.driver.OracleDriver
app.datasource.oracle.jdbc-url=jdbc:oracle:thin:@localhost:1521:orcl
app.datasource.oracle.username=auth
app.datasource.oracle.password=oracle
app.datasource.dm.driver-class-name=dm.jdbc.driver.DmDriver
app.datasource.dm.jdbc-url=jdbc:dm://127.0.0.1:5236/DMSERVER?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8
app.datasource.dm.username=SYSDBA
app.datasource.dm.password=123456789
app.datasource.sqlserver.jdbc-url=jdbc:sqlserver://127.0.0.1:1433;DatabaseName=crew
app.datasource.sqlserver.username=sa
app.datasource.sqlserver.password=sqlserver
app.datasource.sqlserver.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
app.datasource.mysql.jdbc-url=jdbc:mysql://129.204.105.23:3306/jwtse
app.datasource.mysql.username=root
app.datasource.mysql.password=Mysqlzekai3!@#
app.datasource.mysql.driver-class-name=com.mysql.jdbc.Driver
2. 数据源总配置类
package xyz.tangzekk.data4;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.*;
import org.springframework.boot.context.properties.*;
import org.springframework.boot.jdbc.*;
import org.springframework.context.annotation.*;
@Configuration
public class MultiDataSourceConfig {
@Bean(name = "dm")
@Qualifier("dm")
@Primary // 定义主数据源
@ConfigurationProperties(prefix = "app.datasource.dm")
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "oracle")
@Qualifier("oracle")
@ConfigurationProperties(prefix = "app.datasource.oracle")
public DataSource oracleDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "sqlserver")
@Qualifier("sqlserver")
@ConfigurationProperties(prefix = "app.datasource.sqlserver")
public DataSource sqlServerDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "mysql")
@Qualifier("mysql")
@ConfigurationProperties(prefix = "app.datasource.mysql")
public DataSource mysqlDataSource() {
return DataSourceBuilder.create().build();
}
}
3. mysql oracle dm sqlserver 数据源配置类,这里不管了全拉过来
1. 达梦
package xyz.tangzekk.data4;
import javax.sql.DataSource;
import org.apache.ibatis.session.*;
import org.mybatis.spring.*;
import org.mybatis.spring.annotation.*;
import org.springframework.beans.*;
import org.springframework.beans.factory.annotation.*;
import org.springframework.context.*;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.*;
import org.springframework.core.io.*;
import org.springframework.jdbc.datasource.*;
import org.springframework.transaction.*;
@Configuration
@MapperScan(basePackages = "xyz.tangzekk.data4.dm", sqlSessionTemplateRef = "primarySqlSessionTemplate")
public class SqlSessionTemplate1 implements ApplicationContextAware {
@Bean(name = "primarySqlSessionFactory")
@Primary
public SqlSessionFactory primarySqlSessionFactory(@Qualifier("dm") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
// bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
Resource[] resources = ctx.getResources("classpath*:/mapper/dm/*.xml");
bean.setMapperLocations(resources);
return bean.getObject();
}
//配置声明式事务管理器
@Bean(name = "primaryTransactionManager")
@Primary
public PlatformTransactionManager primaryTransactionManager(@Qualifier("dm") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "primarySqlSessionTemplate")
@Primary
public SqlSessionTemplate primarySqlSessionTemplate(
@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
private ApplicationContext ctx;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
this.ctx = applicationContext;
}
}
2. oracle
package xyz.tangzekk.data4;
import javax.sql.DataSource;
import org.apache.ibatis.session.*;
import org.mybatis.spring.*;
import org.mybatis.spring.annotation.*;
import org.springframework.beans.*;
import org.springframework.beans.factory.annotation.*;
import org.springframework.context.*;
import org.springframework.context.annotation.*;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.*;
import org.springframework.core.io.support.*;
import org.springframework.jdbc.datasource.*;
import org.springframework.transaction.*;
@Configuration
@MapperScan(basePackages = "xyz.tangzekk.data4.oracle", sqlSessionTemplateRef = "secondarySqlSessionTemplate")
public class SqlSessionTemplate2 implements ApplicationContextAware {
@Bean(name = "secondarySqlSessionFactory")
public SqlSessionFactory secondarySqlSessionFactory(@Qualifier("oracle") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
// bean.setMapperLocations(
// new PathMatchingResourcePatternResolver().getResources("classpath:oracleMapper/*.xml"));
Resource[] resources = ctx.getResources("classpath*:/mapper/oracle/*.xml");
bean.setMapperLocations(resources);
return bean.getObject();
}
@Bean(name = "secondaryTransactionManager")
public PlatformTransactionManager secondaryTransactionManager(
@Qualifier("oracle") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "secondarySqlSessionTemplate")
public SqlSessionTemplate secondarySqlSessionTemplate(
@Qualifier("secondarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
private ApplicationContext ctx;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
this.ctx = applicationContext;
}
}
3. sqlserver
package xyz.tangzekk.data4;
import javax.sql.DataSource;
import org.apache.ibatis.session.*;
import org.mybatis.spring.*;
import org.mybatis.spring.annotation.*;
import org.springframework.beans.*;
import org.springframework.beans.factory.annotation.*;
import org.springframework.context.*;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.*;
import org.springframework.core.io.*;
import org.springframework.jdbc.datasource.*;
import org.springframework.transaction.*;
@Configuration
@MapperScan(basePackages = "xyz.tangzekk.data4.sqlserver", sqlSessionTemplateRef = "sqlServerSqlSessionTemplate")
public class SqlSessionTemplate3 implements ApplicationContextAware {
@Bean(name = "sqlServerSqlSessionFactory")
public SqlSessionFactory sqlServerSqlSessionFactory(@Qualifier("sqlserver") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
// bean.setMapperLocations(
// new PathMatchingResourcePatternResolver().getResources("classpath:oracleMapper/*.xml"));
Resource[] resources = ctx.getResources("classpath*:/mapper/sqlserver/*.xml");
bean.setMapperLocations(resources);
return bean.getObject();
}
@Bean(name = "sqlServerTransactionManager")
public PlatformTransactionManager sqlServerTransactionManager(
@Qualifier("sqlserver") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "sqlServerSqlSessionTemplate")
public SqlSessionTemplate sqlServerSqlSessionTemplate(
@Qualifier("sqlServerSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
private ApplicationContext ctx;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
this.ctx = applicationContext;
}
}
4. mysql
package xyz.tangzekk.data4;
import javax.sql.DataSource;
import org.apache.ibatis.session.*;
import org.mybatis.spring.*;
import org.mybatis.spring.annotation.*;
import org.springframework.beans.*;
import org.springframework.beans.factory.annotation.*;
import org.springframework.context.*;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.*;
import org.springframework.core.io.*;
import org.springframework.jdbc.datasource.*;
import org.springframework.transaction.*;
@Configuration
@MapperScan(basePackages = "xyz.tangzekk.data4.mysql", sqlSessionTemplateRef = "mysqlSqlSessionTemplate")
public class SqlSessionTemplate4 implements ApplicationContextAware {
@Bean(name = "mysqlSqlSessionFactory")
public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysql") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
// bean.setMapperLocations(
// new PathMatchingResourcePatternResolver().getResources("classpath:oracleMapper/*.xml"));
Resource[] resources = ctx.getResources("classpath*:/mapper/mysql/*.xml");
bean.setMapperLocations(resources);
return bean.getObject();
}
@Bean(name = "mysqlTransactionManager")
public PlatformTransactionManager mysqlTransactionManager(
@Qualifier("mysql") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "mysqlSqlSessionTemplate")
public SqlSessionTemplate mysqlSqlSessionTemplate(
@Qualifier("mysqlSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
private ApplicationContext ctx;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
this.ctx = applicationContext;
}
}
看配置文件应该可以看出,不同的包下告诉Spring用对应不同数据源,比如达梦的包
xyz.tangzekk.data4.dm
下,操作时就用的达梦数据源
package xyz.tangzekk.data4.dm;
import java.util.List;
import org.apache.ibatis.annotations.*;
public interface AddressMapper {
@Select("select * from PERSON.ADDRESS")
List<Address> findall();
@Select("select * from PERSON.ADDRESS where ADDRESSID = #{id}")
Address findById(Integer id);
// @Insert("INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,ADDRESS) values (#{address1},#{address2},#{city},#{postalCode})")
// void save(Address address);
@Insert("INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) values (#{address1},#{address2},#{city},#{postalCode})")
void save(Address address);
// @Insert("INSERT INTO PERSON.ADDRESS(ADDRESS1,ADDRESS2,CITY,POSTALCODE) VALUES('北京路22号','','山东市','430073')")
// void save2(Address address);
@Select("select count(1) from PERSON.ADDRESS")
Long count();
}
4.事务
事务很关键啊,举个例子
package xyz.tangzekk.data4.dm;
import org.springframework.beans.factory.annotation.*;
import org.springframework.stereotype.*;
import org.springframework.transaction.annotation.*;
/**
* @program: ohon
* @description:
* @author: tangzekai
* @create: 2019-05-10 18:12
**/
@Service
public class AddressService {
@Autowired
AddressMapper addressMapper;
// @Transactional(transactionManager = "primaryTransactionManager")
@Transactional
public void add(Address address) {
addressMapper.save(address);
throw new RuntimeException();
}
}
哈哈,抛出异常后,数据回滚了,事务 OK,成了老铁