November 4, 2024

Spring Boot REST API, Data JPA, One-to-Many/Many-To-One Bidirectional Example

In this tutorial we'll see how to create a Spring Boot REST API CRUD example with Spring data JPA (Hibernate) and entities having One-To-Many, Many-To-One bidirectional association. Database used is MySQL.

Database Tables

Tables used for the example are Customer and Account. Queries for creating tables are given below. A customer can have many accounts which means a One-To-Many association between Customer and Account. That is done by having the customer_id as foreign key in Account table.

I want the account number to start from 1000 so that is given as the initial value of auto_increment in Account table.

CREATE TABLE `customer` (
  `customer_id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `mobile_number` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `created_at` datetime(6) DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `updated_on` datetime(6) DEFAULT NULL,
  `updated_by` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`customer_id`),
  UNIQUE KEY `UK5v8hijx47m783qo8i4sox2n5t` (`mobile_number`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE `account` (
  `account_number` bigint NOT NULL AUTO_INCREMENT,
  `branch_address` varchar(255) DEFAULT NULL,
  `account_type` enum('CHECKING','SALARY','SAVINGS') DEFAULT NULL,
  `customer_id` bigint NOT NULL,
  `created_at` datetime(6) DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `updated_on` datetime(6) DEFAULT NULL,
  `updated_by` varchar(255) DEFAULT NULL,  
  PRIMARY KEY (`account_number`),
  KEY `FKnnwpo0lfq4xai1rs6887sx02k` (`customer_id`),
  CONSTRAINT `FKnnwpo0lfq4xai1rs6887sx02k` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DB Tables

Spring Boot REST API Example

In the Spring Boot project we'll create entity classes having fields that will map to DB Table columns. We'll create resources (Customer and Account) exposing REST endpoints to perform the following operations.

Customer

  • @PostMapping("/customer")- To create a new Customer
  • @GetMapping("/customer/{id}")- To fetch customer details for a passed customerId
  • @GetMapping("/customer")- To fetch customer details for a passed mobile number (mobile number is passed as query parameter)
  • @DeleteMapping("/customer/{id}")- To delete customer having the passed ID.
  • @PutMapping("/customer")- To update whole customer object
  • @PatchMapping("/customer")- For partial update (updating only the customer contact fields)

Account

  • @PostMapping("/account")- To create a new Account
  • @DeleteMapping("/account/{acctNo}")- To delete account having the passed ID.
  • @GetMapping("/account/{acctNo}")- Fetch account details for the passed account number.

Technologies Used

  • Spring Boot 3.x.x
  • Java 21
  • Spring Data JPA
  • Hibernate 6
  • MySQL 8
  • Maven
  • Spring Tool Suite (STS 4.x) is used as IDE.

Creating Spring Starter Project

In the STS select File - New - Spring Starter Project and provide the details for project name and packaging type. Please use the following image as reference.

Spring Starter Project

Click next and select the required starter dependencies.

  1. Spring Web- Needed to create a web, RESTful applications.
  2. Spring Data JPA- Need for Java Persistence API. With Spring Data JPA you won't have to write boiler plate code for CRUD operations, it will be generated by Spring framework. You just need to create an interface.
  3. Spring Boot Dev Tools- Provides fast application restarts, LiveReload, and configurations for enhanced development experience.

Click next and finish to create a Spring boot project.

In the generated pom.xml also add the dependency for MySQL driver.

<dependency>
  <groupId>com.mysql</groupId>
  <artifactId>mysql-connector-j</artifactId>
  <scope>runtime</scope>
</dependency>

Refer the generated pom.xml for better understanding.

<?xml version="1.0" encoding="UTF-8"?>
<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>3.3.5</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>
  <groupId>com.knpcode</groupId>
  <artifactId>SpringBootProj</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>SpringBootProj</name>
  <description>Customer Service</description>
  <url/>
  <licenses>
    <license/>
  </licenses>
  <developers>
    <developer/>
  </developers>
  <scm>
    <connection/>
    <developerConnection/>
    <tag/>
    <url/>
  </scm>
  <properties>
    <java.version>21</java.version>
  </properties>
  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-devtools</artifactId>
      <scope>runtime</scope>
      <optional>true</optional>
    </dependency>
    <dependency>
      <groupId>com.mysql</groupId>
      <artifactId>mysql-connector-j</artifactId>
      <scope>runtime</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>
</project>

Configuring DB properties

Under src/main/resources create a file named application.yml. Note that application.properties file already exists once the project structure is created, this .properties file can also be used for providing configuration but these days YAML is preferred as it is more convenient to provide configuration for deployment. Note that YAML file uses indentation so always give space(s) when nesting.

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/knpcode?createDatabaseIfNotExist=true
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: DB_USER
    password: DB_PASSWORD

  application:
    name: customer-service

  jpa:
    database-platform: org.hibernate.dialect.MySQLDialect
    hibernate:
      ddl-auto: update
    show-sql: true

Under spring.datasource DB related configurations like DB_URL, DB_user, DB_password are provided. Please change the URL, user and password as per your DB configuration.

Application name is given as customer-service.

Under jpa, dialect is given as org.hibernate.dialect.MySQLDialect so that generated SQL is optimized for MySQL DB.

ddl-auto: update is used for auto-generation of DB schema.

show-sql: true is used to show the generated SQL in the console.

Create Packages

In the generated project structure create the following packages to keep the source files in a structured way. As per my package names the packages which I have to create are-

com.knpcode.customer.controller
com.knpcode.customer.entity
com.knpcode.customer.repository
com.knpcode.customer.service
com.knpcode.customer.audit
com.knpcode.customer.dto
com.knpcode.customer.exception

Please put the classes in the appropriate package.

Entity classes

Under the com.knpcode.customer.entity package create classes Customer, Account and Base Entity. Generally in a project you do have tables with these columns created_by, created_at, updated_on, updated_by to capture user data for the same. When creating Entity classes general design is to create a BaseEntity class with these columns which can then be extended by other entity classes.

BaseEntity.java

import java.time.LocalDateTime;
import org.springframework.data.annotation.CreatedBy;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedBy;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import jakarta.persistence.Column;
import jakarta.persistence.EntityListeners;
import jakarta.persistence.MappedSuperclass;

@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
public class BaseEntity {
	@CreatedDate
	@Column(name = "created_at", updatable = false)
	private LocalDateTime createdAt;
	
	@CreatedBy
	@Column(name = "created_by", updatable = false)
	private String createdBy;
	
	@LastModifiedDate
	@Column(name = "updated_on", insertable = false)
	private LocalDateTime updatedOn;
	
	@LastModifiedBy
	@Column(name = "updated_by", insertable = false)
	private String updatedBy;
	
	public LocalDateTime getCreatedAt() {
		return createdAt;
	}
	public void setCreatedAt(LocalDateTime createdAt) {
		this.createdAt = createdAt;
	}
	public String getCreatedBy() {
		return createdBy;
	}
	public void setCreatedBy(String createdBy) {
		this.createdBy = createdBy;
	}
	public LocalDateTime getUpdatedOn() {
		return updatedOn;
	}
	public void setUpdatedOn(LocalDateTime updatedOn) {
		this.updatedOn = updatedOn;
	}
	public String getUpdatedBy() {
		return updatedBy;
	}
	public void setUpdatedBy(String updatedBy) {
		this.updatedBy = updatedBy;
	}
}

In the above class some of the important points are-

  1. Use of @MappedSuperclass annotation to indicate that this class would be inherited and it is not an entity class which maps to a DB table.
  2. Use of @LastModifiedDate, @LastModifiedBy, @CreatedDate, @CreatedBy annotations to configure JPA to persist values for these columns automatically. Note that with the @CreatedDate and @CreatedBy annotations updatable = false attribute is used because you don’t want to change the values of these columns at the time of update. Same way, with @LastModifiedDate and @LastModifiedBy annotations insertable = false attribute is used because these columns should have value only when row is updated not when the row is inserted.
  3. To ensure that JPA can automatically put values for these four columns you need some extra configuration. First thing is using the @EntityListeners(AuditingEntityListener.class) annotation to configure an entity listener to capture auditing information on persisting and updating entities.
  4. To provide information about the user (value that will be inserted in createdBy and updatedBy fields), you need to provide an implementation of AuditorAware interface and override getCurrentAuditor() method.

AuditorAware interface implementation

import java.util.Optional;
import org.springframework.data.domain.AuditorAware;
import org.springframework.stereotype.Component;

@Component
public class CustomAuditAware implements AuditorAware<String> {

  @Override
  public Optional<String> getCurrentAuditor() {
    return Optional.of("admin");
  }
}

Here the user name is hardcoded. In actual project you can get it from Security context.

You also need to use @EnableJpaAuditing annotation to enable auditing in JPA. That annotation can be used in the application class.

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;

@SpringBootApplication
@EnableJpaAuditing(auditorAwareRef = "customAuditAware")
public class SpringBootProjApplication {
	public static void main(String[] args) {
		SpringApplication.run(SpringBootProjApplication.class, args);
	}
}

Customer.java

import java.util.ArrayList;
import java.util.List;
import jakarta.persistence.CascadeType;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;

@Entity
@Table(name = "customer")
public class Customer extends BaseEntity{
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "customer_id")
  private Long customerId;
  
  @Column(name = "name")
  private String name;
  
  private String email;
  
  @Column(name = "mobile_number", unique = true)
  private String mobileNumber;
  
  @Column(name = "address")
  private String address;
  
  @OneToMany(mappedBy = "customer", cascade = CascadeType.ALL, fetch = FetchType.LAZY)  
  private List<Account> accounts = new ArrayList<>();

  public List<Account> getAccounts() {
    return accounts;
  }

  public void setAccounts(List<Account> accounts) {
    this.accounts = accounts;
    for(Account account : accounts) {
      account.setCustomer(this);
      }
  }

  public Long getCustomerId() {
    return customerId;
  }

  public void setCustomerId(Long customerId) {
    this.customerId = customerId;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public String getEmail() {
    return email;
  }

  public void setEmail(String email) {
    this.email = email;
  }

  public String getMobileNumber() {
    return mobileNumber;
  }

  public void setMobileNumber(String mobileNumber) {
    this.mobileNumber = mobileNumber;
  }

  public String getAddress() {
    return address;
  }

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

In the above class some of the important points are-

  1. Since a customer can have many accounts, in Java class that means using a field of type List or Set. That is why List<Account> accounts field is there.
  2. Field accounts is annotated with @OneToMany annotation indicating the association that one customer may associate with many accounts. Cascade type is used as CascadeType.ALL which means all operations like merge, delete will be cascaded to associated entity. Though Fetch type is lazy by default for OnetoMany, still keeping it for readability purpose.
  3. In the setAccounts(List<Account> accounts) method customer is explicitly set for each account.
    	for(Account account : accounts) {
          account.setCustomer(this);	  
        }
    	

Account.java

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.EnumType;
import jakarta.persistence.Enumerated;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.Table;
import com.fasterxml.jackson.annotation.JsonProperty;


@Entity
@Table(name = "account")
public class Account extends BaseEntity{
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name = "account_number")	
	private Long accountNumber;
	
	@Enumerated(EnumType.STRING)
	@Column(name = "account_type")
	private AccountType accountType;
	@Column(name = "branch_address")
	private String branchAddress;
	
	@ManyToOne(fetch = FetchType.LAZY, optional = false)
	@JoinColumn(name = "customer_id")
	@JsonProperty(access = JsonProperty.Access.WRITE_ONLY) 
	private Customer customer;

	public Long getAccountNumber() {
		return accountNumber;
	}

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

	public AccountType getAccountType() {
		return accountType;
	}
	

	public void setAccountType(AccountType accountType) {
		this.accountType = accountType;
	}

	public String getBranchAddress() {
		return branchAddress;
	}

	public void setBranchAddress(String branchAddress) {
		this.branchAddress = branchAddress;
	}

	public Customer getCustomer() {
		return customer;
	}

	public void setCustomer(Customer customer) {
		this.customer = customer;
	}

	@Override
	public String toString() {
		return "Account [accountNumber=" + accountNumber + ", accountType=" + accountType + ", branchAddress="
				+ branchAddress + ", customer=" + customer + "]";
	}
}

In the above class some of the important points are-

  1. To make the association bi-directional @ManytoOne annotation is used here with the customer field. Use of @JsonProperty(access = JsonProperty.Access.WRITE_ONLY) ensures that customer object is not created from JSON (deserialization process) otherwise the process may go into an infinite loop.
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "customer_id")
    @JsonProperty(access = JsonProperty.Access.WRITE_ONLY)
    private Customer customer;
    
  2. accountType field is annotated with @Enumerated(EnumType.STRING) which means this field needs an enum value and persists that value as String.

AccountType.java

This is the enum with 3 values for account types.

public enum AccountType {
	CHECKING,
	SAVINGS,
	SALARY
}

Controller Classes

Controller classes with the REST endpoints.

CustomerController.java

import java.net.URI;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PatchMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.servlet.support.ServletUriComponentsBuilder;
import com.knpcode.customer.dto.ResponseDto;
import com.knpcode.customer.entity.Customer;
import com.knpcode.customer.service.CustomerService;

@RestController
@RequestMapping("/api")
public class CustomerController {
  private final CustomerService customerService;
  
  public CustomerController(CustomerService customerService) {
    this.customerService = customerService;
  }
  
  @PostMapping("/customer")
  public ResponseEntity<Customer> createCustomer(@RequestBody Customer customer) {
      try {
          Customer savedCustomer = customerService.createCustomer(customer);
          final URI location = ServletUriComponentsBuilder.fromCurrentRequest().path("/{id}").build()
                    .expand(savedCustomer.getCustomerId()).toUri();
         
          return ResponseEntity.created(location).body(savedCustomer);
        }catch(Exception e) {
          throw new RuntimeException("Error while creating customer " + e.getMessage());          
        }
  }
  
  @GetMapping("/customer/{id}")
  public ResponseEntity<Customer> getCustomerById(@PathVariable Long id) {
    Customer customer = customerService.getCustomerById(id);
    return new ResponseEntity<>(customer, HttpStatus.OK);
  }
  
  @GetMapping("/customer")
  public ResponseEntity<Customer> getCustomerByMobileNumber(@RequestParam String mobileNumber) {
    Customer customer = customerService.getCustomerByMobileNumber(mobileNumber);
    return new ResponseEntity<>(customer, HttpStatus.OK);
    
  }
  
  @DeleteMapping("/customer/{id}")
  public ResponseEntity<ResponseDto> deleteCustomerById(@PathVariable Long id) {
    Customer customer = customerService.getCustomerById(id);
    customerService.deleteCustomerById(id);
    return ResponseEntity.ok(new ResponseDto(HttpStatus.OK, "Customer deleted successfully"));
  }
  
  @PutMapping("/customer")
  public ResponseEntity<Customer> updateCustomer(@RequestBody Customer customer) {
    Customer updatedCustomer = customerService.updateCustomer(customer);
    return new ResponseEntity<>(updatedCustomer, HttpStatus.OK);
  }
  
  @PatchMapping("/customer")
  public ResponseEntity<Customer> UpdateCustomerContactDeatails(@RequestBody Customer customer) {
    Customer updatedCustomer = customerService.updateCustomerContactDeatails(customer);
    return new ResponseEntity<>(updatedCustomer, HttpStatus.OK);
  }
}

In the above class some of the important points are-

  1. For mapping, nouns are used as per REST specification so for creating a resource, mapping is /api/customer whereas for getting or deleting a specific resource mapping is /api/customer/id where id is the customerId.
  2. When creating a resource, response which is sent back should have the location of the newly created resource sent as header and body may include the created object, along with the status code (which is 201 Created here).
  3. When deleting or fetching Customer data by passing a specific ID, you should also ensure that the ID which is sent by user actually exists. That validation is done in the Service class.
  4. There is a method getCustomerByMobileNumber() which needs mobile number to be sent as a query parameter. That means URL to access this endpoint should be something like this- localhost:8080/api/customer?mobileNumber=9888777650
  5. There is a method updateCustomer() which is annotated with PutMapping which means it will replace the whole object. For that validation is done initially to ensure that such a customer exists.
  6. In the method updateCustomerContactDeatails() which is annotated with @PatchMapping, partial update of email, address, mobileNumber fields is done.
  7. In all the methods appropriate response is sent back which contains status code and body. Body can be an object or a message of type ResponseDto.

AccountController.java

import java.net.URI;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.DeleteMapping;
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.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.servlet.support.ServletUriComponentsBuilder;
import com.knpcode.customer.dto.ResponseDto;
import com.knpcode.customer.entity.Account;
import com.knpcode.customer.entity.Customer;
import com.knpcode.customer.service.AccountService;
import com.knpcode.customer.service.CustomerService;

@RestController
@RequestMapping("/api")
public class AccountController {
  private final AccountService accountService;
  private final CustomerService customerService;
  AccountController(AccountService accountService, CustomerService customerService) {
    this.accountService = accountService;
    this.customerService = customerService;
  }
  @PostMapping("/account")
  public ResponseEntity<?> createAccount(@RequestBody Account account) {
    try {
      System.out.println(account.getCustomer().getCustomerId());
      Customer customer = customerService.getCustomerById(account.getCustomer().getCustomerId());
      // set the customer
      account.setCustomer(customer);
      Account savedAccount = accountService.createAccount(account);
      
      final URI location = ServletUriComponentsBuilder.fromCurrentRequest()
                              .path("/{id}").build()
                              .expand(savedAccount.getAccountNumber()).toUri();      
      return ResponseEntity.created(location).body(savedAccount);
    } catch (Exception e) {
      e.printStackTrace();
      throw new RuntimeException("Error while creating Account " + e.getMessage());
    }
  }
  
  @DeleteMapping("/account/{acctNo}")
  public ResponseEntity<ResponseDto> deleteAccountByAccountNumber(@PathVariable Long acctNo) {
    accountService.deleteAccountByAccountNumber(acctNo);
    return ResponseEntity.ok(new ResponseDto(HttpStatus.OK, "Account deleted successfully"));
  }
  
  @GetMapping("/account/{acctNo}")
  public Account getAccountByAccountNumber(@PathVariable Long acctNo) {
    Account account=  accountService.getAccountByAccountNumber(acctNo);
    System.out.println(account.getCustomer().getName());
    return account;
  }
}

Service classes

Since Service layer should not be tightly coupled with Presentation layer so interfaces are created for the abstraction.

CustomerService.java

import com.knpcode.customer.entity.Customer;
public interface CustomerService {
	public Customer createCustomer(Customer customer);
	public Customer getCustomerById(Long customerId);
	public Customer getCustomerByMobileNumber(String mobileNumber);
	public void deleteCustomerById(Long customerId);
	public Customer updateCustomer(Customer customer);
	public Customer updateCustomerContactDeatails(Customer customer);
}

CustomerServiceImpl.java

import org.springframework.beans.factory.annotation.Autowired;
import com.knpcode.customer.entity.Customer;
import com.knpcode.customer.exception.ResourceNotFoundException;
import com.knpcode.customer.repository.CustomerRepository;
import org.springframework.stereotype.Service;

@Service
public class CustomerServiceImpl implements CustomerService {
  @Autowired
  CustomerRepository customerRepository;
  
  @Override
  public Customer createCustomer(Customer customer) {
    return customerRepository.save(customer);
  }

  @Override
  public Customer getCustomerById(Long customerId) {
    Customer customer = customerRepository.findById(customerId)
                        .orElseThrow(() -> new ResourceNotFoundException("Customer not found for the given Id: " + customerId));
    return customer;
  }
  
  @Override
  public Customer getCustomerByMobileNumber(String mobileNumber) {
    Customer customer = customerRepository.findByMobileNumber(mobileNumber)
          .orElseThrow(() -> new ResourceNotFoundException("Customer not found for the given mobileNumber: " + mobileNumber));
    return customer;
  }

  @Override
  public void deleteCustomerById(Long customerId) {
    customerRepository.deleteById(customerId);
    
  }
  
  @Override
  public Customer updateCustomer(Customer customer) {
    Customer dbCustomer = customerRepository.findById(customer.getCustomerId())
          .orElseThrow(() -> new ResourceNotFoundException("Customer not found for the given Id: " + customer.getCustomerId()));
    mapToCustomer(customer, dbCustomer);
    return customerRepository.save(dbCustomer);
  }
  
  private Customer mapToCustomer(Customer customer, Customer dbCustomer) {
    dbCustomer.setName(customer.getName());
    dbCustomer.setEmail(customer.getEmail());
    dbCustomer.setMobileNumber(customer.getMobileNumber());
    dbCustomer.setAddress(customer.getAddress());
    return dbCustomer;
  }
  
  /**
   * Method for partial update 
   * Update only Contact Details - email, address, mobileNumber
   */
  @Override
  public Customer updateCustomerContactDeatails(Customer customer) {
    Customer dbCustomer = customerRepository.findById(customer.getCustomerId())
          .orElseThrow(() -> new ResourceNotFoundException("Customer not found for the given Id: " + customer.getCustomerId()));
    if(customer.getMobileNumber() != null) {
      dbCustomer.setMobileNumber(customer.getMobileNumber());
    }
    if(customer.getAddress() != null) {
      dbCustomer.setAddress(customer.getAddress());
    }
    if(customer.getEmail() != null) {
      dbCustomer.setEmail(customer.getEmail());
    }
    return customerRepository.save(dbCustomer);
    
  }
}

AccountService.java

import com.knpcode.customer.entity.Account;
public interface AccountService {
	public Account createAccount(Account account);
	public Account getAccountByAccountNumber(Long acctNo);
	public void deleteAccountByAccountNumber(Long acctNo);
}

AccountServiceImpl.java

import org.springframework.stereotype.Service;
import com.knpcode.customer.entity.Account;
import com.knpcode.customer.exception.ResourceNotFoundException;
import com.knpcode.customer.repository.AccountRepository;

@Service
public class AccountServiceImpl implements AccountService {
  private final AccountRepository accountRepository;
  AccountServiceImpl(AccountRepository accountRepository){
    this.accountRepository = accountRepository;
  }
  @Override
  public Account createAccount(Account account) {
    return accountRepository.save(account);  
  }
  @Override
  public void deleteAccountByAccountNumber(Long acctNo) {
    accountRepository.findById(acctNo).orElseThrow(() -> new ResourceNotFoundException("No account associated with the given account number " + acctNo));
    accountRepository.deleteById(acctNo);
    
  }
  @Override
  public Account getAccountByAccountNumber(Long acctNo) {
    return accountRepository.findById(acctNo)
                .orElseThrow(() -> new ResourceNotFoundException("No account associated with the given account number " + acctNo));
    
  }
}

DTO classes

Data transfer object are POJOs that are used to transfer data among layers.

ResponseDto.java

import org.springframework.http.HttpStatus;

public class ResponseDto {
	private HttpStatus statusCode;
	private String message;
	
	public ResponseDto(HttpStatus statusCode, String message) {
		this.statusCode = statusCode;
		this.message = message;
	}
	
	public HttpStatus getStatusCode() {
		return statusCode;
	}
	public void setStatusCode(HttpStatus statusCode) {
		this.statusCode = statusCode;
	}
	public String getMessage() {
		return message;
	}
	public void setMessage(String message) {
		this.message = message;
	}
}

Custom Exception classes

A custom exception class is also used in the example which is used to throw an exception when resource is not found.

public class ResourceNotFoundException extends RuntimeException {
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	public ResourceNotFoundException(String message) {
		super(message);
    }

}

Repository classes

Since we are using Spring Data JPA so there is no need to create classes with data access code. We just need to create interfaces by extending JpaRepository interface and passing entity for which data access code is needed and the type of the ID.

AccountRepository.java

import org.springframework.data.jpa.repository.JpaRepository;
import com.knpcode.customer.entity.Account;
import org.springframework.stereotype.Repository;

@Repository
public interface AccountRepository extends JpaRepository<Account, Long> {
}

CustomerRepository.java

import java.util.Optional;
import org.springframework.data.jpa.repository.JpaRepository;
import com.knpcode.customer.entity.Customer;
import org.springframework.stereotype.Repository;

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Long> {
  public Optional<Customer> findByMobileNumber(String mobileNumber);
}

In the CustomerRepository interface one method findByMobileNumber() is added, for this method also framework will create the data access code (including SQL) by parsing the method.

With that we have all the classes for the example and we can run the application class by right clicking it and choosing Run As - Spring Boot App. If there is no error then Tomcat server should start listening on the port 8080 (default port).

Creating Customer resource

Creating a customer object by passing customer and account information. Using Postman you can test the creation of Customer resource.

In the Postman, change the mapping to POST in the dropdown and select tab body - raw -JSON. URL is localhost:8080/api/customer

Paste the following as body and click Send.

{
    "name": "Ram",
    "email": "ram@ram.com",
    "mobileNumber": "9888777648",
    "address": "112, MG Road, Mumbai",
    "accounts":[{
        "accountType": "SAVINGS",
        "branchAddress": "MG Road, Mumbai"
    }, 
    {
        "accountType": "CHECKING",
        "branchAddress": "MG Road, Mumbai"
    }]
}
Spring Boot REST API

If you check in the STS console you should also be able to see the generated queries because of setting jpa.show-sql: true in the application.yml file.

Hibernate: insert into customer (address,created_at,created_by,email,mobile_number,name) values (?,?,?,?,?,?)
Hibernate: insert into account (account_type,branch_address,created_at,created_by,customer_id) values (?,?,?,?,?)
Hibernate: insert into account (account_type,branch_address,created_at,created_by,customer_id) values (?,?,?,?,?)

In the DB, you should have one entry in the Customer table and two entries with the same customer_id in Account table. Also verify the values in the created_by and created_at table which should be "admin" and current date and time. Columns updated_on and updated_by shouldn't have any value.

Creating Account resource

You can add account to the existing customer by sending Account info to the URL- localhost:8080/api/account

{
    "accountType": "SALARY",
    "branchAddress": "JP Nagar, Mumbai",
    "customer": {
        "customerId": 1
    }
}

That should add one more account to the existing Customer with ID as 1.

Get and Delete Customer

Same way you can check get and delete mapping by passing URL- localhost:8080/api/customer/1 where 1 is the ID part. Deleting customer resource should also delete the associated accounts because of the cascade property used in the entity mapping.

JPA One-to-Many/Many-To-One

Getting account

You can get specific account by passing the account number with the URL- localhost:8080/api/account/1003

One thing to note here is initially Hibernate gets only the account data (not the associated customer) because of the FetchType = lazy setting. This is the generated SQL.

select a1_0.account_number,a1_0.account_type,a1_0.branch_address,a1_0.created_at,a1_0.created_by,a1_0.customer_id,a1_0.updated_by,a1_0.updated_on from account a1_0 where a1_0.account_number=?

In the AccountController I have also tried to access the customer name, that triggers another query to get associated customer information.

@GetMapping("/account/{acctNo}")
public Account getAccountByAccountNumber(@PathVariable Long acctNo) {
  Account account=  accountService.getAccountByAccountNumber(acctNo);
  System.out.println(account.getCustomer().getName());
  return account;
}

You should see another Select query-

select c1_0.customer_id,c1_0.address,c1_0.created_at,c1_0.created_by,c1_0.email,c1_0.mobile_number,c1_0.name,c1_0.updated_by,c1_0.updated_on from customer c1_0 where c1_0.customer_id=?

Updating customer

You can update customer by using PUT mapping and the URL as- localhost:8080/api/customer

In the case of update using PUT, you need to send whole body even if there is change in mobileNumber and address fields only. That is required because PUT replaces the entire resource. If you don't send the whole customer object whatever is not sent is replaced by null.

{
    "customerId": 1,
    "name": "Ram",
    "email": "ram@ram.com",
    "mobileNumber": "9888777649",
    "address": "113, MG Road, Mumbai"
}

This is the generated query-

update customer set address=?,email=?,mobile_number=?,name=?,updated_by=?,updated_on=? where customer_id=?

If you verify in the DB table now you should see values for updated_on and updated_by columns apart from the updated values.

Partial update (Using PATCH)

Patch mapping has been used to update the contact details of the customer (email, address, mobile number). With Patch you can send only the fields that need to be updated not the whole object as required with PUT.

PATCH mapping Postman

That's all for the topic Spring Boot REST API, Data JPA, One-to-Many/Many-To-One Bidirectional Example. If something is missing or you have something to share about the topic please write a comment.


You may also like

August 24, 2024

ExpressJS MVC CRUD Application + MongoDB + EJS

In the post ExpressJS MVC CRUD Application + MySQL + EJS we saw how to create an Express MVC CRUD application with MySQL as database and EJS as template engine. In this tutorial we'll see how to create Express MVC CRUD application with MongoDB as database and EJS as template engine. Bootstrap 5 is used for styling.

Note that this is not an Express REST application, it is an MVC application using HTML form which uses methods POST and GET.

Prerequisite

To create this application NodeJS, ExpressJS and MongoDB should be installed in the system.

Installing MongoDB driver for NodeJS

You can install the official MongoDB driver for NodeJS by running the following command from the root directory of your application.

npm install mongodb

Creating DB Connection

In the mongodb driver there is a MongoClient class which is used to connect to MongoDB server. DB connected is 'knpcode', there is no need to have that DB instance already created. MongoDB will create the DB with the given name if it doesn't exist.

util\dbconfig.js

const {MongoClient} = require('mongodb');

let dbconnection;
const url = 'mongodb://127.0.0.1:27017';
const client = new MongoClient(url);
const mongodbConnect = async () => {
  console.log('mongodbConnect');
  try{
    await client.connect();
    console.log('Connected successfully to server');
    dbconnection = client.db('knpcode');
  }catch(error){
    console.log(error);
    throw error;
  }
}

const getDBConnection = async() => {
  if(dbconnection){
    console.log('in if getDBConnection')
    return dbconnection;
  }else{
    console.log('in else getDBConnection')
    await mongodbConnect();
    return dbconnection;
  }
}

exports.getDBConnection = getDBConnection;

In the file there are two functions mongodbConnect and getDBConnection. Using getDBconnection function it is checked if the connection already exists then the connection is reused only if it is not yet created mongodbConnect function is called to create DB connection. In other classes where MongoDB connection is needed, getDBConnection function will be called, that's why it is exported.

Express MVC CRUD example with MongoDB

In this example User is used as Model class.

models\user.js

module.exports = class User {
  constructor(firstName, lastName, userType, startDate){
    // MongoDB adds _id that will be used as id
    //this.id = id; 
    this.firstName = firstName;
    this.lastName = lastName;
    this.userType = userType;
    this.startDate = startDate;
  }
}

Table in MySQL

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `user_type` varchar(15) NOT NULL,
  `start_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb3

In the example we'll create an application where root route ('/) should display all user data along with Edit and Delete button. On clicking Edit a modal with the data for the corresponding user should open and clicking Delete button should delete that particular User data.

