Monday, 3 February 2020

Spring JPA: One to One Mapping: Using Foreign Key

In One-to-One mapping, tables are associated with each other based on exactly one matching row.

We can create One-to-One mapping in three approaches.
a.   Using a foreign key in either of the tables.
b.   Using third table to store mapping between these two entities
c.    Using Common Primary Key value in both the tables.

In this post, I am going to explain how to establish One-to-One mapping using foreign key constraint.

Let’s create two tables like below.
BANK_ACCOUNT_DETAILS
ACCOUNT_ID 
ACCOUNT_NUMBER 
ADDRESS 
BRANCH 
IFSC_CODE 











EMPLOYEES
EMP_ID 
FIRST_NAME 
LAST_NAME 
ACCOUNT_ID 









ACCOUNT_ID column of EMPLOYEES tables is a foreign key references to ACCOUNT_ID column of BANK_ACCOUNT_DETAILS  table.

SQL statements to get above data definition looks like below
create table bank_account_details (account_id integer not null, account_number varchar(255), address varchar(255), branch varchar(255), ifsc_code varchar(255), primary key (account_id))

create table employees (emp_id integer not null, first_name varchar(255), last_name varchar(255), account_id integer, primary key (emp_id))

alter table employees add constraint FKsjblkax5fjj3evmq8jswq7lrl foreign key (account_id) references bank_account_details

Entity classes defined like below.
@Entity
@Table(name = "employees")
public class Employee {
 @Id
 @GeneratedValue
 @Column(name = "emp_id")
 private int id;
 private String firstName;
 private String lastName;

 @OneToOne(cascade = CascadeType.ALL)
 @JoinColumn(name="account_id")
 @JsonManagedReference
 private SalaryAccount salaryAccount;

 ......
 ......
}

@Entity
@Table(name = "bank_account_details")
public class SalaryAccount {

 @Id
 @GeneratedValue
 @Column(name = "account_id")
 private int id;

 private String accountNumber;
 private String branch;
 private String ifscCode;

 private String address;

 @JsonBackReference
 @OneToOne(mappedBy = "salaryAccount")
 private Employee employee;

 ......
 ......
}


Find the below working application.

Employee.java    
package com.sample.app.entity;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;
import javax.persistence.Table;

import com.fasterxml.jackson.annotation.JsonManagedReference;

@Entity
@Table(name = "employees")
public class Employee {
 @Id
 @GeneratedValue
 @Column(name = "emp_id")
 private int id;
 private String firstName;
 private String lastName;

 @OneToOne(cascade = CascadeType.ALL)
 @JoinColumn(name="account_id")
 @JsonManagedReference
 private SalaryAccount salaryAccount;

 public int getId() {
  return id;
 }

 public void setId(int id) {
  this.id = id;
 }

 public String getFirstName() {
  return firstName;
 }

 public void setFirstName(String firstName) {
  this.firstName = firstName;
 }

 public String getLastName() {
  return lastName;
 }

 public void setLastName(String lastName) {
  this.lastName = lastName;
 }

 public SalaryAccount getSalaryAccount() {
  return salaryAccount;
 }

 public void setSalaryAccount(SalaryAccount salaryAccount) {
  this.salaryAccount = salaryAccount;
 }

}

SalaryAccount.java
package com.sample.app.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToOne;
import javax.persistence.Table;

import com.fasterxml.jackson.annotation.JsonBackReference;

@Entity
@Table(name = "bank_account_details")
public class SalaryAccount {

 @Id
 @GeneratedValue
 @Column(name = "account_id")
 private int id;

 private String accountNumber;
 private String branch;
 private String ifscCode;

 private String address;

 @JsonBackReference
 @OneToOne(mappedBy = "salaryAccount")
 private Employee employee;

 public String getAccountNumber() {
  return accountNumber;
 }

 public void setAccountNumber(String accountNumber) {
  this.accountNumber = accountNumber;
 }

 public String getBranch() {
  return branch;
 }

 public void setBranch(String branch) {
  this.branch = branch;
 }

 public String getIfscCode() {
  return ifscCode;
 }

 public void setIfscCode(String ifscCode) {
  this.ifscCode = ifscCode;
 }

 public String getAddress() {
  return address;
 }

 public void setAddress(String address) {
  this.address = address;
 }

 public Employee getEmployee() {
  return employee;
 }

 public void setEmployee(Employee employee) {
  this.employee = employee;
 }

 public int getId() {
  return id;
 }

 public void setId(int id) {
  this.id = id;
 }

}

EmployeeDto.java
package com.sample.app.dto;

