Skip to main content

DBMS Port Definition

Basic Structure​

port:
  PortServiceName:  ## PortServiceName can be freely chosen as long as it is unique within the project
    meta: dbms @datasource("datasourceName")
    methods:
      methodName:   ## methodName should be unique within the service and meaningful
        meta: method [-- description]
        params:
          paramName1: ParamType
          paramName2: ParamType
        return: ReturnType
        nativeQuery: true/false
        pageable: true/false (optional)
        ref: referencedMethodName (optional)
        query: |
          SQL or JPQL query

DBMS Configuration Options​

  • @datasource(name): DataSource name to use (default: "main"); not required for single datasource setups
  • nativeQuery: Choose between Native SQL (true) or JPQL (false). Default is true
  • pageable: Whether to support pagination
  • ref: Reference another method's query (useful for pagination)
  • query: SQL/JPQL to execute (uses YAML multi-line syntax)

State-changing Queries (insert/update/delete)​

  • methodName must start with insert, update, or delete.
  • return should be set to int, and the number of affected rows is returned.

Parameter Binding Methods​

query: |
  SELECT * FROM users 
  WHERE name = :name AND age >= :age

2. String Substitution (At Execution Time)​

query: |
  SELECT * FROM ${tableName} 
  WHERE status = :status

3. Conditional Query (Dynamic SQL)​

query: |
  SELECT * FROM users
  WHERE 1=1
  AND name = :name /* if:name */
  AND age >= :minAge /* if:minAge */

Primitive Type Return Support​

DBMS Port supports direct primitive type returns for SQL aggregate functions like COUNT, MAX, MIN, SUM, and single-value queries.

Supported Primitive Types​

  • int, integer: Integer type (COUNT, SUM results)
  • long: Long integer type (large COUNT, SUM results)
  • double: Floating-point type (AVG results)
  • float: Float type
  • string: String type (single column query, MAX/MIN results)
  • boolean: Boolean type

Primitive Type Return Examples​

port:
  UserDbPortService:
    meta: dbms @datasource("main")
    methods:
      # COUNT query - returns int
      getUserCount:
        meta: method -- Get total user count
        return: int
        query: "SELECT COUNT(*) FROM users"
        
      # Conditional COUNT query - returns long 
      getActiveUserCount:
        meta: method -- Get active user count
        params:
          active: boolean
        return: long
        query: "SELECT COUNT(*) FROM users WHERE active = :active"
        
      # MAX query - returns long
      getMaxPrice:
        meta: method -- Get maximum price
        return: long
        query: "SELECT MAX(price) FROM products WHERE active = true"
        
      # AVG query - returns double
      getAverageAge:
        meta: method -- Get average age
        return: double
        query: "SELECT AVG(age) FROM users"
        
      # Single value query - returns string
      getUserNameById:
        meta: method -- Get username by ID
        params:
          userId: long
        return: string
        query: "SELECT name FROM users WHERE id = :userId"
        
      # Existence check - returns boolean
      existsByEmail:
        meta: method -- Check if email exists
        params:
          email: string
        return: boolean
        query: "SELECT CASE WHEN COUNT(*) > 0 THEN true ELSE false END FROM users WHERE email = :email"

Generated Java Code Example​

@DbmsService(id="UserDbPortService", datasource="main")
public interface UserDbPortService {
    Integer getUserCount();
    Long getActiveUserCount(Boolean active);
    Long getMaxPrice();
    Double getAverageAge();
    String getUserNameById(Long userId);
    Boolean existsByEmail(String email);
}

DBMS Port Examples​

1. Basic Query​

dto:
  UserSearchInput:
    meta: dto
    fields:
      name: string
      minAge: int

  UserDTO:
    meta: dto
    fields:
      id: long
      name: string
      email: string
      age: int

port:
  UserDbPortService:
    meta: dbms @datasource("main")
    methods:
      findUsersByName:
        meta: method -- Search users by name
        params:
          input: UserSearchInput
        return: List<UserDTO>
        nativeQuery: true
        query: |
          SELECT id, name, email, age
          FROM users
          WHERE name LIKE CONCAT('%', :name, '%')
          AND age >= :minAge

2. Conditional Dynamic Query​

port:
  OrderDbPortService:
    meta: dbms @datasource("order")
    methods:
      searchOrders:
        meta: method -- Order search (dynamic conditions)
        params:
          input: OrderSearchDTO
        return: List<OrderDTO>
        nativeQuery: true
        query: |
          SELECT o.id, o.order_date, o.status, o.amount
          FROM orders o
          WHERE 1=1
          AND o.status = :status /* if:status */
          AND o.order_date >= :startDate /* if:startDate */
          AND o.order_date <= :endDate /* if:endDate */
          AND o.amount >= :minAmount /* if:minAmount */
          ORDER BY o.order_date DESC

3. Paginated Query​

