TreeGrid JSP Framework

The framework contains support Java functions to help you with generating and parsing TreeGrid XML data and reading it from and upating it to database in server side script.
You need not use this framework, it contains only helper functions to simplify writting your code.
The framework is located in single file TreeGridFramework.jsp and can be included into your JSP pages by standard include mechanism <%@include file="TreeGridFramework.jsp"%>.
Remember, if you are generating XML files, be careful to not generate any white space before the first < character.

Parsing uploaded XML

Specific functions for parsing TreeGrid output XML changes

Document parseXML (String XML)
Returns XML Document from String XML.
Returns null when XML is empty or not valid.
XML is complete data XML uploaded by TreeGrid.

Element[] getChanges (String XML)

Element[] getChanges (Document XML)
Returns all changed rows - array of XML <I> elements in <Changes> tag.
XML is complete data XML uploaded by TreeGrid.

int getPagePos (String XML)

int getPagePos (Document XML)
Returns page number (<B Pos='000'/>) from input XML. By this number is page identified for server paging.
XML is complete data XML uploaded by TreeGrid.

String getPageId (String XML)

String getPageId (Document XML)
Returns page id attribute (<B id='xxx'/>) from input XML. By this id is row identified to get its children when server child paging is used.
XML is complete data XML uploaded by TreeGrid.

String[] getSortCols (String XML)

String[] getSortCols (Document XML)
Returns array of columns according to is grid sorted. Returns null if there are no columns.
XML is complete data XML uploaded by TreeGrid.

int[] getSortTypes (String XML)

int[] getSortTypes (Document XML)
Returns array of sorting types for columns according to is grid sorted. Returns null if there are no columns.
XML is complete data XML uploaded by TreeGrid.

String[] getGroupCols (String XML)

String[] getGroupCols (Document XML)
Returns array of columns according to is grid grouped. Returns null if there are no columns.
XML is complete data XML uploaded by TreeGrid.

int[] getGroupTypes (String XML)

int[] getGroupTypes (Document XML)
Returns array of grouping types for columns according to is grid grouped. Returns null if there are no columns.
XML is complete data XML uploaded by TreeGrid.

boolean isAttribute (Element I, String name, String value)
Compares value of row I attribute name with given value.

boolean isDeleted (Element I)

boolean isAdded (Element I)

boolean isChanged (Element I)

boolean isMoved (Element I)
Tests if given row I has set the flag. For Moved tests equality to "2" (moved to another parent).

boolean getParameter (HttpServletRequest request,String name)
Returns request parameter value, for null returns "".

Generating SQL commands

Universal functions for generating SQL commands from strings or values in XML attributes

