Parsing Data from Web Layer to DB

Owned by kiran.kumari@intellectdesign.com (Unlicensed)
Last updated: Mar 02, 2020 by Ravikumar Babu CTOO (Unlicensed)

Canvas provides the default support for iBATIS, which is a persistence layer that works on the concept of query-based abstraction and an XML-based modeling of the DML.

While iBATIS is a key aspect, if your application demands an adapter for any other Data Access Layer or framework like Hibernate, etc. you can create your own custom implementation in the Persistence Layer as follows:


To parse the data, perform the following steps:

  1. Submit a AJAX Request in your JS listener class for the Product Code, Sub Product, and Function Codes entitled along with the model data.
    The following keys must be keys must be sent as parameters in the AJAX request in the JS listener:

    // Mandatory keys for AJAX request:
"PAGE_CODE_TYPE" : '<PAGE_CODE_TYPE>',
"INPUT_PRODUCT" : '<PRODUCT_CODE>',
"INPUT_SUB_PRODUCT" : '<SUB_PRODUCT_CODE>',
"INPUT_FUNCTION_CODE" : '<FUNCTION_CODE>',
"PRODUCT_NAME" : '<PRODUCT_NAME>'
"INPUT_ACTION" : '<ACTION_CODE>',

// If sending JSON in the AJAX request, the following additional keys are needed:
"JSON_TO_HASH_MAP_SUPPORT_FLAG" : "<SOME_KEY>",
"<SOME_KEY>" : <JSON_OBJECT>

    For the Transaction Payment form, add the request as follows in the Submit button handler:

    CABR.registerHandler('SUBMIT', 'PAYMENTS_CONTAINER', function (config)
{
	var fm = new canvas.form.FormManager(
	{
		formId : "PAYMENT_FORM"
	}); 

	var mData = fm.model.getModelData(); 

	var paymenttxnData = 
	{
		'ACC_NO' : mData.TXT_DBIT_AC_NO,
		'ACC_NAME' : mData.TXT_DBIT_AC_NAME
		'BENE_ACC_NO' : mData.TXT_BEN_AC_NO,
		'BENE_NAME' : mData.TXT_BEN_AC_NAME,
		'TRANS_DATE' : mData.TRANS_DATE,
		'TRANS_AMNT' : mData.TXT_TRANS_AMNT,
		"PAGE_CODE_TYPE" : 'PMNT_TRNFR',
		"INPUT_PRODUCT": 'PAYMNT',
		"INPUT_SUB_PRODUCT": 'BKSIFT',
		"INPUT_FUNCTION_CODE" : 'PAYXFER',
		"PRODUCT_NAME" : 'CUSER',
		"INPUT_ACTION" : 'GET_TXN'
	};
	canvas.ajax(
	{
		params : paymenttxnData,
		
		success : function (result, request)
		{
			alert("Record added successfully");
		},
		
		failure : function (result, request)
		{
			alert("Error, something went wrong");
		}
	});
});

  2. Create a forward mapping in the forward mapping file to an action class that forwards the Ajax request to the server.
    The forward mapping files are simple mapping files that contain the action class for each combination of Screen Code (Added as PAGE_CODE_TYPE in AJAX parameters in JS Listener), product code, Sub Product Code, and Function Code. These files should be registered with ActionMapRegistry in the application Startup Servlet to get the requests raised from the client and forward it to the Server. The typical structure of the forward mapping file is:

    <action-mapping>
	<action-map 
        screenCode= "PAGE_CODE" 
        prodCode = "PROD_CODE" 
        subProdCode = "SUB_PROD_CODE" 
        funcCode = "FUNCT_CODE" 
        host = "HOST_CODE">
		<action-class>action class path</action-class>
	</action-map>
</action-mapping>

    For the Payment Transaction form, create a mapping file (payment-forward.mapping) as follows:

    <action-map 
    screenCode = "PMNT_TRNFR" 
    prodCode = "PAYMNT" 
    subProdCode = "BKSIFT" 
    funcCode = "PAYXFER" 
    host = "PYTTXNHOST">

	<action-class> com.intellectdesign.modelhouse.PymtTxnAction </action-class>
</action-map>

    Also, add the mapping file in the start-up servlet as follows:

    try
{
	ActionMapRegistry.setCustomInstanceIndexer(ClassicalActionMapIndexer.class);
	ActionMapRegistry actionMapRegistry = ActionMapRegistry.getCustomInstance();
	actionMapRegistry.register("coresvs-forward.mapping"); 
	//Adding the mapping file in the startup servlet
	actionMapRegistry.register("payment-forward.mapping"); 
} 
catch (BaseException e)
{
	LOGGER.error("Unable to start ActionMapRegistry", e);
}

  3. The given mapping file expects the PymtTxnAction action class to read the client request. Create the action class extending the Canvas's PortletAction class that validates the user entitlement and formats the request data as JSON request to the server:

    package com.intellectdesign.modelhouse; 
