Spring JDBCtemplateの使い方
15487 ワード
JAva beanオブジェクト実装インタフェースRowMapperクエリーの結果セットを処理するために使用
DAOレイヤの呼び出し
まだ終わっていないので、よく使うものに出会ったら更新し続けます.
public class Account implements RowMapper<Account> {
private String accountId;
private String bankId;
private String individualId;
private String MBankId;
private String EBankId;
private String identityNum;
private String accountCode;
private String password;
private String individualName;
private String accountType;
private String createTime;
private String accountStatus;
private Double balance;
private String creditRating;
private Double cashLimit;
private Double borrowCash=0.0;
private Double consumption=0.0;
private List<Account> list;
public Account() {
}
public Account(String accountId) {
this.accountId = accountId;
}
public Account(String accountId, String bankId, String individualId,
String MBankId, String EBankId, String identityNum,Double cashLimit,
String accountCode, String password, String individualName,
String accountType, String createTime, String accountStatus,
Double balance, String creditRating,Double borrowCash,Double consumption) {
this.accountId = accountId;
this.bankId = bankId;
this.individualId = individualId;
this.MBankId = MBankId;
this.EBankId = EBankId;
this.identityNum = identityNum;
this.accountCode = accountCode;
this.password = password;
this.individualName = individualName;
this.accountType = accountType;
this.createTime = createTime;
this.accountStatus = accountStatus;
this.balance = balance;
this.creditRating = creditRating;
this.cashLimit=cashLimit;
this.borrowCash=borrowCash;
this.consumption=consumption;
}
public Double getBorrowCash() {
return borrowCash;
}
public void setBorrowCash(Double borrowCash) {
this.borrowCash = borrowCash;
}
public Double getConsumption() {
return consumption;
}
public void setConsumption(Double consumption) {
this.consumption = consumption;
}
public String getAccountId() {
return this.accountId;
}
public void setAccountId(String accountId) {
this.accountId = accountId;
}
public String getBankId() {
return this.bankId;
}
public void setBankId(String bankId) {
this.bankId = bankId;
}
public String getIndividualId() {
return this.individualId;
}
public void setIndividualId(String individualId) {
this.individualId = individualId;
}
public String getMBankId() {
return this.MBankId;
}
public void setMBankId(String MBankId) {
this.MBankId = MBankId;
}
public String getEBankId() {
return this.EBankId;
}
public void setEBankId(String EBankId) {
this.EBankId = EBankId;
}
public String getIdentityNum() {
return this.identityNum;
}
public void setIdentityNum(String identityNum) {
this.identityNum = identityNum;
}
public String getAccountCode() {
return this.accountCode;
}
public void setAccountCode(String accountCode) {
this.accountCode = accountCode;
}
public String getPassword() {
return this.password;
}
public void setPassword(String password) {
this.password = password;
}
public String getIndividualName() {
return this.individualName;
}
public void setIndividualName(String individualName) {
this.individualName = individualName;
}
public String getAccountType() {
return this.accountType;
}
public void setAccountType(String accountType) {
this.accountType = accountType;
}
public String getCreateTime() {
return this.createTime;
}
public void setCreateTime(String createTime) {
this.createTime = createTime;
}
public String getAccountStatus() {
return this.accountStatus;
}
public void setAccountStatus(String accountStatus) {
this.accountStatus = accountStatus;
}
public String getCreditRating() {
return this.creditRating;
}
public void setCreditRating(String creditRating) {
this.creditRating = creditRating;
}
public Double getBalance() {
return balance;
}
public void setBalance(Double balance) {
this.balance = balance;
}
public List<Account> getList() {
return list;
}
public void setList(List<Account> list) {
this.list = list;
}
public Double getCashLimit() {
return cashLimit;
}
public void setCashLimit(Double cashLimit) {
this.cashLimit = cashLimit;
}
@Override
public Account mapRow(ResultSet rs, int arg1) throws SQLException {
Account account = new Account();
account.setAccountId(rs.getString("ACCOUNT_ID"));
account.setAccountCode(rs.getString("ACCOUNT_CODE"));
account.setBalance(rs.getDouble("BALANCE"));
account.setCashLimit(rs.getDouble("CASH_LIMIT"));
account.setIndividualId(rs.getString("INDIVIDUAL_ID"));
account.setBorrowCash(rs.getDouble("BORROW_CASH"));
account.setConsumption(rs.getDouble("CONSUMPTION"));
account.setAccountStatus(rs.getString("ACCOUNT_STATUS"));
account.setAccountType(rs.getString("ACCOUNT_TYPE"));
account.setIndividualName(rs.getString("INDIVIDUAL_NAME"));
account.setBankId(rs.getString("BANK_ID"));
account.setConsumption(rs.getDouble("CONSUMPTION"));
account.setCreateTime(rs.getTimestamp("CREATE_TIME").toString());
account.setCreditRating(rs.getString("CREDIT_RATING"));
account.setEBankId(rs.getString("E_BANK_ID"));
account.setIdentityNum(rs.getString("IDENTITY_NUM"));
account.setMBankId(rs.getString("M_BANK_ID"));
account.setPassword(rs.getString("PASSWORD"));
return account;
}
}DAOレイヤの呼び出し
public class AccountDao extends BaseDao {
/**
*
* @param id
* @return
*/
public Account queryAccountById(String id){
String sql ="select * from ACCOUNT where ACCOUNT_ID=?";
Object[] params = new Object[] {id};
List<Account> items = jdbcTemplate.query(sql,params,new Account());
if(items.isEmpty()){
return null;
}
return (Account)items.get(0);
}
/**
*
* @param accountId
* @param consumption
*/
public void updataAccount(String accountId,double consumption) {
Object[] params =new Object[] {consumption,accountId};
jdbcTemplate.update("update ACCOUNT set CONSUMPTION = ? where ACCOUNT_ID = ?", params);
}
/**
*
* @param id
* @return
*/
public List<Transaction> queryTransaction(String id){
String sql ="select * from TRANSACT where Account_Id=? AND TRANS_STATE='ing' FETCH FIRST 5 ROWS ONLY";
Object[] params = new Object[] {id};
List<Transaction> items = jdbcTemplate.query(sql,params,new Transaction());
return items;
}
/**
*
* @param transactionIds
* @return
*/
public double countTransactionByIds(List<Object> transactionIds) {
final List<Double> count =new ArrayList<Double>();
String sql ="select sum(DEPOSIT) as totle from TRANSACT where TRANS_ID in (:transactionIds)";
NamedParameterJdbcTemplate namedParameterJdbcTemplate = null;
namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("transactionIds",transactionIds);
namedParameterJdbcTemplate.query(sql, paramMap, new RowCallbackHandler(){
@Override
public void processRow(ResultSet rs) throws SQLException {
count.add(0, rs.getDouble("totle"));
}
});
return count.get(0);
}
/**
*
* @param transactionIdList
*/
public void updateTransact(List<Object> transactionIdList) {
String sql ="update TRANSACT set TRANS_STATE = 'save' where TRANS_ID in (:transactionIds)";
NamedParameterJdbcTemplate namedParameterJdbcTemplate = null;
namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("transactionIds",transactionIdList);
namedParameterJdbcTemplate.update(sql, paramMap);
}
/**
*
* @param accountId
* @return
*/
public double accountBill(String accountId){
Repayment repayment = new Repayment();
String sql ="select date(rtrim(char(year(min(start_time))))||'-'||rtrim(char(month(min(start_time))))||'-1') as startTime,date(rtrim(char(year(max(start_time))))||'-'||rtrim(char(month(max(start_time)+1 month)))||'-1') as endTime from REPAYMENT";
List<Repayment> items =jdbcTemplate.query(sql,new RowMapper<Repayment>() {
@Override
public Repayment mapRow(ResultSet rs, int i)
throws SQLException {
Repayment r = new Repayment();
r.setStartTime(rs.getDate("startTime"));
r.setEndTime(rs.getDate("endTime"));
return r;
}
});
if(items.size()>0){
repayment=(Repayment)items.get(0);
}
String sql2 = "select sum(MONTHLY_REPAYMENTS) as totle from REPAYMENT where ACCOUNT_ID=? AND date('"
+repayment.getStartTime()+"') < date(current timestamp ) and date('"+repayment.getEndTime()
+"') > date(current timestamp)";
Object[] params = new Object[] {accountId};
Double count = jdbcTemplate.queryForObject(sql2, params,Double.class);
if(count==null){
count=0.0;
}
return count;
}
/**
*
*
*
*/
public void save(final Individuals individuals) {
String sql = "insert into INDIVIDUAL(INDIVIDUAL_ID,FAMILY_ID,INDIVIDUAL_NAME,englishname,MARITAL_STATUS,nationality,folk" +
",BIRTH_DAY,EDUCATION_LEVEL,address,email,sex,telephone,IDENTITY_NUM,CUSTOMER_TYPE,EMPLOYER_NAME" +
",EMPLOYER_ADDRESS,CERTIFICATE_TYPE,CERTIFICATE_NUMBER,POSTAL_CODE,LOCATION_CITY,OFFICE_TELEPHONE,OFFICE_FAXNUMBER" +
",WORK_YEAR,CREDIT_RATING,IMAGE) " +
"values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i)
throws SQLException {
ps.setString(1, IdUtils.uuid());
ps.setString(2, individuals.getFamilyId());
ps.setString(3, individuals.getIndividualName()[i]);
ps.setString(4, individuals.getEnglishname()[i]);
ps.setString(5, individuals.getMaritalStatus()[i]);
ps.setString(6, individuals.getNationality()[i]);
ps.setString(7, individuals.getFolk()[i]);
ps.setTimestamp(8, DateUtils.getTimestamp(individuals.getBirthDay()[i]));
ps.setString(9, individuals.getEducationLevel()[i]);
ps.setString(10, individuals.getAddress()[i]);
ps.setString(11, individuals.getEmail()[i]);
ps.setString(12, individuals.getSex()[i]);
ps.setString(13, individuals.getTelephone()[i]);
ps.setString(14, individuals.getIdentityNum()[i]);
ps.setString(15, individuals.getCustomerType()[i]);
ps.setString(16, individuals.getEmployerName()[i]);
ps.setString(17, individuals.getEmployerAddress()[i]);
ps.setString(18, individuals.getCertificateType()[i]);
ps.setString(19, individuals.getCertificateNumber()[i]);
ps.setString(20, individuals.getPostalCode()[i]);
ps.setString(21, individuals.getLocationCity()[i]);
ps.setString(22, individuals.getOfficeTelephone()[i]);
ps.setString(23, individuals.getOfficeFaxnumber()[i]);
ps.setInt(24, individuals.getWorkYear()[i]);
ps.setString(25, individuals.getCreditRating()[i]);
ps.setString(26, individuals.getImage()[i]);
}
public int getBatchSize() {
return individuals.getIndividualName().length;
}
});
}
}まだ終わっていないので、よく使うものに出会ったら更新し続けます.