How to handle foreign keys relation in JOOQ

Question

I have to create a new record of an entity with some given properties.

Will be more like coping an existing record with some values replaced using JOOQ. I can do it for a single table, but I am finding right way to do it for related entities.

For example: The main table is Student and I need a new records of student of grade 10. I can fetch a records of student in grade 10 and change name and save it.

    Result<Record> result= context.select().from(Student.STUDENT).where(Student.STUDENT.GRADE.eq(studRecords.getGrade()))
                .fetch();
        StudentRecord appER=(StudentRecord ) result.get(0);
            Field<?>[] fields= Student.STUDENT.fields();
for (Field<?> field : fields)
            {
                //System.out.println(field.getName() + " = " +field.getType() + field.getDataType() + "  " + field.getBinding());
                DataType<Comparable> dt= (DataType<Comparable>) field.getDataType() ;
              //if(!dt.getSQLDataType().nullable()  && !dt.getSQLDataType().defaulted() && studRecords.getValue(field) ==null)
                if(studRecords.getValue(field) ==null)
              {  
                  if(studRecords.getTable().getPrimaryKey().getFields().contains(field)){
                      System.out.println("Primary key is null . New Record Adding");
                      continue;
                  }
                  if(t.getTable().getReferences().contains(field))
                      System.out.println("Foreign key");
                  Object obj=appER.getValue(field);
                    if(field.getType().equals(Integer.class))
                     {
                      studRecords.setValue((Field<Comparable>)field,(obj!=null?(Integer)obj :new Integer(0)));
                     }
                      if(field.getType().equals(Boolean.class)){
                          studRecords.setValue((Field<Comparable>)field,(obj!=null?(Boolean)obj :new Boolean(false)));
                      }
                      if(field.getType().equals(Byte.class)){
                          studRecords.setValue((Field<Comparable>)field,(obj!=null?(Byte)obj :new Byte((byte) 0)));
                      }
                      if(field.getType().getName().equals("java.lang.String")){
                          studRecords.setValue((Field<Comparable>)field,(obj!=null?(String)obj :new String("")));
                      }
                      if(field.getType().equals(Double.class)){
                          studRecords.setValue((Field<Comparable>)field,(obj!=null?(Double)obj :new Double(0.0)));
                      }
                      if(field.getType().equals(Long.class)){
                          studRecords.setValue((Field<Comparable>)field,(obj!=null?(Long)obj :new Long(0)));
                      }
              }
            }

But now I also want to create related object like address, hobbies which are related tables.

What would be the best way to do it?

Answer 1

If I understand you correctly, most of your code sample looks fairly unnecessary. To update the student names you could do something more like this:

final List<StudentRecord> students = context.selectFrom(Student.STUDENT)
       .where(Student.STUDENT.GRADE.eq(10))
       .fetchInto(StudentRecord.class);
for (final StudentRecord student : students) {
    student.setName("some other name");
}
context.batchStore(students).execute();

Re adding other tables, you should be able to do it the same way you added the student table. If your database looked something along these lines, where a student has one address but may have many hobbies:

create table address(
    id serial primary key,
    street text not null
    -- other fields, etc
); 
create table student(
    id serial primary key,
    name text not null,
    grade int not null,
    address_id not null references address(id)
    -- ...
);
create table hobby(
    id serial primary key,
    student_id not null references student(id),
    name text
    -- ...
); 

And assuming you have it in the same schema and jOOQ is already picking up your student table, your hobby and address tables will also be picked up. You can then fetch then as you would in regular sql:

final Result<Record<Integer,String,String,String>> ret = context
        .select(Student.STUDENT.ID,
                Student.STUDENT.NAME,
                Address.ADDRESS.STREET,
                Hobby.HOBBY.NAME)
        .from(Student.STUDENT)
        .join(Hobby.HOBBY)
        .on(Student.STUDENT.ID.eq(Hobby.HOBBY.STUDENT_ID))
        .join(Address.ADDRESS)
        .on(Student.STUDENT.ADDRESS_ID.eq(Address.ADDRESS.ID))
        .where(Student.STUDENT.GRADE.eq(10))
        .orderBy(Student.STUDENT.NAME.asc())
        .fetch();
Integer lastStudentId = null;
for (final Record<Integer,String,String,String> val : ret) {
    final Integer studentId = val.getValue(Student.STUDENT.ID,);
    if (!studentId.equals(lastStudentId)) {
        System.out.println("Student " + val.getValue(Student.STUDENT.NAME)
                + " who lives on " + val.getValue(Address.ADDRESS.STREET)
                + " street has hobbies:"
        );
    }
    lastStudentId = studentId;
    System.out.println("\t" + val.getValue(Hobby.HOBBY.NAME));
}

If your question was more regarding the design of your database (i.e. should your addresses be individual and have their own pk or should they be owned by the student and have the same id as the student, that sort of thing), then I think that is less a jOOQ question and may be more suited to posting here: https://dba.stackexchange.com/?tags=database-design.