The aim of Squirt is to provide a tool to autogenerate stub code for BO-BDO files. This code is generated for the business area to call upon the DO layer. In most cases, little or no hand modification is required to the stub code generated.
The first version of this tool generates code for specific types of queries, namely those that result in zero or more DODS created DOs.
Squirt generates code into a directory specified on the command line. Each code stub will be placed in a file called "queryname.squirt". Invoking squirt without parameters gives the usage statement.
The QUERY configuration section is the main section used by Squirt. Note that there are three types of queries for which Squirt generates code: OBJECTID, EASY and CUSTOM.
OBJECTID query finds a single DO based on the ObjectId passed. Only the DO & TYPE subsections in QUERY are required for this type.
Here is an example of the query code generated for this QUERY section to find a person:
QUERY.findCompany.DO = Company
QUERY.findCompany.TYPE = OBJECTID
####
// 'cache' for query - createExisting can be a time expensive call... private String findCompany_ObjectId = ""; private CompanyDO findCompany_DO = null; /** * findCompany: generated by Squirt! This query is for * finding a record in a table by ObjectId. The row * must be in the table, otherwise an exception will * be thrown. * * @param objectid The record to retrieve from Company * @return an array of CompanyDOs for this query * @exception ApplicationException All exceptions are caught and redirected to this. */ public CompanyDO findCompany(String objectid) throws ApplicationException { // have we just referenced this object id? if (objectid.equals(findCompany_ObjectId)) { // yes, use our cached version return findCompany_DO; } CompanyDO result = null; try { java.math.BigDecimal bd = new java.math.BigDecimal(objectid); result = CompanyDO.createExisting(bd); // keep details in our cache findCompany_ObjectId = objectid; findCompany_DO = result; } catch (Exception e) { throw new ApplicationException("Unable to create existing Company Data Object", e ); } // return the results found, if any return result; }####
This code is generated into a file called 'findCompany.squirt' in the output directory.
EASY query uses the DODS Query class to set the various parameters for searching. It assumes that the fields in the where clause are ANDed together. The PARAMETERS section is used to determine the fields in the where clause. If this option is not set, then the where clause is empty, so the results of the query will be all records from the table.
Here is an example of the query code generated for this QUERY section to find people using zero or more of the parameters passed:
QUERY.getPerson.DO = Person
QUERY.getPerson.TYPE = EASY
QUERY.getPerson.PARAMETERS[] = FirstName, Surname, Title, Email, Phone, Fax, Type, UserName, CompanyId
QUERY.getPerson.PARAMTYPES[] = String, String, String, String, String, String, String, String, CompanyDO
QUERY.getPerson.ORDERBY[] = "Surname", "FirstName"
####
/** * getPerson: generated by Squirt! * *####* @return an array of PersonDOs for this query * @exception ApplicationException All exceptions are caught and redirected to this. */ public PersonDO[] getPerson(String FirstName, String Surname, String Title, String Email, String Phone, String Fax, String Type, String UserName, CompanyDO CompanyId) throws ApplicationException { PersonDO[] results = null; PersonQuery query = new PersonQuery(); try { if (ProdUtil.stringHasContent(FirstName )) { query.setQueryFirstName( FirstName ); } if (ProdUtil.stringHasContent(Surname )) { query.setQuerySurname( Surname ); } if (ProdUtil.stringHasContent(Title )) { query.setQueryTitle( Title ); } if (ProdUtil.stringHasContent(Email )) { query.setQueryEmail( Email ); } if (ProdUtil.stringHasContent(Phone )) { query.setQueryPhone( Phone ); } if (ProdUtil.stringHasContent(Fax )) { query.setQueryFax( Fax ); } if (ProdUtil.stringHasContent(Type )) { query.setQueryType( Type ); } if (ProdUtil.stringHasContent(UserName )) { query.setQueryUserName( UserName ); } if (ProdUtil.stringHasContent(CompanyId )) { query.setQueryCompanyId( CompanyId ); } query.addOrderBySurname(); query.addOrderByFirstName(); results = query.getDOArray(); } catch (DataObjectException doe) { throw new ApplicationException( "Unable to create existing Person Data Object", doe ); } catch ( NonUniqueQueryException nuqe ) { // ...this exception will only occur is requireUniqueInstance was called. throw new ApplicationException( "Non unique result!", nuqe ); } // return the results found, if any return results; }
This code is generated into a file called 'getPerson.squirt' in the output directory.
The above code shows one of the current limitations of squirt. The line "if (ProdUtil.stringHasContent(CompanyId )) {" is the wrong for the data type of the parameter CompanyId. In this case the code should probably be "if (CompanyId != null) {". This is an example of possible tweaking required from the generated squirt code.
A future version of squirt will attempt to handle more than just String type parameters for the null (or "empty" value) detection.
CUSTOM queries allow the specification of arbitrary where clauses. The SQL subsection contains important details used in generation of the code.
Here is an example of the query code generated for this QUERY section to find all Time Entries for a person between two dates:
QUERY.getTimeEntryRecords.DO = TimeEntry
QUERY.getTimeEntryRecords.TYPE = CUSTOM
QUERY.getTimeEntryRecords.PARAMETERS[] = PersonId, FromDate, ToDate
QUERY.getTimeEntryRecords.PARAMTYPES[] = String, String, String
QUERY.getTimeEntryRecords.SQL.WHERE.clause1.CODE = "PersonId = <PersonId>"
QUERY.getTimeEntryRecords.SQL.WHERE.clause2.OPERATOR = "AND"
QUERY.getTimeEntryRecords.SQL.WHERE.clause2.CODE = "EntryDate BETWEEN '<FromDate>' AND '<ToDate>'"
QUERY.getTimeEntryRecords.SQL.WHERE.clause3.CODE = "ORDER BY EntryDate, ObjectId"
####
/** * getTimeEntryRecords: generated by Squirt! * *####* @return an array of TimeEntryDOs for this query * @exception ApplicationException All exceptions are caught and redirected to this. */ public TimeEntryDO[] getTimeEntryRecords(String PersonId, String FromDate, String ToDate) throws ApplicationException { String WHERE = " WHERE "; String sql = null; StringBuffer sqlbuf = new StringBuffer(); TimeEntryDO[] results = null; TimeEntryQuery query = new TimeEntryQuery(); boolean operator_required = false; sqlbuf.append(WHERE); // test for null parameters, if required if (ProdUtil.stringHasContent(PersonId)) { // need to concatentate this part of the where clause with previous details? if (operator_required) { sqlbuf.append(' '); sqlbuf.append(" "); sqlbuf.append(' '); } sqlbuf.append("PersonId = "); sqlbuf.append(PersonId); sqlbuf.append(""); operator_required = true; } // test for null parameters, if required if (ProdUtil.stringHasContent(FromDate) && ProdUtil.stringHasContent(ToDate)) { // need to concatentate this part of the where clause with previous details? if (operator_required) { sqlbuf.append(' '); sqlbuf.append("AND"); sqlbuf.append(' '); } sqlbuf.append("EntryDate BETWEEN '"); sqlbuf.append(FromDate); sqlbuf.append("' AND '"); sqlbuf.append(ToDate); sqlbuf.append("'"); operator_required = true; } // test for null parameters, if required if (true) { // need to concatentate this part of the where clause with previous details? if (operator_required) { sqlbuf.append(' '); sqlbuf.append(" "); sqlbuf.append(' '); } sqlbuf.append("ORDER BY EntryDate, ObjectId"); operator_required = true; } sql = sqlbuf.toString(); // ensure there's something for the where clause if (!sql.equalsIgnoreCase(WHERE)) query.setArbitraryWhereClause(sql); try { results = query.getDOArray(); } catch (DataObjectException doe) { throw new ApplicationException( "Unable to create existing TimeEntry Data Object", doe ); } catch ( NonUniqueQueryException nuqe ) { // ...this exception will only occur if requireUniqueInstance was called. throw new ApplicationException( "Non unique result!", nuqe ); } // return the results found, if any return results; }
This code is generated into a file called 'getTimeEntryRecords.squirt' in the output directory.
Note that the dates passed to this method were given as type String. In most cases, it's probably best to pass String values to CUSTOM generated queries, as the code is basically creating the SQL WHERE clause for the query. Also, these queries are often called with data supplied by the presentation layer (i.e. input forms, etc), so the data type is already a String. Finally, no hand editing is required if String types are passed.
In most cases, the code generated is inserted into the BDO-BO class appropriate for the query. For example, the findCompany.squirt code would be put into the CompanyBO class.
This code is then called from the get[ACCESS_NAME] method in an appropriate POBO. For example, in the class ReportPeriodResultsBO, the presentation layer refers to an access method called 'getTimeEntryTable'. In the code for this query, a call to the custom query, 'getTimeEntryRecords' is hand coded. The code looks like this:
####/** * getTimeEntryTable: Query method for use by the PO layer. * * @param qsFromDate Parameter supplied by PO * @param qsToDate Parameter supplied by PO * @return The number of records found. * @exception ApplicationException All exceptions are caught and redirected to this. */ public int getTimeEntryTable(String qsFromDate, String qsToDate) throws ApplicationException { /* * Insert your code to do your query. Put the result into * the variable TimeEntryTable as defined above. getIdValue * will use the TimeEntryTable where appropriate. * */ // Hand coding starts here String fromDate = FormatUtil.displayDateToSQL(qsFromDate, null); String toDate = FormatUtil.displayDateToSQL(qsToDate, null); // any problems with the dates? if (!ProdUtil.stringHasContent(fromDate)) { throw new ApplicationException("From Date is invalid: <"+qsFromDate+">"); } if (!ProdUtil.stringHasContent(toDate)) { throw new ApplicationException("To Date is invalid: <"+qsToDate+">"); } TimeEntryTable = timeEntryBO.getTimeEntryRecords(operator_id, fromDate, toDate); // Hand coding ends here // determine the number of records found return (TimeEntryTable == null) ? 0 : TimeEntryTable.length; }####
In the hand coded area above, the dates passed by the presentation layer are converted to SQL type dates. Also, the operator_id passed to the getTimeEntryRecords method is the object id, as a String, of the operator using the application. This value was setup elsewhere.
Note: All code shown in this document can be found in the Platypus tutorial.