A link to add user is also provided, clicking that link should display a form to enter user data.

Express.js MVC example with MongoDB

Route definition

When creating an Express MVC application, convention is to keep route class light. Logic for the routes is kept in the Controller class. Route definition should refer the function in Controller.

routes\user.js

const express = require('express');
const router = express.Router();
const userController = require('../controllers/user');

//to show user data
router.get('/', userController.getUsers)
// show user form
router.get('/user', userController.showUserForm)
// Insert user and edit user
router.post('/adduser', userController.saveUser)
// delete user
router.post('/deleteuser', userController.deleteUser)

module.exports = router;

As you can see Controller is imported here and the function in the Controller are called for processing when corresponding paths are accessed.

Controller

controllers\user.js

const getDBConnection = require('../util/dbconfig').getDBConnection;
const ObjectId = require('mongodb').ObjectId;
const User = require('../models/user')

exports.getUsers = async (req, res) => {
  console.log('In getUsers user Controller');
  try{
    const dbConnection = await getDBConnection();
    const cursor = dbConnection.collection('user').find().sort({firstName:1});
    // Retreive all the results in an array
    const results = await cursor.toArray();
    res.render('user/displayUser', {
      users: results,
      pageTitle: 'Users Data',
      userType: ['Silver', 'Gold', 'Platinum']
    })
  }catch(err){
    console.log(err);
  }
}

