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();
        }
    }
}