Attribute
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:
|
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:
|
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> 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 | optional | 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 paramsattribute .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 | optional | 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 below. | ||
generated | optional | never | Specifies that this property value is actually generated by the {{database {never|always |
insert}}}
|
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
|
|
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.
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 |
Attribute |
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: integerlongshort |
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: falsetrue |
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: dbvm |
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.