BankSystem系统-分层架构连接操作数据库DML语言基础操作篇（二）

一、建包

二、在entity包下创建与数据库中表对应的实体类

Administrator表：

import java.sql.Date;

public class Administrator {

    /**
     * 管理员编号
     */
    private String adminNumber;
    /**
     * 管理员密码
     */
    private String adminPwd;
    /**
     * 管理员名字
     */
    private String adminName;

    public Administrator() {
        //System.out.println("我是无参构造函数！！！");
    }

    public Administrator(String adminNumber, String adminPwd, String adminName) {
        this.adminNumber = adminNumber;
        this.adminPwd = adminPwd;
        this.adminName = adminName;
        //System.out.println("我是带参构造函数！！！");
    }

    public String getAdminNumber() {
        return adminNumber;
    }

    public void setAdminNumber(String adminNumber) {
        this.adminNumber = adminNumber;
    }

    public String getAdminPwd() {
        return adminPwd;
    }

    public void setAdminPwd(String adminPwd) {
        this.adminPwd = adminPwd;
    }

    public String getAdminName() {
        return adminName;
    }

    public void setAdminName(String adminName) {
        this.adminName = adminName;
    }
  
    @Override
    public String toString() {
        return "Administrator{" +
                "adminNumber='" + adminNumber + '\'' +
                ", adminPwd='" + adminPwd + '\'' +
                ", adminName='" + adminName + '\'' +
                '}';
    }
}

数据库中Administrator表：

Customer表：

import java.util.Date;

public class Customer {
    /**
     * 银行账号
     */
    private String custNumber;
    /**
     * 开户姓名
     */
    private String   custName;
    /**
     * 账户密码
     */
    private String  custPwd;
    /**
     * 身份证号码
     */
    private String  custIdCard;
    /**
     * 开户金额
     */
    private double  custMoney;
    /**
     * 开户日期
     */
    private Date custDate;

    public Customer() {
    }

    public Customer(String custNumber, String custName, String custPwd, String custIdCard, double custMoney, Date custDate) {
        this.custNumber = custNumber;
        this.custName = custName;
        this.custPwd = custPwd;
        this.custIdCard = custIdCard;
        this.custMoney = custMoney;
        this.custDate = custDate;
    }


    public String getCustNumber() {
        return custNumber;
    }

    public void setCustNumber(String custNumber) {
        this.custNumber = custNumber;
    }

    public String getCustName() {
        return custName;
    }

    public void setCustName(String custName) {
        this.custName = custName;
    }

    public String getCustPwd() {
        return custPwd;
    }

    public void setCustPwd(String custPwd) {
        this.custPwd = custPwd;
    }

    public String getCustIdCard() {
        return custIdCard;
    }

    public void setCustIdCard(String custIdCard) {
        this.custIdCard = custIdCard;
    }

    public double getCustMoney() {
        return custMoney;
    }

    public void setCustMoney(double custMoney) {
        this.custMoney = custMoney;
    }

    public Date getCustDate() {
        return custDate;
    }

    public void setCustDate(Date custDate) {
        this.custDate = custDate;
    }


    @Override
    public String toString() {
        return "Customer{" +
                "custNumber='" + custNumber + '\'' +
                ", custName='" + custName + '\'' +
                ", custPwd='" + custPwd + '\'' +
                ", custIdCard='" + custIdCard + '\'' +
                ", custMoney=" + custMoney +
                ", custDate=" + custDate +
                '}';
    }
}

数据库中Customer表：

三、创建BaseDao类，使用jdbc实现数据库连接和关闭功能

BaseDao类：

package com.wbs.dao;

import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BaseDao<T> {

    private final String DRIVER = "com.mysql.jdbc.Driver";
    //MySQL5
    private final String URL = "jdbc:mysql://localhost:3306/bank?userUnicode=true&amp;characterEncoding=utf-8";
    //MySQL8（此处看你的MySQL版本，是5就用上面的，是8就用下面这个）
    //private final String URL="jdbc:mysql://localhost/bank?useSSL=FALSE&serverTimezone=UTC";

    private String USERNAME = "root";//此处是你自己的数据库账户名
    private String PASSWORD = "root";//此处是你自己的数据库账户密码
    private Connection conn = null;

    //连接数据库
    public Connection getConnection() throws Exception{
        try{
            //加载驱动
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
            return conn;
        }catch (Exception ex){
            throw new Exception(ex);
        }
    }
  
   /**
     * 关闭数据库连接资源
     * @param rs
     * @param ps
     * @param conn
     * @throws Exception
     */
    protected void close(ResultSet rs, PreparedStatement ps, Connection conn) throws Exception {
        try{
            if(rs != null){
                rs.close();
            }
            if(ps != null){
                ps.close();
            }
            if(conn != null){
                conn.close();
            }
        }catch (Exception ex){
            throw new Exception(ex);
        }

    }
}

