cborm
Install
v3.x
v3.x
  • Introduction
  • Intro
    • Release History
      • What's New With 3.9.0
      • What's New With 3.8.0
      • What's New With 3.7.0
      • What's New With 3.6.0
      • What's New With 3.5.0
      • What's New With 3.4.0
      • What's New With 3.3.0
      • What's New With 3.2.x
      • What's New With 3.1.0
      • What's New With 3.0.0
    • About This Book
    • Author
  • Getting Started
    • Overview
    • Installation
    • Basic Crud - Services
    • Basic Crud - ActiveEntity
  • Base ORM Service
    • Overview
    • Service Properties
    • Concrete Services
    • Service Methods
      • Criteria Queries
        • getRestrictions
        • newCriteria
      • Creation - Population
        • new
        • populate
        • populateFromJSON
        • populateFromQuery
        • populateFromXML
      • Counters
        • count
        • countWhere
        • exists
      • Deleting Entities
        • delete
        • deleteAll
        • deleteByID
        • deleteByQuery
        • deleteWhere
      • Entity Convenience Methods
        • getDirtyPropertyNames
        • getEntityGivenName
        • getEntityMetadata
        • getKey
        • getKeyValue
        • getPropertyNames
        • getTableName
        • isDirty
        • refresh
      • Finders
        • findit
        • findOrFail
        • findByExample
        • findWhere
        • findAll
        • findAllWhere
      • Getters
        • get
        • getOrFail
        • getAll
      • ORM Session
        • clear
        • evict
        • evictCollection
        • evictQueries
        • getSessionStatistics
        • isSessionDirty
        • merge
        • sessionContains
      • Querying
        • executeQuery
        • list
      • Saving Entities
        • save
        • saveAll
      • Utility Methods
        • autoCast
        • createService
        • idCast
        • nullValue
        • when
    • Dynamic Finders- Counters
      • Method Signatures
      • Method Expressions
      • Query Options
    • Automatic Java Types
  • Virtual Services
    • Overview
    • Service Properties
    • Concrete Virtual Services
  • Active Record
    • Active Entity Overview
    • Constructor Properties
    • Usage
    • Validation
  • Criteria Queries
    • Criteria Builder
      • Getting Started
      • Restrictions
        • Value Casting
        • SQL Restrictions
      • Modifiers
      • Results
      • Associations
      • Projections & Aggregates
    • Detached Criteria Builder
      • Getting Started
      • Projections
      • Subqueries
      • DetachedSQLProjection()
      • Criterias
      • Associations
    • Help! I'm Not Getting the Result I expected!
  • Advanced Features
    • Automatic REST Crud
    • Hibernate Logging
    • Mementifier
    • ORM Events
      • Custom Event Handler
    • Unique Property Validation
Powered by GitBook
On this page
  • Method Signature
  • Arguments
  • SQL Params

Was this helpful?

Edit on Git
Export as PDF
  1. Criteria Queries
  2. Criteria Builder
  3. Restrictions

SQL Restrictions

SQL restrictions will allow you to add ad-hoc SQL to the criteria you are building. Simple enough, but we have made great strides to make this look easy for a developer but behind the scenes we take each sql you pass and compile it so we can abstract all the native Java types for you. This means that you can use it in a similar manner to executeQuery() in CFML.

Method Signature

The method you will use for this restriction is sql()

/**
 * Use arbitrary SQL to modify the resultset
 *
 * @sql The sql to execute, it can contain parameters via positional `?` placeholders
 * @params This is an array of value definitions which need to be a struct of { value: , type: } or if the value is a simple value, we will try to infer it's type
 */
function sql( required string sql, array params=[] )

Arguments

The method takes in two arguments:

  • sql - The ad-hoc query to execute. You can use positional parameters via the ? placeholder.

  • params - An array of parameters to bind the SQL with, can be simple values or a struct of a values and a supported type or a combination of both.

SQL Params

The parameters you bind the SQL with can be of two types

  1. Plain values.

  2. Typed values

If you use plain values, then we will INFER the type from it, which is not as accurate as using a typed value. A typed value is a struct with the value and a valid type.

c.sql( "isActive = true" );

// simple values
c.sql( "id = ?", [ 123 ] );
c.sql( "userName = ? and firstName like ?", [ "joe", "%joe%"] );

// strong typed values
c.sql( "id = ?", [ { value:123, type=c.TYPES.integer } ] );
c.sql( "isActive = ?", [ { value:true, type=c.TYPES.boolean } ] );
c.sql( "userName = ? and firstName like ?", [
    { value : "joe", type : "string" },
    { value : "%joe%", type : "string" }
] );

Valid Types

Below you can see the TYPES struct available in the criteria builder object which map the CFML types to the native Hibernate Types.

this.TYPES = {
    "string"      : "StringType",
    "clob"        : "ClobType",
    "text"        : "TextType",
    "char"        : "ChareacterType",
    "boolean"     : "BooleanType",
    "yesno"       : "YesNoType",
    "truefalse"   : "TrueFalseType",
    "byte"        : "ByteType",
    "short"       : "ShortType",
    "integer"     : "IntegerType",
    "long"        : "LongType",
    "float"       : "FloatType",
    "double"      : "DoubleType",
    "bigInteger"  : "BigIntegerType",
    "bigDecimal"  : "BigDecimalType",
    "timestamp"   : "TimestampType",
    "time"        : "TimeType",
    "date"        : "DateType",
    "calendar"    : "CalendarType",
    "currency"    : "CurrencyType",
    "locale"      : "LocaleType",
    "timezone"    : "TimeZoneType",
    "url"         : "UrlType",
    "class"       : "ClassType",
    "blob"        : "BlobType",
    "binary"      : "BinaryType",
    "uuid"        : "UUIDCharType",
    "serializable": "SerializableType"
};

Inferred Types

The inferred types we infer are the following and in the following order.

  1. Binary

  2. Boolean

  3. Time

  4. Date

  5. uuid

  6. float

  7. numeric

  8. url

  9. string

  10. text

PreviousValue CastingNextModifiers

Last updated 5 years ago

Was this helpful?