public class EmployeeDto {
 private String firstName;
 private String lastName;
 private SalaryAccountDto salaryAccDto;

 public String getFirstName() {
  return firstName;
 }

 public void setFirstName(String firstName) {
  this.firstName = firstName;
 }

 public String getLastName() {
  return lastName;
 }

 public void setLastName(String lastName) {
  this.lastName = lastName;
 }

 public SalaryAccountDto getSalaryAccDto() {
  return salaryAccDto;
 }

 public void setSalaryAccDto(SalaryAccountDto salaryAccDto) {
  this.salaryAccDto = salaryAccDto;
 }

}

SalaryAccountDto.java
package com.sample.app.dto;

public class SalaryAccountDto {
 private String accountNumber;
 private String branch;
 private String ifscCode;
 private String address;

 public String getAccountNumber() {
  return accountNumber;
 }

 public void setAccountNumber(String accountNumber) {
  this.accountNumber = accountNumber;
 }

 public String getBranch() {
  return branch;
 }

 public void setBranch(String branch) {
  this.branch = branch;
 }

 public String getIfscCode() {
  return ifscCode;
 }

 public void setIfscCode(String ifscCode) {
  this.ifscCode = ifscCode;
 }

 public String getAddress() {
  return address;
 }

 public void setAddress(String address) {
  this.address = address;
 }

}

EmployeeRepository.java
package com.sample.app.repository;

import org.springframework.data.repository.CrudRepository;

import com.sample.app.entity.Employee;

public interface EmployeeRepository extends CrudRepository<Employee, Integer> {

}

SalaryAccountRepository.java
package com.sample.app.repository;

import org.springframework.data.repository.CrudRepository;

import com.sample.app.entity.SalaryAccount;

public interface SalaryAccountRepository extends CrudRepository<SalaryAccount, Integer> {

}

EmployeeService.java
package com.sample.app.service;

import com.sample.app.dto.EmployeeDto;
import com.sample.app.entity.Employee;

public interface EmployeeService {

 Employee save(EmployeeDto emp);

 Employee getById(int id);

}

SalaryAccountService.java
package com.sample.app.service;

import com.sample.app.entity.SalaryAccount;

public interface SalaryAccountService {
 
 SalaryAccount getAccount(int id);

}

EmployeeServiceImpl.java
package com.sample.app.service.impl;

import javax.transaction.Transactional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.sample.app.dto.EmployeeDto;
import com.sample.app.dto.SalaryAccountDto;
import com.sample.app.entity.Employee;
import com.sample.app.entity.SalaryAccount;
import com.sample.app.repository.EmployeeRepository;
import com.sample.app.service.EmployeeService;

@Service
public class EmployeeServiceImpl implements EmployeeService {

 @Autowired
 private EmployeeRepository empRepo;

 @Override
 @Transactional
 public Employee save(EmployeeDto empDto) {

  String firstName = empDto.getFirstName();

  String lastName = empDto.getLastName();

  Employee emp = new Employee();
  emp.setFirstName(firstName);
  emp.setLastName(lastName);

  SalaryAccountDto salaryAccDto = empDto.getSalaryAccDto();

  SalaryAccount salaryAccount = new SalaryAccount();

  salaryAccount.setAccountNumber(salaryAccDto.getAccountNumber());
  salaryAccount.setAddress(salaryAccDto.getAddress());
  salaryAccount.setBranch(salaryAccDto.getBranch());
  salaryAccount.setIfscCode(salaryAccDto.getIfscCode());

  emp.setSalaryAccount(salaryAccount);

  return empRepo.save(emp);
 }

 @Override
 public Employee getById(int id) {
  return empRepo.findById(id).get();
 }

}

SalaryAccountServiceImpl.java
package com.sample.app.service.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.sample.app.entity.SalaryAccount;
import com.sample.app.repository.SalaryAccountRepository;
import com.sample.app.service.SalaryAccountService;

@Service
public class SalaryAccountServiceImpl implements SalaryAccountService {

 @Autowired
 private SalaryAccountRepository salaryAccRepo;

 @Override
 public SalaryAccount getAccount(int id) {
  return salaryAccRepo.findById(id).get();
 }

}

EmployeeController.java
package com.sample.app.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

import com.sample.app.dto.EmployeeDto;
import com.sample.app.entity.Employee;
import com.sample.app.service.EmployeeService;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;

@RestController
@Api(tags = { "This section contains all Employee Speicifc Operations" })
public class EmployeeController {

 @Autowired
 private EmployeeService empService;

