Generated Column data manipulation from row to column

I have an unique solution need and not sure how vaadin framework or any add-in to it can help.

Situation:
I have following database tables:
database table name: OrderDetail
Fields: Article | Colour | Size | Quantity

I want to do CRUD the above data in a Vaadin Table or Grid in the following layout

Article | Colour | SIZE
| |----------------------------------------
| | sizeS | SizeM | SizeL | SizeXL

Shirt | Blue | 56 | 40 | 26 | 40
| Red | 24 | 15 | 30 | 20

As number of sizes varies from Order to Order, the columns for different sizes is expected be Generated at run time based on size profile of this Order. When saving the data, it should dave the data in the Entity in the followinng standard format :


Article | Colour | Size | quantity

Shirt | Blue | SizeS | 56
Shirt | Blue | SizeM | 40
Shirt | Blue | SizeL | 26
Shirt | Blue | SizeXL | 40
Shirt | Red | SizeS | 24
Shirt | Red | SizeM | 15
Shirt | Red | SizeL | 30
Shirt | Red | SizeXL | 20

What do you suggest as potential solution of CRUD functionality?

There’s nothing built-in for this, exactly, but let’s see…

It’s not possible to merge header cells vertically in Grid, but you could maybe fake it by only having one header row with a merged header cell for fixed width size columns, and put a custom component that lists the size options using those same fixed widths to keep them corresponding with the columns. Table doesn’t support merging or components in columns, so Grid would be the better option here.

Using
GeneratedPropertyContainer
around BeanItemContainer gives you more flexibility on what you display, but you can’t edit generated properties, so I assume that would be mostly usable for the Article column.

Your size and quantity could be on a list of nested properties to be able to get them on the same row. Can’t remember off the top of my head how to configure an entity to save the nested properties to the same table like that, but I think it should be doable.

Hi Anna
Thanks for your tips. I was looking at the code that I developed in my java swing application many years ago which is somehow similar to the need as follows:

Saving data to the database:

for (int i = 0; i < NRows; i++) { jCat = (String) rowData[0]
;
jId = (Integer) rowData[1]
;
for (int c = firstFloorCol; c < floorHorizonLastCol; c++) {
fId = table1.getColumnModel().getColumn(c).getHeaderValue().toString();
rate = (Double) rowData[c]
;
if(fId != null && fId.length()<6){
stmt.addBatch("INSERT INTO DcmSubContJobRateD (JobRateId, SubContJobId, Rate, JobCategory, FloorNo) "
+ "VALUES (" + docNumber + ", '" + jId + "'," + rate + ",'" + jCat + "','" + fId + "')");
}
}
}

Loading existing data from database to UI Table:

[code]
while (rs.next()) {
rowData[j]
[0]
= rs.getString(1);
jId = rs.getInt(2);
rowData[j]
[1]
= rs.getInt(2);
rowData[j]
[2]
= rs.getString(3);
rowData[j]
[3]
= rs.getString(4);
//Load Floors as columns
if (!actionType.equalsIgnoreCase(“new”)) {
sql = "SELECT f.FloorNo, ISNULL(d.Rate,0) FROM DcmFloors f LEFT OUTER JOIN DcmSubContJobRateD d ON (f.FloorNo=d.FloorNo) "

  • “AND (d.JobRateId='” + mJobRateId.getText() + “‘) AND (d.JobCategory=’” + jobCategory + “') AND (d.SubContJobId=” + jId + “)”;
    Statement stmt2 = dbConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet rs2 = stmt2.executeQuery(sql);
    int firstFloorCol = 4;
    if (rs2.next()) {
    rs2.beforeFirst();
    while (rs2.next()) {
    fId = rs2.getString(1);
    rate = rs2.getDouble(2);
    for (int i = firstFloorCol; i < floorHorizonLastCol; i++) {
    floorColumn = table1.getColumnModel().getColumn(i).getHeaderValue().toString();
    if (fId.equalsIgnoreCase(floorColumn)) {
    rowData[j]
    = new Double(rate);
    break;
    }
    }
    }
    } else {
    for (int i = firstFloorCol; i < floorHorizonLastCol; i++) {
    rowData[j]
    = new Double(0);
    }
    rowData[j]
    [floorHorizonLastCol]
    = new Double(0);
    }
    rs2.close();
    stmt2.close();
    }
    j++;
    }
    [/code]Is there any way we can manipulate similarly in Vaadin?

Hi!

If you weren’t planning to use JPA or any such thing, then it’s definitely easy to construct your database in whatever way you want, although dealing with direct JDBC queries is always a bit messier and more prone to mistakes than an entity-based approach that automates part of the process.

You can find one article about the use of JPA with Vaadin
here
, and an article about using LazyQueryContainer
here
(it’s written for FilteringTable instead of Grid, but that doesn’t matter for database handling) – those are far from your only options, of course, but are both fairly popular.