import java.util.Map; 
import javax.servlet.http.HttpServletRequest; 
import com.intellectdesign.canvas.action.PortletAction;
import com.intellectdesign.canvas.common.ReplyObject;
import com.intellectdesign.canvas.exceptions.action.OrbiActionException;
import com.intellectdesign.canvas.exceptions.common.ProcessingErrorException;
import com.intellectdesign.canvas.login.sessions.SessionInfo;
import com.intellectdesign.canvas.web.config.ActionMap;
import com.intellectdesign.canvas.data.conversion.util.JSONObjectBuilderForExtJs; 

public class PymtTxnAction extends PortletAction
{
	@Override
	public ReplyObject executePortletActionUsing(String action, SessionInfo sessionInfo, 
												ActionMap actionMap, Map requestParams, 
												HttpServletRequest request) throws OrbiActionException
	{
		ReplyObject reply = null;
		try
		{
			reply = executeHostRequest(sessionInfo, actionMap.getHostCode(), requestParams, request);
			JSONObjectBuilderForExtJs.buildFormResultMap(reply);
		} 
		catch (ProcessingErrorException procExp)
		{
			throw new OrbiActionException("System Error:"+ procExp);
		}
		return reply;
	}
}

  4. In the handler properties file (which is defined for the HANDLER_PROPERTIES_FILE key in System Preference Configurator), Map the handler class to the Host Code to handle all the requests from the host (defined in the forward mapping file).

    The typical mapping should be:

    HOST_CODE_Handler = Handler file path

    For the Payment Transaction form, create the mapping in the (handler.properties) file as follows:

    PYTTXNHOST_Handler = com.intellectdesign.modelhouse.PymtTxnRequestHandler

  5. Create the handler class (added in the system handler properties file) extending the Canvas SimpleRequestHandler class that provides the Ajax request data as HashMap to the handler class.
    You can use this handler class to massage the data, implement your business logic and pass to instruction class.

    The typical handler class should be:

    package com.intellectdesign.<IMPLEMENTATION_NAME>.handler;

import java.util.HashMap;
import com.intellectdesign.canvas.common.ExtReplyObject;
import com.intellectdesign.canvas.common.ReplyObject;
import com.intellectdesign.canvas.exceptions.common.ProcessingErrorException;
import com.intellectdesign.canvas.handler.SimpleRequestHandler;
import com.intellectdesign.canvas.value.CanvasRequestVO;

public class <HANDLER_CLASS_NAME> extends SimpleRequestHandler
{
	/**
	 * @param request
	 * @return
	 * @throws ProcessingErrorException
	 * @see com.intellectdesign.canvas.handler.SimpleRequestHandler#process(com.intellectdesign.canvas.value.CanvasRequestVO)
	 */
	@Override
	public ReplyObject process(CanvasRequestVO request) throws ProcessingErrorException
	{
		ExtReplyObject replyObject = new ExtReplyObject();
		ReplyObject reply = null;
		String action = request.getActionCode();
		try
		{
			if (<ACTION_CODE1>.equals(action))
			{
				HashMap returnMap = null;
				//Implementation specific logic for the action code ACTION_CODE1
				replyObject.headerMap = returnMap;
				reply = replyObject;
			} else if (<ACTION_CODE2>.equals(action))
			{
				HashMap returnMap = null;
				//Implementation specific logic for the action code ACTION_CODE2
				replyObject.headerMap = returnMap;
				reply = replyObject;
			} else
			{
				throw new ProcessingErrorException("Invalid Action", "Action received '"
						+ request.getActionCode() + "' is not supported by this handler");
			}
		} catch (Exception exception)
		{
			//Handle Exception
		} 
		return reply;
	}
}

  6. Create the Instruction class that handles the DBRequest.
    Refer the Database Framework to learn about Canvas database framework.

    package com.intellectdesign.modelhouse; 
import java.util.HashMap; 
import com.intellectdesign.canvas.database.DatabaseConstants;
import com.intellectdesign.canvas.database.DatabaseException;
import com.intellectdesign.canvas.database.DatabaseRequest;
import com.intellectdesign.modelhouse.txn.TxnData; 

