Posts Tagged hibernate

ColdFusion ORM : Using DB Views instead of Table

One of the frequent question that comes up for ORM is – Can I use database Views instead of the table? And the answer is “of course”! From ORM perspective, there is no difference between database view and a table. Any query that ORM generates will work on the views in the same way as it does on a table. So while defining the persistence metadata for your CFC, just use the view name instead of the table name and you should be all set.

Of course views are used just for the query and not for the insert/update/delete. Hence method like EntitySave/EntityDelete which will try to do insert/update/delete on View will not change the view and would throw an error at the time of flushing the ORM session.

Tags: , ,

ColdFusion ORM : What is “N+1 Select problem”

In my last two posts, I mentioned that immediate fetching or lazy fetching can cause ‘N+1 select problem’. If you are wondering what exactly is this, read on.

Consider the example of Department and Employees. When you call EntityLoad(“Department”), following sqls will be executed.

SELECT * FROM department;
 
SELECT * FROM employees WHERE deptId = ?

The first query will be executed once (1) and the second query will be executed as many times as the department (N). Thus the above entityLoad call results into ‘N+1′ sql execution and thus can be a cause of performance bottleneck. Because of N+1 sqls, this is known as ‘N+1 select problem’. This will almost always happen when the fetching is “Immediate” (using fetch=”select”) or can happen with lazy loading.

With immediate fetching it is obvious why this would happen. When lazy=’true”, this can happen when the association is accessed immediately on each of the owning object (department in this case).

If you think this could be happening in your application, use either of these two options.

  1. set lazy=”false” and use fetch=”join” so that the departments and its employees get loaded together. (Eager fetch)
  2. Keep lazy=”true” but load the department using hql with join. So instead of using EntityLoad(“Department”), use
    ORMExecuteQuery("from Department dept left join fetch dept.employees") 
    

Tags: , , ,

ColdFusion ORM : Performance tuning – Lazy loading

In the previous post, we talked about different fetching strategies and when to use them. In this post, we will go little deep in lazy loading which is the most popular and commonly used fetching strategy.

As we said in the earlier post – with this strategy, when you load an entity, ColdFusion ORM will load the entity’s data but relations and any mapped collections and are not loaded. They are loaded only when you want to load them i.e by calling the getter method for it and accessing it. Thus the relations and collection mappings are lazily loaded. To give an example, when Department is loaded, all its employees are not loaded and they are loaded only when getEmployees() is called.

There are three types of lazy loading that is provided by ColdFusion ORM for relationship.

  • lazy : This is the default lazy loading that applies to collection mapping, one-to-many and many-to-many relationship. In this case, when you call the accessor for the collection/relation, the collection is fully loaded. Thus when you call EntityLoad() for a particular department, its employees are not loaded at that time. When you call dept.getEmployees(), all the employees object belonging to the department will get loaded. This is achieved by setting lazy=”true” on the relationship property definition in the CFC.Example : In Department.cfc
    <cfproperty name="employees" fieldtype="one-to-many" cfc="employee" fkcolumn="deptId" lazy="true" >
  • Extra lazy : This applies to one-to-many and many-to-many relationship. This is similar to lazy loading but goes one step ahead of it and does not load the associated objects for for calls like size(), contains(Object). This means that calls like ArrayLen(dept.getEmployees()) or ArrayContains(dept.getEmployees(), anEmployee) or ArrayFind(dept.getEmployees(), anEmployee) will not result into loading any employee object. It will just execute the sql for finding size or finding if the employee belongs to the department. The employee objects will be loaded only when a employee is accessed from this collection. This is very useful if the collection is huge. This is achieved by setting lazy=”extra” on the relationship property definition in the CFCExample : In Department.cfc
    <cfproperty name="employees" fieldtype="one-to-many" cfc="employee" fkcolumn="deptId" lazy="extra" >
  • proxy : This applies to one-to-one and many-to-one relationship. When an object is loaded, the associated object is not loaded from the database. ColdFusion will only create a proxy object for the related object and when any method is invoked on the related object, the data for the proxy object is loaded from the database and populated in the proxy object. To give an example, if the Employee-Department relation is lazy, when Employees is loaded, the department is not loaded and when you call employee.getDepartment(), you would only get a proxy object. When you call any method on the proxy object, query will be executed on the DB to load department’s data. This is achieved by setting lazy=”true” on the relationship property definition in the CFCExample : In Employee.cfc
    <cfproperty name="department" fieldtype="many-to-one" cfc="department" fkcolumn="deptId" lazy="true" >

    An important thing to note here is – An entity is loaded only once in the request (in Hibernate session to be more specific) and there will always be only one copy of it in the request. So for Employee-Department relationship, which is lazy, if the department is already loaded, calling employee.getDepartment() will not create a proxy object and will return the loaded department object.

