Connection pool exhausted in Spring Boot with JdbcTemplate
<p>I have an issue with connection pool being exhausted when querying a database using Spring Boot and JdbcNamedTemplate.</p>

<p>This is how it is supposed to work:</p>

<ol>
<li>Get a request from outside, with some parameters about how the house is supposed to be built.</li>
<li>Based on parameters recieved, use some REST endpoints and two DAOs to gather data.</li>
<li>Use data to create House object and return it.</li>
<li>Rcieve more requests...</li>
</ol>

<p>How it works as of now:</p>

<ol>
<li>Request is good. </li>
<li>Data from REST endpoints - OK. Data with DAO -> OK only for first 50 requests with two DAOs, after that NOT OK. When one of the DAOs is disabled, no connections are blocked. </li>
<li>After 50 houses are built OK, rest will take forever to finish and have no windows in the end.</li>
<li>Makes it unusable for more requests as they will simply timeout.</li>
</ol>

<p>I get this exception when I call endpoint more than 50 times (max pool size): </p>

<pre><code>    com.atomikos.jdbc.AtomikosSQLException: Connection pool exhausted - try increasing 'maxPoolSize' and/or 'borrowConnectionTimeout' on the DataSourceBean.
</code></pre>

<p>And it will stay like this until I restart the app. It seems like there is something off about my DAO or configuration, but I haven't been able to figure out what, despite searching all day. If anyone can help, I will be thankfull.</p>

<p>Extra info:
No other exceptions are thrown that I am aware of.
All the other data is retrieved correcly.
Send help.</p>

<p>UPDATE:
I did some more experiments:
This app uses another dao that I didnt mention before because I forgot.
It works almost the same, only it connects to a different database, so it has a separate configuration. It also takes advantage of JdbcNamedTemplate and @Qualifier is used to select the correct one. </p>

<p>Now, what I discovered is that disbling one or the other DAO will not eat the connections anymore. So the question is: What can't they coexist in peace?</p>

<p>This is the dao.</p>

<pre><code>    @Component
    public class WindowsDao {

        private static final String PARAM_1 = "param";

        private final String SELECT_ALL = ""
                + " SELECT "
                + "   STUFF "
                + " FROM TABLE " 
                + " WHERE "
                + "     THING =:" + PARAM_1
                + " WITH UR";

        @Autowired
        private NamedParameterJdbcTemplate myTemplate;

        public Optional&lt;List&lt;BottomDealerText&gt;&gt; getWindows(
                final WindowsCode windowCode {

            final MapSqlParameterSource queryParameters = new MapSqlParameterSource()
                    .addValue(PARAM_1, windowCode.getValue())

            final Optional&lt;List&lt;Window&gt;&gt; windows;
            try {
                windows = Optional.of(myTemplate.query(
                        SELECT_ALL,
                        queryParameters,
                        new WindowsRowMapper()));
            }
            catch (final EmptyResultDataAccessException e) {
                LOG.warn("No results were found.");
                return Optional.empty();
            }
            return windows;
        }
    }
</code></pre>

<p>DAO is called from this service:</p>

<pre><code>    @Service
    @Transactional
    public class WindowsService {

        @Autowired
        private WindowsDao windowsDao;

        public Optional&lt;List&lt;Stuff&gt;&gt; getWindows(
                final WindowCode windowCode) {
            final Optional&lt;List&lt;Window&gt;&gt; windows = windowsDao.getWindows(
                    windowCode;
            return windows;
        }
    }
</code></pre>

<p>Which is called from this service: </p>

<pre><code>    @Service
    @Transactional
    public class AssembleHouseService { 
        // some things
        @Autowired
        private WindowsService windowsService;

        public House buildHouse(final SomeParams params) {
            // This service will fetch parts of the house
            HouseBuilder builder = House.builder();
            // call other services and then...
            builder.windows(windowsService.getWindows(args).orElse(/*something*/));
            //and then some more things...
        }
    }
</code></pre>

<p>This is what I use to configure the datasource:</p>

<pre><code>    myDb:
    driver: db2
    schema: STUFF
    unique-resource-name: STUFF
    database-name: STUFF1
    server-name: myServer
    port: 12312
    username: hello
    password: world
    driver-type: 4
    min-pool-size: 2
    max-pool-size: 50
</code></pre>

<p>RowMapper:</p>

<pre><code>public class WindowsRowMapper implements RowMapper&lt;Window&gt; {

    @Override
    public Windows mapRow(final ResultSet rs, final int rowNum)
            throws SQLException {
        return new BottomDealerText(
                re.getString("WSIZE"),
                rs.getString("DESCRIPTION"),
                rs.getString("COLOR"));
    }
}
</code></pre>