How pureQuery uses the case of Java and SQL identifiers when running SQL statements and building beans or Map objects

When you use a bean or a Map object in an annotated or inline method to pass values to parameters in a WHERE clause, and when pureQuery constructs a bean or a Map to contain a query result, pureQuery follows rules concerning the case of parameter markers and SQL columns.
  1. When you use a bean or a Map object in an annotated or inline method to pass values to parameters in an SQL statement, pureQuery does not change the case of the name portion of the parameter markers in the statement.

    pureQuery observes this rule only when the input parameter for a method is a bean or a Map. Because the name portion of the parameter markers are preceded by : or ?, pureQuery considers them to be case-sensitive Java identifiers when locating the corresponding property values in the bean or Map.

  2. When pureQuery constructs a bean or a Map from the results of a query, it converts the labels of the SQL columns to lowercase. If the return type is a bean, then pureQuery builds the bean. If the return type is a Map or perhaps a collection of Map objects, pureQuery passes the lowercase labels to the put() method of each Map.

pureQuery does not change Java's rules for the equality of identifiers when an identifier is used only by Java. Nor does pureQuery change SQL's rules for the equality of identifiers when an identifier is used only by SQL.

Example 1

In this example, an annotated method takes a bean named Act as an input parameter and runs a SELECT statement against a database. The results of the SELECT statement are filtered with the value of one of the properties of the bean.

@Select(sql="select ACTNO, ACTKWD, ACTDESC from ACT where ACTNO = :actNo")
  Act getAct(Act a);

When running the SELECT statement, pureQuery first searches the bean for a getActNo() method. If it does not find one, pureQuery looks for a property with the name "actNo". If it does not find one, then pureQuery passes the String "actNo" to the get(String) method of the bean, if the bean defines such a method.

When returning the Act objects that correspond to matching records, pureQuery lowercases the SQL column labels. Then, pureQuery builds the bean.

Example 2

Suppose that the method takes a Map as an input parameter and returns a List of Map objects:

@Select(sql="select ACTNO, ACTKWD, ACTDESC from ACT where ACTNO = :actNo")
  List<Map<String, Object>> getAct(Map<String, Object> a);

When running the SELECT statement, pureQuery passes the String "actNo" to the get(String) method of the Map. When returning the Map objects that correspond to matching records, pureQuery lowercases the SQL column labels before passing them to each Map objects put() method.

Example 3

In the next example, another annotated method also takes an Act bean as an input parameter and runs an UPDATE statement against a database. The values in the statement come from properties in the bean. The method returns an update count.

@Update(sql="update ACT set ACTKWD = :actKwd, ACTDESC = :actDesc where ACTNO = :actNo")
  int updateAct(Act a);

When running the UPDATE statement, pureQuery searches for matches of the parameters in the UPDATE statement with names of get() methods in the bean, the names of properties in the bean, and then get(String). For example, for the parameter with the name actKwd, pureQuery first searches the bean for a getActKwd() method. If it does not find one, pureQuery looks for a property with the name "actKwd". If it does not find one, then pureQuery passes the String "actKwd" to the get(String) method of the bean, if the bean defines such a method.


Feedback