Lazy loading can be disabled by setting lazy=”false” on the relationship property definition in the CFC.

Choosing an appropriate lazy loading option is very important for the performance of your application. Extra lazy means more number of trips to the database (each trip to the DB is expensive) but less data in memory whereas no lazy loading means a huge object graph in the memory. So you need to make a balance depending on the application need.

While lazy loading is very useful and helpful in reducing the amount of data loaded from the database and thus reducing the number of objects in memory, overdoing it can have an inverse effect. Lets say in your application, when you load an object, you always access its associated data, lazy loading will again cause ‘N+1 select problem’. This means that a huge number of sqls will be executed which can be avoided by using eager fetch or using HQL with join (See query example of “Eager Fetch” in this post).

There are some other important things to remember/note while using lazy loading

  1. The lazy collection (including one-to-many and many-to-many) is not immediately loaded when you call the getter for the relationship. The sql is executed only when you access anything on the result of the getter (either get its size, or iterate over it etc). lazy=”extra” is little extra lazy (see “Extra Lazy” above).
  2. has*** methods on the entity for relationship are optimized in such a way that it will not result into loading the associated object.
  3. You can quite easily hit the famous “LazyInitializationException“. Mark Mandel explains this nicely in his post on “Explaining Hibernate Sessions“. Ray Camden also talks about his experience with it here. So you need to be careful when using detached object.
  4. If you are retrieving ORM entities in flex, even if you set lazy=”false”, ColdFusion will not send the whole object graph. If you need the relation data to be serialized to flex, you need to set “remotingfetch=’true’” on the relationship property. More on this later.

Tags: , , ,

ColdFusion ORM : Performance tuning – Fetching Strategy

In any application that needs database interaction, DB operations are the key to the application performance. Most of the application performance problems come because the sqls being executed are not optimized or there are huge numbers of queries being executed or there is too much data getting loaded by the query or the columns are not properly indexed or there is no caching being done and the application always hits the DB. In this series, I will try to cover different strategies that you need to use for a good performing ORM based application.

As we all know, the fundamental strategy to tune an application performance is to optimize the sql queries. As a general practice, object retrieval using many round trips to the database is avoided and you would fetch all the required data for a particular operation using a single SQL query using Joins to retrieve related entities. Also, you would fetch only the data that is required i.e data will not be fetched unnecessarily if it is not needed so as to reduce the load on the DB. However this becomes an issue when you use ORM because you no longer write the SQL queries yourself and queries are generated and executed by the underlying ORM engine.

Thankfully ORM engine like Hibernate provides various hooks to optimize the sql as well no of trips that will be made to the database. The most important of these hooks is “fetching strategy” which defines what data will be fetched, when and how.

