Handling dynamic data in RDBMS (i.e. no direct column mappin

[tailor]

Hi all,

I'm actually a bloody beginner in Seam but somehow managed to succesfully develop my first web app in Seam. Right now I'm trying to solve a problem where I need some further advice.

I have chosen a somewhat dynamic approach to save additional data of a user in the RDBMS. There are four main tables.

user (id, name)
record (id, user_id)
data_value (id, record_id, data_type_id, value)
data_type (id, name, description)

The data_type table is a simple table that holds a list of possible fields so to say where you can save values for. Those values should be saved for a user. Each user has its own record which consists of defined data_types. The relation data_value brings it all together, this is where the actual values for the defined data_values are stored.

According SQL Create-Statements (MySQL): http://pastebin.com/f5883f89d
According Entity Beans: http://pastebin.com/m501ed44e

I created a SLSB to retrieve a list of "DataValue" objects from the db.

@Stateless
@Name("recordList")
public class RecordListAction implements RecordList, Serializable {

 private static final long serialVersionUID = 1L;

 @PersistenceContext
 private EntityManager em;

 @In
 private User user;

 @DataModel
 private List<DataValue> dataValues;

 // needed to prevent spurious warning
 // getResultList() returns a non generic version of List
 @SuppressWarnings("unchecked")
 @Factory("dataValues")
 public void getDataValues() {
 dataValues = em.createQuery(
 "select dv from DataValue dv "
 + "inner join dv.record as record "
 + "inner join dv.dataType as dataType "
 + "where record.user.id = :userId")
 .setParameter("userId", user.getId())
 .getResultList();
 }

 @Remove
 public void destroy() {
 }
}

I am able to output this list in a facelet.

<h:dataTable id="dataValues" var="dataValue" value="#{dataValues}" rendered="#{dataValues.rowCount gt 0}">
 <h:column>
 <f:facet name="header">Name</f:facet>
 <h:outputText value="#{dataValue.dataType.name}" />
 </h:column>
 <h:column>
 <f:facet name="header">Value</f:facet>
 <h:outputText value="#{dataValue.value}" />
 </h:column>
 </h:dataTable>

And now it comes to the interesting part (finally!). I want to be able to modify (for now; creating and deleting should be integrated later) the "DataValue" objects. Everything I did before was straightforward, i.e. retrieve one row of a table and edit its columns. But now I retrieve one or more rows with two columns (one with the data_type.name and one with the data_value.value) and want to edit them.

I currently do not know where to start solving this problem. Maybe anyone of you can give me some hints to start?

[tailor]

After some further investigation I'm now able to do what I want. I can now modify multiple rows at once in one transaction.

My SLSB with an added update() method:

@Stateless
@Name("recordList")
public class RecordListAction implements RecordList, Serializable {

 private static final long serialVersionUID = 1L;

 @PersistenceContext
 private EntityManager em;

 @In
 private User user;

 @DataModel
 private List<DataValue> dataValues;

 @In
 private FacesMessages facesMessages;

 // needed to prevent spurious warning
 // getResultList() returns a non generic version of List
 @SuppressWarnings("unchecked")
 @Factory("dataValues")
 public void getDataValues() {
 dataValues = em.createQuery(
 "select dv from DataValue dv "
 + "inner join dv.record as record "
 + "inner join dv.dataType as dataType "
 + "where record.user.id = :userId")
 .setParameter("userId", user.getId())
 .getResultList();
 }

 @TransactionAttribute
 public void update() {
 for (DataValue dataValue : dataValues) {
 em.merge(dataValue);
 }
 facesMessages.add("Data successfully saved.");
 }

 @Remove
 public void destroy() {
 }
}

My corresponding facelet:

<h:form>
 <rich:panel>
 <f:facet name="header">Edit Data</f:facet>

 <ui:repeat var="dataValue" value="#{dataValues}">

 <s:decorate id="idDecoration" template="layout/edit.xhtml">
 <ui:define name="label">
 <h:outputText value="#{dataValue.dataType.name}" />
 </ui:define>

 <h:inputText value="#{dataValue.value}" />
 </s:decorate>

 </ui:repeat>

 <div style="clear: both"><span class="required">*</span>
 required fields</div>
 </rich:panel>

 <div class="actionButtons"><h:commandButton
 action="#{recordList.update}" value="submit" /></div>
 </h:form>

Everything fine so far, but I'm now stucked at validating entered data. The actual data is stored in the column "value" of the table "data_value". The column "value" is a varchar(255) in MySQL but its validation rules depend on "data_type_id" which references the table "data_type".

To give you an example:
There are 2 entries in "data_type". One is a type for e-mail addresses and one for surnames. This results in different validation rules that have to be applied to the same column in table "data_value".

Again I'm asking for some hints how to do so. Has anyone some suggestions?