JdbcTemplate是什么?
-
JdbcTemplate是spring框架中提供的一个模板对象,是对原始繁琐的Jdbc API对象的简单封装。
核心对象 JdbcTemplate jdbcTemplate = new JdbcTemplate(DataSource dataSource); 核心方法 int update(); 执行增、删、改语句 List<T> query(); 查询多个 T queryForObject(); 查询一个 new BeanPropertyRowMapper<>(); 实现ORM映射封装
-
查询数据库所有账号信息到Account实体中
public class JdbcTemplateTest { @Test public void testFindAll() throws Exception { // 创建核心对象 JdbcTemplate jdbcTemplate = new JdbcTemplate(JdbcUtils.getDataSource()); // 编写sql String sql = "select * from account"; // 执行sql List<Account> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Account.class)); } }
Spring整合JdbcTemplate
-
基于Spring的xml配置实现账户的CRUD案例
-
AccountDao接口和实现类
package com.code.dao; import com.code.entity.Account; import java.util.List; public interface AccountDao { public List<Account> findAll(); public Account findById(Integer id); public void save(Account account); public void update(Account account); public void delete(Integer id); }
package com.code.dao.impl; import com.code.dao.AccountDao; import com.code.entity.Account; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; import java.util.List; @Repository public class AccountDaoImpl implements AccountDao { @Autowired private JdbcTemplate jdbcTemplate; /* 查询所有账户 */ @Override public List<Account> findAll() { // 需要用到jdbcTemplate String sql = "select * from account"; List<Account> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Account>(Account.class)); return list; } /* 根据ID查询账户 */ @Override public Account findById(Integer id) { String sql = "select * from account where id = ?"; Account account = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Account>(Account.class), id); return account; } /* 添加账户 */ @Override public void save(Account account) { String sql = "insert into account values(null,?,?)"; jdbcTemplate.update(sql,account.getName(),account.getMoney()); } /* 更新账户 */ @Override public void update(Account account) { String sql = "update account set money = ? where name = ?"; jdbcTemplate.update(sql,account.getMoney(),account.getName()); } /* 根据ID删除账户 */ @Override public void delete(Integer id) { String sql = "delete from account where id = ?"; jdbcTemplate.update(sql,id); } }
-
AccountService接口和实现类
package com.code.service; import com.code.entity.Account; import java.util.List; public interface AccountService { /* 查询所有账户 */ public List<Account> findAll(); /* 根据ID查询账户 */ public Account findById(Integer id); /* 添加账户 */ public void save(Account account); /* 更新账户信息 */ public void update(Account account); /* 根据ID删除账户 */ public void delete(Integer id); }
package com.code.service.impl; import com.code.dao.AccountDao; import com.code.entity.Account; import com.code.service.AccountService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; @Service public class AccountServiceImpl implements AccountService { @Autowired private AccountDao accountDao; @Override public List<Account> findAll() { List<Account> all = accountDao.findAll(); return all; } @Override public Account findById(Integer id) { Account account = accountDao.findById(id); return account; } @Override public void save(Account account) { accountDao.save(account); } @Override public void update(Account account) { accountDao.update(account); } @Override public void delete(Integer id) { accountDao.delete(id); } }
-
spring核心配置文件
<?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: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/context http://www.springframework.org/schema/context/spring-context.xsd"> <!--IOC注解扫描--> <context:component-scan base-package="com.code"/> <!--引入properties--> <context:property-placeholder location="classpath:jdbc.properties"/> <!--dataSource--> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"> <property name="driverClassName" value="${jdbc.driverClassName}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </bean> <!--jdbcTemplate--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <constructor-arg name="dataSource" ref="dataSource"/> </bean> </beans>
Spring实现转账案例
-
AccountDao接口和实现类
package com.code.dao; public interface AccountDao { /* 减钱:转出操作 */ public void out(String outUser,Double money); /* 加钱:转入操作 */ public void in(String inUser,Double money); }
package com.code.dao.impl; import com.code.dao.AccountDao; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; @Repository public class AccountDaoImpl implements AccountDao { @Autowired private JdbcTemplate jdbcTemplate; @Override public void out(String outUser, Double money) { String sql = "update account set money = money - ? where name = ?"; jdbcTemplate.update(sql, money, outUser); } @Override public void in(String inUser, Double money) { String sql = "update account set money = money + ? where name = ?"; jdbcTemplate.update(sql, money, inUser); } }
-
AccountService接口和实现类
package com.code.service; public interface AccountSerivce { /* 转账方法 */ public void transfer(String outUser,String inUser,Double money); }
package com.code.service.impl; import com.code.dao.AccountDao; import com.code.service.AccountSerivce; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Isolation; import org.springframework.transaction.annotation.Propagation; import org.springframework.transaction.annotation.Transactional; @Service @Transactional public class AccountServiceImpl implements AccountSerivce { @Autowired private AccountDao accountDao; @Override @Transactional(propagation = Propagation.REQUIRED,isolation = Isolation.REPEATABLE_READ,timeout = -1,readOnly = false) public void transfer(String outUser, String inUser, Double money) { //调用dao的out及in方法 accountDao.out(outUser,money); // int i = 1/0; accountDao.in(inUser,money); } }
-
spring核心配置文件
<?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: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/context http://www.springframework.org/schema/context/spring-context.xsd"> <!--IOC注解扫描--> <context:component-scan base-package="com.lagou"/> <!--加载jdbc配置文件--> <context:property-placeholder location="classpath:jdbc.properties"/> <!--把数据库连接池交给IOC容器--> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"> <property name="driverClassName" value="${jdbc.driver}"></property> <property name="url" value="${jdbc.url}"></property> <property name="username" value="${jdbc.username}"></property> <property name="password" value="${jdbc.password}"></property> </bean> <!--把JdbcTemplate交给IOC容器--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <constructor-arg name="dataSource" ref="dataSource"></constructor-arg> </bean> </beans>