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.
| Code Block |
|---|
|
<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"/> |
...
...
<select id="EMPLOYEE_SELECT_MYAPP" parameterClass ="java.util.HashMap" resultMap="FORMATTER_PRPT_MAP"> |
...
SELECT EMP_ID,EMP_NAME, DOB, MOB_NO |
...
...
The code to retrieve the data can be something like -
| Code Block |
|---|
|
List<HashMap> allEmployees = null; |
...
...
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:
| Code Block |
|---|
|
<select id="EMPLOYEE_SELECT_MYAPP" parameterClass ="java.util.HashMap" resultMap="FORMATTER_PRPT_MAP"> |
...
SELECT EMP_ID,EMP_NAME, DOB, MOB_NO |
...
...
...
<iterate property="EmployeeList" open="(" close=")" conjunction=","> |
...
...
...
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:
| Code Block |
|---|
|
List<HashMap> allEmployees = null; |
...
String[] empIdsArr = getEmpIdsFilterList(); |
...
...
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. |
...