Unidev – Software Development

Archive for March, 2009

Paul Litwin posted an interesting article that discusses the four pillars of ASP.NET (Web Forms, MVC, AJAX, and Dynamic Data).

He said: “Microsoft used to present ASP.NET Web Forms vs. MVC as a choice between a car and a motorcycle. Both will get you to your job , but some (the majority of the population, I might add) prefer driving a car, while a sizable minority love their motorcycles which give you better gas mileage and independence, but don’t protect you in the rain. To stretch this analogy to its breaking point, let me suggest that ASP.NET AJAX is like riding a bicycle to work (lean and mean, best gas mileage, but it requires you to exercise for your commute and exposes you to the elements like the motorcycle) while Dynamic Data is like taking the bus to work (let metro do the driving for you.)”

You can read his blog entry here:

While calling Oracle stored procedure from Hibernate  there is couple of rules that we need to follow. There can be only one return value and this must be a reference cursor. This return reference cusor must be the first and only out value for the stored procedure. It is required that stored procedure must return a reference cursor to be used from Hibernate.

Here is my stored procedure which return book name and ISBN number for a given branch and for a given author

CREATE OR REPLACE PROCEDURE SP_LIB_DTL(p_cursor    out sys_refcursor,
                                       in_brnch_cd in number,
                                       in_auth_cd in number)
as
  bookName varchar2(8);
  ISBN     number;
begin
  bookName := null;
  ISBN     := 0;
  open p_cursor for
    select l.book_name, l.isbn_nbr
      into bookName, ISBN
      from LIB_BRNCH_DTL l
     where l.branch_code = in_brnch_cd
     and l.auth_code = in_auth_cd;

end;

HIbernate Mapping for return class and Named Query:

<?xml version=”1.0″ encoding=”utf-8″?>
<!DOCTYPE hibernate-mapping PUBLIC “-//Hibernate/Hibernate Mapping DTD 3.0//EN”
“http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd“>
<hibernate-mapping>
<class name=”com.org.lib.LibraryDetails”>
<id name=”ISBN” type=”long” />
<property name=”bookName” type=”string” />
</class>
 <sql-query name=”LIB_SP” callable=”true”>
 <return class=”com.org.lib.LibraryDetails”>
   <return-property name=”ISBN” column=”isbn_nbr” />
   <return-property name=”bookName” column=”book_name” />
 </return>
  {  call SP_LIB_DTL(? , :branchCD ,:authorCD) }
 </sql-query>
</hibernate-mapping>

Make sure you have used the correct database field name value for the column attribute for the return property mapping. You will get the following error if you don’t map the correct databse field name

could not execute query; bad SQL grammar [{  call SP_LIB_DTL(? , ?) }];
nested exception is java.sql.SQLException: Invalid column name

Here is the DAO implementation for executing query and to set the bind parameter values.

public  List selectBooks(final BigDecimal branchCode,final BigDecimal authorCode){
        return (List) getHibernateTemplate().execute(new HibernateCallback() {
              public Object doInHibernate(Session session) throws HibernateException, SQLException
              {
                  Query q = session.getNamedQuery(“LIB_SP”);
                  q.setLong(“branchCD”, branchCode.longValue());
                  q.setLong(“authorCD”, authorCode.longValue());
                  return q.list();
              }
          });
      }

Unidev has achieved Gold Certified Partner status with Microsoft along with 4 competencies. (Custom Development Solutions, Business Intelligence, ISV/Software Solutions, Data Management Solutions).