Java Spring + MyBatis + MySQL + JDBC 连接过多

Question

提问by Zoltan Szabo

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.

我正在尝试使用 jdbc 使用 spring、mybatis 和 mysql。经过一些查询后，我遇到的唯一小问题是抛出异常。DB 说：连接太多。

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.

所以我试图做的是防止连接过多的问题是建立一个连接池。我也尝试过 dbcp 和 bonecp，但两者都有相同的行为。当我重新加载我的页面时，它只会在浏览器中继续加载，经过一些调试后，它似乎在第 9 次或第 10 次选择后挂起。

My scenario looks like this:

我的场景是这样的：

1: "select * from maincategory"

1：“从主类别中选择*”

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

此查询返回大约 15 个类别。我将结果集映射到一个包含 MainCategory 元素的列表中。

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:

然后我要做的是遍历 MainCategory 列表并从每个 MainCategory 中获取 ID，然后我执行另一个选择以获取它们的子类别：

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

2：“从子类别中选择 * 其中 id = ${id_from_main_category_actual_row}”

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

它似乎在第 9 个或第 10 个子类别选择之后挂起。

Here is my transaction manager and sqlsessionfactory configuration:

这是我的事务管理器和 sqlsessionfactory 配置：

<beans:bean id="transactionManager"  
    class="org.springframework.jdbc.datasource.DataSourceTransactionManager">  
    <beans:property name="dataSource" ref="dataSource" />  
</beans:bean>  

<tx:annotation-driven transaction-manager="transactionManager" />

<beans:bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">  
    <beans:property name="configLocation" value="WEB-INF/spring/myBatis/mybatis.xml" />  
    <beans:property name="mapperLocations" value="WEB-INF/spring/myBatis/mbmapper-*.xml" />  
    <beans:property name="dataSource" ref="dataSource" />  
</beans:bean>

And here is the configuration for a datasource with dbcp:

这是带有 dbcp 的数据源的配置：

<beans:bean id="dataSource"  class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <beans:property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <beans:property name="url" value="jdbc:mysql://localhost:3306/testdb" />
    <beans:property name="username" value="root"/>
    <beans:property name="password" value=""/>
</beans:bean>

And my other try was with the bonecp:

我的另一个尝试是使用 bonecp：

<beans:bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
    <beans:property name="driverClass" value="com.mysql.jdbc.Driver" />
    <beans:property name="jdbcUrl" value="jdbc:mysql://localhost:3306/testdb" />
    <beans:property name="username" value="root"/>
    <beans:property name="password" value=""/>
    <beans:property name="idleConnectionTestPeriodInSeconds" value="30"/>
    <beans:property name="idleMaxAgeInMinutes" value="1"/>
    <beans:property name="maxConnectionsPerPartition" value="7"/>
    <beans:property name="minConnectionsPerPartition" value="7"/>
    <beans:property name="partitionCount" value="1"/>
    <beans:property name="acquireIncrement" value="1"/>
    <beans:property name="acquireRetryAttempts" value="1"/>
    <beans:property name="acquireRetryDelayInMs" value="5000"/>
    <beans:property name="maxConnectionAgeInSeconds" value="0"/>
    <beans:property name="connectionTestStatement" value="SELECT 1"/>
    <beans:property name="statementsCacheSize" value="100"/>
    <beans:property name="disableConnectionTracking" value="true"/>
</beans:bean>

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.

正如你所看到的，我尝试了很多关于属性的优化。我认为无论查询 main 及其子类别的方法是好的还是错误的做法，它只有 10 个小查询并返回了一些数据，因此它应该可以工作。

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.

如果我使用“org.springframework.jdbc.datasource.DriverManagerDataSource”作为数据源，数据库会在一些页面刷新后说“连接太多”，而尝试过的连接池只是在提到的查询量后挂起。

UPDATEbased on comments:

根据评论更新：

Here is my detailed code as requested:

这是我要求的详细代码：

web.xml:

网页.xml：