 @ApiOperation(value = "Create new Employee", notes = "Create new Employee")
 @PostMapping(value = "/employees", produces = MediaType.APPLICATION_JSON_VALUE)
 public ResponseEntity<Employee> saveEmployee(@RequestBody EmployeeDto empDto) {

  Employee persistedEmp = empService.save(empDto);

  return new ResponseEntity<>(persistedEmp, HttpStatus.CREATED);
 }

 @ApiOperation(value = "Get Employee", notes = "Get employee by id")
 @GetMapping(value = "/employees/{id}", produces = MediaType.APPLICATION_JSON_VALUE)
 public ResponseEntity<Employee> getEmployees(@PathVariable final Integer id) {

  Employee persistedEmp = empService.getById(id);

  return new ResponseEntity<>(persistedEmp, HttpStatus.CREATED);
 }

}

SalaryAccountController.java
package com.sample.app.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

import com.sample.app.entity.SalaryAccount;
import com.sample.app.service.SalaryAccountService;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;

@RestController
@Api(tags = { "This section contains all Account Speicifc Operations" })
public class SalaryAccountController {

 @Autowired
 private SalaryAccountService salaryAccService;

 @ApiOperation(value = "Get Account Details", notes = "Get Account Details by id")
 @GetMapping(value = "/accounts/{id}", produces = MediaType.APPLICATION_JSON_VALUE)
 public ResponseEntity<SalaryAccount> getAccount(@PathVariable final Integer id) {

  SalaryAccount salaryAcc = salaryAccService.getAccount(id);

  return new ResponseEntity<>(salaryAcc, HttpStatus.CREATED);
 }
}

SwaggerConfig.java
package com.sample.app.config;

import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.stereotype.Component;

import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

@Component
@EnableAutoConfiguration
@EnableSwagger2
public class SwaggerConfig {

 @Bean
 public Docket userApi() {

  return new Docket(DocumentationType.SWAGGER_2).apiInfo(apiInfo()).select().paths(PathSelectors.any())
    .apis(RequestHandlerSelectors.basePackage("com.sample.app.controller")).build();
 }

 private ApiInfo apiInfo() {

  return new ApiInfoBuilder().title("Query builder").description("Query builder using spring specification")
    .version("2.0").build();
 }
}

App.java
package com.sample.app;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class App {

 public static void main(String args[]) {
  SpringApplication.run(App.class, args);
 }

}

pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>
 <groupId>com.sample.app</groupId>
 <artifactId>manyToMany</artifactId>
 <version>1</version>

 <parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>2.1.6.RELEASE</version>
 </parent>

 <properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
 </properties>


 <dependencies>
  <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>

  <!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
  <dependency>
   <groupId>com.h2database</groupId>
   <artifactId>h2</artifactId>
  </dependency>

  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-web</artifactId>
  </dependency>

  <dependency>
   <groupId>io.springfox</groupId>
   <artifactId>springfox-swagger2</artifactId>
   <version>2.9.2</version>
  </dependency>


  <dependency>
   <groupId>io.springfox</groupId>
   <artifactId>springfox-swagger-ui</artifactId>
   <version>2.9.2</version>
  </dependency>

 </dependencies>
</project>

Create application.properties file under src/main/resources folder.

application.properties
logging.level.root=WARN
logging.level.org.hibernate=ERROR

## H2 specific properties
spring.h2.console.enabled=true
spring.h2.console.path=/h2

spring.datasource.url=jdbc:h2:file:~/db/myOrg.db;DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1;

spring.datasource.username=krishna
spring.datasource.password=password123

spring.datasource.driverClassName=org.h2.Driver

## JPA specific properties
# Creates the schema, destroying previous data.
spring.jpa.hibernate.ddl-auto=create

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

spring.jpa.show-sql=true
#spring.jpa.properties.hibernate.format_sql=true

## Database connection pooling properties
# Number of ms to wait before throwing an exception if no connection is available.
spring.datasource.max-wait=10000

# Maximum number of active connections that can be allocated from this pool at the same time.
spring.datasource.tomcat.max-active=10
spring.datasource.tomcat.max-idle=5
spring.datasource.tomcat.min-idle=3

spring.jackson.serialization.FAIL_ON_EMPTY_BEANS=false

Total project structure looks like below.




Run App.java.

Open swagger url 'http://localhost:8080/swagger-ui.html' in browser to interact with the REST services.

Open the url 'http://localhost:8080/h2/login.do' to see H2 console.

You can download complete working application from this link.




Previous                                                    Next                                                    Home

No comments:

Post a Comment