exports.showUserForm = (req, res) => {
  console.log('In showUserForm user Controller');
  res.render('user/userform', {
    pageTitle: 'User Form',
    userType: ['Silver', 'Gold', 'Platinum']
  })
}

exports.saveUser = async (req, res) => {
  console.log('In saveUser user Controller');
  const id = req.body.id;
  const firstName = req.body.firstName;
  const lastName = req.body.lastName;
  const userType = req.body.userType;
  const startDate = req.body.startDate;
  const user = new User(firstName, lastName, userType, startDate);
  try{
    const dbConnection = await getDBConnection();
    // id present so update
    if(id != undefined){
      await dbConnection.collection('user').updateOne(
        {_id: ObjectId.createFromHexString(id)},
        {
          $set: user
        }
      );
    }else{ // no id means insert
      await dbConnection.collection('user').insertOne(user);
    }
  }catch(err){
    console.log(err);
  }
  console.log('redirecting....')
  res.redirect('/');
}

exports.deleteUser = async (req, res) => {
  console.log('In deleteUser user Controller');
  const id = req.body.deleteid;
  console.log('UserID ', id)

  try{
    const dbConnection = await getDBConnection();
    console.log('.... Waiting for connection');
    await dbConnection.collection('user').deleteOne({
      _id: ObjectId.createFromHexString(id)
    })
    res.redirect('/');
  }catch(err){
    console.log(err);
  }
}

