Complete example on useing stored procedures with hibernate

An Outline of An Example

We want to represent a User in Java. we may define something like:

public class User {

    private String id;

    private String gender;

    protected final Logger log = LogManager.getLogger(this.getClass());

    public User() {

    }

    public void setId(String id) {

        log.info("Set id: " + id);

        this.id = id;

    }

    public String getId() {

         return this.id;

    }

    public void setGender(String gender) {

        log.info("Set gender: " + gender);

        this.gender = gender;

   }

   public String getGender() {

        return this.gender;

   }

}

MySQL Schema Tables

Pretty straightforward. Now, let’s say we have two tables in MySQL that collectively represent the User object:

DROP TABLE IF EXISTS User;

CREATE TABLE User  (

    id  VARCHAR (50) NOT NULL,

    PRIMARY KEY(id)

); 

DROP TABLE IF EXISTS Pref;

CREATE TABLE Pref  (

    User_id VARCHAR (50) NOT NULL,

    gender VARCHAR (2) NOT NULL,

    PRIMARY KEY (User_id),

    FOREIGN KEY (User_id) REFERENCES User (id)

); 

Although the above example with the gender would be better as a 1-to-1 field within one table (i.e., the User table), one could imagine some other real-world examples using 1:N relationships using phone numbers, usernames, etc. So how can we map and bind these two tables to one object using stored procedures?

Hibernate 3.x Mapping File

First we must provide a mapping file to Hibernate. It looks like the following:

<hibernate-mapping>

        <class name=”User” table=”User”>

               <id name=”id” column=”id” type=”java.lang.String”>

                       <generator class=”assigned”/>

               </id>

               <property name=”gender” column=”gender” type=”java.lang.String”/>

            <loader query-ref=”getUser”>

            <sql-insert callable=”true”>{call createUser (?,?)}

            <sql-update callable=”true”>{call updateUser (?,?)}

            <sql-delete callable=”true”>{call deleteUser (?)}

        </class>

        <sql-query name=”getUser” callable=”true”>

           <return alias=”user” class=”User”>

              <return-property name=”id” column=”id” />

              <return-property name=”gender” column=”gender” />

           </return>

       { call getUser(?) }

    </sql-query>

</hibernate-mapping>

For anyone who has read older documents on Hibernate notice the lack of OUT parameters to MySQL. Hibernate 3.x apparently uses JDBC and possibly some introspection to figure out row counts, etc.

MySQL Stored Procedures

The stored procedures in the MySQL schema look like the following:

DELIMITER //

DROP PROCEDURE IF EXISTS getUser;

//

/*

 * Get a user.

 */

CREATE PROCEDURE getUser (IN inId VARCHAR(50))

BEGIN

   SELECT u.id, p.gender FROM User AS u, Pref AS p WHERE u.id = inId AND p.User_id = u.id;

END

//

DROP PROCEDURE IF EXISTS createUser;

//

/*

 * Create a user profile.

 */

CREATE PROCEDURE createUser (IN inGender VARCHAR(2), IN inId VARCHAR(50))

BEGIN

   INSERT INTO User (id) VALUES (inId);

   INSERT INTO Pref (User_id, gender) VALUES (inId, inGender);

END

//

DROP PROCEDURE IF EXISTS updateUser;

//

/*

 * Update a user profile.

 */

CREATE PROCEDURE updateUser (IN inGender VARCHAR(2), IN inId VARCHAR(50))

BEGIN

   UPDATE Pref SET gender = inGender WHERE User_id = inId;

END

//

DROP PROCEDURE IF EXISTS deleteUser;

//

/*

 * Delete the user.

 */

CREATE PROCEDURE deleteUser (IN inId VARCHAR(50))

BEGIN

   DELETE from Pref WHERE User_id = inId;

   DELETE from User WHERE id = inId;

END

//

So what does the code within our data access object in Java look like? Although it uses the Spring Framework HibernateDAOSupport class, more on that in a minute, here is the code:

DAO Java Code

public boolean createUser(User user) {

        boolean status = false;

        Session session = this.getSessionFactory().openSession();

        session.clear();

        session.beginTransaction();

        session.save(user);

        session.getTransaction().commit();

        session.close();

        status = true;

        return status;

    }

    /**

     * Get a user.

     */

    @SuppressWarnings("unchecked")

        public User getUser(User user) {

        User u = null;

        Session session = this.getSessionFactory().openSession();

        Query query = session.getNamedQuery("getUser");

        query.setString(0, (String)user.getId());

        List result = query.list();

        if (result == null) {

            log.info("Could not retrieve user.");

        } else {

            Iterator i = result.iterator();

            if (i.hasNext()) {

                u = (User) i.next();

            }

            if (i.hasNext()) {

                log.fatal("DB returned too many users.");

                u = null;

            }

        }

        session.close();

        return u;

    } 

    /**

     * Update a user profile.

     */

    public boolean updateUser(User user) {

        boolean status = false;

        User luser;

        String gender;

        if (user == null) {

            log.error("Cannot process invalid user.");

            return status;

        }

        gender = user.getGender();

        Session session = this.getSessionFactory().openSession();

        session.clear();

        session.beginTransaction();

        Query query = session.getNamedQuery("getUser");

        query.setString(0, (String)user.getId());

        List result = query.list();

        if (result == null) {

            log.error("Could not retrieve user.");

            session.close();

            return status;

        } 

        Iterator i = result.iterator();

        if (i.hasNext()) {

               luser = (User) i.next();

        } else {

               log.error("Could not retrieve user from list.");

               session.close();

               return status;

        }

        luser.setGender(gender);

        session.update(luser);

        session.getTransaction().commit();

        session.close();

        status = true;

        return status;

    }

    /**

     * Delete

     */

    public boolean deleteUser(User user) {

        boolean status = false;

        User luser;

        if (user == null) {

            log.error("Cannot process invalid user.");

            return status;

        }

        Session session = this.getSessionFactory().openSession();

        session.beginTransaction();

        Query query = session.getNamedQuery("getUser");

        query.setString(0, (String)user.getId());

        List result = query.list();

        if (result == null) {

            log.error("Could not retrieve user.");

            session.close();

            return status;

        } 

        Iterator i = result.iterator();

        if (i.hasNext()) {

               luser = (User) i.next();

        } else {

               log.error("Could not retrieve user from list.");

               session.close();

               return status;

        }

        log.info("Now deleting user...");

        session.delete(luser);

        session.getTransaction().commit();

        session.close();

        status = true;

        return status;

    }