There are four fetching strategies for loading an object and its associations. (We will use Department-Employee relationship for all the explanation)

  1. Immediate fetching : In this strategy, the associated object is fetched immediately after the owning entity is fetched, either from the database using a separate SQL query or from the seconadary cache. This is usually not an efficient strategy unless the associated object is cached in the secondary cache or when separate queries are more efficient than a Join query. You can define this strategy by setting lazy=”false” and fetch=”select” for the relationship property definition in the CFC.example :
    <cfproperty name="employees" fieldtype="one-to-many" cfc="employee" fkcolumn="deptId" lazy="false" fetch="select">

    With this strategy, on loading the department object, its employees object will be loaded immediately using a separate SQL query. As a result, this strategy is extremely vulnerable to ‘N+1 Select problem’.

    pros :
    The association is loaded immediately and hence the associated object can be accessed even after the ORM session is closed.
    cons : A large number of sqls get executed causing a higher traffic between application and the database. The association is loaded even if it might not be needed.

    When to use : When the association is almost always read after loading the object and executing separate sql is more efficient than executing a join query.

  2. Lazy fetching : In this strategy, the associated object or collection is fetched lazily i.e only when required. For example, when you load a Department object, all the associated employees will not be loaded at all. It will be loaded only when you access it. This results in a new request to the database but it controls how much of data is loaded and when is it loaded. This helps in reducing the database load because you fetch only the data that is required and is a good default strategy. We will talk about this in much more detail in the next post. For the time being lets just say this is the most commonly used and the default strategy for obvious reasons. You can define this strategy by setting lazy=”true” or lazy=”extra”.
    example :

    <cfproperty name="employees" fieldtype="one-to-many" cfc="employee" fkcolumn="deptId" lazy="true" >

    pros : Only the minimum required data is loaded. This avoids loading of entire object graph in memory and hence the performance is generally good.
    cons : If the association is always accessed after loading, this would result in extra sql execution. If the loaded object is accessed in another ORM session (i.e has become detached), extra care must be taken to avoid errors like ‘LazyInitializationException’ or ‘NonUniqueObjectException’.

    When to use : When the association is not immediately read after loading the object. This is the most commonly used and default strategy.

  3. Eager fetching : In this strategy, the associated object or collection is fetched together with the owning entity using a single SQL Join query. Thus, this strategy reduces the number of trips to the database and is a good optimization when you always access the associated object immediately after loading the owning entity. You can define this strategy by setting fetch=”join” for the relationship property definition in the CFC.example :
    <cfproperty name="employees" fieldtype="one-to-many" cfc="employee" fkcolumn="deptId" fetch="join">

    With this strategy, on loading the department object, both department and employees data will be fetched from the database using a single join query.

    Even if the eager fetching is not defined in the CFC metadata, it can be done at runtime using ORMExecuteQuery. This can be very powerful in scenarios where in most of the cases, you choose the assocition to be lazily loaded but in some cases, you want to immediately load it. In those case, use Join in the HQL and execute that using ORMExecuteQuery.

    Example :

    ORMExecuteQuery("from Department dept left join fetch dept.employees")
    ORMExecuteQuery("from Department dept left join fetch dept.employees where dept.id=1001")

    pros : The association is loaded immediately and hence the associated object can be accessed even after the ORM session is closed. The association is loaded using a single join query which usually is more efficient than executing multiple queries.
    cons : The association is loaded even if it might not be needed. Since the query used is a join query, the resultset returned by the DB will typically contain lot of repititive data. If used for more than one collection of an entity, this will create a cartesian product of the collection’s data and thus causing creation of a huge resultset.

    When to use : When the association is almost always read after loading the object. More suitable for many-to-one and one-to-one association or single collection where the associated objects can be loaded using join query without much overhead.

  4. Batch fetching : This strategy tells Hibernate to optimize the second SQL select in Immediate fetching or lazy fetching to load batch of objects or collections in a single query. This allows you to load a batch of proxied objects or unitialized collections that are referenced in the current request. This is a blind-guess optimization technique but very useful in nested tree loading.
    The concept of batch-fetching is slightly confusing (at least I got confused when I first read about it). So you need to pay careful attention to this.
    This can be specified using “batch-size” attribute for CFC or relationship property. There are two ways you can tune batch fetching: on the CFC and on the collection.

    • Batch fetching at CFC level : This allows batch fetching of the proxied objects and hence is applied to one-to-one and many-to-one relationship. To give an example, cosider Employee-Department example where there are 25 employee instance loaded in the request(ORM session). Each employee has a reference to the department and the relationship is lazy. Therefore employee objects will contain the proxied object for Department.If you now iterate through all the employees and call getDepartment() on each, by default 25 SELECT statements will be executed to retrieve the proxied owners, one for each Department proxy object. This can be batched by specifying the ‘batch-size’ attribute on the Department CFC like

      <cfcomponent table=”Department” batch-size=”10″ …>

      When you call getDepartment() on the first employee object, it will see that department should be batch fetched, and hence it will fetch 10 department objects that are proxied in the current request.
      So for 25 employee objects, this will make Hibernate to execute at max three queries – in batch of 10, 10 and 5.
      You must note that batch-size at component level does not mean that whenever you load a Department object, 10 department objects will get loaded in the session. It just means that if there are proxied instances of Department object in the session, 10 of those proxied objects will get loaded together.

    • Batch fetching at collections : This allows batch fetching of value collections, one-to-many or many-to-many relationships that are unitialized. To give an example, consider Department-Employee one-to-many relationship where there are 25 departments loaded and each department has a lazy collection of employees. If you now iterate through the departments and call getEmployees() on each, by default 25 SELECT statements will be executed, one for each Department to load its employee objects. This can be optimized by enabling batch fetching which is done by specifying “batch-size” on the relationship property like

      In Department.cfc :

      &lt;cfproperty name="employees" fieldtype="one-to-many" cfc="employee" fkcolumn="deptId" lazy="true" batch-size="10"&gt;

      One important thing to understand here is that batch-size here does not mean that 10 employees will be loaded at one time for a department. it actually means that 10 employee collections (i.e employees for 10 department objects) will be loaded together.
      When you call getEmployees() on the first department, employees for 9 other departments will also be fetched along with the one that was asked for.

    The value for batch-size attribute should be chosen based on the expected number of proxied objects or unitialized collections in the session.