public class PaymentInstruction
{
	public void insertTxnData(TxnData txnData) throws DatabaseException
	{
		// First prepare the hashmap for passing to the DB layer.
		DatabaseRequest dbRequest = null;
		HashMap dataMap = new HashMap(); 
		dataMap.put("ACC_NO", txnData.getAccNo());
		dataMap.put("ACC_NAME", txnData.getAccName()); 
		dataMap.put("TRANSACTION_AMT", txnData.getTransactionAmt());
		dataMap.put("TRAN_TYPE", txnData.getTranType());
		dataMap.put("TRAN_DATE", txnData.getTranDate()); 
		dataMap.put("BENE_ACC_NO", txnData.getBeneAccNo());
		dataMap.put("BENE_NAME", txnData.getBeneName());
	
		// Create a DB request to execute the query.
		try
		{
			dbRequest = new DatabaseRequest();
			dbRequest.setDataSource(DatabaseConstants.DEFAULT_DATASOURCE);
			dbRequest.setDataAccessMapKey("INSERT_PAYMNT");
			dbRequest.setOperation(DatabaseConstants.INSERT);
			dbRequest.setData(dataMap); //Pass the data to insert 
			dbRequest.execute(); 
		} 
		catch (DatabaseException dbException)
		{
			throw dbException;
		} 
	}
}

  7. The given instruction class expects a result map with the Access Key INSERT_PAYMNT in the SQL map file. For information on SQL Map parameters, refer Quick primer on iBatis SQL Map.
    So, create PaymentSQLMap.xml SQLMap file that contains the SQLs to insert the data into the database as follows:

    <?xmlversion="1.0"encoding="UTF-8" ?> 
	<!DOCTYPE sqlMap 
	PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
	<sqlMap>

		<!-- Parameter map for inserting details in PAYMENT_TXN_DATA table -->
		<parameterMap id="TRASACTION_PARAM_MAP" class="java.util.HashMap">
			<parameter property="ACC_NO" jdbcType="VARCHAR" javaType="java.lang.String" />
			<parameter property="ACC_TYPE" jdbcType="VARCHAR" javaType="java.lang.String" />
			<parameter property="ACC_CCY" jdbcType="VARCHAR" javaType="java.lang.String" />
			<parameter property="ACC_NAME" jdbcType="VARCHAR" javaType="java.lang.String" />
			<parameter property="TRANS_AMT" jdbcType="NUMBER" javaType="java.lang.String" />
			<parameter property="TRAN_DATE" jdbcType="TIMESTAMP" javaType="java.lang.String" />
			<parameter property="BENE_ACC_NO" jdbcType="VARCHAR" javaType="java.lang.String" />
			<parameter property="BENE_NAME" jdbcType="VARCHAR" javaType="java.lang.String" />
		</parameterMap> 

		<!-- PAYMENT_TXN_DATA insertion -->
		<insert id="INSERT_PAYMNT_INSERT_TRASACTION" parameterMap="TRASACTION_PARAM_MAP">
		INSERT INTO PAYMENT_TXN_DATA
		(
			ACC_NO,
			ACC_NAME, 
			TRANSACTION_AMT,
			TRAN_DATE,
			BENE_ACC_NO,
			BENE_NAME
		)
		VALUES
		(
			?,
			?, 
			?,
			?,
			to_date(?,'DD/MM/YY'),
			?,
			?
		) 
		</insert> 
	</sqlMap>

  8. Make an entry for the SQL Map file in the implementation database configuration file of your application. The implementation database configuration file for your application will be specified in the <DATA_SOURCE_KEY>_<DSXML> key in the databaseconfig.properties file, refer Configuration Descriptor: Database for more information. For example, the Modelhouse application uses CTIMPLDatabaseConfig.xml as the implementation database configuration file and a sample configuration XML file is as follows:

    <?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMapConfig      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
    
<sqlMapConfig>
	<properties resource="databaseconfig.properties" />
	<transactionManager type="EXTERNAL">
		<property name="SetAutoCommitAllowed" value="false"/>
		<dataSource type="JNDI">
			<property name="DataSource" value="${DATASOURCE_NAME}"/>
		</dataSource>
	</transactionManager>
	
	<sqlMap resource="com/intellectdesign/canvas/database/ibatis/${CT_FW_DATABASE_VENDOR}/maps/viewdefn/Default_Function_SqlMap.xml" />
	<sqlMap resource="com/intellectdesign/canvas/database/ibatis/common/maps/viewdefn/Default_SqlMap.xml" />
	<sqlMap resource="com/intellectdesign/modelhouse/database/ibatis/${CT_FW_DATABASE_VENDOR}/maps/grid/myWidgetView_SqlMap.xml" />
	<sqlMap resource="com/intellectdesign/modelhouse/database/ibatis/${CT_FW_DATABASE_VENDOR}/maps/calendar/myCalendarView_SqlMap.xml" />
	<sqlMap resource="com/intellectdesign/modelhouse/database/ibatis/${CT_FW_DATABASE_VENDOR}/maps/chart/myChartView_SqlMap.xml" />
	<sqlMap resource="com/intellectdesign/modelhouse/database/ibatis/${CT_FW_DATABASE_VENDOR}/maps/asw/Activity_Summary_SqlMap.xml" />
	<sqlMap resource="com/intellectdesign/modelhouse/database/ibatis/${CT_FW_DATABASE_VENDOR}/maps/appstore/Appstore_SqlMap.xml" />
	<sqlMap resource="com/intellectdesign/modelhouse/database/ibatis/${CT_FW_DATABASE_VENDOR}/maps/txn/PaymentTxn_SqlMap.xml" />
	
	<!-- SQL Map file for the Payment Transaction form. -->
	<sqlMap resource="com/intellectdesign/modelhouse/database/ibatis/${CT_FW_DATABASE_VENDOR}/maps/txn/PaymentSQLMap.xml" />	
</sqlMapConfig>