BATTLEFIELD ORM: TIPS, TACTICS AND STRATEGIES FOR CONQUERING YOUR DATABASE

Feeling lost in the trenches of complex SQL queries and manual database interaction? Join us for a beginner-friendly mission to conquer your data with ColdFusion ORM powered by Hibernate! Whether you're a fresh recruit in the coding field or a seasoned veteran navigating legacy ColdFusion applications, this session equips you with the tools and strategies to level up your development game. We will cover ORM session management, ORM settings, caching strategies, virtual service layers, dynamic finders, dynamic counters, and an enhanced Hibernate Criteria builder for ColdFusion to create easy and programmatic HQL queries. We will even see how to build automatic CRUD APIs using only your ORM definitions. Ready to win?

1. By Luis Majano
ORM
Battlefield

2. @lmajano
@ortussolutions
•Salvadorean Born!
•Imported to the USA
•On-loan to Spain!
•Computer Engineering
•CEO of Ortus Solutions
MAJANO
LUIS

3. Discount Code:CFSUMMIT_ITB

4. https://github.com/coldbox-samples/Pink-Unicorns-Do-Exist
Examples

5. ➡How to use the ColdFusion ORM
➡When to use the ColdFusion ORM
➡Tips & Trips to master Hibernate ORM
➡Pitfalls to avoid
➡Architectural overviews of ORM
➡Extend ORM
Agenda

6. ➡Object Relational Mapper
➡Maps:
-Objects to tables
-Properties to columns
-Relationships to foreign keys and tables
➡An easier way to persist data vs. boring CRUD SQL
➡Focus on objects instead of data
➡ColdFusion ORM (Abstraction to Hibernate/JPA)
➡Almost Everything in Hibernate works in CF
ORM
What is
?

7. ➡Write less boilerplate boring SQL
➡Increase in productivity
➡Rich Object Models
➡Increased Flexibility
➡Database vendor abstraction
➡OO instead of query-based
➡but…..
BENEFITS

8. Is it a Silver Bullet ?
➡Just another tool
➡Times you need the power of the database: reports, legacy, sp, etc.
➡Mix and Match
➡There is a learning curve, but it is worth the investment
➡What if you wanted an array of structs, list queries, or data arrays?
Use it Wisely!

9. coldfusionormbook.com
Enhance yourself!
coldbox-orm.ortusbooks.com
orm-extension.ortusbooks.com

10. Database(s)
ORM
CFML Application
Persisted Entities
CFML Engine
Config Mappings
ORM+
Architecture Qb/CF
SQL

11. ➡Enabled + Configured via Application.cfc
➡this.ormEnabled = true;
➡this.datasource = “MyDatasource”;
➡this.ormSettings = {}
➡https://cfdocs.org/ormsettings
ORM
Activating

12. ORM
Settings
BAD
DEFAULTS

13. BAD
DEFAULTS
Control Data Flushing + Transaction Borders:

ormsettings.flushAtRequestEnd = false
ormsettings.autoManageSession = false
Control Database Dialects (If Possible)
ormsettings.dialect = “MySQLwithInnoDB”

14. BAD
DEFAULTS
Control Entity Discovery or pay the price!

ormSettings.cfclocation = [ “/models”, “/ext/entities” ]
Great for debugging, NOT for production!
ormSettings.logSql = false

15. Need
HELP
Need even more debugging?
- Build out the Hibernate MXML
- Show syntax exceptions

ormSettings.saveMapping = true
ormSettings.skipCFCWithError = false

16. DataBoss - github.com/coldbox-modules/databoss
$> install databoss ORM
Entities &
Metadata
Dynamic
Administrator
Inspect, Analyze, Scaffold
Need
HELP