Tags: , , ,

ColdFusion ORM : Troubleshooting – ‘Lazy’ does not work

Few days back Manju logged a bug in CF-ORM saying ‘lazy’ does not work for many-to-one relation and that too on non-Windows machine. At first, I simply rejected the bug because a) ORM can not have anything to do with OS and therefore, if it works on Windows, it works on all the plaform and b) I know it works :-) . But he did not agree and I had to take a look at that machine. And apparently he was right – lazy was not working ! The related entity was in-fact getting loaded immediately. (Question for you – how will you know that lazy is working or not?)

Even after seeing this, I did not believe it and asked him to replicate this on another system and he successfully showed that to me on one another system. And he agreed that it works fine on most of the configurations. The problem exists only on a few of the systems.

This got me thinking – Why would a relation get loaded immediately even after it is marked lazy? The only answer would be – if some one is accessing that lazy field and calling some method on it. I checked his code which was loading the entities to see if there could be any case, where the field would get loaded and unfortunately there was none.

And then suddenly it hit me – what if “memory tracking” is swithched on? That would access each of the field of each object recursively to compute the size of an object and that can definitely cause this. I immediately checked the server monitor and the “memory tracking” was right there staring at me in “red”! It was indeed enabled. I asked Manju to check the other system as well (where lazy was not working) and the memory tracking was enabled there as well.

So the lesson – If the ‘memory tracking’ is enabled on the server, the relationship will no longer remain lazy. And btw, you should enable “Memory tracking” on the server only if you need to track the memory for some troubleshooting. Memory tracking is really really expensive in terms of performance.

Another reason why it might not work for you could be – if you are sending the object to flex. Currently, during serialization, the related objects also get sent irrespective of the lazy attribute set on the relationship. We are still working on it and hopefully by the time we release, this will be fixed.

Tags: , , , ,

ColdFusion ORM : How to log SQL

When you use ORM for developing an application, SQLs are generated and executed by the underlying ORM engine (i.e Hibernate for ColdFusion ORM). However, for both troubleshooting and performance optimization, it is crucial to monitor what queries are getting generated. It can help you find out if there is any error in mapping that you have provided as well as it can help you decide various tuning strategies.

ColdFusion can log the SQLs generated by ORM either onto the console or a file. At the same time it leaves enough hook for you to log it anywhere you want.

ColdFusion ORM provides two ways to log the SQLs.

  1. Using application setting to log to console : This is a quick and simple way to log the sql to console. This is enabled by setting “logsql” in ormsettings.

       <cfset this.ormsettings.logsql="true">

    This setting is self sufficient and it will log all the sqls executed by hibernate to the console (or a file where the server output goes). However this is not a very flexible option. The sqls are always written to the console and it will be combined with any other output that goes to console. Also this option will not show the DDL queries used for creating or updating tables. It only logs the SQL for the entity operations.

  2. Using log4J.properties: Hibernate uses log4j for its logging and you can completely control its logging (including SQL) by modifying the log4j.properties. log4j.properties is present under <cf_home>/lib directory. Please note that you don’t need to do any application specific setting for this.

 

I will go in details about using log4j.properties for SQL logging. Here is a snippet from log4j.properties file that is shipped with ColdFusion.

###--------------- Hibernate Log Settings ------ 
### Set Hibernate log 
log4j.logger.org.hibernate=ERROR, HIBERNATECONSOLE 
 
