If pureQuery determines that only one row is to be returned, it might choose to optimize the query by adding the FETCH FIRST ROW ONLY clause or by changing the SELECT statement to a SELECT-INTO statement.
The Data interface provides three forms of the queryFirst() method.
The following code produces a single Map that has keys of average_edlevel and workdept. The code passes in the value of REPORTDEPT to identify the department of interest.
Connection con = DriverManager.getConnection(...); Data db = DataFactory.getData(con); String reportDept = "B01"; Map<String, Object> edLevelReport = db.queryFirst( "SELECT AVG(EDLEVEL) AS AVERAGE_EDLEVEL, WORKDEPT" + " FROM SAMPLE_SCHEMA.EMP WHERE WORKDEPT = ?" + " GROUP BY WORKDEPT HAVING COUNT(*) > 3", reportDept );
Instead of a Map, one could use one of the other two forms of the queryFirst() method, each of which returns an instance of an existing class.
For example, returning the information in a com.company.Employee class might be sufficient. This bean does have properties that are capable of storing the two items of information that you are interested in. However, it is poor practice to populate an Employee bean if you do not intend to provide the values of all of the bean's properties'
Alternatively, you could define a new Java object, possibly as a private class, that is able to store exactly the necessary information. In many situations, this might be the best solution because it provides self-documenting, clear code. However, this solution would require additional application logic to process an instance of this new class, for instance by entering its contents into a spreadsheet or sending its contents to an XML document.
public Employee { @Column(name="EMPNO") public String employeeId; @Column(name="FIRSTNME") public String firstName; @Column(name="MIDINIT") public String middleInitial; public String lastName; @Column(name="WORKDEPT") public String departmentId; @Column(name="PHONENO") public String extension; public Date hireDate; }
Connection con = DriverManager.getConnection(...); Data db = DataFactory.getData(con); String managerEmp = "000070"; com.company.Employee empData = db.queryFirst( "SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT," + " PHONENO, HIREDATE FROM HRDept.Employee" + " WHERE EMPNO = ?", Employee.class, managerEmp );