17. this.ormSettings = {
// Restrict Entity Locations
cfclocation = [ "/models" ],
// Chose the DB Dialect
dialect = "MySQLwithInnoDB",
// Don’t inspect the database
dbcreate = "none",
// Active caching if needed
secondarycacheenabled = false,
cacheProvider = "ehCache",
// Logging only in development
logSQL = env == “dev” ? false : false,
// Session Management: NEVER TRUE
flushAtRequestEnd = false,
autoManageSession = false,
// Event Handling: Yes Please!
eventHandling = true,
eventHandler = "cborm.models.EventHandler",
// Turn on/off skipping of exceptions on syntax: Maybe!
skipCFCWithError = false
};
ORM
Settings
Better

18. Logging is your best friend, 4EVER!

19. 1. Application.cfc
2. ExtendedLog4j Logging
this.ormSettings = {
// Logging
logSQL = true,
};
WEB-INF/cfusion/lib/log4j.properties
LOGGING

20. ###--------------- Hibernate Log Settings ------
### Set Hibernate log
log4j.logger.org.hibernate=INFO, HIBERNATECONSOLE
### log just the SQL
log4j.logger.org.hibernate.SQL=DEBUG, HIBERNATECONSOLE
log4j.additivity.org.hibernate.SQL=true
### 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=DEBUG, HIBERNATECONSOLE
#---------------------------------------------
WEB-INF/cfusion/lib/log4j.properties
Log4J
Logging

21. No Object Modeling
#1 Failure

22. ➡ORM relationship modeling is key
➡OO is required
➡UML is your best friend
➡STOP THINKING ABOUT DATA
➡YOU ARE NOT MODELING A DATABASE
OO Modeling is Key

23. OO Modeling is Key

24. Query Object
Data Data + Behavior
OO Modeling is Key

25. #2 RELATIONSHIPS

26. one-to-one
Student Metadata
one-to-many
Student Address
many-to-one
User Role
many-to-many
User Permissions
fieldtype=“”
RELATIONSHIPS

27. ➡Unidirectional or Bidirectional
➡Collections can be as arrays or structs
➡Filtered Collections (Missed by devs)
➡Performance Failures
➡Control when they are loaded (lazyness)
➡Control how many related entities are loaded
➡Executing code on loading
➡The hardest part of ORM
RELATIONSHIPS

28. ➡one-to-many, many-to-many
➡add<relationship_property_name>(<associated_object>)
➡boolean remove<relationship_property_name>(<associated_object>)
➡boolean has<relationship_property_name>(<associated_object>)
➡many-to-one, one-to-one
➡boolean has<relationship_property_name>()
•addArts( Art art )
•removeArts( Art art )
•hasArts()
•hasArts( Art art )
property name=“arts"
fieldtype="one-to-many"
cfc="Art"
fkcolumn=“ArtistID";
GENERATED
METHODS

29. YOU HAVE TO BE
LAZY

30. ➡Immediate Fetching (Default)
➡select with left outer join
poor, poor, poor, poor performance
➡Use lazy For ALL Relationships or pay the price YOU HAVE TO BE
LAZY

31. ➡Three types of laziness values:
lazy=“true”
all relationships
Loads the relationship data when the getter is called (Batches, if used)
lazy=“extra”
one-to-many, many-to-many
Loads proxy light objects with primary keys
(Event better - Batches, if used)
lazy=“proxy”
one-to-one, many-to-one
Loads proxy with primary key (Same as above) YOU HAVE TO BE
LAZY

32. ➡Eager Fetching
➡Mostly used in one-to-one and many-to-one, but applies to all
➡Default uses 2 SQL queries, reduce to 1 Query
property name=“role” fieldtype=“many-to-one” fetch=“join”;
➡Batch Fetching
➡Limits the way relationships are loaded, else Hibernate tries to load all records
➡Used on many-to-many and one-to-many collection properties:
property name=“comments” fieldtype=“one-to-many” batchsize=“10” lazy=“extra”;
➡Used at entity level as well:
component name=“Comment” batchsize=10{} YOU HAVE TO BE
LAZY

33. Oracle Tip
➡JDBC Fetch Sizes are defaulted to 10
➡Slow for many records or batch operations
➡Increase the JDBC Fetch size
➡Custom Hibernate setting
hibernate.jdbc.fetch_size=100 YOU HAVE TO BE
LAZY

