SpringBoot之数据访问和事务-专题三

四、数据访问

4.1、springboot整合使用JdbcTemplate

4.1.1 pom文件引入

<parent>

		<groupId>org.springframework.boot</groupId>

		<artifactId>spring-boot-starter-parent</artifactId>

		<version>2.0.0.RELEASE</version>

	</parent>

	<dependencies>

		<!-- jdbcTemplate 依赖 -->

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter-jdbc</artifactId>

		</dependency>

		<!-- mysql 依赖 -->

		<dependency>

			<groupId>mysql</groupId>

			<artifactId>mysql-connector-java</artifactId>

		</dependency>

		<!-- 测试 -->

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter-test</artifactId>

			<scope>test</scope>

		</dependency>

		<!-- springboot-web组件 -->

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter-web</artifactId>

		</dependency>

	</dependencies>

4.1.2 application.properties新增配置

spring.datasource.url=jdbc:mysql://localhost:3306/test

spring.datasource.username=root

spring.datasource.password=root

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

4.1.3 UserService类

@Service

public class UserServiceImpl implements UserService {

	@Autowired

	private JdbcTemplate jdbcTemplate;

	public void createUser(String name, Integer age) {

		jdbcTemplate.update("insert into users values(null,?,?);", name, age);

	}

}

4.1.4 App类

@SpringBootApplication

public class App {

	public static void main(String[] args) {

		SpringApplication.run(App.class, args);

	}

}

注意: spring-boot-starter-parent要在1.5以上

4.2、springboot整合使用mybatis

4.2.1、pom文件引入

<parent>

		<groupId>org.springframework.boot</groupId>

		<artifactId>spring-boot-starter-parent</artifactId>

		<version>2.0.0.RELEASE</version>

	</parent>

	<dependencies>

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter</artifactId>

		</dependency>

		<!-- 测试 -->

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter-test</artifactId>

			<scope>test</scope>

		</dependency>

		<dependency>

			<groupId>org.mybatis.spring.boot</groupId>

			<artifactId>mybatis-spring-boot-starter</artifactId>

			<version>1.1.1</version>

		</dependency>

		<!-- mysql 依赖 -->

		<dependency>

			<groupId>mysql</groupId>

			<artifactId>mysql-connector-java</artifactId>

		</dependency>

		<!-- springboot-web组件 -->

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter-web</artifactId>

		</dependency>

	</dependencies>

4.2.2、配置文件引入

spring.datasource.url=jdbc:mysql://localhost:3306/test

spring.datasource.username=root

spring.datasource.password=root

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

4.2.3、Mapper代码

public interface UserMapper {

	@Select("SELECT * FROM USERS WHERE NAME = #{name}")

	User findByName(@Param("name") String name);

	@Insert("INSERT INTO USERS(NAME, AGE) VALUES(#{name}, #{age})")

	int insert(@Param("name") String name, @Param("age") Integer age);

}

4.2.4、启动方式

@MapperScan("com.mapper")

@SpringBootApplication

public class MybatisApp {

	public static void main(String[] args) {

		SpringApplication.run(MybatisApp.class, args);

	}

}

4.2.5、Mybatis整合分页插件

pageHelper

PageHelper 是一款好用的开源免费的 Mybatis 第三方物理分页插件

物理分页

支持常见的 12 种数据库。Oracle,MySql,MariaDB,SQLite,DB2,PostgreSQL,SqlServer 等

支持多种分页方式

支持常见的 RowBounds(PageRowBounds)，PageHelper.startPage 方法调用，Mapper 接口参数调用

Maven依赖

<parent>

		<groupId>org.springframework.boot</groupId>

		<artifactId>spring-boot-starter-parent</artifactId>

		<version>2.0.0.RELEASE</version>

	</parent>

	<dependencies>

		<dependency>

			<groupId>org.projectlombok</groupId>

			<artifactId>lombok</artifactId>

		</dependency>

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter</artifactId>

		</dependency>

		<!-- 测试 -->

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter-test</artifactId>

			<scope>test</scope>

		</dependency>

		<dependency>

			<groupId>org.mybatis.spring.boot</groupId>

			<artifactId>mybatis-spring-boot-starter</artifactId>

			<version>1.1.1</version>

		</dependency>

		<!-- mysql 依赖 -->

		<dependency>

			<groupId>mysql</groupId>

			<artifactId>mysql-connector-java</artifactId>

		</dependency>

		<!-- springboot-web组件 -->

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter-web</artifactId>

		</dependency>

		<!-- springboot 整合 pagehelper -->

		<dependency>

			<groupId>com.github.pagehelper</groupId>

			<artifactId>pagehelper-spring-boot-starter</artifactId>

			<version>1.2.5</version>

		</dependency>

		<dependency>

			<groupId>org.apache.commons</groupId>

			<artifactId>commons-lang3</artifactId>

			<version>3.7</version>

		</dependency>

	</dependencies>

