Using HSQL database with Hibernate

Step-1

Add dependency inside the pom.xml

<!-- Dependency for hibernate -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>4.2.4.Final</version>
        </dependency>

        <!-- Dependency for HSQL Database -->
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>2.2.9</version>
        </dependency>
    </dependencies>



Step-2

<!-- Hibernate 4 with spring 3.x -->
    <bean id="librarySessionFactory"
        class="org.springframework.orm.hibernate4.LocalSessionFactoryBean" scope="singleton">
        <property name="dataSource" ref="libraryDataSource" />
        <property name="packagesToScan">
            <list>
                <value>com.usc.dao.entity</value>
            </list>
        </property>
        <property name="hibernateProperties">
            <props>
                 <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
                <prop key="hibernate.show_sql">true</prop>
                <!-- below key is important use this key = hibernate.hbm2ddl.auto not
                    hbm2ddl.auto -->
                <prop key="hibernate.generate_statistics">true</prop>
                <prop key="hibernate.hbm2ddl.auto">update</prop>
            </props>
        </property>
    </bean>


Step-3
Define the datasource



<context:property-placeholder location="/WEB-INF/context/persistence-mysql.properties" /> 
    <bean id="libraryDataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource" scope="singleton">
        <property name="driverClassName" value="${db.driverclassname}" />
        <property name="url" value="${db.url}" />
        <property name="username" value="${db.username}" />
        <property name="password" value="${db.password}" />
    </bean>

Step-4
"/WEB-INF/context/persistence-mysql.properties

################Comment###############
#db.driverclassname=com.mysql.jdbc.Driver
#db.url=jdbc:mysql://localhost:3306/library_db?createDatabaseIfNotExist=true
#db.username=root
#db.password=root
########Setting for HSQL##################
db.driverclassname=org.hsqldb.jdbc.JDBCDriver
db.url=jdbc:hsqldb:mem:library_db?createDatabaseIfNotExist=true
db.username=sa
db.password=
 

Step-5
Define Spring Transaction Manager

<!--
       @Transactional
     -->
    <tx:annotation-driven proxy-target-class="true"
        transaction-manager="transactionManager" />

    <bean id="transactionManager"
        class="org.springframework.orm.hibernate4.HibernateTransactionManager">
        <property name="sessionFactory" ref="librarySessionFactory"></property>
    </bean> 


Step-6
Applying transaction manager in dao layer 

/**
 *
 * @author usc
 *
 */
@Repository("IUserDao")
@Transactional(propagation=Propagation.REQUIRED)
public class IUserDao  extends AbstractDaoImpl<UserEntity,Integer> implements UserDao {

}


AbstractDaoImpl.java

/**
 * @author usc
 * @param <E>
 * @param <I>
 */
public abstract class AbstractDaoImpl<E, I extends Serializable> implements AbstractDao<E, I> {

    private Class<E> entityClass;

    protected AbstractDaoImpl(Class<E> entityClass) {
        this.entityClass = entityClass;
    }

    @Autowired
    @Qualifier("librarySessionFactory")
    private SessionFactory psessionFactory;

    public Session getCurrentSession() {
        return psessionFactory.getCurrentSession();
    }
}

Implementing Pagination with Spring JDBC

JSP Code :

     <span style="float: right;margin-right: 150px;">
      ${(fruitPaginationForm.currentPage-1)*fruitPaginationForm.recordsPerPage+1}

         -
       <c:if test="${(fruitPaginationForm.currentPage-1)*fruitPaginationForm.recordsPerPage+fruitPaginationForm.recordsPerPage gt fruitPaginationForm.noOfRecords}">
          ${fruitPaginationForm.noOfRecords}
       </c:if>
       
       <c:if test="${(fruitPaginationForm.currentPage-1)*fruitPaginationForm.recordsPerPage+fruitPaginationForm.recordsPerPage lt fruitPaginationForm.noOfRecords}">
       ${(fruitPaginationForm.currentPage-1)*fruitPaginationForm.recordsPerPage+fruitPaginationForm.recordsPerPage}
       </c:if>

       of  ${fruitPaginationForm.noOfRecords}  
     </span>