Important points about the logic here-

  1. In the beginning there are imports, one is for getDBConnection function in DBConfig.js, ObjectId is class representation of the BSON ObjectId type this is needed to create _id in correct format for storing in MongoDB. User model is also imported to create User object using the passed user data.
  2. Note that the collection with in the DB is ‘user’.
  3. First function getUsers() is used to get all users from DB. To query all documents, collection’s find() method is used. User data is also sorted in ascending order on the basis of firstName.
  4. Note that find() returns a cursor to the documents that match the query criteria. Using Cursor’s toArray() function the results are retrieved in an array.
    This array (containing User objects) is passed to the EJS template. User type is a dropdown, options for that are also passed.
  5. showUserForm() function is used to display a form that is used to enter and save new user.
  6. saveUser() function is used for both inserting and updating data. If userID is present that means an update otherwise insert. Form data is received by accessing request.body method. body-parser module is used to parse form data sent by a user by submitting a web form.
  7. For updating collection’s updateOne() method is used where the selection criteria is based on matching IDs. Complete User object (containing modified data for the properties) is passed for updating data.
  8. For inserting collection’s insertOne() method is used where User object is passed.
  9. Once updation or insertion is done, redirection to root route ‘/’ is done which triggers the execution of getUsers() function in the controller.
  10. deleteUser() function is used to delete user from DB. User id is passed from the view in a hidden field which is accessed in the deleteUser() function to create Delete query. Collection’s deleteOne() method is used for deleting user, deletion criteria is based on matching IDs.

