Thư viên JDBC MySQL Driver

Chương trình quản lý khách hàng
	- Lấy danh sách khách hàng
	- Thêm/sửa/xoá khách hàng.

B1. Tạo project
B2. Nhúng thư viện jdbc mysql driver -> maven
B3. Phát triển dự án
- Mapping tables <-> class object trong project
- DAO -> tuong tac database -> lay du lieu, them, sua, xoa

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

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(CustomerDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    static void closeConnection() {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException ex) {
                Logger.getLogger(CustomerDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(CustomerDAO.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 lesson07;

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

#Customer.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 lesson07;

import java.util.Scanner;

/**
 *
 * @author Diep.Tran
 */
public class Customer {
    int id;
    String fullname, email, phoneNumber, birthday, address, password;

    public Customer() {
    }

    public Customer(int id, String fullname, String email, String phoneNumber, String birthday, String address, String password) {
        this.id = id;
        this.fullname = fullname;
        this.email = email;
        this.phoneNumber = phoneNumber;
        this.birthday = birthday;
        this.address = address;
        this.password = password;
    }

    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 getPhoneNumber() {
        return phoneNumber;
    }

    public void setPhoneNumber(String phoneNumber) {
        this.phoneNumber = phoneNumber;
    }

    public String getBirthday() {
        return birthday;
    }

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

    public String getAddress() {
        return address;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "Customer{" + "id=" + id + ", fullname=" + fullname + ", email=" + email + ", phoneNumber=" + phoneNumber + ", birthday=" + birthday + ", address=" + address + ", password=" + password + '}';
    }
    
    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();
        
        System.out.println("Nhap SDT: ");
        phoneNumber = scan.nextLine();
        
        System.out.println("Nhap ngay sinh: ");
        birthday = scan.nextLine();
        
        System.out.println("Nhap dia chi: ");
        address = scan.nextLine();
        
        System.out.println("Nhap mat khau: ");
        password = scan.nextLine();
    }
}

#CustomerDAO.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 lesson07;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Diep.Tran
 */
public class CustomerDAO extends BaseDAO{
    public static List<Customer> getCustomerList() {
        openConnection();
        
        List<Customer> dataList = new ArrayList<>();

        try {
            String sql = "select * from customer";
            statement = conn.prepareStatement(sql);

            ResultSet resultSet = statement.executeQuery();

            while (resultSet.next()) {
                Customer c = new Customer(
                        resultSet.getInt("id"),
                        resultSet.getString("fullname"),
                        resultSet.getString("email"),
                        resultSet.getString("phone_number"),
                        resultSet.getString("birthday"),
                        resultSet.getString("address"),
                        resultSet.getString("password")
                );
                dataList.add(c);
            }
        } catch (SQLException e) {
        }
        closeConnection();
        return dataList;
    }
    
    public static void insert(Customer customer) {
        openConnection();
        
        try {
            String sql = "insert into customer(fullname, email, phone_number, birthday, address, password) "
                    + "values(?, ?, ?, ?, ?, ?)";
            statement = conn.prepareStatement(sql);
            statement.setString(1, customer.getFullname());
            statement.setString(2, customer.getEmail());
            statement.setString(3, customer.getPhoneNumber());
            statement.setString(4, customer.getBirthday());
            statement.setString(5, customer.getAddress());
            statement.setString(6, customer.getPassword());
            
            statement.execute();
        } catch (SQLException e) {
        }
        closeConnection();
    }
    
    public static void update(Customer customer) {
        openConnection();
        
        try {
            String sql = "update customer set fullname = ?, email = ?, phone_number = ?, birthday = ?, address = ?, password = ? where id = ?";
            statement = conn.prepareStatement(sql);
            statement.setString(1, customer.getFullname());
            statement.setString(2, customer.getEmail());
            statement.setString(3, customer.getPhoneNumber());
            statement.setString(4, customer.getBirthday());
            statement.setString(5, customer.getAddress());
            statement.setString(6, customer.getPassword());
            statement.setInt(7, customer.getId());
            
            statement.execute();
        } catch (SQLException e) {
        }
        closeConnection();
    }
    
    public static void delete(int id) {
        openConnection();
        
        try {
            String sql = "delete from customer where id = ?";
            statement = conn.prepareStatement(sql);
            statement.setInt(1, id);
            
            statement.execute();
        } catch (SQLException e) {
        }
        closeConnection();
    }
}

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

import java.util.List;

/**
 *
 * @author Diep.Tran
 */
public class Main {
    public static void main(String[] args) {
        List<Customer> dataList = CustomerDAO.getCustomerList();
        for (Customer customer : dataList) {
            System.out.println(customer);
        }
        
        //INSERT
//        System.out.println("Nhap du lieu vao database: ");
//        Customer customer = new Customer();
//        customer.input();
//        CustomerDAO.insert(customer);
        //UPDATE
//        System.out.println("Sua thong tin khach hang");
//        Customer c = dataList.get(0);
//        c.input();
//        CustomerDAO.update(c);
        //DELETE
//        CustomerDAO.delete(dataList.get(0).getId());
//        System.out.println("Xoa thanh cong");
    }
}