Mercurial > hg > Members > kazuma > jungle-ormapper
view src/test/java/jp/ac/u_ryukyu/ie/cr/JDBCTest.java @ 6:233bb94ec43a
Add one sentence SQL..
author | Kazuna |
---|---|
date | Thu, 01 Dec 2016 15:28:04 +0900 |
parents | aebf5df61545 |
children |
line wrap: on
line source
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, 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)"); // 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)"); // 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++; // } // 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)); } stmt.close(); conn.close(); } catch (Exception e) { e.printStackTrace(); } } }