[Source Code] Tìm hiểu về CSDL trong Java - C2206L

Hoàn thiện thêm chương trình theo menu sau:

1. Hiển thị danh sách sinh viên

2. Thêm sinh viên

3. Sửa thông tin sinh viên theo ID

4. Xóa sinh viên theo ID

5. Tìm kiếm theo tên

6. Sắp xếp theo tên

7. Thoat

#Main.java

Name: Học Lập Trình Từ A - Z (HTML/CSS/JS - Bootstrap/JQuery - AngularJS - PHP/Laravel - Java v.v)
Address: Số 8, Tôn Thất Thuyết, Mỹ Đình, Từ Liêm, Hà Nội, Nam Từ Liêm, Hà Nội, 100000
Telephone: 0762103668
Price range: $$$

/*
 * 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 com.gokisoft.lesson06;

import java.util.List;

/**
 *
 * @author teacher
 */
public class Main {

    public static void main(String[] args) {
        Students std = new Students("Xin Chao", "abc@gmail.com", "2023-02-12", "2023-02-20");
        StudentsDAO.insert(std);

        List<Students> dataList = StudentsDAO.select();

        for (Students students : dataList) {
            System.out.println(students);
        }
    }
}

#Students.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 com.gokisoft.lesson06;

/**
 *
 * @author teacher
 */
public class Students {
    int id;
    String fullname;
    String email;
    String createdAt;
    String updatedAt;

    public Students() {
    }

    public Students(int id, String fullname, String email, String createdAt, String updatedAt) {
        this.id = id;
        this.fullname = fullname;
        this.email = email;
        this.createdAt = createdAt;
        this.updatedAt = updatedAt;
    }

    public Students(String fullname, String email, String createdAt, String updatedAt) {
        this.fullname = fullname;
        this.email = email;
        this.createdAt = createdAt;
        this.updatedAt = updatedAt;
    }
    
    public int getId() {
        return id;
    }

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

    public String getFullname() {
        return fullname;
    }

    public void setFullname(String fullname) {
        this.fullname = fullname;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getCreatedAt() {
        return createdAt;
    }

    public void setCreatedAt(String createdAt) {
        this.createdAt = createdAt;
    }

    public String getUpdatedAt() {
        return updatedAt;
    }

    public void setUpdatedAt(String updatedAt) {
        this.updatedAt = updatedAt;
    }

    @Override
    public String toString() {
        return "id=" + id + ", fullname=" + fullname + ", email=" + email + ", createdAt=" + createdAt + ", updatedAt=" + updatedAt;
    }
}

#StudentsDAO.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 com.gokisoft.lesson06;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
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 teacher
 */
public class StudentsDAO {
    static Connection conn = null;
    static PreparedStatement statement = null;
    
