The data grid shown below was dynamically created using Struts 2, MyBatis, and jQuery. XML files are used to define the structure of the table being edited and the table relationship of foreign key fields. The structure that is used to dynamically generate the final form can be easily adapted to fit any simple table. This structure gives me the ability to quickly add tables, add or remove table fields, and create a data grid that can be used to view and delete records and edit fields.
The SQL script to create the table.
CREATE TABLE [dbo].[cjxProjectStaff](
[RecordID] [int] NULL,
[AppUserID] [int] NULL,
[StaffRoleID] [int] NULL,
[BudgetAuthority] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[cjxProjectStaff] WITH CHECK ADD CONSTRAINT [FK_cjxProjectStaff_AppUser] FOREIGN KEY([AppUserID])
REFERENCES [dbo].[AppUser] ([ID])
GO
ALTER TABLE [dbo].[cjxProjectStaff] CHECK CONSTRAINT [FK_cjxProjectStaff_AppUser]
GO
ALTER TABLE [dbo].[cjxProjectStaff] WITH CHECK ADD CONSTRAINT [FK_cjxProjectStaff_luStaffRole] FOREIGN KEY([StaffRoleID])
REFERENCES [dbo].[luStaffRole] ([ID])
GO
ALTER TABLE [dbo].[cjxProjectStaff] CHECK CONSTRAINT [FK_cjxProjectStaff_luStaffRole]
GO
ALTER TABLE [dbo].[cjxProjectStaff] WITH CHECK ADD CONSTRAINT [FK_cjxProjectStaff_Project] FOREIGN KEY([RecordID])
REFERENCES [dbo].[Project] ([ID])
GO
I use two XML files. One XML file defines the structure of the table and is used in the application to provide information on what fields to display, the types of fields, and whether or not the field can be edited. Here is the file that defines the fields in the ProjectStaff table:
<list>
<FieldDescriptor>
<fieldName>recordID</fieldName>
<fieldLabel>Project ID</fieldLabel>
<fieldType>1</fieldType>
<fieldSize>0</fieldSize>
<editable>false</editable>
<listable>false</listable>
</FieldDescriptor>
<FieldDescriptor>
<fieldName>appUserID</fieldName>
<fieldLabel>Staff ID</fieldLabel>
<fieldType>1</fieldType>
<fieldSize>0</fieldSize>
<editable>true</editable>
<listable>true</listable>
<fkeyName>lstKeyStaff</fkeyName>
</FieldDescriptor>
<FieldDescriptor>
<fieldName>StaffRoleID</fieldName>
<fieldLabel>Role ID</fieldLabel>
<fieldType>1</fieldType>
<fieldSize>0</fieldSize>
<editable>true</editable>
<listable>true</listable>
<fkeyName>lstStaffRole</fkeyName>
</FieldDescriptor>
<FieldDescriptor>
<fieldName>budgetAuthority</fieldName>
<fieldLabel>Budget Authority</fieldLabel>
<fieldType>5</fieldType>
<fieldSize>0</fieldSize>
<editable>true</editable>
<listable>true</listable>
</FieldDescriptor>
</list>
When this file is read in to the application, it creates a List of type FieldDescriptor, describing all of the fields in the table and relationships to other tables if the field is a foreign key.
Another XML file defines tables that are related to the main table by a foreign key. The fkeyName field from the XML file above is used to link to the fields defined in the following XML file.
<list>
<KeyDescriptor>
<name>lstKeyStaff</name>
<mapper>KeyValueDescriptionMapper</mapper>
<method>selectAllKeyValue</method>
<luTable>vwAllKeyStaff</luTable>
<luTableField>DescValue</luTableField>
</KeyDescriptor>
<KeyDescriptor>
<name>lstStaffRole</name>
<mapper>KeyValueDescriptionMapper</mapper>
<method>selectAllKeyValue</method>
<luTable>luStaffRole</luTable>
<luTableField>DescValue</luTableField>
</KeyDescriptor>
</list>
When this file is read in to the application, it creates a List of type KeyDescriptor. The "KeyDescriptor" tells the application how to load in values to create a list of Key/Value pairs. The list will be used by Struts to create the select lists in our JSP page.
Now for the main part of the application. Besides my Struts action class, there are two classes with five main fields that are used.
public abstract class BaseTable {
protected List<Object> lstRecord;//list of records from the table
protected BaseTableDescriptor baseTableDescriptor;//the table descriptor for the table records that are being viewed or edited
protected HashMap<Object, Map<Object, String>> selectMap; //map of selection values for foreign key tables
public abstract class BaseTableDescriptor {
protected List<FieldDescriptor> lstFieldDescriptor;
protected List<KeyDescriptor> lstKeyDescriptor;
The list, lstRecord, is populated with the table records from the ProjectStaff table.
The list, lstFieldDescriptor, is created from the XML file displayed above and as stated before this list describes the database fields.
The list, lstKeyDescriptor, is created from the XML file displayed above and the information in this list is used to create the selectMap HashMap. For each entry in "selectMap", the key is "KeyDescriptor.name" and the value is the list of Key/Value pairs for the named table.
This information is then used in the Struts tags in the following JSP page to create the table.
<!--Iterate through our list of records-->
<s:iterator value="baseTable.lstRecord" var="element" status="stat">
<tr>
<!--For each record, iterate through our list of field descriptors-->
<s:iterator value="baseTable.baseTableDescriptor.lstFieldDescriptor" var="fdelement" status="fdstat">
<s:if test="listable">
<s:if test="#stat.odd == true">
<td style="background-color:#F9F9d9;white-space:nowrap;height:23px;">
</s:if>
<s:else>
<td style="background-color:#F9F9F9;white-space:nowrap;height:23px;">
</s:else>
<s:if test="#fdstat.index == 1">
<img id="imgDel${stat.index}" src="../images/del1.gif" style="border:0px" alt="Delete" title="Delete"/>
<script type="text/javascript">
//delete the record
$('#imgDel${stat.index}').click(function() {
//clear the value to mark the record for deletion
$('#${fieldName}${stat.index}').val('');
//hide the row
$(this).parent().parent().hide();
});
</script>
</s:if>
<!--If this is a foreign key field-->
<s:if test="fkeyName != null">
<s:select name="baseTable.lstRecord[%{#stat.index}].%{#fdelement.fieldName}" id="%{#fdelement.fieldName}%{#stat.index}" list="baseTable.selectListMap(fkeyName)" headerKey="" headerValue="Please Select" theme="simple"/>
</s:if>
<s:else>
<!--If this is a boolean field-->
<s:if test="fieldType == 5">
<s:checkbox name="baseTable.lstRecord[%{#stat.index}].%{#fdelement.fieldName}" theme="simple" title="%{fieldLabel}"/>
</s:if>
<!--The following is just used for debugging-->
<s:else>
<s:if test="fkeyName != null">
<s:property value="keyValue(fkeyName, '#element.' + #fdelement.fieldName)"/>
</s:if>
<s:else>
${element[fdelement.fieldName]}
</s:else>
</s:else>
</s:else>
</td>
</s:if>
</s:iterator>
</tr>
</s:iterator>
<tr>
<s:if test="baseTable.lstRecord.size() < 5">
<td id="tdAddNew" style="background-color:#a0F0d9;" colspan="1"> <img id="imgAddNew" src="../images/max1t.gif" style="border:0px"/> <b>Add New Project Staff</b></td>
<script type="text/javascript">
//add a new record
$('#tdAddNew').click(function() {
//clear the result message
$("#tdResMsg").val("")
//save the records and reload the table
$("#${baseTable.ajaxTarget}").load('../${namespace}/AddCustomTableRecord.action?btName=${baseTable.tableName}&btRecID=${baseTable.recordID}&rtSize=${baseTable.lstRecord.size()}&rNew=true&target=${baseTable.ajaxTarget}', $("#saveRecord").serialize());
});
</script>
</s:if>
<s:else>
<td colspan="1"><b> Max records allowed reached.</b></td>
</s:else>
<td id="tdResMsg"colspan="2"><s:property value="baseTable.resMsg"/></td>
</tr>
<tr>
<td><SPACER height="5" type="block"></td>
</tr>
<tr>
<td colspan="2" align="center" style="white-space: nowrap">
<s:submit action="SaveCustomTable" id="saveCustomTable" value="Save" onclick="return false;" theme="simple"/>
<s:submit action="ReloadCustomTable" id="reloadCustomTable" value="Reload" onclick="return false;" theme="simple"/>
</td>
</tr>
<script type="text/javascript">
//submit the records
$('#saveCustomTable').click(function() {
$("#tdResMsg").val("Saving records. Please wait.")
$("#${baseTable.ajaxTarget}").load('../${namespace}/SaveCustomTable.action?btName=${baseTable.tableName}&btRecID=${baseTable.recordID}&rtSize=${baseTable.lstRecord.size()}&target=${baseTable.ajaxTarget}', $("#saveRecord").serialize());
});
//reload the records
$('#reloadCustomTable').click(function() {
$("#tdResMsg").val("Reloading records. Please wait.")
$("#${baseTable.ajaxTarget}").load('../${namespace}/ReloadCustomTable.action?btName=${baseTable.tableName}&btRecID=${baseTable.recordID}&rtSize=${baseTable.lstRecord.size()}&target=${baseTable.ajaxTarget}', $("#saveRecord").serialize());
});
</script>
This is a very brief explanation of the process I use. Please comment if you would like some additional information or have any questions.
