Lesson 6 - Programming a Database Wrapper in Java - The Database class

In the previous lesson of our Java database tutorials series, Database Wrapper in Java - Creating the Query class, we started creating a database wrapper to simplify our work with the database and created the Query class. Today, we're going to create a Database class, which is documented, but still I'll describe some parts of it in more detail. But before that, we'll test our Query class.

Testing the Query class

In the Wrapper class, we'll paste the following code into the main() method to test each method of the Query class:

Query query = new Query();

query.delete("programmers").where("name = ?");
System.out.println(query.getQuery());
//Output: DELETE FROM programmers WHERE name = ?

String[] columns3 = {"name","language"};
query.update("programmers").set(columns3).where("where id = ?");
System.out.println(query.getQuery());
//Output: UPDATE programmers SET name = ?,language = ? WHERE where id = ?

query.select(null).from("programmers").where("language = ?");
System.out.println(query.getQuery());
//Output: SELECT * FROM programmers WHERE language = ?

Object[] firstParams = {null, "Galí", 43, "PHP"};
query.insert("programmers").values(firstParams);
System.out.println(query.getQuery());
//Output: INSERT INTO programmers VALUES(?,?,?,?);

Don't forget to import the Query class!

Now let's move on to the Database class, which holds the entire wrapper together.

Constructor - Database Connection

In the constructor, we'll connect to the database and store this connection:

package DB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.PreparedStatement;

/**
*
* @author Milan Gallas
*/

public class Database {

     //connecting to the database
     protected Connection connection;

     //the Query class instance
     protected Query query;

     /**
     * The Database class constructor
     * @param db
     * @param userName
     * @param password
     * @throws SQLException
     */
     public Database(String db, String userName, String password) throws SQLException{

          connection = DriverManager.getConnection("jdbc:mysql://localhost/" + db, userName, password);

     }

}

Note: If we used the wrapper in a web application instead of in a desktop one, this approach would not be appropriate because of the large number of active database connections. In this case, we should reconnect in every method using the TWR block, as you know from the previous tutorials.

A general query() method

This method will be private and it'll be an auxiliary method for the delete(), save(), and update() methods that we'll create in the next chapters.

public class Database {
      //class attribute
      //class methods

     /**
     *
     * @param query
     * @param params
     * @return
     * @throws SQLException
     */
    private int query(String query, Object[] params) throws SQLException{
         PreparedStatement ps = connection.prepareStatement(query);
         if (params != null){
           int index = 1;
           for(Object param : params){
             ps.setObject(index, param);
            index++;
           }
         }
         return ps.executeUpdate();
    }
 }

The method accepts an SQL query as the first parameter and then it accepts any number of parameters of different types.

Also note the setObject() method of the PreparedStatement class. Thanks to this method, we can store values without knowing their data type yet. This method is a rescue for us and will make our work much easier.

The method returns the number of affected rows. The exception that the query might throw is propagated automatically thanks to using the throws keyword in the method header. The exception data type is SQLException in this case.

Deleting entries - The delete() method

public class Database {
  //class attribute
  //class methods

   /**
     * Removes data from a database table
     * @param table
     * @param requirement
     * @param param
     * @return
     * @throws SQLException
     */
    public int delete(String table, String requirement, Object[] param) throws SQLException{
        query = new Query();
        query.delete(table)
             .where(requirement);
        return query(query.getQuery(), param);
    }
}

We can pass a condition and parameters to the delete() method, and only those entries that meet our criteria are deleted.

Exceptions are also thrown automatically in this method and we leave them to be caught by the class from which the method will be called.

The method then returns the number of affected rows. We then use this value to determine if the query has been successful. This return value is also returned by the save() and update() methods! It's important to remember that the resulting SQL query is returned by the getQuery() method. A common mistake is that only a Query instance is passed to the query() method. See the example:

//This code won't work
return query(query, param);

Adding entries - The insert() method

public class Database {
    //class attribute
    //class methods

    /**
     * Inserts one row to a database table
     * @param table
     * @param params
     * @return
     * @throws java.sql.SQLException
     */
    public int insert(String table, Object[] params) throws SQLException{
        query = new Query();
        query.insert(table)
             .values(params);
        return query(query.getQuery(), params);
    }
}

The insert() method of the Database class has 2 parameters. The table name and the data to be written to the table. The method is simple and there's nothing more to say.

Updating data - The update() method

public class Database {
      //class attribute
      //class methods

   /**
     * Updates data stored in a database table
     * @param table
     * @param columns
     * @param requirement
     * @param params
     * @return
     * @throws SQLException
     */
    public int update(String table, String[] columns, String requirement, Object[] params) throws SQLException{
        query = new Query();

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

        return query(query.getQuery(), params);
    }
 }

The first three parameters of this method are: the database table name, a column names array, and the condition. In addition, the method may have other parameters of the Object type specifying data to be written to the table instead of the old ones.

You can see the benefits of the fluent interface in all methods of the Database class. The code is very simple, readable, and easy to understand.

So we still have the select() method to finish. We'll create this method in the next lesson, Finishing a Database Wrapper in Java, and there we'll work with our wrapper and test it.