spring+JDBC练习
spring+jdbc完成增删改查
创建一个maven项目,创建一个resources问件夹并作为资源目录。创建相应的包结构
既然要连接数据库就要导入连接数据库的jar包
mysql-connector-java
c3p0 dbcp druid (选其中一个连接池就行了)
spring-jdbc
在pomxml中的导入的jar包,看自己情况导什么jar包都行
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.1.4.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>5.1.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.1.4.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-logging/commons-logging -->
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.2</version>
</dependency>
<!--aop 面向切面编程需要的jar包-->
<!--aop-->
<!--aspects-->
<!-- https://mvnrepository.com/artifact/org.springframework/spring-aspects -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>5.1.14.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/aopalliance/aopalliance -->
<dependency>
<groupId>aopalliance</groupId>
<artifactId>aopalliance</artifactId>
<version>1.0</version>
</dependency>
<!--连接jdbc-->
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.1.14.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
在数据库中找一个数据库进行测试
先在测试文件中测试导入的jar是否可用
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import java.beans.PropertyVetoException;
//创建一个数据源
public class TestJDBC {
@Test
public void add() throws PropertyVetoException {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/mybaits");
dataSource.setUser("root");
dataSource.setPassword("123456");
//创建一个JdbcTemplate
JdbcTemplate template = new JdbcTemplate(dataSource);
//调用工具类中的方法数据
String sql = "insert into product(product_type,status) values(?,?)";
template.update(sql,new Object[]{"书",0});
}
}
之前删了一些数据所以id是25
代码示例
在xml文件中配置
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/tx
https://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd">
<!--com.lanou包下面使用注解,开启注解-->
<context:component-scan base-package="com.lanou"></context:component-scan>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/mybaits"></property>
<property name="user" value="root"></property>
<property name="password" value="123456"></property>
</bean>
<!--创建jdbcTemplate工具类对象-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
在pojo包中创建product实体类,跟数据库表的列名对应
public class Product {
private int id;
private String productType;
private int status;
//setter getter ...
}
在dao层创建接口
import java.util.List;
public interface ProductDao {
public List<Product> list();
void save(Product pro);
void update(Product pro);
void delete(int id);
int findTotalCount();
}
创建实现dao接口的实现类
import com.lanou.dao.ProductDao;
import com.lanou.pojo.Product;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
@Repository
public class ProductDaoImpl implements ProductDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public List<Product> list() {
System.out.println("------qq------");
String sql = "select * from product";
List<Product> list = jdbcTemplate.query(sql, new RowMapper<Product>() {
@Override
public Product mapRow(ResultSet resultSet, int i) throws SQLException {
Product p = new Product();
p.setId(resultSet.getInt("id"));
p.setProductType(resultSet.getString("product_type"));
p.setStatus(resultSet.getInt("status"));
return p;
}
});
return list;
}
@Override
public void save(Product pro) {
String sql = "insert into product (product_type,status) values (?,?)";
jdbcTemplate.update(sql,pro.getProductType(),pro.getStatus());
}
@Override
public void update(Product pro) {
String sql = "update product set product_type=?,status=? where id = ?";
jdbcTemplate.update(sql,pro.getProductType(),pro.getStatus(),pro.getId());
}
@Override
public void delete(int id) {
String sql = "delete from product where id =?";
jdbcTemplate.update(sql,id);
}
@Override
public int findTotalCount() {
return jdbcTemplate.queryForObject("select count(*) from product",Integer.class);
}
}
实现service层的代码
package com.lanou.service;
import com.lanou.pojo.Product;
import java.util.List;
public interface ProductService {
public List<Product> list();
}
import com.lanou.dao.ProductDao;
import com.lanou.pojo.Product;
import com.lanou.service.ProductService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class ProductServiceImpl implements ProductService {
@Autowired
private ProductDao productDao;
@Override
public List<Product> list() {
productDao.list();
return null;
}
}
controller层
import com.lanou.service.ProductService;
import org.springframework.beans.factory.annotation.Autowired;
@org.springframework.stereotype.Controller
public class ProductController {
@Autowired
private ProductService productService;
public void list(){
productService.list();
}
}
测试类中的测试
import com.lanou.controller.ProductController;
import com.lanou.dao.Impl.ProductDaoImpl;
import com.lanou.dao.ProductDao;
import com.lanou.pojo.Product;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.Arrays;
import java.util.List;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class TestJDBCDao {
@Autowired
private ProductDao productDao;
@Test
public void add(){
Product product = new Product();
product.setProductType("大碗");
product.setStatus(0);
productDao.save(product);
}
@Test
public void update(){
Product product = new Product();
product.setId(23);
product.setProductType("大碗1");
productDao.update(product);
}
@Test
public void delete(){
productDao.delete(23);
}
@Test
public void findTotalCount(){
int i = productDao.findTotalCount();
System.out.println(i);
}
@Test
public void findAll(){
List<Product> list = productDao.list();
System.out.println(list.toString());//输出
}
}
事务
导包
spring-tx
<!--导入事务的jar-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.1.14.RELEASE</version>
</dependency>
数据库表语句
CREATE TABLE `ar_account` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`username` VARCHAR(20) NOT NULL,
`money` DECIMAL(10,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*Data for the table `ar_account` */
INSERT INTO `ar_account`(`id`,`username`,`money`) VALUES (1,'Helen','1000.00');
INSERT INTO `ar_account`(`id`,`username`,`money`) VALUES (2,'Tom','1000.00');
账户的实体类
package com.lanou.pojo;
public class ArAccount {
private int id;
private String username;
private double money;
//...
}
dao层中的方法
public interface ArAccountDao {
//减少资金
public void decrease(int id,double money);
//增加资金
public void increase(int id,double money);
}
dao的实现方法
@Repository
public class AracountDaoImpl implements ArAccountDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
//减少用户资金
public void decrease(int id, double money) {
jdbcTemplate.update("update ar_account set money= money-? where id = ?",new Object[]{money,id});
}
@Override
//增加用户资金
public void increase(int id, double money) {
jdbcTemplate.update("update ar_account set money=money+? where id = ?",new Object[]{money,id});
}
}
service层接口
public interface ArAccountService {
//转账业务
public void transferMoney(int fromId,int told,double money);
}
接口实现类
@Service
public class ArAccountServiceImpl implements ArAccountService {
@Autowired
private ArAccountDao arAccountDao;
@Override
public void transferMoney(int fromId, int told, double money) {
//调用一个账户得减少办法
arAccountDao.decrease(fromId,money);
//System.out.println(1/0);
//调用一个账户增加的方法
arAccountDao.increase(told,money);
}
测试方法
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class TestTranfer {
@Autowired
private ArAccountService arAccountService;
@Test
public void tranferMoney(){
arAccountService.transferMoney(1,2,100);
}
}
可以发现当执行时不报错时可以看到代码正常执行,但是当报错时(打开//System.out.println(1/0);)可以看到数据库中的一个人少了一百,一个人没多钱。
所以,要加上事务进行处理
事务
在xml配置文件中加上事务
<!--事务切面bean-->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!--配置事务通知-->
<tx:advice id="txAdvice" transaction-manager="transactionManager">
<tx:attributes>
<!--
根据service层的方法名,判断是否需要事务操作
增删改需要事务,查询不需要事务
name="方法名"
propagation=“事务的传播行为” 两个方法间调用
-->
<tx:method name="*" propagation="REQUIRED" rollback-for=""/>
</tx:attributes>
</tx:advice>
<!--配置事务切面-->
<aop:config>
<!--配置切入点-->
<aop:pointcut id="pt" expression="execution(* com..service..*.*(..))"></aop:pointcut>
<!--配置切入点和切面关联-->
<aop:advisor advice-ref="txAdvice" pointcut-ref="pt"></aop:advisor>
</aop:config>
执行报错时可以看到数据库中的数据都没变,说明事务起作用了
基于注解的事务
把xml文件中的事务配置给注释掉,加入如下:
<!--开启事务注解模式-->
<tx:annotation-driven></tx:annotation-driven>
在类上使用,对当前类中所有方法有效
在业务层上的实现类中使用
@Service
@Transactional(
propagation = Propagation.REQUIRED,
isolation = Isolation.READ_COMMITTED,
timeout = 600
)
public class ArAccountServiceImpl implements ArAccountService {
//....
}
在数据库中查看可以看到数据报错后账户的钱没有减少
事务配置配置详解
<tx:method name="*" propagation=“REQUIRED” rollback-for=""/>
增删改需要事务,查询不需要事务
根据service层的方法名,判断是否需要事务操作
增删改需要事务,查询不需要事务
name=“方法名”
propagation=“事务的传播行为” 两个方法间调用
传播行为:多个方法相互调用时,事务如何执行?
int PROPAGATION_REQUIRED = 0; //支持当前事务,如果不存在,就新建一个
int PROPAGATION_SUPPORTS = 1; //支持当前事务,如果不存在,就不使用事务
int PROPAGATION_MANDATORY = 2; //支持当前事务,如果不存在,就抛出异常
int PROPAGATION_REQUIRES_NEW = 3;//如果有事务存在,挂起当前事务,创建一个新的事物
int PROPAGATION_NOT_SUPPORTED = 4;//以非事务方式运行,如果有事务存在,挂起当前事务
int PROPAGATION_NEVER = 5;//以非事务方式运行,如果有事务存在,就抛出异常
int PROPAGATION_NESTED = 6;//如果有事务存在,则嵌套事务执行
事务的5种隔离级别
int ISOLATION_DEFAULT = -1;//默认级别,
- MYSQL: 默认为REPEATABLE_READ级别
- SQLSERVER: 默认为READ_COMMITTED
int ISOLATION_READ_UNCOMMITTED = 1;//读取未提交数据(会出现脏读, 不可重复读) 基本不使用
int ISOLATION_READ_COMMITTED = 2;//读取已提交数据(会出现不可重复读和幻读)
int ISOLATION_REPEATABLE_READ = 4;//可重复读(会出现幻读)
int ISOLATION_SERIALIZABLE = 8;//串行化
两个事务 a b
查询数据条数10 查询数据条数10
insert 没有commit 查询10(READ_COMMITTED) 11
其他方法的作用
readOnly:读写性事务,只读性事务
timeout:超时时间,单位秒 -1超时不管
no-rollback-for:一组异常类的实例,遇到时必须不回滚,遇到某某异常,不处理
rollback-for:一组异常类的实例,遇到某某异常,自定义一个异常方法,执行这个自定义方法