Too many connections with Spring + MyBatis + MySQL + JDBC

Question

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.

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.

My scenario looks like this:

1: "select * from maincategory"

This query returns around 15 categories. I map the resultset into a List containing MainCategory elements.

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:

2: "select * from subcategories where id = ${id_from_main_category_actual_row}"

And it seems it hangs after the 9th or 10th subcategory selection.

Here is my transaction manager and sqlsessionfactory configuration:

And here is the configuration for a datasource with dbcp:

And my other try was with the bonecp:

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.

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.

UPDATE based on comments:

Here is my detailed code as requested:

web.xml:




    
    
        contextConfigLocation
        
            /WEB-INF/spring/root-context.xml
            /WEB-INF/spring/appServlet/ApplicationContext.xml
            /WEB-INF/spring/appServlet/ApplicationContext-security.xml
        
    

    
    
        org.springframework.web.context.ContextLoaderListener
    

    
    
        appServlet
        org.springframework.web.servlet.DispatcherServlet
        1
    

    
        appServlet
        /
    




    
    
      springSecurityFilterChain
      org.springframework.web.filter.DelegatingFilterProxy
    

    
      springSecurityFilterChain
      /*

root-context.xml:

ApplicationContext.xml:

ApplicationContext-security.xml:

mybatis.xml:

mbmapper-mainCategory-mapper.xml:

mbmapper-mainCategory-resultMap.xml:

mbmapper-subCategory-mapper.xml:

mbmapper-subCategory-resultMap.xml:

MainCategory.java:

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 subCategories;

    public List getSubCategories() {
        return subCategories;
    }

    public void setSubCategories(List 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;
    }

}

MainCategoryDao.java:

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 getAllMainCategories(){  
        SqlSession session = sessionFactory.openSession();
        List mainCategoriesFromDb = (List)(List) session.selectList("maincategory.getAllMainCategories");  
        return mainCategoriesFromDb;  

    }  

}

MainCategoryService.java:

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 getAllCategories() {

        return mainCategoryDao.getAllMainCategories();
    }

    public MainCategoryDao getMainCategoryDao() {
        return mainCategoryDao;
    }

    public void setMainCategoryDao(MainCategoryDao mainCategoryDao) {
        this.mainCategoryDao = mainCategoryDao;
    }

}

SubCategory.java:

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;
    }

}

SubCategoryDao.java:

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 getAllSubCategoriesForMainCategory(int id){  
        SqlSession session = sessionFactory.openSession();
        List subCategoriesFromDb = (List)(List) session.selectList("subcategory.getAllSubCategoriesForMainCategory", id);  
        return subCategoriesFromDb;  

    }  

}

SubCategoryService.java:

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 getAllSubCategoriesForMainCategory(int id){  
        SqlSession session = sessionFactory.openSession();
        List subCategoriesFromDb = (List)(List) session.selectList("subcategory.getAllSubCategoriesForMainCategory", id);  
        return subCategoriesFromDb;  

    }  

}

HomeController.java:

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 mainCategories;

     @Resource
     private SubCategoryService subCategoryService;
     private List 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("		- {}", subCategory.getName());
            }
        }
        model.addAttribute("mainCategories", mainCategories);

        return "index";
    }

}

Any suggestion are very welcomed.

kopytko · Accepted Answer

1. When creating SqlSessions manually you have to make sure that you close them after use.

Look for all places where you are opening a SqlSession:

SqlSession session = sessionFactory.openSession();

Make sure that you're closing the session after use. You should do that in try-finally block, like below:

SqlSession session = sqlSessionFactory.openSession(); 
try {
    // following 3 lines pseudocode for "doing some work"
    session.insert(...);
    session.update(...);
    session.delete(...);
    session.commit();
} finally {
    session.close();
}

More information on how to handle MyBatis SqlSessions can be found here: http://mybatis.github.io/mybatis-3/java-api.html#sqlSessions

2. You can use MyBatis-Spring to create and manage the lifecycle of SqlSessions.

Instead of injecting DAO's with SqlSessionFactory, you can ask spring to inject the SqlSessions directly. When you do this, Spring will manage the SqlSessions for you, so you will not have to open/close sessions by yourself. More information how to do this is here: http://mybatis.github.io/spring/sqlsession.html

Too many connections with Spring + MyBatis + MySQL + JDBC

Answers (1)

1. When creating SqlSessions manually you have to make sure that you close them after use.

2. You can use MyBatis-Spring to create and manage the lifecycle of SqlSessions.

Related Questions