package jdbc;

import java.awt.BorderLayout;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

// create a users class
class Users{

    private int Id;
    private String Fname;
    private String Lname;
    private String Mname;
    private int Age;
	private String Gender;
	private String Address;
	private String Level;
	private String Section;
	private int Phone;

    public Users(int Stu_ID,String fname,String lname,String mname,int age,String gender, String address,String level,String section,int phone){
        this.Id = Stu_ID;
        this.Fname = fname;
        this.Lname = lname;
        this.Mname = mname;
        this.Age = age;
        this.Gender = gender;
        this.Address = address;
        this.Level = level;
        this.Section = section;
        this.Phone = phone;

    }

    public int getId(){
        return this.Id;
    }

    public String getFname(){
        return this.Fname;
    }

    public String getLname(){
        return this.Lname;
    }
    public String getMname(){
        return this.Mname;
    }
    public int getAge(){
        return this.Age;
    }
    public String getGender(){
        return this.Gender;
    }
    public String getAddress(){
        return this.Address;
    }
    public String getLevel(){
        return this.Level;
    }
    public String getSection(){
        return this.Section;
    }
    public int getPhone(){
        return this.Phone;
    }
}

public class Work extends JFrame {

    public Work(){

        super("Bind JTable From MySQL DataBase");

        setLocationRelativeTo(null);

        setSize(600,400);

        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

        setVisible(true);
    }

 // create a Function to get the connection
    static Connection getConnection(){
        Connection con = null;

        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost/test","root","");
        } catch (SQLException ex) {
            Logger.getLogger(Work.class.getName()).log(Level.SEVERE, null, ex);
        }

        return con;
    }

 // create a function to fill the an arraylist from database
    static ArrayList<Users> getUsers(){

        ArrayList<Users> users = new ArrayList<Users>();

        Connection con = getConnection();

        Statement st;

        ResultSet rs;

        Users u;

        try {

            st = con.createStatement();
            rs = st.executeQuery("SELECT * FROM student");

            while(rs.next()){


                u = new Users(
                        rs.getInt("Stu_ID"),
                        rs.getString("Stu_Fname"),
                        rs.getString("Stu_Lname"),
                        rs.getString("Stu_Mname"),
                        rs.getInt("Stu_Age"),
                        rs.getString("Stu_gender"),
                        rs.getString("Stu_Address"),
                        rs.getString("Stu_Level"),
                        rs.getString("Stu_Section"),
                        rs.getInt("Stu_phone")



                );

        }
        }
            catch (SQLException ex) {
            Logger.getLogger(Work.class.getName()).log(Level.SEVERE, null, ex);
        }

        return users;
    }


    public static void main(String[] args){

 /*
   now we are gonna create and populate a jtable from the arraylist who is populated from mysql database
*/

        JTable table = new JTable();

        DefaultTableModel model = new DefaultTableModel();

        Object[] columnsName = new Object[10];

        columnsName[0] = "Id";
        columnsName[1] = "Fname";
        columnsName[2] = "Lname";
        columnsName[3] = "Mname";
        columnsName[4] = "Age";
        columnsName[5] = "Gender";
        columnsName[6] = "Address";
        columnsName[7] = "Level";
        columnsName[8] = "Section";
        columnsName[9] = "Phone";


        model.setColumnIdentifiers(columnsName);

        Object[] rowData = new Object[10];

        for(int i = 0; i < getUsers().size(); i++){

        	   rowData[0] = getUsers().get(i).getId();
               rowData[1] = getUsers().get(i).getFname();
                rowData[2] = getUsers().get(i).getLname();
                 rowData[3] = getUsers().get(i).getMname();
                 rowData[4] = getUsers().get(i).getAge();
                 rowData[5] = getUsers().get(i).getGender();
                 rowData[6] = getUsers().get(i).getAddress();
                 rowData[7] = getUsers().get(i).getLevel();
                 rowData[8] = getUsers().get(i).getSection();
                 rowData[9] = getUsers().get(i).getPhone();

               model.addRow(rowData);
        }

        table.setModel(model);

//        System.out.println(getUsers().size());

        Work window = new Work();

        JPanel panel = new JPanel();

        panel.setLayout(new BorderLayout());

        JScrollPane pane = new JScrollPane(table);

        panel.add(pane,BorderLayout.CENTER);

        window.setContentPane(panel);
    }
}