no notes exist for this slide

Somewhere Over the Rainbow Conference

We will look at: Transferring your knowledge of SQL to HQL Learning how to let hibernate do a whole load of work for you Look at some common gotchas (and how to solve them) how we can use objects in our code instead of queries Image credit: http://www.flickr.com/photos/24293932@N00/2752221871/

At the end of this you will not be: A master of hibernate A OOP guru Wishing you were a PHP developer (hopefully!) Image credit: http://www.flickr.com/photos/24293932@N00/2752221871/

Who is this guy? Devon CFUG Aliaspooryorik Crisp Group Certified Expert in Advanced ColdFusion Image credit: http://www.flickr.com/photos/shereen84/3187315381/sizes/l/

This is my first time speaking at a conference so be nice Image credit: http://www.flickr.com/photos/prh/490428886/sizes/l/

Some twitter hash tags if you want to follow along Image credit: http://www.flickr.com/photos/tveskov/3387394098/

What is HQL? HQL is an acronym for Hibernate Query Language It’s similar to SQL, except you query the objects and properties instead of the underlying database tables and columns Means you can write much more object orientated code (if you want to) Can return objects, which can have associations and behaviour image credit: http://www.flickr.com/photos/dullhunk/358365339/sizes/l/

What is Hibernate? Hibernate is: an open source Java persistence framework now built-in to ColdFusion 9 (and soon Railo). It primarily focuses on mapping Objects to a Relation Database – a technique known as Object Relational Mapping (ORM). It sits between your persistence layer and the database – it acts as a gateway to the database. Wikipedia says: “Hibernate is an object-relational mapping (ORM) library for the Java language, providing a framework for mapping an object-oriented domain model to a traditional relational database. Hibernate solves object-relational impedance mismatch problems by replacing direct persistence-related database accesses with high-level object handling functions. “ Image credit: http://www.flickr.com/photos/oberazzi/318947873/sizes/l/

So what is Object Relational Mapping (ORM)? Quote from: http://en.wikipedia.org/wiki/Object-relational_impedance_mismatch “Solves the object-relational impedance mismatch is a set of conceptual and technical difficulties that are often encountered when a relational database management system (RDBMS) is being used by a program written in an object-oriented programming language or style; particularly when objects or class definitions are mapped in a straightforward way to database tables or relational schemata. This practice has been recommended and documented by some object-oriented literature as a way to use databases in object-oriented programs” I say: “Object Relational Mapping is a way of mapping objects to a relational database. The idea is that you as a developer can think in terms of objects, rather than thinking about how to persist the objects in a database. “ Image credit: http://www.flickr.com/photos/oberazzi/318947873/sizes/l/

Hibernate generates SQL for you; so you don’t have to do the slow and boring process of writing 4 SQL queries per object (Insert, Select, Update & Delete). This means you’re more productive because: You don’t have to write repetitive code and can focus on the cool stuff The database schema will be created for you (great for dev worth tweaking for production) Less code for you to maintain Less typos / bugs Easier for multi-developers to work on the same app All of which means you have more time to manage your fantasy football team. Image credit: http://www.flickr.com/photos/wherearethejoneses/1070506827/sizes/o/

Mature - 80k lines of code Stable - 25k lines of unit tests Database Agnostic - Microsoft SQL Server, MySQL (best with innodb), PostgreSQL, Oracle, Sybase, Derby, SQLite, DB2... - Doesn’t support MS Access (which is a great selling point!) Performance - Tuned per database (for example for update only includes dirty fields) - Lazy loading (associations can be fetched when needed) - Caching Image credit: http://www.flickr.com/photos/stewart/461099066/sizes/l/

No! SQL is useful for: Bulk update/insert/delete (although hibernate can handle bulk updates). Populating database. Complex reports which don't need objects Caveat about changes caused by HQL bulk operations or SQL operations might not be match Hibernate cache. Image credit: http://www.flickr.com/photos/unloveable/2387709961/sizes/o/

So how do I run HQL statements? Meet your new friend: ORMExecuteQuery( hql ); Introduced in ColdFusion 9.

Time to look at some simple HQL examples. Got next slide! Image credit: http://www.flickr.com/photos/andyjarrett/524195523/

Before we write any HQL we need to look at our persistent CFCs and how they map to the database. Image credit: http://www.flickr.com/photos/shortbread1015/2668073706/

