Netbeans for the Coffee Drinker

I’m working on a huge project with some multi tab screens and I noticed two things about the code, firstly my init code had a lot of changes to the default SQL providers, mainly for  things where tables had links to other tables, parent child relationships. The more I added, the slower it got  - plus my code got quite messy.

So - just to tidy up the code, I wrote a class below that has some functions for changing data providers. Guess what, not only was my code neater but the pages speeded up very noticably when loading. - Why not comment if you have a clue why, personally I think its something to do with the way java handles classes that are called a lot.

I declare one change object at the start of the JSF java code.

DataProviderSQLChanger change = new DataProviderSQLChanger();

Then I can use the change object whenever I like in the init code and throughout the page…

change.ChangeSQL(tblsubservicetypesDataProvider, "select * FROM tblsubservicetypes");

Functions

public void ChangeSQL(CachedRowSetDataProvider dataProvider, String sNewSQL)

This routine changes the SQL of a cached data provider - however it does look to see if the SQL your are submitting is what is already on the cached data provider so we don’t waste time reloading schemas etc.

public void ParentChildLink(
            CachedRowSetDataProvider parentDataProvider,
            String sParentFieldName,
            CachedRowSetDataProvider childDataProvider,
            String sChildFieldName,
            boolean bIsText)

 This Parentchildlink is for when you have a parent child relationship for example, when I have a “MAIN SUBFORM” Idea. It’s pretty straight forward (see the code for documentation and parameters).

public void ForceParentChildLink(
            String sKey,
            CachedRowSetDataProvider parentDataProvider,
            String sParentFieldName,
            CachedRowSetDataProvider childDataProvider,
            String sChildFieldName,
            boolean bIsText)

This is the same as parent child link but FORCES the Parent CachedRowSetDataProvider to look only to the record selected.

Anyway - here’s the class, have fun!

import com.sun.data.provider.impl.CachedRowSetDataProvider;
import java.sql.SQLException;
/**
 * This is an easier way to change a Data Providers SQL and manage data providers
 * By Paul Clevett http://www.netbeansboy.com
 * @author paulc
 */
public class DataProviderSQLChanger {
    /**
     * Changes the SQL Statement of a Dataprovider
     * If the sql statement is already set to that, just return, refreshing the data provider
     * @param dataProvider
     * @param sNewSQL
     */
    public void ChangeSQL(CachedRowSetDataProvider dataProvider, String sNewSQL) {
        // Is the dataproviders sql already set?
        if (!dataProvider.getCachedRowSet().getCommand().equals(sNewSQL)) {
            try {
                dataProvider.getCachedRowSet().setCommand(sNewSQL);
            } catch (SQLException ex) {
                System.out.println("SQL Error: " + ex);
            // Logger.getLogger(DataProviderSQLChanger.class.getName()).log(Level.SEVERE, null, ex);
            }

        }
        dataProvider.refresh();
    }
    /**
     * Manage the Parent Child Link between two cached data providers
     * gives much clearer code, note that this routinedoes not change the parents
     * data provider's SQL unlike ForceParentChildLink
     * @param parentDataProvider    - Parent Data Provider
     * @param sParentFieldName      - Parent Field name
     * @param childDataProvider     - Child Data Provider
     * @param sChildFieldName       - Child Field name
     * @param bIsText               - if the link is a text link
     */
    public void ParentChildLink(
            CachedRowSetDataProvider parentDataProvider,
            String sParentFieldName,
            CachedRowSetDataProvider childDataProvider,
            String sChildFieldName,
            boolean bIsText) {
        // Sets up the link between parent and childe data providers
        String sOrderBy="";
        String sSQL = childDataProvider.getCachedRowSet().getCommand();
        if (sSQL.indexOf("ORDER BY")!=-1) sOrderBy = sSQL.substring(sSQL.indexOf("ORDER BY"));
        // Now remove any existing where's
       
        if (sSQL.indexOf("WHERE")!=-1) sSQL = sSQL.substring(0, sSQL.indexOf("WHERE") - 1);
        // Add the Query
        if (bIsText) {
           
            sSQL = sSQL.concat(" WHERE " + sChildFieldName + "='" + parentDataProvider.getValue(sParentFieldName).toString().trim() + "'");
           
        } else {
           
            sSQL = sSQL.concat(" WHERE " + sChildFieldName + "=" + parentDataProvider.getValue(sParentFieldName));
           
        }
        sSQL=sSQL.concat(sOrderBy);
       
        // Execute the query on the child
        ChangeSQL(childDataProvider,sSQL);
    }
   
    /**
     * Syncs up the parent/child relationship between two data providers with
     * a key
     * @param sKey                  - Parents Key Value or blank if the fieldname is the same as the sParentNameField
     * @param parentDataProvider    - Parents Data Provider
     * @param sParentFieldName      - Parents FieldName
     * @param childDataProvider     - Childs Data Provider
     * @param sChildFieldName       - Childs Field Name
     * @param bIsText               - Is the key Text or Numeric? (TRUE/FALSE)
     */
    public void ForceParentChildLink(
            String sKey,
            CachedRowSetDataProvider parentDataProvider,
            String sParentFieldName,
            CachedRowSetDataProvider childDataProvider,
            String sChildFieldName,
            boolean bIsText) {
        // Sets up the link between parent and childe data providers
        String sOrderBy="";
        String sSQL= parentDataProvider.getCachedRowSet().getCommand();
        if (sKey.equals("")) sKey=parentDataProvider.getValue(sParentFieldName).toString();
        if (sSQL.indexOf("ORDER BY")!=-1) sOrderBy = sSQL.substring(sSQL.indexOf("ORDER BY"));
        // Now remove any existing where's
       
        if (sSQL.indexOf("WHERE")!=-1) sSQL = sSQL.substring(0, sSQL.indexOf("WHERE") - 1);
        // Add the Query
        if (bIsText) {
           
            sSQL = sSQL.concat(" WHERE " + sParentFieldName + "='" + sKey + "'");
           
        } else {
           
            sSQL = sSQL.concat(" WHERE " + sParentFieldName + "=" + sKey);
           
        }
        sSQL=sSQL.concat(sOrderBy);
        // Force the parent over to look at the particular row
        ChangeSQL(parentDataProvider,sSQL);
       
       
        // Now the child
        sSQL = childDataProvider.getCachedRowSet().getCommand();
        if (sSQL.indexOf("ORDER BY")!=-1) sOrderBy = sSQL.substring(sSQL.indexOf("ORDER BY"));
        // Now remove any existing where's
       
        if (sSQL.indexOf("WHERE")!=-1) sSQL = sSQL.substring(0, sSQL.indexOf("WHERE") - 1);
        // Add the Query
        if (bIsText) {
           
            sSQL = sSQL.concat(" WHERE " + sChildFieldName + "='" + parentDataProvider.getValue(sParentFieldName) + "'");
           
        } else {
           
            sSQL = sSQL.concat(" WHERE " + sChildFieldName + "=" + parentDataProvider.getValue(sParentFieldName));
           
        }
        sSQL=sSQL.concat(sOrderBy);
       
        // Execute the query on the child
        ChangeSQL(childDataProvider,sSQL);
    }
   
   
   
}