Spring jpa criteria example

-

 import jakarta.persistence.EntityManager;

import jakarta.persistence.TypedQuery;

import jakarta.persistence.criteria.CriteriaBuilder;

import jakarta.persistence.criteria.CriteriaQuery;

import jakarta.persistence.criteria.Join;

import jakarta.persistence.criteria.Path;

import jakarta.persistence.criteria.Predicate;

import jakarta.persistence.criteria.Root;

import java.math.BigDecimal;

import java.time.LocalDate;

import java.time.LocalDateTime;

import java.time.Month;

import java.util.List;

import java.util.Objects;

import org.springframework.data.jpa.domain.Specification;

import org.springframework.stereotype.Component;


@Component

public class TransactionCriteria {


private static final String TRANSACTION_POINT = "transactionPoint";

private static final String EARNED_POINTS = "earnedPoints";

private static final String CREATED_AT = "createdAt";

private static final String OPERATION_TYPE = "operationType";

private static final String CUSTOMER_ID = "customerId";

private static final String STATUS = "status";


public List<TransactionInfo> findTransactionsInfoByCriteria(

EntityManager entityManager,

String customerId,

TransactionInfoCriteria transactionInfoCriteria) {

if (Objects.isNull(transactionInfoCriteria.getMonth())

&& Objects.isNull(transactionInfoCriteria.getQuarter())) {

transactionInfoCriteria.setMonth(LocalDate.now().getMonthValue());

}


CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();


CriteriaQuery<TransactionInfo> criteriaQuery = criteriaBuilder.createQuery(TransactionInfo.class);

Root<TransactionEntity> root = criteriaQuery.from(TransactionEntity.class);


Path<String> operationType = root.get(OPERATION_TYPE);

criteriaQuery.select(

criteriaBuilder.construct(

TransactionInfo.class,

criteriaBuilder.count(root),

criteriaBuilder.sum(root.get(TRANSACTION_POINT).get(EARNED_POINTS)),

operationType)

);


Predicate customerIdPredicate = criteriaBuilder.equal(root.get(CUSTOMER_ID), customerId);

Predicate completedTransactionCriteria =

criteriaBuilder.equal(root.join(TRANSACTION_POINT).get(STATUS), COMPLETED);

Predicate earnedPointsGtThanZeroCriteria =

criteriaBuilder.gt(root.join(TRANSACTION_POINT).get(EARNED_POINTS), 0);


Predicate operationTypePredicate = root.get(OPERATION_TYPE).in(

OperationType.CARD,

OperationType.SIMPLIFIED_TAX_PAYMENT,

OperationType.GAMIFICATION_TRANSACTION_EVENT,

OperationType.QR,

OperationType.POS,

OperationType.UNIFIED_TAX_PAYMENT

);


Predicate yearPredicate = buildRangePredicate(criteriaBuilder, root,

transactionInfoCriteria.getYear(), transactionInfoCriteria, YEAR);

Predicate monthPredicate = buildRangePredicate(criteriaBuilder, root,

transactionInfoCriteria.getMonth(), transactionInfoCriteria, MONTH);

Predicate quarterPredicate = buildRangePredicate(criteriaBuilder, root,

transactionInfoCriteria.getQuarter(), transactionInfoCriteria, QUARTER);


criteriaQuery.where(criteriaBuilder.and(customerIdPredicate,

operationTypePredicate, yearPredicate, monthPredicate,

quarterPredicate, completedTransactionCriteria, earnedPointsGtThanZeroCriteria));


criteriaQuery.groupBy(operationType);


TypedQuery<TransactionInfo> query = entityManager.createQuery(criteriaQuery);

return query.getResultList();


}


public List<TransactionInfo> findTransactionsInfoByCriteriaV2(

EntityManager entityManager,

String customerId,

TransactionInfoCriteria transactionInfoCriteria) {

if (Objects.isNull(transactionInfoCriteria.getMonth())

&& Objects.isNull(transactionInfoCriteria.getQuarter())) {

transactionInfoCriteria.setMonth(LocalDate.now().getMonthValue());

}


CriteriaBuilder criteriaBuilderV2 = entityManager.getCriteriaBuilder();


CriteriaQuery<TransactionInfo> criteriaQuery = criteriaBuilderV2.createQuery(TransactionInfo.class);

Root<TransactionEntity> root = criteriaQuery.from(TransactionEntity.class);


Path<String> operationType = root.get(OPERATION_TYPE);

criteriaQuery.select(

criteriaBuilderV2.construct(

TransactionInfo.class,

criteriaBuilderV2.count(root),

criteriaBuilderV2.sum(root.get(TRANSACTION_POINT).get(EARNED_POINTS)),

operationType)

);


Predicate customerIdPredicate = criteriaBuilderV2.equal(root.get(CUSTOMER_ID), customerId);

Predicate completedTransactionCriteria =

criteriaBuilderV2.equal(root.join(TRANSACTION_POINT).get(STATUS), COMPLETED);

Predicate earnedPointsGtThanZeroCriteria =

criteriaBuilderV2.gt(root.join(TRANSACTION_POINT).get(EARNED_POINTS), 0);


Predicate yearPredicate = buildRangePredicate(criteriaBuilderV2, root,

transactionInfoCriteria.getYear(), transactionInfoCriteria, YEAR);

Predicate monthPredicate = buildRangePredicate(criteriaBuilderV2, root,

transactionInfoCriteria.getMonth(), transactionInfoCriteria, MONTH);

Predicate quarterPredicate = buildRangePredicate(criteriaBuilderV2, root,

transactionInfoCriteria.getQuarter(), transactionInfoCriteria, QUARTER);


criteriaQuery.where(criteriaBuilderV2.and(customerIdPredicate,

yearPredicate, monthPredicate,

quarterPredicate, completedTransactionCriteria, earnedPointsGtThanZeroCriteria));


criteriaQuery.groupBy(operationType);


TypedQuery<TransactionInfo> query = entityManager.createQuery(criteriaQuery);

return query.getResultList();


}


public Specification<TransactionEntity> ltThanId(Long lastTransactionId) {

return (root, query, cb) -> cb.lessThan(root.get("id"), lastTransactionId);

}


public Specification<TransactionEntity> ltThanCreatedAt(LocalDateTime createdAt) {

return (root, query, cb)

-> cb.lessThan(root.get(CREATED_AT), createdAt);

}


public Specification<TransactionEntity> byCustomerId(String customerId) {

return (root, query, builder) -> builder.equal(root.get(CUSTOMER_ID), customerId);

}


public Specification<TransactionEntity> transactionPointIsNotNullAndEarnedPointsGreaterThanZero() {

return (root, query, builder) -> {

Join<TransactionEntity, TransactionPointEntity> transactionPointJoin = root.join(TRANSACTION_POINT);

return builder.and(

builder.isNotNull(transactionPointJoin),

builder.greaterThan(transactionPointJoin.get(EARNED_POINTS), BigDecimal.ZERO)

);

};

}


public Specification<TransactionEntity> transactionPointIsNotNullAndEarnedPointsGreaterThanZeroV2() {

return (root, query, builder) -> {

Join<TransactionEntity, TransactionPointEntity> transactionPointJoin = root.join(TRANSACTION_POINT);

return builder.and(

builder.isNotNull(transactionPointJoin),

builder.greaterThan(transactionPointJoin.get(EARNED_POINTS), BigDecimal.ZERO),

builder.not(transactionPointJoin.get(STATUS).in(REFUNDED, COMPLETED_REFUND))

);

};

}


public Specification<TransactionEntity> byOperationType(OperationType operationType) {

return (root, query, builder)

-> builder.equal(root.get(OPERATION_TYPE), operationType);

}


public Specification<TransactionEntity> byAllowedOperationTypes() {

return (root, query, builder) -> root.get(OPERATION_TYPE).in(

OperationType.CARD,

OperationType.SIMPLIFIED_TAX_PAYMENT,

OperationType.GAMIFICATION_TRANSACTION_EVENT,

OperationType.QR,

OperationType.POS,

OperationType.UNIFIED_TAX_PAYMENT

);

}


public Specification<TransactionEntity> byYear(Integer year) {

LocalDateTime startOfYear = LocalDateTime.of(year, 1, 1, 0, 0, 0);

var endOfYear = LocalDateTime.of(year, 12, 31, 23, 59, 59);

return (root, query, builder) ->

builder.between(root.get(CREATED_AT), startOfYear, endOfYear);

}


public Specification<TransactionEntity> byMonth(Integer year, Integer month) {

LocalDateTime startOfMonth = LocalDateTime.of(year, month, 1, 0, 0, 0);

LocalDateTime endOfMonth = startOfMonth.withDayOfMonth(startOfMonth.toLocalDate().lengthOfMonth())

.withHour(23).withMinute(59).withSecond(59);


return (root, query, builder) -> builder.between(

root.get(CREATED_AT), startOfMonth, endOfMonth);

}


public Specification<TransactionEntity> byQuarter(Integer year, Integer quarter) {

return (root, query, builder) -> {

Month startMonth = Month.of((quarter - 1) * 3 + 1);

LocalDateTime startOfQuarter = LocalDateTime.of(year, startMonth, 1, 0, 0, 0);

LocalDateTime endOfQuarter = startOfQuarter.plusMonths(2)

.withDayOfMonth(startOfQuarter.plusMonths(2).toLocalDate().lengthOfMonth())

.withHour(23).withMinute(59).withSecond(59);


return builder.between(root.get(CREATED_AT), startOfQuarter, endOfQuarter);

};

}


}






