通常大多數專案，配置一個或多個資料庫運作，Spring boot 提供了 JPA為基礎了資料庫操作功能，實際應用程式需要對個別專案，需要設計特定的資料分類與格式，以Database Table方式存取，並對其做新增、刪除、編輯等處理。除了這需基本功能外，設計新的資料庫時，可以利用Spring Boot 配置JpaAuditorAware設定，自動加入Create By、Create Time、Last Modify By、 Last Modify Time等等，關於資料庫異動相關的紀錄(Mybatis使用AuditInterceptor)。
lewisyang
<!-- Spring Boot Security -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-security</artifactId>
</dependency>
<dependency>
<groupId>org.thymeleaf.extras</groupId>
<artifactId>thymeleaf-extras-springsecurity6</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.ldap</groupId>
<artifactId>spring-ldap-core</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.security</groupId>
<artifactId>spring-security-ldap</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.security</groupId>
<artifactId>spring-security-test</artifactId>
<scope>test</scope>
</dependency>
<!-- <dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-actuator</artifactId>
</dependency> -->
<!-- End Spring Boot Security -->
增加集中資料庫Audit相關設定與物件，放置於專案database跟目錄下的audit目錄 (tw.lewishome.webapp.database.audit)
增加Spring Boot Configuration，啟動 JAP Auditing功能的程式。
注意: auditorAwareRef = "jpaAuditorAware" 是客製化執行jpaAuditor的 Component名稱，但必須小寫開頭。
package tw.lewishome.webapp.database.audit;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
/**
*
* 此類別為 JPA 審計功能的設定類別，主要用於自動指定實體（Entity）建立者與修改者的資訊。
*
* 啟用 JPA 的審計功能，並指定 auditorAwareRef 為 "JpaAuditorAware"，
* 以便於在所有 JPA 資料來源（Datasource）中統一管理審計資訊。
*
* 此設定只需在專案中指定一次，即可對所有 JPA 資料來源生效。
*
* 適用於需要追蹤資料建立者與修改者的情境，例如：資料安全、稽覈、歷程記錄等。
*
* @author Lewis
*/
@Configuration
@EnableJpaAuditing(auditorAwareRef = "jpaAuditorAware") // 確認 Component 指定名稱(要小寫開頭)
public class JpaAuditorAwareConfig {
/**
* Fix for javadoc warning :
* use of default constructor, which does not provide a comment
* Constructs a new JpaAuditorAwareConfig instance.
* This is the default constructor, implicitly provided by the compiler
* if no other constructors are defined.
*/
public JpaAuditorAwareConfig() {
// Constructor body (can be empty)
}
}
package tw.lewishome.webapp.database.audit;
import java.io.Serializable;
import java.util.Date;
import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;
import org.springframework.data.annotation.CreatedBy;
import org.springframework.data.annotation.LastModifiedBy;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;
import com.fasterxml.jackson.annotation.JsonFormat;
import jakarta.persistence.Column;
import jakarta.persistence.EntityListeners;
import jakarta.persistence.MappedSuperclass;
import jakarta.persistence.Version;
import lombok.Data;
import tw.lewishome.webapp.base.utility.common.DateUtils;
/**
* 基礎實體稽覈類別，提供通用的稽覈欄位，適用於所有需要記錄新增、修改、刪除等操作的實體類別。
*
* 此類別包含以下稽覈資訊：
* <ul>
* <li><b>version</b>：變更次數，透過 JPA 的 @Version 標註實現樂觀鎖。</li>
* <li><b>createdBy</b>：新增者，系統自動從 Spring Security 取得登入使用者。</li>
* <li><b>createdDate</b>：新增時間，系統自動產生，格式為 yyyy-MM-dd HH:mm:ss。</li>
* <li><b>lastModifiedBy</b>：最後更新者，系統自動從 Spring Security 取得登入使用者。</li>
* <li><b>lastModifiedDate</b>：最後更新時間，系統自動產生，格式為 yyyy-MM-dd HH:mm:ss。</li>
* <li><b>deleted</b>：刪除註記，標示資料是否已刪除（邏輯刪除）。</li>
* </ul>
*
* 此類別需配合 Spring Data JPA
* 的稽覈功能（AuditingEntityListener）使用，並建議所有實體類別繼承此類別以統一管理稽覈欄位。
*
* @param <User> 使用者型別，通常為系統登入使用者的主鍵或帳號資訊。
* @author Lewis
* @since 1.0
*/
@Data
@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
public abstract class EntityAudit<User> implements Serializable {
/**
* Fix for javadoc warning :
* use of default constructor, which does not provide a comment
* Constructs a new EntityAudit instance.
* This is the default constructor, implicitly provided by the compiler
* if no other constructors are defined.
*/
public EntityAudit() {
// Constructor body (can be empty)
}
/** serialVersionUID */
private static final long serialVersionUID = 1L;
/** 變更次數 */
@Version
@Column(name = "version")
public Integer version;
/** 新增者JpaAuditingConfiguration自Spring Security取得Login User */
@CreatedBy
@Column(name = "createdBy", nullable = false, updatable = false, length = 32)
public User createdBy;
/** 新增時間 系統自動產生 */
@CreationTimestamp
@JsonFormat(pattern = DateUtils.YYYY_MM_DD_HH_MM_SS_DASH) // (pattern = "yyyy-MM-dd HH:mm:ss")
@Column(name = "createdDate", nullable = false, updatable = false)
public Date createdDate;
/** 最後更新者 JpaAuditingConfiguration自Spring Security取得Login User */
@LastModifiedBy
@Column(name = "lastModifiedBy", nullable = false, length = 32)
public User lastModifiedBy;
/** 最後更新時間 系統自動產生 */
@UpdateTimestamp
@JsonFormat(pattern = DateUtils.YYYY_MM_DD_HH_MM_SS_DASH) // (pattern = "yyyy-MM-dd HH:mm:ss")
@Column(name = "lastModifiedDate", nullable = false)
public Date lastModifiedDate;
/** 刪除註記 Delete */
@Column(name = "deleted")
public Boolean deleted = false;
}
package tw.lewishome.webapp.database.audit;
import java.util.Optional;
import org.springframework.data.domain.AuditorAware;
import org.springframework.lang.NonNull;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Component;
/**
*
* JpaAuditorAware 是一個用於 JPA 審計（Audit）功能的元件，實作
* {@link org.springframework.data.domain.AuditorAware} 介面，
* 主要用來在資料庫新增或修改操作時，取得目前系統登入使用者的 ID，並自動填入審計欄位（如 createdBy、modifiedBy）。
*
* 本類別會嘗試透過 Spring Security 的
* {@link org.springframework.security.core.context.SecurityContextHolder}
* 取得目前認證的使用者名稱。
* 若無法取得（例如尚未登入或發生例外），則預設回傳 "System" 作為審計使用者。
*
* @author Lewis
*
*/
// @Component("auditorAware") // 指定Component 特別名稱，除非有必要，建議不指定。
@Component // 不指定名稱，自動使用 Class 名稱，但改為小寫開頭。
public class JpaAuditorAware implements AuditorAware<String> {
/**
* Fix for javadoc warning :
* use of default constructor, which does not provide a comment
*
* Constructs a new JpaAuditorAware instance.
* This is the default constructor, implicitly provided by the compiler
* if no other constructors are defined.
*/
public JpaAuditorAware() {
// Constructor body (can be empty)
}
/**
*
*
* 取得系統登入使用者ID，作為JPA 新增或修改時指定 Audit 資料
*/
@Override
@NonNull
public Optional<String> getCurrentAuditor() {
/*
* for spring security , get the currently logged username from
* SecurityContextHolder.getContext().getAuthentication().getName()
* else (set to System)
*/
Optional<String> rtnUserId = Optional.of("Unknown User Id");
try {
String userId = SecurityContextHolder.getContext().getAuthentication().getName();
rtnUserId = Optional.of(userId);
} catch (Exception ex) {
// System.out.println("JPA audit user not found");
rtnUserId = Optional.of("Unknown User");
// logged not found from SecurityContextHolder Set to System
}
return ((rtnUserId.isPresent()) ? rtnUserId : rtnUserId);
}
}
調整部分:
// String userId = "system"; // Default or fallback user ID
String userId = SecurityContextHolder.getContext().getAuthentication().getName(); // From ThreadLocal or SecurityContext
if (StringUtils.isBlank(userId)) {
userId = "unknown user";
}
調整完整的AuditInterceptor.java
package tw.lewishome.webapp.database.audit;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
// import java.lang.reflect.Field;
import java.util.Collection;
import java.util.Date;
import java.util.Map;
import java.util.Properties;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Component;
/**
* MyBatis Interceptor：負責在執行 INSERT / UPDATE 操作時，自動為實作 EntityAudit 的實體填入稽核欄位（created/modified 與 user）。
*
* 功能摘要：
* - 攔截 Executor 的 query 與 update 呼叫（透過 MyBatis @Intercepts / @Signature 設定）。
* - 對於 INSERT：
* - 設定 createdDate 與 lastModifiedDate 為當前時間（new Date()）。
* - 設定 createdBy 與 lastModifiedBy 為目前使用者（由 Spring Security 的 SecurityContextHolder 擷取）。
* - 對於 UPDATE：
* - 設定 lastModifiedDate 與 lastModifiedBy。
* - 對於 DELETE：不做任何處理，直接放行（invocation.proceed()）。
*
* 參數處理：
* - 支援以下情形的參數格式：
* - 單一 EntityAudit 實作物件。
* - Collection（例如 List<EntityAudit>）中每一個元素為 EntityAudit 的情形。
* - Map（MyBatis 在某些情況下會封裝為 Map），會遍歷 Map 的 value，處理其中為 EntityAudit 或為 Collection 的情形。
* - 若參數中存在非 EntityAudit 物件，則不會嘗試修改該物件。
*
* 使用者與例外情況：
* - 會嘗試從 SecurityContextHolder.getContext().getAuthentication().getName() 取得使用者名稱。
* - 若擷取使用者時發生例外，會將使用者名稱設為 "unknown user"（預設變數初始值為 "System"，但若發生例外最終會使用 "unknown user"）。
* - 建議在使用非同步或背景執行緒時，確保 SecurityContext 的正確傳遞（否則可能取得不到使用者資訊）。
*
* 設計與限制：
* - Interceptor 本身為無狀態（stateless）實作，適合在多執行緒環境中共用。
* - 只修改記憶體中的物件欄位值；實際資料庫變更仍由 MyBatis 的後續執行（invocation.proceed()）負責。
* - 不會遞迴處理深層巢狀物件（例如物件內再包含其他非 collection/map 的物件），僅處理直接為 EntityAudit 或 collection/map 包含 EntityAudit 的情形。
* - 若需要不同時間來源（例如 UTC 時間或高精度時間），可在此處改為注入時間服務或使用 Instant 等替代實作。
* - 適用於 MyBatis 3.x 版本。
*/
@Intercepts({
@Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class,
RowBounds.class, ResultHandler.class }),
@Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class,
RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class }),
@Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.class })
})
@Component
public class AuditInterceptor implements Interceptor {
@SuppressWarnings("rawtypes")
@Override
public Object intercept(Invocation invocation) throws Throwable {
MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
Object parameter = invocation.getArgs()[1];
// String sqlCommandType = mappedStatement.getSqlCommandType().name();
SqlCommandType sqlCommandType = mappedStatement.getSqlCommandType();
if (SqlCommandType.DELETE == sqlCommandType) {
return invocation.proceed();
}
String userId = "System";
try {
// From ThreadLocal or SecurityContext
userId = SecurityContextHolder.getContext().getAuthentication().getName();
} catch (Exception e) {
// set default user as unknown if any issue occurs
userId = "unknown user";
}
Date now = new Date();
// Handle single entity
if (parameter instanceof EntityAudit) {
handleEntity((EntityAudit) parameter, sqlCommandType, userId, now);
}
// Handle collections (e.g., List<EntityAudit>)
else if (parameter instanceof Collection) {
Collection<?> collection = (Collection<?>) parameter;
for (Object item : collection) {
if (item instanceof EntityAudit) {
handleEntity((EntityAudit) item, sqlCommandType, userId, now);
}
}
}
// Handle Map (MyBatis might wrap the parameter in a Map)
else if (parameter instanceof Map) {
Map<?, ?> paramMap = (Map<?, ?>) parameter;
for (Object value : paramMap.values()) {
if (value instanceof Collection) {
Collection<?> collection = (Collection<?>) value;
for (Object item : collection) {
if (item instanceof EntityAudit) {
handleEntity((EntityAudit) item, sqlCommandType, userId, now);
}
}
} else if (value instanceof EntityAudit) {
handleEntity((EntityAudit) value, sqlCommandType, userId, now);
}
}
}
return invocation.proceed();
}
@SuppressWarnings({ "rawtypes", "unchecked" })
private void handleEntity(EntityAudit entity, SqlCommandType sqlCommandType, String userId, Date now) {
if (SqlCommandType.INSERT == sqlCommandType) {
entity.setCreatedDate(now);
entity.setLastModifiedDate(now);
entity.setCreatedBy(userId);
entity.setLastModifiedBy(userId);
} else if (SqlCommandType.UPDATE == sqlCommandType) {
entity.setLastModifiedDate(now); // Optional: set on insert
entity.setLastModifiedBy(userId);
}
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
// No properties needed
}
}
package tw.lewishome.webapp.database.audit;
/**
* 提供 MyBatis SQL 組合用的Audit欄位字串常量說明。
*
* 這個類別定義了兩個可重用的常量字串，用於在建構 SQL 時插入標準的Audit欄位與對應的 MyBatis 參數佔位符。
*
* 常量說明：
* - AUDIT_COLUMN_STRING：包含資料表中的Audit欄位名稱，順序為 created_by, created_date, last_modified_by, last_modified_date。
* - AUDIT_FIELD_STRING：包含對應的 MyBatis 參數，順序為 #{createdBy}, #{createdDate}, #{lastModifiedBy}, #{lastModifiedDate}。
*
* 使用情境：
* - 在 INSERT 或 UPDATE 語句中可直接拼接這兩個常量以保持欄位與參數名稱一致性，例如：
* INSERT INTO table_name (..., <Audit欄位>) VALUES (..., <Audit參數>)。
* - 適合在多處 SQL 模板中重複使用，以減少手動輸入欄位名稱導致的不一致或錯誤。
*/
public class AuditMybatisSqlString {
public static final String AUDIT_COLUMN_STRING = "created_by, created_date, last_modified_by, last_modified_date";
public static final String AUDIT_FIELD_STRING = "#{createdBy}, #{createdDate}, #{lastModifiedBy}, #{lastModifiedDate}";
public static final String AUDIT_UPDATE_FIELD_STRING = "last_modified_by = #{lastModifiedBy}, last_modified_date = #{lastModifiedDate}";
}
一般spring boot Jpa資料庫的設計，主要區分作為Key的欄位以及一般資料欄位，而Key欄位需要設計為唯一不可重複值。Key若為單一欄位則使用annotation以@Id宣告，Key若為多個欄位組合則須要以物件型式使用annotation以@EmbeddedId宣告，後續發顫應用系統時，發現每個Entity都需要提供一組新增、修改、刪除等功能(稱CRUD)，基於簡化後續程式碼維護或依板模自動產生CRUD程式，個人習慣建置DataKey的inner Class於該entity內的做為key欄位物件並以@EmbeddedId宣告，而DataKey欄位可以一個欄位或多個欄位並以@Embeddable宣告，基本上entity程式結構都統一使用格式:
public class SysUserProfileEntity extends EntityAudit<String> {
/** Entity Key */
@EmbeddedId
public DataKey dataKey;
....其他欄位
@Embeddable
public static class DataKey implements Serializable {
public String userId = ""; //
....其他Key欄位
}
/** MyBatis TypeHandler for 處理 DataKey */
public static class DataKeyHandler extends BaseTypeHandler<DataKey> {
@Override
.... implements BaseTypeHandler methods
}
}
** 新增SysUserProfileEntity.java於資料庫entity子目錄中:
package tw.lewishome.webapp.database.primary.entity;
import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import jakarta.persistence.Column;
import jakarta.persistence.Embeddable;
import jakarta.persistence.EmbeddedId;
import jakarta.persistence.Entity;
import jakarta.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import tw.lewishome.webapp.database.audit.EntityAudit;
/**
* SysUserProfile Table Entity
*
* @author lewis
* @version :
*/
@Entity
@Table(name = "sysuserprofile") // 資料庫的 Table 名稱
@Data
@EqualsAndHashCode(callSuper = true)
@AllArgsConstructor
public class SysUserProfileEntity extends EntityAudit<String> {
/**
* Fix for javadoc warning :
* use of default constructor, which does not provide a comment
*
* Constructs a new SysUserProfileEntity instance.
* This is the default constructor, implicitly provided by the compiler
* and can be used to create a new instance of the class.
*/
public SysUserProfileEntity() {
// Constructor body (can be empty)
}
/** Entity Key */
@EmbeddedId
public DataKey dataKey;
/** 使用者名稱 */
@Column(name = "NAME", length = 64)
public String userName = "";
/** 使用者部門 */
@Column(name = "DEPT", length = 64)
public String userDept = "";
/** 使用者分機 */
@Column(name = "EXT", length = 32)
public String userExt = "";
/** 使用者Email */
@Column(name = "EMAIL", length = 128)
public String userEmail = "";
/** 使用者密碼 */
@Column(name = "PASSWORD", length = 1024)
public String userPassword = "";
/** 使用者職等 0 - 9 (設定於 SYSTEM Constants) */
@Column(name = "LEVEL", length = 2)
public String userLevel = "";
/** 有效使用者 */
@Column(name = "LastAuth", length = 16)
public String userLastAuth = "";
/** 使用者上次登入佈局 */
@Column(name = "userMenuLayoutTop")
public Boolean userMenuLayoutTop = true;
/** 有效使用者 */
@Column(name = "ISVAILD")
public Boolean userIsVaild = true;
/** 使用者過期 */
@Column(name = "userIsExpired")
public boolean isExpired = false;
/** 使用者鎖定 */
@Column(name = "userIsLocked")
public boolean isLocked = false;
/**
* Entity Key
*
*/
@Embeddable
@Data
public static class DataKey implements Serializable {
/**
* Fix for javadoc warning :
* use of default constructor, which does not provide a comment
*
* Constructs a new DataKey instance.
* This is the default constructor, implicitly provided by the compiler
* and can be used to create a new instance of the class.
*/
public DataKey() {
// Constructor body (can be empty)
}
private static final long serialVersionUID = 1L;
/** 使用者代碼 */
@Column(name = "USERID", length = 64)
public String userId = ""; //
}
/** MyBatis TypeHandler for DataKey */
public static class DataKeyHandler extends BaseTypeHandler<DataKey> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, DataKey parameter, JdbcType jdbcType)
throws SQLException {
try {
ps.setString(1, parameter.getUserId());
} catch (Exception e) {
e.printStackTrace();
}
}
@Override
public DataKey getNullableResult(ResultSet rs, String columnName) throws SQLException {
DataKey dataKey = new DataKey();
if (rs.wasNull() == false) {
dataKey.setUserId(rs.getString("userid"));
}
return dataKey;
}
@Override
public DataKey getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
DataKey dataKey = new DataKey();
if (rs.wasNull() == false) {
dataKey.setUserId(rs.getString(1));
}
return dataKey;
}
@Override
public DataKey getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
DataKey dataKey = new DataKey();
if (cs.wasNull() == false) {
dataKey.setUserId(cs.getString(1));
}
return dataKey;
}
}
}
Spring Boot 自動產生的SysUserProfileEntity資料庫，以工具查詢結果:
** 新增SysUserProfileRepository.java於資料庫repository子目錄中
package tw.lewishome.webapp.database.primary.repository;
import java.util.List;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.NativeQuery;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import tw.lewishome.webapp.database.primary.entity.SysUserProfileEntity;
/**
* SysUserProfile JPA Repository
*
* @author lewis
* @version $Id: $Id
*/
@Transactional
@Repository
public interface SysUserProfileRepository extends JpaRepository<SysUserProfileEntity, SysUserProfileEntity.DataKey>,
JpaSpecificationExecutor<SysUserProfileEntity> {
/**
* JPA Named Query
* 以Method名稱的操作資料庫資料 (inner Class DataKey 的欄位須以底線連接標示)
* find By DataKey_UserId .
*
* @param userId a String object
* @return a
* {@link tw.lewishome.webapp.database.primary.entity.SysUserProfileEntity}
* object
*/
public SysUserProfileEntity findByDataKey_UserId(String userId);
/**
* JPA Named Query
* 以Method名稱的操作資料庫資料
*
* find By userName Like IgnoreCase and UserDept IsContaining
*
* 因為複雜一點的條件，Method名稱會變很長，不習慣。
*
* @param userNameLike a String object
* @param userDeptContain a String object
* @return a
* {@link tw.lewishome.webapp.database.primary.entity.SysUserProfileEntity}
* object
*/
public SysUserProfileEntity findByUserNameLikeIgnoreCaseAndUserDeptIsContaining(String userNameLike, String userDeptContain);
/**
* JPA PSQL
* 以 SQL語法，操作資料庫資料
*
* 以 Entity欄位作為 SQL查詢欄位，與系統使用欄位名稱一致。
* @param usernameVar user name
* @return List SysUserProfileEntity List
*/
@Query("SELECT u FROM SysUserProfileEntity u WHERE u.userName like :userNameParm")
public List<SysUserProfileEntity> findAllUserByUserName(@Param("userNameParm") String usernameVar);
/**
* JPA PSQL Native
*
* 以資料庫 Table 欄位作為 SQL查詢欄位，與系統使用欄位名稱不同，但可以利用 SQL 工具驗證語法。
* @param usernameVar user name
* @return List SysUserProfileEntity List
*/
@NativeQuery(value = "SELECT * FROM sysuserprofile WHERE name like :userNameParm")
public List<SysUserProfileEntity> findAllUserByUserName2(@Param("userNameParm") String usernameVar);
}
** 新增SysUserProfileSpecification.java 於資料庫specification子目錄中。
package tw.lewishome.webapp.database.primary.specification;
import org.apache.commons.lang3.StringUtils;
import org.springframework.data.jpa.domain.Specification;
import jakarta.persistence.criteria.Predicate;
import tw.lewishome.webapp.database.primary.entity.SysUserProfileEntity;
/**
*
* SysUserProfileSpecification class.
*
*
* @author lewis
*/
public class SysUserProfileSpecification {
// Private constructor to prevent instantiation
private SysUserProfileSpecification() {
throw new IllegalStateException("This is a Specification class and cannot be instantiated");
}
/**
*
* hasDeptLevel.
*
*
* @return a {@link org.springframework.data.jpa.domain.Specification} object
* @param name a String object
* @param userId a String object
*/
public static Specification<SysUserProfileEntity> getSysProfileByLikeIdandName(String name, String userId) {
return (root, query, criteriaBuilder) -> {
Predicate findDataPredicate = criteriaBuilder.disjunction(); // Start with no condition false
if (StringUtils.isNotBlank(userId)) {
findDataPredicate = criteriaBuilder.or(findDataPredicate,
criteriaBuilder.like(root.get("dataKey").get("userId"), "%" + userId + "%"));
}
if (StringUtils.isNotBlank(name)) {
findDataPredicate = criteriaBuilder.and(findDataPredicate,
criteriaBuilder.like(root.get("userName"), "%" + name + "%"));
}
return findDataPredicate;
};
}
}
** 新增 SysUserProfileMapper，於資料庫mybatis子目錄
package tw.lewishome.webapp.database.primary.mybatis;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
// import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.annotations.Update;
import tw.lewishome.webapp.database.audit.AuditMybatisSqlString;
import tw.lewishome.webapp.database.primary.entity.SysUserProfileEntity;
/**
* SysUserProfileMapper Mybatis Mapper Interface
*
* Provides CRUD operations for SysUserProfileEntity.
*
* @author lewis
*/
@Mapper
public interface SysUserProfileMapper {
/**
*
* findByAll.
*
* wiill refereance SysUserProfileMapper.xml
* @return a List object
*/
List<SysUserProfileEntity> findByAll();
/**
*
* findUserId.
*
* wiill refereance SysUserProfileMapper.xml
* @return a List object
*/
SysUserProfileEntity findUserId(@Param("parmUserId") String userId);
/**
* Mybatis will auto-handle EntityAudit fields,
* so we do not need to set them manually.
* but need declare them in the SQL.
*/
@Insert("INSERT INTO sysuserprofile (userid, name, dept, ext, email, password, level, last_auth, "
+ "user_menu_layout_top, isvaild, user_is_expired, user_is_locked, "
+ AuditMybatisSqlString.AUDIT_COLUMN_STRING + " ) VALUES ("
+ "#{dataKey.userId}, #{userName}, #{userDept}, #{userExt}, #{userEmail}, #{userPassword}, #{userLevel}, #{userLastAuth}, "
+ "#{userMenuLayoutTop}, #{userIsVaild}, #{isExpired}, #{isLocked} , " + AuditMybatisSqlString.AUDIT_FIELD_STRING + " )")
int insert(SysUserProfileEntity sysUserProfileEntity);
/**
* Update a SysUserProfileEntity.
*
* @param sysUserProfileEntity the entity with updated information
* @return the number of rows affected
*/
@Update("UPDATE sysuserprofile SET name = #{userName}, dept = #{userDept}, ext = #{userExt}, "
+ "email = #{userEmail}, password = #{userPassword}, level = #{userLevel}, last_auth = #{userLastAuth}, "
+ "user_menu_layout_top = #{userMenuLayoutTop}, isvaild = #{userIsVaild}, "
+ "user_is_expired = #{isExpired}, user_is_locked = #{isLocked} , "
+ AuditMybatisSqlString.AUDIT_UPDATE_FIELD_STRING
+ " WHERE userid = #{dataKey.userId}")
int update(SysUserProfileEntity sysUserProfileEntity);
/**
* Delete a SysUserProfileEntity by userId.
*
* @param userId the ID of the user to delete
* @return the number of rows affected
*/
@Delete("DELETE FROM sysuserprofile WHERE userid = #{userId}")
int deleteById(@Param("userId") String userId);
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="AuditFieldsResultMap">
<resultMap id="auditFields" type="tw.lewishome.webapp.database.audit.EntityAudit">
<result property="createdBy" column="created_by" />
<result property="createdDate" column="created_date" />
<result property="deleted" column="deleted" />
<result property="lastModifiedBy" column="last_modified_by" />
<result property="lastModifiedDate" column="last_modified_date" />
<result property="version" column="version" />
</resultMap>
</mapper>
6.. 於mybatis的 Resource 子目錄primary中 (src/main/resources/mybatis/primary)。
** 新增 SysUserProfileMapper.xml (SQL 定義)於Primary mapperLocationa目錄。
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="tw.lewishome.webapp.database.primary.mybatis.SysUserProfileMapper">
<resultMap id="userProfileMap"
type="tw.lewishome.webapp.database.primary.entity.SysUserProfileEntity"
extends="AuditFieldsResultMap.auditFields">
<result property="isExpired" column="user_is_expired" />
<result property="isLocked" column="user_is_locked" />
<result property="userDept" column="dept" />
<result property="userEmail" column="email" />
<result property="userPassword" column="ext" />
<result property="userIsVaild" column="isvaild" />
<result property="userLastAuth" column="last_auth" />
<result property="userLevel" column="level" />
<result property="userMenuLayoutTop" column="user_menu_layout_top" />
<result property="userName" column="name" />
<result property="userPassword" column="password" />
<association property="dataKey" javaType="tw.lewishome.webapp.database.primary.entity.SysUserProfileEntity$DataKey"
typeHandler="tw.lewishome.webapp.database.primary.entity.SysUserProfileEntity$DataKeyHandler">
</association>
</resultMap>
<select id="findByAll" resultMap="userProfileMap">
<!-- Select * from sysuserprofile cannot mapper into entity -->
<!-- because of the column name is not match the entity field name -->
<!-- So we need to specify the column name and the entity field name mapping -->
SELECT * FROM sysuserprofile
</select>
<select id="findUserId" resultMap="userProfileMap">
SELECT * FROM sysuserprofile
WHERE userid = #{parmUserId};
</select>
</mapper>
** 1. 新增 SysUserProfileRepositoryTest.java 於單元測試目錄(src/test/java) tw.lewishome.webapp.database.primary.repository 的 package
package tw.lewishome.webapp.database.primary.repository;
import static org.junit.jupiter.api.Assertions.*;
import java.util.List;
import java.util.Optional;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.TestPropertySource;
import tw.lewishome.webapp.database.primary.entity.SysUserProfileEntity;
/**
* SysUserProfileRepository Integration Tests
*
* Tests for SysUserProfileRepository using real repository with
*
* @BeforeEach to add test data and @AfterEach to clean up.
*
* @author lewis
*/
@SpringBootTest
@TestPropertySource(locations = "classpath:application.properties")
public class SysUserProfileRepositoryTest {
@Autowired
private SysUserProfileRepository sysUserProfileRepository;
private SysUserProfileEntity testEntity;
private SysUserProfileEntity testEntity2;
/**
* Setup test data before each test
* Creates test user profile entities and saves them to database
* Also cleans up any existing test data to ensure clean state
*/
@BeforeEach
void setUp() {
// Clean up any existing test data
cleanupTestUser("TEST_USER_001");
cleanupTestUser("TEST_USER_002");
// Create first test entity
testEntity = new SysUserProfileEntity();
SysUserProfileEntity.DataKey dataKey1 = new SysUserProfileEntity.DataKey();
dataKey1.setUserId("TEST_USER_001");
testEntity.setDataKey(dataKey1);
testEntity.setUserName("Test User One");
testEntity.setUserEmail("testuser1@example.com");
testEntity.setUserDept("IT Department");
testEntity.setUserExt("1234");
testEntity.setUserPassword("encrypted_password_1");
testEntity.setUserLevel("5");
testEntity.setUserLastAuth("0");
testEntity.setUserMenuLayoutTop(true);
testEntity.setUserIsVaild(true);
testEntity.setExpired(false);
testEntity.setLocked(false);
// Create second test entity
testEntity2 = new SysUserProfileEntity();
SysUserProfileEntity.DataKey dataKey2 = new SysUserProfileEntity.DataKey();
dataKey2.setUserId("TEST_USER_002");
testEntity2.setDataKey(dataKey2);
testEntity2.setUserName("Test User Two");
testEntity2.setUserEmail("testuser2@example.com");
testEntity2.setUserDept("HR Department");
testEntity2.setUserExt("5678");
testEntity2.setUserPassword("encrypted_password_2");
testEntity2.setUserLevel("3");
testEntity2.setUserLastAuth("0");
testEntity2.setUserMenuLayoutTop(false);
testEntity2.setUserIsVaild(true);
testEntity2.setExpired(false);
testEntity2.setLocked(false);
// Save both test entities to database
testEntity = sysUserProfileRepository.saveAndFlush(testEntity);
testEntity2 = sysUserProfileRepository.saveAndFlush(testEntity2);
}
/**
* Cleanup test data after each test
* Removes all test entities from database
*/
@AfterEach
void tearDown() {
cleanupTestUser("TEST_USER_001");
cleanupTestUser("TEST_USER_002");
}
/**
* Helper method to cleanup a test user by userId
*
* @param userId the user ID to cleanup
*/
private void cleanupTestUser(String userId) {
SysUserProfileEntity entity = sysUserProfileRepository.findByDataKey_UserId(userId);
if (entity != null && entity.getDataKey() != null) {
sysUserProfileRepository.delete(entity);
sysUserProfileRepository.flush();
}
}
/**
* Test: Find user profile by user ID
* Verifies that findByDataKey_UserId returns correct entity
*/
@Test
void testFindByDataKeyUserId() {
// Query by user ID
SysUserProfileEntity found = sysUserProfileRepository.findByDataKey_UserId("TEST_USER_001");
// Verify entity was found
assertNotNull(found);
assertEquals("TEST_USER_001", found.getDataKey().getUserId());
assertEquals("Test User One", found.getUserName());
assertEquals("testuser1@example.com", found.getUserEmail());
assertEquals("IT Department", found.getUserDept());
assertEquals("1234", found.getUserExt());
}
/**
* Test: Find user profile by ID (primary key)
* Verifies that findById returns correct entity
*/
@Test
void testFindById() {
// Query by DataKey
Optional<SysUserProfileEntity> found = sysUserProfileRepository.findById(testEntity.getDataKey());
// Verify entity was found
assertTrue(found.isPresent());
assertEquals("TEST_USER_001", found.get().getDataKey().getUserId());
assertEquals("Test User One", found.get().getUserName());
assertEquals("IT Department", found.get().getUserDept());
}
/**
* Test: Find all users by user name
* Verifies that findAllUserByUserName returns matching entities
*/
@Test
void testFindAllUserByUserName() {
// Query by user name pattern
List<SysUserProfileEntity> found = sysUserProfileRepository.findAllUserByUserName("%Test User%");
// Verify at least one entity found
assertNotNull(found);
assertTrue(found.size() >= 2, "Should find at least 2 test users");
// Verify first result matches
boolean found1 = found.stream()
.anyMatch(e -> "TEST_USER_001".equals(e.getDataKey().getUserId()));
assertTrue(found1, "Should find TEST_USER_001");
// Verify second result matches
boolean found2 = found.stream()
.anyMatch(e -> "TEST_USER_002".equals(e.getDataKey().getUserId()));
assertTrue(found2, "Should find TEST_USER_002");
}
/**
* Test: Verify all fields of saved entity
* Ensures all entity fields are correctly stored and retrieved
*/
@Test
void testEntityFieldsArePersisted() {
// Query entity
Optional<SysUserProfileEntity> found = sysUserProfileRepository.findById(testEntity2.getDataKey());
// Verify all fields
assertTrue(found.isPresent());
SysUserProfileEntity entity = found.get();
assertEquals("TEST_USER_002", entity.getDataKey().getUserId());
assertEquals("Test User Two", entity.getUserName());
assertEquals("testuser2@example.com", entity.getUserEmail());
assertEquals("HR Department", entity.getUserDept());
assertEquals("5678", entity.getUserExt());
assertEquals("encrypted_password_2", entity.getUserPassword());
assertEquals("3", entity.getUserLevel());
assertEquals("0", entity.getUserLastAuth());
assertEquals(false, entity.getUserMenuLayoutTop());
assertEquals(true, entity.getUserIsVaild());
assertEquals(false, entity.isExpired());
assertEquals(false, entity.isLocked());
}
/**
* Test: Update user profile
* Verifies that entity updates are persisted correctly
*/
@Test
void testUpdateUserProfile() {
// Get entity from database
Optional<SysUserProfileEntity> found = sysUserProfileRepository.findById(testEntity.getDataKey());
assertTrue(found.isPresent());
SysUserProfileEntity entity = found.get();
// Update fields
entity.setUserEmail("updated@example.com");
entity.setUserDept("Updated Department");
entity.setUserMenuLayoutTop(false);
// Save changes
sysUserProfileRepository.saveAndFlush(entity);
// Query again to verify
Optional<SysUserProfileEntity> updated = sysUserProfileRepository.findById(testEntity.getDataKey());
assertTrue(updated.isPresent());
assertEquals("updated@example.com", updated.get().getUserEmail());
assertEquals("Updated Department", updated.get().getUserDept());
assertEquals(false, updated.get().getUserMenuLayoutTop());
}
/**
* Test: Delete user profile
* Verifies that entity can be deleted and no longer found
*/
@Test
void testDeleteUserProfile() {
// Verify entity exists
Optional<SysUserProfileEntity> found = sysUserProfileRepository.findById(testEntity.getDataKey());
assertTrue(found.isPresent());
// Delete entity
sysUserProfileRepository.delete(testEntity);
sysUserProfileRepository.flush();
// Verify entity is deleted
Optional<SysUserProfileEntity> notFound = sysUserProfileRepository.findById(testEntity.getDataKey());
assertFalse(notFound.isPresent());
// Reset testEntity to null to prevent duplicate deletion in tearDown
testEntity = null;
}
/**
* Test: Find user by name pattern (case-insensitive) and department
* Verifies that findByUserNameLikeIgnoreCaseAndUserDeptIsContaining works correctly
*/
@Test
void testFindByUserNameLikeIgnoreCaseAndUserDeptIsContaining() {
// Query by username pattern (case-insensitive) and department containing
SysUserProfileEntity found = sysUserProfileRepository
.findByUserNameLikeIgnoreCaseAndUserDeptIsContaining("Test User One", "IT");
// Verify entity was found
assertNotNull(found);
assertEquals("TEST_USER_001", found.getDataKey().getUserId());
assertEquals("Test User One", found.getUserName());
assertEquals("IT Department", found.getUserDept());
assertEquals("testuser1@example.com", found.getUserEmail());
}
/**
* Test: Find user by name pattern with different case
* Verifies case-insensitive matching in findByUserNameLikeIgnoreCaseAndUserDeptIsContaining
*/
@Test
void testFindByUserNameLikeIgnoreCaseWithDifferentCase() {
// Query with different case (lowercase)
SysUserProfileEntity found = sysUserProfileRepository
.findByUserNameLikeIgnoreCaseAndUserDeptIsContaining("test user two", "HR");
// Verify entity was found with case-insensitive matching
assertNotNull(found);
assertEquals("TEST_USER_002", found.getDataKey().getUserId());
assertEquals("Test User Two", found.getUserName());
assertEquals("HR Department", found.getUserDept());
}
/**
* Test: Find user by partial department name
* Verifies that department IsContaining filter works correctly
*/
@Test
void testFindByUserNameAndPartialDepartmentName() {
// Query with partial department name
SysUserProfileEntity found = sysUserProfileRepository
.findByUserNameLikeIgnoreCaseAndUserDeptIsContaining("Test User One", "Department");
// Verify entity was found with partial department match
assertNotNull(found);
assertEquals("TEST_USER_001", found.getDataKey().getUserId());
assertEquals("IT Department", found.getUserDept());
}
/**
* Test: Query multiple users
* Verifies that multiple entities can be found
*/
@Test
void testFindMultipleUsers() {
// Find all entities
List<SysUserProfileEntity> all = sysUserProfileRepository.findAll();
// Verify at least our two test entities exist
assertNotNull(all);
assertTrue(all.size() >= 2, "Should have at least 2 users in database");
// Verify both test entities are in results
long count = all.stream()
.filter(e -> "TEST_USER_001".equals(e.getDataKey().getUserId()) ||
"TEST_USER_002".equals(e.getDataKey().getUserId()))
.count();
assertEquals(2, count, "Should find exactly 2 test users");
}
}
** 2. 新增 SysUserProfileMapperTest.java 於單元測試目錄(src/test/java) tw.lewishome.webapp.database.primary.mybatis 的 package
*因為JPA使用了 Embedded Key，所以這裡需要特別注意檢查:
. PrimaryDataSourceConfig內的 Mybatis相關設定
. SysUserProfileEntity內的 DataKeyHandler
. DataKeyHandler 內的 Method 確認 dataKey相關設定 dataKey.setUserId(val)
package tw.lewishome.webapp.database.primary.mybatis;
import static org.junit.jupiter.api.Assertions.*;
import java.util.List;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import tw.lewishome.webapp.database.primary.entity.SysUserProfileEntity;
/**
* SysUserProfileMapper Unit Tests
*
* Tests for SysUserProfileMapper using Mockito for mocking.
*
* @author lewis
*/
@SpringBootTest
public class SysUserProfileMapperTest {
@Autowired
private SysUserProfileMapper sysUserProfileMapper;
private SysUserProfileEntity testEntity1;
private SysUserProfileEntity testEntity2;
@BeforeEach
void setUp() {
// Create first test entity
testEntity1 = new SysUserProfileEntity();
SysUserProfileEntity.DataKey dataKey1 = new SysUserProfileEntity.DataKey();
dataKey1.setUserId("TEST_USER_001");
testEntity1.setDataKey(dataKey1);
testEntity1.setUserName("Test User One");
testEntity1.setUserEmail("testuser1@example.com");
testEntity1.setUserDept("IT Department");
// Create second test entity
testEntity2 = new SysUserProfileEntity();
SysUserProfileEntity.DataKey dataKey2 = new SysUserProfileEntity.DataKey();
dataKey2.setUserId("TEST_USER_002");
testEntity2.setDataKey(dataKey2);
testEntity2.setUserName("Test User Two");
testEntity2.setUserEmail("testuser2@example.com");
testEntity2.setUserDept("HR Department");
// Save both test entities to database
sysUserProfileMapper.deleteById("TEST_USER_001");
sysUserProfileMapper.insert(testEntity1);
sysUserProfileMapper.deleteById("TEST_USER_002");
sysUserProfileMapper.insert(testEntity2);
// sysUserProfileRepository.saveAndFlush(testEntity);
// sysUserProfileRepository.saveAndFlush(testEntity2);
// testEntities.add(testEntity1);
// testEntities.add(testEntity2);
}
/**
* Cleanup test data after each test
* Removes all test entities from database
*/
@AfterEach
void tearDown() {
sysUserProfileMapper.deleteById("TEST_USER_001");
sysUserProfileMapper.deleteById("TEST_USER_002");
}
@Test
void testFindByIdReturnsListOfEntities() {
List<SysUserProfileEntity> listSysUserProfileEntity = sysUserProfileMapper.findByAll();
assertNotNull(listSysUserProfileEntity);
assertTrue(listSysUserProfileEntity.size() >= 2);
}
@Test
void testFindByIdReturnsCorrectData() {
SysUserProfileEntity result = sysUserProfileMapper.findUserId("TEST_USER_001");
assertEquals("TEST_USER_001", result.getDataKey().getUserId());
assertEquals("Test User One", result.getUserName());
assertEquals("testuser1@example.com", result.getUserEmail());
}
@Test
void testFindByIdReturnsEmptyList() {
SysUserProfileEntity result = sysUserProfileMapper.findUserId("Empty_USER_001");
assertNull(result);
}
@Test
void testFindByIdMultipleInvocations() {
SysUserProfileEntity result1 = sysUserProfileMapper.findUserId("TEST_USER_001");
assertNotNull(result1);
SysUserProfileEntity result2 = sysUserProfileMapper.findUserId("TEST_USER_002");
assertNotNull(result2);
}
@Test
void testUpdatePersistsChanges() {
// Modify fields of testEntity1
testEntity1.setUserName("Updated Test User One");
testEntity1.setUserEmail("updateduser1@example.com");
testEntity1.setUserDept("Updated IT Department");
int rows = sysUserProfileMapper.update(testEntity1);
assertEquals(1, rows, "Update should affect 1 row");
SysUserProfileEntity updated = sysUserProfileMapper.findUserId("TEST_USER_001");
assertNotNull(updated, "Updated entity should be present");
assertEquals("Updated Test User One", updated.getUserName());
assertEquals("updateduser1@example.com", updated.getUserEmail());
assertEquals("Updated IT Department", updated.getUserDept());
}
@Test
void testUpdateNonExistingReturnsZero() {
// Prepare an entity with a non-existing userId
SysUserProfileEntity nonExisting = new SysUserProfileEntity();
SysUserProfileEntity.DataKey dk = new SysUserProfileEntity.DataKey();
dk.setUserId("NON_EXISTENT_USER_999");
nonExisting.setDataKey(dk);
nonExisting.setUserName("No One");
nonExisting.setUserEmail("noone@example.com");
nonExisting.setUserDept("Nowhere");
int rows = sysUserProfileMapper.update(nonExisting);
assertEquals(0, rows, "Update of non-existing entity should affect 0 rows");
SysUserProfileEntity fetched = sysUserProfileMapper.findUserId("NON_EXISTENT_USER_999");
assertNull(fetched, "No entity should be found for the non-existing userId");
}
/**
* Delete test entity and verify it's removed
*/
@Test
void testDeleteRemovesEntity() {
int rows = sysUserProfileMapper.deleteById("TEST_USER_001");
assertEquals(1, rows, "Delete should affect 1 row");
SysUserProfileEntity deleted = sysUserProfileMapper.findUserId("TEST_USER_001");
assertNull(deleted, "Deleted entity should not be found");
}
}
** 3. 新增 SysUserProfileSpecificationTest.java 於單元測試目錄(src/test/java) tw.lewishome.webapp.database.primary.jpaspecification 的 package
package tw.lewishome.webapp.database.primary.jpaspecification;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.test.context.TestPropertySource;
import jakarta.persistence.criteria.Predicate;
import tw.lewishome.webapp.database.primary.entity.SysUserProfileEntity;
import tw.lewishome.webapp.database.primary.repository.SysUserProfileRepository;
import tw.lewishome.webapp.database.primary.specification.SysUserProfileSpecification;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
//啟動SpringBootTest
@SpringBootTest
// 指定適用Properties (這裡指定專案的properties 檔案，已可以另外指定 test專用的properties 檔案)
@TestPropertySource(locations = "classpath:application.properties")
public class SysUserProfileSpecificationTest {
@Autowired
private SysUserProfileRepository sysUserProfileRepository;
@Test
public void TestSysUserProfileSpecification() {
Specification<SysUserProfileEntity> specification = SysUserProfileSpecification.getSysProfileByLikeIdandName("",
"AA");
try {
// Pageable pageable = PageRequest.of(0, 10, Sort.by("userId").descending());
Pageable pageable = PageRequest.of(0, 10, Sort.by("DataKey.userId").ascending());
Page<SysUserProfileEntity> listSysUserProfileEntity = sysUserProfileRepository.findAll(specification,
pageable);
listSysUserProfileEntity.forEach(x -> {
System.out.println(x);
});
// sysUserProfileRepository.findAll(specification);
List<Sort.Order> orders = new ArrayList<>();
Sort.Order order1 = new Sort.Order(Sort.Direction.ASC, "DataKey.userId");
Sort.Order order2 = new Sort.Order(Sort.Direction.DESC, "userName");
// Add all your order instances
orders.add(order1);
orders.add(order2);
Sort multiSort = Sort.by(orders);
Pageable pageableMultiSort = PageRequest.of(0, 10, multiSort);
listSysUserProfileEntity = sysUserProfileRepository.findAll(specification,
pageableMultiSort);
listSysUserProfileEntity.forEach(x -> {
System.out.println(x);
});
} catch (Exception ex) {
ex.printStackTrace();
}
}
@Test
public void testGetSysProfileByName_emptyParameters_returnsPage() {
Specification<SysUserProfileEntity> specification = SysUserProfileSpecification.getSysProfileByLikeIdandName("",
"");
Pageable pageable = PageRequest.of(0, 10);
Page<SysUserProfileEntity> result = sysUserProfileRepository.findAll(specification, pageable);
org.junit.jupiter.api.Assertions.assertNotNull(result);
}
@Test
public void testGetPageSearchCondition_withSearchString_returnsPage() {
Specification<SysUserProfileEntity> specification = SysUserProfileSpecification.getPageSearchCondition("",
"john");
Pageable pageable = PageRequest.of(0, 10);
Page<SysUserProfileEntity> result = sysUserProfileRepository.findAll(specification, pageable);
org.junit.jupiter.api.Assertions.assertNotNull(result);
}
@Test
public void testGetPageNewSpecification() {
String name = "";
String userId = "A";
Specification<SysUserProfileEntity> newSysUserProfileSpecification = (root, query, criteriaBuilder) -> {
Predicate findDataPredicate = criteriaBuilder.disjunction(); // Start with no condition false
if (StringUtils.isNotBlank(userId)) {
findDataPredicate = criteriaBuilder.or(findDataPredicate,
criteriaBuilder.like(root.get("dataKey").get("userId"), "%" + userId + "%"));
}
if (StringUtils.isNotBlank(name)) {
findDataPredicate = criteriaBuilder.and(findDataPredicate,
criteriaBuilder.like(root.get("userName"), "%" + name + "%"));
}
return findDataPredicate;
};
Pageable pageable = PageRequest.of(0, 10);
Page<SysUserProfileEntity> result = sysUserProfileRepository.findAll(newSysUserProfileSpecification, pageable);
org.junit.jupiter.api.Assertions.assertNotNull(result);
}
@Test
public void testGetPageSearchCondition_withHeaderData_deserializesAndFilters() throws Exception {
// Build headerDataString by serializing TableHeaderData to Base64 to exercise
// headerDataString branch
tw.lewishome.webapp.page.system.model.SysParmDataPageModel.TableHeaderData headerData = new tw.lewishome.webapp.page.system.model.SysParmDataPageModel.TableHeaderData();
headerData.setSelectedEnv("testUserId");
java.io.ByteArrayOutputStream baos = new java.io.ByteArrayOutputStream();
try (java.io.ObjectOutputStream oos = new java.io.ObjectOutputStream(baos)) {
oos.writeObject(headerData);
}
String headerDataString = java.util.Base64.getEncoder().encodeToString(baos.toByteArray());
Specification<SysUserProfileEntity> specification = SysUserProfileSpecification
.getPageSearchCondition(headerDataString, "");
Pageable pageable = PageRequest.of(0, 10);
Page<SysUserProfileEntity> result = sysUserProfileRepository.findAll(specification, pageable);
org.junit.jupiter.api.Assertions.assertNotNull(result);
}
}
** 新增 SysPramDataEntity.java於資料庫entity子目錄中:
比較 SysUserProfileEentity，DataKey 以 env + parmame + parmKey組合而成，所以Mybatis需要使用 DataKeyHandler，將Resultset的內容，轉換指定到這三個欄位。
package tw.lewishome.webapp.database.primary.entity;
import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import jakarta.persistence.Column;
import jakarta.persistence.Embeddable;
import jakarta.persistence.EmbeddedId;
import jakarta.persistence.Entity;
import jakarta.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import tw.lewishome.webapp.database.au
iThome鐵人賽
看影片追技術