I would like to write an article for those who are writing applications for the first time using Java. We would appreciate it if you could use it as a reference for creating training assignments for your portfolio and company. This time, we will create a task manager. By applying this, you can also use it to create Twitter clones.
I will post articles for each function of the application.
eclipse4.16.0 Tomcat9 Java11 Mysql5.7
table of contents
Database name task_db Right username testuser Password password Table user master table Category master table Status master table Task table (has Foreign Key for user ID, category ID, status ID) Comment table (has Foreign Key for user ID and task ID)
① Database creation
create databasetask_db
② User creation First, become the root user and change the password validation
Set global validate_password.length=6;Password length to 6
set global validate_password.policy=LOW;Change policy
Then create a user with a password
create user testuser identified by 'password'
③ Give the user full authority of task_db
grant all on task_db.* to testuser
The preparation is complete.
④ Create each table.
--------User's master table
create table m_user (
user_id varchar(24) primary key,
password varchar(32) not null,
user_name varchar(20) not null unique,
update_datetime timestamp not null default current_timestamp on update current_timestamp
);
--------Category master table
create table m_category (
category_id int primary key auto_increment,
category_name varchar(20) not null unique,
update_datetime timestamp not null default current_timestamp on update current_timestamp
);
--------Status master table
create table m_status (
status_code char(2) primary key,
status_name varchar(20) unique not null,
update_datetime timestamp not null default current_timestamp on update current_timestamp
);
--------Task table
create table t_task (
task_id int auto_increment primary key,
task_name varchar(50) not null,
category_id int not null,
limit_date date,
user_id varchar(24) not null,
status_code char(2) not null,
memo varchar(100),
create_datetime timestamp default current_timestamp not null,
update_datetime timestamp default current_timestamp not null on update current_timestamp,
foreign key(category_id) references m_category(category_id),
foreign key(user_id) references m_user(user_id),
foreign key(status_code) references m_status(status_code)
);
--------Comment table
create table t_comment(
comment_id int auto_increment primary key,
task_id int,
user_id varchar(100) not null,
comment varchar(100) not null,
update_datetime timestamp default current_timestamp not null on update current_timestamp,
foreign key(task_id) references t_task(task_id) on delete set null on update cascade,
foreign key(user_id) references m_user(user_id)
);
Next, put the initial data.
-------Category record creation
insert into m_category (category_name) value("New product A: Development project");
insert into m_category (category_name) value("Existing product B: Improvement project");
select * from m_category;
--------Status record creation
insert into m_status ( status_code, status_name) values("00", "not started yet");
insert into m_status ( status_code, status_name) values("50", "Start");
insert into m_status ( status_code, status_name) values("99", "Done");
select * from m_status;
--------User record creation
insert into m_user(user_id, password, user_name) values("testid", "password", "testuser");
insert into m_user(user_id, password, user_name) values("testid2", "password", "testuser2");
--------Task record creation
insert into t_task(task_name, category_id, limit_date, user_id, status_code, memo) values("Sample task 1", 1, "2022-10-01", "testid", "99", "Test input");
insert into t_task(task_name, category_id, limit_date, user_id, status_code, memo) values("Sample task 2", 2, "2020-07-05", "testid", "50", "Test input");
insert into t_task(task_name, category_id, limit_date, user_id, status_code, memo) values("Sample task 2", 1, "2020-09-30", "testid", "00", "Test input");
insert into t_task(task_name, category_id, limit_date, user_id, status_code, memo) values("Sample task 3", 2, "2002-08-30", "testid", "99", "Test input");
insert into t_task(task_name, category_id, limit_date, user_id, status_code, memo) values("Sample task 4", 1, "2000-09-30", "testid", "00", "Test input");
insert into t_task(task_name, category_id, limit_date, user_id, status_code, memo) values("Sample task 5", 2, "2025-09-30", "testid", "50", "Test input");
insert into t_task(task_name, category_id, limit_date, user_id, status_code, memo) values("Sample task 6", 1, "1998-09-30", "testid", "00", "Test input");
insert into t_task(task_name, category_id, limit_date, user_id, status_code, memo) values("Sample task 7", 2, "2020-09-30", "testid", "99", "Test input");
insert into t_task(task_name, category_id, limit_date, user_id, status_code, memo) values("Sample task 8", 1, "2020-10-30", "testid", "00", "Test input");
Here, we will create beans for each table. It just describes the fields, the constructor, the getters and the setters. After checking, it's okay to copy and paste
model.entity.TaskBean.java
public class TaskBean {
/**
*field
*/
private int task_id;
private String task_name;
private int category_id;
private Date limit_date;
private String user_id;
private String status_code;
private String memo;
private Timestamp create_datetime;
private Timestamp update_datetime;
private int version;
/**
*constructor
*/
public TaskBean(){
}
/**
*Method
*/
public int getTask_id() {
return task_id;
}
public void setTask_id(int task_id) {
this.task_id = task_id;
}
public String getTask_name() {
return task_name;
}
public void setTask_name(String task_name) {
this.task_name = task_name;
}
public int getCategory_id() {
return category_id;
}
public void setCategory_id(int category_id) {
this.category_id = category_id;
}
public Date getLimit_date() {
return limit_date;
}
public void setLimit_date(Date limit_date) {
this.limit_date = limit_date;
}
public String getUser_id() {
return user_id;
}
public void setUser_id(String user_id) {
this.user_id = user_id;
}
public String getStatus_code() {
return status_code;
}
public void setStatus_code(String status_code) {
this.status_code = status_code;
}
public String getMemo() {
return memo;
}
public void setMemo(String memo) {
this.memo = memo;
}
public Timestamp getCreate_datetime() {
return create_datetime;
}
public void setCreate_datetime(Timestamp create_datetime) {
this.create_datetime = create_datetime;
}
public Timestamp getUpdate_datetime() {
return update_datetime;
}
public void setUpdate_datetime(Timestamp update_datetime) {
this.update_datetime = update_datetime;
}
public int getVersion() {
return version;
}
public void setVersion(int version) {
this.version = version;
}
}
model.entity.UserBean.java
public class UserBean {
/**
*field
*/
private String user_id;
private String password;
private String user_name;
/**
*constructor
*/
public UserBean(){
}
/**
*Method
*/
public String getUser_id() {
return user_id;
}
public void setUser_id(String user_id) {
this.user_id = user_id;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getUser_name() {
return user_name;
}
public void setUser_name(String user_name) {
this.user_name = user_name;
}
}
model.entity.StatusBean.java
public class StatusBean {
/**
*field
*/
private String status_code;
private String status_name;
/**
*constructor
*/
public StatusBean(){
}
/**
*Method
*/
public String getStatus_code() {
return status_code;
}
public void setStatus_code(String status_code) {
this.status_code = status_code;
}
public String getStatus_name() {
return status_name;
}
public void setStatus_name(String status_name) {
this.status_name = status_name;
}
}
model.entity.CategoryBean.java
public class CategoryBean {
/**
*field
*/
private int category_id;
private String category_name;
/**
*constructor
*/
public CategoryBean(){
}
/**
*Method
*/
public int getCategory_id() {
return category_id;
}
public void setCategory_id(int category_id) {
this.category_id = category_id;
}
public String getCategory_name() {
return category_name;
}
public void setCategory_name(String category_name) {
this.category_name = category_name;
}
}
model.entity.CommentBean.java
public class CommentBean {
/**
*field
*/
private int comment_id;
private int task_id;
private String user_id;
private String comment;
private Timestamp update_datetime;
/**
*constructor
*/
public CommentBean() {
}
/**
*Method
*/
public int getComment_id() {
return comment_id;
}
public void setComment_id(int comment_id) {
this.comment_id = comment_id;
}
public int getTask_id(){
return task_id;
}
public void setTask_id(int task_id) {
this.task_id = task_id;
}
public String getUser_id() {
return user_id;
}
public void setUser_id(String user_id) {
this.user_id = user_id;
}
public String getComment() {
return comment;
}
public void setComment(String comment) {
this.comment = comment;
}
public Timestamp getUpdate_datetime() {
return update_datetime;
}
public void setUpdate_datetime(Timestamp update_datetime) {
this.update_datetime = update_datetime;
}
}
① Put JDBC in WebContent-Web-INF-lib of the project (2) Add from the build path configuration ③ Create connection manageable The variables USER and PASSWORD are passwords and usernames are testuser and password, respectively.
model.dao.ConnectionManager.java
public class ConnectionManager {
//Database information
private final static String URL = "jdbc:mysql://localhost:3306/task_db?useSSL=false&serverTimezone=JST";
private final static String USER = "testuser";
private final static String PASSWORD = "password";
//Connection method to database
public static Connection getConnection() throws SQLException, ClassNotFoundException{
Class.forName("com.mysql.cj.jdbc.Driver");
return DriverManager.getConnection(URL, USER, PASSWORD);
}
}
This time, we went from database creation to bean creation and connection with mysql. Next time, I will add a login function using the m_user table I created.
Recommended Posts