import jakarta.persistence.EntityManager;

import jakarta.persistence.TypedQuery;

import jakarta.persistence.criteria.CriteriaBuilder;

import jakarta.persistence.criteria.CriteriaQuery;

import jakarta.persistence.criteria.Path;

import jakarta.persistence.criteria.Predicate;

import jakarta.persistence.criteria.Root;

import java.time.LocalDate;

import java.time.LocalDateTime;

import java.time.Month;

import java.util.List;

import java.util.Objects;

import org.springframework.data.jpa.domain.Specification;

import org.springframework.stereotype.Component;


@Component

public class OperationCriteria {


private static final String CREATED_AT = "createdAt";


public List<TransactionInfo> findOperationsInfoByCriteria(

EntityManager entityManager,

Long accountId,

TransactionInfoCriteria transactionInfoCriteria) {

if (Objects.isNull(transactionInfoCriteria.getMonth())

&& Objects.isNull(transactionInfoCriteria.getQuarter())) {

transactionInfoCriteria.setMonth(LocalDate.now().getMonthValue());

}


CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();


CriteriaQuery<TransactionInfo> criteriaQuery = criteriaBuilder.createQuery(TransactionInfo.class);

Root<OperationEntity> root = criteriaQuery.from(OperationEntity.class);


Path<String> operationType = root.get("type");

criteriaQuery.select(

criteriaBuilder.construct(

TransactionInfo.class,

criteriaBuilder.count(root),

criteriaBuilder.sum(root.get("amount")),

operationType)

);


Predicate customerIdPredicate = criteriaBuilder.equal(

root.join("loyaltyAccount").get("id"), accountId);

Predicate completedTransactionCriteria =

criteriaBuilder.equal(root.get("status"), SENT_TO_CUSTOMER_SUCCESS);

Predicate earnedPointsGtThanZeroCriteria =

criteriaBuilder.gt(root.get("amount"), 0);


Predicate yearPredicate = buildRangePredicate(criteriaBuilder, root,

transactionInfoCriteria.getYear(), transactionInfoCriteria, YEAR);

Predicate monthPredicate = buildRangePredicate(criteriaBuilder, root,

transactionInfoCriteria.getMonth(), transactionInfoCriteria, MONTH);

Predicate quarterPredicate = buildRangePredicate(criteriaBuilder, root,

transactionInfoCriteria.getQuarter(), transactionInfoCriteria, QUARTER);


criteriaQuery.where(criteriaBuilder.and(customerIdPredicate,

yearPredicate, monthPredicate,

quarterPredicate, completedTransactionCriteria, earnedPointsGtThanZeroCriteria));


criteriaQuery.groupBy(operationType);


TypedQuery<TransactionInfo> query = entityManager.createQuery(criteriaQuery);

return query.getResultList();


}


public Specification<OperationEntity> ltThanId(Long lastOperationId) {

return (root, query, cb) -> cb.lessThan(root.get("id"), lastOperationId);

}


public Specification<OperationEntity> ltThanCreatedAt(LocalDateTime createdAt) {

return (root, query, cb)

-> cb.lessThan(root.get(CREATED_AT), createdAt);

}


public Specification<OperationEntity> byAccountId(Long accountId) {

return (root, query, builder)

-> builder.equal(root.join("loyaltyAccount").get("id"), accountId);

}


public Specification<OperationEntity> byOperationType(OperationType operationType) {

return (root, query, builder) -> builder.equal(root.get("type"), operationType);

}


public Specification<OperationEntity> byYear(Integer year) {

LocalDateTime startOfYear = LocalDateTime.of(year, 1, 1, 0, 0, 0);

var endOfYear = LocalDateTime.of(year, 12, 31, 23, 59, 59);

return (root, query, builder) ->

builder.between(root.get(CREATED_AT), startOfYear, endOfYear);

}


public Specification<OperationEntity> byMonth(Integer year, Integer month) {

LocalDateTime startOfMonth = LocalDateTime.of(year, month, 1, 0, 0, 0);

LocalDateTime endOfMonth = startOfMonth.withDayOfMonth(startOfMonth.toLocalDate().lengthOfMonth())

.withHour(23).withMinute(59).withSecond(59);


return (root, query, builder) -> builder.between(

root.get(CREATED_AT), startOfMonth, endOfMonth);

}


public Specification<OperationEntity> byQuarter(Integer year, Integer quarter) {

return (root, query, builder) -> {

Month startMonth = Month.of((quarter - 1) * 3 + 1);

LocalDateTime startOfQuarter = LocalDateTime.of(year, startMonth, 1, 0, 0, 0);

LocalDateTime endOfQuarter = startOfQuarter.plusMonths(2)

.withDayOfMonth(startOfQuarter.plusMonths(2).toLocalDate().lengthOfMonth())

.withHour(23).withMinute(59).withSecond(59);


return builder.between(root.get(CREATED_AT), startOfQuarter, endOfQuarter);

};

}

}






