Java Spring JPA Specification

Bởi -
I. Giới thiệu

Spring JPA Specification chứa một bộ api cho phép ta search, filter trong entity một cách dễ dàng.

Trong bài này ta có mối quan hệ giữa Employee và Department như sau

Giờ chúng ta sẽ dùng Specification để lấy ra danh sách Employee theo tên phòng ban mà ta muốn tìm

II. Sử dụng
1. pom.xml
        
    <?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>com.specification</groupId>
        <artifactId>specification-test</artifactId>
        <version>1.0</version>
        <packaging>jar</packaging>
    
        <properties>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
            <maven.compiler.source>1.8</maven.compiler.source>
            <maven.compiler.target>1.8</maven.compiler.target>
            <maven-jar-plugin.version>3.1.1</maven-jar-plugin.version>
            <lombok.version>1.18.16</lombok.version>
            <ojdbc.version>12.2.0.1</ojdbc.version>
        </properties>
    
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.5.3</version>
            <relativePath />
        </parent>
        
        <dependencyManagement>
            <dependencies>	        
                <dependency>
                    <groupId>org.projectlombok</groupId>
                    <artifactId>lombok</artifactId>
                    <version>${lombok.version}</version>
                </dependency>
    
                <dependency>
                    <groupId>com.oracle</groupId>
                    <artifactId>ojdbc8</artifactId>
                    <version>${ojdbc.version}</version>
                </dependency>
            </dependencies>
        </dependencyManagement>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter</artifactId>
            </dependency>
            
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
            </dependency>
                    
            <dependency>
                <groupId>com.oracle</groupId>
                <artifactId>ojdbc8</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <scope>provided</scope>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>
2. Entity

2.1 Department

        
    package com.entity;

    import java.util.Set;
    import javax.persistence.Column;
    import javax.persistence.Entity;
    import javax.persistence.Id;
    import javax.persistence.Table;
    import lombok.AllArgsConstructor;
    import lombok.Builder;
    import lombok.Getter;
    import lombok.NoArgsConstructor;
    import lombok.Setter;
    
    @Getter
    @Setter
    @Entity
    @Builder
    @NoArgsConstructor
    @AllArgsConstructor
    @Table(name = "DEPARTMENT")
    public class Department {
    
        @Id
        @Column(name = "ID")
        private Long id;
    
        @Column(name = "NAME")
        private String name;
    }

2.2 Employee

        
    package com.entity;

    import java.util.Set;
    import javax.persistence.Column;
    import javax.persistence.Entity;
    import javax.persistence.Id;
    import javax.persistence.Table;
    import javax.persistence.JoinColumn;
    import javax.persistence.ManyToOne;
    import lombok.AllArgsConstructor;
    import lombok.Builder;
    import lombok.Getter;
    import lombok.NoArgsConstructor;
    import lombok.Setter;
    
    @Getter
    @Setter
    @Entity
    @Builder
    @NoArgsConstructor
    @AllArgsConstructor
    @Table(name = "EMPLOYEE")
    public class Employee {
    
        @Id
        @Column(name = "ID")
        private Long id;
    
        @Column(name = "NAME")
        private String name;

        @Column(name = "DEPARTMENT_ID")
        private Long departmentId;

        @ManyToOne(fetch = FetchType.LAZY)
        @JoinColumn(name = "DEPARTMENT_ID", insertable = false, updatable = false)
        private Department department;
    }

2.3 SearchCriteria

Dùng để lưu giá trị cần search

        
    package com.entity;

    import lombok.AllArgsConstructor;
    import lombok.Builder;
    import lombok.Getter;
    import lombok.NoArgsConstructor;
    import lombok.Setter;

    @Getter
    @Setter
    @NoArgsConstructor
    @AllArgsConstructor
    @Builder
    public class SearchCriteria {
        
        private String key;
        private String operation;
        private Object value;
        private boolean isOrPredicate;
        private boolean isLower;

    }
3. Repository

extend thêm từ JpaSpecificationExecutor để có thêm các method từ Specification

        
    package com.repository.specification;

    import java.util.List;
    import org.springframework.data.jpa.domain.Specification;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    import org.springframework.stereotype.Repository;
    import com.entity.Employee;
    
    @Repository
    public interface EmployeeRepo extends JpaRepository<Employee, Long>, JpaSpecificationExecutor<Employee> {
    
        List<Employee> findAll(Specification<Employee> spec);
    }