### log just the SQL 
#log4j.logger.org.hibernate.SQL=DEBUG, HIBERNATECONSOLE 
#log4j.additivity.org.hibernate.SQL=false 
### Also log the parameter binding to the prepared statements. 
#log4j.logger.org.hibernate.type=DEBUG 
### log schema export/update ### 
log4j.logger.org.hibernate.tool.hbm2ddl=DEBUG, HIBERNATECONSOLE 
### log cache activity ### 
log4j.logger.org.hibernate.cache=ERROR, HIBERNATECONSOLE 
 
# HibernateConsole is set to be a ColsoleAppender for Hibernate message  using a PatternLayout. 
log4j.appender.HIBERNATECONSOLE=org.apache.log4j.ConsoleAppender 
log4j.appender.HIBERNATECONSOLE.layout=org.apache.log4j.PatternLayout 
log4j.appender.HIBERNATECONSOLE.layout.ConversionPattern=%d{MM/dd HH:mm:ss} [%t] HIBERNATE %-5p - %m%n%n 
#---------------------------------------------

First we will see the relevant settings for SQL logging:

  • log4j.logger.org.hibernate.SQL : Defines whether the SQL executed for entity operations will be logged and where it will be logged. The second value for this i.e ‘HIBERNATECONSOLE’ is a appender that controls where the SQLs will be logged. In the above example HIBERNATECONSOLE is a ‘console’ appender which means it will log the sql to console.
  • log4j.logger.org.hibernate.type : Defines whether parameter values used for parametrized query will be logged.
  • log4j.logger.org.hibernate.tool.hbm2ddl : Defines whether DDL sql statements will be logged.

To enable the SQL logging for console, we just need to uncomment the settings mentioned above. Here is how the hibernate log settings in log4j.properties file would look like

###--------------- Hibernate Log Settings ------ 
### Set Hibernate log 
log4j.logger.org.hibernate=ERROR, HIBERNATECONSOLE 
 
### log just the SQL 
log4j.logger.org.hibernate.SQL=DEBUG, HIBERNATECONSOLE 
log4j.additivity.org.hibernate.SQL=false 
### Also log the parameter binding to the prepared statements. 
#log4j.logger.org.hibernate.type=DEBUG 
### log schema export/update ### 
log4j.logger.org.hibernate.tool.hbm2ddl=DEBUG, HIBERNATECONSOLE 
### log cache activity ### 
log4j.logger.org.hibernate.cache=ERROR, HIBERNATECONSOLE 
 
# HibernateConsole is set to be a ColsoleAppender for Hibernate message  using a PatternLayout. 
log4j.appender.HIBERNATECONSOLE=org.apache.log4j.ConsoleAppender 
log4j.appender.HIBERNATECONSOLE.layout=org.apache.log4j.PatternLayout 
log4j.appender.HIBERNATECONSOLE.layout.ConversionPattern=%d{MM/dd HH:mm:ss} [%t] HIBERNATE %-5p - %m%n%n 
#---------------------------------------------

Here is the complete log4j.properties for logging SQL for console. Ofcourse after changing this you need to restart the server. If you need to log the parameter values used for queries, you need to uncomment ‘#log4j.logger.org.hibernate.type=DEBUG’ as well.

What if you want to log the SQL to a file and not to console? That is pretty easy. You just need to change the ‘Appender’ used here (HIBERNATECONSOLE) to point to a ‘FileAppender’ instead of a ConsoleAppender. Here is how the configuration for HIBERNATECONSOLE should look like after you point it to a File Appender.

log4j.appender.HIBERNATECONSOLE=org.apache.log4j.FileAppender
log4j.appender.HIBERNATECONSOLE.File=../hibernatesql.log
log4j.appender.HIBERNATECONSOLE.Append=true
log4j.appender.HIBERNATECONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.HIBERNATECONSOLE.layout.ConversionPattern=%d{MM/dd HH:mm:ss} [%t] HIBERNATE %-5p - %m%n%n

For standalone ColdFusion installation, the file ‘hibernatesql.log’ will be created in the /logs directory.You can also specify a full path of the file for property ‘log4j.appender.HIBERNATECONSOLE.File’ and the log will be written to that.

That was easy. Isn’t it? What if you want a rolling log file where you dont want the log file size to grow infinitely. That is fairly easy too. All you need to do is to use an appropriate appender. The appender definition for that will look like

