Java learning notes 51 (integrated project: home accounting system)

This case almost uses all the contents of the first 50 articles to realize a simple family accounting software

Function: add, delete, modify, query by criteria

Required jar package:

commons-dbcp-1.4.jar

commons-pool-1.5.6.jar

mysql-connector-java-5.1.37-bin.jar

commons-dbutils-1.6.jar

Database table creation:

/*
  Create database
  Name gjp
*/
CREATE DATABASE gjp;

USE gjp;

CREATE TABLE gjp_zhangwu(
   -- Primary key
   zwid INT PRIMARY KEY AUTO_INCREMENT,
   -- Classification name   
   flname VARCHAR(200),
   -- Amount of money
   money DOUBLE,
   -- account
   zhanghu VARCHAR(100),
   -- Date created
   createtime DATE,
   -- Account description
   description  VARCHAR(1000)
);

Add some data:

-- Write test data
INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description) VALUES (1,'Meal expenses',247,'Bank of Communications','2018-03-02','Family dinner');
INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description) VALUES (2,'Wage income',12345,'cash','2018-03-15','It's paid');
INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description) VALUES (3,'Clothing expenditure',1998,'cash','2018-04-02','Buy clothes');
INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description) VALUES (4,'Meal expenses',325,'cash','2018-06-18','Dinner with friends');
INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description) VALUES (5,'Stock income',8000,'Industrial and Commercial Bank of China','2018-10-28','The stock soared');
INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description) VALUES (6,'Stock income',5000,'Industrial and Commercial Bank of China','2018-10-28','Stocks are up again');
INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description) VALUES (7,'Wage income',5000,'Bank of Communications','2018-10-28','Pay again');
INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description) VALUES (8,'Cash gift',5000,'cash','2018-10-28','Friends get married');
INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description) VALUES (9,'Other expenses',1560,'cash','2018-10-29','Lost money');
INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description) VALUES (10,'Transportation expenditure',2300,'Bank of Communications','2018-10-29','Oil prices are still rising');
INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description) VALUES (11,'Meal expenses',1000,'Industrial and Commercial Bank of China','2018-10-29','Eat again');
INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description) VALUES (12,'Wage income',1000,'cash','2018-10-30','Open capital');
INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description) VALUES (13,'Transportation expenditure',2000,'cash','2018-10-30','Air tickets are expensive');
INSERT  INTO gjp_zhangwu(zwid,flname,money,zhangHu,createtime,description) VALUES (14,'Wage income',5000,'cash','2018-10-30','Re financing');

effect:

Create a project, create a new package, import the jar package, and the result is as follows:

domain package creation class:

Ensure that the member variable name is consistent with the column name of the table

package gjp.domain;

public class Zhangwu {
    private int zwid;
    private String flname;
    private double money;
    private String zhanghu;
    private String createtime;
    private String description;

    public Zhangwu() {
    }

    public Zhangwu(int zwid, String flname, double money, String zhanghu, String createtime, String description) {
        super();
        this.zwid = zwid;
        this.flname = flname;
        this.money = money;
        this.zhanghu = zhanghu;
        this.createtime = createtime;
        this.description = description;
    }

    public int getZwid() {
        return zwid;
    }

    public void setZwid(int zwid) {
        this.zwid = zwid;
    }

    public String getFlname() {
        return flname;
    }

    public void setFlname(String flname) {
        this.flname = flname;
    }

    public double getMoney() {
        return money;
    }

    public void setMoney(double money) {
        this.money = money;
    }

    public String getZhanghu() {
        return zhanghu;
    }

    public void setZhanghu(String zhanghu) {
        this.zhanghu = zhanghu;
    }

    public String getCreatetime() {
        return createtime;
    }

    public void setCreatetime(String createtime) {
        this.createtime = createtime;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    @Override
    public String toString() {
        return "Zhangwu [zwid=" + zwid + ", flname=" + flname + ", money=" + money + ", zhanghu=" + zhanghu
                + ", createtime=" + createtime + ", description=" + description + "]";
    }

}

tools package create tool class:

package gjp.tools;
//Tool class for getting database connection

//Realization DBCP Connection pool

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;

public class JDBCUtils {
    private static BasicDataSource dataSource = new BasicDataSource();
    static {
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/gjp");
        dataSource.setUsername("root");
        dataSource.setPassword("xuyiqing");
        dataSource.setInitialSize(10);
        dataSource.setMaxActive(10);
        dataSource.setMaxIdle(5);
        dataSource.setMinIdle(1);
    }