port:
  ProductDbPortService:
    meta: dbms @datasource("product")
    methods:
      getProductList:
        meta: method -- Product list (paginated)
        params:
          input: ProductSearchDTO
        return: Page<ProductDTO>
        pageable: true
        nativeQuery: true
        query: |
          SELECT p.id, p.name, p.price, p.category
          FROM products p
          WHERE p.active = true
          AND p.name LIKE CONCAT('%', :keyword, '%') /* if:keyword */

4. Aggregate Statistics Query​

port:
  StatisticsDbPortService:
    meta: dbms @datasource("analytics")
    methods:
      getSalesStatistics:
        meta: method -- Sales statistics
        params:
          startDate: date
          endDate: date
        return: List<SalesStatsDTO>
        nativeQuery: true
        query: |
          SELECT 
            DATE_FORMAT(order_date, '%Y-%m') AS month,
            COUNT(*) AS order_count,
            SUM(amount) AS total_amount
          FROM orders
          WHERE order_date BETWEEN :startDate AND :endDate
          GROUP BY DATE_FORMAT(order_date, '%Y-%m')
          ORDER BY month DESC

5. Complex JOIN Query​

port:
  ReportDbPortService:
    meta: dbms @datasource("report")
    methods:
      getUserOrderSummary:
        meta: method -- Order summary by user
        params:
          userId: long
        return: UserOrderSummaryDTO
        nativeQuery: true
        query: |
          SELECT 
            u.name AS user_name,
            u.email,
            COUNT(o.id) AS order_count,
            COALESCE(SUM(o.amount), 0) AS total_amount,
            MAX(o.order_date) AS last_order_date
          FROM users u
          LEFT JOIN orders o ON u.id = o.user_id
          WHERE u.id = :userId
          GROUP BY u.id, u.name, u.email

6. Real-World Example (Car Info)​

dto:
  CarInfoInput:
    meta: dto
    fields:
      brand: string -- Brand name

  CarInfoOutput:
    meta: dto
    fields:
      brand: string -- Brand name
      description: string -- Description
      modelId: string -- Model ID

port:
  CarInfoPortService:
    meta: dbms @datasource("main")
    methods:
      findByBrand:
        meta: method -- Find vehicle info by brand
        params:
          input: CarInfoInput
        return: List<CarInfoOutput>
        nativeQuery: true
        query: |
          SELECT 
            brand, 
            description, 
            model_id 
          FROM car_info
          WHERE brand = :brand

Dynamic SQL Advanced Features​

1. Block-level Conditional Control​

query: |
  SELECT * FROM users
  WHERE 1=1
  /* if:searchCriteria != null */
  AND (
    name LIKE CONCAT('%', :name, '%')
    OR email LIKE CONCAT('%', :email, '%')
  )
  /* if-end */
  /* if:ageRange != null */
  AND age BETWEEN :minAge AND :maxAge
  /* if-end */

2. Automatic Sorting (sortCode Parameter)​

# If the SearchDTO has a sortCode field, an ORDER BY clause is auto-generated
# Input example: {"sortCode": "name+,age-"}
# Generated SQL: ORDER BY name ASC, age DESC

3. Query Reference (ref Feature)​

port:
  ItemDbPortService:
    meta: dbms @datasource("item")
    methods:
      getItemList:
        meta: method -- Item list query
        params:
          input: ItemSearchDTO
        return: List<ItemDTO>
        nativeQuery: true
        query: |
          SELECT id, name, price FROM items
          WHERE active = true
          AND name LIKE CONCAT('%', :keyword, '%') /* if:keyword */

      getItemListPaged:
        meta: method -- Item list query (paginated)
        params:
          input: ItemSearchDTO
        return: Page<ItemDTO>
        pageable: true
        ref: getItemList  # References getItemList's query for pagination

Spring Boot DataSource Configuration​

Single DataSource​

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/maindb
    username: user
    password: password
    driver-class-name: com.mysql.cj.jdbc.Driver

Multiple DataSources (Java Configuration)​

@Configuration
public class DataSourceConfig {
    
    @Primary
    @Bean("main")
    @ConfigurationProperties("spring.datasource")
    public DataSource mainDataSource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean("order")
    @ConfigurationProperties("spring.datasource.order")
    public DataSource orderDataSource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean("analytics")
    @ConfigurationProperties("spring.datasource.analytics")
    public DataSource analyticsDataSource() {
        return DataSourceBuilder.create().build();
    }
}

Important Notes​

SQL Injection Prevention​

# Good example: Using named parameters
query: |
  SELECT * FROM users 
  WHERE name = :name  -- Safe parameter binding

# Bad example: Using string substitution (security risk)
query: |
  SELECT * FROM users 
  WHERE name = '${name}'  -- SQL injection risk

Performance Optimization​

  • Always use pagination for large datasets
  • Set appropriate indexes
  • Use JOINs to prevent N+1 problems
  • Arrange WHERE conditions considering index usage