1. Foundation

1-1. Project design knowledge points

• Basics: object oriented, string, number, date
• Intermediate: exception, collection, JDBC, emission mechanism, I/O, Swing, updating data with TavleModel, skin of graphical interface
• Advanced Icon: chart dynamic generation, database backup and recovery, custom circular progress bar
• Software design idea: Singleton mode, loose coupling of panel class and listener class, Entity layer design, DAO layer design and Service layer design
• Business inspection processing methods: CRUD operation, configuration information, configuration information initialization, report generation, one to many relationship and many to one relationship

1-2. Software molding

• Java source file
• Package as exe program

1-3. Function list

• Consumption list
• Make a note
• Consumption classification management
• Monthly consumption report
• Set budget and database path
• Backup data
• Recover data

1-4. Development process

• Table structure design: database design

Field meaning, type, restriction, relationship between tables, one to many, many to one, primary key constraint, foreign key constraint

• Prototype design

Prototype design is to design the interface first, but false data is used on the interface, which makes it easier to clarify the implementation of various functions and communicate with customers more effectively (confirm the developed functions before formal development)

First build a simple interface - and then gradually optimize decoupling and build tool classes

• Design of entity classes and DAO: database related operations
• Function development

MVC mode determined as multi-layer structure: plan DAO layer and Service layer based on event driven. Gradually design, refactor and optimize in development

There are interdependencies between each module: the development sequence needs to be confirmed

Situations requiring the use of third-party packages - temporary learning methods

database→DAO layer→Service layer→Driven Event
  
       Entity

1-5. Learning methods

• Forward learning [recommended for beginners]: promote learning according to the chapter
• Reverse learning [more efficient]: first run with the existing project jar package to see the effect, and then go back and sigh

2. Table structure design

2-1. Overview

Table contents of structural design:

• Database and table creation
• Table relation
• constraint

2-2. Create database and table [MySQL]

It is preferred to confirm the number of tables and the fields in each table

show databases; # View all databases in the current database relational system
Create database if not exists SimpleBillingRecords;# Add judgment before creating

use SimpleBillingRecords;# Select the currently created database

# Create a configuration information table (including keys and storing configuration information in key value pairs)
create table config(
	id int,# The number used to record configuration information, which can be used as the primary key
    key_ varchar(255), # key is a keyword in the database. You need to add an underscore to distinguish it
    value varchar(255) # Key value pairs use variable length strings to save space
)engine=InnoDB default charset=utf8;# Specify the database engine and character set to use


# Create a consumption classification table (including id, type name)
create table consumptionType(
	id int, # The number of the record type, which can be used as the primary key
    typeName varchar(255)  # Name of the type of consumption recorded
)engine=InnoDB default charset=utf8;# Each table you create needs to specify an engine and character set

# Create a consumption table (including id, consumption amount, consumption type (corresponding to the id in the previous table), remarks and record date)
create table consumptionrecord(
	id int, # Record the number of each consumption, which can be used as the primary key
    amount int, # Record the amount of consumption
    consumptionTypeid int, # Associated to consumption category id
    date date
)engine=InnoDB default charset=utf8;# Each table you create needs to specify an engine and character set

2-3. Table relationship and foreign key confirmation

Common relationships between database tables: one to one, one to many, and many to one

A consumption record - only one consumption type and one consumption type - can have multiple consumption records

Therefore, the relationship between type table and record table in this project is one to many

The value of consumptionTyepid in the consumption table is associated with the id value in the type table

2-4. Create table constraints

use simplebillingrecords;

# The primary keys of the three tables are set to the id field alter
alter table config add constraint pk_config_id  primary key(id);
# Modify table name, add constraint name, primary key constraint and field
alter table consumptionrecord add constraint pk_consumptionrecord_id  primary key(id);
alter table consumptiontype add constraint pk_consumptiontype  primary key(id);

# The primary keys of the three tables are set to self growth
# Self growth is a common data insertion strategy. When data is actually written, the id is automatically generated by the database system
alter table config change id id int auto_increment;
# Modify the table name, change the original field, new field and its self growth constraint
alter table consumptionrecord change id id int auto_increment;
alter table consumptiontype change id id int auto_increment;

# Set foreign key (set the type id in the record table as a foreign key and associate it with the id in the type table)
alter table consumptionrecord add constraint fk_recored_type foreign key (consumptionTypeid)references consumptiontype(id);
# Modify the table name, add the constraint name, and associate the foreign key constraint and field to the associated table name and field

2-5. Description

In the actual working environment, database creation, table creation and field constraints are generally put together to form sql files to facilitate the creation of database structure at one time (the use environment needs to be considered)

# 1. Create a database and add judgment before creating
Create database if not exists SimpleBillingRecordsTest;
# 2. Database created using
use SimpleBillingRecordsTest;# Select the currently created database

# 3. Create table 1 - configuration information table (including fields and constraints)
create table if not exists config(
	id int primary key auto_increment,
    key_ varchar(255),
    value varchar(255)
)engine=InnoDB default charset=utf8;

# 4. Create table 2 - type table (because it contains the primary key to be associated with the consumption table)
create table consumptionType(
	id int primary key auto_increment,
    typeName varchar(255)
)engine=InnoDB default charset=utf8;

# 5. Create table 3 - record table (including fields and constraints)
create table consumptionrecord(
	id int primary key auto_increment,
    amount int, 
    consumptionTypeid int,
    date date,
    constraint fk_record_type foreign key(consumptionTypeid) references consumptionrecord(id)
)engine=InnoDB default charset=utf8;# Each table you create needs to specify an engine and character set

At this time, a database of test class is created, and the table structure is the same as the database created step by step

3. Prototype Foundation

3-1. Overview

Start with a simple interface - Evolution and reconstruction interface - experience different injection ideas and methods

3-2. Primitive prototype

The prototype design is to make the user interface first, but use false data on the interface first

Purpose: through designing user interface prototype, communicate with users more effectively and confirm the functions that need to be realized and can be deleted

Build the user interface directly using JFrame

4. Prototype interface class

5. Entity classes and DAO

6. Function

7. Summary