import jakarta.persistence.criteria.CriteriaBuilder;

import jakarta.persistence.criteria.Predicate;

import jakarta.persistence.criteria.Root;

import java.time.LocalDate;

import java.time.LocalDateTime;

import java.util.Calendar;

import lombok.AccessLevel;

import lombok.NoArgsConstructor;


@NoArgsConstructor(access = AccessLevel.PRIVATE)

public class CriteriaUtil {


private static final String CREATED_AT = "createdAt";


public static Predicate buildRangePredicate(CriteriaBuilder criteriaBuilder, Root<?> root,

Integer value, TransactionInfoCriteria transactionInfoCriteria,

DateFilter dateFilter) {

if (value == null) {

return criteriaBuilder.conjunction();

}

return criteriaBuilder.and(

criteriaBuilder.greaterThanOrEqualTo(root.get(CREATED_AT),

getDateTime(value, transactionInfoCriteria, true, dateFilter)),

criteriaBuilder.lessThanOrEqualTo(root.get(CREATED_AT),

getDateTime(value, transactionInfoCriteria, false, dateFilter))

);

}


public static LocalDateTime getDateTime(Integer value,

TransactionInfoCriteria transactionInfoCriteria,

boolean isStartOfRange,

DateFilter dateFilter) {

Calendar calendar = initializeCalendar(isStartOfRange);


switch (dateFilter) {

case YEAR:

setYearRange(calendar, value, isStartOfRange);

break;

case MONTH:

setMonthRange(calendar, value, transactionInfoCriteria, isStartOfRange);

break;

case QUARTER:

setQuarterRange(calendar, value, transactionInfoCriteria, isStartOfRange);

break;

default:

throw new IllegalArgumentException("Unsupported date filter: " + dateFilter);

}


return LocalDateTime.ofInstant(calendar.toInstant(), calendar.getTimeZone().toZoneId());

}


private static Calendar initializeCalendar(boolean isStartOfRange) {

Calendar calendar = Calendar.getInstance();

calendar.set(Calendar.HOUR_OF_DAY, isStartOfRange ? 0 : 23);

calendar.set(Calendar.MINUTE, isStartOfRange ? 0 : 59);

calendar.set(Calendar.SECOND, isStartOfRange ? 0 : 59);

calendar.set(Calendar.MILLISECOND, isStartOfRange ? 0 : 999);

return calendar;

}


private static void setYearRange(Calendar calendar, Integer value, boolean isStartOfRange) {

calendar.set(Calendar.YEAR, value);

calendar.set(Calendar.MONTH, isStartOfRange ? Calendar.JANUARY : Calendar.DECEMBER);

calendar.set(Calendar.DAY_OF_MONTH, isStartOfRange ? 1 : 31);

}


private static void setMonthRange(Calendar calendar, Integer value,

TransactionInfoCriteria transactionInfoCriteria, boolean isStartOfRange) {

int year = transactionInfoCriteria.getYear() != null

? transactionInfoCriteria.getYear() : LocalDate.now().getYear();

calendar.set(Calendar.YEAR, year);

calendar.set(Calendar.MONTH, value - 1);

calendar.set(Calendar.DAY_OF_MONTH, isStartOfRange

? 1 : calendar.getActualMaximum(Calendar.DAY_OF_MONTH));

}


private static void setQuarterRange(Calendar calendar, Integer value,

TransactionInfoCriteria transactionInfoCriteria, boolean isStartOfRange) {

int year = transactionInfoCriteria.getYear() != null

? transactionInfoCriteria.getYear() : LocalDate.now().getYear();

calendar.set(Calendar.YEAR, year);

int month = isStartOfRange ? (value - 1) * 3 : (value * 3) - 1;

calendar.set(Calendar.MONTH, month);

calendar.set(Calendar.DAY_OF_MONTH, isStartOfRange

? 1 : calendar.getActualMaximum(Calendar.DAY_OF_MONTH));

}


}






