ColdFusion was originally designed to be a language to bridge backend databases to Web pages. As such, handing record sets from database is a key part to using ColdFusion.
RDBMS
ColdFusion has built in support for many flavors of database including: Microsoft SQL, MySQL, Oracle, Derby, DB, and Postegres. However ColdFusion can interact with any RDBMS with a JDBC driver.
Datasources
In order to work with a particular database in ColdFusion, you must first create a datasource. A datasource is the collection of settings you use to communicate with a database, such as database type, sever, port, tablespace, or database, username and password. You collect all of these settings and give them a meaningful name. ColdFusion then allows you to refer to just the datasource name when connecting to the database. The server will handle maintaining connections, closing them, persisting them. In short ColdFusion abstracts database connections into datasources.
Queries
A recordset returned from a database will be turned into
a special type of variables called, in ColdFusion, a query
variable. <cfloop> and <cfoutput> along with a number of other
tags allow you to pass a query attribute in for easy iteration.
Within one of those iterators you can just refer to the column
you are calling:
<cfloop query=”personRS”>
<cfset fullname = lastName & “, “ & firstName />
</cfloop>
Alternately you can choose to manually loop through the query (broken up for readability):
<cfloop index=”i” from=”1” to=”# personRS.recordCount#”>
<cfset fullname = personRS .lastName[i] />
<cfset fullname = fullname & “, ” />
<cfset fullname = fullname & personRS .firstName[i] />
</cfloop>
In order to help looping and paging, queries have a few special properties.
ColumnList |
A comma delimited list of columns in the query. |
CurrentRow |
The current row that is being accessed in the context of a cfoutput or cfloop call. |
RecordCount |
The total number of records |
SQL
Calling SQL is a matter of using <cfquery> with a datasource:
<cfquery name=”resultSet” datasource=”cfartgallery”>
SELECT * FROM artists
</cfquery>
Additionally ColdFusion allows you to do parameterized queries using the cfqueryparam tag.
<cfquery name=”resultSet” datasource=”cfartgallery”>
SELECT *
FROM artists
WHERE artistid =
<cfqueryparam cfsqltype=”cf_sql_integer” value=”1” />
</cfquery>
Stored Procedure
Stored procedures can also be called from ColdFusion using the <cfstoredproc> tag.
ORM
As of ColdFusion 9, Hibernate is baked into ColdFusion and allows CFCs to be mapped to database tables.
Enabling ORM
To enable ORM for an application, the following settings are required in the Application.cfc:
This.datasource |
The name of the datasource to use for ORM CFCs. |
This.ormenabled |
A Boolean which turns on ORM. |
Basics
Assuming that a table named “person” with columns firstName, lastName, and email exists, and that there is a primary key personID, here is the code for a CFC named person.cfc that is mapped to the table:
<cfcomponent persistent=”true”>
<cfproperty name=”personID” fieldtype=”id” />
<cfproperty name=”firstName” />
<cfproperty name=”lastName” />
<cfproperty name=”email”/>
</cfcomponent>
Data from the table can then be retrieved using the EntityLoad() function:
<cfset resultSet = entityLoad(“artists”) />
Or one record (with id = 5) can be requested:
<cfset resultSet = entityLoad(“artists”, 5) />
Creating a new record uses the EntityNew() function:
<cfset person = entityNew(“artists”) />
Then whether you are creating or updating a record, the code is the same:
<cfset person.setFirstName(“Terry”) />
<cfset person.setLastName(“Ryan”) />
<cfset person.setEmail(“terry@terrenceryan.com”) />
<cfset EntitySave(person) />
Deleting records would use the EntityDelete() Function:
<cfset EntityDelete(person) />
Relationships
CFCs support Hibernate relationships, and allow you to setup one-to-one, one-to-many, many-to-one, and many-to-many relationships.
Mappings
CFCs also support more advanced features of Hibernate including: Join Mapping, Collection Mapping, Inheritance Mapping, and Embedded Mapping.
Hibernate Options
In additional to all of the features that ColdFusion enables through CFCs, it also can take hbm.xml files to model your objects.
Also ColdFusion allows for making queries against the object model using HQL, Hibernate’s SQL-like query language.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}