{"id":21347,"date":"2014-02-04T13:00:38","date_gmt":"2014-02-04T11:00:38","guid":{"rendered":"http:\/\/www.javacodegeeks.com\/?p=21347"},"modified":"2014-02-04T09:15:03","modified_gmt":"2014-02-04T07:15:03","slug":"hibernate-facts-how-to-assert-the-sql-statement-count","status":"publish","type":"post","link":"https:\/\/www.javacodegeeks.com\/2014\/02\/hibernate-facts-how-to-assert-the-sql-statement-count.html","title":{"rendered":"Hibernate Facts: How to “assert” the SQL statement count"},"content":{"rendered":"

Introduction<\/h2>\n

Hibernate simplifies CRUD operations, especially when dealing with entity graphs. But any abstraction has its price and Hibernate is no different. I\u2019ve already talked about the importance of fetching strategy<\/a> and knowing your Criteria SQL queries<\/a>, but there is more you can do to rule over JPA. This post is about controlling the SQL statement count that Hibernate calls on your behalf.<\/p>\n

Before ORM tools got so popular, all database interactions were done through explicit SQL statements, and optimizations were mostly targeted towards slow queries.<\/p>\n

Hibernate may give the false impression that you don\u2019t need to worry about SQL statements. This is a wrong and dangerous assumption. Hibernate is supposed to ease domain model persistence, not to free you from any SQL interaction.<\/p>\n

With Hibernate, you manage entity state transitions which are then translated to SQL statements. The number of generated SQL statements is affected by the current fetching strategy, Criteria queries or Collection mappings and you might not always get what you expected. Ignoring SQL statements is risky and it may eventually put a heavy toll on the overall application performance.<\/p>\n

I\u2019m a strong advocate of peer reviewing, but that\u2019s not \u201csine qua non\u201d for detecting bad Hibernate usage. Subtle changes may affect the SQL statement count and pass unnoticed through the reviewing process. Not in the least, when it comes to \u201cguessing\u201d the JPA SQL statements, I feel like I can use any extra help. I\u2019m for as much automation as possible, and that\u2019s why I came up with a mechanism for enforcing the SQL statement count expectations.<\/p>\n

First, we need a way to intercept all executed SQL statements. I researched on this topic and I was lucky to find this great datasource-proxy<\/a> library.<\/p>\n

Adding an automated validator<\/h2>\n

This safeguard is meant to run only during the testing phase, so I\u2019ll add it to the Integration Testing spring context exclusively. I\u2019ve already talked about Spring bean aliasing<\/a> and this is the right time to make use of it.<\/p>\n

<bean id=\"testDataSource\" class=\"bitronix.tm.resource.jdbc.PoolingDataSource\" init-method=\"init\"\r\n\t  destroy-method=\"close\">\r\n\t<property name=\"className\" value=\"bitronix.tm.resource.jdbc.lrc.LrcXADataSource\"\/>\r\n\t<property name=\"uniqueName\" value=\"testDataSource\"\/>\r\n\t<property name=\"minPoolSize\" value=\"0\"\/>\r\n\t<property name=\"maxPoolSize\" value=\"5\"\/>\r\n\t<property name=\"allowLocalTransactions\" value=\"false\" \/>\r\n\t<property name=\"driverProperties\">\r\n\t\t<props>\r\n\t\t\t<prop key=\"user\">${jdbc.username}<\/prop>\r\n\t\t\t<prop key=\"password\">${jdbc.password}<\/prop>\r\n\t\t\t<prop key=\"url\">${jdbc.url}<\/prop>\r\n\t\t\t<prop key=\"driverClassName\">${jdbc.driverClassName}<\/prop>\r\n\t\t<\/props>\r\n\t<\/property>\r\n<\/bean>\r\n\r\n<bean id=\"proxyDataSource\" class=\"net.ttddyy.dsproxy.support.ProxyDataSource\">\r\n\t<property name=\"dataSource\" ref=\"testDataSource\"\/>\r\n\t<property name=\"listener\">\r\n\t\t<bean class=\"net.ttddyy.dsproxy.listener.ChainListener\">\r\n\t\t\t<property name=\"listeners\">\r\n\t\t\t\t<list>\r\n\t\t\t\t\t<bean class=\"net.ttddyy.dsproxy.listener.CommonsQueryLoggingListener\">\r\n\t\t\t\t\t\t<property name=\"logLevel\" value=\"INFO\"\/>\r\n\t\t\t\t\t<\/bean>\r\n\t\t\t\t\t<bean class=\"net.ttddyy.dsproxy.listener.DataSourceQueryCountListener\"\/>\r\n\t\t\t\t<\/list>\r\n\t\t\t<\/property>\r\n\t\t<\/bean>\r\n\t<\/property>\r\n<\/bean>\r\n\r\n<alias name=\"proxyDataSource\" alias=\"dataSource\"\/><\/pre>\n
The new proxy data source decorates the already existing data source, intercepting all executed SQL statements. This library can log all SQL statements along with the actual parameter values, unlike the default Hibernate logging which only prints a placeholder instead.
 
<\/div> <\/div><\/p>\n
This is how the validator looks like:<\/p>\n
public class SQLStatementCountValidator {\r\n\r\n    private SQLStatementCountValidator() {\r\n    }\r\n\r\n    \/**\r\n     * Reset the statement recorder\r\n     *\/\r\n    public static void reset() {\r\n        QueryCountHolder.clear();\r\n    }\r\n\r\n    \/**\r\n     * Assert select statement count\r\n     * @param expectedSelectCount expected select statement count\r\n     *\/\r\n    public static void assertSelectCount(int expectedSelectCount) {\r\n        QueryCount queryCount = QueryCountHolder.getGrandTotal();\r\n        int recordedSelectCount = queryCount.getSelect();\r\n        if(expectedSelectCount != recordedSelectCount) {\r\n            throw new SQLSelectCountMismatchException(expectedSelectCount, recordedSelectCount);\r\n        }\r\n    }\r\n\r\n    \/**\r\n     * Assert insert statement count\r\n     * @param expectedInsertCount expected insert statement count\r\n     *\/\r\n    public static void assertInsertCount(int expectedInsertCount) {\r\n        QueryCount queryCount = QueryCountHolder.getGrandTotal();\r\n        int recordedInsertCount = queryCount.getInsert();\r\n        if(expectedInsertCount != recordedInsertCount) {\r\n            throw new SQLInsertCountMismatchException(expectedInsertCount, recordedInsertCount);\r\n        }\r\n    }\r\n\r\n    \/**\r\n     * Assert update statement count\r\n     * @param expectedUpdateCount expected update statement count\r\n     *\/\r\n    public static void assertUpdateCount(int expectedUpdateCount) {\r\n        QueryCount queryCount = QueryCountHolder.getGrandTotal();\r\n        int recordedUpdateCount = queryCount.getUpdate();\r\n        if(expectedUpdateCount != recordedUpdateCount) {\r\n            throw new SQLUpdateCountMismatchException(expectedUpdateCount, recordedUpdateCount);\r\n        }\r\n    }\r\n\r\n    \/**\r\n     * Assert delete statement count\r\n     * @param expectedDeleteCount expected delete statement count\r\n     *\/\r\n    public static void assertDeleteCount(int expectedDeleteCount) {\r\n        QueryCount queryCount = QueryCountHolder.getGrandTotal();\r\n        int recordedDeleteCount = queryCount.getDelete();\r\n        if(expectedDeleteCount != recordedDeleteCount) {\r\n            throw new SQLDeleteCountMismatchException(expectedDeleteCount, recordedDeleteCount);\r\n        }\r\n    }\r\n}<\/pre>\n
This utility is part of my db-util<\/a> project along with the JPA<\/a> and MongoDB<\/a> optimistic concurrency control retry mechanism.<\/p>\n
Since it\u2019s already available in Maven Central Repository, you can easily use it by just adding this dependency to your pom.xml:<\/p>\n
<dependency>\r\n\t<groupId>com.vladmihalcea<\/groupId>\r\n\t<artifactId>db-util<\/artifactId>\r\n\t<version>0.0.1<\/version>\r\n<\/dependency><\/pre>\n
Let\u2019s write a test for detecting the infamous N+1 select query problem<\/a>.<\/p>\n
For this we will write two service methods, one of them being affected by the aforementioned issue:<\/p>\n
@Override\r\n@Transactional\r\npublic List<WarehouseProductInfo> findAllWithNPlusOne() {\r\n\tList<WarehouseProductInfo> warehouseProductInfos = entityManager.createQuery(\r\n\t\t\t\"from WarehouseProductInfo\", WarehouseProductInfo.class).getResultList();\r\n\tnavigateWarehouseProductInfos(warehouseProductInfos);\r\n\treturn warehouseProductInfos;\r\n}\r\n\r\n@Override\r\n@Transactional\r\npublic List<WarehouseProductInfo> findAllWithFetch() {\r\n\tList<WarehouseProductInfo> warehouseProductInfos = entityManager.createQuery(\r\n\t\t\t\"from WarehouseProductInfo wpi \" +\r\n\t\t\t\"join fetch wpi.product p \" +\r\n\t\t\t\"join fetch p.company\", WarehouseProductInfo.class).getResultList();\r\n\tnavigateWarehouseProductInfos(warehouseProductInfos);\r\n\treturn warehouseProductInfos;\r\n}\r\n\r\nprivate void navigateWarehouseProductInfos(List<WarehouseProductInfo> warehouseProductInfos) {\r\n\tfor(WarehouseProductInfo warehouseProductInfo : warehouseProductInfos) {\r\n\t\twarehouseProductInfo.getProduct();\r\n\t}\r\n}<\/pre>\n
The unit test is rather simple to write since it follows the same coding style of any other JUnit assert mechanism.<\/p>\n
try {\r\n\tSQLStatementCountValidator.reset();\r\n\twarehouseProductInfoService.findAllWithNPlusOne();\r\n\tassertSelectCount(1);\r\n} catch (SQLSelectCountMismatchException e) {\r\n\tassertEquals(3, e.getRecorded());\r\n}\r\n\r\nSQLStatementCountValidator.reset();\r\nwarehouseProductInfoService.findAllWithFetch();\r\nassertSelectCount(1);<\/pre>\n
Our validator works for all SQL statement types, so let\u2019s check how many SQL INSERTs are being executed by the following service method:<\/p>\n
@Override\r\n@Transactional\r\npublic WarehouseProductInfo newWarehouseProductInfo() {\r\n\r\n\tLOGGER.info(\"newWarehouseProductInfo\");\r\n\r\n\tCompany company = entityManager.createQuery(\"from Company\", Company.class).getResultList().get(0);\r\n\r\n\tProduct product3 = new Product(\"phoneCode\");\r\n\tproduct3.setName(\"Phone\");\r\n\tproduct3.setCompany(company);\r\n\r\n\tWarehouseProductInfo warehouseProductInfo3 = new WarehouseProductInfo();\r\n\twarehouseProductInfo3.setQuantity(19);\r\n\tproduct3.addWarehouse(warehouseProductInfo3);\r\n\r\n\tentityManager.persist(product3);\r\n\treturn warehouseProductInfo3;\r\n}<\/pre>\n
And the validator looks like:<\/p>\n
SQLStatementCountValidator.reset();\r\nwarehouseProductInfoService.newWarehouseProductInfo();\r\nassertSelectCount(1);\r\nassertInsertCount(2);<\/pre>\n
Let\u2019s check the test logs to convince ourselves of its effectiveness:<\/p>\n
INFO  [main]: o.v.s.i.WarehouseProductInfoServiceImpl - newWarehouseProductInfo\r\nHibernate: select company0_.id as id1_6_, company0_.name as name2_6_ from Company company0_\r\nINFO  [main]: n.t.d.l.CommonsQueryLoggingListener - Name:, Time:1, Num:1, Query:{[select company0_.id as id1_6_, company0_.name as name2_6_ from Company company0_][]}\r\nHibernate: insert into WarehouseProductInfo (id, quantity) values (default, ?)\r\nINFO  [main]: n.t.d.l.CommonsQueryLoggingListener - Name:, Time:0, Num:1, Query:{[insert into WarehouseProductInfo (id, quantity) values (default, ?)][19]}\r\nHibernate: insert into Product (id, code, company_id, importer_id, name, version) values (default, ?, ?, ?, ?, ?)\r\nINFO  [main]: n.t.d.l.CommonsQueryLoggingListener - Name:, Time:0, Num:1, Query:{[insert into Product (id, code, company_id, importer_id, name, version) values (default, ?, ?, ?, ?, ?)][phoneCode,1,-5,Phone,0]}<\/pre>\n
Conclusion<\/h2>\n
Code reviewing is a fine technique, but it\u2019s not enough on large scale development projects. That\u2019s why automatic checking is of paramount importance. Once the test written, you are assured that no future change can break your assumptions.<\/p>\n