Dynamic Table creation in Hibernate Template

Question

I am importing stock quotes data using HibernateTemplate + SpringFramework

Format of the data

AAPL,09-Jun-2010 09:00,251.47,251.47,251.39,251.39,640
AAPL,09-Jun-2010 09:01,251.4,251.4,251.05,251.26,6844
INTC,09-Jun-2010 09:00,251.47,251.47,251.39,251.39,640
INTC,09-Jun-2010 09:01,251.4,251.4,251.05,251.26,6844
MSFT,09-Jun-2010 09:00,251.47,251.47,251.39,251.39,640
MSFT,09-Jun-2010 09:01,251.4,251.4,251.05,251.26,6844

I have one Entity class called Stock

@Entity
public class Stock implements Serializable {
public Stock() {}

private Long id;
private BigDecimal open;
    .... close,high, low..etc
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
public Long getId() {
    ....

My problem with this setup is that hibernate will put everything into a table called "Stock".

I want to separate different stock into a different table, so with the data above I will end up having 3 tables (AAPL, MSFT, INTC)

Is there an easy way to do this without resorting to hand-written sql statement in Hibernate/HibernateTemplate? sort of creating the table dynamically?

Environment: - Mysql, Hibernate3.5.3, springframework3.0

Answer 1

Without having custom classes for each one, I don't know of an easy way to make these tables appropriately.

Having said that, I would encourage you to reconsider your design, and consider the usage of better OO concepts.

For instance, say you kept your Stock object/table, but then had another object such as DailyActivity component which contained a date, open, high, low, close, etc.

You could then specify your stock as having a collection of DailyActivity objects. This could then be mapped by Hibernate as a Stock to DailyActivity, one-to-many, relationship.

Then, if you wanted to evaluate how MSFT did it would become a relatively trivial query selecting the MSFT record from the Stock table, selecting its DailyActivity objects for a specified period of time.

Answer 2

I'm not sure this is recommended. The reference docs suggest the property is called hibernate.hbm2ddl.auto

There is some previous discussion at http://stackoverflow.com/questions/221379/hibernate-hbm2ddl-autoupdate-in-production and http://stackoverflow.com/questions/438146/hibernate-question-hbm2ddl-auto-possible-values-and-what-they-do which you should also look at

Answer 3

I want to separate different stock into a different table, so with the data above I will end up having 3 tables (AAPL, MSFT, INTC)

Then either introduce an inheritance hierarchy (with a JOINED or TABLE_PER_CLASS strategy) and persist the appropriate sub type or maybe use horizontal partitioning (this would require some testing though, I've never done that with MySQL and Hibernate).