DBMS ports

A DBMS port runs SQL you write - reports, bulk statements, another datasource - behind a generated interface, without a repository or an entity.

model/port.yml

port:
  OrderDbService:
    meta: dbms @datasource("order")
    methods:
      findOrders:
        params:
          input: OrderSearchDTO
        return: List<OrderDTO>
        query: |
          SELECT * FROM orders WHERE status = :status

Option	Default	Means
@datasource("name")	the default datasource	which EntityManagerFactory
nativeQuery	true	native SQL (true) or JPQL (false)
pageable	false	paging
ref	-	the SQL of another method

Return types

Return	Runs as	For
List<DTO>	inqueryList	every row
Page<DTO>	inqueryPage	a page (pageable needed)
DTO	inqueryFirst	one row
int, method name with update / delete / insert	executeUpdate	DML - the rows affected
int, such a name, and a list<DTO> parameter	executeBatchUpdate	the statement per element, in one transaction - the total affected
int (other names), long, double, float, string, boolean	inqueryFirst	one value
decimal	inqueryFirst	one number as java.math.BigDecimal (sums, amounts)

A method that returns one value returns the first column of the row, converted to that type. A NULL value (a SUM over no rows), or no row, gives null. return: decimal and List<decimal> generate java.math.BigDecimal. A single value parameter (long, string, a date, an enum) is bound to :name by the parameter's name; a single DTO is bound by its getters. Binding by name needs the parameter names in the compiled classes (-parameters), which Spring Boot's Gradle and Maven plugins set.

Before 3.0.261023 (up to 3.0.261022-rc1) these did not work: a number failed with Failed to map tuple to DTO, a string came back empty, a single long parameter was not bound, and return: decimal generated the type decimal, which did not compile.

updateStatus:
  params:
    id: string
    newStatus: string
  return: int
  query: |
    UPDATE orders SET status = :newStatus WHERE id = :id

insertOrderBatch:
  params:
    items: list<OrderInputDTO>      # one execution per element
  return: int
  query: |
    INSERT INTO orders (order_no, product_code, quantity, price)
    VALUES (:orderNo, :productCode, :quantity, :price)

An empty list runs nothing and returns 0; a list of simple values (list<long>) is an IN (:ids) binding, not a batch.

Dynamic SQL

AND status = :status /* if:status */          -- dropped when status is null or empty

/* if:input.searchCriteria != null */
  AND criteria = :searchCriteria
/* if-end */

AND min_age >= :minAge /* if:input.getMinAge() > 0 */

Conditions are JEXL expressions; input is the input object.

${name} is replaced into the SQL text (a table or column name the server chooses); :name is a bound parameter. Never pass user input through ${...} - it is open to SQL injection.

In a hexagonal project

The interface goes to namespace.port (application.port.out); the adapter is the runtime proxy, registered by the runtime's auto-configuration from 3.0.261023 (by @EnableElastiCore up to 3.0.261022-rc1; see Ports). The DDD sample's report port, a read model the aggregates do not answer:

@DbmsService(id="shop.ShopReportService", sqlSource="blueprint/shop/port/report_port.yml")
public interface ShopReportService {
    List<CustomerRevenueDTO> revenueByCustomer(RevenueSearchInput input);
}

More - sortCode, hot reload of SQL files, multiple datasources, result mapping - is in blueprint/elasticore-dsl-port-guide.md.