package xxglxt;

import java.sql.*;

import javax.swing.JTree;
import javax.swing.tree.DefaultMutableTreeNode;

public class SelectFrame {

    String strtemp = ""; //用來存放暫時的字符串
    int inttemp = 0; //用來存放暫時的數字
    ResultSet result=null; //保存結果集
    Connection con=null; //連接
    PreparedStatement pre=null; //SQL語句處理
    String sql = ""; //SQL語句
    DefaultMutableTreeNode[] grade;
    DefaultMutableTreeNode[] classname;
    String gradename;
    int inttemp3=0;


    public SelectFrame() {


    }

    public JTree jtree() {

        DefaultMutableTreeNode root = new DefaultMutableTreeNode("學校"); //根目錄為“學校”


        try {
            new sun.jdbc.odbc.JdbcOdbcDriver();
            con = DriverManager.getConnection("jdbc:odbc:glxt");
            sql = "select count(Grade_name) from gradetable";
            pre = con.prepareStatement(sql);
            result = pre.executeQuery();
            if (result.next()) {
                inttemp = result.getInt(1);
                System.out.println("年級" + inttemp);
                grade = new DefaultMutableTreeNode[inttemp];
            }

        inttemp=0;
            result.close();
            pre.close();
            con.close();
        } catch (SQLException ex1) {
            ex1.printStackTrace();
        }

         sql = "select Grade_name,Grade_id  from gradetable";
        try {
            new sun.jdbc.odbc.JdbcOdbcDriver();
            con = DriverManager.getConnection("jdbc:odbc:glxt");
            pre = con.prepareStatement(sql);
            result = pre.executeQuery();
            while (result.next()) {
                int inttemp6=0;
                gradename=result.getString(1);
                grade[inttemp] = new DefaultMutableTreeNode(gradename);
                int inttemp2=result.getInt(2);
                {
                    try {
            new sun.jdbc.odbc.JdbcOdbcDriver();
            Connection con1 = DriverManager.getConnection("jdbc:odbc:glxt");
            sql = "select count(class_name) from classtable where Grade_id=?";
            PreparedStatement pre1 = con1.prepareStatement(sql);
            pre1.setInt(1,inttemp2);
           ResultSet result1 = pre1.executeQuery();

            if (result1.next()) {
                inttemp3 = result1.getInt(1);
                System.out.println("班級" + inttemp3);
                classname = new DefaultMutableTreeNode[inttemp3];
            }
            inttemp3=0;
            result1.close();
            pre1.close();
            con1.close();


            sql="select class_name from classtable where grade_id="+inttemp2;
            System.out.println(sql);
            con1 = DriverManager.getConnection("jdbc:odbc:glxt");
            pre1 = con1.prepareStatement(sql);
            result1 = pre1.executeQuery();
           int inttemp4=0;
            while (result1.next()) {

            classname[inttemp4] = new DefaultMutableTreeNode(result1.getString(1));
            grade[inttemp6].add(classname[inttemp4]);
            inttemp4++;
        }


           result1.close();
           pre1.close();
           con1.close();






        } catch (SQLException ex1) {
            ex1.printStackTrace();
        }






                }



                root.add(grade[inttemp]);
                inttemp++;}
                result.close();
                pre.close();
                con.close();
            }catch (SQLException ex2) {
                ex2.printStackTrace();
            }
           JTree jtree2 = new JTree(root);
        return jtree2;


    }


}