From Examples (01) http://localhost/sotr2010/ex01_from/ My First HQL: The simplest HQL statement you can write is “from myEntity”. This will return an array of “myEntity” objects. Case Gotcha! HQL Entity names are case sensitive. With SQL we don’t have to worry about this so be warned! Displaying the Results: As ORMExecuteQuery() returns an array, we can simply loop through the array and then call the implied getters which ColdFusion creates for you. Miss Query recordsets?: The new EntityToQuery() function can convert an array of objects to a recordset. Obviously you now can’t access the getters and setters. Image credit: http://www.flickr.com/photos/35014792@N00/337187361/

Where Clause Examples (02) http://localhost/sotr2010/ex02_where/ Where Clause: The standard SQL expressions are available in HQL Case Sensitivity: This works because case sensitivity does not apply to property names. Expressions: The standard SQL expressions work. Alternative Expressions: You can also use the != expression for not equal. Functions & Wildcards: The lower and upper functions can be used for comparing performing like matches. In and between work the same as in SQL Image credit: http://www.flickr.com/photos/35014792@N00/337187361/

Select Examples (03) http://localhost/sotr2010/ex02_select/ Using an Alias: You can use select statements in HQL. This example returns an array of Artist objects (same as "from Artist" in previous example). In this example I have used an alias. The alias tends to be the entity name as lowercase (from the Java world), although it is up to you. Gotcha: You need to use an alias if you want to reference it in the select statement otherwise you’ll get an nasty error. Gotcha again: The alias that we used is “artist”, whereas in the select we are called “Artist”. Remember that HQL is case-sensitive Will This Work? Yes it does. You know I said it's case sensitive, well this is only partially true, as keywords (select / from etc ) can be any case. It's applies to entity names and property names. Incidentally the “AS” keyword is optional. I tend to include it as I think it makes the code easier to read. Selecting Specific Properties: So far we have only returned objects in our HQL. What if you just wanted to return specific properties. Well, you can. Unfortunately it returns an array inside an array, which is not particular nice to work with. Yet Another Gotcha: You may think that we could solve the problem of having to work with nested arrays by using EnityToQuery. Sadly, this doesn’t work. Displaying: We need to write code that references each value by it’s position in the array to show our results in a simple list. This is not ideal as it makes it hard to see what will be displayed by looking at the code that we use to display the result. Image credit: http://www.flickr.com/photos/35014792@N00/337187361/

Ordering http://localhost/sotr2010/ex04_orderby/ Not very interesting – it does what you’d expect. Image credit: http://www.flickr.com/photos/35014792@N00/337187361/

Group By http://localhost/sotr2010/ex05_groupby/ Distinct: same as SQL Group by: Same as SQL. Group by is often faster to execute that Distinct. Group by and functions: Same result as SQL, although we are back to having nested arrays. A better way? A different way to get a list of unique states, you can use a subquery. You may find that you may get better performance by doing this as the database isn’t having to compare each row, this does depend on the database vendor. Image credit: http://www.flickr.com/photos/35014792@N00/337187361/

Parameters http://localhost/sotr2010/ex06_parameters/ Positional Parameters: We are used to passing parameters with the cfqueryparam tag, which you should do for security and performance. We can do something similar in ORMExecuteQuery() and HQL. This example uses positional parameters. The values are passed in as an array which must be in the correct order. Named Parameters: A nicer way to pass values is to use named parameters. These don’t need to match up the property names, they just need to be value variable names. The values are passed in as a struct and can be in any order. The parameters names are not case-sensitive. Image credit: http://www.flickr.com/photos/35014792@N00/337187361/

Functions http://localhost/sotr2010/ex07_functions/ Substring: Substring is the HQL equivalent of CFML's Mid function.  Length: length() and trim() functions are available. This example needs trim as the password fieldtype is char. Locate: Locate is the HQL equivalent of CFML's Find. Like Find(), it is case sensitive. Aggregates: max() returns max value min() returns min value avg() returns average value sum() returns sum abs() returns absolute value (no - sign) Image credit: http://www.flickr.com/photos/35014792@N00/337187361/