配置文件

spring.datasource.url=jdbc:mysql://localhost:3306/test

spring.datasource.username=root

spring.datasource.password=root

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

logging.level.com.example.demo.dao=DEBUG

pagehelper.helperDialect=mysql

pagehelper.reasonable=true

pagehelper.supportMethodsArguments=true

pagehelper.params=count=countSql

pagehelper.page-size-zero=true

Entity层

@Data

public class User {

	private Integer id;

	private String name;

	private Integer age;

}

Mapper层

public interface UserMapper {

	@Select("SELECT * FROM USERS ")

	List<User> findUserList();

}

Service层

@Service

public class UserService {

	@Autowired

	private UserMapper userMapper;

	public PageInfo<User> findUserList(int page, int size) {

		// 开启分页插件,放在查询语句上面

		PageHelper.startPage(page, size);

		List<User> listUser = userMapper.findUserList();

		// 封装分页之后的数据

		PageInfo<User> pageInfoUser = new PageInfo<User>(listUser);

		return pageInfoUser;

	}

}

Controller层

@RestController

public class IndexController {

	@Autowired

	private UserService userService;

	@RequestMapping("/findUser")

	public PageInfo<User> findUserList(int page, int size) {

		return userService.findUserList(page, size);

	}

}

启动项目

@MapperScan("com.mapper")

@SpringBootApplication

public class PageHelper {

	public static void main(String[] args) {

		SpringApplication.run(PageHelper.class, args);

	}

}

4.3、springboot整合使用springjpa

4.3.1 pom文件引入依赖

<parent>

		<groupId>org.springframework.boot</groupId>

		<artifactId>spring-boot-starter-parent</artifactId>

		<version>2.0.0.RELEASE</version>

	</parent>

	<dependencies>

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter-data-jpa</artifactId>

		</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>

4.3.2 创建User实体类

@Entity(name = "users")

public class UserEntity {

	@Id

	@GeneratedValue(strategy = GenerationType.IDENTITY)

	private Integer id;

	@Column(name = "name")

	private String name;

	@Column(name = "age")

	private Integer age;

}

4.3.3 创建UserDao

public interface UserDao extends JpaRepository<User, Integer> {

}

4.3.3 创建IndexController

@RestController

public class IndexController {

	@Autowired

	private UserDao userDao;

	@RequestMapping("/jpaFindUser")

	public Object jpaIndex(User user) {

		Optional<User> userOptional = userDao.findById(user.getId());

		User reusltUser = userOptional.get();

		return reusltUser == null ? "没有查询到数据" : reusltUser;

	}

}

4.3.4 启动项目

@SpringBootApplication

public class JpaApp {

	public static void main(String[] args) {

		SpringApplication.run(JpaApp.class, args);

	}

}

4.4、springboot整合多数据源

思考下，你们在项目中有使用到多数据源吗？

原理使用根据包名，加载不同的数据源

4.4.1配置文件中新增两个数据源

###datasource1

spring.datasource.test1.driver-class-name = com.mysql.jdbc.Driver

spring.datasource.test1.jdbc-url = jdbc:mysql://localhost:3306/test01?useUnicode=true&characterEncoding=utf-8

spring.datasource.test1.username = root

spring.datasource.test1.password = root

###datasource2

spring.datasource.test2.driver-class-name = com.mysql.jdbc.Driver

spring.datasource.test2.jdbc-url = jdbc:mysql://localhost:3306/test02?useUnicode=true&characterEncoding=utf-8

spring.datasource.test2.username = root

spring.datasource.test2.password = root

4.4.2配置文件中新增两个数据源

//DataSource01

@Configuration // 注册到springboot容器中

@MapperScan(basePackages = "com.example.test01", sqlSessionFactoryRef = "test1SqlSessionFactory")

public class DataSource1Config {

	@Bean(name = "test1DataSource")

	@ConfigurationProperties(prefix = "spring.datasource.test1")

	@Primary

	public DataSource testDataSource() {

		return DataSourceBuilder.create().build();

	}

	@Bean(name = "test1SqlSessionFactory")

	@Primary

