Hello,
my problem is to map a legacy table without primary key to java.
And no foreign keys are defined in this table.
The table contains a list of list.
What i have to retrieve is a fieldplan for one experiment.
A fieldplan contains a list of lanes, which in turn contains a list of plots
mysql> desc FIELDPLAN;
Code:
+-----------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-------------+------+-----+---------+-------+
| ExperimentID | varchar(55) | YES | | NULL | |
| TreatmentCode | varchar(15) | YES | | NULL | |
| Block | varchar(15) | YES | | NULL | |
| HolderFAOCode | varchar(10) | YES | | NULL | |
| AccessionNumber | varchar(35) | YES | | NULL | |
| IncreaseID | varchar(55) | YES | | NULL | |
| TransactionID | varchar(55) | YES | | NULL | |
| Lane | smallint(6) | YES | | NULL | |
| Plot | int(11) | YES | | NULL | |
[some attributes....]
+-----------------------+-------------+------+-----+---------+-------+
The content of this table is like this:
mysql> select ExperimentID,Block,Lane,Plot,StockSite,TreatmentCode,HolderFAOCode,AccessionNumber,Status from FIELDPLAN order by Exper
Code:
+--------------------------+-------+------+------+-----------------+---------------+---------------+-----------------+----------+
| ExperimentID | Block | Lane | Plot | StockSite | TreatmentCode | HolderFAOCode | AccessionNumber | Status |
+--------------------------+-------+------+------+-----------------+---------------+---------------+-----------------+----------+
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 1 | 1 | NULL | NULL | SW | SW | Marker |
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 1 | 2 | DEUQue_BAZReurs | Ten rows | DEU146 | AVE 3095 | Project |
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 1 | 3 | NULL | Ten rows | BGR001 | A7BM0011 | Standard |
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 1 | 4 | DEUQue_BAZReurs | Ten rows | SWE002 | NGB6360 | Project |
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 1 | 5 | DEUQue_BAZReurs | Ten rows | DEU146 | AVE 4488 | Project |
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 2 | 1 | DEUQue_BAZReurs | Ten rows | SWE002 | NGB2710 | Project |
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 2 | 2 | NULL | NULL | SW | SW | Marker |
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 2 | 3 | DEUQue_BAZReurs | Ten rows | DEU146 | AVE 2950 | Project |
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 2 | 4 | NULL | Ten rows | BGR001 | A7BM0001 | Standard |
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 2 | 5 | DEUQue_BAZReurs | Ten rows | DEU146 | AVE 2924 | Project |
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 3 | 1 | DEUQue_BAZReurs | Ten rows | DEU146 | AVE 4659 | Project |
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 3 | 2 | NULL | Ten rows | CPVO | 19960125 | Standard |
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 3 | 3 | NULL | NULL | SW | SW | Marker |
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 3 | 4 | DEUQue_BAZReurs | Ten rows | DEU146 | AVE 2967 | Project |
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 3 | 5 | DEUQue_BAZReurs | Ten rows | DEU146 | AVE 544 | Project |
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 4 | 1 | DEUQue_BAZReurs | Ten rows | RUS001 | 200111466 | Project |
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 4 | 2 | DEUQue_BAZReurs | Ten rows | DEU146 | AVE 2067 | Project |
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 4 | 3 | DEUQue_BAZReurs | Ten rows | DEU146 | AVE 4794 | Project |
| 2007DEUQue_BAZ/AVEQInc10 | 1 | 4 | 4 | NULL | NULL | SW | SW | Marker |
+--------------------------+-------+------+------+-----------------+---------------+---------------+-----------------+----------+
60 rows in set (0.00 sec)
What i have identified is the key column (ExperimentID) and the two list-index
(Lane and Plot).
But i have not found a way to map this correctly to java.
My current target is this:
Code:
@Entity
public class Experiment ..{
....
List<Fieldplan> expFieldplan
....
}
Must i define the foreign key between fieldplan and Experiment? I suppose so.
Since i use annotations together with hibernate entitymanager i like to know how i have to annotate my classes?
Code:
public class Fieldplan {
List<Plot> Lane;
}
public class Plot {
all attributes of one plot
}
Perhaps someone can shed some light in here.
I have read the brillant book form Christian and searched the WIKI and the FAQ but without success yet. If you can offer any pointer they are welcome.
Ciao,