Read Operation

To select records from the EMPLOYEE table, we need to define a select query in the SQL map and use it to retrieve the data.

<resultMap id="EMPLOYEE_SELECT_RESULT_MAP" class="java.util.HashMap">
<result property="EmployeeId" nullValue="" column="EMP_ID" javaType="java.lang.String" jdbcType="NUMBER"/>
<result property="EmployeeName" nullValue="" column="EMP_NAME" javaType="java.lang.String" jdbcType="VARCHAR"/>
<result property="dtBirthDate" nullValue="" column="DOB" javaType="java.util.Date" jdbcType="DATE"/>
<result property="mobileNum" nullValue="" column="MOB_NO" javaType="java.lang.String" jdbcType="VARCHAR"/>
</resultMap> 
<select id="EMPLOYEE_SELECT_MYAPP" parameterClass ="java.util.HashMap" resultMap="FORMATTER_PRPT_MAP">
SELECT EMP_ID,EMP_NAME, DOB, MOB_NO
FROM EMPLOYEE
</select>

The code to retrieve the data can be something like -

List<HashMap> allEmployees = null;
try { 
DatabaseRequest dbRequest = new DatabaseRequest(); 
dbRequest.setDataAccessMapKey("EMPLOYEE");
dbRequest.setOperation(DatabaseConstants.SELECT)
dbRequest.setOperationExtension("MYAPP"); 
DatabaseResult dbResult = dbRequest.execute(); //Execute the select.
allEmployees = (List<HashMap>)dbResult.getReturnedList(); //Get the actual result set
} catch (DatabaseException dbException) {
//Handle the exception appropriately.
}

This code segment is a simple example to retrieve data. Let us consider a requirement wherea list of employee IDs needs to be filtered. For this requirement, a small tweak to the SQL map and the following code effectively –delivers the results:

<select id="EMPLOYEE_SELECT_MYAPP" parameterClass ="java.util.HashMap" resultMap="FORMATTER_PRPT_MAP">
SELECT EMP_ID,EMP_NAME, DOB, MOB_NO
FROM EMPLOYEE
WHERE EMP_ID in 
<iterate property="EmployeeList" open="(" close=")" conjunction=",">
#EmployeeList[]#
</iterate>
</select>

The property EmployeeList can be a List<String> or a String array. Assuming that it is a String array, the following small changes to the code snippet –are needed:

List<HashMap> allEmployees = null;
String[] empIdsArr = getEmpIdsFilterList();
try { 
DatabaseRequest dbRequest = new DatabaseRequest(); 
dbRequest.setDataAccessMapKey("EMPLOYEE");
dbRequest.setOperation(DatabaseConstants.SELECT)
dbRequest.setOperationExtension("MYAPP");
dbRequest.addFilter("EmployeeList", empIdsArr); //Add the array as a filter 
DatabaseResult dbResult = dbRequest.execute(); //Execute the select.
allEmployees = (List<HashMap>)dbResult.getReturnedList(); //Get the actual result set
} catch (DatabaseException dbException) {
//Handle the exception appropriately.
}