Code for Next and Previous Button on JSP

<c:if test="${fruitPaginationForm.currentPage != 1}">
            <td><a href="${pageContext.request.contextPath}/fruitWithPagination.do?page=${fruitPaginationForm.currentPage - 1}">Previous</a></td>
        </c:if>
        &nbsp;&nbsp; | &nbsp;&nbsp;
        <c:if test="${fruitPaginationForm.currentPage lt fruitPaginationForm.noOfPages}">
            <td><a href="${pageContext.request.contextPath}/fruitWithPagination.do?page=${fruitPaginationForm.currentPage + 1}">Next</a></td>
        </c:if>


Controller Code

@RequestMapping(value="fruitWithPagination.do",method=RequestMethod.GET)
public String fruitWithPagination(@RequestParam(value="page",required=false)String  page,Model model) {
int recordsPerPage=3;
int currentPage=0;
if(page==null) {
currentPage=1;
}else{
currentPage=Integer.parseInt(page);
}
FruitPaginationForm fruitPaginationForm = fruitService.findFruitsWithPagination( (currentPage-1)*recordsPerPage, recordsPerPage);
fruitPaginationForm.setCurrentPage(currentPage);
fruitPaginationForm.initPagination(); //initializing number of total pages......
model.addAttribute("fruitPaginationForm", fruitPaginationForm);
return "tfruits"; //give view name with out extension........../fruitSearch.jsp

}


public class FruitPaginationForm {

private int currentPage;
private int noOfRecords;
private List<FruitForm> fruitFormList;
private int recordsPerPage=3;
private int noOfPages;

public void initPagination(){
noOfPages=(int) Math.ceil(noOfRecords * 1.0 / recordsPerPage);

}
}



Service Layer Code

@Override
public FruitPaginationForm findFruitsWithPagination(int start,
int noOfRecords) {

FruitPaginationEntity fruitPaginationEntity=fruitDao.findFruitsWithPagination(start, noOfRecords);
FruitPaginationForm fruitPaginationForm=new FruitPaginationForm();
BeanUtils.copyProperties(fruitPaginationEntity, fruitPaginationForm);

List<FruitForm> fruitFormList=new ArrayList<FruitForm>();
List<FruitEntity> fruitEntities=fruitPaginationEntity.getFruitEntityList();
for(FruitEntity fe:fruitEntities){
  FruitForm form=new FruitForm();
 BeanUtils.copyProperties(fe, form);
 fruitFormList.add(form);
}
fruitPaginationForm.setFruitFormList(fruitFormList);
return fruitPaginationForm;

}


Dao Layer Code with MySQL Database

@Override

public FruitPaginationEntity findFruitsWithPagination(int start, int noOfRecords) {

        String query = "select  * from fruit_tbl limit " + start + ", " + noOfRecords;
  
         List<FruitEntity> fruitEntityList = (List<FruitEntity>) jdbcTemplate  .query(query,
                        new BeanPropertyRowMapper(FruitEntity.class));
      
        // To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the
        // SELECT statement, and then invoke FOUND_ROWS() afterward
   
         FruitPaginationEntity fruitPaginationEntity = new FruitPaginationEntity();
        
        int tnoOfRecords = jdbcTemplate.queryForInt("select count(*)  from fruit_tbl");
      
         fruitPaginationEntity.setNoOfRecords(tnoOfRecords);
        fruitPaginationEntity.setFruitEntityList(fruitEntityList);
        /* query a total number of rows from database. */
        return fruitPaginationEntity;

} 


 */
public class FruitPaginationEntity {

private int noOfRecords;
private List<FruitEntity> fruitEntityList;

public int getNoOfRecords() {
return noOfRecords;

}
}