How to do one to many relationships in JDBC?

I have class two classes
i want to perform one to many relationship

public class Teacher {
private int id;
private String name;
private List<Class> classes;
 }

public class Class {
private int id;
private String className;
}

I need to retrieve data and print data from the database

<pre>Teacher Classes<br>
Kumar A1,B3,B4<br>
Deepa A1,A2,C1<br>
Alex B2,D1,D2<br></pre>

I don't Know how to retrive one to many relationship data in JDBC <br>
please suggest me what should i do?

I do know about 2 ways to do it with jdbc

1. You need to select all teachers first, then for each teacher select his classes.

   select * from teacher

then map results to your teacher class then on java side make a for each loop and fetch class by teacher_id for each teacher

select * from class where teacher_id = :teacher_id

2. You can select all teachers and classes like that:

   select * from teacher t
   left join class c on c.teacher_id = t.teacher_id

but you will get duplicate data of teacher becouse in each row you will fetch data for teacher also and you will need to organize it on Java side.

So I've been working recently with Spring JDBC myself. This is something similar with what I've come up with by reading here and there. I really appreciate the simplicity of JDBC combined with the domain driven approach:

public class Teacher {
    @Id private int id;
    private String name;
    @MappedCollection(idColumn = "teacher_id", keyColumn = "teacher_key")
    private List<Class> classes;
     
    //setters and getters
}

public class Class {
    private int id;
    private String className;
    
    //setters and getters
}

Then you need to create the repository interface by extending CrudRepository from Spring JDBC, you don't need to create an extra repository to e.g. save Class data through Teacher :

interface TeacherRepository extends CrudRepository<Teacher, Integer> {}

The TeacherRepository will have the typical CRUD methods that allow you to read or write data from/to the database.

The sql code (this is postgres like, change to your specific dialect) would be something like this:

create table teachers(
    id serial primary key,
    name text
 )

 create table classes(
    id serial primary key,
    class_name text,
    teacher_id int references teachers(id),
    teacher_key int
 )

Where the teacher_key column is used to return an ordered list of classes. If you don't care about the classes ordering you can just return a set instead of a list:

public class Teacher {
    @Id private int id;
    private String name;
    @MappedCollection(idColumn = "teacher_id")
    private Set<Class> classes;
     
    //setters and getters
}

public class Class {
    private int id;
    private String className;
    
    //setters and getters
}

the sql code:

create table teachers(
    id serial primary key,
    name text
 )

 create table classes(
    id serial primary key,
    class_name text,
    teacher_id int references teachers(id)
 )

Here are my sources:

• https://www.youtube.com/watch?v=ccxBXDAPdmo
• https://spring.io/blog/2018/09/24/spring-data-jdbc-references-and-aggregates
• https://lumberjackdev.com/spring-data-jdbc
• https://docs.spring.io/spring-data/jdbc/docs/current/reference/html/#jdbc.repositories