	public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource)

			throws Exception {

		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

		bean.setDataSource(dataSource);

		// bean.setMapperLocations(

		// new

		// PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test1/*.xml"));

		return bean.getObject();

	}

	@Bean(name = "test1TransactionManager")

	@Primary

	public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {

		return new DataSourceTransactionManager(dataSource);

	}

	@Bean(name = "test1SqlSessionTemplate")

	@Primary

	public SqlSessionTemplate testSqlSessionTemplate(

			@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {

		return new SqlSessionTemplate(sqlSessionFactory);

	}

}

DataSource02

//DataSource2

@Configuration // 注册到springboot容器中

@MapperScan(basePackages = "com.example.test02", sqlSessionFactoryRef = "test2SqlSessionFactory")

public class DataSource2Config {

	@Bean(name = "test2DataSource")

	@ConfigurationProperties(prefix = "spring.datasource.test2")

	public DataSource testDataSource() {

		return DataSourceBuilder.create().build();

	}

	@Bean(name = "test2SqlSessionFactory")

	public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource)

			throws Exception {

		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

		bean.setDataSource(dataSource);

		// bean.setMapperLocations(

		// new

		// PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test2/*.xml"));

		return bean.getObject();

	}

	@Bean(name = "test2TransactionManager")

	public DataSourceTransactionManager testTransactionManager(@Qualifier("test2DataSource") DataSource dataSource) {

		return new DataSourceTransactionManager(dataSource);

	}

	@Bean(name = "test2SqlSessionTemplate")

	public SqlSessionTemplate testSqlSessionTemplate(

			@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {

		return new SqlSessionTemplate(sqlSessionFactory);

	}

}

4.4.2创建分包Mapper

public interface User1Mapper {

	@Insert("insert into users values(null,#{name},#{age});")

	public int addUser(@Param("name") String name, @Param("age") Integer age);

}

4.4.3 多数据源事务注意事项

在多数据源的情况下，使用@Transactional注解时，应该指定事务管理者

@Transactional(transactionManager = “test2TransactionManager”)

4.4.5启动项目

@SpringBootApplication

@MapperScan(basePackages = { "com.example.mapper" })

public class App {

	public static void main(String[] args) {

		SpringApplication.run(App.class, args);

	}

}

No qualifying bean of type [javax.sql.DataSource] is defined: expected single matching bean but found 2: test1DataSource,test2DataSource

加上@Primary即可。

There was an unexpected error (type=Internal Server Error, status=500).

No qualifying bean of type ‘org.springframework.transaction.PlatformTransactionManager’ available: expected single matching bean but found 2: test1TransactionManager,test2TransactionManager

指定事务管理器

Springboot1.5的时候没有默认指向数据源会报错

Springboot2.0的时候不报错

五、事物管理

5.1.1SpringBoot整合事物管理

Springboot默认集成事物,只主要在方法上加上@Transactional即可

5.1.2SpringBoot分布式事物管理

使用springboot+jta+atomikos 分布式事物管理

Atomikos 是一个为Java平台提供增值服务的并且开源类事务管理器。

5.1.2.1 新增jta-atomikos依赖

<dependency>

	<groupId>org.springframework.boot</groupId>

	<artifactId>spring-boot-starter-jta-atomikos</artifactId>

</dependency>

5.1.2.2新增配置文件信息

# Mysql 1

mysql.datasource.test1.url = jdbc:mysql://localhost:3306/test01?useUnicode=true&characterEncoding=utf-8

mysql.datasource.test1.username = root

mysql.datasource.test1.password = root

mysql.datasource.test1.minPoolSize = 3

mysql.datasource.test1.maxPoolSize = 25

mysql.datasource.test1.maxLifetime = 20000

mysql.datasource.test1.borrowConnectionTimeout = 30

mysql.datasource.test1.loginTimeout = 30

mysql.datasource.test1.maintenanceInterval = 60

mysql.datasource.test1.maxIdleTime = 60

# Mysql 2

mysql.datasource.test2.url =jdbc:mysql://localhost:3306/test02?useUnicode=true&characterEncoding=utf-8

mysql.datasource.test2.username =root

mysql.datasource.test2.password =root

mysql.datasource.test2.minPoolSize = 3

mysql.datasource.test2.maxPoolSize = 25

mysql.datasource.test2.maxLifetime = 20000

mysql.datasource.test2.borrowConnectionTimeout = 30

mysql.datasource.test2.loginTimeout = 30

mysql.datasource.test2.maintenanceInterval = 60

mysql.datasource.test2.maxIdleTime = 60

5.1.2.3 读取配置文件信息

@Data

@ConfigurationProperties(prefix = "mysql.datasource.test1")

public class DBConfig1 {

	private String url;

	private String username;

	private String password;

	private int minPoolSize;

	private int maxPoolSize;

	private int maxLifetime;

	private int borrowConnectionTimeout;