Expressions http://localhost/sotr2010/ex08_expressions/ Concatenation: Either method works Create Artists: Need to set up some Artists with null data to demonstrate. Case Statement: simple case statement. Note the 3 different version of "is not null". Coalesce: Coalesce returns the first not null value. Simpler version of previous example. Delete: Yes, you can delete data with HQL! Note that it returns the number of deleted records. Image credit: http://www.flickr.com/photos/35014792@N00/337187361/

Although HQL is like SQL you can lots of things in HQL that you can't in SQL. Image credit: http://www.flickr.com/photos/nereski/3861447

Relationships Undefined ORM relationship: In SQL you create the relationships between tables using joins. We can do this in HQL as well. Art Entity: To get the previous example to work we needed to have a property for the artistid foreign key in the Art table so that we could reference it in the HQL. We also ended up with a nested array – more on this later. Gotcha: You can't use "JOIN ON" syntax Set up a many-to-one relationship in the Art Entity to Artist ------------------------------------------- http://localhost/sotr2010/ex12_relationships/join.cfm Get all art objects by an artist in the 'SD' state. Don't need to specify the properties in the join. ------------------------------------------- http://localhost/sotr2010/ex12_relationships/implicit-join.cfm Improving on the last example, you don't even need to specify a join. Cool huh!

Many-to-One Define relationship in Art.cfc: ColdFusion ORM lets you create the relationships between Entities by defining them in the CFC. Here we have a many-to-one relationship between art and artist. Note that we don’t have the artistid property in Art anymore. Join: Now we can simply use a join without needed to specify how the join is constructed. Implicit Join: We can improve on the previous example by not even mentioning the Artist Entity. Cool huh! What we are doing is using the artist property in Art.cfc to then match the id of the Artist object.

Bi-directional Artist.cfc: Artist has a one-to-many relationship with Art Art.cfc: Art has a many-to-one relationship with Artist Size of a Collection: We want to find all artists who have one piece of art Size of a Collection (alternative): The same as the last example with alternative syntax Many to one null: Find all the Art objects that don’t have an Artist (which will be none!)

Inheritance Inheritance and ORM is a whole presentation in itself, so I’ll leave that for another day. For now all you need to know is that Inheritance is a Object Orientated Term meaning an “IS-A” relationship. Part.cfc: Imagine you were building a stock system for a garage. You’d might create a Super Class of Part which the more specialised sub classes Tyre and DiscBrake extend. Tyre.cfc: Extends Part and adds additional properties DiscBrake.cfc: Extends Part and adds additional properties Populate: Let’s add some Parts to our application. Filter By Class: We want to know what Tyres we need to re-order as we have less that 4 in stock. Delete By Class: You can also find multiple tyres using IN.

Tips and Tricks HashMap: So far, when selecting properties, the nested arrays we get have not been very nice to work with. A hashmap can help. Displaying a Map: The hashmap means we can have much more descriptive code. Map Gotcha: Unlike a ColdFusion struct, the map keys are case sensitive. Debugging HQL: One of the things I like about using queries in ColdFusion is being able to see the SQL statement that is executed. Well, there is a setting: this.ormsettings.logsql="true“ You can use which logs the SQL statement that hibernate creates to the console. You can have ColdFusion log directly to the console or a file. There is an excellent post by Rupesh Kumar (one of the ColdFusion engineering team) about how to do it: http://www.rupeshk.org/blog/index.php/2009/07/coldfusion-orm-how-to-log-sql/ Personally, I like to be able to see the SQL as debugging info, so I have a method in a super class, which gets the SQL hibernate generates and logs it (I use LogBox) if I’m in development mode. I find this really useful. Params List Problem: One problem that has been identified with the ORM implementation in ColdFusion is passing lists. This script demonstrates the problem. Params List Solution: This solution was posted by Barney Boisvert, which works nicely. Again you would want to have this all encapsulated in a super class, so you can largely forget about it. Image credit: http://www.flickr.com/photos/nozzer/3990622685/sizes/o/

Cf-orm-dev http://groups.google.com/group/cf-orm-dev Hibernate HQL docs: http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html Rupesh Kumar on Logging the SQL: http://www.rupeshk.org/blog/index.php/2009/07/coldfusion-orm-how-to-log-sql/ Image credit: http://www.flickr.com/photos/tomsen/443379474/

Thank you all for not leaving. I’ll let you head off for lunch now. Image credit: http://www.flickr.com/photos/dreyboblue/2299422762/