Chapter 17. Customizing SQL

In this chapter

Falling back to JDBC
Mapping SQL query results
Customizing CRUD operations
Calling stored procedures

In this chapter, we cover customizing and embedding SQL in a Hibernate application. SQL was created in the 1970s, but ANSI didn’t standardized it until 1986. Although each update of the SQL standard has seen new (and many controversial) features, every DBMS product that supports SQL does so in its own unique way. The burden of portability is again on the database application developers. This is where Hibernate helps: its built-in query languages produce SQL that depends on the configured database dialect. Dialects also help produce all other automatically generated SQL (for example, when Hibernate has to retrieve a collection on demand). With a simple dialect switch, you can run your application on a different DBMS. Hibernate generates all SQL statements for you, for all create, read, update, and delete (CRUD) operations.

Sometimes, though, you need more control than Hibernate and the Java Persistence API provide: you need to work at a lower level of abstraction. With Hibernate, you can write your own SQL statements:

17.1. Falling back to JDBC

Path: /examples/src/test/java/org/jpwh/test/querying/sql/JDBCFallback.java

UserTransaction tx = TM.getUserTransaction();
tx.begin();
EntityManager em = JPA.createEntityManager();
<enter/>
Session session = em.unwrap(Session.class);
session.doWork(new QueryItemWork(ITEM_ID));
<enter/>
tx.commit();
em.close();

17.2. Mapping SQL query results

Path: /examples/src/test/java/org/jpwh/test/querying/sql/NativeQueries.java

Query query = em.createNativeQuery(
    "select NAME, AUCTIONEND from {h-schema}ITEM"
);
List<Object[]> result = query.getResultList();
<enter/>
for (Object[] tuple : result) {
    assertTrue(tuple[0] instanceof String);
    assertTrue(tuple[1] instanceof Date);
}

Path: /examples/src/test/java/org/jpwh/test/querying/sql/HibernateSQLQueries.java

org.hibernate.SQLQuery query = session.createSQLQuery(
    "select NAME, AUCTIONEND from {h-schema}ITEM"
);
List<Object[]> result = query.list();
<enter/>
for (Object[] tuple : result) {
    assertTrue(tuple[0] instanceof String);
    assertTrue(tuple[1] instanceof Date);
}

Path: /examples/src/test/java/org/jpwh/test/querying/sql/NativeQueries.java

Query query = em.createNativeQuery(
    "select * from ITEM",
    Item.class
);
<enter/>
List<Item> result = query.getResultList();

Path: /examples/src/test/java/org/jpwh/test/querying/sql/HibernateSQLQueries.java

org.hibernate.SQLQuery query = session.createSQLQuery(
    "select * from ITEM"
);
query.addEntity(Item.class);
<enter/>
List<Item> result = query.list();

Path: /examples/src/test/java/org/jpwh/test/querying/sql/NativeQueries.java

Path: /examples/src/test/java/org/jpwh/test/querying/sql/HibernateSQLQueries.java

Path: /examples/src/test/java/org/jpwh/test/querying/sql/NativeQueries.java

Query query = em.createNativeQuery(
    "select * from ITEM where ID = :id",
    Item.class
);
query.setParameter("id", ITEM_ID);
<enter/>
List<Item> result = query.getResultList();

Path: /examples/src/test/java/org/jpwh/test/querying/sql/HibernateSQLQueries.java

org.hibernate.SQLQuery query = session.createSQLQuery(
    "select * from ITEM where ID = :id"
);
query.addEntity(Item.class);
query.setParameter("id", ITEM_ID);
<enter/>
List<Item> result = query.list();