Java Toplink Essential - Change entities tables at runtime

In my db there are some tables like

TABLE1_2006 (ID, CODE, VOLUME)
TABLE2_2007 (ID, CODE, VOLUME)
TABLE3_2008 (ID, CODE, VOLUME)

They are equal but have diffferent name. In fact these tables are
created
every year (as you can see).
In this sense they use the same entity

public class MyEntity implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Column(name = "ID", nullable = false)
private Integer id;
@Column(name = "CODE")
private String code;
@Column(name = "VOUME")
private String volume;

....
....
....
}

I need to obtain different list of entities relative to all tables in
the same database;
can I change table name of my entity at runtime ?


Thanks

Nicola

On Wed, 10 Sep 2008 01:23:48 -0700, nicola wrote:

> In my db there are some tables like
>
> TABLE1_2006 (ID, CODE, VOLUME)
> TABLE2_2007 (ID, CODE, VOLUME)
> TABLE3_2008 (ID, CODE, VOLUME)
>
> They are equal but have diffferent name. In fact these tables are
> created
> every year (as you can see).
> In this sense they use the same entity
>
> public class MyEntity implements Serializable {
> private static final long serialVersionUID = 1L; @Id
> @Column(name = "ID", nullable = false) private Integer id;
> @Column(name = "CODE")
> private String code;
> @Column(name = "VOUME")
> private String volume;
>
> ...
> ...
> ...
> }
>
> I need to obtain different list of entities relative to all tables in
> the same database;
> can I change table name of my entity at runtime ?
>
>
Declare a view over all the tables and enquire on that.

But, why on earth use multiple tables? They don't do anything that a
single table with a YEAR column couldn't do while managing to make at
least one task more difficult as you've just demonstrated.


--
martin@ | Martin Gregorie
gregorie. | Essex, UK
org |

nicola wrote:
>> In my db there are some tables like
>>
>> TABLE1_2006 (ID, CODE, VOLUME)
>> TABLE2_2007 (ID, CODE, VOLUME)
>> TABLE3_2008 (ID, CODE, VOLUME)

Martin Gregorie wrote:
> Declare a view over all the tables and enquire on that.
>
> But, why on earth use multiple tables? They don't do anything that a
> single table with a YEAR column couldn't do while managing to make at
> least one task more difficult as you've just demonstrated.

I feel suspicion when I read about inherited tables in a DBMS, such as in
Postgres. At first blush the OP's situation seemed to me like an
inherited-table scenario. I felt about as I might encountering an escaped
specimen from Dr. Moreau's island.

I used to feel that way about automated object-relational mapping (ORM)
packages, but JPA alleviates that. There is a dichotomy between the world
view of object-oriented programming and that of relational database
architecture. Inherited tables seem out of place to me - object-oriented
monstrosities in a data world. Multiple identical tables are like that also -
if the data structures are the same and have the same interpretation, that
argues for a single logical table. Martin's suggestion is spot on, and
completely harmonious with the data-world outlook.

I don't know of a term analogous to "normal form" (as in "third normal form")
for excessive splitting of tables into many tables; all the normal forms have
to do with insufficient splitting. Advances in the database world occur in
how to structure data - relations, normal forms, star schemae, foreign keys,
these are the building blocks of DBMSes as classes and objects are of O-O
programming.

How one structures data for a database is different from how one structures
objects for a program.

--
Lew

Hi guys, thanks a lot for your answers.
I agree with you but my boss wants this solution.
He said "To much rows for one table...." [some thousands].
Maybe because we use H2 Database Engine although H2 has very
good performances.
I don't understand his "brain engine".

Thanks again.


Nicola

GG nicola wrote:
> Hi guys, thanks a lot for your answers.
> I agree with you but my boss wants this solution.
> He said "To much rows for one table...." [some thousands].
> Maybe because we use H2 Database Engine although H2 has very
> good performances.
> I don't understand his "brain engine".
>

I'd suggest creating a pair of test systems, one with a single table,
one with many. Write a program that populates each with the same large
set of randomly generated data, measure memory usage and performance for
some typical queries. I'd expect that the results will prove that a
single table works best. However if it doesn't, at least you have a
justification for making a more complex, more expensive (to develop) and
harder to maintain solution.

--
RGB

On 11 Set, 11:06, RedGrittyBrick <RedGrittyBr...@spamweary.invalid>
wrote:
> GG nicola wrote:
> > Hi guys, thanks a lot for your answers.
> > I agree with you but my boss wants this solution.
> > He said "To much rows for one table...." [some thousands].
> > Maybe because we use H2 Database Engine although H2 has very
> > good performances.
> > I don't understand his "brain engine".
>
> I'd suggest creating a pair of test systems, one with a single table,
> one with many. Write a program that populates each with the same large
> set of randomly generated data, measure memory usage and performance for
> some typical queries. I'd expect that the results will prove that a
> single table works best. However if it doesn't, at least you have a
> justification for making a more complex, more expensive (to develop) and
> harder to maintain solution.
>
> --
> RGB

I'll try it thanks.

The use of YEAR colum is the best way. Take this boss!

Thanks all.

Thats all folks.

"Lew" <noone@lewscanon.com> wrote in message
news:cqWdnWKfq97dBlXVnZ2dnUVZ_rCdnZ2d@comcast.com. ..
[ SNIP ]
> I don't know of a term analogous to "normal form" (as in "third normal
> form") for excessive splitting of tables into many tables; all the normal
> forms have to do with insufficient splitting. Advances in the database
> world occur in how to structure data - relations, normal forms, star
> schemae, foreign keys, these are the building blocks of DBMSes as classes
> and objects are of O-O programming.
[ SNIP ]

I've seen this particular operation - splitting one table into many tables
based on the value of an attribute, and dropping that column - described as
"attribute splitting". As I understand it this violates Domain Key Normal
Form (DKNF), because not all of the logical decisions your program would
want to make are defined by the data.

I'm leery of any approach where domain information is captured only in the
names of files or database tables. Even when using flat files I'd still want
to have a header line with information like this.

If the boss absolutely wanted this kind of splitting I'd at least argue for
keeping a year column in each table, even if it's all the same value for
each table.

AHS