Using the Repository

1. Overview
2. Creating Classes
3. Storing Objects
4. Fetching Objects
   • Fetching By Class
   • Restricting By Field
   • Ordering Results By Fields
   • Restricting By SQL (advanced)
5. Removing Objects

The class is declared as normal, and extends JSQLObject:

public class Person extends JSQLObject {

The fields are declared as normal, with annotations where appropriate:

  @StringLength(64)
  private final String firstName;
  @StringLength(64)
  @Searchable
  private final String lastName;
  @StringLength(128)
  private final String address;
  @StringLength(64)
  private final String city;
  @StringLength(16)
  private final String postal;
  private int heightInCMs;

The @StringLength annotation is used to indicate that a string field is to be stored with a specified length. If no StringLength is given for a string field, then the field is stored as a blob (or equivalent), which might have an impact on searching. Fixed length strings are easier to see when browsing the database.

The @Searchable annotation indicates that an upcase copy of the string is also to be stored. Having an upper case representation can make searching easier.

List fields are created as JSQLList objects. JSQLList (or rather the JSQLArrayList implementation) provides facilities for easing list population and storage. The declaration of a list is:

  private final JSQLList<Person> friends=new JSQLArrayList<Person>(this,"friends");

The JSQLArrayList must be created with a reference to the containing JSQLObject (this) and the name of the assigned field. This information is required for list navigation and population. It's not as easy as List/ArrayList, but close. If you want to manage object associations, use a JSQLList/JSQLArrayList pairing.

The rest is standard getters and setters, with the exception of getFriends():

  public List<Person> getFriends() {
    fetchFieldQuiet("friends");
    return friends;
  }

The fetchFieldQuiet("friends") causes the list values to be fetched. If the getFriends() is called repeatedly, the values will only be fetched the first time.

So, that's it for object creation. This is a very basic example, and more examples can be found in src/java/test/org/benow/repository directory. More advanced concepts coved in the sample classes include Abstract classes, Interfaces, etc.

Person andy=new Person("Andrew", "Taylor", "36 End of Internet Cl.", "Noosphere", "T4C 4C4");
andy.setHeightInCMs(185);
Person ben=new Person("Ben", "Yeardly", "420 Green Loop", "Sloquet Hotsprings", "T4C 0C0");
ben.setHeightInCMs(175);
Person john=new Person("John","Smith","123 Here Cres.","Heresville","910222");
john.setHeightInCMs(180);
Person mary=new Person("Mary","Smith","123 Here Cres.","Heresville","910222");
mary.setHeightInCMs(155);

andy.update();
ben.update();
john.update();
mary.update();

Instances are created for andy, ben, john and mary. All the people are then updated. The update causes the database schema to be created (if not existing) and the person values are stored to that schema.

Note that the above code is terse. Unlike in the connection tutorial, there is no repository getting, no connection taking and no transaction used. This is the simple way of working with the repository. On update, the default repository is taken and connections and transactions taken and used automatically. There is more overhead with this simple way, but it's fine for simple usage. More advanced usage, including the reuse of connections and transactions can be found in the src/java/test/org/benow/repository/ code.

The database at this point contains the following tables:

SQL> show tables;
       CLASS_SCHEMA_INFO                      PERMISSION                     
       PERMISSION_ROLE                        PERMISSION_USERS               
       PERSON                                 ROLE                           
       ROLE_USERS                             USERS                          
       USERS_USER_MODULE               

CLASS_SCHEMA_INFO is used internally to track schema change, PERMISSION, ROLE and USERS tables are used for security, and the PERSON table is the table created for the Person object. The PERSON table is:

SQL> show table person;
ID                              INTEGER Not Null 
FIRST_NAME                      VARCHAR(64) Nullable default null
LAST_NAME                       VARCHAR(64) Nullable default null
LAST_NAME_S                     VARCHAR(64) Nullable default null
ADDRESS                         VARCHAR(128) Nullable default null
CITY                            VARCHAR(64) Nullable default null
POSTAL                          VARCHAR(16) Nullable default null
HEIGHT_INC_MS                   INTEGER Nullable default null

Triggers on Table PERSON:
SET_PERSON_ID, Sequence: 0, Type: BEFORE INSERT, Active

ID is an autogenerated integer for each person (as provided by JSQLObject), and the columns correspond to the fields in the Person Object. The PERSON table itself contains:

ID FIRST_NAME LAST_NAME LAST_NAME_S ADDRESS                CITY         POSTAL      HEIGHT_INC_MS 
== ========== ========= =========== ====================== ============ =========== =============
1  Andrew     Taylor    TAYLOR      36 End of Internet Cl. Noosphere    T4C 4C4     185
2  Ben        Yeardly   YEARDLY     420 Green Loop         Sloquet      T4C 0C0     175
3  John       Smith     SMITH       123 Here Cres.         Heresville   910222      180
4  Mary       Smith     SMITH       123 Here Cres.         Heresville   910222      155 

... which correspond to the stored objects. Note that a sequential id has been generated for each object.

List<Person> people = JSQLQuery.getAllObjectsQuery(Person.class).getObjects();

ResultIterator peopleI = JSQLQuery.getAllObjectsQuery(Person.class).getObjectIterator();
while (peopleI.hasNext()) {
  Person p=(Person) peopleI.next();
  System.out.println(p);
}

If you want to page through the results, there is an alternative SubList getObjects(int starting, int spanning) which may be used:

SubList<Person> somePeople=JSQLQuery.getAllObjectsQuery(Person.class).getObjects(2, 2);
System.out.println("\nFetched "+somePeople.size()+" people of: "+somePeople.getSupersetSize()+
    " from index: "+somePeople.getStartPos());

JSQLQuery.getAllObjectsQuery(Person.class).getObjects(SubList.FROM_START, SubList.SPANNING_ALL);

List<Person> smiths=JSQLQuery.getObjectByFieldQuery(Person.class, "lastName", "Smith").getObjects();

try {
  Person johnSmith=(Person) JSQLQuery.getObjectByFieldsQuery(Person.class,
      new String[] {"firstName","lastName"}, 
      new Object[] {"John","Smith"}).getObject();
  System.out.println(johnSmith);
} catch (NoSuchObjectException e) {
  System.err.println("No John Smith");
}

QueryContext ctx = JSQLQuery.getAllObjectsQuery(Person.class);
ctx.setOrderFields(new OrderField[]{
    new OrderField("lastName",Ordering.ASCENDING), 
    new OrderField("firstName",Ordering.ASCENDING), 
});

JSQLClassQuery q = new JSQLClassQuery(Person.class);
String sql=q.getSQLColumnForField("heightInCMs")+">=180";
q.setQuerySuffix(sql);
List<Person> tallPeople=q.createContext().getObjects();

select p.ID,p.FIRST_NAME,p.LAST_NAME,p.LAST_NAME_S,p.ADDRESS,p.CITY,p.POSTAL,p.HEIGHT_INC_MS from PERSON p 
where p.HEIGHT_INC_MS>=180

Person mary=(Person) JSQLQuery.getObjectByFieldQuery(Person.class, "firstName", "Mary").getObject();
mary.remove();

JSQLQuery.deleteObjects(Person.class);