package jp.ac.u_ryukyu.ie.cr;
import org.junit.Test;
import org.junit.internal.matchers.StringContains;

import java.sql.*;

/**
 * Created by e135768K on 2016/11/29.
 */
public class JDBCTest {
    @Test
    public void GetTest () {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/labo", "root", "1128kazu");

            Statement stmt = conn.createStatement();

            // init
            stmt.executeUpdate("delete from user");
            stmt.executeUpdate("delete from type");
            stmt.executeUpdate("delete from organize");


             // INSERT
            // Change to no use AUTO INCREMENTE.
            stmt.executeUpdate("INSERT INTO type (id, name) VALUES (1, 'Teacher')");
            stmt.executeUpdate("INSERT INTO type (id, name) VALUES (2, 'Student')");

            stmt.executeUpdate("INSERT INTO user (id, name, age, type_id) VALUES (1,'Kono', 30, 1)");
            stmt.executeUpdate("INSERT INTO user (id, name, age, type_id) VALUES (2, 'Kanagawa', 25, 1)");

            stmt.executeUpdate("INSERT INTO user (id, name, age, type_id) VALUES (3, 'Takeda', 20, 2)");
            stmt.executeUpdate("INSERT INTO user (id, name, age, type_id) VALUES (4, 'Miyagi', 21, 2)");
            stmt.executeUpdate("INSERT INTO user (id, name, age, type_id) VALUES (5, 'Higashionna', 22, 2)");

            stmt.executeUpdate("INSERT INTO organize (id, boss_id, child_id) VALUES (1, 1, 2)");
            stmt.executeUpdate("INSERT INTO organize (id, boss_id, child_id) VALUES (2, 2, 3)");
            stmt.executeUpdate("INSERT INTO organize (id, boss_id, child_id) VALUES (3, 2, 4)");
            stmt.executeUpdate("INSERT INTO organize (id, boss_id, child_id) VALUES (4, 2, 5)");

            // Find Children
            String[] child_name = new String[5];
            ResultSet set_root_children = stmt.executeQuery("select * from user where id in (select child_id from organize where boss_id in (select id from user where name=\"Kono\"))");
            int i = 0;
            while (set_root_children.next()) {
                child_name[i] = set_root_children.getString(2);
                System.out.printf("User -> " + set_root_children.getString(2) + "\n");
                i++;
            }
            set_root_children.close();

            // Next Children
            for(String name : child_name) {
                ResultSet set_child_children = stmt.executeQuery("select * from user where id in (select child_id from organize where boss_id in (select id from user where name=\'" + name +"\'))");
                while (set_child_children.next()) {
                    System.out.printf("User -> " + set_child_children.getString(2) + "\n");
                }
            }
            stmt.close();
            conn.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}