Mercurial > hg > Members > kazuma > jungle-ormapper
changeset 6:233bb94ec43a
Add one sentence SQL..
author | Kazuna |
---|---|
date | Thu, 01 Dec 2016 15:28:04 +0900 |
parents | 25e515cb5a35 |
children | cd5f2ba0f894 |
files | .idea/vcs.xml build/classes/test/jp/ac/u_ryukyu/ie/cr/JDBCTest.class src/test/java/jp/ac/u_ryukyu/ie/cr/JDBCTest.java |
diffstat | 3 files changed, 40 insertions(+), 25 deletions(-) [+] |
line wrap: on
line diff
--- /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
--- 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(); }