[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

id first_name last_name height weight skill_level
1 TARO TANAKA 170 50 2
2 JIRO YAMADA 150 60 3
3 HANAKO YAMASHITA 156 44 3
4 ICHIRO SUZUKI 180 80 5
5 SABURO TANAKA 174 65 1
7 AI SUZUKI 160 50 4
8 GORO AIDA 177 60 3

Employee2

id first_name last_name height weight skill_level
6 Tomohisa Yamashita 178 67 6
7 Sadao Abe 160 56 5
8 Kaoru Sugita 166 60 2
9 Tomoya Nagase 185 70 6
10 Satoshi Satoshi Tsumabuki 180 70 6
11 Humility Watanabe 180 75 6
12 Yosuke Kubozuka 183 60 6
13 Ai Kato 166 50 4

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);
	}
}

Recommended Posts

[JPA] Compare table1 and table2 to get and update data that does not exist in table2.
Handling when calling a key that does not exist in hash
How to get and add data from Firebase Firestore in Ruby
What to do when "relation" hibernate_sequence "does not exist" in the ID column of PostgreSQL + JPA
How to get date data in Ruby
Processing when an ID that does not exist in the database is entered in the URL
MockMVC returns 200 even if I make a request to a path that does not exist
About the "Oops VFS connection does not exist" error and solution in AWS Cloud9
How to clear all data in a particular table
I tried to get started with Spring Data JPA
How to store Rakuten API data in a table
What to do if you select a JRE in Eclipse and get "The selected JRE does not support the current compliance level 11"
Solved the problem that all the data in the table was displayed
Run raw sql in Hanami to get results not in columns
Create API to send and receive Json data in Spring
How to get the log when install4j does not start