package jp.ac.u_ryukyu.ie.cr;
import org.junit.Test;

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

          // 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)");


            // SELECT
            ResultSet set = stmt.executeQuery("SELECT * FROM type WHERE name=\"Teacher\"");
            int type_id = 0;
            while (set.next()){
                type_id = set.getInt(1);
            }
            ResultSet rset = stmt.executeQuery("SELECT name, age FROM college JOIN user on user_id=user.id where type_id=" + type_id);

            while ( rset.next() ) {
                System.out.println(rset.getString(1) + "\t" + rset.getString(2));
            }

            // rset.close();
            stmt.close();
            conn.close();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}