title: Too many connections with Spring + MyBatis + MySQL + JDBC
body: <p>I am trying to work with spring, mybatis and mysql with jdbc. The only small problem I have after some queries, it throws an exception. The DB says: Too many connections.</p>
  
  <p>So what I am trying to do to prevent the too many connection issue is to set up a connection pool. I have tried dbcp and bonecp also but both have the same behaviour. When I reload my page it just keeps loading in the browser and after some debugging it seems it hangs after the 9th or 10th select.</p>
  
  <p>My scenario looks like this:</p>
  
  <p><strong>1: "select * from maincategory"</strong></p>
  
  <p>This query returns around 15 categories. I map the resultset into a List containing MainCategory elements.</p>
  
  <p>Then what I do is I walk through the MainCategory List and get the IDs from each MainCategory and then I do an other select to get their sub categories:</p>
  
  <p><strong>2: "select * from subcategories where id = ${id_from_main_category_actual_row}"</strong></p>
  
  <p>And it seems it hangs after the 9th or 10th subcategory selection.</p>
  
  <p>Here is my transaction manager and sqlsessionfactory configuration:</p>
  
  <pre><code>&lt;beans:bean id="transactionManager"  
      class="org.springframework.jdbc.datasource.DataSourceTransactionManager"&gt;  
      &lt;beans:property name="dataSource" ref="dataSource" /&gt;  
  &lt;/beans:bean&gt;  
  
  &lt;tx:annotation-driven transaction-manager="transactionManager" /&gt;
  
  &lt;beans:bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"&gt;  
      &lt;beans:property name="configLocation" value="WEB-INF/spring/myBatis/mybatis.xml" /&gt;  
      &lt;beans:property name="mapperLocations" value="WEB-INF/spring/myBatis/mbmapper-*.xml" /&gt;  
      &lt;beans:property name="dataSource" ref="dataSource" /&gt;  
  &lt;/beans:bean&gt;
  </code></pre>
  
  <p>And here is the configuration for a datasource with dbcp:</p>
  
  <pre><code>&lt;beans:bean id="dataSource"  class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"&gt;
      &lt;beans:property name="driverClassName" value="com.mysql.jdbc.Driver" /&gt;
      &lt;beans:property name="url" value="jdbc:mysql://localhost:3306/testdb" /&gt;
      &lt;beans:property name="username" value="root"/&gt;
      &lt;beans:property name="password" value=""/&gt;
  &lt;/beans:bean&gt;
  </code></pre>
  
  <p>And my other try was with the bonecp:</p>
  
  <pre><code>&lt;beans:bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close"&gt;
      &lt;beans:property name="driverClass" value="com.mysql.jdbc.Driver" /&gt;
      &lt;beans:property name="jdbcUrl" value="jdbc:mysql://localhost:3306/testdb" /&gt;
      &lt;beans:property name="username" value="root"/&gt;
      &lt;beans:property name="password" value=""/&gt;
      &lt;beans:property name="idleConnectionTestPeriodInSeconds" value="30"/&gt;
      &lt;beans:property name="idleMaxAgeInMinutes" value="1"/&gt;
      &lt;beans:property name="maxConnectionsPerPartition" value="7"/&gt;
      &lt;beans:property name="minConnectionsPerPartition" value="7"/&gt;
      &lt;beans:property name="partitionCount" value="1"/&gt;
      &lt;beans:property name="acquireIncrement" value="1"/&gt;
      &lt;beans:property name="acquireRetryAttempts" value="1"/&gt;
      &lt;beans:property name="acquireRetryDelayInMs" value="5000"/&gt;
      &lt;beans:property name="maxConnectionAgeInSeconds" value="0"/&gt;
      &lt;beans:property name="connectionTestStatement" value="SELECT 1"/&gt;
      &lt;beans:property name="statementsCacheSize" value="100"/&gt;
      &lt;beans:property name="disableConnectionTracking" value="true"/&gt;
  &lt;/beans:bean&gt;
  </code></pre>
  
  <p>As you can see I tried many optimization on this with the properties. I think regardless if the method querying for main and their subcategories is a good or wrong practise, its only 10 small queries with some data returned so it should work.</p>
  
  <p>If I use the "org.springframework.jdbc.datasource.DriverManagerDataSource" as the datasource, the db says the "too many connection" after some page refresh, while the tried connection pools just hang after the mentioned amount of query.</p>
  
  <p><strong>UPDATE</strong> based on comments:</p>
  
  <p>Here is my detailed code as requested:</p>
  
  <p><strong>web.xml:</strong></p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"&gt;
  
      &lt;!-- The definition of the Root Spring Container shared by all Servlets and Filters --&gt;
      &lt;context-param&gt;
          &lt;param-name&gt;contextConfigLocation&lt;/param-name&gt;
          &lt;param-value&gt;
              /WEB-INF/spring/root-context.xml
              /WEB-INF/spring/appServlet/ApplicationContext.xml
              /WEB-INF/spring/appServlet/ApplicationContext-security.xml
          &lt;/param-value&gt;
      &lt;/context-param&gt;
  
      &lt;!-- Creates the Spring Container shared by all Servlets and Filters --&gt;
      &lt;listener&gt;
          &lt;listener-class&gt;org.springframework.web.context.ContextLoaderListener&lt;/listener-class&gt;
      &lt;/listener&gt;
  
      &lt;!-- Processes application requests --&gt;
      &lt;servlet&gt;
          &lt;servlet-name&gt;appServlet&lt;/servlet-name&gt;
          &lt;servlet-class&gt;org.springframework.web.servlet.DispatcherServlet&lt;/servlet-class&gt;
          &lt;load-on-startup&gt;1&lt;/load-on-startup&gt;
      &lt;/servlet&gt;
  
      &lt;servlet-mapping&gt;
          &lt;servlet-name&gt;appServlet&lt;/servlet-name&gt;
          &lt;url-pattern&gt;/&lt;/url-pattern&gt;
      &lt;/servlet-mapping&gt;
  
  
  
  
      &lt;!-- Spring Security --&gt;
      &lt;filter&gt;
        &lt;filter-name&gt;springSecurityFilterChain&lt;/filter-name&gt;
        &lt;filter-class&gt;org.springframework.web.filter.DelegatingFilterProxy&lt;/filter-class&gt;
      &lt;/filter&gt;
  
      &lt;filter-mapping&gt;
        &lt;filter-name&gt;springSecurityFilterChain&lt;/filter-name&gt;
        &lt;url-pattern&gt;/*&lt;/url-pattern&gt;
      &lt;/filter-mapping&gt;
  
  &lt;/web-app&gt;
  </code></pre>
  
  <p><strong>root-context.xml:</strong></p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;beans xmlns="http://www.springframework.org/schema/beans"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd"&gt;
  
  &lt;/beans&gt;
  </code></pre>
  
  <p><strong>ApplicationContext.xml:</strong></p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;beans:beans xmlns="http://www.springframework.org/schema/mvc"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xmlns:beans="http://www.springframework.org/schema/beans"
      xmlns:context="http://www.springframework.org/schema/context"
      xmlns:tx="http://www.springframework.org/schema/tx"
      xsi:schemaLocation="http://www.springframework.org/schema/mvc
          http://www.springframework.org/schema/mvc/spring-mvc.xsd
          http://www.springframework.org/schema/beans
          http://www.springframework.org/schema/beans/spring-beans.xsd
          http://www.springframework.org/schema/context
          http://www.springframework.org/schema/context/spring-context.xsd
          http://www.springframework.org/schema/tx   
          http://www.springframework.org/schema/tx/spring-tx-2.5.xsd"&gt;
  
      &lt;beans:bean class="org.springframework.aop.framework.autoproxy.DefaultAdvisorAutoProxyCreator" /&gt;
  
      &lt;!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure --&gt;
      &lt;context:annotation-config /&gt;  
  
      &lt;!-- Enables the Spring MVC @Controller programming model --&gt;
      &lt;annotation-driven /&gt;
  
      &lt;!-- Handles HTTP GET requests for /resources/** by efficiently serving up static resources in the ${webappRoot}/resources directory --&gt;
      &lt;resources mapping="/resources/**" location="/resources/" /&gt;
  
      &lt;!-- Resolves views selected for rendering by @Controllers to .jsp resources in the /WEB-INF/views directory --&gt;
      &lt;!-- 
      &lt;beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"&gt;
          &lt;beans:property name="prefix" value="/WEB-INF/views/" /&gt;
          &lt;beans:property name="suffix" value=".jsp" /&gt;
      &lt;/beans:bean&gt;
      --&gt;
  
      &lt;context:component-scan base-package="com.test.packit" /&gt;
  
      &lt;!-- Thymeleaf Template &amp; View Resolver configurations --&gt;
      &lt;beans:bean id="templateResolver"
            class="org.thymeleaf.templateresolver.ServletContextTemplateResolver"&gt;
        &lt;beans:property name="prefix" value="/WEB-INF/templates/" /&gt;
        &lt;beans:property name="suffix" value=".html" /&gt;
        &lt;beans:property name="templateMode" value="HTML5" /&gt;
        &lt;beans:property name="cacheable" value="false" /&gt;
      &lt;/beans:bean&gt;
  
      &lt;beans:bean id="templateEngine" class="org.thymeleaf.spring4.SpringTemplateEngine"&gt;
        &lt;beans:property name="templateResolver" ref="templateResolver" /&gt;
        &lt;beans:property name="additionalDialects"&gt;
               &lt;beans:set&gt;
                   &lt;beans:bean class="org.thymeleaf.extras.springsecurity3.dialect.SpringSecurityDialect" /&gt;
               &lt;/beans:set&gt;
           &lt;/beans:property&gt;
      &lt;/beans:bean&gt;
  
      &lt;beans:bean class="org.thymeleaf.spring4.view.ThymeleafViewResolver"&gt;
        &lt;beans:property name="templateEngine" ref="templateEngine" /&gt;
      &lt;/beans:bean&gt; 
  
      &lt;beans:bean id="messageSource" class="org.springframework.context.support.ResourceBundleMessageSource"&gt;
          &lt;beans:property name="basename" value="localization/general/messages" /&gt;
      &lt;/beans:bean&gt;
  
      &lt;beans:bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close"&gt;
          &lt;beans:property name="driverClass" value="com.mysql.jdbc.Driver" /&gt;
          &lt;beans:property name="jdbcUrl" value="jdbc:mysql://localhost:3306/testdb" /&gt;
          &lt;beans:property name="username" value="root"/&gt;
          &lt;beans:property name="password" value=""/&gt;
          &lt;beans:property name="idleConnectionTestPeriodInSeconds" value="30"/&gt;
          &lt;beans:property name="idleMaxAgeInMinutes" value="1"/&gt;
          &lt;beans:property name="maxConnectionsPerPartition" value="7"/&gt;
          &lt;beans:property name="minConnectionsPerPartition" value="7"/&gt;
          &lt;beans:property name="partitionCount" value="1"/&gt;
          &lt;beans:property name="acquireIncrement" value="1"/&gt;
          &lt;beans:property name="acquireRetryAttempts" value="1"/&gt;
          &lt;beans:property name="acquireRetryDelayInMs" value="5000"/&gt;
          &lt;beans:property name="maxConnectionAgeInSeconds" value="0"/&gt;
          &lt;beans:property name="connectionTestStatement" value="SELECT 1"/&gt;
          &lt;beans:property name="statementsCacheSize" value="100"/&gt;
          &lt;beans:property name="disableConnectionTracking" value="true"/&gt;
      &lt;/beans:bean&gt;
  
  
      &lt;!--
      &lt;beans:bean id="dataSource"  class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"&gt;
          &lt;beans:property name="driverClassName" value="com.mysql.jdbc.Driver" /&gt;
          &lt;beans:property name="url" value="jdbc:mysql://localhost:3306/testdb" /&gt;
          &lt;beans:property name="username" value="root"/&gt;
          &lt;beans:property name="password" value=""/&gt;
      &lt;/beans:bean&gt;
      --&gt;
  
      &lt;!-- Original data source without connection pooling --&gt;
      &lt;!--
      &lt;beans:bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"&gt;  
          &lt;beans:property name="driverClassName" value="com.mysql.jdbc.Driver" /&gt;  
          &lt;beans:property name="url" value="jdbc:mysql://localhost:3306/testdb" /&gt;  
          &lt;beans:property name="username" value="root" /&gt;  
          &lt;beans:property name="password" value="" /&gt;  
      &lt;/beans:bean&gt; 
      --&gt;
  
      &lt;beans:bean id="transactionManager"  
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager"&gt;  
          &lt;beans:property name="dataSource" ref="dataSource" /&gt;  
      &lt;/beans:bean&gt;  
  
      &lt;tx:annotation-driven transaction-manager="transactionManager" /&gt;
  
      &lt;beans:bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"&gt;  
          &lt;beans:property name="configLocation" value="WEB-INF/spring/myBatis/mybatis.xml" /&gt;  
          &lt;beans:property name="mapperLocations" value="WEB-INF/spring/myBatis/mbmapper-*.xml" /&gt;  
          &lt;beans:property name="dataSource" ref="dataSource" /&gt;  
      &lt;/beans:bean&gt;  
  
      &lt;!-- MAPPER: ACCOUNT --&gt;
      &lt;beans:bean id="accountDao" class="com.test.packit.dao.AccountDao"&gt;  
          &lt;beans:property name="sessionFactory" ref="sqlSessionFactory" /&gt;  
      &lt;/beans:bean&gt;  
      &lt;beans:bean id="accountService" class="com.test.packit.service.AccountService"&gt;  
          &lt;beans:property name="accountDao" ref="accountDao" /&gt;  
      &lt;/beans:bean&gt;  
  
      &lt;!-- MAPPER: MAIN_CATEGORY --&gt;
      &lt;beans:bean id="mainCategoryDao" class="com.test.packit.dao.MainCategoryDao"&gt;  
          &lt;beans:property name="sessionFactory" ref="sqlSessionFactory" /&gt;  
      &lt;/beans:bean&gt;
      &lt;beans:bean id="mainCategoryService" class="com.test.packit.service.MainCategoryService"&gt;  
          &lt;beans:property name="mainCategoryDao" ref="mainCategoryDao" /&gt;  
      &lt;/beans:bean&gt;   
  
  
      &lt;!-- MAPPER: SUB_CATEGORY --&gt;
      &lt;beans:bean id="subCategoryDao" class="com.test.packit.dao.SubCategoryDao"&gt;  
          &lt;beans:property name="sessionFactory" ref="sqlSessionFactory" /&gt;  
      &lt;/beans:bean&gt;
      &lt;beans:bean id="subCategoryService" class="com.test.packit.service.SubCategoryService"&gt;  
          &lt;beans:property name="subCategoryDao" ref="subCategoryDao" /&gt;  
      &lt;/beans:bean&gt;   
  &lt;/beans:beans&gt;
  </code></pre>
  
  <p><strong>ApplicationContext-security.xml:</strong></p>
  
  <pre><code>&lt;beans:beans xmlns="http://www.springframework.org/schema/security"
    xmlns:beans="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
             http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
             http://www.springframework.org/schema/security
             http://www.springframework.org/schema/security/spring-security.xsd"&gt;
  
      &lt;http auto-config="true" use-expressions="true"&gt;
          &lt;intercept-url pattern="/admin**" access="ROLE_ADMIN" /&gt;
          &lt;intercept-url pattern="/admin.jsp" access="ROLE_ADMIN" /&gt;  
          &lt;intercept-url pattern="/user.jsp" access="ROLE_USER" /&gt;
  
          &lt;session-management session-fixation-protection="none"&gt;  
              &lt;concurrency-control /&gt;  
          &lt;/session-management&gt;  
  
          &lt;form-login 
              login-page="/login"
              login-processing-url="/letmein"
              default-target-url="/"
              authentication-failure-url="/login-error"
              username-parameter="bm_username"
              password-parameter="bm_password" /&gt;
          &lt;logout logout-url="/logout"/&gt;
          &lt;!-- enable csrf protection --&gt;
          &lt;csrf/&gt;
      &lt;/http&gt;
  
      &lt;authentication-manager&gt;
          &lt;authentication-provider&gt;
  
          &lt;jdbc-user-service data-source-ref="dataSource"  
                  users-by-username-query="SELECT username, password, enabled  
                                          FROM bm_users  
                                          WHERE username=?"
                  authorities-by-username-query="SELECT username, role  
                                                FROM bm_roles  
                                                WHERE username=?" /&gt;  
              &lt;password-encoder hash="bcrypt"/&gt;
        &lt;/authentication-provider&gt;
      &lt;/authentication-manager&gt;  
  &lt;/beans:beans&gt;
  </code></pre>
  
  <p><strong>mybatis.xml:</strong></p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;  
  &lt;!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"&gt;  
  &lt;configuration&gt;  
  
  &lt;/configuration&gt;  
  </code></pre>
  
  <p><strong>mbmapper-mainCategory-mapper.xml:</strong></p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;  
  &lt;!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"   
   "http://mybatis.org/dtd/mybatis-3-mapper.dtd"&gt;  
  
  &lt;mapper namespace="maincategory"&gt;
      &lt;cache type="org.mybatis.caches.hazelcast.LoggingHazelcastCache"/&gt;
  
      &lt;select id="getAllMainCategories" resultType="list" resultMap="mainCategoryMap.mainCategoryResultMap"&gt;  
      SELECT * FROM packit.bm_maincategory;
      &lt;/select&gt;
  &lt;/mapper&gt;
  </code></pre>
  
  <p><strong>mbmapper-mainCategory-resultMap.xml:</strong></p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;  
  &lt;!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"&gt;  
  
  &lt;mapper namespace="mainCategoryMap"&gt;  
      &lt;resultMap type="com.test.packit.model.MainCategory" id="mainCategoryResultMap"&gt;  
         &lt;id property="id" column="id"/&gt;
         &lt;result property="name" column="name"/&gt;  
      &lt;/resultMap&gt;  
  &lt;/mapper&gt;
  </code></pre>
  
  <p><strong>mbmapper-subCategory-mapper.xml:</strong></p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;  
  &lt;!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"   
   "http://mybatis.org/dtd/mybatis-3-mapper.dtd"&gt;  
  
  &lt;mapper namespace="subcategory"&gt;
      &lt;cache type="org.mybatis.caches.hazelcast.LoggingHazelcastCache"/&gt;
  
      &lt;select id="getAllSubCategoriesForMainCategory" parameterType="int" resultType="list" resultMap="subCategoryMap.subCategoryResultMap"&gt;  
      SELECT * FROM packit.bm_subcategory WHERE maincategory_id = #{id};
      &lt;/select&gt;
  &lt;/mapper&gt;
  </code></pre>
  
  <p><strong>mbmapper-subCategory-resultMap.xml:</strong></p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;  
  &lt;!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"&gt;  
  
  &lt;mapper namespace="subCategoryMap"&gt;  
      &lt;resultMap type="com.test.packit.model.SubCategory" id="subCategoryResultMap"&gt;  
         &lt;id property="id" column="id"/&gt;
         &lt;result property="name" column="name"/&gt;  
      &lt;/resultMap&gt;  
  &lt;/mapper&gt;
  </code></pre>
  
  <p><strong>MainCategory.java:</strong></p>
  
  <pre><code>package com.test.packit.model;
  
  import java.io.Serializable;
  import java.util.List;
  
  public class MainCategory implements Serializable {
  
      private static final long serialVersionUID = -7970848646314840509L;
  
      private Integer id;
      private String name;
      private List&lt;SubCategory&gt; subCategories;
  
      public List&lt;SubCategory&gt; getSubCategories() {
          return subCategories;
      }
  
      public void setSubCategories(List&lt;SubCategory&gt; subCategories) {
          this.subCategories = subCategories;
      }
  
      public MainCategory() {
          super();
      }
  
      public Integer getId() {
          return id;
      }
  
      public void setId(Integer id) {
          this.id = id;
      }
  
      public String getName() {
          return name;
      }
  
      public void setName(String name) {
          this.name = name;
      }
  
  }
  </code></pre>
  
  <p><strong>MainCategoryDao.java:</strong></p>
  
  <pre><code>package com.test.packit.dao;
  
  import java.util.List;  
  
  import javax.annotation.Resource;  
  
  import org.apache.ibatis.session.SqlSession;  
  import org.apache.ibatis.session.SqlSessionFactory;  
  import org.springframework.stereotype.Repository;  
  
  import com.test.packit.model.MainCategory;
  
  @Repository  
  public class MainCategoryDao {  
  
      @Resource  
      private SqlSessionFactory sessionFactory;  
  
      public SqlSessionFactory getSessionFactory() {  
          return sessionFactory;  
      }  
  
      public void setSessionFactory(SqlSessionFactory sessionFactory) {  
          this.sessionFactory = sessionFactory;  
      }  
  
      @SuppressWarnings("unchecked")  
      public List&lt;MainCategory&gt; getAllMainCategories(){  
          SqlSession session = sessionFactory.openSession();
          List&lt;MainCategory&gt; mainCategoriesFromDb = (List&lt;MainCategory&gt;)(List&lt;?&gt;) session.selectList("maincategory.getAllMainCategories");  
          return mainCategoriesFromDb;  
  
      }  
  
  }
  </code></pre>
  
  <p><strong>MainCategoryService.java:</strong></p>
  
  <pre><code>package com.test.packit.service;
  
  import java.util.List;
  
  import javax.annotation.Resource;
  
  import org.springframework.stereotype.Repository;
  
  import com.test.packit.dao.MainCategoryDao;
  import com.test.packit.model.MainCategory;
  
  @Repository
  public class MainCategoryService {
      @Resource
      private MainCategoryDao mainCategoryDao;
  
      public List&lt;MainCategory&gt; getAllCategories() {
  
          return mainCategoryDao.getAllMainCategories();
      }
  
      public MainCategoryDao getMainCategoryDao() {
          return mainCategoryDao;
      }
  
      public void setMainCategoryDao(MainCategoryDao mainCategoryDao) {
          this.mainCategoryDao = mainCategoryDao;
      }
  
  }
  </code></pre>
  
  <p><strong>SubCategory.java:</strong></p>
  
  <pre><code>package com.test.packit.model;
  
  import java.io.Serializable;
  
  public class SubCategory implements Serializable {
  
      private static final long serialVersionUID = -7970848646314840509L;
  
      private Integer id;
      private Integer mainCategoryId;
      private String name;
  
      public Integer getMainCategoryId() {
          return mainCategoryId;
      }
  
      public void setMainCategoryId(Integer mainCategoryId) {
          this.mainCategoryId = mainCategoryId;
      }
  
      public SubCategory() {
          super();
      }
  
      public Integer getId() {
          return id;
      }
  
      public void setId(Integer id) {
          this.id = id;
      }
  
      public String getName() {
          return name;
      }
  
      public void setName(String name) {
          this.name = name;
      }
  
  }
  </code></pre>
  
  <p><strong>SubCategoryDao.java:</strong></p>
  
  <pre><code>package com.test.packit.dao;
  
  import java.util.List;  
  
  import javax.annotation.Resource;  
  
  import org.apache.ibatis.session.SqlSession;  
  import org.apache.ibatis.session.SqlSessionFactory;  
  import org.springframework.stereotype.Repository;  
  
  import com.test.packit.model.SubCategory;
  
  @Repository  
  public class SubCategoryDao {  
  
      @Resource  
      private SqlSessionFactory sessionFactory;  
  
      public SqlSessionFactory getSessionFactory() {  
          return sessionFactory;  
      }  
  
      public void setSessionFactory(SqlSessionFactory sessionFactory) {  
          this.sessionFactory = sessionFactory;  
      }  
  
      @SuppressWarnings("unchecked")  
      public List&lt;SubCategory&gt; getAllSubCategoriesForMainCategory(int id){  
          SqlSession session = sessionFactory.openSession();
          List&lt;SubCategory&gt; subCategoriesFromDb = (List&lt;SubCategory&gt;)(List&lt;?&gt;) session.selectList("subcategory.getAllSubCategoriesForMainCategory", id);  
          return subCategoriesFromDb;  
  
      }  
  
  }
  </code></pre>
  
  <p><strong>SubCategoryService.java:</strong></p>
  
  <pre><code>package com.test.packit.dao;
  
  import java.util.List;  
  
  import javax.annotation.Resource;  
  
  import org.apache.ibatis.session.SqlSession;  
  import org.apache.ibatis.session.SqlSessionFactory;  
  import org.springframework.stereotype.Repository;  
  
  import com.test.packit.model.SubCategory;
  
  @Repository  
  public class SubCategoryDao {  
  
      @Resource  
      private SqlSessionFactory sessionFactory;  
  
      public SqlSessionFactory getSessionFactory() {  
          return sessionFactory;  
      }  
  
      public void setSessionFactory(SqlSessionFactory sessionFactory) {  
          this.sessionFactory = sessionFactory;  
      }  
  
      @SuppressWarnings("unchecked")  
      public List&lt;SubCategory&gt; getAllSubCategoriesForMainCategory(int id){  
          SqlSession session = sessionFactory.openSession();
          List&lt;SubCategory&gt; subCategoriesFromDb = (List&lt;SubCategory&gt;)(List&lt;?&gt;) session.selectList("subcategory.getAllSubCategoriesForMainCategory", id);  
          return subCategoriesFromDb;  
  
      }  
  
  }
  </code></pre>
  
  <p><strong>HomeController.java:</strong></p>
  
  <pre><code>package com.test.packit;
  
  import com.test.packit.authentication.AuthenticatorController;
  import com.test.packit.model.MainCategory;
  import com.test.packit.model.SubCategory;
  import com.test.packit.service.MainCategoryService;
  import com.test.packit.service.SubCategoryService;
  
  import java.util.List;
  import java.util.Locale;
  
  import javax.annotation.Resource;
  
  import org.slf4j.Logger;
  import org.slf4j.LoggerFactory;
  import org.springframework.stereotype.Controller;
  import org.springframework.ui.Model;
  import org.springframework.web.bind.annotation.RequestMapping;
  import org.springframework.web.bind.annotation.RequestMethod;
  
  @Controller
  public class HomeController {
  
       @Resource
       private MainCategoryService mainCategoryService;
       private List&lt;MainCategory&gt; mainCategories;
  
       @Resource
       private SubCategoryService subCategoryService;
       private List&lt;SubCategory&gt; subCategories;
  
      private static final Logger logger = LoggerFactory.getLogger(HomeController.class);
  
      @RequestMapping(value = "/", method = RequestMethod.GET)
      public String index(Locale locale, Model model) {
  
          // Get all main categories
          mainCategories = mainCategoryService.getAllCategories();
          for(MainCategory mainCategory : mainCategories) {
              logger.info("Main Category: {}", mainCategory.getName());
  
              // Get all sub categories
              subCategories = subCategoryService.getAllSubCategoriesForMainCategory(mainCategory.getId());
              mainCategory.setSubCategories(subCategories);
              for(SubCategory subCategory : subCategories) {
                  logger.info("\t\t- {}", subCategory.getName());
              }
          }
          model.addAttribute("mainCategories", mainCategories);
  
          return "index";
      }
  
  }
  </code></pre>
  
  <p>Any suggestion are very welcomed.</p>