import java.sql.*;
import javax.swing.*;
import java.awt.Font;

/**
 * Modify and query MySQL database
 *
 */
public class Coffees {
    public Coffees() {   
        String url = "jdbc:mysql://localhost/test?user=root&password=root&useUnicode=true&characterEncoding=utf8";
               
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection(url);
            Statement stmt = con.createStatement();
            
            String createString = "DROP TABLE IF EXISTS Coffees";
            stmt.executeUpdate(createString); 
            
            createString = "CREATE TABLE Coffees (CoffeeName NVARCHAR(32), Price INTEGER, Location NCHAR(20))";
            stmt.executeUpdate(createString);            
        
            stmt.executeUpdate("INSERT INTO Coffees VALUES(N'Java Thượng Hạng', 5000, N'Thượng Nguồn')");
            stmt.executeUpdate("INSERT INTO Coffees VALUES(N'Café Trung Nguyễn', 7000, N'Đà Lạt')");            
            stmt.executeUpdate("INSERT INTO Coffees VALUES(N'Ảo Ảnh Cốc', 10000, N'Đắc Lắc')");            
            
            String update = "UPDATE Coffees SET Location=N'Lâm Đồng' WHERE Location=N'Thượng Nguồn'";
            stmt.execute(update);
            
            String query = "SELECT CoffeeName AS 'Thức uống', CONCAT(CAST(Price AS CHAR(8)), N' ₫') AS 'Giá cả', Location AS 'Địa điểm' FROM Coffees";    
            ResultSet rs = stmt.executeQuery(query);      
            displayResult(rs, "Thực Đơn");
            
            rs.close();
            stmt.close();
            con.close();      
        } catch(Exception exc) {
            System.err.println(exc.getMessage());
        }
    }
    
    /** 
     * Shows resultset 
     */
    void displayResult(ResultSet rs, String tableName) {
        ResultsModel model = new ResultsModel();     // Create a table model
        model.setResultSet(rs);
        JTable table = new JTable(model);            // Create a table from the model
        table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);   // Use scrollbars
        Font font = table.getTableHeader().getFont().deriveFont(Font.BOLD);
        table.getTableHeader().setFont(font); // Bold header font
        
        JFrame jf = new JFrame(tableName);
        jf.setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        jf.setSize(300, 200);
        JTextArea jt = new JTextArea();
        jf.getContentPane().add(new JScrollPane(table));        
        jf.setVisible(true);
    }
    
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        new Coffees();
    }
}