Mercurial > hg > Members > kazuma > jungle-ormapper
changeset 2:aebf5df61545
Add SQLite3 sample code.
author | Kazuma |
---|---|
date | Thu, 01 Dec 2016 03:37:29 +0900 |
parents | afbd14d7c981 |
children | c191aabf698f |
files | build/classes/main/jp/ac/u_ryukyu/ie/cr/jungle/bbs/EditMessageServlet.class 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, 37 insertions(+), 23 deletions(-) [+] |
line wrap: on
line diff
--- a/src/test/java/jp/ac/u_ryukyu/ie/cr/JDBCTest.java Wed Nov 30 02:07:48 2016 +0900 +++ b/src/test/java/jp/ac/u_ryukyu/ie/cr/JDBCTest.java Thu Dec 01 03:37:29 2016 +0900 @@ -1,5 +1,6 @@ package jp.ac.u_ryukyu.ie.cr; import org.junit.Test; +import org.junit.internal.matchers.StringContains; import java.sql.*; @@ -15,34 +16,47 @@ Statement stmt = conn.createStatement(); - // INSERT - stmt.executeUpdate("INSERT INTO type (name) VALUES ('Teacher')"); - stmt.executeUpdate("INSERT INTO type (name) VALUES ('Student')"); - - stmt.executeUpdate("INSERT INTO user (name, age) VALUES ('Kono', 30)"); - stmt.executeUpdate("INSERT INTO user (name, age) VALUES ('Takeda', 20)"); - stmt.executeUpdate("INSERT INTO user (name, age) VALUES ('Miyagi', 21)"); - stmt.executeUpdate("INSERT INTO user (name, age) VALUES ('Higashionna', 22)"); - - stmt.executeUpdate("INSERT INTO college (user_id, type_id) VALUES (1, 1)"); - stmt.executeUpdate("INSERT INTO college (user_id, type_id) VALUES (2, 2)"); - stmt.executeUpdate("INSERT INTO college (user_id, type_id) VALUES (3, 2)"); - stmt.executeUpdate("INSERT INTO college (user_id, type_id) VALUES (4, 2)"); + // init + stmt.executeUpdate("delete from user"); + stmt.executeUpdate("delete from type"); + stmt.executeUpdate("delete from organize"); - // SELECT - ResultSet set = stmt.executeQuery("SELECT * FROM type WHERE name=\"Teacher\""); - int type_id = 0; - while (set.next()){ - type_id = set.getInt(1); + // 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++; } - ResultSet rset = stmt.executeQuery("SELECT name, age FROM college JOIN user on user_id=user.id where type_id=" + type_id); + set_root_children.close(); - while ( rset.next() ) { - System.out.println(rset.getString(1) + "\t" + rset.getString(2)); + // 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"); + } } - - // rset.close(); stmt.close(); conn.close(); }