create table t1 (_id integer primary key autoincrement, dir text not null unique);

insert into t1 values (1, "/dir_1/subdir_1/item_1");
insert into t1 values (2, "/dir_1/subdir_1/item_2");
insert into t1 values (3, "/dir_1/subdir_2/item_1");
insert into t1 values (4, "/dir_1/item_1");
insert into t1 values (5, "/dir_2/item_1");

select * from t1 where dir like "/%" <magic>;
1|/dir_1/subdir_1/item_1
5|/dir_2/item_1

select * from t1 where dir like "/dir_1/% <magic>";
1|/dir_1/subdir_1/item_1
4|/dir_1/item_1

Pattern pattern = Pattern.compile("\\/([^\\/]+)\\/");
        String[] items = new String[] {
                "/dir_1/subdir_1/item_1",
                "/dir_1/subdir_1/item_2",
                "/dir_1/subdir_2/item_1",
                "/dir_1/item_1",
                "/dir_2/item_1"
        };

        Map<String, Integer> dirs = new HashMap<>();
        
        for (int i=0; i < items.length; i++) {
            Matcher matcher = pattern.matcher(items[i]);
            if (matcher.find()) dirs.put(matcher.group(1), i);
        }

        System.out.println("Directories: " + dirs);

create table t2 (_id integer primary key autoincrement, dir text not null, item text not null);
insert into t2 values (1, "/", "dir_1");
insert into t2 values (2, "/dir_1", "subdir_1");
insert into t2 values (3, "/dir_1/subdir_1", "item_1");
insert into t2 values (4, "/dir_1", "subdir_2");
insert into t2 values (5, "/dir_1/subdir_2", "item_1");
insert into t2 values (6, "/dir_1/subdir_1", "item_2");
insert into t2 values (7, "/dir_1", "item_1");
insert into t2 values (8, "/", "dir_2");
insert into t2 values (9, "/dir_2", "item_1");

select * from t2 where dir = "/";
1|/|dir_1
8|/|dir_2
select * from t2 where dir = "/dir_1";
2|/dir_1|subdir_1
4|/dir_1|subdir_2
7|/dir_1|item_1