import java.util.List;

import lombok.AccessLevel;

import lombok.NoArgsConstructor;

import org.springframework.data.jpa.domain.Specification;


@NoArgsConstructor(access = AccessLevel.PRIVATE)

public class SpecificationUtil {


public static <T> Specification<T> transformToAnd(List<Specification<T>> specs) {

return specs.stream().reduce(Specification::and).orElse(null);

}

}


Comments

Post a Comment

Popular posts from this blog

Installation instructions for some programs on linux ubuntu

-
Install tomcat9 on linux ubuntu: https://phoenixnap.com/kb/how-to-install-tomcat-ubuntu https://www.digitalocean.com/community/tutorials/how-to-install-apache-tomcat-8-on-ubuntu-16-04 Install Ubuntu20 alongside Windows 10 as dual boot https://www.youtube.com/watch?v=-iSAyiicyQY Create Windows 10 bootable flash in ubuntu https://www.youtube.com/watch?v=rHFR-L7CtOY Install Windows Fonts on ubuntu https://ostechnix.com/install-microsoft-windows-fonts-ubuntu-16-04/ Install Configuration for Ubuntu https://wiki.ubuntu-tr.net/index.php?title=Ana_Sayfa JDK11 https://www.youtube.com/watch?v=72mz0-98vg8 jdk1.8 and netbeans 8.2 Install : https://www.youtube.com/watch?v=hyw0CHKGQ_Y UnInstall: https://netbeans.org/community/releases/82/install.html#uninstall_sollinux MYSQL https://www.youtube.com/watch?v=ug0TFsort24 GIT https://www.youtube.com/watch?v=ZMgLZUYd8Cw&t=522s Skype https://www.youtube.com/watch?v=58qlQREr_3E Docker https://www.youtube.com/watch?v=W7BvS942UZA h...
Read more