log4j.appender.HIBERNATECONSOLE=org.apache.log4j.RollingFileAppender
log4j.appender.HIBERNATECONSOLE.File=../hibernatesql.log
log4j.appender.HIBERNATECONSOLE.Append=true
log4j.appender.HIBERNATECONSOLE.MaxFileSize=500KB
log4j.appender.HIBERNATECONSOLE.MaxBackupIndex=3
log4j.appender.HIBERNATECONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.HIBERNATECONSOLE.layout.ConversionPattern=%d{MM/dd HH:mm:ss} [%t] HIBERNATE %-5p - %m%n%n

Here is the complete log4j.properties for logging SQL to a file that will be rolled automatically once it reaches 500KB.

Now that you have seen how easy it is to change one ‘Appender’ to another, you can pretty much log it anywhich way you want. Here are some of the interesting ‘Appender’s that come with log4j which you can easily use.

DailyRollingFileAppender, ExternallyRolledFileAppender, JMSAppender, SMTPAppender, SocketAppender, SyslogAppender

See log4J for more details on log4j settings.

Tags: , , ,

ColdFusion ORM : The basics

Before starting on the advanced topics, I thought it will be better to build some ground and hence I decided to do a post on the ORM basics. In this post, we will build a simple example to get a taste of ColdFusion ORM (CF-ORM) and during that we will also understand some of the basic concepts.

ORM is object relational mapping and in ColdFusion, objects are created using CFC. CFCs that needs to be persisted are called persistent CFC and that is marked by setting ‘persistent’ attribute to true on the component. We also need to define what persistent fields will be there in a persistent CFC and that is defined using ‘cfproperty’. A field/property is marked persistent by setting persistent attribute to true on the cfproperty. By default, if the CFC is persistent, all its properties are considered as persistent unless you mark a property non-persistent. So typically ‘persistent’ attribute on the property is used only when you need to make that property non-persistent.

Each persistent CFC in ColdFusion application maps to a table in the database and each property in the persistent CFC maps to a column in the table (not exactly true but we will come to that later.. For the time being lets keep it that way). We will use the cfartgallery datasource for this example which has Artists and Art tables.

The first thing you need to do is – enable ORM for the application and define a datasource to be used (What is an ORM without a datasource?). ColdFusion ORM uses Application.cfc to define all the ORM specific settings. (If you haven’t started using Application.cfc for your application, its time to start using it!)

Application.cfc

Component {
    this.name="ArtGallery";
    this.ormenabled="true";
    this.datasource="cfartgallery";
}

Note that the datasource setting defined here makes it the default datasource of your application which can be used by tags like cfquery, cfinsert, cfupdate, cfdbinfo. The same default datasource will be used by ORM as well.

There are a whole bunch of ORM related configuration that you can do in application.cfc which you can refer here.

Now that the application is configured, let us build the object and define the persistence information on it. To start with, we will first define the Artist.cfc

Artists.cfc
/**
 * @persistent
 */
Component {
    property name="artistid" generator="increment";
    property firstname;
    property lastname;   
    property address;
    property city;
    property state;
    property postalcode;
    property email;
    property phone;         
    property fax;
    property thepassword;
}

This is the most simplistic definition of the component where we have defined only the component and its properties names. Since the table for this CFC already exists in the database, we have not added any table specific information in this and we will let ORM infer all the information from the database. The only additional setting that we have added here is the ‘generator’ attribute which is used to auto-generate the primary key.

After the components are defined, the first request to this application (i.e a page in this application) will make CF-ORM do all the setup necessary (basically generation of hibernate configuration, mapping files, building the session factory etc). Once the setup is done, you are all set to work with the entities.

We will first list all the artists and here is what you need to do for that

listAll.cfm

<cfscript>
   artists = EntityLoad("Artists");
   writedump(artists);
</cfscript>

To load a particular Artists with its ID, here is what you do

 list.cfm

<cfscript>
   artist = EntityLoadByPK("Artists", 1);
   writedump(artist);
</cfscript>

There are several flavors of EntityLoad functions details of which can be read here

Let us now see how to perform insert and update on it.

save.cfm

1
2
3
4
5
6
7
8
9
10
11
<cfscript><br>   // Insert a new artist
   artist = new Artists();
   artist.setfirstname("Leonardo");
   artist.setlastname("Da vinci");
   artist.setcity("Paris");
   EntitySave(artist);
 
   writeOutput(artist.getartistid());// Update an artist
   artist = EntityLoadByPK("Artists", 2);
   artist.setcity("NewYork"); // artist is automatically updated.
