package jp.ac.u_ryukyu.ie.cr;

import org.junit.Test;

import java.sql.*;

/**
 * Created by Kazuma on 2016/12/01.
 */
public class JDBCSqliteTest {
    @Test
    public void GetConnect () throws SQLException, ClassNotFoundException {
        try {
            Class.forName("org.sqlite.JDBC");

            /*
            - create db file on working directory. name ex. @windows -> C:/workingspace/test.db", @mac -> "jdbc:sqlite:workingspace/test.db
            - do not need create table and columns in Sqlite console.
             */
            Connection conn = DriverManager.getConnection("jdbc:sqlite:C:/sqlite/database.db");

            Statement stmt = conn.createStatement();

            stmt.executeUpdate("drop table if exists user");
            stmt.executeUpdate("drop table if exists type");

            stmt.executeUpdate("create table user (id integer, name string, age integer, type_id integer, parent_id integer, FOREIGN KEY(parent_id) references user (id))");
            stmt.executeUpdate("create table type (id integer, name string)");

            stmt.executeUpdate("insert into type values(1, 'Teacher')");
            stmt.executeUpdate("insert into type values(2, 'Student')");

            stmt.executeUpdate("insert into user values(1, 'Kono', 40, 1, null)");
            stmt.executeUpdate("insert into user values(2, 'Kanagawa', 30, 1, 1)");
            stmt.executeUpdate("insert into user values(3, 'Takeda', 20, 2, 2)");
            stmt.executeUpdate("insert into user values(4, 'Miyagi', 21, 2, 2)");
            stmt.executeUpdate("insert into user values(5, 'Higashionna', 22, 2, 2)");

            ResultSet get_user = stmt.executeQuery("select * from user where name=\'Kono\'");
            ResultSet rs = stmt.executeQuery("with recursive n as (select * from user where id="+ get_user.getInt("id") +" union all select user.* from user, n where user.parent_id = n.id) select * from n");

            // get all children from root.
            while (rs.next()){
                System.out.println("User ->  " + rs.getString("name"));
            }

            stmt.close();
            conn.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}