When it comes to interacting with relational databases, citizens of the Java world tend to be in one of two camps. Lately, the rift continues to widen.
- Use Hibernate ORM for all CRUD operations, all queries, and anything else that would otherwise touch SQL. SQL is icky. Abstract all the things.
- I got 99 problems and Hibernate ORM caused them all. Es el Diablo. Avoid it, no matter what. Use nothing but pure SQL, as God himself intended.
Folks miss an important point: ORM was never meant to be used for everything! It is a tool to be used where applicable. Can it be used as a complete abstraction or replacement for everything involving the database, shielding developers from ever having to learn SQL? For the most part, yes. Should it be? Definitely no. It often makes sense to combine ORM with other SQL approaches, marrying the best of both worlds.
NeighborLink
Throughout this tutorial, we’ll refer to an example model that’s specific to NeighborLink, a network of nonprofit organizations that provide volunteer home improvement to neighbors in need. Operations holistically revolve around a web platform, involving homeowners, volunteers, projects, donations, events, communities, notifications, skills, tools, and a large set of associations between them all.
Command Query Responsibility Segregation
The combination of multiple DB tools is one of the core concepts within Command Query Responsibility Segregation (CQRS). In a nutshell, the design pattern utilizes separate models, approaches, and frameworks for CRUD vs. large-scale fetches.
More specifically, Hibernate ORM excels at managing complex CRUD operations for associated objects. As an example, a homeowner can visit the website and request assistance. That eventually causes a set of associated user, project, skill, funding request, and notification objects needing to be written to the database. To do so with straight SQL is painful, especially considering the large network of foreign keys and join tables. Hibernate ORM persisting and cascading the entities makes a lot of sense. Obviously, it’s a similar story for updates. Additionally, when viewing a single project’s detail page, we have an easy way of obtaining the Project and all of its associations in a few simple steps, rather than managing all the individual queries and building up the objects by-hand.
However, we also support an advanced search capability that queries every project in the system. The results page only needs to display a small subset each Project’s data. Even with proper laziness configured on entity associations, proper fetching strategies, and other tactics, executing the queries with HQL/JPQL/Criteria can result in a less-than-stellar quantity of over-reaching SQL hits. There are certainly ways to further improve the ORM-driven queries. However, I suggest that CQRS is a powerful idea and the hybrid approach is helpful.
ORM for Complex CRUD, SQL for Large-Scale Fetches
So, how exactly should we split up the approach? Here’s what I generally do. Again, in a nutshell, use ORM for complex CRUD — that is precisely why it was designed and developed. For large-scale fetches, rely on SQL.
- CREATE: Generally, my inserts use Hibernate. Since you’re typically building up a set of objects and associations between them, it makes sense to do so with entity classes and allow the ORM to persist them all.
- UPDATE: More often than not, I’ll use Hibernate for updates as well, since edits frequently happen on the entities themselves (retrieve an entity, modify its fields, and re-persist it). However, batch updates are a toss-up. If you’re only changing a handful of fields within a single table, both SQL and HQL/JPQL work well and efficiently.
- DELETE: Again, more often than not, Hibernate excels here. Deleting a row in a table frequently requires additional cleanup of associations (removing the associated row(s) in a join table, nulling foreign keys, etc.). With ORM’s association and “cascading” support, this ends up being fairly trivial. Most RDBMS vendors support some notion of cascading, but ORM’s ability to manage all of it based on the object model is helpful. That being said, simple SQL can be hard to beat for batch deletes acting on a single or small set of tables.
- READ (using the NeighborLink example):
- For fetching a single Project and its associations (a “complex read”) and populating the View Project page, let ORM fetch the entity by ID and rely on your laziness and fetch strategies. This is nearly always preferable over straight SQL, since the retrieval of the object tree is condensed to one line of code and a small set of SQL statements.
- For querying all Projects in the system (“advanced search”), rely on SQL. Since the association tree is deep, properly controlling the SQL generation through ORM gets complex and difficult to maintain. Explicit control over SQL grabbing only what you need, and nothing you don’t, is vital.
CQRS Models
One thing to note: typical CQRS discussions include different models for each separate layer. So, complex CRUD would use the entity model layer, while SQL results would use a completely different set of simplified DTO objects. However, the models can be approached in any number of ways:
- Use the explicit separation. Entities for CRUD, simple DTOs for query results.
- Use the same objects for both. Rather than maintaining multiple models, I often “cheat” and re-use the entity objects for the SQL results.
- Use a layered approach through class inheritance. This can take multiple forms. Generate a base layer of objects containing the essentials, then have both the entities and DTOs extend them. Alternatively, use the DTOs as your base layer and have the entities extend them. Variations come up here and there. The main point is that attempts can be made to reduce duplication.
Statically-Typed SQL
“Ok, we get it. Use SQL where it makes sense. But we don’t want to maintain a bunch of SQL strings in the code. What happens when the schema changes, or worse, we migrate to a different RDBMS vendor?”
Fantastic question. Bonus points for worrying about that. Of course, the other primary benefits to ORMs are portability and statically-typed interactions.
Numerous frameworks exist that can help. One example is jOOQ, which analyzes your RDBMS schema and (at a minimum) generates 1.) a statically-typed metamodel and 2.) a useful Java DSL for building queries. With this approach, you can explicitly define and execute SQL using nothing but Java. If your schema changes, the generated metamodel changes with it, clearly showing issues through compilation errors. And, like ORMs, the metamodel and DSL are aware of the underlying RDBMS vendor through “dialects”, providing automatic portability.
(One somewhat-unfortunate caveat with jOOQ: only fully open source RDBMS vendors are supported in the community version of the framework. In order to use it with Oracle, SQL Server, HANA, Sybase, and other “enterprise” databases, you have to purchase a commercial license.)
SHOW ME THE CODE
Alright, up until now, we’ve been talking from a 30,000′ level. Here’s a sample of how this looks, again using NeighborLink as an example. The approach marries Hibernate ORM and jOOQ for CQRS, HikariCP as the connection pool (I can’t speak highly enough about this library), and Spring MVC wiring it all together (in addition to providing the Transactional support).
First, the relevant sections of the Maven POM. I’m including the jOOQ Maven plugin, showing how the metamodel and DSL is generated at build time. However, I’m omitting some Hibernate specifics (bytecode enhancement, etc.) for the sake of brevity.
<build>
<plugins>
...
<plugin>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<version>${jooq.version}</version>
<executions>
<execution>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<dependencies>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-codegen</artifactId>
<version>${jooq.version}</version>
</dependency>
</dependencies>
<configuration>
<jdbc>
<driver>com.mysql.jdbc.Driver</driver>
<url>jdbc:mysql://[HOST]:[PORT]/[DB NAME]</url>
<user>[USER]</user>
<password>[PASSWORD]</password>
</jdbc>
<generator>
<name>org.jooq.util.DefaultGenerator</name>
<database>
<name>org.jooq.util.mysql.MySQLDatabase</name>
<includes>.*</includes>
<excludes></excludes>
<inputSchema>[DB NAME]</inputSchema>
</database>
<target>
<packageName>org.threeriverdev.neighborlink.query</packageName>
<directory>target/generated-sources/jooq</directory>
</target>
</generator>
</configuration>
</plugin>
...
</build>
<dependencies>
<!-- Spring framework -->
...
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>${spring.version}</version>
</dependency>
...
<!-- Hibernate -->
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>${hibernate.version}</version>
</dependency>
<!-- DataSource -->
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>2.4.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.39</version>
</dependency>
<!-- jOOQ -->
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq</artifactId>
<version>${jooq.version}</version>
</dependency>
<dependency>
<groupId>org.jooq</groupId>
<artifactId>jooq-meta</artifactId>
<version>${jooq.version}</version>
</dependency>
...
</dependencies>This is how it’s all bootstrapped. NeighborLink is a typical Spring MVC app, so here we’re making use of the Java-based @Configuration to instantiate relevant beans. For anyone with past Hibernate experience, the ORM pieces should look familiar.
One important thing to note: the jOOQ setup is making use of Spring’s TransactionAwareDataSourceProxy, meaning it’s able to plug into the TransactionManager we have defined. Also note that, in this case, that will be a HibernateTransactionManager. That’s entirely safe to use outside of Hibernate! See notes on Spring’s HibernateTransactionManager JavaDoc for more info. Essentially, it’s perfectly acceptable to share the same DataSource and HibernateTransactionManager for all DB interactions, including ORM, jOOQ, and straight JDBC!
An off-topic bit I left in is the #isProduction support. Based on the existence of an environment variable, our ConfigService knows whether or not this app instance is running in the production server or locally. We decide which database to use based on that.
@Configuration
@EnableWebMvc
@EnableTransactionManagement
@ComponentScan
public class AppConfig {
@Autowired
private Environment env;
@Autowired
private ConfigService configService;
...
@Bean(destroyMethod = "close")
public DataSource dataSource() {
String dbHost;
String dbPort;
String dbName;
String dbUsername;
String dbPassword;
if (configService.isProduction()) {
// We're in a production environment.
dbHost = "localhost";
dbPort = "3306";
dbName = configService.getConfig().getDatabase().getName();
dbUsername = configService.getConfig().getDatabase().getUsername();
dbPassword = configService.getConfig().getDatabase().getPassword();
} else {
// Local dev or unit test.
dbHost = "[HOST]";
dbPort = "[PORT]";
dbName = "[DB NAME]";
dbUsername = "[USER]";
dbPassword = "[PASSWORD]";
}
final String url = new StringBuilder()
.append("jdbc:mysql://")
.append(dbHost)
.append(":")
.append(dbPort)
.append("/")
.append(dbName)
.toString();
HikariDataSource dataSource = new HikariDataSource();
...[HikariCP options]
MysqlDataSource mysqlDataSource = new MysqlDataSource();
mysqlDataSource.setURL(url);
mysqlDataSource.setUser(dbUsername);
mysqlDataSource.setPassword(dbPassword);
...[MySQL-specific options]
dataSource.setDataSource(mysqlDataSource);
return dataSource;
}
// HIBERNATE
@Bean
public LocalSessionFactoryBean sessionFactory() {
LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
sessionFactory.setDataSource(dataSource());
sessionFactory.setHibernateProperties(hibernateProperties());
List<String> packages = new ArrayList<String>();
packages.add("org.threeriverdev.neighborlink.entity.core");
...
sessionFactory.setPackagesToScan(packages.toArray(new String[packages.size()]));
return sessionFactory;
}
@Bean
public HibernateTransactionManager transactionManager() {
HibernateTransactionManager txManager = new HibernateTransactionManager();
txManager.setSessionFactory(sessionFactory().getObject());
txManager.setDataSource(dataSource());
return txManager;
}
private Properties hibernateProperties() {
final Properties properties = new Properties();
...[Add all "hibernate.*" specific properties -- dialect, etc.]
return properties;
}
// JOOQ
@Bean
public TransactionAwareDataSourceProxy transactionAwareDataSource() {
return new TransactionAwareDataSourceProxy(dataSource());
}
@Bean
public DataSourceConnectionProvider connectionProvider() {
return new DataSourceConnectionProvider(transactionAwareDataSource());
}
@Bean()
public DSLContext jooq() {
// Generated source assumes the development DB. Dynamically change it to the production DB name.
String dbName;
if (configService.isProduction()) {
// We're in a production environment.
dbName = configService.getConfig().getDatabase().getName();
} else {
// Local dev or unit test.
dbName = "[SANDBOX DB NAME]";
}
// The DB name used in the generated DSL (see the Maven plugin use) will not be the same as
// the production DB, unless running in a local dev env! withSchemata allows us
// to override that during runtime.
Settings settings = new Settings().withRenderMapping(
new RenderMapping().withSchemata(new MappedSchema().withInput("[SANDBOX DB NAME]").withOutput(dbName)));
return DSL.using(connectionProvider(), SQLDialect.MYSQL, settings);
}
}I’ll now show snippets from a Controller, a Service, and two DAOs, providing a glimpse into how we make use of ORM and jOOQ in a CQRS pattern.
@Controller
...
public class ProjectController {
@Autowired
protected ProjectService projectService;
...
// COMPLEX READ
@RequestMapping("/project/{projectId}")
public String project(@PathVariable("projectId") int projectId, ModelMap model) {
...
Project project = projectService.getProject(projectId);
...
model.addAttribute("project", project);
...
return "/view/project.jsp";
}
...
// COMPLEX CREATE
@RequestMapping(value = "/project}", method = RequestMethod.POST)
public String addProject(@ModelAttribute("addProjectWizard") AddProjectWizard wizard, ModelMap model) {
...
Project project = new Project();
...[Build up the object.]
projectService.insert(project);
...
return "redirect:/project/" + project.getId();
}
...
// COMPLEX UPDATE
@RequestMapping(value = "/project/{projectId}", method = RequestMethod.POST)
public String editProject(@PathVariable("projectId") int projectId, @ModelAttribute("project") Project projectEdit, ModelMap model) {
...
Project project = projectService.getProject(projectId));
...[Edit 'project' with fields from 'projectEdit'. Ex: project.setDescription(projectEdit.getDescription);]
projectService.update(project);
...
return "redirect:/project/" + project.getId();
}
// LARGE-SCALE FETCH
// NOTE: AdvancedSearchForm is a bean that was bound to a JSP/JSTL form, containing free-text search, checkboxes, etc.
@RequestMapping(value = "/advancedSearch")
public String projects(@ModelAttribute("advancedSearch") AdvancedSearchForm advancedSearch, ModelMap model) {
...
List<Project> projects = projectService.search(advancedSearch);
...
model.addAttribute("projects", projects);
...
return "/view/projects.jsp";
}@Service
@Transactional
...
public class ProjectService {
@Autowired
private ProjectDao projectDao;
@Autowired
private ProjectJooqDao projectJooqDao;
...
// COMPLEX READ
public Project getProject(int id) {
return projectDao.getProject(id);
}
...
// COMPLEX CREATE
public void insert(Project project) {
projectDao.insert(project);
}
...
// COMPLEX UPDATE
public void update(Project project) {
project.setLastUpdated(Calendar.getInstance());
projectDao.update(project);
}
...
// LARGE-SCALE FETCH
public List<Project> search(AdvancedSearchForm advancedSearch) {
return projectJooqDao.search(advancedSearch);
}@Repository
...
public class ProjectDao {
@Autowired
private SessionFactory sessionFactory;
...
// COMPLEX READ
public Project getProject(int id) {
// Note that the simple #get is likely not enough, since there are likely associations that are LAZY.
// This likely implies HQL + joins, a fetch profile, explicit use of Hibernate#initialize, or a combo.
return (Project) sessionFactory.getCurrentSession().get(Project.class, id);
}
...
// COMPLEX CREATE
public void insert(Project project) {
sessionFactory.getCurrentSession().save(project);
}
...
// COMPLEX UPDATE
public void update(Project project) {
sessionFactory.getCurrentSession().update(project);
}@Repository
…
public class ProjectJooqDao {
@Autowired
private DSLContext jooq;
...
// LARGE-SCALE FETCH
public List<Project> search(AdvancedSearchForm advancedSearch) {
Result<Record> results = jooq.selectDistinct()
.from(Projects.PROJECTS)
.join(...)
...
.where(...)
.orderBy(...)
.fetch();
// NOTE: Mapping the jOOQ result can be done in any number of ways!
// jOOQ DSL includes a fetchInto(Class) object that can map results
// into POJOs automatically, including support for a JPA annotated
// primitive fields. However, since Project contains enums, collections,
// and other non-primitives, it's easier to build this manually.
// More recent versions of jOOQ include integration with
// frameworks like ModelMapper that can help automate and clean up the mapping!
List<Project> projects = new ArrayList<>();
...[Build up each Project using the jOOQ Records.]
return projects;
}