Announcement Announcement Module
Collapse
No announcement yet.
Dynamic partioned table Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Dynamic partioned table

    We're currently using Spring with JdbcTemplate to access MySQL tables.

    Need some feedbacks from experts in forum.

    Due to high volume transactions (over 20millions records/month), the DBA choose to use partitioned table model, where transaction named as transaction_year_month (eg transaction_200502).

    Since JdbcTemplate using compile-model at initializing time, is there any work-around to support dynamic table name for both insert & update based on transaction_record_time?

  • #2
    You can build the SQL string for JdbcTemplate just before issuing the query. So you should be able to do this. JdbcTemplate, rather than the "JDBC object" approach, will be a better fit here.

    Comment


    • #3
      I don't quite understand what you meant by building SQL string before issuing query. Can give some example (really appreciate that).

      Our current implementation something like below:

      Code:
      protected class TrxQuery extends TransactionsQuery {
              protected WebAccountQuery( DataSource ds ) {
                  super( ds, "SELECT * FROM transaction"
                      + " WHERE TRX_DATE = ?" );
      
                  declareParameter( new SqlParameter(Types.DATE) );
                  compile();
              }
      }
      This implementation preset the table name during the initialization, and can't be changed during runtime query.

      We want to use the transaction table (eg transaction_200501) dynamically during query based on given Date range.

      Thanks

      Comment


      • #4
        You can use the JdbcTemplate class--the alternative to the JDBC object class, which is a bit closer to the metal--like this:
        Code:
        jdbcTemplate.query("SELECT NAME, DATE, ... " + 
               "FROM " + workOutDynamicTableName(someArgs) + " WHERE SOMETHING = ?",
                new RowMapper() {
                   public Object mapRow(ResultSet rs, int rowNum) 
        					  throws SQLException {
                         SomeObject so = new SomeObject();
                         so.setName(rs.getString("NAME"));
                         so.setDate(rs.getDate("DATE"));
                         ...
                         return so;
                },
                new Object[] { myBindVariable }
        });
        JdbcTemplate is the fundamental enabling class for the Spring JDBC functionality. Its Javadoc is at http://www.springframework.org/docs/...cTemplate.html. It includes numerous methods for querying and updating, and convenient aggregate function methods, all of which allow you to specify the table(s) involved dynamically.

        Rgds
        Rod

        Comment

        Working...
        X