Spring JDBCTemplate用法

java bean对象 实现接口RowMapper用来处理查询出来的结果集

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;
            }
        });

    }

}

 

 

未完，碰到常用的还会继续更新的。