| Panel |
|---|
|
Useful methods frequently used in the system concerning date, SQL and metadata handling. What does this section cover? |
...
| Expand |
|---|
|
truncate(Date date) method returns the date rounded to the day with a time of 00:00:00:00. setEndOfDay(Date date) method returns the date rounded to the day with a time of 23:59:59:999. | Code Block |
|---|
| public DateRange getDateRange(Date fromDate, Date toDate){
if (fromDate!=null && toDate!=null )
{
fromDate = DateUtil.truncate(fromDate);
toDate = DateUtil.setEndOfDay(toDate);
range = new DateRange(fromDate, toDate);
}
return range;
} |
|
...
| Expand |
|---|
|
getSelectSQLDate(String dbType, String datefield) method returns a script which is the given date or date field in the correct form for the given the database type. It works for both SQL and Hibernate queries. | Code Block |
|---|
| title | Hibernate Query Example |
|---|
| public ArrayList<CRMDO> loadEffective(Date asOfDate){
ArrayList<Object> values = new ArrayList<Object>();
String dbType = getCRMSession().getDbtype();
if(expirationDate!=null)
{
values.add(asOfDate);
criteria+= " and " + DateUtil.getSelectSQLDate(dbType, getDOName().toLowerCase() + ".effectiveDate")+"<=:p"+values.size();
criteria+= " and " + DateUtil.getSelectSQLDate(dbType, getDOName().toLowerCase() + ".expirationDate")+">:p"+values.size();
}
return load(getDOName().toLowerCase() + ".isDeleted=0" +criteria,
values,
getDefaultAssociations(),
null,
null);
} |
| Code Block |
|---|
| public Boolean isEffective(CUSTOMCRMDORentalType rentalType, CRMDODate asOfDate){
String dbtype = getCRMSession().getDbtype();
String sql =
"\n SELECT RENTTYPEID FROM TRN_RENTALTYPES RT " +
"\n WHERE RT.RENTTYPEID = ?" +
"\n AND PV.PRODVALIDITYDELETED = 0 " +
"\n AND " + DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEFFECTIVEDATE") + " <= " + DateUtil.getSQLDate(dbtype, asOfDate) +
"\n AND " + DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEXPIRATIONDATE") + " > " + DateUtil.getSQLDate(dbtype, asOfDate) +;
ArrayList<Object> parameters = new ArrayList<Object>();
parameters.add(rentalType.getId());
if (SQLUtil.queryReturnedRows(sql, getOrganisationID(), parameters, dbType))
{
return new Boolean(true);
}
return new Boolean(false);
} |
|
| Expand |
|---|
| title | Add period of time to SQL Date |
|---|
|
addMonthsSQLDate(String dbType, String dateField, int months) method returns a script which adds the given number of months to the given date field. It works for both SQL and Hibernate queries. | Code Block |
|---|
| title | Hibernate Query Example |
|---|
| public ArrayList<CRMDO> loadEffectiveAfterAMonth(Date asOfDate){
ArrayList<Object> values = new ArrayList<Object>();
String dbType = getCRMSession().getDbtype();
if(expirationDate!=null)
{
values.add(asOfDate);
criteria+= " and " + DateUtil.addMonthsSQLDate(dbType, DateUtil.getSelectSQLDate(dbType, getDOName().toLowerCase() + ".effectiveDate"),1)+"<=:p"+values.size();
criteria+= " and " + DateUtil.addMonthsSQLDate(dbType, DateUtil.getSelectSQLDate(dbType, getDOName().toLowerCase() + ".expirationDate"),1)+">:p"+values.size();
}
return load(getDOName().toLowerCase() + ".isDeleted=0" +criteria,
values,
getDefaultAssociations(),
null,
null);
} |
| Code Block |
|---|
| public Boolean isEffectiveAfterAMonth(CUSTOMCRMDORentalType rentalType, CRMDODate asOfDate){
String dbtype = getCRMSession().getDbtype();
String sql =
"\n SELECT RENTTYPEID FROM TRN_RENTALTYPES RT " +
"\n WHERE RT.RENTTYPEID = ?" +
"\n AND PV.PRODVALIDITYDELETED = 0 " +
"\n AND " + DateUtil.addMonthsSQLDate(dbType,DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEFFECTIVEDATE"),1) + " <= " + DateUtil.getSQLDate(dbtype, asOfDate) +
"\n AND " + DateUtil.addMonthsSQLDate(dbType,DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEXPIRATIONDATE"),1) + " > " + DateUtil.getSQLDate(dbtype, asOfDate) +;
ArrayList<Object> parameters = new ArrayList<Object>();
parameters.add(rentalType.getId());
if (SQLUtil.queryReturnedRows(sql, getOrganisationID(), parameters, dbType))
{
return new Boolean(true);
}
return new Boolean(false);
} |
The following method works in a corresponding way: addDaysSQLDate(String dbType, String dateField, int days) method returns a script which adds the given number of days to the given date field. It works for both SQL and Hibernate queries. |
...
SQL handling methods can be found in com.crm.framework.util.SQLUtil class.
| Expand |
|---|
| title | Retrieve results from DBrows using prepared statement |
|---|
|
The following methods return a ResultSetUtil object containing the records returned by the given SQL prepared statement and the list of parameters run on the given DB . In the first method, the maximum number of returned rows can be defined. executeUsingPreparedStatement(String sqlStatement, String dbName, ArrayList<Object> parameters, String dbType, Integer maxRows) executeUsingPreparedStatement(String sqlStatement, String dbName, ArrayList<Object> parameters, String dbType) | Code Block |
|---|
| title | Hibernate Query Example |
|---|
| public ArrayList<CUSTOMCRMDORental> loadLatest(CRMDOAccountReceivable account, Integer numberOfRentals){
ArrayList<CUSTOMCRMDORental> rentals = new ArrayList<CUSTOMCRMDORental>();
ArrayList<Object> parameters = new ArrayList<Object>();
String sql ="\n SELECT RENTALID FROM TRN_RENTALS" +
"\n WHERE RENTALDELETED = 0 " +
"\n AND ACCRECID = ?"+
"\n ORDER BY RENTALCREATEDDATE" ;
parameters.add(account.getId());
ResultSetUtil rsu = SQLUtil.executeUsingPreparedStatement(sql, getOrganisationID(), parameters, getCRMSession().getDbtype(), numberOfRentals);
if(rsu!=null && rsu.next())
{
if(rsu.getString("RENTALID")!=null)
{
rentals .add((CUSTOMCRMDORental)rentalBean.load(rsu.getString("RENTALID")));
}
}
return rentals;
} |
|
| Expand |
|---|
| title | Check if query returned rows |
|---|
|
The following method returns true if the given prepared statement and the list of parameters run on the given DB return at least one row. If not the method returns false. queryReturnedRows(String sqlStatement, String jndi, ArrayList<Object> parameters, String dbType) | Code Block |
|---|
| title | Hibernate Query Example |
|---|
| public Boolean isEffective(CUSTOMCRMDORentalType rentalType, CRMDODate asOfDate){
String dbtype = getCRMSession().getDbtype();
String sql =
"\n SELECT RENTTYPEID FROM TRN_RENTALTYPES RT " +
"\n WHERE RT.RENTTYPEID = ?" +
"\n AND PV.PRODVALIDITYDELETED = 0 " +
"\n AND " + DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEFFECTIVEDATE") + " <= " + DateUtil.getSQLDate(dbtype, asOfDate) +
"\n AND " + DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEXPIRATIONDATE") + " > " + DateUtil.getSQLDate(dbtype, asOfDate) + ;
ArrayList<Object> parameters = new ArrayList<Object>();
parameters.add(rentalType.getId());
if (SQLUtil.queryReturnedRows(sql, getOrganisationID(), parameters, dbType))
{
return new Boolean(true);
}
return new Boolean(false);
} |
|
| Expand |
|---|
| title | Update rows using prepared statement |
|---|
|
The following method runs an update SQL prepared statement on the given DB . executeUpdateUsingPreparedStatement(String sqlStatement, String dbName, ArrayList<Object> parameters, String dbType) | Code Block |
|---|
| title | Hibernate Query Example |
|---|
| public void deleteNonEffective(Date asOfDate, CUSTOMCRMDORentalType type){
ArrayList<Object> parameters = new ArrayList<Object>();
String sql =
"\n UPDATE TRN_RENTALTS " +
"\n SET RENTALDELETED = 1" +
"\n WHERE RENTALTYPEID = ?" +
"\n AND "+ DateUtil.getSelectSQLDate(dbtype, "RT.RENTTYPEEXPIRATIONDATE") + " < " + DateUtil.getSQLDate(dbtype, asOfDate);
parameters.add(type.getId());
ResultSetUtil rsu = SQLUtil.executeUsingPreparedStatement(sql, getOrganisationID(), parameters, getCRMSession().getDbtype(), numberOfRentals);
SQLUtil.executeUpdateUsingPreparedStatement(
sql,
getOrganisationID(),
parameters,
getCRMSession().getDbtype());
} |
|
Metadata Handling Methods
...