Spring JDBC and Sybase Temp Tables

[henryn73]

I have a problem, which I think is common, but I haven't had any luck finding and answer to.

Here is what I need to do - API to get User Info -

1. create a temp table (like #my_user_ids)
2. populate the temp table with user Ids I'm interested in (could be over 500 ids)
3. use select inner join with temp table to get the info
4. drop temp table.


In regular SQL I can do that by just executing all of these statements on one connection, but I'd like to not break the spring jdbc and stay within that.

The API has other methods within the same class that does not need single connection (like get all user info) - so I don't want to switch to SingleConnectionDataSource for the entire class.

I tried making the call transactional (put @Transactional), whic allowed me to create, populate and use the temp table, but does not alllow me to drop it

(com.sybase.jdbc3.jdbc.SybSQLException: The 'DROP TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.)

What is the right way to do this within spring jdbc?

[trisberg]

I would use a SingleConnectionDataSource just for that specific method - create it at the beginning, then create a JdbcTemplate based on the SingleConnectionDataSource, do your work and then call destroy() on the SingleConnectionDataSource instance.

[henryn73]

Thank you. Could you post an example of how to do that, with two methods one of which that would use single connection datasource.

[trisberg]

Sure, how about:

package spring.test;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

@Component
public class DbTestDao {

  JdbcTemplate jdbcTemplate;
  
  @Autowired
  public void setDataSource(DataSource dataSource) {
    jdbcTemplate = new JdbcTemplate(dataSource);
  }
  
  @Transactional(readOnly = true)
  public List getCustomers() {
    return jdbcTemplate.query("select id, name, customer_since from customers", 
        new CustomerMapper());
  }
  
  // NOT Transactional - using Sybase temp tables
  public List getSomeCustomers(final List<Long> ids) {
    SingleConnectionDataSource scds = null;
    try {
      scds =
        new SingleConnectionDataSource(jdbcTemplate.getDataSource().getConnection(), true);
    } catch (SQLException e) {
      throw new DataAccessResourceFailureException("Unable to create single connnection ds", e);
    }
    
    List results;
    try {
      JdbcTemplate scdsJdbcTemplate = new JdbcTemplate(scds);
      
      scdsJdbcTemplate.execute("create table #my_ids (id int)");

      scdsJdbcTemplate.batchUpdate("insert into #my_ids(id) values(?)", 
          new BatchPreparedStatementSetter() {

            public int getBatchSize() {
              return ids.size();
            }

            public void setValues(PreparedStatement ps, int i)
                throws SQLException {
              ps.setLong(1, ids.get(i));
            }

          });
      
      results = scdsJdbcTemplate.query(
          "select id, name, customer_since from customers where id in (select id from #my_ids)", 
          new CustomerMapper());
      
      scdsJdbcTemplate.execute("drop table #my_ids");
    } 
    finally {
      scds.destroy();
    }
    
    return results;
  }
  
  public class CustomerMapper implements RowMapper {

    public Object mapRow(ResultSet rs, int row) throws SQLException {
      Customer c = new Customer();
      c.setId(rs.getLong(1));
      c.setName(rs.getString(2));
      c.setCustomerSince(rs.getDate(3));
      return c;
    }
    
  }
  
}

[henryn73]

Perfect -that's exactly what I needed!!! Thank you very much!

Henry