34. ➡They can be more of a headache
➡Cascading Deletes are painful
➡Choose the controlling relationship
➡inverse=true
➡Else double queries, inefficient queries
➡Does it make sense?
➡Supporting methods for bi-directional linkage
➡Supporting methods for un-linkages
AVOID BI-DIRECTIONAL,
IF POSSIBLE!

39. BETTER Relationships

40. ➡Not the same as session scope
➡A transitionary space + Caching Layer
➡entityLoad()
➡entityNew() ?
➡You need to control when to send to DB
➡transaction{}, ormflush()
➡You can remove entities from it and clear it
➡ORMClearSession()
➡ORMEvictEntity(), ORMEvictCollection()
➡ORMCloseSession()
UNDERSTAND THE HIBERNATE SESSION

41. Hibernate Session
(Conversation - Request)
DB
Eu
Ex EzEy
Cache
ehCache/
Couchbase
Batched SQL
EntityNew()
EntityLoad()
Data (!CFCs)
Hibernate Session
Factory (application)
CRUD
When?
ORMClearSession()
UNDERSTAND THE HIBERNATE SESSION

42. DB Sync
Insertions in order
updates
Collection deletions
collection deletion, updates, inserts
collection insertions
deletions in order
UNDERSTAND THE HIBERNATE SESSION

43. ➡Transactions demarcate SQL boundaries
➡Important Imperative for ORM + SQL
➡No communication to DB should occur without one
➡Reactive programming, expect the worst
➡cftransaction or Hibernate raw transactions
TRANSACTION DEMARCATION

44. ➡Transaction Theory:
➡Any existing ORM session is flushed and reused
➡Data can be committed or rollback
➡ORMFlush() is ignored in transactions
➡If commit, then flushed to database
➡If rollback, session is cleared
TRANSACTION DEMARCATION

45. TRANSACTION DEMARCATION

46. TRANSACTION DEMARCATION

47. ➡Don’t do it!
➡Breaks the link to the Hibernate Session
➡Relationships will fail if not lazy loaded
➡entityMerge()
➡Store ID’s or data instead, then inflate the entities
AVOID SCOPING ENTITIES

48. ➡#1 Performance Problem
➡Identify relationships
➡Identify HQL, SQL
➡Create indexes!
DATABASE
INDEXES

49. ➡Don’t go cache crazy, develop a strategy
➡Misconception: Does not store CFC
➡Stores individual property values
➡Use distributed caches: ehcache, couchbase, redis
➡You can cache:
➡Entity property data : Only caches properties data values
➡Entity association data : Only caches primary keys
➡Query data : HQL, ORMExecuteQuery()
➡Evictions:
➡ORMEvictEntity(), ORMEvictCollection()
CACHE
BOOSTING

50. CACHE
BOOSTING

51. CACHE
BOOSTING

52. ➡The fastest gun in the galaxy!
➡Return array of structs
➡Why a full ORM Object graph?
➡Boost your APIs, stop converting queries/
array of objects to JSON HQL
MAPS

53. HQL
MAPS

55. When is HappyBox?
ORM was fast
Extensible way to finish
the remaining 20%
CF ORM was easier to use?
80% of API Querying
OO way to query
Auto build relationships
ORM
THOUGHTS?

56. Base ORM Service
Virtual ORM Service
Active Entity
Entity Populators
Validation
Event Handlers
DI/AOP
OVERVIEW

57. ➡Service layer for any entity
➡OO Querying, caching, transactions
➡Dynamic finders, getters, counters
➡Object metadata & session management
➡Exposes more features from Hibernate
➡90% Foundation
BASE ORM
SERVICE

58. ➡Extends Base ORM Services
➡Roots itself to a single entity = Less Typing
➡You can build the 10%
VIRTUAL
SERVICES

59. ➡Active Record Pattern
➡Sweet validation integration
➡DI/AOP Available
ACTIVE
ENTITY

60. ➡Populate Entities: xml, json, queries, structs
➡Compose relationships from simple values
➡Null support
➡Exclude/include fields
➡Server-side validation
➡Dependency Injection Listeners
➡Custom Event-Driven Programming
Entity Populators
Validation
Event Handlers
ORM
UTILITIES

