packagecom.shsxt;importcom.shsxt.po.Account;importcom.sun.xml.internal.bind.v2.model.core.ID;importorg.apache.commons.lang3.StringUtils;importorg.springframework.jdbc.core.BatchPreparedStatementSetter;importorg.springframework.jdbc.core.JdbcTemplate;importorg.springframework.jdbc.core.PreparedStatementCreator;importorg.springframework.jdbc.core.RowMapper;importorg.springframework.jdbc.support.GeneratedKeyHolder;importorg.springframework.jdbc.support.KeyHolder;importorg.springframework.stereotype.Component;importorg.springframework.stereotype.Repository;importjavax.annotation.Resource;importjavax.lang.model.element.Name;importjava.math.BigDecimal;import java.sql.*;importjava.util.ArrayList;importjava.util.List;
@Repositorypublic class AccountDaoImpl implementsAccountDao {
@ResourceprivateJdbcTemplate jdbcTemplate;/*** 添加记录返回受影响行数
*@paramaccount
*@return
*/@OverridepublicInteger saveAccount(Account account) {
String sql="insert into account (name,type,money,remark,create_time,update_time,userId) values(?,?,?,?,?,?,?)";returnjdbcTemplate.update(sql,account.getName(),account.getType(),account.getMoney(),account.getRemark(),account.getCreate_time(),account.getUpdate_time(),account.getUserId());
}/*** 添加记录返回主键
*@paramaccount
*@return
*/@OverridepublicInteger saveAccountHasPrimaryKey(Account account) {
String sql="insert into account (name,type,money,remark,create_time,update_time,userId) values(?,?,?,?,?,?,?)";
KeyHolder keyHolder=newGeneratedKeyHolder();
jdbcTemplate.update(newPreparedStatementCreator() {
@Overridepublic PreparedStatement createPreparedStatement(Connection con) throwsSQLException {
PreparedStatement ps=con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1,account.getName());
ps.setString(2,account.getType());
ps.setBigDecimal(3,account.getMoney());
ps.setString(4,account.getRemark());
ps.setObject(5,account.getCreate_time());
ps.setObject(6,account.getUpdate_time());
ps.setInt(7,account.getUserId());returnps;
}
},keyHolder);returnkeyHolder.getKey().intValue();
}/*** 批量添加记录
*@paramaccounts
*@return
*/@Overridepublic Integer saveAccountsBatch(Listaccounts) {
String sql="insert into account (name,type,money,remark,create_time,update_time,userId) values(?,?,?,?,?,?,?)";return jdbcTemplate.batchUpdate(sql, newBatchPreparedStatementSetter() {
@Overridepublic void setValues(PreparedStatement ps, int i) throwsSQLException {
ps.setString(1,accounts.get(i).getName());
ps.setString(2,accounts.get(i).getType());
ps.setBigDecimal(3,accounts.get(i).getMoney());
ps.setString(4,accounts.get(i).getRemark());
ps.setObject(5,accounts.get(i).getCreate_time());
ps.setObject(6,accounts.get(i).getUpdate_time());
ps.setInt(7,accounts.get(i).getUserId());
}
@Overridepublic intgetBatchSize() {returnaccounts.size();
}
}).length;
}/*** 统计账户记录-聚合查询
*@paramuserId
*@return
*/@OverridepublicInteger countAccountsByUserId(Integer userId) {
String sql="select count(1) from account where userId=?";return jdbcTemplate.queryForObject(sql,Integer.class,userId);
}/*** 根据id查询记录详情
*@paramId
*@return
*/@OverridepublicAccount queryAccountById(Integer Id) {
String sql="select id,userId,name,type,money,remark,update_time,create_time from account where id=?";return (Account) jdbcTemplate.queryForObject(sql,new Object[]{Id}, new RowMapper() {
@Overridepublic Account mapRow(ResultSet rs, int rowNum) throwsSQLException {
Account account=newAccount();
account.setUpdate_time(rs.getDate("update_time"));
account.setRemark(rs.getString("remark"));
account.setType(rs.getString("type"));
account.setMoney(rs.getBigDecimal("money"));
account.setCreate_time(rs.getDate("create_time"));
account.setId(rs.getInt("id"));
account.setName(rs.getString("name"));
account.setUserId(rs.getInt("userId"));returnaccount;
}
});
}/*** 多条件查询
*@paramuserId
*@paramtype
*@paramcreateTime
*@paramaname
*@return
*/@Overridepublic ListqueryAccountsByParams(Integer userId, String type, String createTime, String aname) {
StringBuffer sql=new StringBuffer("select id,userId,name,type,money,remark,update_time,create_time from account where 1=1");
List params=new ArrayList<>();if (null!=userId){
sql.append(" and userId=?");
params.add(userId);
}if(StringUtils.isNotBlank(type)){
sql.append(" and type=? ");
params.add(type);
}if(StringUtils.isNotBlank(createTime)){
sql.append(" and createTime=?");
params.add(createTime);
}if(StringUtils.isNotBlank(aname)){
sql.append(" and name=?");
params.add(aname);
}return jdbcTemplate.query(sql.toString(), params.toArray(), new RowMapper() {
@Overridepublic Account mapRow(ResultSet rs, int rowNum) throwsSQLException {
Account account=newAccount();
account.setId(rs.getInt("id"));
account.setUserId(rs.getInt("userId"));
account.setName(rs.getString("name"));
account.setCreate_time(rs.getDate("create_time"));
account.setMoney(rs.getBigDecimal("money"));
account.setType(rs.getString("type"));
account.setRemark(rs.getString("remark"));
account.setUpdate_time(rs.getDate("update_time"));returnaccount;
}
});
}
@OverridepublicInteger updateAccount(Account account) {
String sql="update account set name=?,type=?,money=? where id=?";returnjdbcTemplate.update(sql,account.getName(),account.getType(),account.getMoney(),account.getId());
}/*** 批量更新
*@paramaccounts
*@return
*/@Overridepublic Integer updateAccountsBatch(Listaccounts) {
String sql="update account set name=?,type=?,money=? where id=?";return jdbcTemplate.batchUpdate(sql, newBatchPreparedStatementSetter() {
@Overridepublic void setValues(PreparedStatement ps, int i) throwsSQLException {
ps.setString(1,accounts.get(i).getName());
ps.setString(2,accounts.get(i).getType());
ps.setBigDecimal(3,accounts.get(i).getMoney());
ps.setInt(4,accounts.get(i).getId());
}
@Overridepublic intgetBatchSize() {returnaccounts.size();
}
}).length;
}/*** 根据id删除记录
*@paramid
*@return
*/@OverridepublicInteger deleteAccountById(Integer id) {
String sql="delete from account where id=?";returnjdbcTemplate.update(sql,id);
}/*** 批量删除
*@paramids
*@return
*/@OverridepublicInteger deleteAccountsBatch(Integer[] ids) {
String sql="delete from account where id=?";return jdbcTemplate.batchUpdate(sql, newBatchPreparedStatementSetter() {
@Overridepublic void setValues(PreparedStatement ps, int i) throwsSQLException {
ps.setInt(1,ids[i]);
}
@Overridepublic intgetBatchSize() {returnids.length;
}
}).length;
}
@OverridepublicInteger inMoney(Integer sourceId, BigDecimal money) {
String sql="update account set money=money-? where id=?";returnjdbcTemplate.update(sql,money,sourceId);
}
@OverridepublicInteger outMoney(Integer targetId, BigDecimal money) {
String sql="update account set money=money+? where id=?";returnjdbcTemplate.update(sql,money,targetId);
}
}