4. BaseSpecification

Viết thêm một số lớp Base để tái sử dụng

4.1 SpecificationConstants

        
    package com.constants;

    public final class SpecificationConstants {
    
        private SpecificationConstants() {
    
        }
    
        public static final String LIKE = ":";
        public static final String GREATER_THAN_OR_EQUAL = ">";
        public static final String LESS_THAN_OR_EQUAL = "<";
        public static final String EQUAL = "=";
        public static final String PERCENT = "%";
        public static final String UNDERSCORE = "_";
        public static final char ESCAPE = '\\';
    
    }

4.2 BaseSpecification

        
    package com.repository.specification;

    import static com.constants.SpecificationConstants.EQUAL;
    import static com.constants.SpecificationConstants.ESCAPE;
    import static com.constants.SpecificationConstants.FUNCTION_NORMALIZE;
    import static com.constants.SpecificationConstants.GREATER_THAN_OR_EQUAL;
    import static com.constants.SpecificationConstants.LESS_THAN_OR_EQUAL;
    import static com.constants.SpecificationConstants.LIKE;
    import static com.constants.SpecificationConstants.PERCENT;
    import static com.constants.SpecificationConstants.UNDERSCORE;
    import java.time.LocalDate;
    import java.time.LocalDateTime;
    import java.util.Date;
    import javax.persistence.criteria.CriteriaBuilder;
    import javax.persistence.criteria.CriteriaQuery;
    import javax.persistence.criteria.Expression;
    import javax.persistence.criteria.Predicate;
    import javax.persistence.criteria.Root;
    import org.springframework.data.jpa.domain.Specification;
    import com.entity.SearchCriteria;
    
    public class BaseSpecification<T> implements Specification<T> {
    
        private static final long serialVersionUID = 1L;
        private static final List SPECIAL_CHARACTER = Arrays.asList(new String[] { ".", "%", "_" });
        private static final String SEPARATOR_SPECIAL_CHAR = "\\";
    
        protected SearchCriteria criteria;
    
        public BaseSpecification(SearchCriteria criteria) {
            this.criteria = criteria;
        }
    
        @Override
        public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
            return getPredicate(root, query, criteriaBuilder, criteria);
        }
    
        public static <E> Predicate getPredicate(Root<E> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder,
                SearchCriteria criteria) {
            if (criteria == null) {
                return null;
            }
            if (GREATER_THAN_OR_EQUAL.equalsIgnoreCase(criteria.getOperation())) {
                return greaterThanOrEqualTo(criteriaBuilder, root.<String>get(criteria.getKey()), criteria);
            }
            if (LESS_THAN_OR_EQUAL.equalsIgnoreCase(criteria.getOperation())) {
                return lessThanOrEqualTo(criteriaBuilder, root.<String>get(criteria.getKey()), criteria);
            }
            if (LIKE.equalsIgnoreCase(criteria.getOperation())) {
                if (root.get(criteria.getKey()).getJavaType() == String.class) {
                    return like(criteriaBuilder, root.<String>get(criteria.getKey()), criteria);
                } else {
                    return equalTo(criteriaBuilder, root.get(criteria.getKey()), criteria);
                }
            }
            if (EQUAL.equalsIgnoreCase(criteria.getOperation())) {
                return equalTo(criteriaBuilder, root.get(criteria.getKey()), criteria);
            }
            return null;
        }
    
        @SuppressWarnings({ "unchecked", "rawtypes" })
        public static Predicate greaterThanOrEqualTo(CriteriaBuilder criteriaBuilder, Expression expression,
                SearchCriteria criteria) {
            if(criteria.getValue() == null) {
                return null;
            }
            if(criteria.getValue() instanceof Date) {
                return criteriaBuilder.greaterThanOrEqualTo(expression, (Date) criteria.getValue());
            }
            if(criteria.getValue() instanceof LocalDate) {
                return criteriaBuilder.greaterThanOrEqualTo(expression, (LocalDate) criteria.getValue());
            }
            if(criteria.getValue() instanceof LocalDateTime) {
                return criteriaBuilder.greaterThanOrEqualTo(expression, (LocalDateTime) criteria.getValue());
            }
            return criteriaBuilder.greaterThanOrEqualTo(expression, criteria.getValue().toString());
        }
    
        @SuppressWarnings({ "unchecked", "rawtypes" })
        public static Predicate lessThanOrEqualTo(CriteriaBuilder criteriaBuilder, Expression expression,
                SearchCriteria criteria) {
            if(criteria.getValue() == null) {
                return null;
            }
            if(criteria.getValue() instanceof Date) {
                return criteriaBuilder.lessThanOrEqualTo(expression, (Date) criteria.getValue());
            }
            if(criteria.getValue() instanceof LocalDate) {
                return criteriaBuilder.lessThanOrEqualTo(expression, (LocalDate) criteria.getValue());
            }
            if(criteria.getValue() instanceof LocalDateTime) {
                return criteriaBuilder.lessThanOrEqualTo(expression, (LocalDateTime) criteria.getValue());
            }
            return criteriaBuilder.lessThanOrEqualTo(expression, criteria.getValue().toString());
        }
    
        @SuppressWarnings("rawtypes")
        public static Predicate equalTo(CriteriaBuilder criteriaBuilder, Expression expression,
                SearchCriteria criteria) {
            return criteriaBuilder.equal(expression, criteria.getValue());
        }
    
        public static Predicate like(CriteriaBuilder criteriaBuilder, Expression<String> expression,
                SearchCriteria criteria) {
            return criteriaBuilder.like(getExpression(criteriaBuilder, expression, criteria),
                    PERCENT + escape(criteria.getValue()) + PERCENT, ESCAPE);
        }
    
        public static <E> Expression<String> getExpression(CriteriaBuilder criteriaBuilder, Expression<String> expression,
                SearchCriteria criteria) {
            if (criteria.isLower()) {
                expression = criteriaBuilder.lower(expression);
            }
            return expression;
        }
    
        public static String escape(Object value) {
            if (value instanceof String) {
                String escapeValue = escape(value.toString(), PERCENT);
                escapeValue = escape(escapeValue, UNDERSCORE);
                return escapeValue;
            }
            return null;
        }
    
        public static String escape(String value, String character) {
            if (value == null) {
                return null;
            }
            if (character == null || character.trim().isEmpty()) {
                return value;
            }
            character = getIfSpecialChar(character);
            return value.replaceAll(character, ESCAPE + character);
        }

        public static String getIfSpecialChar(String value) {
            if (value == null) {
                return null;
            }
            if (SPECIAL_CHARACTER.contains(value)) {
                value = SEPARATOR_SPECIAL_CHAR + value;
            }
            return value;
        }
    
    }

