Spring框架降低了javaeeAPI的使用难度,其中包括JDBC,下面进行SpringJDBC的讲解
一.JbdcTemplate是什么?
spring提供用于操作数据库模版,类似Dbutils，操作数据的时候spring也会帮我们提供一个操作数据库的工具供我们使用，而不用我们自己手动编写连接数据库，获取结果集等等操作，这个工具就是JdbcTemplate。
下面是一个实例
不过在这之前我们先建立一个数据库 取名为spring
1.导包
2.写配置文件,相当于配置Dbutil类
<?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 id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost/spring"/> <property name="username" value="root"/> <property name="password" value="19981998"/> </bean> <!--配置jdbc模板 --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"/> </bean> </beans>
测试
这个例子是JbdcTemplate常用方法中的execute()方法
其他的update()和query()方法在下面的操作中直接展示,直接贴代码
二:Spring事物管理
基于上面的来操作,基于注解进行事物管理
Account类
package com.zlj.jdbc; public class Account { private Integer id; private String username; private Double balance; 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; } public String toString(){ return "Account[id="+id+", username="+username+", balance="+balance+"]"; } }
AccountDaoImpl类(在transfer方法中使用了@Transactional注解)
package com.zlj.jdbc; import java.util.List; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.PreparedStatementSetter; import org.springframework.jdbc.core.RowMapper; import org.springframework.transaction.annotation.Isolation; import org.springframework.transaction.annotation.Propagation; import org.springframework.transaction.annotation.Transactional; 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; } public Account findAccountById(int id) { String sql = "select * from account where id = ?"; //创建Mapper对象 RowMapper<Account> rm = new BeanPropertyRowMapper<Account>(Account.class); return this.jdbcTemplate.queryForObject(sql, rm,id); } public List<Account> findAllAccount() { String sql = "select * from account"; RowMapper<Account> rm = new BeanPropertyRowMapper<Account>(Account.class); return this.jdbcTemplate.query(sql, rm); } @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,inUser); //模拟系统运行的突发性问题 //int i = 1/0; //模拟汇款人 this.jdbcTemplate.update("update account set balance = balance-?" +"where username=?",money,outUser); } }
配置文件(记住头文件一定要写全)
<?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-3.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-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 "> <!-- 配置数据源 --> <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"/> <property name="username" value="root"/> <property name="password" value="19981998"/> </bean> <!--配置jdbc模板 --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"/> </bean> <!-- 定义id为accountDao的Bean --> <bean id="accountDao" class="com.zlj.jdbc.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>
测试类
package com.zlj.jdbc; import java.util.List; import org.junit.Test; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.JdbcTemplate; //execute() public class JdbcTemplateTest { public static void main(String[] args) { ApplicationContext ac = new ClassPathXmlApplicationContext("Bean.xml"); JdbcTemplate jb = (JdbcTemplate) ac.getBean("jdbcTemplate"); jb.execute("create table account("+"id int primary key auto_increment,"+"username varchar(50),"+"balance double)"); System.out.println("account表创建成功"); } @Test public void add(){ ApplicationContext ac = new ClassPathXmlApplicationContext("Bean.xml"); AccountDao accountDao = (AccountDao) ac.getBean("accountDao"); Account account = new Account(); account.setUsername("tom"); account.setBalance(1000.00); int num = accountDao.addAccount(account); if(num>0) System.out.println("成功插入"+num+"条数据"); else System.out.println("操作失败"); } @Test public void findId(){ ApplicationContext ac = new ClassPathXmlApplicationContext("Bean.xml"); AccountDao accountDao = (AccountDao) ac.getBean("accountDao"); Account account = accountDao.findAccountById(1); System.out.println(account); } @Test public void findAll(){ ApplicationContext ac = new ClassPathXmlApplicationContext("Bean.xml"); AccountDao accountDao = (AccountDao) ac.getBean("accountDao"); List<Account> account = accountDao.findAllAccount(); for(Account act:account){ System.out.println(act); } } @Test public void shiwuTest(){ ApplicationContext ac = new ClassPathXmlApplicationContext("Bean.xml"); AccountDao accountDao = (AccountDao) ac.getBean("accountDao"); accountDao.transfer("Tom", "leonard", 666.0); System.out.println("成功转账.........."); } }
结果
转账成功,如果有int i=1/0那么就不能执行,执行事物的错误操作