</cfscript>

As we see in the above example, EntitySave is used to insert/update an object in the table. There are some important things to note here

  • EntitySave is an intelligent function which automatically finds if a new row needs to be inserted for the given object or whether an existing row needs to be updated.
  • We called EntitySave for the insert here but not for update but even then artist ’2′ gets updated. So how did that happen? Actually what happens here is when you load an artist object, it becomes associated with the hibernate session which keeps track of any changes in the object and automatically saves it when the session is flushed. We will talk about more about hibernate session in a later post. For the time being lets just say that Hibernate Session is a short-lived object that represents a conversation between the application and the persistence layer and also acts as the first level of cache.
  • We did not write any setter or getter method for artist’s properties in Artists.cfc but we are calling them here. That works because ColdFusion 9 automatically generates accessor methods for any property written in a CFC. More details on generated methods in a later post.
  • At line no 6, we called entitySave, but if you check the database, the row is not inserted yet. So when does that happen? Hibernate batches up all the operations till the end of the request or to be exact till hibernate session is flushed. ColdFusion ORM starts up a session when the first ORM method is called in the request and is automatically flushed when the request ends. The batching is done for performance reason so that hibernate executes the sql with the final state of the objects. It will be a huge performance bottleneck if ORM keeps executing sql for each changes in the object.

To delete an Artist, you need to call EntityDelete() passing the object to be deleted.

delete.cfm

<cfscript>
    artist = EntityLoadByPK("Artists", 15, true);
    EntityDelete(artist);
</cfscript>

Relationship

So far we have seen how to perform CRUD for a single entity. But in any application, there will be entities which are associated and ORM must load the associated object as well when loading a particular entity. For our example, an Art will have an Artist and when loading the art object, it should also load the associated artist. So lets build the model first after which we will see how to work with the association.

In cfartgallery, the table Artists has a one-to-many relationship with Art table, which are joined using the foreign key column ARTISTSID. This means that each artist has created multiple arts and each art is created by one artist. To represent this in the object model, each ARTIST object would contain an array of ART objects. Each ART object will also contain a reference to its ARTIST object thereby forming a bidirectional relation.

To achieve this, we will need to add an extra property ‘arts’ to ‘Artists’ that contains an array of ART objects for that Artist. The modified Artists.cfc would look like

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/**
 * @persistent
 */
Component {
    property name="artistid" generator="increment";
    property firstname;
    property lastname;   
    property address;
    property city;
    property state;
    property postalcode;
    property email;
    property phone;         
    property fax;
    property thepassword;
    property name="arts" fieldtype="one-to-many" fkcolumn="artistid" cfc="Art" cascade="all" inverse="true";
}

Here is the Art.cfc

1
2
3
4
5
6
7
8
9
10
11
12
/**
 * @persistent
 */
Component {
    property name="artid" generator="increment";
    property artname;
    property price;
    property largeimage;
    property mediaid;
    property issold;
    property name="artist" fieldtype="many-to-one" fkcolumn="artistid" cfc="Artists" ;
}

Notice the artist property above which is of many-to-one type. Also notice that both the property use the same value for fkcolumn attribute i.e ‘artistid’ of Art table that references artistID pk of Artist table.

Since we have added a new persistent CFC (Art.cfc) after the application was loaded, we need to re-initialize the ORM for this application so that mappings for Art.cfc also gets generated. This can be done by calling ORMReload() method. There are some nice ways to do this but for the time being lets keep it simple by putting this in a separate page which we will call to reload ORM.

initializeORM.cfm

<cfset ormReload()>

If you load and dump Artist (using listAll.cfm), you should also see the associated art objects for artists.

Now let us create a new Art and associate it with an existing Artist.

artCreate.cfm

1
2
3
4
5
6
7
8
9
10
<cfscript>
    artist = EntityLoad("artists", 1 ,"true");
    art = new Art();
    art.setartname("landscape");
    art.setPrice(1500);
    art.setissold(false);
    art.setartist(artist);
    artist.addArts(art);
    EntitySave(art);
</cfscript>