    public static DataSource getDataSource() {
        return dataSource;
    }
}

app package:

package gjp.app;

import gjp.view.MainView;

//Main program class, used to open software programs
public class MainApp {
    public static void main(String[] args) {
        new MainView().run();
    }
}

view package:

package gjp.view;

import java.util.List;
import java.util.Scanner;

import gjp.controller.ZhangWuController;
import gjp.domain.Zhangwu;

public class MainView {
    // Interface for users to see and operate
    // Data transfer to controller Layer implementation
    private ZhangWuController controller = new ZhangWuController();

    public void run() {
        // Realize interface effect and receive input
        Scanner sc = new Scanner(System.in);
        while (true) {
            System.out.println("----------------------Home accounting software----------------------");
            System.out.println("1.Add account 2.Edit account 3.Delete account 4.Query account 5.Exit the system");
            System.out.println("Please enter the function serial number to operate(1-5)");
            int choose = sc.nextInt();
            switch (choose) {
            case 1:
                addZhangWu();
                break;
            case 2:
                editZhangWu();
                break;
            case 3:
                deleteZhangWu();
                break;
            case 4:
                selectZhangWu();
                break;
            case 5:
                System.exit(0);
                break;
            }
        }
    }
    
    public void deleteZhangWu(){
        selectAll();
        System.out.println();
        System.out.println("Delete function, please enter the ID");
        int zwid = new Scanner(System.in).nextInt();
        System.out.println("Are you sure you want to delete? Y/N");
        String flag = new Scanner(System.in).next();
        if(flag.equals("Y")){
            controller.deleteZhangWu(zwid);
            System.out.println("Delete account successfully!");
        }else if(flag.equals("N")){
            System.out.println("Enter any key to return");
            new Scanner(System.in).next();
            deleteZhangWu();
        }else{
            System.out.println("Wrong input,Enter any key to return");
            new Scanner(System.in).next();
            deleteZhangWu();
        }
    }
    
    public void editZhangWu(){
        selectAll();
        System.out.println();
        System.out.println("Edit function, please input data");
        Scanner sc = new Scanner(System.in);
        System.out.println("input ID:");
        int zwid = sc.nextInt();
        System.out.println("Enter classification name:");
        String flname = sc.next();
        System.out.println("Enter amount:");
        double money = sc.nextDouble();
        System.out.println("Enter account:");
        String zhanghu = sc.next();
        System.out.println("Enter date(format XXXX-XX-XX):");
        String createtime = sc.next();
        System.out.println("Enter a specific description");
        String description = sc.next();
        Zhangwu zw = new Zhangwu(zwid, flname, money, zhanghu, createtime, description);
        controller.editZhangWu(zw);
        System.out.println("Account editing succeeded!");
    }
    
    public void addZhangWu(){
        System.out.println("Add accounting function, please input the following content");
        Scanner sc = new Scanner(System.in);
        System.out.println("Enter classification name:");
        String flname = sc.next();
        System.out.println("Enter amount:");
        double money = sc.nextDouble();
        System.out.println("Enter account:");
        String zhanghu = sc.next();
        System.out.println("Enter date(format XXXX-XX-XX):");
        String createtime = sc.next();
        System.out.println("Enter a specific description");
        String description = sc.next();
        Zhangwu zw = new Zhangwu(0, flname, money, zhanghu, createtime, description);
        controller.addZhangWu(zw);
        System.out.println("Add account succeeded!");
    }

    public void selectZhangWu() {
        System.out.println("1.Query all 2.Condition query");
        Scanner sc = new Scanner(System.in);
        int selectChooser = sc.nextInt();
        switch (selectChooser) {
        case 1:
            selectAll();
            break;
        case 2:
            select();
            break;
        }
    }

    public void selectAll() {
        List<Zhangwu> list = controller.selectAll();
        if (list.size() != 0) {
            print(list);
        } else {
            System.out.println("No data found");
        }
    }

    public void select() {
        System.out.println("Condition query:Enter date format XXXX-XX-XX");
        System.out.println("for example:2018-01-22");
        Scanner sc = new Scanner(System.in);
        System.out.println("Please enter the start date:");
        String startDate = sc.nextLine();
        System.out.println("Please enter the end date:");
        String endDate = sc.nextLine();
        List<Zhangwu> list = controller.select(startDate, endDate);
        if (list.size() != 0) {
            print(list);
        } else {
            System.out.println("No data found");
        }
    }

