|
3
|
1 package jp.ac.u_ryukyu.ie.cr;
|
|
|
2
|
|
|
3 import org.junit.Test;
|
|
|
4
|
|
|
5 import java.sql.*;
|
|
|
6
|
|
|
7 /**
|
|
4
|
8 * Created by Kazuma on 2016/12/01.
|
|
3
|
9 */
|
|
|
10 public class JDBCSqliteTest {
|
|
|
11 @Test
|
|
|
12 public void GetConnect () throws SQLException, ClassNotFoundException {
|
|
|
13 try {
|
|
|
14 Class.forName("org.sqlite.JDBC");
|
|
|
15
|
|
|
16 Connection conn = DriverManager.getConnection("jdbc:sqlite:C:/sqlite/database.db");
|
|
|
17
|
|
|
18 Statement stmt = conn.createStatement();
|
|
|
19
|
|
|
20 stmt.executeUpdate("drop table if exists user");
|
|
|
21 stmt.executeUpdate("drop table if exists type");
|
|
|
22
|
|
|
23 stmt.executeUpdate("create table user (id integer, name string, age integer, type_id integer, parent_id integer, FOREIGN KEY(parent_id) references user (id))");
|
|
|
24 stmt.executeUpdate("create table type (id integer, name string)");
|
|
|
25
|
|
|
26 stmt.executeUpdate("insert into type values(1, 'Teacher')");
|
|
|
27 stmt.executeUpdate("insert into type values(2, 'Student')");
|
|
|
28
|
|
|
29 stmt.executeUpdate("insert into user values(1, 'Kono', 40, 1, null)");
|
|
|
30 stmt.executeUpdate("insert into user values(2, 'Kanagawa', 30, 1, 1)");
|
|
|
31 stmt.executeUpdate("insert into user values(3, 'Takeda', 20, 2, 2)");
|
|
|
32 stmt.executeUpdate("insert into user values(4, 'Miyagi', 21, 2, 2)");
|
|
|
33 stmt.executeUpdate("insert into user values(5, 'Higashionna', 22, 2, 2)");
|
|
|
34
|
|
|
35 ResultSet get_user = stmt.executeQuery("select * from user where name=\'Kono\'");
|
|
|
36 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");
|
|
|
37
|
|
|
38 while (rs.next()){
|
|
|
39 System.out.println("User -> " + rs.getString("name"));
|
|
|
40 }
|
|
|
41
|
|
|
42 stmt.close();
|
|
|
43 conn.close();
|
|
|
44
|
|
|
45 } catch (ClassNotFoundException e) {
|
|
|
46 e.printStackTrace();
|
|
|
47 }
|
|
|
48 }
|
|
|
49 }
|
