JDBC 女神走过来 3-3

Model Account.java

package com.imooc.model;

import java.util.Date;

public class Account {

    private Integer id;
    private String account;
    private Double amount;
    private Date createAt;

    public Date getCreateAt() {
        return createAt;
    }

    public void setCreateAt(Date createAt) {
        this.createAt = createAt;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getAccount() {
        return account;
    }

    public void setAccount(String account) {
        this.account = account;
    }

    public Double getAmount() {
        return amount;
    }

    public void setAmount(Double amount) {
        this.amount = amount;
    }

}

Model TransInfos.java

package com.imooc.model;

import java.util.Date;

public class TransInfos {

    private Integer id;
    private Integer sourceId;
    private String sourceAccount;
    private Integer destinationId;
    private String destinationAccount;
    private Double amount;
    private Date createAt;

    public String getDestinationAccount() {
        return destinationAccount;
    }

    public void setDestinationAccount(String destinationAccount) {
        this.destinationAccount = destinationAccount;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getSourceId() {
        return sourceId;
    }

    public void setSourceId(Integer sourceId) {
        this.sourceId = sourceId;
    }

    public String getSourceAccount() {
        return sourceAccount;
    }

    public void setSourceAccount(String sourceAccount) {
        this.sourceAccount = sourceAccount;
    }

    public Integer getDestinationId() {
        return destinationId;
    }

    public void setDestinationId(Integer destinationId) {
        this.destinationId = destinationId;
    }

    public Double getAmount() {
        return amount;
    }

    public void setAmount(Double amount) {
        this.amount = amount;
    }

    public Date getCreateAt() {
        return createAt;
    }

    public void setCreateAt(Date createAt) {
        this.createAt = createAt;
    }

}

DAO AccountDao.java

package com.imooc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.imooc.db.DBUtil;
import com.imooc.model.Account;

public class AccountDao {

    public void addAccount(Account a) throws Exception{
        Connection conn = DBUtil.getConnection();
        String sql = ""+
                    "insert into account_info "+
                    " (account,amount,create_at) "+
                    " values("+
                    "?,?,current_date()) ";
        PreparedStatement ptmt = conn.prepareStatement(sql);

        ptmt.setString(1, a.getAccount());
        ptmt.setDouble(2, a.getAmount());

        ptmt.execute();
    }

    public void updateAccount(Account a) throws Exception{
        Connection conn = DBUtil.getConnection();
        String sql = ""+
                    " update account_info "+
                    " set account = ?,amount = ?"+
                    " createAt = current_date() "+
                    " where id = ? ";
        PreparedStatement ptmt = conn.prepareStatement(sql);

        ptmt.setString(1, a.getAccount());
        ptmt.setDouble(2, a.getAmount());
        ptmt.setInt(3, a.getId());

        ptmt.execute();
    }

    public void delAccount(Integer id) throws Exception{
        Connection conn = DBUtil.getConnection();
        String sql = ""+
                    " delete from account_info "+
                    " where id = ? ";
        PreparedStatement ptmt = conn.prepareStatement(sql);

        ptmt.setInt(1, id);
        ptmt.execute();
    }

    public List<Account> queryAccount(List<Map<String, Object>> params) throws Exception{
        Connection conn = DBUtil.getConnection();

        StringBuilder sb = new StringBuilder();
        sb.append("select * from account_info where 1=1");

        if(params!=null&&params.size()>0){
            for(int i = 0;i < params.size();i++){
                Map<String, Object> map = params.get(i);
                sb.append(" and "+ map.get("name") + " " + map.get("rela")+ " " + map.get("value") + " " );
            }
        }
        PreparedStatement ptmt = conn.prepareStatement(sb.toString());

        ResultSet rs = ptmt.executeQuery();

        List<Account> result = new ArrayList<Account>();
        Account a = null;

        while(rs.next()){
            a = new Account();
            a.setId(rs.getInt("id"));
            a.setAccount(rs.getString("account"));
            a.setAmount(rs.getDouble("amount"));
            a.setCreateAt(rs.getDate("create_at"));

            result.add(a);
        }
        return result;
    }
}

DAO TransInfosDao.java

package com.imooc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.imooc.db.DBUtil;
import com.imooc.model.TransInfos;

public class TransInfoDao {

    public void addTransInfos(TransInfos t) throws Exception{
        Connection conn = DBUtil.getConnection();
        String sql = ""+
                    "insert into trans_info "+
                    " (source_id,source_account,destination_id,destination_account,amount,create_at) "+
                    " values("+
                    "?,?,?,?,?,current_date()) ";
        PreparedStatement ptmt = conn.prepareStatement(sql);

        ptmt.setInt(1, t.getSourceId());
        ptmt.setString(2, t.getSourceAccount());
        ptmt.setInt(3, t.getDestinationId());
        ptmt.setString(4, t.getSourceAccount());
        ptmt.setDouble(5, t.getAmount());

        ptmt.execute();
    }

    public void updateTransInfos(TransInfos t) throws Exception{
        Connection conn = DBUtil.getConnection();
        String sql = ""+
                    " update trans_info "+
                    " set source_id = ?,source_account = ?,destination_id = ?,destination_account = ?,"+
                    " amount = ?,createAt = current_date() "+
                    " where id = ? ";
        PreparedStatement ptmt = conn.prepareStatement(sql);

        ptmt.setInt(1, t.getSourceId());
        ptmt.setString(2, t.getSourceAccount());
        ptmt.setInt(3, t.getDestinationId());
        ptmt.setString(4, t.getSourceAccount());
        ptmt.setDouble(5, t.getAmount());
        ptmt.setInt(6, t.getId());

        ptmt.execute();
    }

    public void delTransInfos(Integer id) throws Exception{
        Connection conn = DBUtil.getConnection();
        String sql = ""+
                    " delete from trans_info "+
                    " where id = ? ";
        PreparedStatement ptmt = conn.prepareStatement(sql);

        ptmt.setInt(1, id);
        ptmt.execute();
    }

    public List<TransInfos> queryTransInfos(List<Map<String, Object>> params) throws Exception{
        Connection conn = DBUtil.getConnection();

        StringBuilder sb = new StringBuilder();
        sb.append("select * from trans_info where 1=1");

        if(params!=null&&params.size()>0){
            for(int i = 0;i < params.size();i++){
                Map<String, Object> map = params.get(i);
                sb.append(" and "+ map.get("name") + " " + map.get("rela")+ " " + map.get("value") + " " );
            }
        }
        PreparedStatement ptmt = conn.prepareStatement(sb.toString());

        ResultSet rs = ptmt.executeQuery();

        List<TransInfos> result = new ArrayList<TransInfos>();
        TransInfos t = null;

        while(rs.next()){
            t = new TransInfos();
            t.setId(rs.getInt("id"));
            t.setSourceId(rs.getInt("source_id"));
            t.setSourceAccount(rs.getString("source_account"));
            t.setDestinationId(rs.getInt("destination_id"));
            t.setDestinationAccount(rs.getString("destination_account"));
            t.setAmount(rs.getDouble("amount"));
            t.setCreateAt(rs.getDate("create_at"));

            result.add(t);
        }
        return result;
    }
}