StockJdbcRepository

StockJdbcRepository.groovy

@Repository
  class StockJdbcRepository {

    final NamedParameterJdbcTemplate inventoryJdbcTemplate
    @Autowired
    StockJdbcRepository(NamedParameterJdbcTemplate inventoryJdbcTemplate) {
          this.inventoryJdbcTemplate = inventoryJdbcTemplate
      }

      List<Stock> findAllBySkuInAndOrganizationId(Set<String> skus, Long organizationId) {
          MapSqlParameterSource parameters = [
              SKUS                : skus,
              ORGANIZATION_ID     : organizationId
          ]

          String sql = """
              SELECT s.id,
                  s.organization_id,
                  s.quantity,
                  s.sku,
                  s.warehouse_id,
                  w.description,
                  w.lead_time_business_days,
                  w.location,
                  w.priority,
                  w.sum,
                  (SELECT SUM(c.quantity) FROM commitment c WHERE c.stock_id = s.id) total_committed_quantity
              FROM stock s LEFT JOIN warehouse w ON s.warehouse_id = w.id
              WHERE (s.sku IN (:SKUS)) AND s.organization_id = :ORGANIZATION_ID
          """

          inventoryJdbcTemplate.query(sql, parameters, new RowMapper() {
              def mapRow(ResultSet rs, int index) throws SQLException {
                  new Stock(
                      sku: rs.getInt('sku'),
                      organizationId: rs.getInt('organization_id'),
                      quantity: rs.getInt('quantity'),
                      totalCommittedQuantity: rs.getInt('total_committed_quantity'),
                      warehouse: new Warehouse(
                          id: rs.getInt('warehouse_id'),
                          description: rs.getString('description'),
                          leadTimeBusinessDays: rs.getInt('lead_time_business_days'),
                          location: rs.getString('location'),
                          priority: rs.getInt('priority'),
                          sum: rs.getBoolean('sum')
                      )
                  )
              }
          }) as List<Stock>
      }

  //...
}