JdbcTemplate
依赖项
除mysql驱动以外还需要以下两个jar包
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.3.10</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.3.10</version>
</dependency>
其中spring-tx是事务控制相关的
最基本的使用
基本使用和c3p0和dbutils差别不大
public static void main(String[] args) {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUsername("root");
dataSource.setPassword("adminadmin");
dataSource.setUrl("jdbc:mysql://localhost:3306/spring_test");
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.execute("insert into account(name,money) values('ccc',123)");
}
写到这里我们可以注意到这里有很多语句可以使用到spring中的IOC,那么下步我们就进行对spring的配置
JdbcTemplate的IOC配置:
xml配置文件
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="username" value="root"/>
<property name="password" value="adminadmin"/>
<property name="url" value="jdbc:mysql://localhost:3306/spring_test"/>
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
</bean>
<bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
主函数
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("Bean.xml");
JdbcTemplate jdbcTemplate = (JdbcTemplate)context.getBean("jdbcTemplate");
jdbcTemplate.execute("insert into account(name,money) values('ccc',123)");
}
改成原来常用的Dao模式
创建接口:
package com.spring.dao;
import com.spring.beans.Account;
import java.util.List;
/**
* @author 28985
*/
public interface IAccountDao {
/**
* 查找所有
* @return
*/
public List<Account> findAll();
/**
* 根据ID查找
* @return
*/
public Account findById(Integer id);
/**
* 更新
* @param account
*/
public void update(Account account);
/**
* 删除
* @param id
*/
public void delete(Integer id);
/**
* 插入
* @param account
*/
public void insert(Account account);
/**
* 大于多少钱的人数
* @param money
* @return
*/
public Integer moneyNumber(Integer money);
}
创建其实现类:
package com.spring.dao.impl;
import com.spring.beans.Account;
import com.spring.dao.IAccountDao;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.List;
/**
* @author 28985
*/
public class AccountDao implements IAccountDao {
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Override
public List<Account> findAll() {
return jdbcTemplate.query("select * from account", new BeanPropertyRowMapper<Account>(Account.class));
}
@Override
public Account findById(Integer id) {
try {
return jdbcTemplate.query("select * from account where id = ?", new BeanPropertyRowMapper<Account>(Account.class),id).get(0);
}
catch (Exception e){
Account account =new Account();
account.setName("NOTFOUND");
return account;
}
}
@Override
public void update(Account account) {
jdbcTemplate.update("update account set name = ?,money = ? where id = ?",account.getName(),account.getMoney(),account.getId());
}
@Override
public void delete(Integer id) {
jdbcTemplate.update("delete from account where id = ?",id);
}
@Override
public void insert(Account account) {
jdbcTemplate.update("insert into account(name,money) values(?,?)",account.getName(),account.getMoney());
}
@Override
public Integer moneyNumber(Integer money) {
return jdbcTemplate.queryForObject(" select count(*) from account where money > ? ", Integer.class, 900);
}
}
再进行springIOC的配置即可使用
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
<bean name="dao" class="com.spring.dao.impl.AccountDao">
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="username" value="root"/>
<property name="password" value="adminadmin"/>
<property name="url" value="jdbc:mysql://localhost:3306/spring_test"/>
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
</bean>
<bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
进行到这里,我们会发现一个问题,如果我们有多个dao接口的实现类,他们其中的
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
这段代码是重复的
此时我们便可以提取这段代码 创建AccountDaoSupper类
package com.spring.dao.impl;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
public class AccountDaoSupper {
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
public void setDatasource(DataSource dataSource){
jdbcTemplate = new JdbcTemplate(dataSource);
}
}
然后再创建dao时就可以extends AccountDaoSupper implements IAccountDao
了
对应的其中所有对jdbcTemplate都将改为getJdbcTemplate()
改后效果是这样的:
package com.spring.dao.impl;
import com.spring.beans.Account;
import com.spring.dao.IAccountDao;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.List;
/**
* @author 28985
*/
public class AccountDao extends AccountDaoSupper implements IAccountDao {
@Override
public List<Account> findAll() {
return getJdbcTemplate().query("select * from account", new BeanPropertyRowMapper<Account>(Account.class));
}
@Override
public Account findById(Integer id) {
try {
return getJdbcTemplate().query("select * from account where id = ?", new BeanPropertyRowMapper<Account>(Account.class),id).get(0);
}
catch (Exception e){
Account account =new Account();
account.setName("NOTFOUND");
return account;
}
}
@Override
public void update(Account account) {
getJdbcTemplate().update("update account set name = ?,money = ? where id = ?",account.getName(),account.getMoney(),account.getId());
}
@Override
public void delete(Integer id) {
getJdbcTemplate().update("delete from account where id = ?",id);
}
@Override
public void insert(Account account) {
getJdbcTemplate().update("insert into account(name,money) values(?,?)",account.getName(),account.getMoney());
}
@Override
public Integer moneyNumber(Integer money) {
return getJdbcTemplate().queryForObject(" select count(*) from account where money > ? ", Integer.class, 900);
}
}
然后再进行xml的修改:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
<bean name="dao" class="com.spring.dao.impl.AccountDao">
<property name="datasource" ref="dataSource"/>
</bean>
<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="username" value="root"/>
<property name="password" value="adminadmin"/>
<property name="url" value="jdbc:mysql://localhost:3306/spring_test"/>
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
</bean>
</beans>
但实际上AccountDaoSupper中的代码Spring已经为我们提供好了,我们删除掉AccountDaoSupper换成继承JdbcDaoSupport这时便可以实现相同功能,而无需创建AccountDaoSupper了
package com.spring.dao.impl;
import com.spring.beans.Account;
import com.spring.dao.IAccountDao;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.util.List;
/**
* @author 28985
*/
public class AccountDao extends JdbcDaoSupport implements IAccountDao {
@Override
public List<Account> findAll() {
return getJdbcTemplate().query("select * from account", new BeanPropertyRowMapper<Account>(Account.class));
}
@Override
public Account findById(Integer id) {
try {
return getJdbcTemplate().query("select * from account where id = ?", new BeanPropertyRowMapper<Account>(Account.class),id).get(0);
}
catch (Exception e){
Account account =new Account();
account.setName("NOTFOUND");
return account;
}
}
@Override
public void update(Account account) {
getJdbcTemplate().update("update account set name = ?,money = ? where id = ?",account.getName(),account.getMoney(),account.getId());
}
@Override
public void delete(Integer id) {
getJdbcTemplate().update("delete from account where id = ?",id);
}
@Override
public void insert(Account account) {
getJdbcTemplate().update("insert into account(name,money) values(?,?)",account.getName(),account.getMoney());
}
@Override
public Integer moneyNumber(Integer money) {
return getJdbcTemplate().queryForObject(" select count(*) from account where money > ? ", Integer.class, 900);
}
}
spring中的事务
在需要事务时我们可以使用spring-tx这个jar包来进行事务管理
那么先进行maven导包的配置
<?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>org.example</groupId>
<artifactId>spring_day04_06TX_zhujie</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<packaging>jar</packaging>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.3.9</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.3.10</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.3.10</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.8.M1</version>
</dependency>
</dependencies>
</project>
首先我们先看看xml版本
首先最基本的当然是创建数据库封装的bean对象
package com.spring.beans;
/**
* @author 28985
*/
public class Account {
private int id;
private String name;
private float money;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getMoney() {
return money;
}
public void setMoney(float money) {
this.money = money;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", name='" + name + '\'' +
", money=" + money +
'}';
}
}
再来创建 持久层Dao的接口类
package com.spring.dao;
import com.spring.beans.Account;
import java.util.List;
/**
* @author 28985
*/
public interface IAccountDao {
/**
* 查找所有
* @return
*/
public List<Account> findAll();
/**
* 根据ID查找
* @return
*/
public Account findById(Integer id);
/**
* 更新
* @param account
*/
public void update(Account account);
/**
* 删除
* @param id
*/
public void delete(Integer id);
/**
* 插入
* @param account
*/
public void insert(Account account);
/**
* 大于多少钱的人数
* @param money
* @return
*/
public Integer moneyNumber(Integer money);
/**
* 根据名称查找账户
* @param name
* @return
*/
public Account findByName(String name);
}
再来创建他的实现类AccountDao
package com.spring.dao.impl;
import com.spring.beans.Account;
import com.spring.dao.IAccountDao;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.util.List;
/**
* @author 28985
*/
public class AccountDao extends JdbcDaoSupport implements IAccountDao {
@Override
public List<Account> findAll() {
return getJdbcTemplate().query("select * from account", new BeanPropertyRowMapper<Account>(Account.class));
}
@Override
public Account findById(Integer id) {
try {
return getJdbcTemplate().query("select * from account where id = ?", new BeanPropertyRowMapper<Account>(Account.class),id).get(0);
}
catch (Exception e){
Account account =new Account();
account.setName("NOTFOUND");
return account;
}
}
@Override
public void update(Account account) {
getJdbcTemplate().update("update account set name = ?,money = ? where id = ?",account.getName(),account.getMoney(),account.getId());
}
@Override
public void delete(Integer id) {
getJdbcTemplate().update("delete from account where id = ?",id);
}
@Override
public void insert(Account account) {
getJdbcTemplate().update("insert into account(name,money) values(?,?)",account.getName(),account.getMoney());
}
@Override
public Integer moneyNumber(Integer money) {
return getJdbcTemplate().queryForObject(" select count(*) from account where money > ? ", Integer.class, 900);
}
@Override
public Account findByName(String name) {
try {
List<Account> query = getJdbcTemplate().query("select * from account where name = ?", new BeanPropertyRowMapper<Account>(Account.class), name);
if (query.size()==1){
return query.get(0);
}
else {
return query.get(-1);
}
}catch (Exception e){
throw new RuntimeException(e);
}
}
}
创建业务层接口
package com.spring.service;
import com.spring.beans.Account;
/**
* 账户的业务层接口
* @author 28985
*/
public interface IAccountService {
/**
*根据id查询账户
* @param id
* @return
*/
Account findAccountById(Integer id);
/**
* 转账
* @param sourceName 转出账户名
* @param targetName 转入账户名称
* @param money 转账金额
*/
void transfer(String sourceName,String targetName,float money);
}
及业务层实现类
package com.spring.service.impl;
import com.spring.service.IAccountService;
import com.spring.beans.Account;
import com.spring.dao.IAccountDao;
/**
* @author 28985
*/
public class AccountServiceImpl implements IAccountService {
private IAccountDao accountDao;
public void setAccountDao(IAccountDao accountDao) {
this.accountDao = accountDao;
}
@Override
public Account findAccountById(Integer id) {
return accountDao.findById(id);
}
@Override
public void transfer(String sourceName, String targetName, float money) {
System.out.println("转账");
Account sourceAccount = accountDao.findByName(sourceName);
Account targetAccount = accountDao.findByName(targetName);
sourceAccount.setMoney(sourceAccount.getMoney()-money);
targetAccount.setMoney(targetAccount.getMoney()+money);
accountDao.update(sourceAccount);
int i= 1/0;
accountDao.update(targetAccount);
}
}
之后我们通过xml的方式对其进行配置
先看看不带事务的配置
<?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"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd">
<!-- 业务层-->
<bean name="accountServiceImpl" class="com.spring.service.impl.AccountServiceImpl">
<property name="accountDao" ref="dao"/>
</bean>
<!-- 持久层-->
<bean name="dao" class="com.spring.dao.impl.AccountDao">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 数据源-->
<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="username" value="root"/>
<property name="password" value="adminadmin"/>
<property name="url" value="jdbc:mysql://localhost:3306/spring_test"/>
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
</bean>
</beans>
编写主函数:
package com.spring.jdbcTemplate;
import com.spring.service.IAccountService;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class JdbcTemplate_test {
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("Bean.xml");
IAccountService service = (IAccountService) context.getBean("accountServiceImpl");
service.transfer("aaa","bbb",200);
}
}
此时执行主函数,会抛出异常,异常原因是因为业务层实现类里int i= 1/0;
,而且会进行错误的转账,这时我们就可以添加事务进行解决了
修改bean.xml
<?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"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd">
<!-- 业务层-->
<bean name="accountServiceImpl" class="com.spring.service.impl.AccountServiceImpl">
<property name="accountDao" ref="dao"/>
</bean>
<!-- 持久层-->
<bean name="dao" class="com.spring.dao.impl.AccountDao">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 数据源-->
<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="username" value="root"/>
<property name="password" value="adminadmin"/>
<property name="url" value="jdbc:mysql://localhost:3306/spring_test"/>
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
</bean>
<!-- 配置事务管理器-->
<bean name="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 配置事务通知-->
<tx:advice id="txAdvice" transaction-manager="transactionManager">
<!-- 配置事务的属性
isolation:用于指定事务的隔离级别。默认值是DEFAULT,表示使用数据库的默认隔离级别。
propagation:用于指定事务的传播行为。默认值是REQUIRED,表示一定会有事务,增删改的选择。查询方法可以选择SUPPORTS。
read-only:用于指定事务是否只读。只有查询方法才能设置为true。默认值是false,表示读写。
timeout:用于指定事务的超时时间,默认值是-1,表示永不超时。如果指定了数值,以秒为单位。
rollback-for:用于指定一个异常,当产生该异常时,事务回滚,产生其他异常时,事务不回滚。没有默认值。表示任何异常都回滚。
no-rollback-for:用于指定一个异常,当产生该异常时,事务不回滚,产生其他异常时事务回滚。没有默认值。表示任何异常都回滚。-->
<tx:attributes>
<tx:method name="*" propagation="REQUIRED" read-only="false"/>
<tx:method name="find*" propagation="SUPPORTS" read-only="true"/>
</tx:attributes>
</tx:advice>
<!-- 配置aop-->
<aop:config>
<!-- 配置切入点表达式-->
<aop:pointcut id="pt1" expression="execution(* com.spring.service.impl.*.*(..))"/>
<!-- 建立切入点表达式和事务通知的对应关系-->
<aop:advisor advice-ref="txAdvice" pointcut-ref="pt1"/>
</aop:config>
</beans>
即可完成事务的配置,此时运行便可以得到正常的结果
再来看看注解的配置
修改原来xml的bean.xml
主要注意第一句和最后一句的修改
<?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:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
<!-- 配置spring创建容器时要扫描的包-->
<context:component-scan base-package="com.spring"></context:component-scan>
<!-- 业务层-->
<bean name="accountServiceImpl" class="com.spring.service.impl.AccountServiceImpl">
<property name="accountDao" ref="dao"/>
</bean>
<!-- 持久层-->
<bean name="dao" class="com.spring.dao.impl.AccountDao">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 数据源-->
<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="username" value="root"/>
<property name="password" value="adminadmin"/>
<property name="url" value="jdbc:mysql://localhost:3306/spring_test"/>
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
</bean>
<!-- 配置事务管理器-->
<bean name="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 开启spring对注解事务的支持-->
<tx:annotation-driven transaction-manager="transactionManager"></tx:annotation-driven>
</beans>
然后今天我们就只需要配置需要添加事务的类,这里专指业务层实现类AccountServiceImpl
package com.spring.service.impl;
import com.spring.service.IAccountService;
import com.spring.beans.Account;
import com.spring.dao.IAccountDao;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
/**
* @author 28985
*/
@Transactional
public class AccountServiceImpl implements IAccountService {
private IAccountDao accountDao;
public void setAccountDao(IAccountDao accountDao) {
this.accountDao = accountDao;
}
@Transactional(propagation = Propagation.SUPPORTS,readOnly = true)
@Override
public Account findAccountById(Integer id) {
return accountDao.findById(id);
}
@Transactional(propagation = Propagation.REQUIRED,readOnly = false)
@Override
public void transfer(String sourceName, String targetName, float money) {
System.out.println("转账");
Account sourceAccount = accountDao.findByName(sourceName);
Account targetAccount = accountDao.findByName(targetName);
sourceAccount.setMoney(sourceAccount.getMoney()-money);
targetAccount.setMoney(targetAccount.getMoney()+money);
accountDao.update(sourceAccount);
// int i= 1/0;
accountDao.update(targetAccount);
}
}
这时我们对比xml和注解两种配置方式
我们发现注解要针对每个方法做出不同的配置
而xml则不需要