4.3 BaseSpecificationBuilder

        
    package com.repository.specification;

    import static com.constants.SpecificationConstants.EQUAL;
    import static com.constants.SpecificationConstants.ESCAPE;
    import static com.constants.SpecificationConstants.FUNCTION_NORMALIZE;
    import static com.constants.SpecificationConstants.GREATER_THAN_OR_EQUAL;
    import static com.constants.SpecificationConstants.LESS_THAN_OR_EQUAL;
    import static com.constants.SpecificationConstants.LIKE;
    import static com.constants.SpecificationConstants.PERCENT;
    import static com.constants.SpecificationConstants.UNDERSCORE;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.stream.Collectors;
    import org.springframework.data.jpa.domain.Specification;
    import com.entity.SearchCriteria;
    import lombok.Getter;

    @Getter
    public class BaseSpecificationBuilder<T> {
        
        private final List<SearchCriteria> params;

        public BaseSpecificationBuilder() {
            params = new ArrayList<SearchCriteria>();
        }

        public BaseSpecificationBuilder<T> with(SearchCriteria criteria) {
            params.add(criteria);
            return this;
        }

        @SuppressWarnings({ "rawtypes", "unchecked" })
        public Specification<T> build() {
            if (params.size() == 0) {
                return null;
            }

            List<Specification> specs = params.stream().map(BaseSpecification::new).collect(Collectors.toList());

            Specification<T> result = specs.get(0);
            for (int i = 1; i < params.size(); i++) {
                result = params.get(i).isOrPredicate() ? Specification.where(result).or(specs.get(i))
                        : Specification.where(result).and(specs.get(i));
            }
            return result;
        }
        
        public static SearchCriteria.SearchCriteriaBuilder criteriaLikeLowerBuilder(String key, String value) {
            return SearchCriteria.builder()
                    .key(key)
                    .operation(LIKE)
                    .value(value.toLowerCase())
                    .isLower(true);
        }

        @SuppressWarnings({ "rawtypes", "unchecked" })
        public static Predicate likeLowerBuilder(CriteriaBuilder criteriaBuilder, Expression expression, String key,
                String value) {
            if(value == null) {
                return null;
            }
            return BaseSpecification.like(criteriaBuilder, expression,
                    criteriaLikeLowerBuilder(key, value).build());
        }
    
        public static SearchCriteria.SearchCriteriaBuilder criteriaGreaterThanOrEqualToBuilder(String key, Object value) {
            return SearchCriteria.builder()
                    .key(key)
                    .operation(GREATER_THAN_OR_EQUAL)
                    .value(value);
        }
        
        public static SearchCriteria.SearchCriteriaBuilder criteriaLessThanOrEqualToBuilder(String key, Object value) {
            return SearchCriteria.builder()
                    .key(key)
                    .operation(LESS_THAN_OR_EQUAL)
                    .value(value);
        }
        
        public static SearchCriteria.SearchCriteriaBuilder equalToBuilder(String key, Object value) {
            return SearchCriteria.builder()
                    .key(key)
                    .operation(EQUAL)
                    .value(value);
        }
        
        @SuppressWarnings("rawtypes")
        public static Predicate greaterThanOrEqualTo(CriteriaBuilder criteriaBuilder, Expression expression, String key,
                Object value) {
            if(value == null) {
                return null;
            }
            return BaseSpecification.greaterThanOrEqualTo(criteriaBuilder, expression,
                    criteriaGreaterThanOrEqualToBuilder(key, value).build());
        }
    
        @SuppressWarnings("rawtypes")
        public static Predicate lessThanOrEqualTo(CriteriaBuilder criteriaBuilder, Expression expression, String key,
                Object value) {
            if(value == null) {
                return null;
            }
            return BaseSpecification.lessThanOrEqualTo(criteriaBuilder, expression,
                    criteriaLessThanOrEqualToBuilder(key, value).build());
        }
        
        @SuppressWarnings("rawtypes")
        public static Predicate equalTo(CriteriaBuilder criteriaBuilder, Expression expression, String key,
                Object value) {
            if(value == null) {
                return null;
            }
            return BaseSpecification.equalTo(criteriaBuilder, expression,
                    equalToBuilder(key, value).build());
        }

    }

