一、创建一个Maven项目
首先创建一个Maven的quickstar项目
二、配置文件
1、设置jdbc.properties文件
这里是相关的mysql的连接配置，改成自己的数据库用户名和密码
#mysql驱动类
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/spring_jdbc?userUnicode=true&characterEncoding=utf8
#数据库用户名
jdbc.user=root
#数据库密码
jdbc.password=123456
2、spring.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:context="http://www.springframework.org/schema/context"
xmlns:cache="http://www.springframework.org/schema/cache"
xmlns:p="http://www.springframework.org/schema/p"
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
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd">
<context:component-scan base-package="com.whq"/>
<!--加载配置文件-->
<context:property-placeholder location="jdbc.properties"/>
<!--配置c3p0数据源-->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<!---->
<property name="driverClass" value="${jdbc.driver}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="user" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<!--配置jdbc模板对象，并注入一个数据源-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
<!--spring 事务配置
1、添加事务与aop 的命名空间
2、开启aop代理
3、配置事务管理器
4、配置事务通知
5、配置aop
-->
<!--开启aop代理-->
<aop:aspectj-autoproxy/>
<!--配置事务管理器-->
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<!--数据源 ref代表c3p0数据源-->
<property name="dataSource" ref="dataSource"/>
</bean>
<!--配置事务通知 transaction-manager表示绑定那个事务管理器-->
<tx:advice id="txAdvice" transaction-manager="txManager">
<!--定义什么方法使用事务处理-->
<tx:attributes>
<!--name属性代表的是方法名（或方法）-->
<tx:method name="add*" propagation="REQUIRED"/>
<tx:method name="update*" propagation="REQUIRED"/>
<tx:method name="delete*" propagation="REQUIRED"/>
<tx:method name="query*" propagation="REQUIRED"/>
</tx:attributes>
</tx:advice>
<!--配置aop:定义aop切面-->
<aop:config>
<!--设置切入点，设置需要被拦截的方法-->
<aop:pointcut id="cut" expression="execution(* com.whq.service..*.*(..))"/>
<!--设置通知，事务通知-->
<aop:advisor advice-ref="txAdvice" pointcut-ref="cut"/>
</aop:config>
</beans>
三、java代码实现
分别完成以下目录代码实现
1、Dao层实现
IAccount.java
package com.whq.dao;
import com.whq.po.Account;
import java.util.List;
/*
* 账户接口模块
* 1、添加账户
* 添加账户记录，返回受影响的行数
* 添加账户记录，返回主键
* 批量添加账户记录，返回受影响的行数
* 2.修改账户
* 修改账户记录，返回受影响的行数
* 批量修改账户记录，返回受影响的行数
* 3、删除账户
* 删除账户记录，返回受影响的行数
* 删除修改账户记录，返回受影响的行数
* 4、查询账户
* 查询指定用户的账户的总记录数，返回总记录数
* 查询指定账户的账户详情，返回账户对象
* 多条件查询指定用户的账户列表，返回账户集合
*
* */
public interface IAccount {
/**
* 添加账户
* * 添加账户记录，返回受影响的行数
* @param account
* @return
*/
public int addAccount(Account account);
/**
* 添加账户
* * 添加账户记录，返回主键
* @param account
*/
public int addAccountHasKey(Account account);
/**
* 添加账户
* * 批量添加账户记录，返回受影响的行数
* @param accounts
* @return
*/
public int addAccountBatch( List<Account> accounts);
public int queryAccountCount(int userId);
public Account queryAccountById(int accountId);
public List<Account> queryAccountByParams(Integer userId,String accountName,String accountType,String createTime);
public int updateAccount(Account account);
public int updateAccountBatch(List<Account> accounts);
public int deleteAccount(int accountId);
public int deleteAccountBatch(Integer[] ids);
/**
* 支出
* @param accountId
* @param money
* @return
*/
public int outAccount(Integer accountId,Integer money);
/**
* 收入
* @param accountId
* @param money
* @return
*/
public int inAccount(Integer accountId,Integer money);
}
2、Po层实现
Account.java实现
package com.whq.po;
import java.util.Date;
/*
* 用户账户类
* */
public class Account {
private Integer accountId;
private String accountName;
private String accountype;
private Integer money;
private String remark;
private Date createTime;
private Date updateTime;
private Integer userId;
public Account() {
}
public Account(String accountName, String accountype, Integer money, String remark, Integer userId) {
this.accountName = accountName;
this.accountype = accountype;
this.money = money;
this.remark = remark;
this.userId = userId;
}
public void setAccountId(Integer accountId) {
this.accountId = accountId;
}
public void setAccountName(String accountName) {
this.accountName = accountName;
}
public void setAccountype(String accountype) {
this.accountype = accountype;
}
public void setMoney(Integer money) {
this.money = money;
}
public void setRemark(String remark) {
this.remark = remark;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public void setUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public Integer getAccountId() {
return accountId;
}
public String getAccountName() {
return accountName;
}
public String getAccountype() {
return accountype;
}
public Integer getMoney() {
return money;
}
public String getRemark() {
return remark;
}
public Date getCreateTime() {
return createTime;
}
public Date getUpdateTime() {
return updateTime;
}
public Integer getUserId() {
return userId;
}
@Override
public String toString() {
return "Account{" +
"accountId=" + accountId +
", accountName='" + accountName + '\'' +
", accountype='" + accountype + '\'' +
", money=" + money +
", remark='" + remark + '\'' +
", createTime=" + createTime +
", updateTime=" + updateTime +
", userId=" + userId +
'}';
}
}
3、Impl层实现
AccoutDaoImpl.java实现
package com.whq.impl;
import com.whq.dao.IAccount;
import com.whq.po.Account;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import javax.annotation.Resource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
@Repository
public class AccoutDaoImpl implements IAccount {
//注入jabc模板
@Resource
private JdbcTemplate jdbcTemplate;
/**
* 添加账户记录，返回受影响的行数
* @param account
* @return
*/
@Override
public int addAccount(Account account) {
String sql = "insert into tb_account (account_name,account_type,money,remark,create_time,update_time,user_id) values(?,?,?,?,now(),now(),?)";
Object[] objs = {account.getAccountName(),account.getAccountype(),account.getMoney(),account.getRemark(),account.getUserId()};
int update = jdbcTemplate.update(sql, objs);
// System.out.println(update);
return update;
}
@Override
public int addAccountHasKey(Account account) {
//定义sql语句
String sql = "insert into tb_account (account_name,account_type,money,remark,create_time,update_time,user_id) values(?,?,?,?,now(),now(),?)";
//定义KeyHolder对象，获取记录的主键值
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(connection -> {
//预编译sql语句，并设置返回主键
PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
//设置参数
ps.setString(1,account.getAccountName());
ps.setString(2,account.getAccountype());
ps.setInt(3,account.getMoney());
ps.setString(4,account.getRemark());
ps.setInt(5,account.getUserId());
//返回预编译对象
return ps;
},keyHolder);
//得到返回的主键
int key = keyHolder.getKey().intValue();
return key;
}
@Override
public int addAccountBatch(List<Account> accounts) {
//定义sql语句
String sql = "insert into tb_account (account_name,account_type,money,remark,create_time,update_time,user_id) values(?,?,?,?,now(),now(),?)";
int row = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Account account = accounts.get(i);
//设置参数
ps.setString(1,account.getAccountName());
ps.setString(2,account.getAccountype());
ps.setInt(3,account.getMoney());
ps.setString(4,account.getRemark());
ps.setInt(5,account.getUserId());
}
@Override
public int getBatchSize() {
return accounts.size();
}
}).length;
return row;
}
@Override
public int queryAccountCount(int userId) {
//定义sql语句
String sql = "select count(1) from tb_account where user_id = ?";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class, userId);
return count;
}
/**
* 查询指定账户记录详情，返回账户对象
* @param accountId
* @return
*/
@Override
public Account queryAccountById(int accountId) {
//定义sql语句
String sql = "select * from tb_account where account_id = ?";
//查询对象
Account account = jdbcTemplate.queryForObject(sql, (ResultSet rs, int i)->{
Account acc = new Account();
acc.setAccountId(accountId);
acc.setAccountName(rs.getString("account_name"));
acc.setAccountype(rs.getString("account_type"));
acc.setMoney(rs.getInt("money"));
acc.setRemark(rs.getString("remark"));
acc.setUserId(rs.getInt("user_id"));
acc.setCreateTime(rs.getDate("create_time"));
acc.setUpdateTime(rs.getDate("update_time"));
return acc;
},accountId);
return account;
}
/**
* 多条件查询指定用户的账户记录列表，返回账户集合
* @param userId
* @param accountName
* @param accountType
* @param createTime
* @return
*/
@Override
public List<Account> queryAccountByParams(Integer userId, String accountName, String accountType, String createTime) {
//定义sql语句
String sql = "select * from tb_account where user_id = ?";
//定义参数列表
List<Object> params = new ArrayList<>();
params.add(userId);
//判断参数是否为空，如果不为空，拼接SQL语句及设置对应的参数
//账户名称
if (StringUtils.isNoneBlank(accountName)){
//拼接SQL语句
sql += " and account_name like concat('%',?,'%')";
//设置参数
params.add(accountName);
}
//账户类型
if (StringUtils.isNoneBlank(accountType)){
//拼接SQL语句
sql += " and account_type = ?";
//设置参数
params.add(accountType);
}
//账户类型
if (StringUtils.isNoneBlank(createTime)){
//拼接SQL语句
sql += " and create_time < ?";
//设置参数
params.add(createTime);
}
//定义参数 将集合转为数组
Object[] objs = params.toArray();
//查询集合
List<Account> accountList = jdbcTemplate.query(sql,objs,(ResultSet rs, int i)->{
Account acc = new Account();
acc.setAccountId(rs.getInt("account_id"));
acc.setAccountName(rs.getString("account_name"));
acc.setAccountype(rs.getString("account_type"));
acc.setMoney(rs.getInt("money"));
acc.setRemark(rs.getString("remark"));
acc.setUserId(rs.getInt("user_id"));
acc.setCreateTime(rs.getTimestamp("create_time"));
acc.setUpdateTime(rs.getTimestamp("update_time"));
return acc;
});
return accountList;
}
/**
* 更新指定的账户，返回受影响的行数
* @param account
* @return
*/
@Override
public int updateAccount(Account account) {
//定义sql语句
String sql = "update tb_account set account_name = ? , account_type = ? , money = ? , remark = ? , update_time = now(),user_id = ? where account_id = ?";
//设置参数
Object[] objs = {account.getAccountName(),account.getAccountype() ,account.getMoney(),account.getRemark() ,account.getUserId(),account.getAccountId()};
int row = jdbcTemplate.update(sql, objs);
return row;
}
/**
* 批量修改账户记录，返回受影响的行数
* @param accounts
* @return
*/
@Override
public int updateAccountBatch(List<Account> accounts) {
//定义sql语句
String sql = "update tb_account set account_name = ? , account_type = ? , money = ? , remark = ? , update_time = now(),user_id = ? where account_id = ?";
int rows = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Account account = accounts.get(i);
//设置参数
ps.setString(1,account.getAccountName());
ps.setString(2,account.getAccountype());
ps.setInt(3,account.getMoney());
ps.setString(4,account.getRemark());
ps.setInt(5,account.getUserId());
ps.setInt(6,account.getAccountId());
}
@Override
public int getBatchSize() {
return accounts.size();
}
}).length;
return rows;
}
/**
* 删除账户记录，返回受影响的行数
* @param accountId
* @return
*/
@Override
public int deleteAccount(int accountId) {
//定义sql语句
String sql = "delete from tb_account where account_id = ?";
int row = jdbcTemplate.update(sql, accountId);
return row;
}
/**
* 批量删除账户记录，返回影响的行数
* @param ids
* @return
*/
@Override
public int deleteAccountBatch(Integer[] ids) {
//定义sql语句
String sql = "delete from tb_account where account_id = ?";
int rows = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setInt(1,ids[i]);
}
@Override
public int getBatchSize() {
return ids.length;
}
}).length;
return rows;
}
/**
* 支出
* @param accountId
* @param money
* @return
*/
@Override
public int outAccount(Integer accountId, Integer money) {
//定义sql语句
String sql = "update tb_account set money = money - ? where account_id = ?";
Object[] objs = {money,accountId};
int row = jdbcTemplate.update(sql, objs);
return row;
}
/**
* 收入
* @param accountId
* @param money
* @return
*/
@Override
public int inAccount(Integer accountId, Integer money) {
//定义sql语句
String sql = "update tb_account set money = money + ? where account_id = ?";
Object[] objs = {money,accountId};
int row = jdbcTemplate.update(sql, objs);
return row;
}
}
4、Service层实现
AccountService.java实现
package com.whq.service;
import com.whq.dao.IAccount;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Resource;
@Service
public class AccountService {
@Resource
private IAccount accountDao;
/**
* 转账业务实现
* @param outId 支出账户
* @param inId 收入账户
* @param money
* @return
*/
@Transactional(propagation = Propagation.REQUIRED)
public int updateAccountByTranfer(Integer outId , Integer inId ,Integer money){
int code = 0;//成功或者失败，1成功，0失败
/**
* 账户A向B转账100元
* A：-100
* B：+100
*/
// 账户A支出，修改账户金额，返回受影响的行数
int outRow = accountDao.outAccount(outId,money);
//int i = 1/0;
// 账户B收入，修改账户金额，返回受影响的行数
int inRow = accountDao.inAccount(inId,money);
//如果支出和收入都转账成功，表示成功
if (outRow == 1 && inRow == 1){
code = 1;
}
return code;
}
}
5、测试
BaseTest.java基类
package com.whq;
import org.junit.runner.RunWith;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring.xml"})
public class BaseTest {
private JdbcTemplate jdbcTemplate;
}
1、添加操作
package com.whq;
import com.whq.dao.IAccount;
import com.whq.po.Account;
import org.junit.Test;
import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
public class SpringJdbcTest extends BaseTest{
@Resource
private IAccount accountDao;
@Test
public void testAddAccount(){
//准备添加的数据
Account account = new Account("zhanghu3","gongshang",200,"jiangjin",3);
//调用对象中的添加方法，返回受影响的行数
int row = accountDao.addAccount(account);
System.out.println("添加账户受影响的行数"+row);
}
@Test
public void testAddAccountHasKey(){
//准备添加的数据
Account account = new Account("zhanghu4","zhongguo",300,"jiangjin",4);
//调用对象中的添加方法，返回受影响的行数
int key = accountDao.addAccountHasKey(account);
System.out.println("添加账户返回主键"+key);
}
@Test
public void testBatchAddAccount(){
//准备添加的数据
Account account = new Account("zhanghu5","zhongguo",600,"jiangjin",5);
Account account2 = new Account("zhanghu6","gongshang",700,"jiangjin",5);
Account account3 = new Account("zhanghu7","nongye",800,"jiangjin",6);
List<Account> accounts = new ArrayList<>();
accounts.add(account);
accounts.add(account2);
accounts.add(account3);
//调用对象中的添加方法，返回受影响的行数
int rows = accountDao.addAccountBatch(accounts);
System.out.println("批量添加"+rows);
}
}
2、查询操作
package com.whq;
import com.whq.dao.IAccount;
import com.whq.po.Account;
import org.junit.Test;
import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
public class SpringJdbcQueryTest extends BaseTest{
@Resource
private IAccount accountDao;
@Test
public void testQueryAccountCount(){
int total = accountDao.queryAccountCount(1);
System.out.println("查询指定用户的账户总记录数："+total);
}
/**
* 查询指定账户记录的详情。返回账户对象
*/
@Test
public void testQueryAccountById(){
Account account = accountDao.queryAccountById(1);
System.out.println("查询指定用户的账户："+account.toString());
}
/**
* 多条件查询。返回账户列表
*/
@Test
public void testQueryAccountList(){
List<Account> accountList = accountDao.queryAccountByParams(5,null,null,null);
System.out.println("查询指定用户的账户："+accountList.toString());
List<Account> accountList2 = accountDao.queryAccountByParams(5,"5",null,null);
System.out.println("查询指定用户的账户2："+accountList2.toString());
List<Account> accountList3 = accountDao.queryAccountByParams(5,"5","gongshang",null);
System.out.println("查询指定用户的账户2："+accountList3.toString());
}
}
3、更新操作
package com.whq;
import com.whq.dao.IAccount;
import com.whq.po.Account;
import org.junit.Test;
import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
public class SpringJdbcUpdateTest extends BaseTest{
@Resource
private IAccount accountDao ;
/**
* 更新指定的账户，返回受影响的行数
*/
@Test
public void testUpdateAccount(){
Account account = new Account("zhanghu56","zhongguo",600,"jiangjin",5);
account.setAccountId(7);
int row = accountDao.updateAccount(account);
System.out.println("更新的行数"+row);
}
/**
* 批量更新指定的账户，返回受影响的行数
*/
@Test
public void testBatchUpdateAccount(){
//准备添加的数据
Account account = new Account("zhanghu55","zhongguo",600,"jiangjin",5);
account.setAccountId(7);
Account account2 = new Account("zhanghu66","gongshang",700,"jiangjin",5);
account2.setAccountId(8);
Account account3 = new Account("zhanghu77","nongye",800,"jiangjin",6);
account3.setAccountId(9);
List<Account> accounts = new ArrayList<>();
accounts.add(account);
accounts.add(account2);
accounts.add(account3);
//调用对象中的添加方法，返回受影响的行数
int rows = accountDao.updateAccountBatch(accounts);
System.out.println("批量更新"+rows);
}
}
4、删除操作
package com.whq;
import com.whq.dao.IAccount;
import org.junit.Test;
import javax.annotation.Resource;
public class SpringJdbcDeleteTest extends BaseTest{
@Resource
private IAccount accountDao;
/**
* 删除记录，返回受影响的行数
*/
@Test
public void testDeleteAccount(){
int row = accountDao.deleteAccount(1);
System.out.println("删除账户记录"+row);
}
/**
* 批量删除记录，返回受影响的行数
*/
@Test
public void testDeleteAccountBatch(){
Integer[] ids = {6,7,8,9};
int rows = accountDao.deleteAccountBatch(ids);
System.out.println("批量删除账户记录"+rows);
}
}
5、事务操作
package com.whq;
import com.whq.service.AccountService;
import org.junit.Test;
import javax.annotation.Resource;
public class SpringJdbcMoney extends BaseTest{
@Resource
private AccountService accountService;
@Test
public void test(){
int code = accountService.updateAccountByTranfer(2, 3, 100);
if (code == 1){
System.out.println("转账成功");
}
}
}
890
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
