Let's consider the following example, I need to join the 3 tables data and insert/update/delete into single table whenever changes happening into the any one of the single table.
Previously I wrote one join query to combine 6 tables data and truncate the existing table(student_info_table) data before inserting the records(student_info_table).
3 tables join:
select s.id,s.name,sc.school_name,sc.address from student s inner join student_school_mapping ssm on s.id = ssm.student_id inner join school sc on sc.id=ssm.school_id;
Single table
create table student_info_table(id number, student_name varchar, school_name varchar, school_addr varchar)
For example I want to insert a record into student table, after successful insertion doing the following ones,
Calling the above join query and prepare the list of studentinfo entity results
studentInfoRepo.deleteAll();
studentInfoRepo.saveAll(List<StudentInfo>)
This approach is giving performance bottleneck to my spring boot app.
I am trying to achieve this using Debezium connector. but I dont see any option to combine all the tables and insert into single table.
I saw these options in Debezium, but these are individual tables CDC is happening
"table.whitelist": "public.student,public.student1"
Is there any standard mechanisms in Debezium to handle multiple table joins CDC ?
Can you suggest me if any other way is having to handle this type of use case ?