这是以mysql作为示范
maven 依赖
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.3.21</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.42</version>
<scope>runtime</scope>
</dependency>
Java内进行使用
//1.配置数据源
DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
driverManagerDataSource.setDriverClassName("com.mysql.jdbc.Driver");
driverManagerDataSource.setUrl("jdbc:mysql://localhost:3306/库名");
driverManagerDataSource.setUsername("");
driverManagerDataSource.setPassword("");
//2.使用JDBCTemplate对数据源进行操作
JdbcTemplate jdbcTemplate = new JdbcTemplate(driverManagerDataSource);
JdbcTemplate的操作
1.常用方法
JdbcTemplate jdbcTemplate = new JdbcTemplate(driverManagerDataSource);
jdbcTemplate.execute(""); //执行 数据定义语言DDL(表创建,表删除....)
jdbcTemplate.update(""); //执行 数据操纵语言DML(增删改)
jdbcTemplate.query(""); //执行 数据查询语言DQL(查)
jdbcTemplate.execute(sql); | 执行 数据定义语言DDL(表创建,表删除....) |
jdbcTemplate.update(sql); | 执行 数据操纵语言DML(增删改) |
jdbcTemplate.queryForObject(sql,rowMapper); | 查一条内容,参数需要设置RowMapper返回映射 |
jdbcTemplate.query(sql,rowMapper); | 查多条内容,参数需要设置RowMapper返回映射 |
2.RowMapper
结果映射:用于设置查询结果的储存类,使得查出来的表每行内容存入每个RowMapper指定的对象中
//BeanPropertyRowMapper是基于属性储存的RowMapper
BeanPropertyRowMapper<User> userRowMapper = new BeanPropertyRowMapper<>(User.class);
User user = jdbcTemplate.queryForObject("sql语句", userRowMapper);
3.普通事务处理
//1.配置数据源
DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
driverManagerDataSource.setDriverClassName("com.mysql.jdbc.Driver");
driverManagerDataSource.setUrl("jdbc:mysql://localhost:3306/库名");
driverManagerDataSource.setUsername("");
driverManagerDataSource.setPassword("");
//2.通过数据源过的数据库Connection对象,操作Connection来commit,rollback;
Connection connection = driverManagerDataSource.getConnection();
connection.commit();
connection.rollback();
4.基于注解的声明式事务
声明式事务所需依赖
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.3.21</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>5.3.21</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>5.3.21</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>5.3.21</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>5.3.21</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjrt</artifactId>
<version>1.9.1</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.1</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.3.21</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.3.21</version>
</dependency>
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.42</version>
<scope>runtime</scope>
</dependency>
</dependencies>
applicationContext.xml ioc配置文件所需配置
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-4.3.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.3.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-4.3.xsd">
<!-- 1.配置数据源 -->
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<!--数据库驱动 -->
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<!--连接数据库的url -->
<property name="url" value="jdbc:mysql://localhost/spring"/>
<!--连接数据库的用户名 -->
<property name="username" value="root"/>
<!--连接数据库的密码 -->
<property name="password" value=""/>
</bean>
<!-- 2.配置JDBC模板 -->
<bean id="jdbcTemplate"
class="org.springframework.jdbc.core.JdbcTemplate">
<!-- 默认必须使用数据源 -->
<property name="dataSource" ref="dataSource"/>
</bean>
<!--3.定义id为accountDao的Bean -->
<bean id="accountDaoImpl" class="com.sqllearn.dao.AccountDaoImpl">
<!-- 将jdbcTemplate注入到AccountDao实例中 -->
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
<!-- 4.事务管理器,依赖于数据源 -->
<bean id="transactionManager" class=
"org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 5.注册事务管理器驱动 -->
<tx:annotation-driven transaction-manager="transactionManager"/>
</beans>
原理: 使用了@Transactional注解的方法,会在其异常通知处插入事务回滚方法
@Transactional(propagation = Propagation.REQUIRED,isolation = Isolation.DEFAULT,readOnly = false)
public void transfer(String outUser,String inUser,double money){
//1.收钱方收钱
jdbcTemplate.update("update account set balance = balance + ? where username = ?",money,inUser);
//2.发钱方扣钱
jdbcTemplate.update("update account set balance = balance - ? where username = ?",money,outUser);
}