	private int loginTimeout;

	private int maintenanceInterval;

	private int maxIdleTime;

	private String testQuery;

}

@Data

@ConfigurationProperties(prefix = "mysql.datasource.test2")

public class DBConfig2 {

	private String url;

	private String username;

	private String password;

	private int minPoolSize;

	private int maxPoolSize;

	private int maxLifetime;

	private int borrowConnectionTimeout;

	private int loginTimeout;

	private int maintenanceInterval;

	private int maxIdleTime;

	private String testQuery;

}

5.1.2.4 创建多数据源

@Configuration

// basePackages 最好分开配置 如果放在同一个文件夹可能会报错

@MapperScan(basePackages = "com.example.test01", sqlSessionTemplateRef = "testSqlSessionTemplate")

public class MyBatisConfig1 {

	// 配置数据源

	@Primary

	@Bean(name = "testDataSource")

	public DataSource testDataSource(DBConfig1 testConfig) throws SQLException {

		MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();

		mysqlXaDataSource.setUrl(testConfig.getUrl());

		mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);

		mysqlXaDataSource.setPassword(testConfig.getPassword());

		mysqlXaDataSource.setUser(testConfig.getUsername());

		mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);

		AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();

		xaDataSource.setXaDataSource(mysqlXaDataSource);

		xaDataSource.setUniqueResourceName("testDataSource");

		xaDataSource.setMinPoolSize(testConfig.getMinPoolSize());

		xaDataSource.setMaxPoolSize(testConfig.getMaxPoolSize());

		xaDataSource.setMaxLifetime(testConfig.getMaxLifetime());

		xaDataSource.setBorrowConnectionTimeout(testConfig.getBorrowConnectionTimeout());

		xaDataSource.setLoginTimeout(testConfig.getLoginTimeout());

		xaDataSource.setMaintenanceInterval(testConfig.getMaintenanceInterval());

		xaDataSource.setMaxIdleTime(testConfig.getMaxIdleTime());

		xaDataSource.setTestQuery(testConfig.getTestQuery());

		return xaDataSource;

	}

	@Primary

	@Bean(name = "testSqlSessionFactory")

	public SqlSessionFactory testSqlSessionFactory(@Qualifier("testDataSource") DataSource dataSource)

			throws Exception {

		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

		bean.setDataSource(dataSource);

		return bean.getObject();

	}

	@Primary

	@Bean(name = "testSqlSessionTemplate")

	public SqlSessionTemplate testSqlSessionTemplate(

			@Qualifier("testSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {

		return new SqlSessionTemplate(sqlSessionFactory);

	}

}

@Configuration

@MapperScan(basePackages = "com.example.test02", sqlSessionTemplateRef = "test2SqlSessionTemplate")

public class MyBatisConfig2 {

	// 配置数据源

	@Bean(name = "test2DataSource")

	public DataSource testDataSource(DBConfig2 testConfig) throws SQLException {

		MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();

		mysqlXaDataSource.setUrl(testConfig.getUrl());

		mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);

		mysqlXaDataSource.setPassword(testConfig.getPassword());

		mysqlXaDataSource.setUser(testConfig.getUsername());

		mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);

		AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();

		xaDataSource.setXaDataSource(mysqlXaDataSource);

		xaDataSource.setUniqueResourceName("test2DataSource");

		xaDataSource.setMinPoolSize(testConfig.getMinPoolSize());

		xaDataSource.setMaxPoolSize(testConfig.getMaxPoolSize());

		xaDataSource.setMaxLifetime(testConfig.getMaxLifetime());

		xaDataSource.setBorrowConnectionTimeout(testConfig.getBorrowConnectionTimeout());

		xaDataSource.setLoginTimeout(testConfig.getLoginTimeout());

		xaDataSource.setMaintenanceInterval(testConfig.getMaintenanceInterval());

		xaDataSource.setMaxIdleTime(testConfig.getMaxIdleTime());

		xaDataSource.setTestQuery(testConfig.getTestQuery());

		return xaDataSource;

	}

	@Bean(name = "test2SqlSessionFactory")

	public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource)

			throws Exception {

		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

		bean.setDataSource(dataSource);

		return bean.getObject();

	}

	@Bean(name = "test2SqlSessionTemplate")

	public SqlSessionTemplate testSqlSessionTemplate(

			@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {

		return new SqlSessionTemplate(sqlSessionFactory);

	}

}

5.1.2.4 启动加载配置

@EnableConfigurationProperties(value = { DBConfig1.class, DBConfig2.class })

分布式事务，可以预览我的这个链接分布式事务

巴特西

SpringBoot之数据访问和事务-专题三