# HG changeset patch
# User Kazuna
# Date 1480573684 -32400
# Node ID 233bb94ec43a4cc6816d614d7d5207cda466baf1
# Parent  25e515cb5a35076ea16d97a25151a904729898b4
Add one sentence SQL..

diff -r 25e515cb5a35 -r 233bb94ec43a .idea/vcs.xml
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/.idea/vcs.xml	Thu Dec 01 15:28:04 2016 +0900
@@ -0,0 +1,6 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<project version="4">
+  <component name="VcsDirectoryMappings">
+    <mapping directory="$PROJECT_DIR$" vcs="hg4idea" />
+  </component>
+</project>
\ No newline at end of file
diff -r 25e515cb5a35 -r 233bb94ec43a build/classes/test/jp/ac/u_ryukyu/ie/cr/JDBCTest.class
Binary file build/classes/test/jp/ac/u_ryukyu/ie/cr/JDBCTest.class has changed
diff -r 25e515cb5a35 -r 233bb94ec43a src/test/java/jp/ac/u_ryukyu/ie/cr/JDBCTest.java
--- a/src/test/java/jp/ac/u_ryukyu/ie/cr/JDBCTest.java	Thu Dec 01 03:54:30 2016 +0900
+++ b/src/test/java/jp/ac/u_ryukyu/ie/cr/JDBCTest.java	Thu Dec 01 15:28:04 2016 +0900
@@ -27,36 +27,45 @@
             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, parent_id) VALUES (1,'Kono', 30, 1, null)");
+            stmt.executeUpdate("INSERT INTO user (id, name, age, type_id, parent_id) VALUES (2, 'Kanagawa', 25, 1, 1)");
+
+            stmt.executeUpdate("INSERT INTO user (id, name, age, type_id, parent_id) VALUES (3, 'Takeda', 20, 2, 2)");
+            stmt.executeUpdate("INSERT INTO user (id, name, age, type_id, parent_id) VALUES (4, 'Miyagi', 21, 2, 2)");
+            stmt.executeUpdate("INSERT INTO user (id, name, age, type_id, parent_id) VALUES (5, 'Higashionna', 22, 2, 2)");
 
-            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)");
+            // if you want to write as object, change column.
+//            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)");
 
-            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)");
-
+            // if you want to write as object, use this code.
             // 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++;
+//            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");
+//                }
+//            }
+
+            // if you want to write one sql sentence, use this code.
+            ResultSet set = stmt.executeQuery("select * from user where parent_id in (select id from user where parent_id = (select id from user where name = \"Kono\" AND id = (select id from type where name = \"Teacher\"))) union select * from user where parent_id = (select id from user where name = \"Kono\" AND id in (select id from type where name = \"Teacher\"))");
+            while (set.next()) {
+                System.out.println("user -> " + set.getString(2));
             }
-            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();
         }