四、创建DAO层管理员接口AdminDao

4.1在接口中定义管理员登陆、添加顾客(开户)、计算储蓄总额、富豪排行榜等方法

AdminDao接口:

package com.wbs.dao;

import com.wbs.entity.Administrator;
import com.wbs.entity.Customer;
import java.util.List;

public interface AdminDao {

    /**
     * 管理员登录
     * @param name
     * @param pwd
     */
    public List<Administrator>  login(String name, String pwd);

    /**
     * 添加顾客
     * @param cust
     * @return
     */
    public int addCust(Customer cust) throws Exception;

    /**
     * 计算储蓄总额
     *
     * @return
     */
    public double cxze();

    /**
     * 富豪排行榜
     *
     * @return
     */
    public List<Customer> phb();
}

4.2、创建DAO层实现类AdminDaoImpl，继承BaseDao,实现AdminDao接口，使用jdbc完成相应的数据库操作

AdminDaoImpl类：

package com.wbs.dao.impl;

import com.wbs.dao.AdminDao;
import com.wbs.dao.BaseDao;
import com.wbs.entity.Administrator;
import com.wbs.entity.Customer;
import java.lang.reflect.Constructor;
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 AdminDaoImpl extends BaseDao implements AdminDao {

    private static Connection conn = null;
    private static PreparedStatement ps = null;
    private static ResultSet rs = null;

    /**
     * 管理员登录
     * @param name
     * @param pwd
     * @return
     */
    @Override
    public List<Administrator> login(String name, String pwd) throws Exception {
        List<Administrator> administratorList = new ArrayList<>();
        try {
            //获取连接
            conn = super.getConnection();
            String sql = "select * from administrator where adminName = ? and adminPwd = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,name);
            ps.setObject(2,pwd);
            rs = ps.executeQuery();
            while (rs.next()){
                Administrator administrator = new Administrator();
                administrator.setAdminNumber(rs.getString("adminNumber"));
                administrator.setAdminName(rs.getString("adminName"));
                administrator.setAdminPwd(rs.getString("adminPwd"));
                administratorList.add(administrator);
            }
            super.close(rs,ps,conn);
        }catch (Exception ex){
            throw ex;
        }
        return administratorList;
    }


    /**
     * 添加用户（增）
     * @param cust
     * @return
     */
    @Override
    public int addCust(Customer cust) throws Exception{
        int i =-1;
        try {
            //继承父类conn
            conn = super.getConnection();
            String sql = "insert into customer values(?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            //设置字段值
            ps.setObject(1,cust.getCustNumber());
            ps.setObject(2,cust.getCustName());
            ps.setObject(3,cust.getCustPwd());
            ps.setObject(4,cust.getCustIdCard());
            ps.setObject(5,cust.getCustMoney());
            ps.setObject(6,cust.getCustDate());
            //将执行语句提交更新
            i = ps.executeUpdate();
            //关闭资源
            super.close(null,ps,conn);
        } catch (SQLException ex) {
            throw new Exception(ex);
        }
        return i;
    }

    /**
     * 计算储蓄总额
     * @return
     */
    @Override
    public double cxze() {
        double m = -1;
        try{
            Connection conn = super.getConnection();
            //计算用户中的所有总额
            String sql = "select sum(custMoney) SumMoney from customer";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if(rs.next()){
                //从结果集中获取查询的总金额
                m = rs.getDouble("SumMoney");
            }
            super.close(null,ps,conn);
        }catch (Exception ex){
            ex.printStackTrace();
        }
        return m;
    }


    /**
     * 富豪排行榜(根据用户的银行账户金额，自动排序)
     * @return
     */
    @Override
    public List<Customer> phb() {
        List<Customer> clist = new ArrayList<>();
        try{
            conn = super.getConnection();
            String sql = "select * from customer order by custMoney desc";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                Customer customer = new Customer();
                customer.setCustNumber(rs.getString("custNumber"));
                customer.setCustName(rs.getString("custName"));
                customer.setCustPwd(rs.getString("custPwd"));
                customer.setCustIdCard(rs.getString("custIdCard"));
                customer.setCustMoney(rs.getDouble("custMoney"));
                customer.setCustDate(rs.getDate("custDate"));
                //将该用户添加到集合中
                clist.add(customer);
            }
            super.close(null,ps,conn);
        }catch (Exception ex){
            ex.printStackTrace();
        }
        return clist;
    }

