ColdFusion-ORM: Using CRUD Functions
Previous Related Posts:
Getting Started with ORM
First of all, pardon me for posting this example – I could have easily clubbed these concepts with my first post: Getting Started with ORM. I promise that in my further posts, I will club more concepts into a single example (but still try to keep it simple).
Task:
Example that demonstrates the CRUD Functions in ColdFusion-ORM
Stuff that you would learn:
- To work with the CRUD Functions – EntityNew, EntityLoad, EntitySave, EntityDelete
- ormflush() function to force-commit ORM calls
- OrmExecuteQuery function to execute HQL
- Saving the mapping file
Steps to Run the example:
- This example needs the cfartgallery datasource. This is shipped with ColdFusion by default.
- Create a directory say “crudorm” under webroot.
- Create the following files – Application.cfc, CArtists.cfc and index.cfm.
- Run the URL http://localhost:8500/crudorm/index.cfm
I have interspersed the example with a lot of comments. You can understand the concept by just following the comments starting with Application.cfc and then CArtists.cfc and then index.cfm.
Application.cfc
component
{
//Name of the application
this.name = "ORM_CRUDExample";
//ormenabled should be set to true so that ORM is enabled for this application
this.ormenabled = "true";
//Set the datasource that needs to be used by the ORM Functions. You can also set this in the ormsettings struct
this.datasource = "cfartgallery";
/*
ColdFusion-ORM uses hibernate as its under-lying engine. ColdFusion-ORM generates
the hbm.xml file which contains the hibernate mapping. To save the hibernate mapping
that is generated, you need to set savemapping flag to true. In this case, CArtists.hbm.xml
file will be generated in the same folder as that of the application.
*/
this.ormsettings.savemapping="true";
}
CArtists.cfc
(I have not added any comments to this file. If you need to learn about the different attributes used here, refer the example Getting Started with ORM)
component persistent="true" entityname="Artists" table="Artists"
{
property name="id" column="ARTISTID" generator="increment";
property name="firstname";
property name="lastname";
property name="address";
property name="city";
property name="state";
property name="postalcode";
property name="email";
property name="phone";
property name="fax";
property name="thepassword";
}
index.cfm
<!---
This example will teach you
- how to do CRUD operations on this table using the Entity* functions.
- ormflush function.
- how to use ORMExecuteQuery function.
- Saving the mapping file
cfartgallery datasource is used for this application.
Artists table is one of the table in cfartgallery which contains a list of
artists records. This table is used in this example.
--->
<cfscript>
ormreload();
/*
Load the Artist records to display them. There are a number of
variations to the EntityLoad method which will help you to retrieve
the records the way you want. Refer the documentation for EntityLoad for details.
*/
WriteOutput("<b>Initial state of the table<br /></b>");
DisplayArtists(EntityLoad("Artists"));
/*
Create a new Artist object and set the properties. This will
be inserted to the table in the next step.
EntityNew function takes entityname as input and creates a
fresh object of the entity.
*/
newArtistObj = EntityNew("Artists");
newArtistObj.setfirstname("John");
newArtistObj.setlastname("Smith");
newArtistObj.setaddress("5 Newport lane");
newArtistObj.setcity("San Francisco");
newArtistObj.setstate("CA");
newArtistObj.setPostalCode("90012");
newArtistObj.setphone("612-832-2343");
newArtistObj.setfax("612-832-2344");
newArtistObj.setemail("jsmith@company.com");
newArtistObj.setThePassword("jsmith");
/*
Insert the new artist object that you just created.
EntitySave will insert the newArtistObj into the database.
EntitySave is used for both update and insert. ColdFusion
will smartly figure out whether it is an update or insert. As we are
sure that this is an insert operation, we set the secondparameter to "true".
ColdFusion now will always do the insert operation.
*/
EntitySave(newArtistObj, true);
/*
Call ormflush so that the Insert SQL runs immediately. If ormflush
is not called, all the CRUD operations in this page will be flushed at
the end of the request.
*/
ormflush();
/*
Display the artist records now to check if the new record got added.
This time I have used a different function to retrieve the Artist records.
This is just to introduce you to the ORMExecuteQuery set of functions.
ORMExecuteQuery takes HQL as input and returns one-entity/array-of-entities/
string/array-of-strings depending on the HQL. HQL is the query language used
in hibernate to retrieve objects based on complex joins. This function has
a number of overloads. Please refer the documentation for more details.
*/
WriteOutput("<b>After adding the new record (Notice the Record with FirstName John being added)<br /></b>");
DisplayArtists(ORMExecuteQuery("from Artists"));
/*
Update the new Artist record. Change the Phone number.
You dont need to call EntitySave method here as the newArtistObj is
an entity maintained by ORM. Hence the updates to this entity will be automatically committed.
*/
newArtistObj.setphone("612-832-1111");
ormflush();
/*
Display the Artist records now to check if the new record got updated.
*/
WriteOutput("<b>After updating the new record (Notice the phone number with FirstName John updated)<br /></b>");
DisplayArtists(ORMExecuteQuery("from Artists"));
/*
Delete the record. Also call ormflush so that the Delete SQL gets run immediately
*/
EntityDelete(newArtistObj);
ormflush();
/*
Display the Artist records now to check if the new record that was added, got deleted
*/
WriteOutput("<b>After deleting the new record (Notice the Record with FirstName John deleted)<br /></b>");
DisplayArtists(ORMExecuteQuery("from Artists"));
</cfscript>
<!---A simple function to display the artist records in a table--->
<cffunction name="DisplayArtists">
<cfargument name="artistArr">
<cfoutput>
<table border="1">
<tr>
<td>ID</td>
<td>NAME</td>
<td>ADDRESS</td>
<td>PHONE</td>
<td>FAX</td>
<td>EMAIL</td>
</tr>
<cfloop array="#artistArr#" index="artistsObj">
<tr>
<td>#artistsObj.getid()#</td>
<td>#artistsObj.getfirstname()# #artistsObj.getlastname()#</td>
<td>#artistsObj.getaddress()# #artistsObj.getCity()# #artistsObj.getState()# #artistsObj.getPostalCode()#</td>
<td>#artistsObj.getphone()#</td>
<td>#artistsObj.getFax()#</td>
<td>#artistsObj.getemail()#</td>
</tr>
</cfloop>
</table>
</cfoutput>
</cffunction>
Getting Started with ColdFusion-ORM
ColdFusion-ORM is the one of the top features in ColdFusion 9. This feature is close to my heart as I have worked on it the whole release right from its inception – specification, design, development and testing – most of the time involved in testing it. Believe it or not, I have written more than 500 test cases for ORM which include 2000+ CFM/CFC files. I am making an effort to help the community to get started on this feature by posting a series of examples that would introduce you to ColdFusion-ORM. The documentation does cover the ColdFusion-ORM stuff in good detail. But I believe in the “Learn as you code” approach. I will post a series of examples – each example would introduce a new concept in ColdFusion-ORM. I hope you would like this approach. and as you read the posts, do provide me with timely feedback so that I can mend my further examples as appropriate.
Task:
Basic ColdFusion-ORM Example
Stuff that you would learn:
To Configure ORM for a ColdFusion application.
Configuring a datasource to be used by ORM.
Mapping a table to a CFC.
Using the EntityLoad method and iterating over the resultset.
To Log the SQLs generated by ORM to the ColdFusion console.
Steps to Run the example:
- This example needs the cfartgallery. This is shipped with ColdFusion by default.
- Create a directory say “basicorm” under webroot.
- Create the following files – Application.cfc, Artists.cfc and index.cfm.
- Run the URL http://localhost:8500/basicorm/index.cfm
I have interspersed the example with a lot of comments. You can understand the concept by just following the comments starting with Application.cfc and then Artists.cfc and then index.cfm.
Application.cfc
/*
You always need a Application.cfc to work with ORM. You
need to set ormenabled to true for the application to use ORM.
Note that ORM Configuration and mapping is generated and loaded
when the aplication starts.
*/
component
{
//Name of the application
this.name = "ORM_BasicExample";
//ormenabled should be set to true so that ORM is enabled for this application
this.ormenabled = "true";
//Set the datasource that needs to be used by the ORM Functions. You can also set this in the ormsettings struct
this.datasource = "cfartgallery";
//Set logSQL to true. This will log SQLs to the console.
this.ormsettings.logSQL = "true";
}
Artists.cfc
/*
FileName: Artists.cfc
Usually one CFC maps to one table. In this case, the CFC Artists.cfc maps to the table Artists.
Persistent attribute should be set to true for cfcomponent so that ColdFusion knows
that mapping needs to generated for this CFC.
If the name of the component is not the same as that of the table, specify the table
name.
EntityName should be specified for the CFC which will be used by the Entity* functions
to do CRUD operations on this CFC. If EntityName is not specified, then, the name of
the component is taken as the entityname.
*/
component persistent="true" entityname="Artists" table="Artists"
{
/*Usually one property corresponds to one column in the table. In this case, the property
"firstname" corresponds to the column FIRSTNAME in the Employees table.
*/
property name="firstname";
/*
fieldtype="id" denotes that the property "id" is the primary key of the table Art.
ColdFusion will figure it out by inspecting the database if not specified.
column attribute is used to define the name of the field in the table to which this
property maps to. If the column attribute is not specified, then, the name of the
property is taken as the name of the field.
Generator attribute is used to auto-generate the primarykey when a new record is added.
Here we use increment generator. To know more about generators, refer ColdFusion ORM documentation.
*/
property name="id" column="ARTISTID" generator="increment";
/*
Few other properties which map to the appropriate columns in the Employees table
*/
property name="lastname";
property name="address";
property name="city";
property name="state";
property name="postalcode";
property name="email";
property name="phone";
property name="fax";
property name="thepassword";
}
index.cfm
<!---
FileName: index.cfm
This is the most basic example in this kit. This example will teach you
- how to map a table to a CFC
- how to Retrieve records using the EntityLoad function.
cfartgallery datasource is used for this application.
Artists table is one of the table in cfartgallery which contains a list of
artists records. This table is used in this example.
--->
<cfscript>
/*
Call ormreload() function so that ORM configuration/mapping is always created afresh
when index.cfm is called. This is convenirent to have in development time. You shouldn't
do this in production
*/
ormreload();
/*
Load the Artist records to display them. There are a number of
variations to the EntityLoad method which will help you to retrieve
the records the way you want. Refer the documentation for EntityLoad for details.
*/
artists = EntityLoad("Artists");
/*
Display the Artist records that you just loaded
*/
for (i=1;i<ArrayLen(artists);i++)
{
artistObj = artists[i];
WriteOutput("<b>" & artistObj.getFirstName() & " " & artistObj.getLastName() & "<br></b>");
WriteOutput("Address: " & artistObj.getAddress() & " " & artistObj.getCity() & " " &
artistObj.getState() & " " & artistObj.getPostalCode() & "<br>");
WriteOutput("Ph: " & artistObj.getPhone() & "<br>");
WriteOutput("Fax: " & artistObj.getFax() & "<br>");
WriteOutput("EMail: " & artistObj.getEmail() & "<br>");
WriteOutput("<br>");
}
</cfscript>
Recent Comments