实验八:Spring的数据库编程
一、实验目的
1.熟悉JdbcTemplate的增删改查操作
2.掌握基于XML方式的声明式事务
3.熟悉基于注解方式的声明式事务
二、实验内容
1.使用JdbcTemplate的增删改查操作
2.实现基于XML方式的声明式事务
3.实现基于注解方式的声明式事务
三、实验步骤
1.使用JdbcTemplate的增删改查操作
(1)数据准备
创建spring数据库,创建account表:
create database spring;
use spring;
create table account(id int primary key auto_increment,username varchar(50),balance double);
(2)创建实体类
在src\main\java文件夹中创建com\sun文件夹,在文件夹中创建Account.java
package com.sun;
public class Account {
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Double getBalance() {
return balance;
}
public void setBalance(Double balance) {
this.balance = balance;
}
@Override
public String toString() {
return "账户ID=" + id + ", 用户名='" + username +", 账户余额=" + balance ;
}
private Integer id;
private String username;
private Double balance;
}
(3)编写Dao层接口
在src\main\java\com\sun文件夹中创建AccountDao接口
package com.sun;
import java.util.List;
public interface AccountDao {
public int addAccount(Account account);
public int updateAccount(Account account);
public int deleteAccount(int id);
}
(4)实现Dao层接口
在src\main\java\com\sun文件夹中创建AccountDaoImpl接口实现类
package com.sun;
导入相关包....
public class AccountDaoImpl implements AccountDao {
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate){
this.jdbcTemplate=jdbcTemplate;
}
public int addAccount(Account account){
String sql="insert into account(username,balance) value(?,?)";
Object[] obj=new Object[]{
account.getUsername(),
account.getBalance()
};
int num = this.jdbcTemplate.update(sql,obj);
return num;
}
public int updateAccount(Account account) {
String sql="update account set username=?,balance=? where id=?";
Object[] params=new Object[]{
account.getUsername(),
account.getBalance(),
account.getId()
};
int num=this.jdbcTemplate.update(sql,params);
return num;
}
public int deleteAccount(int id) {
String sql="delete from account where id=?";
int num=this.jdbcTemplate.update(sql,id);
return num;
}
}
(5)编写配置文件
在src\main\resources文件夹中创建applicationContext.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/aop https://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost/spring?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai" />
<property name="username" value="root" />
<property name="password" value="1234" />
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
<bean id="accountDao" class="com.sun.AccountDaoImpl">
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
(6)测试添加功能
在src\main\java\com\sun文件夹中创建TestAddAccount.java测试添加功能
package com.sun;
导入相关包....
public class TestAddAccount {
public static void main(String[] args) {
ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
AccountDao accountDao=(AccountDao)applicationContext.getBean("accountDao");
Account account=new Account();
account.setUsername("孙昊楠");
account.setBalance(1000.00);
int num=accountDao.addAccount(account);
if (num>0){
System.out.println("成功插入了"+num+"条数据!");
}else {
System.out.println("插入操作执行失败!");
}
}
}
运行结果:
(7)测试更新操作
在src\main\java\com\sun文件夹中创建TestUpdateAccount.java测试更新功能
package com.sun;
导入相关包....
public class TestUpdateAccount {
public static void main(String[] args) {
ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
AccountDao accountDao=(AccountDao)applicationContext.getBean("accountDao");
Account account=new Account();
account.setId(1);
account.setUsername("孙昊楠");
account.setBalance(20000.00);
int num=accountDao.updateAccount(account);
if (num>0){
System.out.println("成功修改了"+num+"条数据!");
}else {
System.out.println("插入操作执行失败!");
}
}
}
运行结果:
(8)测试更新操作
在src\main\java\com\sun文件夹中创建TestDeleteAccount.java测试删除功能
package com.sun;
导入相关包....
public class TestDeleteAccount {
public static void main(String[] args) {
ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
AccountDao accountDao=(AccountDao)applicationContext.getBean("accountDao");
int num=accountDao.deleteAccount(1);
if (num>0){
System.out.println("成功删除了"+num+"条数据!");
}else {
System.out.println("插入操作执行失败!");
}
}
}
运行结果:
(9)数据准备
向account表中插入数据
insert into account (id,username,balance)values(2,'孙昊楠2',1000),(3,'孙昊楠3',2662),(4,'孙昊楠4',6985);
(10)编写查询方法
在AccountDao接口中添加
public Account findAccount(int id);
public List<Account>findAccount();
(11)实现查询方法
在AccountDaoImpl接口实现类中添加
public Account findAccount(int id) {
String sql="select * from account where id=?";
RowMapper<Account>rowMapper=new BeanPropertyRowMapper<Account>(Account.class);
return this.jdbcTemplate.queryForObject(sql,rowMapper,id);
}
public List<Account> findAccount() {
String sql="select * from account";
RowMapper<Account>rowMapper=new BeanPropertyRowMapper<Account>(Account.class);
return this.jdbcTemplate.query(sql,rowMapper);
}
(12)测试条件查询
在src\main\java\com\sun文件夹中创建FindAccountByIdTest .java测试条件查询功能
package com.sun;
导入相关包....
public class FindAccountByIdTest {
public static void main(String[] args) {
ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
AccountDao accountDao=(AccountDao)applicationContext.getBean("accountDao");
Account account=accountDao.findAccount(1);
System.out.println(account);
}
}
运行结果:
(13)测试查询所有用户信息
在src\main\java\com\sun文件夹中创建FindAllAccountTest .java测试查询功能
package com.sun;
导入相关包....
public class FindAllAccountTest {
public static void main(String[] args) {
ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
AccountDao accountDao=(AccountDao)applicationContext.getBean("accountDao");
List<Account> account=accountDao.findAccount();
for (Account act:account){
System.out.println(act);
}
}
}
运行结果:
2.实现基于XML方式的声明式事务
(1)导入依赖
在pom.xml文件中添加依赖包
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.6</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>aopalliance</groupId>
<artifactId>aopalliance</artifactId>
<version>1.0</version>
</dependency>
(2)定义Dao层方法
在AccountDao接口中添加
public void transfer(String outUser,String inUser,Double money);
(3)实现Dao层方法
在AccountDaoImpl接口实现类中添加
public void transfer(String outUser, String inUser, Double money) {
this.jdbcTemplate.update("update account set balance=balance+? where username=?",money,outUser);
int i=1/0;
this.jdbcTemplate.update("update account set balance=balance-?
where username=?",money,inUser);
}
(4)修改配置文件
在applicationContext.xml文件中添加
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
(5)测试系统
在src\main\java\com\sun文件夹中创建测试类TransactionTest
package com.sun;
导入相关包....
public class TransactionTest {
public static void main(String[] args) {
ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
AccountDao accountDao=(AccountDao)applicationContext.getBean("accountDao");
accountDao.transfer("孙昊楠3","孙昊楠2",100.00);
System.out.println("转账成功!");
}
}
运行结果:
(6)使用事务管理测试系统
在applicationContext.xml文件中添加
<tx:advice id="txAdvice" transaction-manager="transactionManager">
<tx:attributes>
<tx:method name="*" propagation="REQUIRED" isolation="DEFAULT" read-only="false" />
</tx:attributes>
</tx:advice>
<aop:config>
<aop:pointcut expression="execution(* com.sun.*.*(..))" id="txPointCut" />
<aop:advisor advice-ref="txAdvice" pointcut-ref="txPointCut" />
</aop:config>
运行结果:
3.实现基于注解方式的声明式事务
(1)创建配置文件
在src\main\resources文件夹中创建applicationContext-annotation.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"
xmlns:context="http://www.springframework.org/schema/context"
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/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd">
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost/spring?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai" />
<property name="username" value="root" />
<property name="password" value="root" />
</bean>
<bean id="jdbcTemplate"
class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource" />
</bean>
<bean id="accountDao" class="com.sun.AccountDaoImpl">
<property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<tx:annotation-driven transaction-manager="transactionManager"/>
</beans>
(2)修改Dao层实现类
在AccountDaoImpl接口实现类中的transfer()方法前添加
@Transactional(propagation = Propagation.REQUIRED,isolation = Isolation.DEFAULT,readOnly = false)
public void transfer(String outUser, String inUser, Double money) {
this.jdbcTemplate.update("update account set balance=balance+? where username=?",money,outUser);
int i=1/0;
this.jdbcTemplate.update("update account set balance=balance-? where username=?",money,inUser);
}
(3)编写测试类
在src\main\java\com\sun文件夹中创建测试类AnnotationTest
package com.sun;
导入相关包....
public class AnnotationTest {
public static void main(String[] args) {
ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
AccountDao accountDao=(AccountDao)applicationContext.getBean("accountDao");
accountDao.transfer("孙昊楠3","孙昊楠2",100.00);
System.out.println("转账成功!");
}
}
运行结果: