[JPA] Compare table1 and table2 to get and update data that does not exist in table2.

This time, we prepared tables called Employee1 and Employee2 in the DB, Compare both to get / update id data that does not exist in Employee2

DB preparation

Employee1

Employee2

Entity class

● @Entity Declares that it is an Entity class ● @Table Specify the table name of the DB to be mapped ● Added to @id PK column ● @Column Specify the column name of the table to be mapped

Employee1

@Entity
@Table(name = "employee1")
@Getter
public class Employee1 {

	@Id
	@Column(name = "id")
	private Integer id;
	
	@Column(name = "first_name")
	private String firstName;
	
	@Column(name = "last_name")
	private String lastName;
	
	@Column(name = "height")
	private Integer height;
	
	@Column (name = "weight")
	private Integer weight;
	
	@Column (name = "skill_level")
	private Integer skillLevel;
	
	//Method to convert to Employee2
	public Employee2 toConvert() {
		return new Employee2(
				this.getId(),
				this.getFirstName(),
				this.getLastName(),
				this.getHeight(),
				this.getWeight(),
				this.getSkillLevel()
				) ;
	}
}

● @NoArgsConstructor Argumentless constructor (default constructor) is automatically generated ● @AllArgsConstructor Automatically generate constructor with arguments (all members)

Employee2

@Entity
@Table(name = "employee1")
@Getter
@NoArgsConstructor
@AllArgsConstructor //Employee1 method creates an instance of Employee2, so a constructor with arguments is required
public class Employee1 {
	
	@Id
	@Column(name = "id")
	private Integer id;
	
	@Column(name = "first_name")
	private String firstName;
	
	@Column(name = "last_name")
	private String lastName;
	
	@Column(name = "height")
	private Integer height;
	
	@Column (name = "weight")
	private Integer weight;
	
	@Column (name = "skill_level")
	private Integer skillLevel;
}

Repository class

● Repository class is an interface ● Inherit JpaRepository and pass Entity class as the first argument and PK type as the second argument ● Annotation given to the class that communicates with @Repository DB ● @Query You can define a query with any method name

Employee1Repository

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
	
	@Query(nativeQuery=true, value = " SELECT e.* FROM employee  e LEFT OUTER JOIN employee1"
	+ "  e1 ON e.id = e1.id WHERE e1.id IS NULL " )
	public List<Employee> notExist();

Employee2Repository

@Repository
public interface Employee1Repository extends JpaRepository<Employee1, Integer> {

}

Execution class

● @Autowired Assigned to an instance variable

Main

@SpringBootApplication
public class EmployeeApplication implements CommandLineRunner{
	
	@Autowired
	EmployeeRepository employeeRepository;
	
	@Autowired
	Employee1Repository employee1Repository;
	
	
	public static void main(String[] args) {
		SpringApplication.run(EmployeeApplication.class, args);
	}

	@Override
	public void run(String... args) throws Exception {
		
		List<Employee> list = employeeRepository.notExist();
		System.out.println(list.size());
		List<Employee1> list1 = list.stream().map(Employee::toConvert).collect(toList());
		employee1Repository.saveAll(list1);
	}
}