ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.


AddThis Social Bookmark Button

Agile Database Refactoring with Hibernate
Pages: 1, 2, 3

Stored Procedures or INSTEAD OF Triggers

We now want to be able to treat our new views as if they were tables--inserting, updating, and deleting to our hearts' content without actually worrying about what is going on behind the scenes. Although some views may be directly updatable without any further intervention on the part of the database designer, our views are a little more complex, and we want to make sure we control exactly how the database will affect the underlying (CUST_ORDER) table. The best way to do this is to define code on the database that will execute every time we try to execute one of these CUD operations against our views.

In most databases (MS SQL Server, Sybase, Oracle, DB2), we can define INSTEAD OF triggers (PostreSQL uses "rules" that behave similarly) that will be responsible for inserting, updating, and deleting records from the underlying table from which the view is defined. MySQL, however, does not currently support INSTEAD OF triggers. In their place, we can create stored procedures and, through careful configuration of Hibernate mapping files, call these stored procedures every time a CUD operation is triggered in our code (and persisted by Hibernate). Be it stored procedures or instead of triggers, the code is very similar.

Since our example uses MySQL, we will demonstrate our solution using stored procedures.

The code

Our stored procedures for inserting, updating, and deleting into our denormalized table must take into account all aspects of the denormalization: repetitive rows, additional fields, superfluous values, etc. When we use these stored procedures, the data model we created with the definition of nice, normalized views is turned back into the flawed, denormalized structure. Why? Because the rest of our applications are expecting the data to be presented in this way. In addition, our view definitions rely on the data to exist in the current structure.

So what does one of these procedures look like? Here's an example of inserting an item to the order:

create procedure insert_order_item 
   (in itemprice FLOAT, in itemqty INT, in orderid INT, in itemid INT)
      DECLARE p_order_id INT;
      DECLARE p_cust_id INT;
      DECLARE max_order_id INT;
      DECLARE p_itemprice FLOAT;
      -- apply the current price to the line item
      if itemprice is null then 
         select prod_price into p_itemprice from product where prod_id=itemid;
         set p_itemprice = itemprice;
      end if;
      -- get the customer id.
      select order_cust into p_cust_id 
      from cust_order where order_id=orderid limit 1;
      insert into cust_order 
         (order_id, order_cust, order_date, 
          order_item, order_item_price, order_item_qty)
         (orderid, p_cust_id, now(), itemid, p_itemprice, itemqty);


Notice that whatever data is usually missing from the ORDER_ITEM_V view has to be sought out and inserted in the underlying CUST_ORDER table. This procedure, if successful in inserting into the CUST_ORDER table, will return the number of rows affected as 1. It is important to note that Hibernate expects either 1 or 0 as a result of these stored procedures, since it treats them as single rows in tables (even though they are really views). To ensure that this happens, we might have to throw little tricks into our stored procedures. For instance, the stored procedure to update an order may affect various rows in the CUST_ORDER table (one row for every order item). If we were to simply update all the rows with the given order ID, the rows' affected value returned would be greater than 1. Since this would present a problem for Hibernate, we use a small table and update it after the update to the CUST_ORDER table. This causes the stored procedure to return 1 as the number of affected rows (since the update we executed only affects one row). Here is what the stored procedure looks like:

create procedure update_order 
    (in ordercust INT, in orderdate DATETIME, in orderid INT)
        update cust_order set order_cust=ordercust, 
                            where order_id=orderid;
        if row_count() > 0 then
            update help_table set i=i+1;
        end if;

POJOs, Hibernate Mappings, and DAOs

Creating the POJOs and Hibernate mappings for your new, view-based data model is fairly straightforward. There are, however, a couple of gotchas to keep in mind here.

Virtual foreign and primary keys

Although database views do not have foreign and primary keys, you should still map these in your solution's mapping file. This allows other developers to treat this new data model as if it were a true physical model. Furthermore, mapping these elements will ensure an almost seamless transition when you move on to a final solution based on real tables.

Overriding insert, update, and delete

When using stored procedures (you do not need to do this if your solution is implementing instead of triggers), you must override the insert, update, and delete calls with calls to your stored procedures. This is done by adding <sql-insert>, <sql-update> and <sql-delete> elements to the mapping. These elements tell Hibernate to call the given procedures instead of inserting, updating, and deleting directly to the database. Here is the ORDER_V mapping:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"

    <class name="org.onjava.shared.vo.Order" table="order_v" catalog="dma_example">

        <id name="orderId" type="int" column="order_id" />
        <property name="orderCust" type="int" column="order_cust" />
        <property name="orderDate" type="timestamp" column="order_date" length="19"/>
        <set name="items" inverse="true" cascade="all-delete-orphan" lazy="true">
        <key column="oi_order"/>
        <one-to-many class="org.onjava.shared.vo.OrderItem" />
      <sql-insert callable="true">{call insert_order(?, ?, ?)}</sql-insert>
      <sql-update callable="true">{call update_order(?, ?, ?)}</sql-update>
      <sql-delete callable="true">{call delete_order(?)}</sql-delete>

The parameter order is important here. Refer to the custom SQL reference of the Hibernate manual to determine the parameter order in your stored procedures.

Data access objects

Once the right mapping is in place, the data access objects for the view-based data model are identical to table-based models. Hibernate takes care of executing the stored procedures and treats the views much like tables. See this article's sample DMA solution for complete data access classes for the ORDER_V and ORDER_ITEM_V views.

Pages: 1, 2, 3

Next Pagearrow