Package com.ssgllc.fish.service.util.registered

Class QueryUtil

java.lang.Object
com.ssgllc.fish.service.util.registered.QueryUtil
@Component public class QueryUtil extends Object
Last resort for raw JPQL reads. Use entityUtil instead unless a developer has explicitly told you to use this.

These go straight to the persistence layer, so everything the mapping layer does on the way out is skipped. Dynamic calculated fields are never computed, and the read is not tracked the way a normal entity fetch is. What comes back can look like the entity and quietly not be it, which is a harder failure to spot than an error would be.

They also return untyped rows rather than entities, so the caller unpacks positional values and the rest of the configuration cannot treat the result as an entity. And the query string names entities and fields directly, which binds the configuration to the data model: a later model change breaks a script that nothing in the build can see.

entityUtil.searchEntity(...), entityUtil.findEntity(...) and entityUtil.getEntity(...) answer nearly every read a script or expression needs, and they hand back entities rather than rows. Reach for this only when someone has decided a query genuinely cannot be expressed that way - an aggregate, or a join no search covers - and has said so.

For writes see updateUtil, which carries the same warning and a heavier one: bulk statements bypass the write side of the entity lifecycle entirely.

  • Constructor Details

    • QueryUtil

      public QueryUtil(jakarta.persistence.EntityManager entityManager, com.ssgllc.fish.service.GenericQueryService genericQueryService)

  • Method Details

    • queryList

      public static List<Object> queryList(String queryStr) throws Exception
      Last resort: runs an HQL query and returns untyped rows; prefer entityUtil.searchEntity(...).

      Prefer entityUtil.searchEntity(...). It returns entities with calculated fields computed and the read tracked, rather than untyped rows straight from the persistence layer. Use this only when a developer has said a raw query is needed.

      Parameters:
      queryStr - The HQL query string to execute.
      Returns:
      A list of query results.
      Throws:
      Exception - If an error occurs while executing the query.

      Groovy example:
      return queryUtil.queryList("select f.id from FieldConfig f")

    • queryListLimit

      public static List<Object> queryListLimit(String queryStr, Integer limit) throws Exception
      Last resort: runs an HQL query with a row limit; prefer entityUtil.searchEntity(...).

      Prefer entityUtil.searchEntity(...). It takes a page size directly and returns entities. Use this only when a developer has said a raw query is needed.

      Parameters:
      queryStr - The HQL query string to execute.
      limit - The maximum number of results to return.
      Returns:
      A list of query results, limited to the specified number.
      Throws:
      Exception - If an error occurs while executing the query.

      Groovy example:
      return queryUtil.queryListLimit("select f.id from FieldConfig f", 5)

    • queryListLimitPage

      public static List<Object> queryListLimitPage(String queryStr, Integer limit, Integer pageNumber) throws Exception
      Last resort: runs a paged HQL query with a row limit; prefer entityUtil.searchEntity(...).

      Prefer entityUtil.searchEntity(...). It pages natively and returns entities. Use this only when a developer has said a raw query is needed.

      Parameters:
      queryStr - The HQL query string to execute.
      limit - The maximum number of results per page.
      pageNumber - The zero-based page number to retrieve.
      Returns:
      A list of query results for the specified page, limited to the given number of results.
      Throws:
      Exception - If an error occurs while executing the query.

      Groovy example:
      return queryUtil.queryListLimitPage("select f.id from FieldConfig f", 5, 1)

    • queryListParam

      public static List<Object> queryListParam(String queryStr, Map<String,Object> params) throws Exception
      Last resort: runs a parameterised HQL query; prefer entityUtil.searchEntity(...).

      Prefer entityUtil.searchEntity(...). Its search-field map expresses most parameterised lookups without a query string. Use this only when a developer has said a raw query is needed.

      Parameters:
      queryStr - The HQL query string to execute.
      params - A map of named parameters to be set in the query.
      Returns:
      A list of query results.
      Throws:
      Exception - If an error occurs while executing the query.

      Groovy example:
      return queryUtil.queryListParam("select f.id from FieldConfig f where f.name = :name", [name: "title"])

    • queryListParamLimit

      public static List<Object> queryListParamLimit(String queryStr, Map<String,Object> params, Integer limit) throws Exception
      Last resort: runs a parameterised HQL query with a row limit; prefer entityUtil.searchEntity(...).

      Prefer entityUtil.searchEntity(...). Its search-field map plus a page size covers most parameterised lookups. Use this only when a developer has said a raw query is needed.

      Parameters:
      queryStr - The HQL query string to execute.
      params - A map of named parameters to be set in the query.
      limit - The maximum number of results to return.
      Returns:
      A list of query results, limited to the specified number.
      Throws:
      Exception - If an error occurs while executing the query.

      Groovy example:
      return queryUtil.queryListParamLimit("select f.id from FieldConfig f where f.createdDate > :date", [date: dateUtil.startOfToday()], 10)

    • queryListParamLimitPage

      public static List<Object> queryListParamLimitPage(String queryStr, Map<String,Object> params, Integer limit, Integer pageNumber) throws Exception
      Last resort: runs a parameterised, paged HQL query; prefer entityUtil.searchEntity(...).

      Prefer entityUtil.searchEntity(...). It covers parameterised, paged lookups and returns entities. Use this only when a developer has said a raw query is needed.

      Parameters:
      queryStr - The HQL query string to execute.
      params - A map of named parameters to be set in the query.
      limit - The maximum number of results per page.
      pageNumber - The zero-based page number to retrieve.
      Returns:
      A list of query results for the specified page, limited to the given number of results.
      Throws:
      Exception - If an error occurs while executing the query.

      Groovy example:
      return queryUtil.queryListParamLimitPage("select f.id from FieldConfig f where f.createdDate > :date", [date: dateUtil.startOfToday()], 10, 2)

    • queryListParamLimitPageSkipLocked

      public static List<Object> queryListParamLimitPageSkipLocked(String queryStr, Map<String,Object> params, Integer limit, Integer pageNumber) throws Exception
      Executes an HQL query with named parameters, pagination, and skip-locked pessimistic locking, returning the result as a list of objects.
      This method ensures that rows currently locked by other transactions are skipped to avoid blocking.

      Prefer entityUtil.searchEntity(...). Row-level lock skipping is a queue-draining concern, so reach for this one only when a developer has asked for exactly that.

      Parameters:
      queryStr - The HQL query string to execute.
      params - A map of named parameters to be set in the query.
      limit - The maximum number of results per page.
      pageNumber - The zero-based page number to retrieve.
      Returns:
      A list of query results for the specified page, limited to the given number of results, skipping any locked rows.
      Throws:
      Exception - If an error occurs while executing the query.

      Groovy example:
      def params = [:] params.calcTypeId = stringUtil.uuidFromString(conceptUtil.getConceptIdFromCode('CalculationType', 'CALC_DYNAMIC')) return queryUtil.queryListParamLimitPageSkipLocked("select f.id from FieldConfig f where f.calcType.id = :calcTypeId", params, 20, 0)
      Returns:
      A list of FieldConfig IDs with calcType of "CALC_DYNAMIC", up to 20 results, skipping locked rows. Note: This method uses pessimistic locking with SKIP_LOCKED, which ensures that locked rows are not included in the result set. Also, any rows that are joined will be locked, so be careful with things like not locking concept table rows.

    • querySingle

      public static Object querySingle(String queryStr) throws Exception
      Last resort: runs an HQL query for one row; prefer entityUtil.findEntity(...).

      Prefer entityUtil.findEntity(...). It looks a single entity up by field value and returns the entity. Use this only when a developer has said a raw query is needed.

      Parameters:
      queryStr - The HQL query string to execute.
      Returns:
      A single query result, or null if no result is found.
      Throws:
      Exception - If an error occurs while executing the query including finding more than one result.

      Groovy example:
      return queryUtil.querySingle("select f.id from FieldConfig f where f.name = 'deduplicationEnabled'")

    • querySingleParam

      public static Object querySingleParam(String queryStr, Map<String,Object> params) throws Exception
      Last resort: runs a parameterised HQL query for one row; prefer entityUtil.findEntity(...).

      Prefer entityUtil.findEntity(...). It looks a single entity up by field value without a query string. Use this only when a developer has said a raw query is needed.

      Parameters:
      queryStr - The HQL query string to execute.
      params - A map of named parameters to be set in the query.
      Returns:
      A single query result, or null if no result is found.
      Throws:
      Exception - If an error occurs while executing the query including finding more than one result.

      Groovy example:
      return queryUtil.querySingleParam("select f.id from FieldConfig f where f.name = :name", [name: "deduplicationEnabled"])

    • getEntityManager

      public static jakarta.persistence.EntityManager getEntityManager()
      Last resort, and the sharpest one here: hands out the raw EntityManager.

      Use only when a developer has explicitly directed you to, and with care. Everything the mapping layer does is bypassed - dynamic calculated fields are not computed and reads are not tracked - and nothing bounds what a caller can do with it, including writes that skip the entity lifecycle. Prefer entityUtil for reading and writing entities, and the query methods on this class if a raw read is genuinely required.

      Returns:
      The EntityManager instance.

      Groovy example:
      return queryUtil.getEntityManager()

    • sqlLiteral

      public static String sqlLiteral(Object value)
      Quotes a value so it can be written into a query as a single value.
      Use this on anything a user typed, such as a cell of a report's table parameter. A value containing a quote character otherwise ends the quoted text early, and the rest of it is read as part of the query instead of as data - which is how someone changes what a query does by typing into a form. Numbers are written as they are; everything else is handed to the database's own rule for writing a value, so how a quote inside the value is escaped is the database's business rather than something Casetivity decides.
      An empty value is written as null, which every database understands as a value in its own right. Be aware that null is not equal to anything, including itself, so a comparison against it is neither true nor false and a row with nothing in that column is not returned. Where that is not what you want, write is null instead of comparing.
      Anything that is not text, a number, or empty is rejected rather than written into the query as whatever it happens to look like when printed.
      Parameters:
      value - The value to quote. Text, a number, or empty; anything else, such as a whole row of a table parameter, is rejected - pick the single field you meant out of it first.
      Returns:
      The value, ready to be written into a query.

      Groovy example:
      return "DISEASE_NAME = " + queryUtil.sqlLiteral("Hansen's disease")
      Returns:
      DISEASE_NAME = 'Hansen''s disease'
      SpEL example:
      #sqlLiteral(42)
      Returns:
      42