    private void print(List<Zhangwu> list) {
        System.out.println("ID\t\t category\t\t account\t\t Amount of money\t\t time\t\t Explain");
        for (Zhangwu zw : list) {
            System.out.println(zw.getZwid() + "\t\t" + zw.getFlname() + "\t\t" + zw.getZhanghu() + "\t\t"
                    + zw.getMoney() + "\t\t" + zw.getCreatetime() + "\t" + zw.getDescription());
        }
    }
}

controller package:

package gjp.controller;

import java.util.List;

import gjp.domain.Zhangwu;
import gjp.service.ZhangWuService;

public class ZhangWuController {
    // Receive view Layer data, passing to service layer
    private ZhangWuService service = new ZhangWuService();
    
    public void deleteZhangWu(int zwid){
        service.deleteZhangWu(zwid);
    }
    
    public void addZhangWu(Zhangwu zw){
        service.addZhangWu(zw);
    }
    
    public void editZhangWu(Zhangwu zw){
        service.editZhangWu(zw);
    }
    
    public List<Zhangwu> select(String startDate,String endDate){
        return service.select(startDate, endDate);
    }
    
    public List<Zhangwu> selectAll(){
        return service.selectAll();
    }
    
}

service package:

package gjp.service;
//Business layer

import java.util.List;

//Receive controller Data for
//Pass after calculation dao Layer operation database

import gjp.dao.ZhangWuDao;
import gjp.domain.Zhangwu;

public class ZhangWuService {
    private ZhangWuDao dao = new ZhangWuDao();
    
    public void deleteZhangWu(int zwid){
        dao.deleteZhangWu(zwid);
    }
    
    public void editZhangWu(Zhangwu zw){
        dao.editZhangWu(zw);
    }
    
    public void addZhangWu(Zhangwu zw){
        dao.addZhangWu(zw);
    }
    
    public List<Zhangwu> select(String startDate, String endDate) {
        return dao.select(startDate, endDate);
    }

    public List<Zhangwu> selectAll() {
        return dao.selectAll();
    }
}

dao package:

package gjp.dao;
//Implement database tables gjp_zhangwu Add, delete, modify and query

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import gjp.domain.Zhangwu;
import gjp.tools.JDBCUtils;

public class ZhangWuDao {
    private QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

    public void deleteZhangWu(int zwid){
        try{
        String sql = "DELETE FROM gjp_zhangwu WHERE zwid=?";
        qr.update(sql,zwid);}catch(SQLException ex){
            System.out.println(ex);
            throw new RuntimeException("Failed to delete account");
        }
    }
    
    public void editZhangWu(Zhangwu zw) {
        try {
            String sql = "UPDATE gjp_zhangwu SET flname=?,money=?,zhanghu=?,createtime=?,description=? WHERE zwid=?";
            Object[] params = { zw.getFlname(), zw.getMoney(), zw.getCreatetime(), zw.getDescription(), zw.getZwid() };
            qr.update(sql, params);
        } catch (SQLException ex) {
            System.out.println(ex);
            throw new RuntimeException("Account edit failed");
        }
    }

    public void addZhangWu(Zhangwu zw) {
        try {
            String sql = "INSERT INTO gjp_zhangwu (flname,money,zhanghu,createtime,description)VALUES(?,?,?,?,?)";
            Object[] params = { zw.getFlname(), zw.getMoney(), zw.getZhanghu(), zw.getCreatetime(),
                    zw.getDescription() };
            qr.update(sql, params);
        } catch (SQLException ex) {
            System.out.println(ex);
            throw new RuntimeException("Failed to add account");
        }
    }

    public List<Zhangwu> select(String startDate, String endDate) {
        try {
            String sql = "SELECT * FROM gjp_zhangwu WHERE createtime BETWEEN ? AND ?";
            Object[] params = { startDate, endDate };
            List<Zhangwu> list = qr.query(sql, new BeanListHandler<Zhangwu>(Zhangwu.class), params);
            return list;
        } catch (SQLException ex) {
            System.out.println(ex);
            throw new RuntimeException("Condition query failed");
        }
    }

    public List<Zhangwu> selectAll() {
        try {
            String sql = "SELECT * FROM gjp_zhangwu";
            List<Zhangwu> list = qr.query(sql, new BeanListHandler<Zhangwu>(Zhangwu.class));
            return list;
        } catch (SQLException ex) {
            System.out.println(ex);
            throw new RuntimeException("Failed to query all accounts");
        }
    }
}