Map the properties

The cfproperty tag is used to define:

  • Primary or composite key

  • Columns

  • Relationships

  • Versioning

The following table lists the common attributes that are used to define these mappings:

Attribute

Req/Optional

Default

Description

default

optional

 

This sets the default value on the property when the object is created.

fieldtype

optional

column

This attribute is used to specify the type of the property. Use this attribute to specify the following:

  • Primary key: Specify fieldtype = "id" to map a property to the primary key in the table. For details, see Primary key.

  • Column: Specify the fieldtype = "column" to map a property to a column in the table. For details, see Column.

  • Relationship: Specify the fieldtype = "relationship_type". The relationship_type can be one-to-one, one-to-many, many-to-one, or many-to-many. For details, see Define Relationships.

  • Version: Specify the fieldtype="version” to indicate that the column contains versioned data. For details, see Version.

  • Timestamp: Specify the fieldtype="timestamp" to indicate the column contains data with timestamp. For details, see Time stamp.

  • Collection: Specify the fieldtype="collection" to define the collection mapping. For details, see Collection Mapping

name

required

 

Specifies the name of the property.

type

optional

 

Specifies the ColdFusion data type for the property.

persistent

optional

true

Specifies whether this property should be persisted. If persistent="false” all the ORM related attributes are ignored.

Set this attribute to false if you do not want this property to be persisted by ORM. For example:

<cfcomponent persistent=true> 
     <cfproperty name="a"> 
     <cfproperty name="b" persistent="true"> 
      <cfproperty name="c" persistent="false"> 
</cfcomponent>

When an object of this CFC is persisted, the properties a and b would be persisted in the database but the property c would not be persisted.

remotingFetch

optional

true

If remotingFetch is false, then the value of that attribute is not sent over flash remoting. The attribute is true by default for all properties. However, for ORM CFCs where persistent = true, the value of the remotingFetch attribute is set to false, by default, for one-to-one, one-to-many, many-to-one, or many-to-many relationships.

Primary key

Simple primary key

In a relational database, a primary key is defined as a key that uniquely identifies a row in a table. Typically, a table has one primary key that represents a single column of information in the table.

To indicate that a cfproperty maps to a primary key in the table, set the attribute fieldtype="id".

Syntax

<cfproperty 
    name="property_name" 
    fieldType="id" 
    ormtype="type" 
    column="column_name" 
    generator="increment|identity 
    |sequence|sequence-identity|seqhilo 
    |uuid|guid|native|[assigned]|select|foreign" 
    params="{key1=val1,key2=val2...}" 
    sqltype="sql_type" 
    length="column_length" 
    unsavedvalue="instantiated_instance">

Example

An example to define an assigned primary key:

<cfproperty name="artistid" fieldtype="id" column="ARTISTID" generator="assigned">

An example to define a auto-generated primary key using increment generator:

<cfproperty name="artid" fieldtype="id" column="ARTID" generator="increment">

An example to define a auto-generated primary key using a generator, which requires additional parameters:

<cfproperty name="id" fieldtype="id" column="ID" generator="sequence" params="{sequence='id_sequence'}>

Attributes

Attribute

Req/Opt

Default

Description

column

Optional

The value of the name attribute

Used to specify the primary key column name.

fieldType

Optional

 

Should be "id" for primary key. If fieldtype is not specified and the useDBForMapping=true, then the fieldtype is determined by inspecting the database.

generator

Optional

assigned

Algorithm used to generate unique identifiers for instances of the persistent CFC. See Generators for details.

length

optional

 

Use this attribute to specify the length of the column. This attribute is used for table creation only.

name

Required

 

Name of the property

ormtype

Optional

String

Used to specify the data type of the primary key. If data type is not set and the ORM setting useDBForMapping=true, then the ormtype is determined by inspecting the database.

The different data types that are supported by ColdFusion are specified in the ORM data types.

params

   

Additional parameters required by the various generators to generate the ID.

The value for the params attribute should follow the CF Struct syntax. For example: params = {key1='value1', key2='value2'}

See Generators for details.

selectkey

optional

 

The column name that is used to retrieve the primary key generated by a database trigger.

sqltype

optional

 

Use this attribute to specify the DB-specific SQLType that should be used for the column. This attribute is used for table creation only.

If this attribute is not specified, ColdFusion will automatically decide the SQLType based on the ormtype specified for this property.

unSavedValue

optional

 

An identifier property value that indicates that an instance is newly instantiated and not saved/loaded in the database

Composite Key

