Persistence Gone Awry with Ms Access

I was given a task to work on Ms Access database. I had to choose either JDBC, iBatis and Hibernate for the persistence mechanic. So I tried each of them to see which one is the best.

First, I worked with JDBC with Spring. Below is one of the query where I mapped the result to Country object.

public class CountryDaoJdbcImpl extends AbstractJdbcDao implements CountryDao {

    public List getAllCountries() {

        List countries = getJdbcTemplate().
                query(
                "SELECT * FROM Country_Code", new RowMapper() {

            public Object mapRow(ResultSet rs, int rowNum)
                    throws SQLException, DataAccessException {
                Country country = new Country();
                country.setCode(rs.getString(1));
                country.setName(rs.getString(2));
                country.setIsdCode(rs.getString(3));
                return country;
            }
        });

        return countries;
    }
}

It is easy to implement but the code is too verbose. Plus, if I have relationships between objects, the code will be more complicated and maintenance would be tough.

Then, I tried to implement iBatis with Spring. Below is the iBatis config for an object with a relationship with another object.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="General">

    <resultMap id="CategoryResultMap" class="Category" groupBy="categoryCode">
        <result property="categoryCode" column="categoryCode" />
        <result property="description" column="description" />
    </resultMap>    

    <resultMap id="SubCategoryResultMap" class="SubCategory" groupBy="subCategoryCode">
        <result property="subCategoryCode" column="subCategoryCode" />
        <result property="description" column="description" />
        <result property="categoryCode" column="categoryCode" />
        <result property="category"
                column="categoryCode"
                select="General.getCategoryByCategoryCode" />
    </resultMap>

    <select id="getAllCountries" resultClass="Country">
        SELECT
        country_code as code, name, isd_code as isdCode
        FROM
        country_code
    </select>   

    <select id="getAllSubCategories" resultMap="SubCategoryResultMap">
        SELECT
        sub_category_code as subCategoryCode,
        sub_category_name as description,
        category_code as categoryCode
        FROM
        sub_category_code
    </select>  

    <select id="getCategoryByCategoryCode" parameterClass="string"
            resultMap="CategoryResultMap" >
        SELECT
        category as categoryCode,
        description
        FROM
        category_code
        WHERE
        category = #value#
    </select>

</sqlMap>

Above is the iBatis config for a SubCategory object that has a Category object as a parent.

public class SubCategoryDaoIbatisImpl extends AbstractIbatisDao implements SubCategoryDao{

    public SubCategoryDaoIbatisImpl(DaoManager daoManager){
        super(daoManager);
    }

    @Override
    public List<SubCategory> getAllSubCategories() throws SQLException {
        return getSqlMapExecutor().queryForList("General.getAllSubCategories", null);
    }
}

Above is the query made via SqlMapExecutor, but this doesn’t work with MS Access. When I migrated the database to MySQL, it worked fine. So, iBatis is a NO too for Ms Access.

My last resort was Hibernate with Spring. Below is my code for the same object above.

...

/**
* @author Taufek
*/
@Entity
@Table(name = "sub_category_code")
public class SubCategory extends AbstractEntity {

private Category category;
private String subCategoryCode;
private String description;

@ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE}, fetch = FetchType.EAGER)
@JoinColumn(name = "category_code")
public Category getCategory() {
return category;
}

public void setCategory(Category category) {
this.category = category;
}

@Column(name = "sub_category_name")
public String getDescription() {
return description;
}

public void setDescription(String description) {
this.description = description;
}

@Id
@Column(name = "sub_category_code")
public String getSubCategoryCode() {
return subCategoryCode;
}

public void setSubCategoryCode(String subCategoryCode) {
this.subCategoryCode = subCategoryCode;
}
...
}

Above is the SubCategory object.

...
/**
* @author Taufek
*/
@Entity
@Table(name="category_code")
public class Category extends AbstractEntity{

private String categoryCode;
private String description;

@Id
@Column(name="category")
public String getCategoryCode() {
return categoryCode;
}

public void setCategoryCode(String code) {
this.categoryCode = code;
}

@Column(name="description")
public String getDescription() {
return description;
}

public void setDescription(String description) {
this.description = description;
}
...
}

Above is Category object which is the parent of SubCategory.

public class SubCategoryDaoHibernateImpl extends AbstractHibernateDao implements SubCategoryDao{

    public List getAllSubCategories() throws SQLException {
        return getHibernateTemplate().loadAll(SubCategory.class);
    }

}

Above is the query. It is simpler with Hibernate. I only need to put the Hibernate annotations on each property in an entity to map it to its counterpart in the database. Then, by using HibernateTemplate from Spring, just use one of the methods provided to make a query.

I thought, I was done with choosing persistence method, but whenever there is an object which has more than 1 relationship, the query will return error. I found out that whenever there is a query with a relationship, hibernate will construct a query consists of LEFT OUTER JOIN to combine between 2 tables. I was surprised to find out that MS Access query can only has 1 LEFT OUTER JOIN. If you run a query with more than 1 LEFT OUTER JOIN, it will return an error.

So now,I’m back to square one with ol’ JDBC.