@Where
clause is used to work with soft deletes.
What is
soft delete?
In a typical
application, whenever user request to delete an entity, it remains in the
database, but a special column ‘isDeleted’ set to true.
Why
soft deletes?
With soft
deletes, we no need to worry about data loss. Data always in our tables.
One
problem with soft deletes is, table size will keep on increasing, it will affect
the performance. May be after some time, archive the soft deleted data to some
other tables periodically.
How to work
with soft deletes using Where clause?
@MappedSuperclass
public abstract class BaseEntity {
@Column(name = "IS_DELETED")
private boolean isDeleted = false;
......
......
}
@Entity
@Table(name = "employees")
@Where(clause = "IS_DELETED = 'false'")
public class Employee extends BaseEntity {
}
Now, for
all the queries that we are going to execute ‘IS_DELETED = 'false'’ gets added,
so we will get only active records.
Find the
below working application.
BaseEntity.java
package com.sample.app.entity;
import java.sql.Timestamp;
import javax.persistence.Column;
import javax.persistence.MappedSuperclass;
import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;
@MappedSuperclass
public abstract class BaseEntity {
@Column(name = "CREATED_BY", updatable = false)
private String createdBy;
@CreationTimestamp
@Column(name = "CREATED_TIME", updatable = false)
private Timestamp createdTime;
@Column(name = "UPDATED_BY")
private String updatedBy;
@UpdateTimestamp
@Column(name = "UPDATED_TIME")
private Timestamp updatedTime;
@Column(name = "IS_DELETED")
private boolean isDeleted = false;
public String getCreatedBy() {
return createdBy;
}
public void setCreatedBy(String createdBy) {
this.createdBy = createdBy;
}
public Timestamp getCreatedTime() {
return createdTime;
}
public void setCreatedTime(Timestamp createdTime) {
this.createdTime = createdTime;
}
public String getUpdatedBy() {
return updatedBy;
}
public void setUpdatedBy(String updatedBy) {
this.updatedBy = updatedBy;
}
public Timestamp getUpdatedTime() {
return updatedTime;
}
public void setUpdatedTime(Timestamp updatedTime) {
this.updatedTime = updatedTime;
}
public boolean isDeleted() {
return isDeleted;
}
public void setDeleted(boolean isDeleted) {
this.isDeleted = isDeleted;
}
}
Employee.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.Table;
import org.hibernate.annotations.Where;
@Entity
@Table(name = "employees")
@Where(clause = "IS_DELETED = 'false'")
public class Employee extends BaseEntity {
@Id
@GeneratedValue
@Column(name = "EMPLOYEE_ID")
private int id;
@Column(name = "EMPLOYEE_FIRST_NAME")
private String firstName;
@Column(name = "EMPLOYEE_LAST_NAME")
private String lastName;
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;
}
@Override
public String toString() {
return "Employee [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + ", getCreatedBy()="
+ getCreatedBy() + ", getCreatedTime()=" + getCreatedTime() + ", getUpdatedBy()=" + getUpdatedBy()
+ ", getUpdatedTime()=" + getUpdatedTime() + ", isDeleted()=" + isDeleted() + "]";
}
}
EmployeeRepository.java
package com.sample.app.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import com.sample.app.entity.Employee;
public interface EmployeeRepository extends JpaRepository<Employee, Integer>{
}
App.java
package com.sample.app;
import java.util.Arrays;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import com.sample.app.entity.Employee;
import com.sample.app.repository.EmployeeRepository;
@SpringBootApplication
public class App {
public static void main(String args[]) {
SpringApplication.run(App.class, args);
}
@Bean
public CommandLineRunner demo(EmployeeRepository empRepo) {
return (args) -> {
Employee emp1 = new Employee();
Employee emp2 = new Employee();
Employee emp3 = new Employee();
emp1.setFirstName("Ram");
emp1.setLastName("Majety");
emp1.setCreatedBy("Ram");
emp1.setUpdatedBy("Ram");
emp2.setFirstName("Sowmya");
emp2.setLastName("Konagandla");
emp2.setCreatedBy("Ram");
emp2.setUpdatedBy("Ram");
emp3.setFirstName("Narendra");
emp3.setLastName("Bora");
emp3.setCreatedBy("Ram");
emp3.setUpdatedBy("Ram");
empRepo.saveAll(Arrays.asList(emp1, emp2, emp3));
empRepo.save(emp1);
empRepo.save(emp2);
Employee emp = empRepo.save(emp3);
empRepo.findAll().forEach(System.out::println);
System.out.println("\nSoft deleting 3rd record\n");
emp.setDeleted(true);
empRepo.save(emp);
empRepo.findAll().forEach(System.out::println);
};
}
}
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=false spring.jpa.properties.hibernate.format_sql=false ## 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
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>springH2</groupId>
<artifactId>springH2</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>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
</dependencies>
</project>
Total
project structure looks like below.
Run
App.java, you can see below messages in console.
Employee [id=1, firstName=Ram, lastName=Majety, getCreatedBy()=Ram, getCreatedTime()=2019-09-28 23:19:30.915, getUpdatedBy()=Ram, getUpdatedTime()=2019-09-28 23:19:30.915, isDeleted()=false] Employee [id=2, firstName=Sowmya, lastName=Konagandla, getCreatedBy()=Ram, getCreatedTime()=2019-09-28 23:19:30.92, getUpdatedBy()=Ram, getUpdatedTime()=2019-09-28 23:19:30.92, isDeleted()=false] Employee [id=3, firstName=Narendra, lastName=Bora, getCreatedBy()=Ram, getCreatedTime()=2019-09-28 23:19:30.92, getUpdatedBy()=Ram, getUpdatedTime()=2019-09-28 23:19:30.92, isDeleted()=false] Soft deleting 3rd record Employee [id=1, firstName=Ram, lastName=Majety, getCreatedBy()=Ram, getCreatedTime()=2019-09-28 23:19:30.915, getUpdatedBy()=Ram, getUpdatedTime()=2019-09-28 23:19:30.915, isDeleted()=false] Employee [id=2, firstName=Sowmya, lastName=Konagandla, getCreatedBy()=Ram, getCreatedTime()=2019-09-28 23:19:30.92, getUpdatedBy()=Ram, getUpdatedTime()=2019-09-28 23:19:30.92, isDeleted()=false]
You can download
complete working application from this link.
why doesn't @where works at "BaseEntity"?
ReplyDelete