Hibernate and Application Performance

-
 hibernate 2 cur cache memory var: L1(Level bir) burada entitymanager a aiddir. bazadan gelen entityler cacheda saxlanir ve her entitymanagere xasdir L2(level 2) burada ise umumiyyetle EntityManagerFactory ye xidmet eden entiymanagerler arasinda shared cache memorydir ve level 1 den daha uzun muddetlidir level 2 cache i aktivleshdirmek uchun dependency elave edilir projecte ve 1-2 config edilir Javada 4 cur reference type var : https://dzone.com/articles/reference-types-java-part-1 Strong references. Soft references. Weak references. Phantom references entitymanager de weak referance var , ve o strongla muqayisede GC terefinden istenilen an siline biler ram lazim olsa. soft referance - strongdan zeif , weakdan gucludur ve GC onu ancaq en vacib hallarda sile biler, weak i her memory chatmiyanda sile biler ama softu ancaq outofmemory alacagi teqdirde silib ishlede biler ve soft un omru uzun olur . ve Level 2 cache da istifade edir bunu hibernate. burda da bele shey varki , LRU(last r...
Read more

write huge text into clob column in oracle

-
 varchar2 max character size is 32767 , that's why writing huge texts into column is problem. CLOB type is used for this, it can store 2,147,483,647 characters. But it is a bit problematic to write more than 32767 characters into CLOB typed column. For doing that either we should read from file or divide into small chunks. In example we will do this by chunks Firstly: package org.example; import java.io.*; import java.util.ArrayList; import java.util.List; public class FileChunker { private static final int CHUNK_SIZE = 21000; // Size of each chunk public static void main(String[] args) { String filePath = "/Users/bbb/Documents/aaa/new_license/license_en.html"; // Path to your file String outputDir = "/Users/bbb/Documents/aaa/new_license/chunks_en"; // Directory to save chunks try { List<String> chunks = splitFileIntoChunks (filePath); createFilesFromChunks (chunks, outputDir); System. out...
Read more