If the primary key consists of more than one column, it is called as a composite key. A composite key can be specified by using fieldtype='id' on all the properties that form the primary key.

Example

If in a table, the columns Order_id and Product_id form a composite key, then, they should be defined as:

<cfproperty name="Order_Id" fieldtype="id" column="Order_Id"> 
<cfproperty name="Product_Id" fieldtype="id" column="Product_Id">

For a composite key, the generator should always be "assigned".

Generators

Generators are algorithms that are used to generate unique identifiers for instances of the persistent CFC. Generators can have any one of the following values:

  • increment: This algorithm generates identifiers of type long, short, or int by incrementing a counter maintained by ORM. This is commonly used when auto-generation for the primary key is not enabled in the table and you want ORM to generate the primary key. This should be used when a single instance of ColdFusion is the only process to insert data into the table.

  • identity: For databases such as DB2, MySQL, Sybase, and MS SQL, which support identity columns, you can use identity key generation. The key is automatically generated by the database and the object is populated with the generated key. This strategy requires ORM to execute two SQL queries to insert a new object.

  • sequence: For databases such as DB2, Oracle, PostgreSQL, Interbase, McKoi, and SAP, which support sequences, you can use sequence style key generation. The key is automatically generated by the database and the object is populated with the generated key. This strategy requires ORM to execute two SQL queries to insert a new object. This generator includes the sequence parameter, which needs to be specified in the params attribute.

    For example:

    <cfproperty name="id" fieldtype="id" generator="sequence" params="{sequence='id_sequence'}">
  • native: This algorithm is commonly used to automatically generate primary keys. This generator picks identity or sequence depending upon the capabilities of the underlying database.

  • assigned: This algorithm is used when the application needs to assign its own identifier to the object. It is the default generator used by ColdFusion.

  • foreign: This is used with a <one-to-one> primary key association. In this case, the primary key is the same as the primary key of the associated object. This generator would need the property parameter, which needs to be specified in the params attribute. The value of the param property should be the name of the relationship property.

    See One-to-one relationships for details.

  • seqhilo: See www.hibernate.org/5.html.

  • uuid: See www.hibernate.org/5.html.

  • guid: See www.hibernate.org/5.html.

  • select: See www.hibernate.org/5.html.

  • sequence-identity: See www.hibernate.org/5.html.

Column

To indicate that a cfproperty maps to a column in the table, specify fieldtype="column". If the fieldtype is not specified for cfproperty, it is mapped as a column property.

Syntax

<cfproperty 
    name="Property name" 
    fieldtype="column" 
    column="column_name" 
    persistent="true|false" 
    formula="SQL expression" 
    ormtype="ormtype" 
    update="[true]|false" 
    insert="[true]|false" 
    optimisticLock="[true]|false" 
    generated="true|[false]" 
    length="column_length" 
    precision="precision" 
    scale="scale" 
    index="index_name" 
    unique = "true|[false]" 
    uniquekey="uniquekey_name" 
    notnull="true|[false]" 
    dbdefault="default_col_value" 
    sqltype="sql_type">

Example

To specify a simple property:
<cfproperty name="FIRSTNAME"/> 
To specify a property which has a different name than that of the column name:
<cfproperty name="LNAME" column="LASTNAME"/>
To specify a property which should be read-only:
<cfproperty name="password" column="THEPASSWORD" insert="false" update="false">

Attributes

Attribute

Req/Opt

Default

Description

column

optional

Name of the property

Name of the column.

dbdefault

   

This sets the default value of a column in the table when schema is exported.

fieldType

optional

column

Should be “column” for column property.

formula

optional

 

SQL expression that defines the value of the property. If you specify a formula, the column value is ignored.

See Computed property.

generated

optional

never

Specifies that this property value is actually generated by the database {never|always|insert}

  • never: Specifies that the value for this property is never generated by database

  • always: Specifies that the value for this property is always generated by database

  • insert: Specifies that the value for this property is generated at the time of insert but is not regenerated at updates.

insert

optional

true

Specifies whether the column should be included in SQL UPDATE and/or INSERT statements:

{true/false}

Set update=false and insert=false if the column needs to be read-only.

name

Required

 

Name of the property. If this attribute is not specified, the name of the property is used as the column name.

optimisticlock

optional

true

Whether updates to this property require acquisition of the optimistic lock on the table row:

{true/false}

ormtype

optional

string

Specifies the data type.

If specified, then

  • If ORM setting useDBForMapping is set to true and the table exists, then ormtype is obtained by inspecting the table

  • (Otherewise) Type, if specified, is used as ormtype