testAdminDaoImpl方法：

package com.wbs.dao.impl;

import com.wbs.dao.AdminDao;
import com.wbs.entity.Administrator;
import com.wbs.entity.Customer;
import org.junit.Test;

import java.util.List;

public class testAdminDaoImpl {
    @Test
    public void test() throws Exception {
        AdminDao adminDao = new AdminDaoImpl();

        /**
         * 测试管理员登录
         */
        List<Administrator> administratorList =  adminDao.login("老庞","112233");
        System.out.println(administratorList.toString());

        /**
         * 测试添加顾客
         */
        Customer customer = new Customer("1004","赵六","112233","411424188801018001",5000.0,new Date());
        int i = adminDao.addCust(customer);
        if(i>0){
            System.out.println("添加顾客成功！");
        }

        /**
         * 测试查询储蓄总额
         */
        double m = adminDao.cxze();
        System.out.println("储蓄总额：" + m);

        /**
         * 测试查询富豪排行榜
         */
        List<Customer> clist = adminDao.phb();
        for (Customer c:clist){
            System.out.println(c.toString());
        }
    }
}

五、创建DAO层顾客接口CustomerDao

5.1定义顾客登录、存款、取款、查询余额、转账、修改密码方法

CustomerDao接口：

package com.wbs.dao;

import com.wbs.entity.Administrator;
import com.wbs.entity.Customer;
import java.util.Date;
import java.util.List;

public interface CustomerDao {

    /**
     * 顾客登录
     * @param name
     * @param pwd
     */
    public List<Customer> login(String name, String pwd);

    /**
     * 存款
     * @param custNumber 修改哪个用户
     * @param m 修改金额
     * @return
     */
    public int ck(String custNumber,double m);

    /**
     * 取款
     */
    public int qk(String custNumber,double m);

    /**
     * 查询余额
     */
    public double cxye(String custNumber);

    /**
     * 转账
     */
    public boolean zz(String custNumber1,String custNumber2,double m);

    /**
     * 根据银行卡号查询在数据库中是否存在该用户
     */
    public int cxyhkh(String custNumber);

    /**
     * 修改密码
     */
    public int xgmm(String custNumber,String newPwd);
}

CustomerDaoImpl类：

package com.wbs.dao.impl;

