使用spring jdbc 完成账户单表crud *** 作

使用spring jdbc 完成账户单表crud *** 作 搭建数据库

项目骨架

环境搭建浏览主页

定义实体类

Account.java

    private Integer accountId; // 账户ID，主键
    private String accountName; // 账户名称
    private String accountType; // 账户类型
    private Double money; // 账户金额
    private String remark; // 账户备注
    private Integer userId; // 用户ID，账户所属用户
    private Date createTime; // 创建时间
    private Date updateTime; // 修改时间
    

定义接口类

public interface IAccountDao {

public int addAccount(Account account) ;

public int addAccountHasKey(Account account);

public int addAccountBatch(List accounts);

public int queryAccountCount(Integer userId);

public Account queryAccountById(Integer accountId);

public List queryAccountsByParams(Integer userId, String
accountName, String accountType, String createTime);

public int updateAccountById(Account account);

public int updateAccountBatch(List accounts);

public Integer deleteAccoutById(Integer accountId);

public int deleteAccountBatch(Integer[] ids);
}

定义接口实现类

快捷键快速实现接口类

@Repository
public class AccountDaoImpl implements IAccountDao {
// JdbcTemplate 模板类注入
@Resource
private JdbcTemplate jdbcTemplate;
@Override
public int addAccount(Account account) {
return 0;
}
@Override
public int addAccountHasKey(Account account) {
return 0;
}
@Override
public int addAccountBatch(List accounts) {
return 0;
}
@Override
public int queryAccountCount(Integer userId) {
return 0;
}
@Override
public Account queryAccountById(Integer accountId) {
return null;
}
eturn null;
}
@Override
public int updateAccountById(Account account) {
return 0;
}
@Override
public int updateAccountBatch(List accounts) {
return 0;
}
@Override
public Integer deleteAccoutById(Integer accountId) {
return null;
}
@Override
public int deleteAccountBatch(Integer[] ids) {
return 0;
}
}

添加账户记录

 添加单条记录，返回受影响的行数
* @param account
* @return
*/
@Override
public int addAccount(Account account) {
String sql = "insert into
tb_account(account_name,account_type,money,remark," +
"user_id,create_time,update_time) values (?,?,?,?,?,now(),now())";
Object[] objs = {account.getAccountName(),account.getAccountType(),
account.getMoney(),account.getRemark(),account.getUserId()};
return jdbcTemplate.update(sql,objs);
}

测试方法

@Test
public void testAddAccount() {
// 准备要添加的数据
Account account = new Account("张三","建设银行",100.0,"零花钱",1);
// 调用对象的添加方法，返回受影响的行数
int row = accountDao.addAccount(account);
System.out.println("添加账户受影响的行数：" + row);
}

添加记录返回主键

@Override
public int addAccountHasKey(Account account) {
String sql = "insert into
tb_account(account_name,account_type,money,remark," +
"user_id,create_time,update_time) 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.getAccountType());
ps.setDouble(3,account.getMoney());
ps.setString(4,account.getRemark());
ps.setInt(5,account.getUserId());
return ps;
},keyHolder);
// 得到返回的主键
Integer key = keyHolder.getKey().intValue();
return key;
}

测试方法

@Test
public void testAddAccountHasKey() {
// 准备要添加的数据
Account account = new Account("李四","招商银行",200.0,"兼职费",2);
// 调用对象的添加方法，返回主键
int key = accountDao.addAccountHasKey(account);
System.out.println("添加账户返回的主键：" + key);
}

批量添加账户记录

@Override
public int addAccountBatch(final List accounts) {
String sql = "insert into
tb_account(account_name,account_type,money,remark," +
"user_id,create_time,update_time) values (?,?,?,?,?,now(),now())";
int rows = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter()
{
@Override
public void setValues(PreparedStatement preparedStatement, int i)
throws SQLException {
// 设置参数
preparedStatement.setString(1,accounts.get(i).getAccountName());
preparedStatement.setString(2,accounts.get(i).getAccountType());
preparedStatement.setDouble(3,accounts.get(i).getMoney());
preparedStatement.setString(4,accounts.get(i).getRemark());
preparedStatement.setInt(5,accounts.get(i).getUserId());
}
@Override
public int getBatchSize() {
return accounts.size();
}
}).length;
return rows;
}

测试方法

@Test
public void testAddAccountBatch() {
// 准备要添加的数据
Account account = new Account("王五","农业银行",2000.0,"工资",3);
Account account2 = new Account("赵六","中国银行",280.0,"奖金",3);
Account account3 = new Account("田七","工商银行",800.0,"零花钱",3);
List accountList = new ArrayList<>();
accountList.add(account);
accountList.add(account2);
accountList.add(account3);
// 调用对象的添加方法，返回主键
int rows = accountDao.addAccountBatch(accountList);
System.out.println("批量添加账户受影响的行数：" + rows);
}

账户记录查询实现

查询用户的账户总记录数

@Override
public int queryAccountCount(Integer userId) {
String sql = "select count(1) from tb_account where user_id = ?";
int count = jdbcTemplate.queryForObject(sql,Integer.class,userId);
return count;
}

测试方法

@Test
public void testQueryAccountCount(){
// 查询ID为1的用户的账户总记录数
int total = accountDao.queryAccountCount(1);
System.out.println("总记录数：" + total);
}

查询指定账户记录详情

@Override
public Account queryAccountById(Integer accountId) {
String sql = "select * from tb_account where account_id = ?";
Account account = jdbcTemplate.queryForObject(sql, new Object[]
{accountId}, (resultSet, i) -> {
Account acc = new Account();
acc.setAccountId(resultSet.getInt("account_id"));
acc.setMoney(resultSet.getDouble("money"));
acc.setAccountName(resultSet.getString("account_name"));
acc.setAccountType(resultSet.getString("account_type"));
acc.setRemark(resultSet.getString("remark"));
acc.setCreateTime(resultSet.getDate("create_time"));
acc.setUpdateTime(resultSet.getDate("update_time"));
acc.setUserId(resultSet.getInt("user_id"));
return acc;
});
return account;
}

测试方法

@Test
public void testQueryAccountById(){
// 查询ID为1的账户记录的详情
Account account = accountDao.queryAccountById(1);
System.out.println("账户详情：" + account.toString());
}

多条件查询用户账户记录

* 多条件查询指定用户的账户列表，返回账户集合
* @param userId 用户Id
* @param accountName 账户名称 （模糊查询）
* @param accountType 账户类型
* @param createTime 账户创建时间
* @return
*/
@Override
public List queryAccountsByParams(Integer userId, String accountName,
String accountType,
String createTime) {
String sql = "select * from tb_account where user_id = ? ";
List