<?xml version="1.0" encoding="UTF-8"?>
<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">

    <!-- The definition of the Root Spring Container shared by all Servlets and Filters -->
    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>
            /WEB-INF/spring/root-context.xml
            /WEB-INF/spring/appServlet/ApplicationContext.xml
            /WEB-INF/spring/appServlet/ApplicationContext-security.xml
        </param-value>
    </context-param>

    <!-- Creates the Spring Container shared by all Servlets and Filters -->
    <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>

    <!-- Processes application requests -->
    <servlet>
        <servlet-name>appServlet</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <load-on-startup>1</load-on-startup>
    </servlet>

    <servlet-mapping>
        <servlet-name>appServlet</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>




    <!-- Spring Security -->
    <filter>
      <filter-name>springSecurityFilterChain</filter-name>
      <filter-class>org.springframework.web.filter.DelegatingFilterProxy</filter-class>
    </filter>

    <filter-mapping>
      <filter-name>springSecurityFilterChain</filter-name>
      <url-pattern>/*</url-pattern>
    </filter-mapping>

</web-app>

root-context.xml:

根上下文.xml：

<?xml version="1.0" encoding="UTF-8"?>
<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">

</beans>

ApplicationContext.xml:

应用上下文.xml：

<?xml version="1.0" encoding="UTF-8"?>
<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">

    <beans:bean class="org.springframework.aop.framework.autoproxy.DefaultAdvisorAutoProxyCreator" />

    <!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->
    <context:annotation-config />  

    <!-- Enables the Spring MVC @Controller programming model -->
    <annotation-driven />

    <!-- Handles HTTP GET requests for /resources/** by efficiently serving up static resources in the ${webappRoot}/resources directory -->
    <resources mapping="/resources/**" location="/resources/" />

    <!-- Resolves views selected for rendering by @Controllers to .jsp resources in the /WEB-INF/views directory -->
    <!-- 
    <beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <beans:property name="prefix" value="/WEB-INF/views/" />
        <beans:property name="suffix" value=".jsp" />
    </beans:bean>
    -->

    <context:component-scan base-package="com.test.packit" />

    <!-- Thymeleaf Template & View Resolver configurations -->
    <beans:bean id="templateResolver"
          class="org.thymeleaf.templateresolver.ServletContextTemplateResolver">
      <beans:property name="prefix" value="/WEB-INF/templates/" />
      <beans:property name="suffix" value=".html" />
      <beans:property name="templateMode" value="HTML5" />
      <beans:property name="cacheable" value="false" />
    </beans:bean>

    <beans:bean id="templateEngine" class="org.thymeleaf.spring4.SpringTemplateEngine">
      <beans:property name="templateResolver" ref="templateResolver" />
      <beans:property name="additionalDialects">
             <beans:set>
                 <beans:bean class="org.thymeleaf.extras.springsecurity3.dialect.SpringSecurityDialect" />
             </beans:set>
         </beans:property>
    </beans:bean>

    <beans:bean class="org.thymeleaf.spring4.view.ThymeleafViewResolver">
      <beans:property name="templateEngine" ref="templateEngine" />
    </beans:bean> 

    <beans:bean id="messageSource" class="org.springframework.context.support.ResourceBundleMessageSource">
        <beans:property name="basename" value="localization/general/messages" />
    </beans:bean>

    <beans:bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
        <beans:property name="driverClass" value="com.mysql.jdbc.Driver" />
        <beans:property name="jdbcUrl" value="jdbc:mysql://localhost:3306/testdb" />
        <beans:property name="username" value="root"/>
        <beans:property name="password" value=""/>
        <beans:property name="idleConnectionTestPeriodInSeconds" value="30"/>
        <beans:property name="idleMaxAgeInMinutes" value="1"/>
        <beans:property name="maxConnectionsPerPartition" value="7"/>
        <beans:property name="minConnectionsPerPartition" value="7"/>
        <beans:property name="partitionCount" value="1"/>
        <beans:property name="acquireIncrement" value="1"/>
        <beans:property name="acquireRetryAttempts" value="1"/>
        <beans:property name="acquireRetryDelayInMs" value="5000"/>
        <beans:property name="maxConnectionAgeInSeconds" value="0"/>
        <beans:property name="connectionTestStatement" value="SELECT 1"/>
        <beans:property name="statementsCacheSize" value="100"/>
        <beans:property name="disableConnectionTracking" value="true"/>
    </beans:bean>


    <!--
    <beans:bean id="dataSource"  class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <beans:property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <beans:property name="url" value="jdbc:mysql://localhost:3306/testdb" />
        <beans:property name="username" value="root"/>
        <beans:property name="password" value=""/>
    </beans:bean>
    -->

    <!-- Original data source without connection pooling -->
    <!--
    <beans:bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">  
        <beans:property name="driverClassName" value="com.mysql.jdbc.Driver" />  
        <beans:property name="url" value="jdbc:mysql://localhost:3306/testdb" />  
        <beans:property name="username" value="root" />  
        <beans:property name="password" value="" />  
    </beans:bean> 
    -->

    <beans:bean id="transactionManager"  
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">  
        <beans:property name="dataSource" ref="dataSource" />  
    </beans:bean>  

    <tx:annotation-driven transaction-manager="transactionManager" />

    <beans:bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">  
        <beans:property name="configLocation" value="WEB-INF/spring/myBatis/mybatis.xml" />  
        <beans:property name="mapperLocations" value="WEB-INF/spring/myBatis/mbmapper-*.xml" />  
        <beans:property name="dataSource" ref="dataSource" />  
    </beans:bean>  

    <!-- MAPPER: ACCOUNT -->
    <beans:bean id="accountDao" class="com.test.packit.dao.AccountDao">  
        <beans:property name="sessionFactory" ref="sqlSessionFactory" />  
    </beans:bean>  
    <beans:bean id="accountService" class="com.test.packit.service.AccountService">  
        <beans:property name="accountDao" ref="accountDao" />  
    </beans:bean>  

    <!-- MAPPER: MAIN_CATEGORY -->
    <beans:bean id="mainCategoryDao" class="com.test.packit.dao.MainCategoryDao">  
        <beans:property name="sessionFactory" ref="sqlSessionFactory" />  
    </beans:bean>
    <beans:bean id="mainCategoryService" class="com.test.packit.service.MainCategoryService">  
        <beans:property name="mainCategoryDao" ref="mainCategoryDao" />  
    </beans:bean>   


    <!-- MAPPER: SUB_CATEGORY -->
    <beans:bean id="subCategoryDao" class="com.test.packit.dao.SubCategoryDao">  
        <beans:property name="sessionFactory" ref="sqlSessionFactory" />  
    </beans:bean>
    <beans:bean id="subCategoryService" class="com.test.packit.service.SubCategoryService">  
        <beans:property name="subCategoryDao" ref="subCategoryDao" />  
    </beans:bean>   
</beans:beans>

ApplicationContext-security.xml:

ApplicationContext-security.xml：

<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">

    <http auto-config="true" use-expressions="true">
        <intercept-url pattern="/admin**" access="ROLE_ADMIN" />
        <intercept-url pattern="/admin.jsp" access="ROLE_ADMIN" />  
        <intercept-url pattern="/user.jsp" access="ROLE_USER" />

        <session-management session-fixation-protection="none">  
            <concurrency-control />  
        </session-management>  

        <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" />
        <logout logout-url="/logout"/>
        <!-- enable csrf protection -->
        <csrf/>
    </http>

    <authentication-manager>
        <authentication-provider>

        <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=?" />  
            <password-encoder hash="bcrypt"/>
      </authentication-provider>
    </authentication-manager>  
</beans:beans>

mybatis.xml:

<?xml version="1.0" encoding="UTF-8"?>  
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">  
<configuration>  

</configuration>

mbmapper-mainCategory-mapper.xml:

mbmapper-mainCategory-mapper.xml：

<?xml version="1.0" encoding="UTF-8"?>  
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"   
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">  

<mapper namespace="maincategory">
    <cache type="org.mybatis.caches.hazelcast.LoggingHazelcastCache"/>

    <select id="getAllMainCategories" resultType="list" resultMap="mainCategoryMap.mainCategoryResultMap">  
    SELECT * FROM packit.bm_maincategory;
    </select>
</mapper>

mbmapper-mainCategory-resultMap.xml:

mbmapper-mainCategory-resultMap.xml：

<?xml version="1.0" encoding="UTF-8"?>  
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">  

<mapper namespace="mainCategoryMap">  
    <resultMap type="com.test.packit.model.MainCategory" id="mainCategoryResultMap">  
       <id property="id" column="id"/>
       <result property="name" column="name"/>  
    </resultMap>  
</mapper>

mbmapper-subCategory-mapper.xml:

mbmapper-subCategory-mapper.xml：

<?xml version="1.0" encoding="UTF-8"?>  
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"   
 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">  

<mapper namespace="subcategory">
    <cache type="org.mybatis.caches.hazelcast.LoggingHazelcastCache"/>

    <select id="getAllSubCategoriesForMainCategory" parameterType="int" resultType="list" resultMap="subCategoryMap.subCategoryResultMap">  
    SELECT * FROM packit.bm_subcategory WHERE maincategory_id = #{id};
    </select>
</mapper>

mbmapper-subCategory-resultMap.xml:

mbmapper-subCategory-resultMap.xml：

<?xml version="1.0" encoding="UTF-8"?>  
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">  

<mapper namespace="subCategoryMap">  
    <resultMap type="com.test.packit.model.SubCategory" id="subCategoryResultMap">  
       <id property="id" column="id"/>
       <result property="name" column="name"/>  
    </resultMap>  
</mapper>

MainCategory.java:

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

    public List<SubCategory> getSubCategories() {
        return subCategories;
    }

    public void setSubCategories(List<SubCategory> 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:

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

    }  

}

MainCategoryService.java:

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

        return mainCategoryDao.getAllMainCategories();
    }

    public MainCategoryDao getMainCategoryDao() {
        return mainCategoryDao;
    }

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

}

SubCategory.java:

子类别.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:

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

    }  

}

SubCategoryService.java:

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

    }  

}

HomeController.java:

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

     @Resource
     private SubCategoryService subCategoryService;
     private List<SubCategory> 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";
    }

}

Any suggestion are very welcomed.

任何建议都非常受欢迎。

Answer 1

采纳答案by kopytko

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

1. 手动创建SqlSessions 时，必须确保在使用后关闭它们。

Look for all places where you are opening a SqlSession:

查找您打开 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:

确保在使用后关闭会话。您应该在 try-finally 块中执行此操作，如下所示：

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

关于如何处理 MyBatis SqlSessions 的更多信息可以在这里找到：http: //mybatis.github.io/mybatis-3/java-api.html#sqlSessions

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

2、可以使用MyBatis-Spring来创建和管理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

您可以要求 spring 直接注入 SqlSessions，而不是使用 SqlSessionFactory 注入 DAO。当您这样做时，Spring 将为您管理 SqlSessions，因此您不必自己打开/关闭会话。有关如何执行此操作的更多信息，请访问：http: //mybatis.github.io/spring/sqlsession.html

Java Spring + MyBatis + MySQL + JDBC 连接过多

提问by Zoltan Szabo

采纳答案by kopytko

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

1. 手动创建SqlSessions 时，必须确保在使用后关闭它们。

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

2、可以使用MyBatis-Spring来创建和管理SqlSessions的生命周期。

相关推荐

最近更新

标签

Java Spring + MyBatis + MySQL + JDBC 连接过多

提问by Zoltan Szabo

采纳答案by kopytko

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

1. 手动创建SqlSessions 时，必须确保在使用后关闭它们。

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

2、可以使用MyBatis-Spring来创建和管理SqlSessions的生命周期。

相关推荐

Java 无法使用 jdk8 和 netbeans 8 打开 Web 服务测试器页面

单击按钮打开新屏幕或页面（java-eclipse）

Java 登出 Facebook Android SDK

Java 如何使用正则表达式在 Intellij IDEA 中用小写替换大写？

相关推荐

最近更新

标签