【大数据开发】JDBC编程——使用druid、dbutils工具类、MVC设计模式简单对银行账号转账业务进行优化day28

使用的数据库是前一天的，前面有脚本。这里只是实现了控制层的分层和模型层的分层，视图层简单模拟。
知识准备
QueryRunner的使用
ResultSetHandler接口与实现

一、项目结构

二、封装工具类

（1）util\DBUtilDruid.java

package com.qf.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
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 DBUtilDruid {
    private static DataSource dataSource;

    static {

        try {                         //Class  利用字节码获取字节读取流对象
            InputStream inputstream = DBUtilDruid.class.getClassLoader().getResourceAsStream("druid.properties");

            Properties properties=new Properties();

            properties.load(inputstream);//InputStream

            dataSource = DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        Connection connection = dataSource.getConnection();
        return connection;
    }

    public static void close(ResultSet resultSet, Statement statement, Connection connection) throws SQLException {
        if(resultSet!=null)
            resultSet.close();
        if(statement!=null)
            statement.close();
        if(connection!=null)
            connection.close();
    }
}

三、account对象

（1）pojo\account.java

这里写的是JavaBeen

package com.qf.pojo;

import java.sql.Date;
//账户类
public class Account {

    //属性名和字段名必须相同
    private  int id;
    private String account_id;
    private double account_balance;
    private String user_name;
    private String user_pwd;
    private String user_idcard;
    private Date oper_time;
    private String gender;

    public Account() {
    }

    public Account(int id, String account_id, double account_balance, String user_name, String user_pwd, String user_idcard, Date oper_time, String gender) {
        this.id = id;
        this.account_id = account_id;
        this.account_balance = account_balance;
        this.user_name = user_name;
        this.user_pwd = user_pwd;
        this.user_idcard = user_idcard;
        this.oper_time = oper_time;
        this.gender = gender;
    }

    public int getId() {
        return id;
    }

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

    public String getAccount_id() {
        return account_id;
    }

    public void setAccount_id(String account_id) {
        this.account_id = account_id;
    }

    public double getAccount_balance() {
        return account_balance;
    }

    public void setAccount_balance(double account_balance) {
        this.account_balance = account_balance;
    }

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public String getUser_pwd() {
        return user_pwd;
    }

    public void setUser_pwd(String user_pwd) {
        this.user_pwd = user_pwd;
    }

    public String getUser_idcard() {
        return user_idcard;
    }

    public void setUser_idcard(String user_idcard) {
        this.user_idcard = user_idcard;
    }

    public Date getOper_time() {
        return oper_time;
    }

    public void setOper_time(Date oper_time) {
        this.oper_time = oper_time;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", account_id='" + account_id + '\'' +
                ", account_balance=" + account_balance +
                ", user_name='" + user_name + '\'' +
                ", user_pwd='" + user_pwd + '\'' +
                ", user_idcard='" + user_idcard + '\'' +
                ", oper_time=" + oper_time +
                ", gender='" + gender + '\'' +
                '}';
    }
}

四、对数据库的操作

（1）dao\AccountDao.java

写根据账号查询用户和修改账户余额两个方法接口

package com.qf.dao;

import com.qf.pojo.Account;

import java.sql.SQLException;

public interface AccountDao {

    //根据账号查询用户
    Account findByAccount(String accountid)throws SQLException;

    //修改账户余额
    void updateAccount(Account account)throws SQLException;
}

（2）dao\impl\AccountDaoImpl.java

实现上面的两个接口

package com.qf.dao.impl;

import com.qf.dao.AccountDao;
import com.qf.pojo.Account;
import com.qf.util.DBUtilDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;

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

public class AccountDaoImpl implements AccountDao {

    private Connection connection;
    public AccountDaoImpl() {
    }

    public AccountDaoImpl(Connection connection) {
        this.connection = connection;
    }

    //根据账号查询用户
    @Override
    public Account findByAccount(String accountid) throws SQLException {
        QueryRunner queryRunner=new QueryRunner();
        String sql="select * from bank_account where account_id=?";
        Account account = queryRunner.query(connection, sql, new BeanHandler<Account>(Account.class), accountid);
        return account;
    }


    //修改账户余额
    @Override
    public void updateAccount(Account account) throws SQLException {
        QueryRunner queryRunner=new QueryRunner();
        String sql="update bank_account set account_balance=? where account_id=?";
        queryRunner.update(connection,sql,account.getAccount_balance(),account.getAccount_id());
    }
}

五、业务层

（1）service\AccountService.java

这里写转账业务

package com.qf.service;

import java.sql.SQLException;

public interface AccountService {

    //转账
    void transfer(String from,String to,double money)throws SQLException;
}

（2）service\AccountServiceImp.java

实现上面的接口

package com.qf.service.impl;

import com.qf.dao.AccountDao;
import com.qf.dao.impl.AccountDaoImpl;
import com.qf.pojo.Account;
import com.qf.service.AccountService;
import com.qf.util.DBUtilDruid;

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

public class AccountServiceImpl implements AccountService {

     AccountDao accountDao;//=new AccountDaoImpl();

    @Override
    public void transfer(String from, String to, double money)  {

        Connection conn= null;
        try {
        conn = DBUtilDruid.getConnection();

        accountDao=new AccountDaoImpl(conn);

         //根据账号查询用户信息
        Account out = accountDao.findByAccount(from);
        Account in = accountDao.findByAccount(to);

        //更新余额
        out.setAccount_balance(out.getAccount_balance()-money);
        in.setAccount_balance(in.getAccount_balance()+money);
        //修改账户余额
        //每条sql语句执行完自动提交，凡是提交了的就是真正修改了数据库中数据，是不能撤销的，每条sql语句的执行都在一个单独的事务中
        //手动开启事务  事务是在数据库连接中的，
        conn.setAutoCommit(false);//把自动提交设置为false

        accountDao.updateAccount(out);// update
        int a=5/0;
        accountDao.updateAccount(in);//update 默认：每条sql语句执行完自动提交

        //提交事务，在同一个事务中的操作可以实现成功都成功，失败都失败
        conn.commit();

        } catch (SQLException e) {
            try {
                conn.rollback();//回滚
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}

六、测试类

test\tests.java

package com.qf.test;

import com.qf.service.AccountService;
import com.qf.service.impl.AccountServiceImpl;

import java.sql.SQLException;
public class Tests {

    public static void main(String[] args) throws SQLException {

        AccountService accountService=new AccountServiceImpl();

        accountService.transfer("6225113088436225","6225113088436227",500);

    }
}