Tutorial 03

How to add new queries

Here we start the gory details.
Every call to the [TABLE_NAME]Broker, do the following: get a DatabaseBroker instance, and then asks for a query.
The query has a name, so, the DatabaseBroker gets the query depending on the database that you are using.
The query is not a full query yet, it is a java.sql.PreparedStatement; then, the method sets some variables and execute the query.
If the query is SELECT like, we get a result set, and using the getResultAs method from the Query class, we quickly wrap the result set, and get a Collection of [Table] instances, or something else that we desire.
Lets take a look in a method from the MemberBroker class: 

    /**
     * Select all rows from the MEMBER table, given a email.
     * Then returns each row as one instance of Member, all of them inside a 
     * Collection. It never returns null, maybe an empty Collection.
     */
    public static Collection selectByEmail(String email) throws DAOException {
        Collection result = null;
        Query query = DatabaseBroker.getBroker().getQuery("member.select.by.email");
        query.setObject(1, email);
        query.execute();
        result = query.getResultAs(Member.class);
        return result;
    }

NOTE: we will probably eliminate the DataBroker.getBroker() singleton stuff, and make everything inside of the constructors for Query, making Gerbo able to implement transactions and completely encapsulating the Database.
-- Include description for query.getResultAs(Class) here!!! --

Well, the code is quite simple, but What is returned after getQuery("member.select.by.email"); ?
Open your MySQL.sql file, in the same directory that you have the Database.properties file, look for the following line:
    
# Queries for EMAIL
member.select.by.email=SELECT * FROM MEMBER WHERE EMAIL = ?

Things are starting to make sense.
Gerbo will be able to change databases without recompiling a single java file, you just need to change your .sql file and database.properties, this is nice.
As you know, Gerbo generated just the basic selects, insert and updates. It is your job to do the ones that you need.
Lets create a new one, for the Member table.
For example, we want to know all the members that have a name like some string, ordered by one field 

#you can choose any names for the queries, sure
member.select.by.name.ordered.by.x=SELECT * FROM MEMBER WHERE NAME LIKE ? ORDER BY ?

And now, inside MemberBroker.java:

    /**
     * Our fresh new select method
     */
    public static Collection selectByNameOrderedByX(String name, String fieldX) throws DAOException {
        Collection result = null;
        Query query = DatabaseBroker.getBroker().getQuery("member.select.by.name.ordered.by.x");
        query.setObject(1, name);
        query.setObject(2, fieldX);
        query.execute();
        result = query.getResultAs(Member.class);
        return result;
    }

Now we can test our new select: 
	Collection c;
	Iterator i;
	
	c = MemberBroker.selectByNameOrderedByDay("%paulo%", Member.FIELD_EMAIL);
	i = c.iterator();

	while (i.hasNext()) {
		Member member = (Member) i.next();
		System.out.println(member);
	}

The % sign is kinda hardcoded, we need to find a solution about it.
I wait for your comments. paulo@paulo.com.br