Lesson 7 - Finishing a Database Wrapper in Java

In the previous lesson of our Java database course, Programming a Database Wrapper in Java - The Database class, we started writing a Java database wrapper. In today's last lesson, we're going to complete the select() method to retrieve data from the database. Then we'll test all the methods we wrote and finally we'll show how we could improve our wrapper even further.

Finishing the Wrapper - The select() method

This method will work differently than the save(), delete(), and update() methods. It won't use the query() method because we call the executeUpdate() method in it, which returns the number of affected rows. In the select() method, we'll return a ResultSet that we'll create using the executeQuery() method.

public class Database {
    //class attribute
    //class methods
    /**
     * Returns data from a table
     * @param table
     * @param columns
     * @param params
     * @return
     * @throws SQLException
     */
    public ResultSet select(String table, Object[] columns, Object[] params) throws SQLException{
        return this.select(table, columns, "", params);
    }

    /**
     * Returns data from a table
     * @param table
     * @param columns
     * @param requirement
     * @param params
     * @return
     * @throws SQLException
     */
    public ResultSet select(String table, Object[] columns, String requirement, Object[] params) throws SQLException{
        query = new Query();
        query.select(columns)
             .from(table)
             .where(requirement);

        PreparedStatement ps = connection.prepareStatement(query.getQuery());
        if(params != null){
            int index = 1;
            for(Object param : params){
            ps.setObject(index, param);
            index++;
         }
        }

        ResultSet rs = ps.executeQuery();
        return rs;
    }
}

The select() method is the last method to implement in our wrapper. There's one overload. We can call the select() method both with or without the condition.

Testing the Wrapper

So we have finished the Database class and now we'll move to the Wrapper class that NetBeans generated. You can see all changes we're going to make in your graphical database environment. First, let's test the connection:

package wrapper;


import DB.Database;
import java.sql.SQLException;


public class Wrapper {


    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        try {
            Database database = new Database("persons", "root", "");
        } catch (SQLException ex) {
            System.out.println("error - "+ex.getMessage());
        }
    }

}

Test the code. Everything should be fine. You shouldn't see any error messages. From now on, we'll only add code to the main() method. So I'll provide contents of the main() method only. I won't provide the rest of the class in the following chapters.

Writing

try {
    Database database = new Database("persons", "root", "");
            Object[] firstParams = {22, "Galí", 43, "PHP"};
            int success1 = database.insert("programmers", firstParams);
            System.out.println("Insert a user = " + success1);
} catch (SQLException ex) {
         System.out.println("error - "+ex.getMessage());
}

We should get the following result:

Insert a user = 1
BUILD SUCCESSFUL (total time: 1 second)*

The procedure (of the insert() and values() methods in the Query class)

In the first step after the for loop is completed, we get the following SQL query:

INSERT INTO programmers VALUES(?,?,?,?,

Thanks to the deleteCharAt() method, to which we pass the index of the last comma occurrence in the String array, which we get using the sb.lastIndexOf() method, deletes the last comma. Finally, we add the closing parenthesis and the semicolon. Then will make the query complete and look like this:

INSERT INTO programmers VALUES(?,?,?,?);

The query is then executed by the query method.

Deleting a user

Database database = new Database("persons", "root", "");

Object[] params2 = {"Galí"};
        int success2 = database.delete("programmers", "name = ?", params2);
        System.out.println("Delete the user Gali = " + success2);

Ther output:

Delete the user Gali = 1
BUILD SUCCESSFUL (total time: 1 second)

Editing a user

Database database = new Database("persons", "root", "");

String[] columns = {"name", "age", "language"};
        Object[] params = {"Galileo", 18, "Java", 6};
        int success4 = database.update("programmers", columns, "id = ?", params);
        System.out.println("Edit a user = "+success4);

The output:

Edit a user = 1
BUILD SUCCESSFUL (total time: 1 second)

Procedure:

The method generates the following SQL query before the for loop:

UPDATE persons SET

After the for loop, the query will look like this:

UPDATE persons SET name = ? ,age = ? ,

And after removing the last comma and adding the condition, the query will be in the final form:

UPDATE persons SET name = ? ,age = ? where id = ?

Printing

String[] columns3 = {"name", "language"};
            Object[] params3 = {"Java"};
            ResultSet rs = database.select("programmers", columns3, "language = ?", params3);
}

This method works a little differently than the other methods. Its return type is ResultSet and we use a loop to list the users. Here's what it'd look like to print the users:

while(rs.next()) {
      System.out.println(rs.getString("name") + " - " + rs.getString("language"));
}

The output:

James - java
Danna - java
Galileo - Java
Amy - java
BUILD SUCCESSFUL (total time: 1 second)

Improving the Wrapper

A library like this would be enough for many things in our applications. But let's show how to improve that even further.

The count() method - The number of rows in a database table

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

    /**
     *
     * @param table
     * @return
     * @throws SQLException
     */
    public int count(String table) throws SQLException{
        PreparedStatement ps = connection.prepareStatement("SELECT COUNT(*) FROM " + table);
        ResultSet result = ps.executeQuery();
        result.next();
        return result.getInt(1);
    }

}

The table must be hardcoded in the "SELECT COUNT (*) FROM" + table sequence. We can't pass a table name as a parameter to the setString() method.

Testing the entire Database class

public static void main(String[] args) {
    try {
            Database database = new Database("persons", "root", "");
            Object[] firstParams = {22, "Galí", 43, "PHP"};
            int success1 = database.insert("programmers", firstParams);
            System.out.println("Insert a user = " + success1);

            Object[] params2 = {"Galí"};
            int success2 = database.delete("programmers", "name = ?", params2);
            System.out.println("Delete the user Gali = " + success2);

            String[] columns = {"name", "age", "language"};
            Object[] params = {"Galileo", 18, "Java", 6};
            int success4 = database.update("programmers", columns, "id = ?", params);
            System.out.println("Edit a user = " + success4);

            String[] columns3 = {"name", "language"};
            Object[] params3 = {"Java"};
            ResultSet rs = database.select("programmers", columns3, "language = ?", params3);

            while(rs.next()) {
                System.out.println(rs.getString("name") + " - " + rs.getString("language"));
            }
        System.out.println(database.count("programmers"));
    } catch (SQLException ex) {
        System.out.println("error - "+ex.getMessage());
    }
}

So that's all for our application. You can test the wrapper on your applications and then give me some feedback. For example, what you would add to the wrapper or what you would do differently. The finished library can be downloaded below the article including source code.

Did you have a problem with anything? Download the sample application below and compare it with your project, you will find the error easily.