1 Reply Latest reply on Dec 1, 2007 7:19 AM by Luke Skywalker

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

    Luke Skywalker Newbie

      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?

        • 1. Re: Handling dynamic data in RDBMS (i.e. no direct column ma
          Luke Skywalker Newbie

          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?