CODEWITHSUNDEEP
javadatabaseswingarchitecture
dareurdream
dareurdream

Reputation: 241

Design architecture for a tool

I have a requirement of a tool which would capture data and then save it in an excel file and generate sql’s for database execution.

To address the same, I started designing and developing a framework which would read xml file and render the swing screen for data capture and saving it in excel. Now, I have reached a stage where I have got data in excel. Now, I am not able to decide the approach further. I have an approach in mind to get the data from excel sheet and build sql’s, but am not confident of the approach.

The approach am thinking of is to develop more xml’s(one for each Schema in db) which would contain replica of the database table structure needed. In the GUI xml, I would provide a new tag called "databaseMapping" containing SCHEMA_NAME;TABLE_NAME ;COLUMN_NAME. When a user asks generate sql’s for a specific file then, I would read the screen xml related to that excel and find out the database mappings and then build the sql’s. But I am seeing challenges in this approach :-

1) In an excel, columns would correspond to more than one table and could have columns scattered across different sheets and so reading and building the sql would be a resource consuming activity.

2) Reading excel itself is so slow (using POI), so the performance of the app will go down as excel grows. I believe POI does not support coping whole columns from different sheets and building a new workbook or sheet? If that can be done then it would still be manageable. Reading each row and then each cell to build the new temporary work sheet in the db table structure so as to generate sql, does not seem quite right to me.

GUI XML

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<DataDetails>
    <Page pageId="1">
        <Column columnName="Branch ID">
            <dataType>String</dataType>
            <maxLength>3</maxLength>
            <isMandatory>true</isMandatory>
            <isUnique>true</isUnique>
            <defaultValue></defaultValue>
            <forbiddenCharacters></forbiddenCharacters>
            <limitedChoices></limitedChoices>
            <databaseMapping></databaseMapping>
        </Column>

        <Column columnName="Branch Name">
            <dataType>String</dataType>
            <maxLength>10</maxLength>
            <isMandatory>false</isMandatory>
            <isUnique>true</isUnique>
            <defaultValue></defaultValue>
            <forbiddenCharacters></forbiddenCharacters>
            <limitedChoices></limitedChoices>
            <databaseMapping></databaseMapping>
        </Column>

        <Column columnName="Branch Type">
            <dataType>String</dataType>
            <maxLength>15</maxLength>
            <isMandatory>false</isMandatory>
            <isUnique>true</isUnique>
            <defaultValue></defaultValue>
            <forbiddenCharacters></forbiddenCharacters>
            <limitedChoices></limitedChoices>
            <databaseMapping></databaseMapping>
        </Column>

        <Column columnName="Location">
            <dataType>String</dataType>
            <maxLength>3</maxLength>
            <isMandatory>false</isMandatory>
            <isUnique>true</isUnique>
            <defaultValue></defaultValue>
            <forbiddenCharacters></forbiddenCharacters>
            <limitedChoices></limitedChoices>
            <databaseMapping></databaseMapping>
        </Column>

        <Column columnName="Pincode">
            <dataType>String</dataType>
            <maxLength>3</maxLength>
            <isMandatory>false</isMandatory>
            <isUnique>true</isUnique>
            <defaultValue></defaultValue>
            <forbiddenCharacters></forbiddenCharacters>
            <limitedChoices></limitedChoices>
            <databaseMapping></databaseMapping>
        </Column>
    </Page>
    <Page pageId="2">
        <Column columnName="Business Line">
            <dataType>String</dataType>
            <maxLength>3</maxLength>
            <isMandatory>false</isMandatory>
            <isUnique>true</isUnique>
            <defaultValue></defaultValue>
            <forbiddenCharacters></forbiddenCharacters>
            <limitedChoices></limitedChoices>
            <databaseMapping></databaseMapping>
        </Column>

        <Column columnName="Branch Currency">
            <dataType>Date</dataType>
            <maxLength></maxLength>
            <isMandatory>false</isMandatory>
            <isUnique></isUnique>
            <defaultValue></defaultValue>
            <forbiddenCharacters></forbiddenCharacters>
            <limitedChoices></limitedChoices>
            <databaseMapping></databaseMapping>
        </Column>

        <Column columnName="Action">
            <dataType>String</dataType>
            <maxLength>10</maxLength>
            <isMandatory>false</isMandatory>
            <isUnique>true</isUnique>
            <defaultValue></defaultValue>
            <forbiddenCharacters></forbiddenCharacters>
            <limitedChoices>ComboBoxTest_Single.xlsx - ACTION;ACTION </limitedChoices>
            <headerName>CODE_SUB_ID</headerName>
            <databaseMapping></databaseMapping>
        </Column>
    </Page>
</DataDetails>

DB XML(Proposed)

<?xml version="1.0" encoding="UTF-8"?>
<table name="tablename">
    <column name="column1">
        <dataType>varchar</dataType>
        <length>20</length>
        <nullAllowed>Y</nullAllowed>
        <defaultValue></defaultValue>
        <isPrimaryKey>Y</isPrimaryKey>
    </column>

    <column name="column2">
        <dataType>timestamp</dataType>
        <length></length>
        <nullAllowed>Y</nullAllowed>
        <defaultValue></defaultValue>
    </column>

    <column name="column3">
        <dataType>varchar</dataType>
        <length>20</length>
        <nullAllowed>Y</nullAllowed>
        <defaultValue></defaultValue>
    </column>

    <column name="column4">
        <dataType>decimal</dataType>
        <lengthIntegerPart>24</lengthIntegerPart>
        <lengthFractionalPart>6</lengthFractionalPart>
        <nullAllowed>Y</nullAllowed>
        <defaultValue></defaultValue>
    </column>

    <column name="column5">
        <dataType>integer</dataType>
        <length>1</length>
        <nullAllowed>Y</nullAllowed>
        <defaultValue></defaultValue>
    </column>

    <column name="column6">
        <dataType>varchar2</dataType>
        <length>30</length>
        <nullAllowed>N</nullAllowed>
        <defaultValue></defaultValue>
    </column>

    <column name="column7">
        <dataType>date</dataType>
        <length></length>
        <nullAllowed>Y</nullAllowed>
        <defaultValue></defaultValue>
    </column>

    <column name="column8">
        <dataType>decimal</dataType>
        <lengthIntegerPart>24</lengthIntegerPart>
        <lengthFractionalPart>6</lengthFractionalPart>
        <nullAllowed>N</nullAllowed>
        <defaultValue></defaultValue>
    </column>
</table>

Thanks !!

Upvotes: 0

Views: 151

Answers (1)

trashgod
trashgod

Reputation: 205785

I'd go with an embedded database, such as H2, and synchronize the traveling users' changes upon their return home.

If you pursue your proposed approach, you may be able to leverage this Database Schema Definition Language (DBSDL) that uses "XSLT for generating DDL in the form of SQL statements."

Upvotes: 1

Related Questions