4.4 EmployeeSpecificationBuilder

Lớp xử lý logic nghiệp vụ search của chúng ta

        
    package com.repository.specification;

    import javax.persistence.criteria.Join;
    import javax.persistence.criteria.JoinType;
    import javax.persistence.criteria.Root;
    import javax.persistence.criteria.Subquery;
    import org.springframework.data.jpa.domain.Specification;
    import com.entity.Employee;
    import com.entity.Department;
    import com.repository.specification.BaseSpecification;
    import com.repository.specification.BaseSpecificationBuilder;

    import lombok.Builder;

    @Builder
    public class EmployeeSpecificationBuilder extends BaseSpecificationBuilder<Employee> {

        public static Specification<Employee> searchByDepartmentName(String departmentName) {
            return (root, query, cb) -> {
                // lấy ra 1 list Long trong câu subquery
                final Subquery<Long> subQuery = query.subquery(Long.class);
                // subquery lấy list Long từ Department
                final Root<Department> subRoot = subQuery.from(Department.class);
                // lấy ra trường id trong entity Department
                subQuery.select(subRoot.<Long>get("id"));
                // where lower(department.name) like lower(departmentName)
                subQuery.where(likeLowerBuilder(cb, subRoot.get("name"),
                    "name", departmentName));
                // select e from Employee e where e.departmentId in (Select d.id from Department d where lower(department.name) like '%' || lower(departmentName) || '%')
                return cb.in(root.get("departmentId")).value(subQuery);
            };
        }
    }
5. Test
        
    package com.service;

    import lombok.RequiredArgsConstructor;
    import com.repository.specification.EmployeeRepo;
    import com.repository.specification.EmployeeSpecificationBuilder;

    @RequiredArgsConstructor
    public class SpecificationTest {
    
        private final EmployeeRepo employeeRepo;

        public List getEmployeesByDepartmentName(String departmentName){
            return employeeRepo.findAll(EmployeeSpecificationBuilder.searchByDepartmentName(departmentName));
        }
    
    }