Views

Views are created using EJS template engine.

views\user\displayUser.ejs

<!DOCTYPE html>
<html>
  <head>
    <title><%= pageTitle %></title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
    <script>
      function editData(user){
      // Converting to object 
      const temp = JSON.parse(user);
      // setting values in modal box form - EDIT
      document.getElementById("id").value = temp._id;
      document.getElementById("firstName").value = temp.firstName;
      document.getElementById("lastName").value = temp.lastName;
      document.getElementById("startDate").value = temp.startDate;
      document.getElementById("userType").value = temp.userType;
    }

    function deleteData(id){
      document.getElementById("deleteid").value = id;
    }
    </script>
  </head>
  <body>
    <div class="container">
    <a href="/user">Add User</a>
    <h2 class="text-info d-flex justify-content-center mb-3">User Data</h2>
   
    <table  class="table table-striped table-bordered table-sm">
      <thead>
      <tr>
        <th>id</th>
        <th>First Name</th>
        <th>Last Name</th>
        <th>User Type</th>
        <th>Start Date</th>
      </tr>
      </thead>
      <% for(let user of users) { %>
      <tr> 
        <td><%= user._id%></td>
        <td><%= user.firstName%></td>
        <td><%= user.lastName%></td>
        <td><%= user.userType%></td>
        <td><%= user.startDate%></td>
        <td class="text-center"><button type="button" class="btn btn-primary me-3" data-bs-toggle="modal" data-bs-target="#editModal" onclick="editData('<%= JSON.stringify(user)%>')">
          Edit
        </button>
        <button type="button" class="btn btn-danger" data-bs-toggle="modal" data-bs-target="#deleteModal" onclick="deleteData('<%= user._id%>')">
          Delete
        </button>
      </td>
      </tr>
      <%}%>
    </table>
    <!-- Edit and Delete Modal-->
    <div class="modal fade" id="editModal" tabindex="-1">
      <div class="modal-dialog">
        <div class="modal-content">
          <div class="modal-header">
            <h5 class="modal-title" id="editModalLabel">Modify User</h5>
            <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
          </div>
          <div class="modal-body">
            <form action="/adduser" method="POST">
              <div class="mb-3 row">
                <label for="id" class="form-label" ></label>User ID</label>
                <input type="text"  class="form-control" name="id" id="id" readonly/>
              </div>
              <div class="mb-3 row">
                  <label for="firstName" class="form-label" ></label>Enter First Name</label>
                  <input type="text"  class="form-control" name="firstName" id="firstName" />
              </div>
              <div class="mb-3 row">
                  <label for="lastName" class="form-label">Enter Last Name</label>
                  <input type="text"  class="form-control" name="lastName" id="lastName" />
              </div>
              <div class="mb-3 row">
                  <label for="userType" class="form-label">User Type</label>
                  <select class="form-select" name="userType" id="userType">
                      <% for(let type of userType) {%>
                          <option value="<%=type%>"><%=type%></option>
                      <% } %>
                  </select>
              </div>
              <div class="mb-3 row">
                  <label for="startDate" class="form-label">Enter Start Date</label>
                  <input type="date"  class="form-control" name="startDate" id="startDate" />
              </div>

          </div>
          <div class="modal-footer">
            <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
            <button type="submit" class="btn btn-primary">Save changes</button>
          </div>
        </form>
        </div>
      </div>
    </div>
  </div>
  <div class="modal fade" id="deleteModal" tabindex="-1">
    <div class="modal-dialog">
      <div class="modal-content">
        <div class="modal-header">
          <h5 class="modal-title" id="deleteModalLabel">Delete User</h5>
          <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
        </div>
        <div class="modal-body">
          <form action="/deleteuser" method="POST">
              <p>Do you really want to delete the user</p>
              <input type="hidden"  class="form-control" name="deleteid" id="deleteid"/>
        </div>
        <div class="modal-footer">
          <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
          <button type="submit" class="btn btn-primary">OK</button>
        </div>
      </form>
      </div>
    </div>
  </div>
