🚨 These rules MUST be followed when working with DTOs:
- NEVER modify existing constructors or attributes - only add new ones. Don't change parameters, remove constructors, modify existing fields, or change method signatures other code depends on.
- Use direct DTO queries - avoid entity-to-DTO conversion loops
- JPQL PERSISTED FIELDS ONLY - never use derived/calculated properties like
nameWithTitle,age,displayNamein JPQL; only persisted database fields work (see § Derived/Calculated Properties) - Use
findLightsByJpql()with an explicit cast for DTO constructor queries - neverfindByJpql()(wrong return type) orfindAggregates()(Object[]results only) (see § Correct Facade Method)
When replacing entities with DTOs in controllers:
❌ WRONG APPROACH:
// DON'T DO THIS - Inefficient and resource-intensive
List<Stock> stocks = stockFacade.findByJpql(sql, params);
List<StockDTO> dtos = new ArrayList<>();
for (Stock stock : stocks) {
StockDTO dto = new StockDTO(stock.getField1(), stock.getField2(), ...);
dtos.add(dto);
}✅ CORRECT APPROACH:
// DO THIS - Direct DTO query from database
String jpql = "SELECT new com.divudi.core.data.dto.StockDTO("
+ "s.id, "
+ "s.itemBatch.item.name, "
+ "s.itemBatch.item.code, "
+ "s.itemBatch.retailsaleRate, "
+ "s.stock, "
+ "s.itemBatch.dateOfExpire, "
+ "s.itemBatch.batchNo, "
+ "s.itemBatch.purcahseRate, "
+ "s.itemBatch.wholesaleRate) "
+ "FROM Stock s WHERE ...";
// 🚨 CRITICAL: Use findLightsByJpql() with cast for DTO constructor queries
List<StockDTO> dtos = (List<StockDTO>) facade.findLightsByJpql(jpql, params, TemporalType.TIMESTAMP);🚨 CRITICAL PATTERN for Navigation Support:
When DTOs need to support navigation (e.g., clicking on a row to view details), use IDs and String names instead of full entity references.
❌ WRONG - Including entity objects in DTOs:
public class OpdSaleSummaryDTO {
private Category category; // Don't do this - defeats DTO purpose
private Item item; // Don't do this - loads entity graph
private String itemName;
private Double total;
}✅ CORRECT - Use IDs and names for navigation:
public class OpdSaleSummaryDTO {
private Long categoryId; // For navigation
private String categoryName; // For display
private Long itemId; // For navigation
private String itemName; // For display
private Double total;
// Constructor for JPQL query
public OpdSaleSummaryDTO(Long categoryId, String categoryName,
Long itemId, String itemName, Double total) {
this.categoryId = categoryId;
this.categoryName = categoryName;
this.itemId = itemId;
this.itemName = itemName;
this.total = total;
}
}JPQL Query Pattern:
String jpql = "SELECT new com.divudi.core.data.dto.OpdSaleSummaryDTO("
+ "bi.item.category.id, " // Category ID for navigation
+ "bi.item.category.name, " // Category name for display
+ "bi.item.id, " // Item ID for navigation
+ "bi.item.name, " // Item name for display
+ "sum(bi.netValue)) " // Aggregated data
+ "FROM BillItem bi "
+ "WHERE ... "
+ "GROUP BY bi.item.category.id, bi.item.category.name, bi.item.id, bi.item.name";
List<OpdSaleSummaryDTO> dtos = (List<OpdSaleSummaryDTO>) facade.findLightsByJpql(jpql, params, TemporalType.TIMESTAMP);Navigation Controller Pattern:
// In controller - load full entity only when navigating
public String navigateToDetails(OpdSaleSummaryDTO dto) {
// Load full entities only when needed for detail page
if (dto.getCategoryId() != null) {
this.category = categoryFacade.find(dto.getCategoryId());
}
if (dto.getItemId() != null) {
this.item = itemFacade.find(dto.getItemId());
}
return "/detail_page?faces-redirect=true";
}Benefits:
- ✅ DTOs remain lightweight (no entity graph loading)
- ✅ Navigation still works (using IDs to load entities on demand)
- ✅ Display names available without entity access
- ✅ Database aggregation stays efficient
- ✅ Memory footprint minimized
When changing controller properties from entities to DTOs:
❌ WRONG - Breaking existing functionality:
// This breaks other code that depends on the Stock entity
Stock stock; // Changed to StockDTO - BREAKS OTHER CODE!✅ CORRECT - Add new property, keep existing:
Stock stock; // Keep existing for business logic
StockDTO selectedStockDto; // Add new for UI displayWhen updating XHTML to use DTOs:
For dataTable with DTO data source:
<p:dataTable value="#{controller.stockDtoList}" var="i"
selection="#{controller.selectedStockDto}">
<p:column headerText="Name">
<h:outputText value="#{i.itemName}" />
</p:column>
</p:dataTable>Sync DTO selection with entity if needed:
public void setSelectedStockDto(StockDTO dto) {
this.selectedStockDto = dto;
// Load full entity only if needed for business operations
if (dto != null) {
this.stock = stockFacade.find(dto.getId());
}
}// ✅ KEEP existing constructor intact
public StockDTO(Long id, String itemName, String code, String genericName,
Double retailRate, Double stockQty, Date dateOfExpire) {
// Original constructor - NEVER CHANGE
}
// ✅ ADD new constructors for additional use cases
public StockDTO(Long id, String itemName, String code, Double retailRate,
Double stockQty, Date dateOfExpire, String batchNo,
Double purchaseRate, Double wholesaleRate) {
// New constructor with additional fields
}- Identify the display use case - what data does the UI actually need?
- Add new fields to DTO (never remove existing ones)
- Add new constructor with required fields for the use case
- Create direct JPQL query using the new constructor
- Add new controller properties for DTO selection (keep existing entity properties)
- Update XHTML to use DTO properties for display
- Maintain entity properties for business logic operations
Direct DTO queries provide:
- Memory efficiency: Only loads required display fields
- Database efficiency: Single optimized query instead of entity + conversion
- Network efficiency: Reduced data transfer
- Compilation safety: No breaking changes to existing code
See StockSearchService.findStockDtos() method for the correct pattern of direct DTO querying.
🚨 ALWAYS use findLightsByJpql() with explicit cast for DTO constructor queries:
// ✅ CORRECT - DTO constructor query
String jpql = "SELECT new com.divudi.core.data.dto.PharmacySaleByBillTypeDTO("
+ "i.bill.billTypeAtomic.label, "
+ "sum(i.pharmaceuticalBillItem.qty)) "
+ "FROM BillItem i "
+ "WHERE ... "
+ "GROUP BY i.bill.billTypeAtomic.label";
// MUST use findLightsByJpql() with cast
salesByBillType = (List<PharmacySaleByBillTypeDTO>) getBillItemFacade().findLightsByJpql(jpql, m, TemporalType.TIMESTAMP);❌ WRONG facade methods for DTO constructor queries:
// DON'T USE THESE for DTO constructor queries:
facade.findByJpql(jpql, params) // Wrong return type
facade.findAggregates(jpql, params) // For Object[] results only
facade.findLightsByJpql(jpql, params) // Missing TemporalType when using Date parametersWhy findLightsByJpql() is required:
- Optimized for lightweight objects (DTOs)
- Handles constructor queries correctly
- Supports temporal parameters for Date/Timestamp filtering
- Returns properly typed collections
- Changing existing constructor signatures → Compilation errors in dependent code
- Converting entities to DTOs in loops → Performance degradation
- Removing entity properties used by business logic → Runtime failures
- Missing explicit cast → Type safety issues with DTO constructor queries
- Accessing properties through null relationships → Silent query failures (most common issue!)
5a.
BigDecimalconstructor param for adouble/Doublecolumn → loudIllegalArgumentException: argument type mismatchat construction time (see § Numeric Type Mismatch) - Including cancellation details in list DTOs → Unnecessary complexity and performance issues
- Calling
Staff.getName()to display a person's name → returns theStaffentity's own (usually unset)namefield, not the linked person's name — see below
Staff has its own name field (often null/unset) separate from the
linked Person entity's name. Calling staff.getName() to display "who did
this" silently renders blank or the literal string "null" — there is no
exception, so this is easy to miss in code review.
<h:outputText value="#{bean.selectedRecord.administeredBy.name}" />sb.append(" by ").append(mar.getAdministeredBy().getName()); // "by null"<h:outputText value="#{bean.selectedRecord.administeredBy.person.name}" />if (mar.getAdministeredBy() != null && mar.getAdministeredBy().getPerson() != null
&& mar.getAdministeredBy().getPerson().getName() != null) {
sb.append(" by ").append(mar.getAdministeredBy().getPerson().getName());
}Guard each hop (administeredBy, .getPerson(), .getName()) — all three can
be null for incompletely-seeded Staff records.
Found during #21488: the Ward Medicines Timeline's "Administered By" field
showed "by null" until switched to administeredBy.person.name.
Always use wrapper types (Boolean, Integer, Long) in DTO constructor parameters for consistency and null safety:
// ✅ RECOMMENDED - Use Boolean wrapper type
public PharmacyPurchaseOrderDTO(
Long billId,
String deptId,
Boolean cancelled, // ✅ Wrapper type - handles nulls gracefully
Boolean billClosed, // ✅ Wrapper type
Boolean fullyIssued) { // ✅ Wrapper type
this.cancelled = cancelled != null ? cancelled : false;
this.billClosed = billClosed != null ? billClosed : false;
this.fullyIssued = fullyIssued != null ? fullyIssued : false;
}| Entity Type | DTO Constructor Parameter | Result |
|---|---|---|
boolean (primitive) |
Boolean (wrapper) |
✅ Works |
Boolean (wrapper) |
Boolean (wrapper) |
✅ Works |
int (primitive) |
Integer (wrapper) |
✅ Works |
Long |
Long |
✅ Works |
String |
String |
✅ Works |
double (primitive) |
Double (wrapper) |
✅ Works |
Double (wrapper) |
Double (wrapper) |
✅ Works |
double / Double |
BigDecimal |
❌ IllegalArgumentException: argument type mismatch — see next section |
Note: Primitive-to-matching-wrapper auto-boxing works correctly in EclipseLink JPQL
(boolean→Boolean, double→Double). EclipseLink does not perform cross-type
numeric conversion when invoking the DTO constructor — a double column will not flow into
a BigDecimal parameter. The two most common DTO query failures are null relationship
access (silent, returns 0 rows) and numeric type mismatch (loud SEVERE
argument type mismatch, see below).
First, check which kind of failure it is:
- Loud
SEVERE ... argument type mismatchin server.log → numeric/type mismatch between the projected column and the constructor parameter (see § Numeric Type Mismatch). Most often adouble/Doublecolumn bound to aBigDecimalparameter. - Silent (no exception, just
result size = 0) → null relationship access (below).
When COUNT returns results but DTO query returns 0 (silent case):
- Check for null relationship access - This is the #1 cause!
b.cancelledBill.createdAtfails ifcancelledBillis null - Test with minimal constructor - Create a 4-param constructor with just basic fields (id, deptId, createdAt, netTotal). If it works, the issue is with additional fields
- Verify parameter count - Must match exactly (11 params in query = 11 in constructor)
- Check parameter order - Must match query SELECT order exactly
- Check constructor parameter types - Use wrapper types (
Boolean, notboolean) - Remove relationship traversals one by one - Identify which nullable relationship is causing the failure
Symptom (server.log):
SEVERE javax.persistence.PersistenceException: java.lang.IllegalArgumentException: argument type mismatch
...
Caused by: java.lang.IllegalArgumentException: argument type mismatch
at ...Constructor.newInstance(...)
at org.eclipse.persistence.queries.ReportQueryResult.processConstructorItem(...)
The COUNT query returns the correct number, but the DTO query throws at construction time
and the controller logs DTO result size = 0. Unlike null-relationship failures, this one
throws a SEVERE exception — grep the log for argument type mismatch.
Root cause: EclipseLink builds the DTO via reflective Constructor.newInstance(...),
passing each projected column's Java type as produced by the query. It does not
convert Double → BigDecimal (or Integer → Long, etc.). If the entity field is
primitive double (or the projection yields Double) but the constructor parameter is
BigDecimal, the reflective call fails.
Most HMIS monetary/quantity fields on Bill, BillItem, PharmaceuticalBillItem, etc.
are primitive double (e.g. Bill.total, Bill.netTotal, Bill.discount,
Bill.margin). A JPQL projection over them yields Double.
❌ WRONG — BigDecimal constructor parameter for a double column:
public BillListReportDTO(Long id, ..., BigDecimal total, BigDecimal netTotal) { ... }
// JPQL: SELECT new ...BillListReportDTO(b.id, ..., b.total, b.netTotal) FROM Bill b
// ^^^^^^^ double -> BigDecimal => mismatch✅ SOLUTION 1 (preferred): make the constructor parameter Double, convert inside.
The DTO field can stay BigDecimal — convert in the constructor body:
public BillListReportDTO(Long id, ..., Double total, Double netTotal) {
this.total = total != null ? BigDecimal.valueOf(total) : null;
this.netTotal = netTotal != null ? BigDecimal.valueOf(netTotal) : null;
}✅ SOLUTION 2: keep the field/param Double end-to-end if the report doesn't need
BigDecimal precision (most list/display reports don't).
- Don't create two constructors of the same arity that differ only by
BigDecimalvsDoublenumeric params. EclipseLink matches the constructor by argument count first and may bind the wrong overload, re-introducing the mismatch. Keep exactly one constructor per arity. (Per the constructor-immutability rule you normally never modify an existing constructor — but if it was added for the current feature and has no other caller, changing its numeric params fromBigDecimaltoDoubleis the correct fix, not an add-only overload. Verify with agrepfornew <DtoName>(and the JPQLnew ...<DtoName>(string that it truly has a single caller.) COALESCE(b.total, 0)projects anIntegerliteral type, which can also mismatch aDouble/BigDecimalparameter. Use a typed literal:COALESCE(b.total, 0.0).- After applying any automated (CodeRabbit/Codex) numeric fix, re-confirm the entity field's
declared type (
grep "private .* total"on the entity) — primitivedoublevs boxedDoublevsBigDecimalis the whole game here.
Reference: issue #21247 inpatient service bill list — BillListReportDTO +
InwardReportControllerBht.fetchServiceBillDtos(). Bill.total/discount/netTotal/margin
are primitive double; the DTO constructor originally declared them BigDecimal →
argument type mismatch. Fixed by switching the constructor params to Double (converting
to BigDecimal internally) and using COALESCE(..., 0.0) literals.
This is the most common cause of "DTO query returns 0 results" issues.
When accessing properties through a nullable relationship in a JPQL DTO constructor expression, the entire query fails silently if the relationship is null - returning 0 results with no exception.
❌ WRONG - Direct access through nullable relationship:
String jpql = "SELECT new DTO("
+ "b.id, "
+ "b.cancelledBill.createdAt, " // ❌ FAILS SILENTLY if cancelledBill is null
+ "b.cancelledBill.creater.webUserPerson.name) " // ❌ FAILS SILENTLY
+ "FROM Bill b WHERE ...";What happens:
- If ANY row has
cancelledBill = null, the ENTIRE query returns 0 results - No exception is thrown
- COUNT query on same data returns correct count (e.g., 1)
- This is JPQL behavior, not a bug
// Simply don't include cancelledBill fields in the DTO query
String jpql = "SELECT new DTO("
+ "b.id, "
+ "b.deptId, "
+ "b.cancelled) " // Just the boolean flag, not the relationship details
+ "FROM Bill b WHERE ...";String jpql = "SELECT new DTO("
+ "cb.createdAt, " // Safe - cb can be null from LEFT JOIN
+ "COALESCE(cancellerPerson.name, '')) " // Safe - COALESCE handles null
+ "FROM Bill b "
+ "LEFT JOIN b.cancelledBill cb "
+ "LEFT JOIN cb.creater cancellerCreater "
+ "LEFT JOIN cancellerCreater.webUserPerson cancellerPerson "
+ "WHERE ...";Note: Even with LEFT JOIN, you must join EACH level of the relationship chain separately.
🚨 An explicit LEFT JOIN without an alias is a hard compile error, not a silent failure. Unlike the silent-zero-rows case above, writing LEFT JOIN b.toInstitution (no alias) and then still referencing b.toInstitution.name in SELECT/WHERE throws EclipseLink-0 JPQLException: An identification variable must be defined for a JOIN expression at query-build time — every call fails, wrapped in an EJBTransactionRolledbackException. If the caller catches and swallows the exception (catch (Exception e) { e.printStackTrace(); return new ArrayList<>(); }), this looks identical to "no matching rows" from the UI, with the real error only visible in server.log. Once you add an explicit LEFT JOIN x.y alias, every reference to that path in the rest of the query must go through alias, not x.y again. (Found in issue #18579 — BillService.fetchPharmacyReturnWithoutTrasingBillDTOs/...BillItemDTOs had four such unaliased joins.)
Even when LEFT JOINs are present, a null value flowing into a String DTO constructor
parameter causes that row to be dropped in some EclipseLink versions. Always wrap every
LEFT JOIN String field in COALESCE(..., '') to guarantee rows are never silently omitted.
// ✅ CORRECT — COALESCE on every nullable LEFT JOIN String
String sql = "SELECT new com.divudi.core.data.dto.PharmacySaleSearchDTO("
+ "b.id, b.deptId, b.department.name, b.createdAt, "
+ "COALESCE(creatorPerson.name, ''), " // nullable LEFT JOIN
+ "COALESCE(patientPerson.name, ''), " // nullable LEFT JOIN
+ "COALESCE(refDoctorPerson.name, ''), " // nullable LEFT JOIN
+ "b.paymentMethod, "
+ "COALESCE(ps.name, ''), " // nullable LEFT JOIN
+ "b.total, b.discount, b.netTotal, "
+ "b.refunded, b.cancelled) "
+ "FROM Bill b "
+ "LEFT JOIN b.creater creater "
+ "LEFT JOIN creater.webUserPerson creatorPerson "
+ "LEFT JOIN b.patient patient "
+ "LEFT JOIN patient.person patientPerson "
+ "LEFT JOIN b.referredBy referredBy "
+ "LEFT JOIN referredBy.person refDoctorPerson "
+ "LEFT JOIN b.paymentScheme ps "
+ "WHERE ...";Rule: Every column from a LEFT-joined table that is a String in the DTO constructor
must be wrapped in COALESCE(expr, ''). Numeric and boolean fields from the root entity
(b.total, b.cancelled) are safe without COALESCE.
For list/table displays, AVOID including cancellation relationship details:
cancelledBill.createdAt(cancellation date)cancelledBill.creater.name(canceller name)cancelledBill.comments(cancellation reason)
Why:
- Performance: These require LEFT JOINs through multiple tables
- Complexity: Nullable relationships cause silent query failures
- UX: Users can click through to view full bill details including cancellation info
- Simplicity: A boolean
cancelledflag is sufficient for list filtering/display
✅ Recommended Pattern for List DTOs:
public class PurchaseOrderListDTO {
private Long billId;
private String deptId;
private Date createdAt;
private Double netTotal;
private Boolean cancelled; // ✅ Simple boolean flag for display/filtering
private Boolean billClosed;
// ❌ Don't include: cancelledAt, cancellerName, cancellationReason
}If user needs cancellation details: Provide a "View Details" action that navigates to the full bill view where all cancellation information is available.
When exposing a DTO fetch method that should respect the user's configured row limit,
accept int maxResult directly (from searchController.getMaxResult()) rather than a
boolean maxNum flag. Pass 0 or a negative value to mean "unlimited".
// ✅ Preferred — caller controls the limit
public void fetchSaleSearchDtosFromNativeBills(int maxResult) {
...
if (maxResult > 0) {
saleBillDtos = (List<PharmacySaleSearchDTO>)
billFacade.findLightsByJpql(sql, m, TemporalType.TIMESTAMP, maxResult);
} else {
saleBillDtos = (List<PharmacySaleSearchDTO>)
billFacade.findLightsByJpql(sql, m, TemporalType.TIMESTAMP);
}
}
// ✅ Backward-compat shim keeps old callers working
@Deprecated
public void fetchSaleSearchDtosFromNativeBills(boolean maxNum) {
fetchSaleSearchDtosFromNativeBills(maxNum ? 25 : 0);
}Do not add JPQL subqueries (e.g. b.id IN (SELECT bi.bill.id FROM BillItem bi WHERE ...))
inside DTO constructor queries. Subqueries inside SELECT new DTO(...) are not supported by
EclipseLink and subqueries in the WHERE clause of a constructor query degrade performance
significantly for large tables. If item-level filtering is required, discuss an alternative
approach before implementing.
- Always use wrapper types (
Boolean,Integer,Long,Double) for DTO constructor parameters 1a. Match the numeric wrapper to the entity field — primitivedouble→Doubleparam, neverBigDecimal(convert toBigDecimalinside the constructor if the field needs it); useCOALESCE(x, 0.0)notCOALESCE(x, 0) - Avoid nullable relationship traversal - accessing
b.cancelledBill.createdAtfails silently ifcancelledBillis null - Use LEFT JOIN with explicit aliases if you must access nullable relationships
- Test COUNT separately to verify data exists before troubleshooting DTO construction
- Add debug logging when implementing new DTO queries to catch silent failures early
- Avoid cancellation details in list DTOs - use boolean flags, let users navigate to details for full info
- Test with minimal constructor first - if a 4-param constructor works but 11-param fails, the issue is with the additional fields
- Accept
int maxResultnotboolean maxNum- lets the caller passsearchController.getMaxResult()directly - No subqueries in DTO fetch methods - discuss alternatives before adding
WHERE b.id IN (SELECT ...)
JPQL can only access persisted database fields, not derived properties (getter methods that compute values).
❌ WRONG - Using derived property:
// Person entity has getNameWithTitle() method that combines title + name
// But 'nameWithTitle' is NOT a persisted column in the database!
String jpql = "SELECT new DTO("
+ "p.nameWithTitle) " // ❌ ERROR: 'nameWithTitle' cannot be resolved to a valid type
+ "FROM Person p";✅ CORRECT - Use only persisted fields:
String jpql = "SELECT new DTO("
+ "p.name) " // ✅ 'name' is a persisted field
+ "FROM Person p";
// If you need the title, select it separately:
String jpql = "SELECT new DTO("
+ "p.title, " // ✅ Persisted field
+ "p.name) " // ✅ Persisted field
+ "FROM Person p";| Entity | Non-Persisted Property | Use Instead |
|---|---|---|
Person |
nameWithTitle |
name (or title, name separately) |
Person |
age |
dob (calculate age in Java) |
Bill |
netTotal (if calculated) |
Sum the actual persisted fee fields |
Item |
displayName |
name |
- Check if the property has
@Columnor@JoinColumnannotation → Persisted - Check if the property is only a getter method with no backing field → NOT Persisted
- Check if the getter computes/derives a value from other fields → NOT Persisted
Example from Person entity:
// This is a derived property - NO @Column annotation, just a getter
public String getNameWithTitle() {
return (title != null ? title + " " : "") + name;
}
// This IS a persisted field - has @Column annotation
@Column(name = "name")
private String name;- Entity/DTO Dual Navigation Pattern - How to expose Entity and DTO versions of the same report side-by-side in navigation menus