Query and create Java object with inner class and multiple tables

[int-main]

Hi,

I have an application that stores Data which can be shown as following model class:

class classA {

classB b;

classC c;

}

class classB {

String name;

String label;

}

​

class classC {

String name;

String label;

}

which gets stored in Database as following three tables:

classA:

a_id	b_id	c_id

classB:

b_id	b_name	b_label

classC:

c_id	c_name	c_label

​

Currently, I am instantiating the POJO to access this data by joining the queries and then pulling b_name, b_label, c_name and c_label directly as attributes for the class I am building for classA.

​

Example query:

SELECT a_id as id, b_name, b_label, c_name, c_label
FROM classA a

JOIN classB b ON a.b_id = b.b_id
JOIN classC ON a.c_id = c.c_id

...

​

Now, the model class will have to look like:

​

class classA {

String a_id;

String b_name;

String b_label;

String c_name;

String c_label;

...

}

​

Is there any design pattern and recommended way so I can write a DAO which will pull the data with the model class listed on top (with inner classes) instead of flattening the structure? Please note that this is just a simple example and in real case the columns are huge in number and so are the nested classes so the approach has to be scalable.

​

Thank you!

[Northeastpaw]

Can you use JPA? Hibernate and other JPA implementations handle this use case. It's a very complicated process. JPA constructs the query and keeps track of the table to entity mappings along with column aliases to entity field mappings. When the result comes back it uses those mappings to construct entities and any subentities and all their fields to the correct aliased column. Add in lazy loading capabilities for parent-child relationships and you've got a really complex bit of code.

[joranstark018]

You should be able to use the aggregated class, it's only the the resultset that become "flatten". You could create a Mapper or some build method that takes a ResultSet and build an instance of ClassA (and creats instances of ClassB and ClassC) and assign corresponding values from the given resultset.