</div>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous">
    
  </script>
  </body>
</html>

Important points about this view-

  1. Bootstrap is used for styling, css for the same is imported from the CDN.
  2. This same view code is used for showing user data in a table, modifying existing user and deleting user.
  3. Clicking Edit or Delete button opens a Modal box. In case of edit, existing user data is pre-populated, for that JavaScript function editData() is used. In case of delete id is populated in a hidden field, for that JavaScript function deleteData() is used.

views\user\userform.ejs

This is the EJS template for showing user form, where new user data is entered that can be then saved in DB.

<!DOCTYPE html>
<html>
  <head>
    <title><%= pageTitle %></title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
  </head>
  <body>
    <div class="container">
      <form action="/adduser" method="POST">
        <div class="mb-3 row">
          <label for="firstName" class="form-label" ></label>Enter First Name</label>
          <input type="text"  class="form-control" name="firstName" id="firstName" />
        </div>
        <div class="mb-3 row">
          <label for="lastName" class="form-label">Enter Last Name</label>
          <input type="text"  class="form-control" name="lastName" id="lastName" />
        </div>
        <div class="mb-3 row">
          <label for="userType" class="form-label">User Type</label>
          <select class="form-select" name="userType" id="userType">
            <% for(let type of userType) {%>
                <option value="<%=type%>"><%=type%></option>
            <% } %>
          </select>
        </div>
        <div class="mb-3 row">
          <label for="startDate" class="form-label">Enter Start Date</label>
          <input type="date"  class="form-control" name="startDate" id="startDate" />
        </div>                
        <button type="submit" class="btn btn-primary">Save</button>
      </form>
    </div>
  </body>
</html>

App.js

This JS file acts as a starting point.

const express = require('express');
const app = express();
const bodyParser = require('body-parser'); 
const port = 3000;
app.set('view engine', 'ejs');
app.set('views', 'views');

const userRoutes = require('./routes/user');

app.use(bodyParser.urlencoded({ extended: false }));
app.use(userRoutes);

app.listen(port, () => {
    console.log(`Example app listening on port ${port}`)
})

With all the changes done you can run the application by using the command.

node app.js

On accessing http://localhost:3000/ all the users are displayed.

Edit user data

ExpressJS MVC MySQL

Delete User

Add user

That's all for the topic ExpressJS MVC CRUD Application + MongoDB + EJS. If something is missing or you have something to share about the topic please write a comment.


You may also like

August 20, 2024

ExpressJS MVC CRUD Application + MySQL + EJS

In this tutorial we'll see how to create an Express MVC CRUD application with MySQL as database and EJS as template engine. Bootstrap 5 is used for styling.

Note that this is not an Express REST application, it is an MVC application using HTML form which uses methods POST and GET.

Express MVC CRUD example

