[Examination] Kiểm tra 60 phút - Viết chương trình quản lý lịch thi đấu bóng đá vòng loại 3 World Cup - Lập trình Java nâng cao

B1. Tạo database + tables
create table schedule2 (
	id int PRIMARY KEY AUTO_INCREMENT, 
	team_a varchar(50), 
	team_b varchar(50), 
	startime datetime, 
	endtime datetime, 
	score_a int, 
	score_b int, 
	referee_name varchar(50), 
	note varchar(200) 
)

B2. Tạo project + add thư viện jdbc mysql driver
B3. Mapping tables <-> class object
B4. DAO

#BaseDAO.java

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package lesson12.bt2395;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Diep.Tran
 */
public class BaseDAO {
    static Connection conn = null;
    static PreparedStatement statement = null;

    static void openConnection() {
        try {
            conn = DriverManager.getConnection(Config.DB_URL, Config.USERNAME, Config.PASSWORD);
        } catch (SQLException ex) {
            Logger.getLogger(BaseDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    static void closeConnection() {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException ex) {
                Logger.getLogger(BaseDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(BaseDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
}

#Config.java

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package lesson12.bt2395;

/**
 *
 * @author Diep.Tran
 */
public interface Config {
    String DB_URL = "jdbc:mysql://localhost:3306/C2010L";
    String USERNAME = "root";
    String PASSWORD = "";
}

#Main.java

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package lesson12.bt2395;

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

/**
 *
 * @author Diep.Tran
 */
public class Main {
    static Scanner scan;
    
    public static void main(String[] args) {
        scan = new Scanner(System.in);
        int choose;
        
        do {
            showMenu();
            choose = Integer.parseInt(scan.nextLine());
            
            switch(choose) {
                case 1:
                    input();
                    break;
                case 2:
                    showMatchesLater();
                    break;
                case 3:
                    showMatchesBefore();
                    break;
                case 4:
                    updateMatch();
                    break;
                case 5:
                    showMatches();
                    break;
                case 6:
                    showMatchesByTime();
                    break;
                case 7:
                    System.out.println("Thoat!!!");
                    break;
                default:
                    System.out.println("Nhap sai!!!");
                    break;
            }
        } while(choose != 7);
    }
    
    static void showMenu() {
        System.out.println("1. Nhap thong tin tran dau");
        System.out.println("2. Tran dau sap dien ra");
        System.out.println("3. Tran dau da dien ra");
        System.out.println("4. Cap nhat ket qua");
        System.out.println("5. Tran dau dang dien ra");
        System.out.println("6. Tran dau dang dien ra theo 1 khung gio");
        System.out.println("7. Thoat");
        System.out.println("Chon: ");
    }

    private static void input() {
        System.out.println("Nhap thong tin tran dau: ");
        Schedule schedule = new Schedule();
        schedule.input();
        
        ScheduleDAO.insert(schedule);
    }

    private static void showMatchesLater() {
        List<Schedule> dataList = ScheduleDAO.getMatchesLater();
        
        for (Schedule schedule : dataList) {
            System.out.println(schedule);
        }
    }

    private static void showMatchesBefore() {
        List<Schedule> dataList = ScheduleDAO.getMatchesBefore();
        
        for (Schedule schedule : dataList) {
            System.out.println(schedule);
        }
    }

    private static void updateMatch() {
        System.out.println("Cap nhat thong tin tran dau:");
        System.out.println("Team A: ");
        String teamA = scan.nextLine();
        
        System.out.println("Team B: ");
        String teamB = scan.nextLine();
        
        System.out.println("Thoi gian: ");
        String startime = scan.nextLine();
        
        System.out.println("Thoi gian ket thuc: ");
        String endtime = scan.nextLine();
        
        System.out.println("Ty so A: ");
        int scoreA = Integer.parseInt(scan.nextLine());
        
        System.out.println("Ty so B: ");
        int scoreB = Integer.parseInt(scan.nextLine());
        
        Schedule schedule = new Schedule(0, teamA, teamB, startime, endtime, scoreA, scoreB, null, null);
        ScheduleDAO.update(schedule);
    }

    private static void showMatches() {
        List<Schedule> dataList = ScheduleDAO.showMatches();
        
        for (Schedule schedule : dataList) {
            System.out.println(schedule);
        }
    }

    private static void showMatchesByTime() {
        System.out.println("Nhap khung gio can kiem tra: ");
        String time = scan.nextLine();
        
        List<Schedule> dataList = ScheduleDAO.showMatchesByTime(time);
        
        for (Schedule schedule : dataList) {
            System.out.println(schedule);
        }
    }
}

#Schedule.java

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package lesson12.bt2395;

import java.util.Scanner;

/**
 *
 * @author Diep.Tran
 */
public class Schedule {
    int id;
    String teamA, teamB;
    String startTime, endTime;
    int scoreA, scoreB;
    String refereeName, note;

    public Schedule() {
    }

    public Schedule(int id, String teamA, String teamB, String startTime, String endTime, int scoreA, int scoreB, String refereeName, String note) {
        this.id = id;
        this.teamA = teamA;
        this.teamB = teamB;
        this.startTime = startTime;
        this.endTime = endTime;
        this.scoreA = scoreA;
        this.scoreB = scoreB;
        this.refereeName = refereeName;
        this.note = note;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getTeamA() {
        return teamA;
    }

    public void setTeamA(String teamA) {
        this.teamA = teamA;
    }

    public String getTeamB() {
        return teamB;
    }

    public void setTeamB(String teamB) {
        this.teamB = teamB;
    }

    public String getStartTime() {
        return startTime;
    }

    public void setStartTime(String startTime) {
        this.startTime = startTime;
    }

    public String getEndTime() {
        return endTime;
    }

    public void setEndTime(String endTime) {
        this.endTime = endTime;
    }

    public int getScoreA() {
        return scoreA;
    }

    public void setScoreA(int scoreA) {
        this.scoreA = scoreA;
    }

    public int getScoreB() {
        return scoreB;
    }

    public void setScoreB(int scoreB) {
        this.scoreB = scoreB;
    }

    public String getRefereeName() {
        return refereeName;
    }

    public void setRefereeName(String refereeName) {
        this.refereeName = refereeName;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

    @Override
    public String toString() {
        return "Schedule{" + "id=" + id + ", teamA=" + teamA + ", teamB=" + teamB + ", startTime=" + startTime + ", endTime=" + endTime + ", scoreA=" + scoreA + ", scoreB=" + scoreB + ", refereeName=" + refereeName + ", note=" + note + '}';
    }
    
    public void input() {
        Scanner scan = new Scanner(System.in);
        System.out.println("Nhap doi A: ");
        teamA = scan.nextLine();
        
        System.out.println("Nhap doi B: ");
        teamB = scan.nextLine();
        
        System.out.println("Thoi gian bat dau: ");
        startTime = scan.nextLine();
        
//        System.out.println("Thoi gian ket thuc: ");
//        endTime = scan.nextLine();
//        
//        System.out.println("Ty so A: ");
//        scoreA = Integer.parseInt(scan.nextLine());
//        
//        System.out.println("Ty so B: ");
//        scoreB = Integer.parseInt(scan.nextLine());
        
        System.out.println("Trong tai: ");
        refereeName = scan.nextLine();
        
        System.out.println("Ghi chu: ");
        note = scan.nextLine();
    }
}

#ScheduleDAO.java

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package lesson12.bt2395;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Diep.Tran
 */
public class ScheduleDAO extends BaseDAO{
    public static void insert(Schedule schedule) {
        openConnection();
        
        String sql = "insert into schedule2(team_a, team_b, startime, referee_name, note) "
                + "values(?, ?, ?, ?)";
        try {
            statement = conn.prepareStatement(sql);
            statement.setString(1, schedule.getTeamA());
            statement.setString(2, schedule.getTeamB());
            statement.setString(3, schedule.getStartTime());
            statement.setString(4, schedule.getRefereeName());
            statement.setString(5, schedule.getNote());
            
            statement.execute();
        } catch (SQLException ex) {
            Logger.getLogger(ScheduleDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        closeConnection();
    }
    
    public static void update(Schedule schedule) {
        openConnection();
        
        String sql = "update schedule2 set score_a = ?, score_b = ?, endtime = ? where team_a = ? and team_b = ? and startime = ?";
        try {
            statement = conn.prepareStatement(sql);
            statement.setInt(1, schedule.getScoreA());
            statement.setInt(2, schedule.getScoreB());
            statement.setString(3, schedule.getEndTime());
            statement.setString(4, schedule.getTeamA());
            statement.setString(5, schedule.getTeamB());
            statement.setString(6, schedule.getStartTime());
            
            statement.execute();
        } catch (SQLException ex) {
            Logger.getLogger(ScheduleDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        closeConnection();
    }
    
    public static List<Schedule> getMatchesLater() {
        List<Schedule> dataList = new ArrayList<>();
        
        openConnection();
        
        String sql = "select * from schedule2 where startime > ?";
        try {
            statement = conn.prepareStatement(sql);
            
            statement.setString(1, Utility.convertCurrentDateToString());
            
            ResultSet resultSet = statement.executeQuery();
            while(resultSet.next()) {
                Schedule schedule = new Schedule(
                        resultSet.getInt("id"), 
                        resultSet.getString("team_a"), 
                        resultSet.getString("team_b"), 
                        resultSet.getString("startime"), 
                        resultSet.getString("endtime"), 
                        resultSet.getInt("score_a"), 
                        resultSet.getInt("score_b"), 
                        resultSet.getString("referee_name"), 
                        resultSet.getString("note")
                );
                dataList.add(schedule);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ScheduleDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        closeConnection();
        
        return dataList;
    }
    
    public static List<Schedule> showMatches() {
        List<Schedule> dataList = new ArrayList<>();
        
        openConnection();
        
        String sql = "select * from schedule2 where startime <= ? and (endtime >= ? or endtime is null)";
        try {
            statement = conn.prepareStatement(sql);
            
            statement.setString(1, Utility.convertCurrentDateToString());
            statement.setString(2, Utility.convertCurrentDateToString());
            
            ResultSet resultSet = statement.executeQuery();
            while(resultSet.next()) {
                Schedule schedule = new Schedule(
                        resultSet.getInt("id"), 
                        resultSet.getString("team_a"), 
                        resultSet.getString("team_b"), 
                        resultSet.getString("startime"), 
                        resultSet.getString("endtime"), 
                        resultSet.getInt("score_a"), 
                        resultSet.getInt("score_b"), 
                        resultSet.getString("referee_name"), 
                        resultSet.getString("note")
                );
                dataList.add(schedule);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ScheduleDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        closeConnection();
        
        return dataList;
    }
    
    public static List<Schedule> showMatchesByTime(String time) {
        List<Schedule> dataList = new ArrayList<>();
        
        openConnection();
        
        String sql = "select * from schedule2 where startime <= ? and (endtime >= ? or endtime is null)";
        try {
            statement = conn.prepareStatement(sql);
            
            statement.setString(1, time);
            statement.setString(2, time);
            
            ResultSet resultSet = statement.executeQuery();
            while(resultSet.next()) {
                Schedule schedule = new Schedule(
                        resultSet.getInt("id"), 
                        resultSet.getString("team_a"), 
                        resultSet.getString("team_b"), 
                        resultSet.getString("startime"), 
                        resultSet.getString("endtime"), 
                        resultSet.getInt("score_a"), 
                        resultSet.getInt("score_b"), 
                        resultSet.getString("referee_name"), 
                        resultSet.getString("note")
                );
                dataList.add(schedule);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ScheduleDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        closeConnection();
        
        return dataList;
    }
    
    public static List<Schedule> getMatchesBefore() {
        List<Schedule> dataList = new ArrayList<>();
        
        openConnection();
        
        String sql = "select * from schedule2 where endtime < ?";
        try {
            statement = conn.prepareStatement(sql);
            
            statement.setString(1, Utility.convertCurrentDateToString());
            
            ResultSet resultSet = statement.executeQuery();
            while(resultSet.next()) {
                Schedule schedule = new Schedule(
                        resultSet.getInt("id"), 
                        resultSet.getString("team_a"), 
                        resultSet.getString("team_b"), 
                        resultSet.getString("startime"), 
                        resultSet.getString("endtime"), 
                        resultSet.getInt("score_a"), 
                        resultSet.getInt("score_b"), 
                        resultSet.getString("referee_name"), 
                        resultSet.getString("note")
                );
                dataList.add(schedule);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ScheduleDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        closeConnection();
        
        return dataList;
    }
}

#Utility.java

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package lesson12.bt2395;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

/**
 *
 * @author Diep.Tran
 */
public class Utility {
    public static String convertCurrentDateToString() {
        Date date = Calendar.getInstance().getTime();  
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
        String strDate = dateFormat.format(date);  
        
        return strDate;
    }
}