If you notice line 7-8 above, we associate artist to art by calling art.setArtist(artist) as well as art to artist by calling artist.addArts(art). Hibernate needs us to do this in order to set up the bidirectional relation properly. Since it is a bidirectional relation, you must also decide which side will set the relation in the database. i.e which side of the relation will set the fkcolumn in the table. This is controlled by the “inverse” attribute of proeprty, which if set to true, tells hibernate that this is a inverse of the other relation and this side of relation should be ignored for persistance. If you don’t set inverse to true, Hibernate will unnecessarily fire two sqls for the same association.

So there you have it. We have seen how you can use ORM to perform the basic CRUD operations on entities. For more details, you can refer to the ORM doc and Hibernate docs.

Tags: , , ,

ColdFusion ORM – An Evolution in building datacentric application

As we all know, ColdFusion was born as DBML – a script that was very similar to HTML but had certain tags that could perform database operation and it revolutionized the way web applications were built. Tags like cfquery, cfinsert, cfupdate made it very easy to perform database operations while building web application. Even after 14 years of CFML, (which we incidently completed day before yesterday), cfquery is *the* most popular and the most commonly used tag. However there are few downsides to it

  • Database vendor dependency – The queries that are written are DB specific. If you ever need to switch the database or if you need to make the application database agnostic, it becomes too much of pain. You will end up changing the entire application code or lot of conditional code to handle queries for different databases.
  • Very tight coupling with the database – The queries have very tight coupling with the database. This means that if there is any change in the table name or column name, you will end up with changes all over your application code.
  • Repititive and hence low on productivity – For any CRUD (Create, read, update and delete) operation, you need to write queries which are more or less same for all the tables. You must have felt that you are repeating the same code everywhere in the application. There is too much of repititiveness which reduces productivity.
  • Error prone – How many times did you write insert/update queries and then you had to struggle to debug because the order of column and its values were different? And how many times did you get frustrated because there was a datatype mismatch between the value you had specified and the one expected? How many times did you write a query that was prone to sql injection and you were reminded to use query param? I am sure all of this has happened to all of us and its completely natural given the way queries are written. They are error prone.

ColdFusion 9 introduces a new way to build datacentric application using ORM, that handles all the downsides we saw above. It is not a replacement to cfquery/cfinsert/cfupdate tags but its a different approach altogether. It allows you to build your application using objects where you focus on the business logic and all the grunt work of persistence is taken care automatically (In simple words you dont write SQL queries generally).

ORM (Object relational mapping) is a well known strategy/technique to map relational data to the object model. In an object model, business objects are not aware of the database structure. Objects have properties and references to other objects whereas Databases consist of tables with columns that are related to other tables using foreign key. ORM provides a bridge between the relational database and the object model allowing you to access and update data entirely using the object model of an application.

ORM is not new to ColdFusion either – Transfer and Reactor being the most popular ones. ColdFusion ORM (or CF-ORM) is much more extensive and sophisticated ORM solution than these ORMs. ColdFusion ORM is built on top of Hibernate – the best java ORM engine out there, which is quite powerful and extensive. ColdFusion ORM makes it very easy to use persistence with objects and at the same time allows you to leverage the full power of Hibernate.

ColdFusion ORM provides features such as

  • Defining the persistence mapping and managing the object’s persistence using very simple methods.
  • Database vendor independence
  • Loose coupling between database and application – The application becomes very adaptive to changes as the configuration is central and changes will be required only there.
  • Auto-generation of schema – ORM lets you auto-generate the schema for your application. So you never need to bother about keeping your object model and the database schema in sync. It can automatically happen.
  • Productivity and manageability – Since ORM takes care of all persistence grunt work, you focus on your application logic and thus your application becomes much more cleaner and manageable. It also makes you lot more productive as application can be built much more faster.
  • Concurrency – ORM inherently takes care of concurrency control. So you dont need to worry much about how things would work when multiple database operations happen in parallel web requests.
  • Performance – ORM provides lot of performance optimizations that can make your application run faster. The optimizations include two levels of caching (including pluggable distributed cache), lazy loading, various settings to tune the sql queries generated by ORM.
  • Secure – Since queries will be executed by ORM, issues like SQL injection no longer exist and thus your application becomes secure.
  • Inbuilt pagination
  • SQL like query (HQL) for a finer control of the data to be loaded.

In this series of posts, I will be talking lot more about ORM which I hope you would like. In case you have not downloaded ColdFusion 9 beta, go download it and start playing with it.

Tags: , ,