How can I use temporary tables, spring Jdbc and connection pool together?
<p>I have to use temporary tables with spring jdbc and tomcat connection pool to mysql.
I have 4 statement:</p>

<ul>
<li>drop temp table if exists (maybe not necessary)</li>
<li>create temp table</li>
<li>populate temp  table </li>
<li>select table joining hard table with temp table</li>
</ul>

<p>I'm using this code for each one:</p>

<pre><code>getJdbcTemplate().execute(sql);
</code></pre>

<p>I have a problem because the table doesn't exists in the second statement:</p>

<blockquote>
  <p>Table 'tmp_table' doesn't exist</p>
</blockquote>

<p>I thought that the problem can be the connection pool because temporary table are accessible only by one connection and it is dropped when the connection is closed.</p>

<p>This is my datasource and txManager configuration:</p>

<pre><code>@Configuration
@EnableTransactionManagement
public class DBConfiguration {
    @Bean(destroyMethod = "close")
    public javax.sql.DataSource dataSource() {
        ...
    }

    @Bean
    public DataSourceTransactionManager txManager()
    {
        DataSourceTransactionManager tx= new DataSourceTransactionManager(dataSource());
        return tx;
    }
}
</code></pre>

<p>How can I solve this problem? I need to create a new non-pooled connection?</p>