title: Spring and MySQL connections (keeps creating new connections instead using sleeping connections)
body: <p>I don't know why each time someone visits a page on my website, a new connection is open to the database. Eventually I reach like 300 and get an error and pages no longer load. I thought the way it should work is, I Have maxIdle set for 30, so that means I should not have more than 30 sleeping connections open by Spring. But at this moment I have 88 open. If I close down or reboot my server it will go back down to near 0. I use getJdbcTemplate() for every query.</p>
  
  <p>Here is part of my spring-config.xml</p>
  
  <pre><code>&lt;bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close"&gt;
      &lt;property name="driverClassName" value="com.mysql.jdbc.Driver" /&gt;
      &lt;property name="url" value="XXXXXXXXX" /&gt;
      &lt;property name="username" value="XXXXXXXXXX" /&gt;
      &lt;property name="password" value="XXXXXXXX" /&gt;
      &lt;property name="maxIdle" value="30"/&gt;
      &lt;property name="defaultAutoCommit" value="true"/&gt;
  &lt;/bean&gt;
  </code></pre>
  
  <p>Here is what part the dao looks like (renamed)</p>
  
  <pre><code>@Repository
  public class ExampleDAOImpl extends JdbcDaoSupport implements ExampleDAO {
  
  @Autowired
  public ExampleDAOImpl(DataSource dataSource) {
      setDataSource(dataSource);
  }
  </code></pre>
  
  <p>Here is what I get from show processlist in MySQL (not display all rows here, there were 88)</p>
  
  <pre><code>+----------+------------+-------------------------------------------+------+---------+-------+----------------+---------------------
  | Id       | User       | Host                                      | db   | Command | Time  | State          | Info             db
  +----------+------------+-------------------------------------------+------+---------+-------+----------------+-----------------------
  | 21721763 | user | XXXXXXXX:60586 | db | Sleep   | 10609 |                | NULL             db
  | 21924432 | user | XXXXXXXX:59498 | db | Query   |     0 | NULL           | show processlist db
  | 21924580 | user | XXXXXXXX:59702 | db | Sleep   |   790 |                | NULL             db
  | 21924722 | user | XXXXXXXX:60010 | db | Sleep   |   790 |                | NULL             db
  | 21924813 | user | XXXXXXXX:60131 | db | Sleep   |   787 |                | NULL             db
  | 21924819 | user | XXXXXXXX:60139 | db | Sleep   |   786 |                | NULL             db
  | 21924841 | user | XXXXXXXX:60156 | db | Sleep   |   785 |                | NULL             db
  | 21927089 | user | XXXXXXXX:56292 | db | Query   |     1 | Opening tables | XXXXXXXX |
  | 21927090 | user | XXXXXXXX:56295 | db | Query   |     1 | Opening tables | XXXXXXXX |
  88 rows in set (0.01 sec)
  </code></pre>
  
  <p>Example dao method</p>
  
  <pre><code>public String selectSomething(String text) throws IllegalArgumentException, DAOException {
       return (String) getJdbcTemplate().queryForObject(SELECT_QUERY_1, new Object[] {text}, String.class);
  }
  </code></pre>