Annotation used to decorate a method in a Query interface with a SQL statement that can return a result set from the underlying data store. The Select annotation must not be used in-conjunction with a SQL statement that returns an update count.

A Query interface may contain zero, one or more methods decorated with an Select annotation.

The following provides an example of using the Select annotation.

 // Define DataSet type
 public class Mammal {
     public String name;
     public String description;
     public int age;
 }
 // Define the interface containing the methods representing SQL statements
 // that can be invoked.
 
 interface MyQueries extends BaseQuery {    
    @Select("select name, description, age from mammal")
    DataSet<Mammal> getAllMammals();
 
    @Update("delete from mammal")
    int deleteAllMammals();
 }
 

To invoke the getAllMammals() method, an instance of the MyQueries interface must be created by invoking either the Connnection.createQueryObject or DataSource.createQueryObject method.

 MyQueries mq = con.createQueryObject(MyQueries.class);
 Dataset<Mammal> rows = mq.getAllMammals();
 

Using a Parameterized sql annotation element

The sql annotation element allows developers to specify parameter markers similar to PreparedStatements. Parameter markers are defined as:

• A String value designated by a question mark(?) prefix follwed by an integer.
• The numbering for parameter markers starts at 1. For example ?1.
• The number of distinct parameter marks must not exceed the number of method parameters for the method that was decorated by the Select annotation. If this occurs, a SQLRuntimeException will be thrown.
• A parameter marker may be specfied more than once in a sql annotation element.

 interface MyQueries extends BaseQuery {    
 
    @Select(sql="select name, description from mammal where weight > ?1")
     DataSet<Mammal> getBigMammals(int weight);
 }
 

 MyQueries mq = con.createQueryObject(MyQueries.class);
 DataSet<Mammal> rows = mq.getBigMammals(200);
 

When the getBigMammals() method is invoked, the value specified for the parameter weight will be used as the value for the parameter marker.

connected annotation element

By default, a method decorated by a Select annotation returns a DataSet instance that is connected to the underlying data store. The returned DataSet is treated similar to a ResultSet object. Any modifications to the DataSet are propagated to the data source if the DataSet is updatable.

If the connected annotation element is set to false, the returned DataSet instance is considered to be disconnected. The DataSet is then treated similar to a CachedRowSet and any modifications to the DataSet are not propogated to the data source until the DataSet.sync method is called.

allColumsMapped annoation element

The allColumnsMapped annotation element is used to specify whether all of the columns that are returned by a method decorated by a Select annotation must be present in the data class for a DataSet. The returned columns are mapped by matching their column names to the field names in the data class for a DataSet. If the allColumnsMapped annotation element is set to false, then any columns returned by a method decorated by a Select annotation that are not contained in the data class will be ignored.

Consider the following example:

 public class Mammal {
     public String firstName;
     public String lastName;
     public String address;
     public String description;
 }
 public class MammalFail {
     public String name;
     public String description;
     public int age;
 }

 interface Queries {
     @Select(sql="select * from Mammal", allColumnsMapped=true)
     DataSet<Mammal> getAllMammals();
     @Select(sql="select * from Mammal", allColumnsMapped=true)
     DataSet<MammalFail> getAllMammals2();
 }
 

The Mammal table contains the following columns and data:

 -----------------------------------------------------------
 |firstName | lastName  | address           | description    |
 ===========================================================
 | homer    | simpson   | Springfield       | Average Joe    | 
 -----------------------------------------------------------
 | fred     | flintsone | San Francisco, CA | stoneage human |
 -----------------------------------------------------------
 | wombat   | jones     | Aspen, CO         | wombat         |
 ============================================================
 

The call to the getAllMammals method will succeed because the data class Mammal contains a field for every column in the result set that is returned by the query specified in the sql annotation element.

The call to the getAllMammals2 method will result in a SQLRuntimeException being thrown because the data class MammalFail does not contain a field for every column in the result set that is returned by the query specified in the sql annotation element. The fields firstName, lastName and address would need to be added to the data class MammalFail in order for the call to the getAllMammals2 method to succeed.

since

1.6