Ví dụ:

Department

ID NAME
1 Kinh doanh
2 Công nghệ
3 Vận hành

Employee

ID NAME DEPARTMENT_ID
1 Employee 1 2
2 Employee 2 1
3 Employee 3 2
4 Employee 4 2

Ví dụ nhập vào departmentName = "công nghệ" ta sẽ có danh sách user là

        
    [
        {id: 1, name: Employee 1, departmentId: 2},
        {id: 3, name: Employee 3, departmentId: 2},
        {id: 4, name: Employee 4, departmentId: 2}
    ]

Nếu muốn search bỏ dấu (cong nghe) thì ta phải thêm một vài đoạn code để bỏ dấu cho giá trị departmentName truyền vào + viết 1 function trong oracle để bỏ dấu (Sẽ giới thiệu với các bạn trong phần sau)

Nhận xét

Đăng nhận xét

Bài đăng phổ biến từ blog này

IBM BPM - Date

Bởi -
I. Giới thiệu Date là kiểu data đã quá quen thuộc với chúng ta. Trong IBM BPM thì Date có phần hơi khác một chút so với Date trong javascript thuần. II. Sử dụng 1. Tạo date theo ngày hiện tại // Cách 1 tw.local.date = new Date(); // Cách 2 tw.local.date = new tw.object.Date(); //Cách 3: tw.local.date = new TWDate(); // Chú ý: Có thể dùng biến var thay cho tw.local (var date ~ tw.local.date) 2. Tạo date theo ngày bất kỳ // Cách 1: tw.local.date = new TWDate("yyyy-MM-dd hh:mm:ss.S"); // Ví dụ: tw.local.date = new TWDate("2019-7-3 13:20:00.000"); // Cách 2: tw.local.date = new Date(date); // Ví dụ: tw.local.date1 = new TWDate("2019-7-3 13:20:00.000"); tw.local.date = new Date(tw.local.date1); // Cách 3: tw.local.date = new Date(year, month, day, hours, minutes, seconds, milliseco...
Đọc thêm

BPM WebSphere - Create Datasource (Connect to DB via JDBC)

Bởi -
Hình ảnh
I. Giới thiệu WebSphere là một application server mạnh mẽ của IBM cung cấp cho chúng ta nhiều chức năng có thể config trên giao diện người dùng (UI). Trong bài này sẽ hướng dẫn cách config đến một database thông qua jdbc url. II. Hướng dẫn Truy cập giao diện console WebSphere, từ menu trái truy cập theo thứ tự sau: 1. Thêm authentication 1.1 Security -> Global security 1.2 Java Authentication and Authorization Service -> J2C authentication data 1.3 Click chọn New để tạo thông tin authen đến DB 2. Config Datasource 2.1 Resources -> JDBC -> Data sources 2.2 Click chọn New và nhập các thông tin Data source name : Tên data source JNDI name : Tên jndi (tên này sẽ sử dụng để truyền input vào service SQL trong toolkit System Data) Chọn Next 2.3 Chọn JDBC provider tương ứng với DB cần connect (tạo mới hoặc chọn những provider sẵn có) ...
Đọc thêm

IBM BPM - Error: Save error

Bởi -
IBM BPM - Error: ArtifactSaveC E com.ibm.bpmsdk.model.autosave.persistence.ArtifactSaveCommand readAndApplyChanges ArtifactSaveC E com.ibm.bpmsdk.model.autosave.persistence.ArtifactSaveCommand readAndApplyChanges com.lombardisoftware.client.delegate.BusinessDelegateException: java.lang.NullPointerException com.ibm.bpmsdk.model.exception.ToolsModelException: com.lombardisoftware.client.delegate.BusinessDelegateException: java.lang.NullPointerException at com.ibm.bpmsdk.model.exception.ToolsModelException.asToolsModelException(ToolsModelException.java:47) at com.ibm.bpmsdk.tools.repo.client.RepositoryAccessHelper.findPOByPrimaryKey(RepositoryAccessHelper.java:1265) at com.ibm.bpmsdk.tools.repo.client.RepositoryAccessHelper.findByPrimaryKey(RepositoryAccessHelper.java:1096) at com.ibm.bpmsdk.tools.repo.client.RepositoryAccessHelper.findArtifactByPrimaryKeyForEdit(RepositoryAccessHelpe...
Đọc thêm