4.01

练习

database.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/account?useUnicode=true&characterEncoding=utf8
username=root
password=1234
#初始化连接
initialSize=5
#最大连接数量
maxActive=30
#最小空闲连接
minIdle=2
#超时等待时间以毫秒为单位
maxWait=5000

DBPoolUtile

package com.qf.day4_1.p1;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DBPoolUtile {
    private static final Properties PROPERTIES = new Properties();
    private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<Connection>();
    private static DruidDataSource druidDataSource;

    static {
        InputStream inputStream = DBPoolUtile.class.getResourceAsStream("/database.properties");
        try {
            PROPERTIES.load(inputStream);
            druidDataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(PROPERTIES);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection connection = THREAD_LOCAL.get();
        try {
            if (connection == null) {
                connection = druidDataSource.getConnection();
                THREAD_LOCAL.set(connection);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
                THREAD_LOCAL.remove();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

T_Account

package com.qf.day4_1.p1;

public class T_Account {
    private String cardId;
    private String password;
    private String username;
    private double balance;
    private String phone;

    @Override
    public String toString() {
        return "T_Account{" +
                "cardId='" + cardId + '\'' +
                ", password='" + password + '\'' +
                ", username='" + username + '\'' +
                ", balance=" + balance +
                ", phone='" + phone + '\'' +
                '}';
    }

    public String getCardId() {
        return cardId;
    }

    public void setCardId(String cardId) {
        this.cardId = cardId;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public double getBalance() {
        return balance;
    }

    public void setBalance(double balance) {
        this.balance = balance;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public T_Account() {
    }

    public T_Account(String cardId, String password, String username, double balance, String phone) {
        this.cardId = cardId;
        this.password = password;
        this.username = username;
        this.balance = balance;
        this.phone = phone;
    }
}

T_AccountDAOImpl

package com.qf.day4_1.p1;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class T_AccountDAOImpl {
    private Connection connection = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;

    public int upDate(T_Account account) {
        connection = DBPoolUtile.getConnection();
        String sql = "update t_account set password = ?,userName = ?,balance = ?,phone = ? where cardId = ?;";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, account.getPassword());
            preparedStatement.setString(2, account.getUsername());
            preparedStatement.setDouble(3, account.getBalance());
            preparedStatement.setString(4, account.getPhone());
            preparedStatement.setString(5, account.getCardId());
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBPoolUtile.closeAll(null, preparedStatement, resultSet);
        }
        return 0;
    }

    public T_Account select(String cardId) {
        connection = DBPoolUtile.getConnection();
        String sql = "SELECT cardid,PASSWORD,username,balance,phone FROM t_account where cardId = ? ";
        T_Account account = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, cardId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                account = new T_Account(resultSet.getString(1), resultSet.getString(2), resultSet.getString(3), resultSet.getDouble(4), resultSet.getString(5));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBPoolUtile.closeAll(null, preparedStatement, resultSet);
        }
        return account;
    }
}

T_AccountServiceImpl

package com.qf.day4_1.p1;

import java.sql.Connection;
import java.sql.SQLException;

public class T_AccountServiceImpl {
    public String transfer(String fromId, String password, String toId, Double money) {
        String result = "转账失败";
        T_AccountDAOImpl t_accountDAO = new T_AccountDAOImpl();
        Connection connection = null;
        try {
            connection = DBPoolUtile.getConnection();
            connection.setAutoCommit(false);
            T_Account fromAcc = t_accountDAO.select(fromId);
            T_Account toAcc = t_accountDAO.select(toId);
            if (fromAcc == null) {
                throw new RuntimeException("账户不存在");
            }
            if (!fromAcc.getPassword().equals(password)) {
                throw new RuntimeException("密码错误");
            }
            if (toAcc == null) {
                throw new RuntimeException("对方账户不存在");
            }
            if (fromAcc.getBalance() < money) {
                throw new RuntimeException("余额不足");
            }
            fromAcc.setBalance(fromAcc.getBalance() - money);
            toAcc.setBalance(toAcc.getBalance() + money);
            t_accountDAO.upDate(fromAcc);
            t_accountDAO.upDate(toAcc);
            result = "转账成功";
            connection.commit();

        } catch (Exception e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            DBPoolUtile.closeAll(connection, null, null);
        }
        return result;
    }
}