Lesson 5 - Database Wrapper in Java - Creating the Query class

In the previous lesson of our Java database tutorial series, Programming a Database Wrapper in Java - Preparation, we started creating a database wrapper that will simplify our work with the database. Today's lesson is going to be mainly about programming. Before we start programming the Database class itself, we will prepare a Query class that will represent an SQL query.

The Query class

We'll create a new Query class in the DB package. In it, we'll use methods to build an SQL query gradually. After this step, our project will look like this:

In the class, we'll create a delete() and where() methods, which we'll use very often:

public class Query {
    private StringBuilder query;

    /**
     *
     * @param table
     * @return
     */
    public Query delete(String table){
        query = new StringBuilder();
        query.append("DELETE FROM ");
        query.append(table);
        return this;
    }

    /**
     * Adds the WHERE condition to the SQL query
     * @param requirement
     * @return
     */
    public Query where(String requirement){
    query.append(" WHERE ");
        query.append(requirement);
        return this;
    }

   /**
     *
     * @param table
     * @return
     */
    public Query from(String table){
        query.append(" FROM ");
        query.append(table);
        return this;
    }
}

With the StringBuilder class, we can assemble our SQL queries more easily and store them in the query instance variable. The append() method simply adds a substring, character, or variable to an array. The variable can be of any data type (Object, String, Char, Boolean, Double, Int, Float, etc.).

We can convert a StringBuilder class instance to a string using the toString() method.

The where() method is important for us because we'll use it most often.

update() and set() methods

Similarly, we'll add update() and set() methods:

public class Query {
    //class variables
    //class methods
    /**
     *
     * @param table
     * @return
     */
    public Query update(String table){
        query = new StringBuilder();
        query.append("UPDATE ");
        query.append(table);
        query.append(" SET ");
        return this;
    }

    /**
     * Adds columns
     * @param Columns
     */
    public Query set(String[] columns){
        int count = columns.length;
        if(count == 0)
            throw new IllegalArgumentException("Invalid argument count");

        for(String column : columns){
           query.append(column);
           query.append(" = ");
           query.append("?");
           query.append(",");
        }
        query.deleteCharAt(query.lastIndexOf(","));
        return this;
    }
}

Now let's explain why there's return this at the end of each method; It's easy. Each method in the Query class is of the Query data type. That means it returns instances of that class. The advantage is that we can make our code more effective like this. Instead of code like this:

query.update(table);
query.set(columns);
query.where(requirement);

We can write this more effectively:

query.update(table).set(columns).where(requirement);

This syntax is called Fluent Interface. It helps us to create an application interface that is very readable and understandable. The smooth interface was created by Martin Fowler and it's an object-oriented programming technique where, as we have seen above, we chain methods called of the same object one after another.

It's also worth noting that in the set() method, we test whether we don't send an empty array. If an invalid parameter is sent, an IllegalArgumentException is thrown. Finally, let's just mention that the deleteCharAt() method removes the last unwanted comma, but we'll discuss the internal logic of the methods and examples in another part of this tutorial.

insert() and values() methods

We'll add the insert() and values() methods:

public class Query {
    //class variables
    //class methods
    /**
     *
     * @param table
     * @return
     */
    public Query insert(String table){
        query = new StringBuilder();
        query.append("INSERT INTO ");
        query.append(table);
        return this;
    }

    /**
     *
     * @param params
     * @return
     */
    public Query values(Object[] params){
        query.append(" VALUES(");

        int count = params.length;

        if(count == 0)
            throw new IllegalArgumentException("Invalid parameter count");

        for (int i = 0; i<count; i++) {
            query.append("?,");
        }
        //removes the last comma
        query.deleteCharAt(query.lastIndexOf(","));
        query.append(");");
        return this;
    }
}

There is nothing new in the insert() method. The values() method takes an array of objects as a parameter and substitutes each "?" in the SQL query for an object from the array. Finally, the last comma is removed and the Query instance returned. If the number of parameters is zero, an IllegalArgumentException is thrown.

select() and auxiliary methods

public class Query {
    //class variables
    //class methods
    /**
     *
     * @param columns
     * @return
     */
    public Query select(Object[] columns){
        query = new StringBuilder();
        query.append("SELECT ");
        if(columns != null){
            for(Object column : columns){
                query.append(column);
                query.append(",");
            }
            //removes the last question mark
            query.deleteCharAt(query.lastIndexOf(","));
        }
        else
            query.append("*");

        return this;
    }
}

The select() method takes an array of objects that represent individual columns as a parameter. If we give the select() method a value of null as a parameter, it appends the * character to the SQL query, which will return all columns in the database table.

A getQuery() method

public class Query {
    //class variables
    //class methods
     /**
     * Returns the generated SQL query
     * @return query
     */
    public String getQuery(){
        return query.toString();
    }
}

Finally, we need to get the resulting SQL query somehow. For this purpose we can use the getQuery() method, which returns the query as a string using the toString() method.

In the next lesson, Programming a Database Wrapper in Java - The Database class, we'll test the Query class and create a Database class.