    static void open() {
        try {
            //B1. Ket noi CSDL
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3307/c2108l", "root", "");
        } catch (SQLException ex) {
            Logger.getLogger(StudentsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    static void close() {
        //B3. Dong ket noi
        if(statement != null) {
            try {
                statement.close();
            } catch (SQLException ex) {
                Logger.getLogger(StudentsDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(StudentsDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
        statement = null;
        conn = null;
    }
    
    public static List<Students> select() {
        List<Students> dataList = new ArrayList<>();
        
        open();
        try {
            //B2. Query du lieu ra
            String sql = "select * from students";
            statement = conn.prepareStatement(sql);
            
            ResultSet resultSet = statement.executeQuery();
            
            while (resultSet.next()) {                
                Students std = new Students(
                        resultSet.getInt("id"), 
                        resultSet.getString("fullname"), 
                        resultSet.getString("email"), 
                        resultSet.getString("created_at"), 
                        resultSet.getString("updated_at")
                );
                dataList.add(std);
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        close();
        
        return dataList;
    }
    
    public static void insert(Students std) {
        open();
        try {
            //B2. Query du lieu ra
            String sql = "insert into students(fullname, email, created_at, updated_at) values (?, ?, ?, ?)";
            statement = conn.prepareStatement(sql);
            statement.setString(1, std.getFullname());
            statement.setString(2, std.getEmail());
            statement.setString(3, std.getCreatedAt());
            statement.setString(4, std.getUpdatedAt());
            
            statement.execute();
        } catch (SQLException ex) {
            Logger.getLogger(StudentsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        close();
    }
    
    public static void update(Students std) {
        open();
        try {
            //B2. Query du lieu ra
            String sql = "update students set fullname = ?, email = ?, updated_at = ? where id = ?";
            statement = conn.prepareStatement(sql);
            statement.setString(1, std.getFullname());
            statement.setString(2, std.getEmail());
            statement.setString(3, std.getUpdatedAt());
            statement.setInt(4, std.getId());
            
            statement.execute();
        } catch (SQLException ex) {
            Logger.getLogger(StudentsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        close();
    }
    
    public static void delete(int id) {
        open();
        try {
            //B2. Query du lieu ra
            String sql = "delete from students where id = ?";
            statement = conn.prepareStatement(sql);
            statement.setInt(1, id);
            
            statement.execute();
        } catch (SQLException ex) {
            Logger.getLogger(StudentsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        close();
    }
    
    public static Students findById(int id) {
        Students std = null;
        open();
        try {
            //B2. Query du lieu ra
            String sql = "select * from students where id = ?";
            statement = conn.prepareStatement(sql);
            statement.setInt(1, id);
            
            ResultSet resultSet = statement.executeQuery();
            
            while (resultSet.next()) {                
                std = new Students(
                        resultSet.getInt("id"), 
                        resultSet.getString("fullname"), 
                        resultSet.getString("email"), 
                        resultSet.getString("created_at"), 
                        resultSet.getString("updated_at")
                );
                break;
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        close();
        
        return std;
    }
}

CODE SAU UPDATE

#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 com.gokisoft.lesson06;

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

/**
 *
 * @author teacher
 */
public class Main {
    static Scanner scan = new Scanner(System.in);
    
    public static void main(String[] args) {
//        Students std = new Students("Xin Chao", "abc@gmail.com", "2023-02-12", "2023-02-20");
//        StudentsDAO.insert(std);
//
//        List<Students> dataList = StudentsDAO.select();
//
//        for (Students students : dataList) {
//            System.out.println(students);
//        }
    
        int choose;
        
        do {            
            showMenu();
            choose = Integer.parseInt(scan.nextLine());
            
            switch(choose) {
                case 1:
                    display();
                    break;
                case 2:
                    input();
                    break;
                case 3:
                    edit();
                    break;
                case 4:
                    delete();
                    break;
                case 5:
                    search();
                    break;
                case 6:
                    sort();
                    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. Hien thi danh sach");
        System.out.println("2. Them sinh vien moi");
        System.out.println("3. Sua");
        System.out.println("4. Xoa");
        System.out.println("5. Tim kiem theo ten");
        System.out.println("6. Sap xep");
        System.out.println("7. Thoat");
        System.out.println("Chon: ");
    }

    private static void display() {
        List<Students> dataList = StudentsDAO.select();
        
        System.out.println("Danh sach sinh vien: ");
        for (Students students : dataList) {
            System.out.println(students);
        }
    }

    private static void input() {
        Students std = new Students();
        std.input();
        
        StudentsDAO.insert(std);
    }

    private static void edit() {
        System.out.println("Nhap ID can sua: ");
        int id = Integer.parseInt(scan.nextLine());
        
        Students std = StudentsDAO.findById(id);
        std.input();
        
        StudentsDAO.update(std);
    }

    private static void delete() {
        System.out.println("Nhap ID can xoa: ");
        int id = Integer.parseInt(scan.nextLine());
        
        StudentsDAO.delete(id);
        System.out.println("Xoa thanh cong!!!");
    }

    private static void search() {
        System.out.println("Nhap ten can tim kiem: ");
        String search = scan.nextLine();
        
        List<Students> dataList = StudentsDAO.searchByName(search);
        
        System.out.println("Ket quan tim thay: ");
        for (Students students : dataList) {
            System.out.println(students);
        }
    }

    private static void sort() {
        List<Students> dataList = StudentsDAO.sortByName();
        
        for (Students students : dataList) {
            System.out.println(students);
        }
    }
}

#Students.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 com.gokisoft.lesson06;

import java.util.Scanner;

/**
 *
 * @author teacher
 */
public class Students {
    int id;
    String fullname;
    String email;
    String createdAt;
    String updatedAt;

    public Students() {
    }

    public Students(int id, String fullname, String email, String createdAt, String updatedAt) {
        this.id = id;
        this.fullname = fullname;
        this.email = email;
        this.createdAt = createdAt;
        this.updatedAt = updatedAt;
    }

    public Students(String fullname, String email, String createdAt, String updatedAt) {
        this.fullname = fullname;
        this.email = email;
        this.createdAt = createdAt;
        this.updatedAt = updatedAt;
    }
    
    public void input() {
        Scanner scan = new Scanner(System.in);
        System.out.println("Nhap ten: ");
        fullname = scan.nextLine();
        System.out.println("Nhap email: ");
        email = scan.nextLine();
        createdAt = "2023-02-12";
        updatedAt = "2023-02-20";
    }
    
    public int getId() {
        return id;
    }

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

    public String getFullname() {
        return fullname;
    }

    public void setFullname(String fullname) {
        this.fullname = fullname;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getCreatedAt() {
        return createdAt;
    }

    public void setCreatedAt(String createdAt) {
        this.createdAt = createdAt;
    }

    public String getUpdatedAt() {
        return updatedAt;
    }

    public void setUpdatedAt(String updatedAt) {
        this.updatedAt = updatedAt;
    }

    @Override
    public String toString() {
        return "id=" + id + ", fullname=" + fullname + ", email=" + email + ", createdAt=" + createdAt + ", updatedAt=" + updatedAt;
    }
}

#StudentsDAO.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 com.gokisoft.lesson06;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
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 teacher
 */
public class StudentsDAO {
    static Connection conn = null;
    static PreparedStatement statement = null;
    
    static void open() {
        try {
            //B1. Ket noi CSDL
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3307/c2108l", "root", "");
        } catch (SQLException ex) {
            Logger.getLogger(StudentsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    static void close() {
        //B3. Dong ket noi
        if(statement != null) {
            try {
                statement.close();
            } catch (SQLException ex) {
                Logger.getLogger(StudentsDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(StudentsDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
        statement = null;
        conn = null;
    }
    
    public static List<Students> searchByName(String name) {
        List<Students> dataList = new ArrayList<>();
        
        open();
        try {
            //B2. Query du lieu ra
            String sql = "select * from students where fullname like ?";
            statement = conn.prepareStatement(sql);
            statement.setString(1, "%" + name + "%");
            
            ResultSet resultSet = statement.executeQuery();
            
            while (resultSet.next()) {                
                Students std = new Students(
                        resultSet.getInt("id"), 
                        resultSet.getString("fullname"), 
                        resultSet.getString("email"), 
                        resultSet.getString("created_at"), 
                        resultSet.getString("updated_at")
                );
                dataList.add(std);
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        close();
        
        return dataList;
    }
    
    public static List<Students> sortByName() {
        List<Students> dataList = new ArrayList<>();
        
        open();
        try {
            //B2. Query du lieu ra
            String sql = "select * from students order by fullname asc";
            statement = conn.prepareStatement(sql);
            
            ResultSet resultSet = statement.executeQuery();
            
            while (resultSet.next()) {                
                Students std = new Students(
                        resultSet.getInt("id"), 
                        resultSet.getString("fullname"), 
                        resultSet.getString("email"), 
                        resultSet.getString("created_at"), 
                        resultSet.getString("updated_at")
                );
                dataList.add(std);
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        close();
        
        return dataList;
    }
    
    public static List<Students> select() {
        List<Students> dataList = new ArrayList<>();
        
        open();
        try {
            //B2. Query du lieu ra
            String sql = "select * from students";
            statement = conn.prepareStatement(sql);
            
            ResultSet resultSet = statement.executeQuery();
            
            while (resultSet.next()) {                
                Students std = new Students(
                        resultSet.getInt("id"), 
                        resultSet.getString("fullname"), 
                        resultSet.getString("email"), 
                        resultSet.getString("created_at"), 
                        resultSet.getString("updated_at")
                );
                dataList.add(std);
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        close();
        
        return dataList;
    }
    
    public static void insert(Students std) {
        open();
        try {
            //B2. Query du lieu ra
            String sql = "insert into students(fullname, email, created_at, updated_at) values (?, ?, ?, ?)";
            statement = conn.prepareStatement(sql);
            statement.setString(1, std.getFullname());
            statement.setString(2, std.getEmail());
            statement.setString(3, std.getCreatedAt());
            statement.setString(4, std.getUpdatedAt());
            
            statement.execute();
        } catch (SQLException ex) {
            Logger.getLogger(StudentsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        close();
    }
    
    public static void update(Students std) {
        open();
        try {
            //B2. Query du lieu ra
            String sql = "update students set fullname = ?, email = ?, updated_at = ? where id = ?";
            statement = conn.prepareStatement(sql);
            statement.setString(1, std.getFullname());
            statement.setString(2, std.getEmail());
            statement.setString(3, std.getUpdatedAt());
            statement.setInt(4, std.getId());
            
            statement.execute();
        } catch (SQLException ex) {
            Logger.getLogger(StudentsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        close();
    }
    
    public static void delete(int id) {
        open();
        try {
            //B2. Query du lieu ra
            String sql = "delete from students where id = ?";
            statement = conn.prepareStatement(sql);
            statement.setInt(1, id);
            
            statement.execute();
        } catch (SQLException ex) {
            Logger.getLogger(StudentsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        close();
    }
    
    public static Students findById(int id) {
        Students std = null;
        open();
        try {
            //B2. Query du lieu ra
            String sql = "select * from students where id = ?";
            statement = conn.prepareStatement(sql);
            statement.setInt(1, id);
            
            ResultSet resultSet = statement.executeQuery();
            
            while (resultSet.next()) {                
                std = new Students(
                        resultSet.getInt("id"), 
                        resultSet.getString("fullname"), 
                        resultSet.getString("email"), 
                        resultSet.getString("created_at"), 
                        resultSet.getString("updated_at")
                );
                break;
            }
        } catch (SQLException ex) {
            Logger.getLogger(StudentsDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        close();
        
        return std;
    }
}

Phản hồi từ học viên

5

(Dựa trên đánh giá ngày hôm nay)

[Source Code] Tìm hiểu về CSDL trong Java - C2206L

#Main.java

#Students.java

#StudentsDAO.java

#Main.java

#Students.java

#StudentsDAO.java

Tags:

Phản hồi từ học viên

5