基于JavaWeb实现ATM机模拟取款系统实战(使用Druid数据源和DBUtils简化操作)

 一：相应jar包介绍

1.1 druid 数据源

Druid连接池是阿里巴巴开源的数据库连接池项目，后来贡献给Apache开源； Druid的作用是负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个； Druid连接池内置强大的监控功能，其中的StatFilter功能，能采集非常完备的连接池执行信息，方便进行监控，而监控特性不影响性能。 Druid连接池内置了一个监控页面，提供了非常完备的监控信息，可以快速诊断系统的瓶颈。

 1.2 dbutils 数据库工具类

DBUtils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用DBUtils能极大简化JDBC编码的工作量，同时也不会影响程序的性能。

 1.3 如何使用以及示例代码

新建druid.properties 编写好自己的连接信息

# 连接地址

url=jdbc:mysql://localhost:3306/atm?useSSL=false

#密码

password=root

#用户名

username=root

创建连接池工具类DruidUtils

/**
 * Druid DataSource
 */
public class DruidUtils {
    private static DataSource ds;
    static {
        InputStream in = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
        Properties properties = new Properties();
        try {
            properties.load(in);
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection(){
        try{
            return ds.getConnection();
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
    public static DataSource getDataSource(){
        return ds;
    }
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

如何使用dbutil

  QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

1 查询操作

public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {

Connection conn = this.prepareConnection();return this.query(conn, true, sql, rsh, params);}

 查询的三种方式 

queryRunner.query("",new BeanHandler<>(User.class)); 返回对象
queryRunner.query("",new BeanListHandler<>(User.class)); 返回集合
queryRunner.query("", new ScalarHandler<>());返回数字

   public static void test01(){
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            User query = queryRunner.query("select * from user where icno = ?", new BeanHandler<>(User.class), "6225123456788");
            System.out.println("query = " + query);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

 

    public static void test02(){
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            List<User> userList = queryRunner.query("select * from user", new BeanListHandler<>(User.class));
            System.out.println("userList = " + userList);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

 

  public static void test03(){
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        try {
            Object query = queryRunner.query("select count(*) from user", new ScalarHandler<>());
            System.out.println("query = " + query);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

 二：项目中使用

2.1原始代码查看

    /**
     * 管理员登录
     * @param name 名字
     * @param password 密码
     * @return 管理员
     */
    public Manager findManager(String name,String password) {
        Connection connection = DBUtils.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Manager manager=null;
        try {
            preparedStatement = connection.prepareStatement("select * from manage where name = ? and password =?");
            preparedStatement.setString(1,name);
            preparedStatement.setString(2,password);
             resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                manager= new Manager();
                manager.setId(resultSet.getInt(1));
                manager.setName(resultSet.getString(2));
                manager.setPassword(resultSet.getString(3));
            }
            return manager;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtils.release(connection,preparedStatement,resultSet);
        }
    }

 优化后的代码

    /**
     * 管理员登录
     * @param name 名字
     * @param password 密码
     * @return 管理员
     */
    public Manager findManager(String name,String password) {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from manage where name = ? and password =?";
        try {
            return queryRunner.query(sql,new BeanHandler<>(Manager.class),name,password);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

代码变得非常简洁了

同理我们对dao的其他类进行操作

public class RunMessageDao {
    QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
    /**
     * 添加账单信息
     * @param runMessage 账单信息
     */
    public void addRunMessage(RunMessage runMessage) {
        String sql ="insert into runmessage values(null,?,?,?,?,?)";
        try {
            queryRunner.update(sql,runMessage.getIcno(),
                    runMessage.getRm_date(),
                    runMessage.getRm_currency(),
                    runMessage.getRm_balance()
                    ,runMessage.getRm_rest());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    /**
     * 账户记录
     * @param icno 卡号
     * @return 账户记录
     */
    public List<RunMessage> findAllDetails(String icno) {
        String  sql ="select * from runmessage where icno = ? ";
        try {
            List<RunMessage> runMessageList = queryRunner.query(sql, new BeanListHandler<>(RunMessage.class), icno);
            return runMessageList;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 查询分页数据
     * @param page 页码
     * @param pageSize 页数大小
     * @return
     */
    public List<RunMessage> findRunMessageByPage(String icno,int page, int pageSize) {
        int startIndex=(page-1)*(pageSize);
        String sql = "select * from runmessage where icno = ? limit ?,? ";
        try {
            List<RunMessage> runMessageList = queryRunner.query(sql, new BeanListHandler<>(RunMessage.class), icno, startIndex, pageSize);
            return runMessageList;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

package com.atm.dao;

import com.atm.pojo.User;
import com.atm.utill.DBUtils;
import com.atm.utill.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

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

public class UserDao {
    private QueryRunner queryRunner=new QueryRunner(DruidUtils.getDataSource());
    /**
     * 查找所有用户
     * @return 所有用户信息
     */
    public List<User> findAllUsers() {
        try {
            return queryRunner.query("select * from user", new BeanListHandler<>(User.class));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 模糊查询 用户信息
     * @param name 模糊查询名子
     * @return 模糊查询结果
     */
    public List<User> queryUsers(String name) {
        String  sql ="select * from user where name like ?";
        try {
            return queryRunner.query(sql, new BeanListHandler<>(User.class), "%" + name + "%");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 通过icno 查询
     * @param icno 卡号
     * @return 用户信息
     */
    public User getUserByIcno(String icno) {
        String  sql ="select * from user where icno = ?";
        try {
            return queryRunner.query(sql, new BeanHandler<>(User.class), icno);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 更新用户信息
     * @param user 用户信息
     * @return result
     */
    public int updateUser(User user) {
        String  sql ="update user set name = ?, pwd = ? ,mobile = ?, idcard = ?, balance = ? where icno = ?";
        try {
            return queryRunner.update(sql, user.getName(), user.getPwd(), user.getMobile(), user.getIdcard(), user.getBalance(), user.getIcno());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 根据icno删除用户
     * @param icno 卡号
     * @return reasult
     */
    public int deleteUser(String icno) {
        String sql ="delete from user where icno =?";
        try {
            return queryRunner.update(sql, icno);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 添加用户信息
     * @param user 用户信息
     * @return result
     */
    public int addUser(User user) {
        String  sql ="insert into  user values(?,?,?,?,?,?)";
        try {
            return queryRunner.update(sql,
                    user.getIcno(),
                    user.getName(),
                    user.getPwd(),
                    user.getMobile(),
                    user.getIdcard(),
                    user.getBalance());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 根据卡号和密码查询用户信息
     * @param icno 卡号
     * @param pwd 密码
     * @return 用户信息
     */
    public User findUserByNameAndPwd(String icno, String pwd) {
   String sql ="select * from user where icno = ? and pwd = ?";
        try {
            return queryRunner.query(sql, new BeanHandler<>(User.class), icno, pwd);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 更新用户信息
     * @param icno 卡号
     * @param newpwd 新密码
     * @return result
     */
    public int  updateUserPwd(String icno,String newpwd) {
        String sql ="update user set pwd = ? where icno=?";
        try {
            return queryRunner.update(sql, icno, newpwd);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 根据卡号获取资金
     * @param icno 卡号
     * @return 资金
     */
    public double getUserBalance(String icno) {
        String sql ="select balance from user where icno=?";
        try {
            Double query = queryRunner.query(sql, new ScalarHandler<>(), icno);
            return  query;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 存钱
     * @param money 金额
     * @param icno 卡号
     * @return result
     */
    public int  saveMoney(int money,String icno) {
        String sql ="update user set balance = balance + ? where icno = ?";
        try {
           return queryRunner.update(sql,money,icno);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 取钱
     * @param money 金额
     * @param icno 卡号
     * @return result
     */
    public int fetchMoney(int money, String icno) {
        String sql ="update user set balance = balance - ? where icno = ?";
        try {
           return queryRunner.update(sql,  money, icno);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}

优化后的代码明显简洁美观，增强了代码的可读性

三：页面效果

 

 

 

 基本效果都实现了