8.2. Customizing SQL
SQL started its life in the 1970s but wasn't (ANSI) standardized 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 mechanisms, HQL and the Criteria API, produce SQL that depends on the configured database dialect. All other automatically generated SQL (for example, when a collection has to be retrieved on demand) is also produced with the help of dialects. With a simple switch of the dialect, you can run your application on a different DBMS.
To support this portability, Hibernate has to handle three kinds of operations:
Every data-retrieval operation results in SELECT statements being executed. Many variations are possible; for example, database products may use a different syntax for the join operation or how a result can be limited to a particular number of rows.
Every data modification requires the execution of Data Manipulation Language (DML) statements, such as UPDATE, INSERT, and DELETE. DML often isn't as complex as data retrieval, but it still has product-specific variations.
A database schema must be created or altered before DML and data retrieval can be executed. You use Data Definition Language (DDL) to work on the database catalog; it includes statements such as CREATE, ALTER, and DROP. DDL is almost completely vendor specific, but most products have at least a similar syntax structure.
Another term we use often is CRUD, for create, read, update, and delete. Hibernate generates all this SQL for you, for all CRUD operations and schema definition. The translation is based on an org.hibernate.dialect.Dialect implementation—Hibernate comes bundled with dialects for all popular SQL database management systems. We encourage you to look at the source code of the dialect you're using; it's not difficult to read. Once you're more experienced with Hibernate, you may even want to extend a dialect or write your own. For example, to register a custom SQL function for use in HQL selects, you'd extend an existing dialect with a new subclass and add the registration code—again, check the existing source code to find out more about the flexibility of the dialect system.
On the other hand, you sometimes need more control than Hibernate APIs (or HQL) provide, when you need to work on a lower level of abstraction. With Hibernate you can override or completely replace all CRUD SQL statements that will be executed. You can customize and extend all DDL SQL statements that define your schema, if you rely on Hibernate's automatic schema-export tool (you don't have to).
Furthermore Hibernate allows you to get a plain JDBC Connection object at all times through session.connection(). You should use this feature as a last resort, when nothing else works or anything else would be more difficult than plain JDBC. With the newest Hibernate versions, this is fortunately exceedingly rare, because more and more features for typical stateless JDBC operations (bulk updates and deletes, for example) are built-in, and many extension points for custom SQL already exist.
This custom SQL, both DML and DDL, is the topic of this section. We start with custom DML for create, read, update, and delete operations. Later, we integrate stored database procedures to do the same work. Finally, we look at DDL customization for the automatic generation of a database schema and how you can create a schema that represents a good starting point for the optimization work of a DBA.
Note that at the time of writing this detailed customization of automatically generated SQL isn't available in annotations; hence, we use XML metadata exclusively in the following examples. We expect that a future version of Hibernate Annotations will include better support for SQL customization.
8.2.1. Writing custom CRUD statements
The first custom SQL you'll write is used to load entities and collections. (Most of the following code examples show almost the same SQL Hibernate executes by default, without much customization—this helps you to understand the mapping technique more quickly.)
Loading entities and collections with custom SQL
For each entity class that requires a custom SQL operation to load an instance, you define a <loader> reference to a named query:
<class name="User" table="USERS">
<id name="id" column="USER_ID"...
<loader query-ref="loadUser"/>
...
</class>
The loadUser query can now be defined anywhere in your mapping metadata, separate and encapsulated from its use. This is an example of a simple query that retrieves the data for a User entity instance:
<sql-query name="loadUser">
<return alias="u" class="User"/>
select
us.USER_ID as {u.id},
us.FIRSTNAME as {u.firstname},
us.LASTNAME as {u.lastname},
us.USERNAME as {u.username},
us."PASSWORD" as {u.password},
us.EMAIL as {u.email},
us.RANKING as {u.ranking},
us.IS_ADMIN as {u.admin},
us.CREATED as {u.created},
us.HOME_STREET as {u.homeAddress.street},
us.HOME_ZIPCODE as {u.homeAddress.zipcode},
us.HOME_CITY as {u.homeAddress.city},
us.DEFAULT_BILLING_DETAILS_ID as {u.defaultBillingDetails}
from
USERS us
where
us.USER_ID = ?
</sql-query>
As you can see, the mapping from column names to entity properties uses a simple aliasing. In a named loader query for an entity, you have to SELECT the following columns and properties:
The primary key columns and primary key property or properties, if a composite primary key is used.
All scalar properties, which must be initialized from their respective column(s).
All composite properties which must be initialized. You can address the individual scalar elements with the following aliasing syntax: {entityalias.componentProperty.scalarProperty}.
All foreign key columns, which must be retrieved and mapped to the respective many-to-one property. See the DEFAULT_BILLING_DETAILS_ID example in the previous snippet.
All scalar properties, composite properties, and many-to-one entity references that are inside a <join> element. You use an inner join to the secondary table if all the joined properties are never NULL; otherwise, an outer join is appropriate. (Note that this isn't shown in the example.)
If you enable lazy loading for scalar properties, through bytecode instrumentation, you don't need to load the lazy properties. See chapter 13, section 13.1.6, "Lazy loading with interception."
The {propertyName} aliases as shown in the previous example are not absolutely necessary. If the name of a column in the result is the same as the name of a mapped column, Hibernate can automatically bind them together.
You can even call a mapped query by name in your application with session.getNamedQuery("loadUser"). Many more things are possible with custom SQL queries, but we'll focus on basic SQL customization for CRUD in this section. We come back to other relevant APIs in chapter 15, section 15.2, "Using native SQL queries."
Let's assume that you also want to customize the SQL that is used to load a collection—for example, the items sold by a User. First, declare a loader reference in the collection mapping:
<set name="items" inverse="true">
<key column="SELLER_ID" not-null="true"/>
<one-to-many class="Item"/>
<loader query-ref="loadItemsForUser"/>
</set>
The named query loadItemsForUser looks almost the same as the entity loader:
<sql-query name="loadItemsForUser">
<load-collection alias="i" role="User.items"/>
select
{i.*}
from
ITEM i
where
i.SELLER_ID = :id
</sql-query>
There are two major differences: One is the <load-collection> mapping from an alias to a collection role; it should be self-explanatory. What is new in this query is an automatic mapping from the SQL table alias ITEM i to the properties of all items with {i.*}. You created a connection between the two by using the same alias: the symbol i. Furthermore, you're now using a named parameter, :id, instead of a simple positional parameter with a question mark. You can use whatever syntax you prefer.
Sometimes, loading an entity instance and a collection is better done in a single query, with an outer join (the entity may have an empty collection, so you can't use an inner join). If you want to apply this eager fetch, don't declare a loader references for the collection. The entity loader takes care of the collection retrieval:
<sql-query name="loadUser">
<return alias="u" class="User"/>
<return-join alias="i" property="u.items"/>
select
{u.*}, {i.*}
from
USERS u
left outer join ITEM i
on u.USER_ID = i.SELLER_ID
where
u.USER_ID = ?
</sql-query>
Note how you use the <return-join> element to bind an alias to a collection property of the entity, effectively linking both aliases together. Further note that this technique also works if you'd like to eager-fetch one-to-one and many-to-one associated entities in the original query. In this case, you may want an inner join if the associated entity is mandatory (the foreign key can't be NULL) or an outer join if the target is optional. You can retrieve many single-ended associations eagerly in one query; however, if you (outer-) join more than one collection, you create a Cartesian product, effectively multiplying all collection rows. This can generate huge results that may be slower than two queries. You'll meet this limitation again when we discuss fetching strategies in chapter 13.
As mentioned earlier, you'll see more SQL options for object loading later in the book. We now discuss customization of insert, update, and delete operations, to complete the CRUD basics.
Custom insert, update, and delete
Hibernate produces all trivial CRUD SQL at startup. It caches the SQL statements internally for future use, thus avoiding any runtime cost of SQL generation for the most common operations. You've seen how you can override the R of CRUD, so let's do the same for CUD.
For each entity or collection, you can define custom CUD SQL statements inside the <sql-insert>, <sql-delete>, and <sql-update> element, respectively:
<class name="User" table="USERS">
<id name="id" column="USER_ID"...
...
<join table="BILLING_ADDRESS" optional="true">
<key column="USER_ID"/>
<component name="billingAddress" class="Address">
<property ...
</component>
<sql-insert>
insert into BILLING_ADDRESS
(STREET, ZIPCODE, CITY, USER_ID)
values (?, ?, ?, ?)
</sql-insert>
<sql-update>...</sql-update>
<sql-delete>...</sql-delete>
</join>
<sql-insert>
insert into USERS (FIRSTNAME, LASTNAME, USERNAME,
"PASSWORD", EMAIL, RANKING, IS_ADMIN,
CREATED, DEFAULT_BILLING_DETAILS_ID,
HOME_STREET, HOME_ZIPCODE, HOME_CITY,
USER_ID)
values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
</sql-insert>
<sql-update>...</sql-update>
<sql-delete>...</sql-delete>
</class>
This mapping example may look complicated, but it's really simple. You have two tables in a single mapping: the primary table for the entity, USERS, and the secondary table BILLING_ADDRESS from your legacy mapping earlier in this chapter. Whenever you have secondary tables for an entity, you have to include them in any custom SQL—hence the <sql-insert>, <sql-delete>, and <sql-update> elements in both the <class> and the <join> sections of the mapping.
The next issue is the binding of arguments for the statements. For CUD SQL customization, only positional parameters are supported at the time of writing. But what is the right order for the parameters? There is an internal order to how Hibernate binds arguments to SQL parameters. The easiest way to figure out the right SQL statement and parameter order is to let Hibernate generate one for you. Remove your custom SQL from the mapping file, enable DEBUG logging for the org.hibernate.persister.entity package, and watch (or search) the Hibernate startup log for lines similar to these:
AbstractEntityPersister - Insert 0: insert into USERS (FIRSTNAME, LASTNAME, USERNAME, "PASSWORD", EMAIL, RANKING, IS_ADMIN, CREATED, DEFAULT_BILLING_DETAILS_ID, HOME_STREET, HOME_ZIPCODE, HOME_CITY, USER_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AbstractEntityPersister - Update 0: update USERS set FIRSTNAME=?, LASTNAME=?, "PASSWORD"=?, EMAIL=?, RANKING=?, IS_ADMIN=?, DEFAULT_BILLING_DETAILS_ID=?, HOME_STREET=?, HOME_ZIPCODE=?, HOME_CITY=? where USER_ID=? ...
You can now copy the statements you want to customize into your mapping file and make the necessary changes. For more information on logging in Hibernate, refer to "Enabling logging statistics" in chapter 2, in section 2.1.3.
You've now mapped CRUD operations to custom SQL statements. On the other hand, dynamic SQL isn't the only way how you can retrieve and manipulate data. Predefined and compiled procedures stored in the database can also be mapped to CRUD operations for entities and collections.
8.2.2. Integrating stored procedures and functions
Stored procedures are common in database application development. Moving code closer to the data and executing it inside the database has distinct advantages.
First, you don't have to duplicate functionality and logic in each program that accesses the data. A different point of view is that a lot of business logic shouldn't be duplicated, so it can be applied all the time. This includes procedures that guarantee the integrity of the data: for example, constraints that are too complex to be implemented declaratively. You'll usually also find triggers in a database that has procedural integrity rules.
Stored procedures have advantages for all processing on large amounts of data, such as reporting and statistical analysis. You should always try to avoid moving large data sets on your network and between your database and application servers, so a stored procedure is a natural choice for mass data operations. Or, you can implement a complex data-retrieval operation that assembles data with several queries before it passes the final result to the application client.
On the other hand, you'll often see (legacy) systems that implement even the most basic CRUD operations with a stored procedure. As a variation of this, systems that don't allow any direct SQL DML, but only stored procedure calls, also had (and sometimes still have) their place.
You may start integrating existing stored procedures for CRUD or for mass data operations, or you may begin writing your own stored procedure first.
Writing a procedure
Programming languages for stored procedures are usually proprietary. Oracle PL/SQL, a procedural dialect of SQL, is very popular (and available with variations in other database products). Some databases even support stored procedures written in Java. Standardizing Java stored procedures was part of the SQLJ effort, which, unfortunately, hasn't been successful.
You'll use the most common stored procedure systems in this section: Oracle databases and PL/SQL. It turns out that stored procedures in Oracle, like so many other things, are always different than you expect; we'll tell you whenever something requires extra attention.
A stored procedure in PL/SQL has to be created in the database catalog as source code and then compiled. Let's first write a stored procedure that can load all User entities that match a particular criterion:
<database-object>
<create>
create or replace procedure SELECT_USERS_BY_RANK
(
OUT_RESULT out SYS_REFCURSOR,
IN_RANK in int
) as
begin
open OUT_RESULT for
select
us.USER_ID as USER_ID,
us.FIRSTNAME as FIRSTNAME,
us.LASTNAME as LASTNAME,
us.USERNAME as USERNAME,
us."PASSWORD" as PASSWD,
us.EMAIL as EMAIL,
us.RANKING as RANKING,
us.IS_ADMIN as IS_ADMIN,
us.CREATED as CREATED,
us.HOME_STREET as HOME_STREET,
us.HOME_ZIPCODE as HOME_ZIPCODE,
us.HOME_CITY as HOME_CITY,
ba.STREET as BILLING_STREET,
ba.ZIPCODE as BILLING_ZIPCODE,
ba.CITY as BILLING_CITY,
us.DEFAULT_BILLING_DETAILS_ID
as DEFAULT_BILLING_DETAILS_ID
from
USERS us
left outer join
BILLING_ADDRESS ba
on us.USER_ID = ba.USER_ID
where
us.RANKING >= IN_RANK;
end;
</create>
<drop>
drop procedure SELECT_USERS_BY_RANK
</drop>
</database-object>
You embed the DDL for the stored procedure in a <database-object> element for creation and removal. That way, Hibernate automatically creates and drops the procedure when the database schema is created and updated with the hbm2ddl tool. You could also execute the DDL by hand on your database catalog. Keeping it in your mapping files (in whatever location seems appropriate, such as in MyStoredProcedures.hbm.xml) is a good choice if you're working on a nonlegacy system with no existing stored procedures. We'll come back to other options for the <database-object> mapping later in this chapter.
As before, the stored procedure code in the example is straightforward: a join query against the base tables (primary and secondary tables for the User class) and a restriction by RANKING, an input argument to the procedure.
You must observe a few rules for stored procedures mapped in Hibernate. Stored procedures support IN and OUT parameters. If you use stored procedures with Oracle's own JDBC drivers, Hibernate requires that the first parameter of the stored procedure is an OUT; and for stored procedures that are supposed to be used for queries, the query result is supposed to be returned in this parameter. In Oracle 9 or newer, the type of the OUT parameter has to be a SYS_REFCURSOR. In older versions of Oracle, you must define your own reference cursor type first, called REF CURSOR—examples can be found in Oracle product documentation. All other major database management systems (and drivers for the Oracle DBMS not from Oracle) are JDBC-compliant, and you can return a result directly in the stored procedure without using an OUT parameter. For example, a similar procedure in Microsoft SQL Server would look as follows:
create procedure SELECT_USERS_BY_RANK
@IN_RANK int
as
select
us.USER_ID as USER_ID,
us.FIRSTNAME as FIRSTNAME,
us.LASTNAME as LASTNAME,
...
from
USERS us
where us.RANKING >= @IN_RANK
Let's map this stored procedure to a named query in Hibernate.
Querying with a procedure
A stored procedure for querying is mapped as a regular named query, with some minor differences:
<sql-query name="loadUsersByRank" callable="true">
<return alias="u" class="User">
<return-property name="id" column="USER_ID"/>
<return-property name="firstname" column="FIRSTNAME"/>
<return-property name="lastname" column="LASTNAME"/>
<return-property name="username" column="USERNAME"/>
<return-property name="password" column="PASSWD"/>
<return-property name="email" column="EMAIL"/>
<return-property name="ranking" column="RANKING"/>
<return-property name="admin" column="IS_ADMIN"/>
<return-property name="created" column="CREATED"/>
<return-property name="homeAddress">
<return-column name="HOME_STREET"/>
<return-column name="HOME_ZIPCODE"/>
<return-column name="HOME_CITY"/>
</return-property>
<return-property name="billingAddress">
<return-column name="BILLING_STREET"/>
<return-column name="BILLING_ZIPCODE"/>
<return-column name="BILLING_CITY"/>
</return-property>
<return-property name="defaultBillingDetails"
column="DEFAULT_BILLING_DETAILS_ID"/>
</return>
{ call SELECT_USERS_BY_RANK(?, :rank) }
</sql-query>
The first difference, compared to a regular SQL query mapping, is the callable="true" attribute. This enables support for callable statements in Hibernate and correct handling of the output of the stored procedure. The following mappings bind the column names returned in the procedures result to the properties of a User object. One special case needs extra consideration: If multicolumn properties, including components (homeAddress), are present in the class, you need to map their columns in the right order. For example, the homeAddress property is mapped as a <component> with three properties, each to its own column. Hence, the stored procedure mapping includes three columns bound to the homeAddress property.
The call of the stored procedure prepares one OUT (the question mark) and a named input parameter. If you aren't using the Oracle JDBC drivers (other drivers or a different DBMS), you don't need to reserve the first OUT parameter; the result can be returned directly from the stored procedure.
Look at the regular class mapping of the User class. Notice that the column names returned by the procedure in this example are the same as the column names you already mapped. You can omit the binding of each property and let Hibernate take care of the mapping automatically:
<sql-query name="loadUsersByRank" callable="true">
<return class="User"/>
{ call SELECT_USERS_BY_RANK(?, :rank) }
</sql-query>
The responsibility for returning the correct columns, for all properties and foreign key associations of the class with the same names as in the regular mappings, is now moved into the stored procedure code. Because you have aliases in the stored procedure already (select ... us.FIRSTNAME as FIRSTNAME...), this is straightforward. Or, if only some of the columns returned in the result of the procedure have different names than the ones you mapped already as your properties, you only need to declare these:
<sql-query name="loadUsersByRank" callable="true">
<return class="User">
<return-property name="firstname" column="FNAME"/>
<return-property name="lastname" column="LNAME"/>
</return>
{ call SELECT_USERS_BY_RANK(?, :rank) }
</sql-query>
Finally, let's look at the call of the stored procedure. The syntax you're using here, { call PROCEDURE() }, is defined in the SQL standard and portable. A nonportable syntax that works for Oracle is begin PROCEDURE(); end;. It's recommended that you always use the portable syntax. The procedure has two parameters. As explained, the first is reserved as an output parameter, so you use a positional parameter symbol (?). Hibernate takes care of this parameter if you configured a dialect for an Oracle JDBC driver. The second is an input parameter you have to supply when executing the call. You can either use only positional parameters or mix named and positional parameters. We prefer named parameters for readability.
Querying with this stored procedure in the application looks like any other named query execution:
Query q = session.getNamedQuery("loadUsersByRank");
q.setParameter("rank", 12);
List result = q.list();
At the time of writing, mapped stored procedures can be enabled as named queries, as you did in this section, or as loaders for an entity, similar to the loadUser example you mapped earlier.
Stored procedures can not only query and load data, but also manipulate data. The first use case for this is mass data operations, executed in the database tier. You shouldn't map this in Hibernate but should execute it with plain JDBC: session.connection().prepareCallableStatement(); and so on. The data-manipulation operations you can map in Hibernate are the creation, deletion, and update of an entity object.
Mapping CUD to a procedure
Earlier, you mapped <sql-insert>, <sql-delete>, and <sql-update> elements for a class to custom SQL statements. If you'd like to use stored procedures for these operations, change the mapping to callable statements:
<class name="User">
...
<sql-update callable="true" check="none">
{ call UPDATE_USER(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }
</sql-update>
</class>
With the current version of Hibernate, you have the same problem as before: the binding of values to the positional parameters. First, the stored procedure must have the same number of input parameters as expected by Hibernate (enable the SQL log as shown earlier to get a generated statement you can copy and paste). The parameters again must be in the same order as expected by Hibernate.
Consider the check="none" attribute. For correct (and, if you enabled it) optimistic locking, Hibernate needs to know whether this custom update operation was successful. Usually, for dynamically generated SQL, Hibernate looks at the number of updated rows returned from an operation. If the operation didn't or couldn't update any rows, an optimistic locking failure occurs. If you write your own custom SQL operation, you can customize this behavior as well.
With check="none", Hibernate expects your custom procedure to deal internally with failed updates (for example, by doing a version check of the row that needs to be updated) and expects your procedure to throw an exception if something goes wrong. In Oracle, such a procedure is as follows:
<database-object>
<create>
create or replace procedure UPDATE_USER
(IN_FIRSTNAME in varchar,
IN_LASTNAME in varchar,
IN_PASSWORD in varchar,
...
)
as
rowcount INTEGER;
begin
update USERS set
FIRSTNAME = IN_FIRSTNAME,
LASTNAME = IN_LASTNAME,
"PASSWORD" = IN_PASSWORD,
where
OBJ_VERSION = ...;
rowcount := SQL%ROWCOUNT;
if rowcount != 1 then
RAISE_APPLICATION_ERROR( -20001, 'Version check failed'),
end if;
end;
</create>
<drop>
drop procedure UPDATE_USER
</drop>
</database-object>
The SQL error is caught by Hibernate and converted into an optimistic locking exception you can then handle in application code. Other options for the check attribute are as follows:
If you enable check="count", Hibernate checks the number of modified rows using the plain JDBC API. This is the default and used when you write dynamic SQL without stored procedures.
If you enable check="param", Hibernate reserves an OUT parameter to get the return value of the stored procedure call. You need to add an additional question mark to your call and, in your stored procedure, return the row count of your DML operation on this (first) OUT parameter. Hibernate then validates the number of modified rows for you.
Mappings for insertion and deletion are similar; all of these must declare how optimistic lock checking is performed. You can copy a template from the Hibernate startup log to get the correct order and number of parameters.
Finally, you can also map stored functions in Hibernate. They have slightly different semantics and use cases.
Mapping stored functions
A stored function only has input parameters—no output parameters. However, it can return a value. For example, a stored function can return the rank of a user:
<database-object>
<create>
create or replace function GET_USER_RANK
(IN_USER_ID int)
return int is
RANK int;
begin
select
RANKING
into
RANK
from
USERS
where
USER_ID = IN_USER_ID;
return RANK;
end;
</create>
<drop>
drop function GET_USER_RANK
</drop>
</database-object>
This function returns a scalar number. The primary use case for stored functions that return scalars is embedding a call in regular SQL or HQL queries. For example, you can retrieve all users who have a higher rank than a given user:
String q = "from User u where u.ranking > get_user_rank(:userId)";
List result = session.createQuery(q)
.setParameter("userId", 123)
.list();
This query is in HQL; thanks to the pass-through functionality for function calls in the WHERE clause (not in any other clause though), you can call any stored function in your database directly. The return type of the function should match the operation: in this case, the greater-than comparison with the ranking property, which is also numeric.
If your function returns a resultset cursor, as in previous sections, you can even map it as a named query and let Hibernate marshal the resultset into an object graph.
Finally, remember that stored procedures and functions, especially in legacy databases, sometimes can't be mapped in Hibernate; in such cases you have to fall back to plain JDBC. Sometimes you can wrap a legacy stored procedure with another stored procedure that has the parameter interface expected by Hibernate. There are too many varieties and special cases to be covered in a generic mapping tool. However, future versions of Hibernate will improve mapping capabilities—we expect better handling of parameters (no more counting of question marks) and support for arbitrary input and output arguments to be available in the near future.
You've now completed customization of runtime SQL queries and DML. Let's switch perspective and customize the SQL used for the creation and modification of the database schema, the DDL.