Return type for inline methods that query databases: first row of a query result

You can use the Data interface's overloaded queryFirst() method to return only the first row in a query result.
You might want to use this method in the following situations:

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 different forms of the queryFirst() method

The Data interface provides three forms of the queryFirst() method.

java.util.Map <java.lang.String, java.lang.Object> queryFirst(java.lang.String sql, java.lang.Object... parameters)
This form of the queryFirst() method returns a result of type java.util.Map<String,Object>. The String is the lowercase name of a column or expression in the given query. The class of the associated Object varies according to the data type of a column or expression. It is an instance of the class that is defined by JDBC as the natural class for storing instances of the data type of the column or expression.
For example, suppose the you need the average number of years of formal education (EDLEVEL) for a single department (WORKDEPT) as long as that department has more than three employees.

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 T queryFirst (java.lang.String sql, Class returnClass, Object... parameters)
The second form of the queryFirst() method uses generics to return a result of type <T>.
For example, you might need an instance of an Employee bean for the employee whose EMPNO is "000070". The definition of the Employee bean might look like this:
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;   }
The EMPNO column is the primary key of the EMPLOYEE table. If your query's WHERE clause contains that column, the method can return only one Employee object. Your code might look like this:
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 );
public T queryFirst (java.lang.String sql, RowHandler singleRowHandler, Object... parameters)
The third form of the queryFirst() method also returns a result of type <T>.
For example, you might need information about an employee, but pureQuery might not be able to map automatically the results of your query to the properties of the Employee bean. You could write a RowHandler to perform that mapping.

Feedback