Page Comparison

Panel

name	blue

Useful methods frequently used in the system concerning date, SQL and metadata handling.

What does this section cover?

Table of Contents

...

Expand

title	Truncate Date

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

title	Example

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

title	Retrieve rows 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());
}

...

Expand

title	Get entity by class, id, type entity id and table

The following methods return an Entity object which holds information about the entity as defined in entities metadata file.

getEntityByClass(String className, String context, String mpID)

getEntity(String entityID, String context, String mpID)

Code Block

title	Hibernate Query Example

...
Entity rentalEntity = MetadataUtil.getEntityByClass(CUSTOMCRMDORental.getClass().getName(), getCRMSession().getRealPath(), getOrganisationID());
String entityId = rentalEntity.getId();
String moduleId = rentalEntity.getModuleid();
String typeEntityId = rentalEntity.getTypeentityid();	
Entity typeEntity = MetadataUtil.getEntity(typeEntityId, getCRMSession().getRealPath(), getOrganisationID());		
...

getEntityByTable(String tableName, String context, String mpID)

getEntityByTypeEntity(String typeEntityID, String context, String mpID)

Code Block

title	Hibernate Query Example

...
Entity rentalTypeEntity = MetadataUtil.getEntityByTable("TRN_RENTALTYPES", getCRMSession().getRealPath(), getOrganisationID());
Entity rentalEntity = MetadataUtil.getEntityByTypeEntity(rentalTypeEntity.getId() , getCRMSession().getRealPath(), getOrganisationID());
...

Expand

title	Get module by id

The following method returns a Module object which holds information about the module as defined in modules metadata file.

getModuleByID(String moduleID, String context, String mpid)

Code Block

title	Hibernate Query Example

...
String moduleId = rentalEntity.getModuleid();
Module module = MetadataUtil.getModuleByID(moduleId, getCRMSession().getRealPath(), getOrganisationID());		
...

Expand

title	Get module additional process

The following method returns a Process object which holds information about the process as defined in modules metadata file.

getModuleAdditionalProcess(Module module, String processID)

Code Block

title	Hibernate Query Example

...
Process process = MetadataUtil.getModuleAdditionalProcess(rentalsModule, "CUSTOM_SET_RENTAL_AS_EFFECTIVE");	
ArrayList<Method> methods = process.getMethods();
...

Expand

title	Get Variable

The following method returns a global variable value as defined in variables metadata file.

getVariable(String variableName, String context, String mpID)

Code Block

title	Hibernate Query Example

...
String testVariableValue= MetadataUtil.getVariable("myvariable", getCRMSession().getRealPath(), getOrganisationID());
...

Versions Compared

Old Version 13

New Version 14

Key