61. box install cartracker-demo.
ORM
SERVICES
IN ACTION

62. ➡count(), countWhere()
➡delete(), deleteAll(), deleteByID(), deleteByQuery(), delete Where()
➡evict(), evictEntity(), evictQueries()
➡executeQuery(), list()
➡exists()
➡findAll(), findAllWhere(), findByExample(), findIt(), findWhere()
➡get(), getAll(),
➡getKey(), getPropertyNames(), getSessionStatistics(), getTableName()
➡clear(), merge(), new(), refresh()
➡populate(), populateFromJSON(), populateFromXML(), populateFromQuery()
➡save(), saveAll()
BASE ORM
SERVICE

63. BASE ORM
SERVICE
Dynamic Finders/Counters
➡Expressive Programming
➡Three types of dynamic Finders/Counters
➡findBy : find ONE entity
➡findAllBy : find ALL entities
➡countBy: Give you a count

64. BASE ORM
SERVICE
Dynamic Finders/Counters
➡Conditionals
➡LessThanEquals, LessThan
➡GreaterThanEquals, GreaterThan
➡Like
➡Equal, NotEqual
➡isNull, isNotNull
➡Between, NotBetween
➡inList, notInList
➡Operators
➡And
➡Or
➡Query Options
➡ignoreCase, timeout, max, offset
➡cacheable, cachename

65. AWESOME OO QUERIES
CRITERIA
BUILDER

66. CRITERIA
BUILDER
➡Limitations of CF ORM:
➡
entityLoad() has limited features
➡Some operations we always need an entity =
slow
➡What if I want arrays, or arrays of structs
➡Complex relationships are hard to query
➡SQL/HQL string build is so 90’s == NOT FUN!

67. CRITERIA
BUILDER
➡Programmatic DSL Builder
➡Rich set of criterias
➡Projections and Result transformations
➡Subqueries
➡Caching
➡SQL Inspections & Debugging
➡Array of structures is twice as fast as
queries

68. CRITERIA
BUILDER

69. CRITERIA
BUILDER
➡Request new criteria
➡newCriteria()
➡Add simple restriction(s)
➡Find all cars sold between April and July
➡Use between()
➡Get results
➡Use list( max, offset, timeout, sortOrder, ignoreCase, asQuery )
➡Get counts
➡Use count()

70. CRITERIA
BUILDER
➡between()
➡eq()
➡gt()
➡ge()
➡gtProperty()
➡isEmpty()
➡isNull()
➡ne()
➡ilike()
➡and()
➡or()
➡not()
➡conjunction()
➡disjunction()
➡isTrue()
➡isFalse()
➡sqlRestriction()
➡...much more!
RESTRICTIONS

71. CRITERIA
BUILDER
RETRIEVALS
➡Retrieval
➡firstResult()
➡get()
➡list()
➡count()
➡Options
➡fetchSize()
➡readOnly()
➡maxResults()
➡cache(), cacheRegion()
➡timeout()
➡order()

72. CRITERIA
BUILDER
ALIASES-> JOINS
➡Allows you to do queries within relationships
➡Creates SQL Joins
➡Aliases can be nested, so if your entity knows
about it, you can query it!

73. CRITERIA
BUILDER
PROJECTIONS
➡Projects change nature of results
➡Arrays of data, or arrays of structs (Mighty Fast)
➡Once its added its there forever
➡avg
➡count
➡countDistinct
➡distinct
➡groupProperty
➡max
➡min
➡property
➡sum
➡rowCount
➡id
➡sqlProjection
➡sqlGroupProjection
➡detachedSQLProjection

74. CRITERIA
BUILDER
DEBUGGING + LOGGING
➡Criteria Builder SQL Inspector
➡startSQLLog( returnExecutableSQL, formatSQL )
➡stopSQLLog()
➡getSQLLog()
➡getSQL( returnExecutableSQL, formatSQL )

75. Thank You
By Luis Majano