MySql Self-Referencing in SpringBoot

主要是树状机构，会有自己引用自己，叫Self-Referencing Foreign Key。

比如，大家都是员工，但有的员工是其他员工的主管。又比如，分类Category，有母类和子类。我是在建国家Country，纠结怎么储存到Mysql数据库中。

因为Country有parent，是Continent，还有children，即各个省。每个省又有children，这是一个数据结构。觉得在mysql中就是self-referencing的一个问题。

就有了这么例子，给大家感受下，代码量很少。

Employee.java

@Getter
@Setter
@NoArgsConstructor
@Entity
@Table(name="EMPLOYEE")
public class Employee {

    @Id
    @Column(name="EMPLOYEE_ID")
    @GeneratedValue
    private Long employeeId;

    @Column(name="FIRSTNAME")
    private String firstname;

    @Column(name="LASTNAME")
    private String lastname;

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name="manager_id")
    private Employee manager;

    @OneToMany(mappedBy="manager", cascade = CascadeType.ALL)
    private Set<Employee> subordinates = new HashSet<Employee>();


    public Employee(String firstname, String lastname) {
        this.firstname = firstname;
        this.lastname = lastname;
    }


    public void setManager(Employee manager) {
        this.manager = manager;
        manager.getSubordinates().add(this);
    }
}

EmployeeRepository.java

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    Optional<Employee> findByFirstname(String firstname);

}

EmployeeRepositoryTest.java

@RunWith(SpringRunner.class)
@SpringBootTest
public class EmployeeRepositoryTest {

    @Autowired
    EmployeeRepository repository;


    @Before
    public void init() {
        repository.deleteAll();
    }

    @Test
    public void test_employee() {
        Employee manager1 = new Employee("Chuck", "Norris");

        Employee employee1 = new Employee("Sergey", "Brin");
        Employee employee2 = new Employee("Larry", "Page");

        employee1.setManager(manager1);
        employee2.setManager(manager1);

        Employee e1 = repository.save(employee1);
        Employee e2 = repository.save(employee2);

        assertNotNull(e1);
        assertNotNull(e2);

        assertEquals(repository.findAll().size(), 3);

        Employee e = repository.findByFirstname("Larry").get();
        assertEquals(e.getFirstname(), "Larry");
    }


}

application.properties

##################
# Mysql
##################
spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/test
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver
spring.jpa.show-sql= false

还有一个问题，就是这种结构mongodb和mysql哪个存储更好？因为数据内容相对固定。你们觉得呢？

完整最新的blog在这里Self-refencing Mysql OneToMany + SpringBoot – jobyme88

如果有什么问题，欢迎指出，谢谢！