String toSQL (String value)
Returns string in quotes (') and with doubled all quotes.

String toSQL (Element I, String name)
Returns value of XML attribute in quotes (') and with doubled all quotes.
If attribute does not exist, returns two single quotes ('').

String toSQLUpdateString (String name, String value)
Returns one item for UPDATE command, for string types. Returns "name='value',".
If value is null, returns empty string.

String toSQLUpdateString (String name, Element I, String attributeName)
Returns one item for UPDATE command, for string types. Returns "name='I.getAttribute(attributeName)',". For example "A='Peter''s bug',".
If attribute does not exist, returns empty string.

String toSQLUpdateNumber (String name, String value)
Returns one item for UPDATE command, for number types. Returns "name=value,".
If value is null or empty string, returns empty string.

String toSQLUpdateNumber (String name, Element I, String attributeName)
Returns one item for UPDATE command, for number types. Returns "name=I.getAttribute(attributeName),". For example "A=3,".
If attribute does not exist, returns empty string.

String trimSQL (String value)
Deletes all commas on the end of string value. Useful when building comma separated list in loop, call it after list is built to strip ending comma(s).

String toSQLInsert (Element I, String[] attrNames, boolean[] attrIsString)
Returns values of row (XML element) I attributes in attrNames, separated by comma.
The string never ends by comma.
If the attribute does not exists, it is not included.
If attrIsString[n] is true, it encloses attribute value by ' '.
This function can be used for SQL INSERT command generating. Example:

Element I;    // I is XML for example "<I id='7' Project='Main' Resource='This' Week='4' Hours='13'/>
String[] Names = {"Project","Resource","Week","Hours"}; 
boolean[] IsString = {false,true,true,false,false};
String SQL = "INSERT INTO TableData(ID,Project,Resource,Week,Hours) VALUES(" + getAttributes(I,Names,IsString) + ")";

String toSQLUpdate (Element I, String[] attrNames, String[] outNames, boolean[] attrIsString)
Returns row (XML element) I attribute values in format "outNames[0]=value_of_attrNames[0],outNames[1]=value_of_attrNames[1], ...".
The string never ends by comma.
If the attribute does not exists, it is not included.
If attrIsString[n] is true, it encloses attribute value by ' '.
This function can be used for SQL UPDATE command generating. Example:

Element I;    // I is XML for example "<I id='7' Project='Main' Hours='13'/>
String[] Names = {"Project","Resource","Week","Hours"}; 
boolean[] IsString = {true,true,false,false};
String SQL = "UPDATE TableData SET " + getAttributes(I,Names,Names,IsString) + " WHERE ID=" + getAttribute(I,"id");

Updating changes in XML to database

Advanced specific functions for updating changes got in TreeGrid XML to database table.

boolean saveTree (Statement Cmd, String table, String idCol, String parentCol, String parentBody) throws SQLException
Saves changes in XML to database table by SQL statement Cmd.
idCol is database table column name where is stored id attribute.
parentCol is database table column name where is stored Parent attribute, for Parent<-Child relation. In Parent column the row has value of id column of parent row => All parent's children have its id in their Parent column.
bodyParent is value of Parent for new added root rows.
This function expects that all other attribute names are the same as column names in database table.

Creating XML

Universal functions for creating XML.

String toXMLString (String value)
Returns value with replaced & ' < by XML entities

String fromXMLString (String value)
Returns value with replaced XML entities by & ' < " > characters.

String toHTMLString (String value)
Returns value with replaced & " by XML entities.
Useful for direct writing generated XML to hidden input value on HTML page. Don't forget to use double quotes to enclose the string.

String toXML (String value)

void toXML (StringBuffer S, String value)
Returns value in single quotes (') with replaced XML entities by & ' < characters.

String toXML (short value)

String toXML (int value)

String toXML (long value)

String toXML (boolean value)

String toXML (float value)

String toXML (double value)
Returns value in single quotes (').

String toXML (String name, String value)

void toXML (StringBuffer S, String name, String value)
Returns " value='value'", value with replaced XML entities by & ' < characters.

String toXML (String name, ResultSet R, String columnName)

void toXML (StringBuffer S, String name, ResultSet R, String columnName)
Returns " name='R.getString(columnName)'", value with replaced XML entities by & ' < characters.
ResultSet must be opened and cursor must be on some row.

Advanced creating XML

Specific functions for creating TreeGrid XML.

String getRowXML (String[] names, String[] values)

String getRowXML (String[] names, String[] values,String end)

String getRowXML (ResultSet R, String[] names, String[] colNames) throws SQLException

String getRowXML (ResultSet R, String[] names, String[] colNames, String end) throws SQLException

String getRowXML (ResultSet R, String[] names, int[] colIndexes) throws SQLException

String getRowXML (ResultSet R, String[] names, int[] colIndexes, String end) throws SQLException

String getRowXML (ResultSet R, String[] names) throws SQLException

String getRowXML (ResultSet R, String[] names, String end) throws SQLException
Returns XML representation of one row with given attribute names and their values in array or from ResultSet R in columns colNames or colIndexes or in columns in order according to names.
end is the string appended after row, for rows with children use ">", without children "/>" (default), or you can set own attributes.
Does not move cursor in ResultSet.
Remember when colIndexes are used, columns are indexed from 1 !
Example:

String[] Names = {"id","Project","Resource","Week","Hours"}; 
String[] Values = {"7", "Main","This","4","13"};
String XML = getRowXML(Names,Values); // The result will be: "<I id='7' Project='Main' Resource='This' Week='4' Hours='13'/>"

String getTableXML (ResultSet R, String[] names, String[] colNames) throws SQLException

String getTableXML (ResultSet R, String[] names, String[] colIndexes) throws SQLException

String getTableXML (ResultSet R, String[] names) throws SQLException
Returns complete (include <Grid> tag) XML representation of all rows with given attribute names and their values from ResultSet R in columns colNames or colIndexes or in columns in order according to names.
Remember when colIndexes are used, columns are indexed from 1 !
Example result:

<Grid><Body><B>
   <I id='7' Project='Main' Resource='This' Week='4' Hours='13'/>
   <I id='8' Project='Main' Resource='That' Week='3' Hours='8'/>
</B></Body><Grid>

String getTableXML (ResultSet R) throws SQLException

String getTableXML (ResultSet R, String idCol) throws SQLException
Returns complete (include <Grid> tag) XML representation of all rows with given attribute names and their values from ResultSet R in the same named attributes.
Remember, some SQL databases have case insensitive column names and TreeGrid XML has always case-sensitive names.
idCol is name of database column that will be stored to id attribute.

String getTreeXML (Statement Cmd, String table, String[] names, String[] colNames, String bodyParent) throws SQLException

String getTreeXML (Statement Cmd, String table, String[] names, String[] colNames, String bodyParent, String headParent, String footParent, boolean deep) throws SQLException

String getTreeXML (Statement Cmd, String table, String[] names, String bodyParent) throws SQLException

String getTreeXML (Statement Cmd, String table, String[] names, String bodyParent, String headParent, String footParent, boolean deep) throws SQLException
Returns complete (include <Grid> tag) XML representation of all rows with given attribute names and their values from ResultSet R in columns indexed as attribute names or when used colNames returns only columns in this array in the same order as names attributes.
It is used for generating tree from one table that has parent->id relation.
table is name of database table names are TreeGrid attribute names in order in that are filled from columns from database table colNames are names of columns in database used instead of indexes. Must be in the same order as names array. Can contain only some columns from database table.
names must contain name "id", this is identity attribute to unique identify row when uploading and in tree.
names must also contain name "Parent", this is column related to id in Parent<-Child. In Parent column the row has value of id column of parent row => All parent's children have its id in their Parent column.
For deep==false reads only one level of tree, for server side child paging.

String getTreeXML (Statement Cmd, String table, String[] names, String[] colNames, String parentVal, boolean deep) throws SQLException

String getTreeXML (Statement Cmd, String table, String[] names, String parentVal boolean deep) throws SQLException
Returns partial XML, returns children of row with id = parentVal => returns all rows that have in their Parent column value parentVal.
Other parameters are the same as in previous function getTreeXML.

Support for examples

Statement getHsqlStatement (HttpServletRequest request, JspWriter out, String dbPath, String user, String pass) throws Exception
Creates SQL statement for SQL database HSQLDB that is used for examples.
HSQLDB is simple database in text files without need of any driver instalation. Just copy file hsqldb.jar file to your shared lib directory and restart http server.
request and out are just the variables in main servlet functions.
dbPath is path and name of database file(s), relative to location of the script file.
user and pass are login information to database.
If database driver cannot be executed, because hsqldb.jar file is missing or not accessible it writes this information to output, closes it and raises Exception.