[Share Code] CRUD - Chương trình quản lý sinh viên - Test CSDL - jdbc mysql driver - Lập Trình Java

B1. Cài đặt XMPP -> Start mysql

- Tạo CSDL đặt tên là C1907L

- Tao bảng student

create table student (
	rollno varchar(20) primary key,
	fullname varchar(50),
	address varchar(150),
	gender varchar(16),
	birthday date
)

B2. Tải thư viện jdbc mysql driver từ link

https://mvnrepository.com/artifact/mysql/mysql-connector-java/8.0.20

B3. Tạo project.

#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 lession7;

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

/**
 *
 * @author Diep.Tran
 */
public class Main {
    public static void main(String[] args) {
//        testInsertDB();
//        testDeleteDB();
        //tuong tu code phan update giong vs insett & delete
        //lay du lieu tu database ra
        testSelectDB();
    }
    
    static void testSelectDB() {
        List<Student> students = StudentModify.findAll();
        
        students.forEach((student) -> {
            System.out.println(student);
        });
    }
    
    static void testDeleteDB() {
        Scanner scan = new Scanner(System.in);
        System.out.println("Nhap MSV Can Xoa: ");
        String rollNo = scan.nextLine();
        
        StudentModify.delete(rollNo);
    }
    
    static void testInsertDB() {
        Scanner scan = new Scanner(System.in);
        System.out.println("Nhap MSV: ");
        String rollNo = scan.nextLine();
        System.out.println("Nhap Ten: ");
        String fullname = scan.nextLine();
        System.out.println("Nhap DC: ");
        String address = scan.nextLine();
        System.out.println("Nhap Gioi Tinh: ");
        String gender = scan.nextLine();
        System.out.println("Nhap Ngay Sinh (yyyy-mm-dd): ");
        String birthday = scan.nextLine();
        
        Student std = new Student(rollNo, fullname, address, gender, birthday);
        
        StudentModify.insert(std);
    }
}

#Student.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 lession7;

/**
 *
 * @author Diep.Tran
 */
public class Student {
    String rollno, fullname, address, gender, birthday;

    public Student() {
    }

    public Student(String rollno, String fullname, String address, String gender, String birthday) {
        this.rollno = rollno;
        this.fullname = fullname;
        this.address = address;
        this.gender = gender;
        this.birthday = birthday;
    }

    public String getRollno() {
        return rollno;
    }

    public void setRollno(String rollno) {
        this.rollno = rollno;
    }

    public String getFullname() {
        return fullname;
    }

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

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getBirthday() {
        return birthday;
    }

    public void setBirthday(String birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "Student{" + "rollno=" + rollno + ", fullname=" + fullname + ", address=" + address + ", gender=" + gender + ", birthday=" + birthday + '}';
    }
}

#StudentModify.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 lession7;

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 Diep.Tran
 */
public class StudentModify {
    //select start
    public static List<Student> findAll() {
        //      start
        List<Student> students = new ArrayList<>();
        
        Connection con = null;
        PreparedStatement statement = null;
        
        try {
            //TEST insert ban student vao CSDL
            //B1. Open connection toi database
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/C1907L", "root", "");
            
            //B2. tao 1 truy van toi CSDL - insert data vao CSDL
//            String query = "insert into student(rollno, fullname, address, gender, birthday) values (\"R001\", \"TRAN VAN A\", \"HA NOI\", \"NAM\", \"1996-06-06\")";
            String query = "select * from student";
            statement = con.prepareCall(query);
            
            ResultSet resultSet = statement.executeQuery();
            
            //next() => cho phep duyet qua tung ban ghi
            while(resultSet.next()) {
                String rollNoVar = resultSet.getString("rollno");//rollno => ten column trong bang student
                String fullnameVar = resultSet.getString("fullname");//fullname => ten column trong bang student
                String addressVar = resultSet.getString("address");//address => ten column trong bang student
                String genderVar = resultSet.getString("gender");//gender => ten column trong bang student
                String birthdayVar = resultSet.getString("birthday");//birthday => ten column trong bang student
                
                Student std = new Student(rollNoVar, fullnameVar, addressVar, genderVar, birthdayVar);
                students.add(std);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //B3. Dong connection
            if(statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            
            if(con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        
        return students;
//      end
    }
    //select end
    
    //delete start
    public static void delete(String rollNo) {
 //      start
        Connection con = null;
        PreparedStatement statement = null;
        
        try {
            //TEST insert ban student vao CSDL
            //B1. Open connection toi database
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/C1907L", "root", "");
            
            //B2. tao 1 truy van toi CSDL - insert data vao CSDL
            String query = "delete from student where rollno = ?";
            
            statement = con.prepareCall(query);
            
            statement.setString(1, rollNo);
            
            statement.execute();
        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //B3. Dong connection
            if(statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            
            if(con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
//      end
    }
    //delete end
    
    //insert start
    public static void update(Student std) {
        Connection con = null;
        PreparedStatement statement = null;
        
        try {
            //TEST insert ban student vao CSDL
            //B1. Open connection toi database
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/C1907L", "root", "");
            
            //B2. tao 1 truy van toi CSDL - insert data vao CSDL
            String query = "update student set fullname = ?, address = ?, gender = ?, birthday = ? where rollno = ?";
            
            statement = con.prepareCall(query);
            
            statement.setString(1, std.getFullname());
            statement.setString(2, std.getAddress());
            statement.setString(3, std.getGender());
            statement.setString(4, std.getBirthday());
            statement.setString(5, std.getRollno());
            
            statement.execute();
        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //B3. Dong connection
            if(statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            
            if(con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }
    //insert end
    
    //insert start
    public static void insert(Student std) {
        Connection con = null;
        PreparedStatement statement = null;
        
        try {
            //TEST insert ban student vao CSDL
            //B1. Open connection toi database
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/C1907L", "root", "");
            
            //B2. tao 1 truy van toi CSDL - insert data vao CSDL
            String query = "insert into student(rollno, fullname, address, gender, birthday) values (?, ?, ?, ?, ?)";
            
            statement = con.prepareCall(query);
            
            statement.setString(1, std.getRollno());
            statement.setString(2, std.getFullname());
            statement.setString(3, std.getAddress());
            statement.setString(4, std.getGender());
            statement.setString(5, std.getBirthday());
            
            statement.execute();
        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //B3. Dong connection
            if(statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            
            if(con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }
    //insert end
}