import com.wbs.dao.BaseDao;
import com.wbs.dao.CustomerDao;
import com.wbs.entity.Customer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class CustomerDaoImpl extends BaseDao implements CustomerDao {

    private static Connection conn = null;
    private static PreparedStatement ps = null;
    private static ResultSet rs = null;

    @Override
    public List<Customer> login(String name, String pwd) throws Exception{
        List<Customer> customerList = new ArrayList<>();
        try {
            //获取连接
            conn = super.getConnection();
            String sql = "select * from customer where custName = ? and custPwd = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,name);
            ps.setObject(2,pwd);
            rs = ps.executeQuery();
            while (rs.next()){
                Customer customer = new Customer();
                customer.setCustNumber(rs.getString("custNumber"));
                customer.setCustName(rs.getString("custName"));
                customer.setCustPwd(rs.getString("custPwd"));
                customer.setCustIdCard(rs.getString("custIdCard"));
                customer.setCustMoney(rs.getDouble("custMoney"));
                customer.setCustDate(rs.getDate("custDate"));
                customerList.add(customer);
            }
            super.close(rs,ps,conn);
        }catch (Exception ex){
            throw ex;
        }
        return customerList;
    }

    /**
     * 存款(改)
     * @param custNumber 修改哪个用户
     * @param m 修改金额
     * @return
     */
    @Override
    public int ck(String custNumber,double m) {
        int i =-1;
        try{
            conn = super.getConnection();
            String sql = "update customer set custMoney = custMoney + ? where custNumber = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,m);
            ps.setObject(2,custNumber);
            i = ps.executeUpdate();
            super.close(null,ps,conn);
        }catch (Exception ex){
            ex.printStackTrace();
        }
        return i;
    }

    /**
     * 取款(改)
     * @param custNumber
     * @param m
     * @return
     */
    @Override
    public int qk(String custNumber,double m) {
        int i =-1;
        try{
            Connection conn = super.getConnection();
            String sql = "update customer set custMoney = custMoney - ? where custNumber = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setObject(1,m);
            ps.setObject(2,custNumber);
            i = ps.executeUpdate();
            super.close(null,ps,conn);
        }catch (Exception ex){
            ex.printStackTrace();

        }
        return i;
    }

    /**
     * 查询余额
     * @param custNumber
     * @return
     */

    @Override
    public double cxye(String custNumber) {
        double m = -1;
        try{
            conn = super.getConnection();
            String sql = "select custMoney from customer where custNumber = ?";
            ps = conn.prepareStatement(sql);
            //设置执行参数的索引
            ps.setObject(1,custNumber);
            rs = ps.executeQuery();
            if(rs.next()){
                m = rs.getDouble("custMoney");
            }
            super.close(null,ps,conn);
        }catch (Exception ex){
            ex.printStackTrace();
        }
        return m;
    }

    /**
     * 转账(改)
     * @param custNumber1 转出账户1
     * @param custNumber2 转人账户2
     * @param m 转账金额
     * @return
     */
    @Override
    public boolean zz(String custNumber1,String custNumber2,double m) {
        int m1 = qk(custNumber1,m);//custNumber1取出m元钱
        int m2 = ck(custNumber2,m);//custNumber2存入m元钱
        if(m1>0 && m2>0 && m1 == m2)
            return true;
        return false;
    }

    @Override
    public int cxyhkh(String custNumber) {
        return 0;
    }

    /**
     * 修改密码(改)
     * @param custNumber
     * @param newPwd
     * @return
     */
    @Override
    public int xgmm(String custNumber,String newPwd) {
        int i = -1;
        try{
            conn = super.getConnection();
            String sql = "update customer set custPwd = ? where custNumber = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,newPwd);
            ps.setObject(2,custNumber);
            i = ps.executeUpdate();
            super.close(null,ps,conn);
        }catch (Exception ex){
            ex.printStackTrace();
        }
        return i;
    }
}

testCustomerDaoImpl类：

package com.wbs.dao.impl;

import com.wbs.dao.CustomerDao;
import com.wbs.entity.Customer;
import org.junit.Test;
import java.util.List;

public class testCustomerDaoImpl {
    @Test
    public void test() throws Exception {
        CustomerDao customerDao = new CustomerDaoImpl();

        /**
         * 测试顾客登录
         */
        List<Customer> customerList = customerDao.login("张三","123456");
        System.out.println(customerList.toString());

        /**
         * 测试顾客存款
         */
        int i1 = customerDao.ck("1004",1000.0);
        if(i1>0){
            System.out.println("存钱成功！");
        }

        /**
         * 测试顾客取款
         */
        int i2 = customerDao.qk("1004",500.0);
        if(i2>0){
            System.out.println("取款成功！");
        }

        /**
         * 测试查询余额
         */
        double m = customerDao.cxye("1004");
        System.out.println("余额为：" + m);

        /**
         * 测试转账
         */
        boolean b = customerDao.zz("1003","1004",150);
        if(b){
            System.out.println("转账成功！");
        }

        /**
         * 测试修改密码
         */
        int i3 = customerDao.xgmm("1004","654321");
        if(i3>0){
            System.out.println("修改密码成功！");
        }

    }
}

注意！！！此处用户转账有bug，如果说当转出账户语句执行完，随后出现一个运行时报错（eg：算数错误）或者说正在赚钱的过程中钱转出了，突然停电了，这样这个转账流程就出问题了，导致转出账户钱少了，而转入账户却没收到钱，你说气不气？

所以我们要引入事务机制，就是所谓同时成功，同时失败！

同时由于上述DML语言过于繁琐，而且是写死的代码，这样不利于代码的后期维护，开发效率也很低，所以我们要尽可能的把代码进行封装，且提高灵活性。

下篇文章简化对数据库的操作语言DML（利用java反射机制进行封装，并引入事务回滚）！

对java反射机制不熟悉的可以在这提前了解下：
JavaSE进阶系列(十二)、java反射机制