update

optional

true

Specifies whether the column should be included in SQL update statement:

{true/false}

Set update=false and insert=false if the column needs to be read-only.

DDL-only attributes

The following attributes are used only when DDL generation is required and not used for runtime.

Attribute

Req/Opt

Default

Description

dbdefault

optional

 

Specifies the default value of the column in the table.

index

optional

 

Specifies the name of an index that is created using the mapped column.

length

optional

 

Specifies the length value.

notnull

optional

false

A Boolean value that specifies whether a notnull constraint should be added for this column.

precision

optional

 

Specifies the precision value.

scale

optional

 

Specifies the scale value.

sqltype

optional

 

This allows user to override the default mapping of ormtype to SQL datatype. sqltype is used as the DB specific SQL type for a column when creating the table. If this attribute is not specified, ColdFusion will automatically decide the sqltype based on the ormtype specified for this property.

For example:

<cfProperty name="active" ormtype="char" sqltype="bit"> 
<cfProperty name="balance" ormtype="float" sqltype="decimal(13,3)">

unique

optional

 

Specifies if there should be a unique constraint on the column.

uniquekey

optional

 

Groups columns in a single unique key constraint.

Computed property

Computed property is a property whose value does not come from a column but is computed using a SQL query. Use formula attribute to specify the SQL to be used to retrieve the value for this property.
<cfcomponent persistent="true" table="ARTISTS" schema="APP"> 
    <cfproperty name="ID" column="ARTISTID" fieldtype="id"/> 
    <cfproperty name="FIRSTNAME"/> 
    <cfproperty name="LASTNAME"/> 
    <cfproperty name="NumberOfArts" formula="select count(*) from Art art where 
                        art.ArtistID=ArtistID"/> 
</cfcomponent>

Versioning

Versioning is a technique that allows you to implement concurrency control fora component. You can specify either version or timestamp property for a component.

For details, see Optimistic locking.

Note: A component can have only one versioning property, either timestamp or version. If you specify multiple versioning properties, such as two timestamps, or two versions, or a timestamp and a version, an error is thrown.

Version

Use the version attribute to indicate that the column contains versioned data. The version attribute is useful for long transactions.

Syntax

<cfproperty 
name="fieldname" 
fieldtype="version" 
column="column name" 
ormtype="type" 
generated="true|[false]" 
insert="[true]|false">

Example

To create a simple version property:

<cfproperty name="version" fieldtype="version">

Attribute

Attribute

Req/Opt

Default

Description

column

Optional

 

The name of the column that contains versioned data

fieldtype

Required

 

Should be “version” for primary key.

generated

Optional

never

Specifies if the versioned field is generated by the database. The values are "never" and "always".

insert

Optional

 

Specifies if the versioned field should be included in the SQL INSERT statement.

name

Required

 

Name of the property.

ormtype

Optional

int

The data type can be any one of the following:

integer

long

short

Time stamp

Use the timestamp attribute to indicate that the column contains time-stamped data. Use the timestamp attribute as an alternative to the version attribute.

Syntax

<cfproperty 
name="fieldname" 
fieldtype="timestamp" 
column="column name" 
generated="true|[false]" 
source="[vm]|db">

Attribute

Req/Opt

Default

Description

column

Optional

 

The name of the column that contains time-stamped data.

fieldtype

Required

 

Specifies the field type.

Specify the field type value as timestamp for a time-stamped field.

generated

Optional

false

Specifies if the timestamp field is generated by the database. You can select from the following values:

false

true

name

Required

 

Name of the property.

source

Optional

vm

Specifies the source from where the timestamp has to be retrieved. You can select from the following values:

db

vm

ORM data types

You can use any of the following ORM data types for CFCs:
  • string

  • character

  • char

  • short

  • integer

  • int

  • long

  • big_decimal

  • float

  • double

  • Boolean

  • yes_no

  • true_false

  • text

  • date

  • timestamp

  • binary

  • serializable

  • blob

  • clob

Escaping SQL keywords in table and column name

ColdFusion automatically escapes the table name or column name if it is an SQL keyword or if there is a space in it.

The list of SQL keywords are present in <CF_HOME>/lib/sqlkeywords.properties file. This file contains standard ANSI SQL keywords and some database-specific keywords. You can modify this file to include any other SQL keyword that is missing. In case you are adding SQL keyword for a database other than the ones specified in this file, you should also add it to the 'ANSI' list so that ColdFusion can use it.