In this example User is used as Model (though direct use of MySQL doesn't have much use of Model class, if used with Sequelize then Model class has more usage).

models\user.js

module.exports = class User {
  constructor(id, firstName, lastName, userType, startDate){
    this.is = id;
    this.firstName = firstName;
    this.lastName = lastName;
    this.userType = userType;
    this.startDate = startDate;
  }
}

Table in MySQL

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `user_type` varchar(15) NOT NULL,
  `start_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb3

Aim is to create an application where root route ('/) should display all user data along with Edit and Delete button. On clicking Edit a modal with the data for the corresponding user should open and clicking Delete button should delete that particular User data.

A link to add user is also provided, clicking that link should display a form to enter user data.

Express.js MVC

Route definition

When creating an Express MVC application, convention is to keep route class light. Logic for the routes is kept in the Controller class. Route definition should refer the function in Controller.

routes\user.js

const express = require('express');
const router = express.Router();
const userController = require('../controllers/user');

//to show user data
router.get('/', userController.getUsers)
// show user form
router.get('/user', userController.showUserForm)
// Insert user and edit user
router.post('/adduser', userController.saveUser)
// delete user
router.post('/deleteuser', userController.deleteUser)

module.exports = router;

As you can see Controller is imported here and the function in the Controller are called for processing when corresponding paths are accessed.

Controller

controllers\user.js

const pool = require('../util/dbconfig');
const date = require('date-and-time') 

exports.getUsers = async (req, res) => {
    console.log('In getUsers user Controller');
    const sql = "SELECT * FROM USER";
    try{
      const [result, fields] = await pool.query(sql);
      res.render('user/displayUser', {
        users: result,
        pageTitle: 'Users Data',
        date: date,
        userType: ['Silver', 'Gold', 'Platinum']
      })
      console.log(result);
    }catch(err){
      console.log(err);
    }
}

exports.showUserForm = (req, res) => {
    console.log('In showUserForm user Controller');
    res.render('user/userform', {
        pageTitle: 'User Form',
        userType: ['Silver', 'Gold', 'Platinum']
    })
}

exports.saveUser = async (req, res) => {
    console.log('In saveUser user Controller');
    const id = req.body.id;
    const firstName = req.body.firstName;
    const lastName = req.body.lastName;
    const userType = req.body.userType;
    const startDate = req.body.startDate;
    let sql = '';
    let values = [];
    // Works as update
    if(id != undefined){
      sql = `UPDATE USER SET first_name = ?, last_name = ?, user_type = ?, 
      start_date = ? WHERE ID = ?`;
      values = [firstName, lastName, userType, startDate, id];
    }else{ // Works as insert
      sql = `INSERT INTO USER (first_name, last_name, user_type, start_date) 
      values (?, ?, ?, ?)`;
      values = [firstName, lastName, userType, startDate];
    }
    try{
      const conn = await pool.getConnection();
      const [result, fields] = await conn.execute(sql, values);
      console.log(result);
      console.log(fields);
      conn.release();
    }catch(err){
      console.log(err);
    }
    res.redirect('/');
}

exports.deleteUser = async (req, res) => {
  console.log('In deleteUser user Controller');
  const id = req.body.deleteid;
  const sql = "DELETE FROM user WHERE id = ?";
  const values = [id];
  try{
    const [result, fields] = await pool.query(sql, values);
    console.log(result);
    res.redirect('/');
  }catch(err){
    console.log(err);
  }
}

Important points about the logic here-

  1. There are two imports, one is for DB configuration and another is date-time library which is used for formatting dates.
  2. First function getUsers() is used to get all users from DB. Fetched users are passed to the EJS template. User type is a dropdown, options for that are also passed.
  3. showUserForm() function is used to display a form that is used to enter and save new user.
  4. saveUser() function is used for both inserting and updating data. If userID is present that means an update otherwise insert. Form data is received by accessing request.body method. body-parser module is used to parse form data sent by a user by submitting a web form.
  5. deleteUser() function is used to delete user from DB. User id is passed from the view in a hidden field which is accessed in the deleteUser() function to create Delete query.

DB Configuration

Using mysql2 package you can also create a connection pool. For that createPool() method is used which needs few configuration values. DB pool is created in a separate file.

util\dbconfig.js

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'admin',
  database: 'knpcode', //Schema Name
  waitForConnections: true, // this is default anyway
  connectionLimit: 10, // this is default anyway
});

module.exports = pool;

Views

Views are created using EJS template engine.

views\user\displayUser.ejs

<!DOCTYPE html>
<html>
  <head>
    <title><%= pageTitle %></title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
    <script>
      function editData(user){
      // Converting to object 
      const temp = JSON.parse(user);
      // setting values in modal box form - EDIT
      document.getElementById("id").value = temp.id;
      document.getElementById("firstName").value = temp.first_name;
      document.getElementById("lastName").value = temp.last_name;
      document.getElementById("startDate").value = temp.start_date;
      document.getElementById("userType").value = temp.user_type;
    }

    function deleteData(id){
      document.getElementById("deleteid").value = id;
    }
    </script>
  </head>
  <body>
    <div class="container">
    <a href="/user">Add User</a>
    <h2 class="text-info d-flex justify-content-center mb-3">User Data</h2>
   
    <table  class="table table-striped table-bordered table-sm">
      <thead>
      <tr>
        <th>id</th>
        <th>First Name</th>
        <th>Last Name</th>
        <th>User Type</th>
        <th>Start Date</th>
      </tr>
      </thead>
      <% for(let user of users) { %>
      <tr> 
        <td><%= user.id%></td>
        <td><%= user.first_name%></td>
        <td><%= user.last_name%></td>
        <td><%= user.user_type%></td>
        <td><%= date.format(user.start_date,'YYYY-MM-DD');%></td>
        <!-- Changing user.start_date to a formatted date -->
        <% user.start_date = date.format(user.start_date,'YYYY-MM-DD');%>
        <td class="text-center"><button type="button" class="btn btn-primary me-3" data-bs-toggle="modal" data-bs-target="#editModal" onclick="editData('<%= JSON.stringify(user)%>')">
          Edit
        </button>
        <button type="button" class="btn btn-danger" data-bs-toggle="modal" data-bs-target="#deleteModal" onclick="deleteData('<%= user.id%>')">
          Delete
        </button>
      </td>
      </tr>
      <%}%>
    </table>
    <!-- Edit and Delete Modal-->
    <div class="modal fade" id="editModal" tabindex="-1">
      <div class="modal-dialog">
        <div class="modal-content">
          <div class="modal-header">
            <h5 class="modal-title" id="editModalLabel">Modify User</h5>
            <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
          </div>
          <div class="modal-body">
            <form action="/adduser" method="POST">
              <div class="mb-3 row">
                <label for="id" class="form-label" ></label>User ID</label>
                <input type="text"  class="form-control" name="id" id="id" readonly/>
              </div>
              <div class="mb-3 row">
                  <label for="firstName" class="form-label" ></label>Enter First Name</label>
                  <input type="text"  class="form-control" name="firstName" id="firstName" />
              </div>
              <div class="mb-3 row">
                  <label for="lastName" class="form-label">Enter Last Name</label>
                  <input type="text"  class="form-control" name="lastName" id="lastName" />
              </div>
              <div class="mb-3 row">
                  <label for="userType" class="form-label">User Type</label>
                  <select class="form-select" name="userType" id="userType">
                      <% for(let type of userType) {%>
                          <option value="<%=type%>"><%=type%></option>
                      <% } %>
                  </select>
              </div>
              <div class="mb-3 row">
                  <label for="startDate" class="form-label">Enter Start Date</label>
                  <input type="date"  class="form-control" name="startDate" id="startDate" />
              </div>

          </div>
          <div class="modal-footer">
            <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
            <button type="submit" class="btn btn-primary">Save changes</button>
          </div>
        </form>
        </div>
      </div>
    </div>
  </div>
  <div class="modal fade" id="deleteModal" tabindex="-1">
    <div class="modal-dialog">
      <div class="modal-content">
        <div class="modal-header">
          <h5 class="modal-title" id="deleteModalLabel">Delete User</h5>
          <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
        </div>
        <div class="modal-body">
          <form action="/deleteuser" method="POST">
              <p>Do you really want to delete the user</p>
              <input type="hidden"  class="form-control" name="deleteid" id="deleteid"/>
        </div>
        <div class="modal-footer">
          <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
          <button type="submit" class="btn btn-primary">OK</button>
        </div>
      </form>
      </div>
    </div>
  </div>
</div>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous">
    
  </script>
  </body>
</html>

Important points about this view-

  1. Bootstrap is used for styling.
  2. This same view code is used for showing user data in a table, modifying existing user and deleting user.
  3. Clicking Edit or Delete button opens a Modal box. In case of edit existing user data is pre-populated, for that JavaScript function editData() is used. In case of delete id is populated in a hidden field.

views\user\userform.ejs

<!DOCTYPE html>
<html>
  <head>
    <title><%= pageTitle %></title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
  </head>
  <body>
    <div class="container">
      <form action="/adduser" method="POST">
        <div class="mb-3 row">
          <label for="firstName" class="form-label" ></label>Enter First Name</label>
          <input type="text" class="form-control" name="firstName" id="firstName" />
        </div>
        <div class="mb-3 row">
          <label for="lastName" class="form-label">Enter Last Name</label>
          <input type="text"  class="form-control" name="lastName" id="lastName" />
        </div>
        <div class="mb-3 row">
          <label for="userType" class="form-label">User Type</label>
          <select class="form-select" name="userType" id="userType">
            <% for(let type of userType) {%>
              <option value="<%=type%>"><%=type%></option>
            <% } %>
          </select>
        </div>
        <div class="mb-3 row">
          <label for="startDate" class="form-label">Enter Start Date</label>
          <input type="date" class="form-control" name="startDate" id="startDate" />
        </div>
        <button type="submit" class="btn btn-primary">Save</button>
      </form>
    </div>
  </body>
</html>

App.js

This JS file acts as a starting point.

const express = require('express');
const app = express();
const bodyParser = require('body-parser'); 
const port = 3000;
app.set('view engine', 'ejs');
app.set('views', 'views');

const userRoutes = require('./routes/user');

app.use(bodyParser.urlencoded({ extended: false }));
app.use(userRoutes);

app.listen(port, () => {
  console.log(`Example app listening on port ${port}`)
})

With all the changes done you can run the application by using the command.

node app.js

On accessing http://localhost:3000/ all the users are displayed.

Edit user data

ExpressJS MVC MySQL

Delete User

Add user

That's all for the topic ExpressJS MVC CRUD Application + MySQL + EJS. If something is missing or you have something to share about the topic please write a comment.


You may also like

July 17, 2024

ExpressJS: EJS Partials

In EJS template engine there is no support for inheritance like you have in Pug through block and extends keywords. So, you can't create layouts using blocks in EJS as you can in Pug template inheritance. But the functionality to include smaller files to create a larger file is very important in order to promote code reuse, making maintenance of code easier as you don't need to change code in every page. In EJS you can have the similar functionality by including smaller files to create a bigger file.

In EJS template terminology these are known as EJS partials which are reusable components that can be combined to create a larger view.

Syntax for include in EJS

You can include partial files by using the following command.

<%- include( PARTIAL_FILE ) %> 

Here PARTIAL_FILE is the file path relative to the template where partial file is included.

Note the use of <%- %> tags which allows you to output unescaped HTML content. When you use this tag <%= %>, if you have any HTML it will be rendered as string (HTML code itself) not as HTML display.

Express.js - EJS partials example

In this example we'll create an ExpressJS app where pages will have a navigation menu and footer. Since this menu and footer is common for all the pages so we can create partial files with navigation menu and footer code which can then be included in other pages which are going to be home.ejs and user.ejs.

There are also stylesheets main.css (for styling menu and footer) and user.css (for styling table which shows user data) where user.css is specific to user.ejs template.

public\css\main.css

.header{
    width: 100%;
    height: 3rem;
    text-align: center;
    background-color: #6d70a8;
    padding: 1 2rem;
    margin-bottom: 1rem;
}

.nav{
    height: 100%;
    display: flex;
    align-items: center;
}

.nav-menu{
    list-style: none;
    display: flex;
}

.nav-menu-item{
    margin: 0 2rem;
    padding: 0;
}

.nav-menu-item a{
    text-decoration: none;
    color: white;
}

.footer { 
    text-align: center;
 
    position: absolute; 
    width: 100%;
    bottom: 0; 
    left: 0; 
    background-color: #6d70a8;
    color: white;
}

public\css\user.css

table, td, th {
  border: 1px solid;
}
table {
  width: 80%;
  border-collapse: collapse;
}

Within views folder let's create a sub-folder named partials to save partial files.

views\partials\navigation.ejs

<header class="header">
  <nav class="nav">
    <ul class="nav-menu">
      <li class="nav-menu-item">
        <a href="/">Home</a></li>
      <li class="nav-menu-item">
        <a href="/user">User</a>
      </li>
    </ul>
  </nav>
</header>

views\partials\footer.ejs

<footer class="footer">
  <p>&copy; 2024 Company Name. All rights reserved.</p>
</footer>

views\home.ejs

<!DOCTYPE html>
<html>
  <head>
    <title><%= pageTitle %></title>
    <link rel="stylesheet" href="/css/main.css">
  </head>
  <body>
    <%- include('partials/navigation.ejs') %>
    <h1>EJS Demo</h1>
    <p>Welcome <strong><%= userName %></strong></p>
    <%- include('partials/footer.ejs') %>
  </body>
</html>

Since navigation and footer are needed so those files are included at the appropriate location within the body.

views\user.ejs

This template is used to show user data by iterating users array. In this file also navigation and footer are needed so those files are included.

<!DOCTYPE html>
<html lang="en">
  <head>
    <title><%= pageTitle%></title>
    <link rel="stylesheet" href="/css/user.css">
    <link rel="stylesheet" href="/css/main.css">
  </head>
  <body> 
    <%- include('partials/navigation.ejs') %>
    <h2>Using for-of to loop array</h2>
    <table>
      <tr>
        <th>Name</th>
        <th>Age</th>
        <th>Gender</th>
      </tr>
      <% for(let user of users) {%>
      <tr> 
        <td><%= user.name%></td>
        <td><%= user.age%></td>
        <td><%= user.gender%></td>
      </tr>
      <%}%>
    </table>
    <%- include('partials/footer.ejs') %>
  </body>
</html>

app.js

const express = require('express');
const app = express();
const port = 3000;
const path = require('path');

// To serve static files like CSS
app.use(express.static(path.join(__dirname, 'public')));

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');

// Hardcoded user data
const users = [{name:'Reene', age: 28, gender:'F'}, 
    {name:'Badal', age: 34, gender:'M'},
    {name:'Vidyut', age: 25, gender:'M'},
    {name:'Dhriti', age: 29, gender:'F'}
]

// for home page
app.get('/', (req, res) => {
    res.render('home', {pageTitle:'HomePage', userName:'TestUser'});
})

// for user page
app.get('/user', (req, res) => {
    res.render('user', {users: users, pageTitle: 'User Page'});
})

app.listen(port, () => {
    console.log(`Example app listening on port ${port}`)
})

On running this file- node app.js and then accessing the URL- http://localhost:3000/

EJS Partials

Clicking on user menu option.

Express.js EJS partials

That's all for the topic ExpressJS: EJS Partials. If something is missing or you have something to share about the topic please write a comment.


You may also like