Java JDBC using SQLite/Improving

The code on the previous page, whilst usable, is lumpy and more procedural than object-oriented. Typically in a real world situation the database specific code would be located in a class of its own, and then created and used as necessary as a discrete object.

Let's have a look at how we might think about doing this.

import java.sql.ResultSet;

public class BetterHelloDatabase
{
    public static void main (String[] args)
    {
        String sDropTable = "DROP TABLE IF EXISTS dummy";
        String sMakeTable = "CREATE TABLE dummy (id numeric, response text)";
        String sMakeInsert = "INSERT INTO dummy VALUES(1,'Hello from the database')";
        String sMakeSelect = "SELECT response from dummy";

        String sDriver = "jdbc:sqlite";
        String sDatabaseToUse = "hello.db";
        String sDbUrl = sDriver + ":" + sDatabaseToUse;

        DbClass db = new DbClass(sDbUrl);
        try {

        db.execute(sDropTable);
        db.execute(sMakeTable);
        db.execute(sMakeInsert);

        ResultSet rs = db.executeQuery(sMakeSelect);
        try {
            while(rs.next())
            {
                String sResult = rs.getString("response");
                System.out.println(sResult);
            }
            } finally {
                try { rs.close(); } catch (Exception ignore) {}
            }
        } finally {
            try { db.close(); } catch (Exception ignore) {}
        }
    }
}

It's an improvement on the previous cut since we have now the possibility to have more than one database connection in play by use of the instantiation of the proposed new class DbClass. Moreover since the constructor takes a database URL as an argument it would be feasible to make this proposed class service more than one SqLite database, (and, in fact, service a completely different database type using a different JDBC provider if one is available). If you consider this carefully, what this should be suggesting to you is that in fact what we will ultimately need is an abstract base class of generalised database methods, from which we can then inherit to provide a concrete class which is database type specific, e.g. we have an abstract base class called something like DbBaseClass from which we can derive such classes as SqLiteDbClass, OracleDbClass, MySQlDbClass.

You will note that only java.sql.ResultSet is now needed for import into main as the database logic will be encapsulated elsewhere. Ultimately again we should be thinking about encapsulating that untidy ResultSet for loop block as a method in yet another class which is specific to handling outcomes of queries, with a view to providing its methods to an Interface class which bridges the concrete implementation of the database logic and the way that the data is returned etc. However, this is somewhat of a digression at the moment since what we really are interested in for now are the practicalities of getting our database to do what we want it to do.

The main method anticipates there being (at least) three publicly available methods, as well as having (at least) one constructor which takes the URL as an argument:

• void execute(String)
• ResultSet executeQuery(String)
• void close()

It will inevitably also need a number of private methods and fields to satisfy this encapsulation. Let's start with the constructor and the obvious methods which it will need to provide a viable database class.

{imports}

public class DbClass {
    public String sUrl; // for advertising and debug purposes
    private String sDriverName = "org.sqlite.JDBC"; 
    private String sDriver; 
    private Connection conn = null;
    private Statement stmt = null;

    public DbClass(String sDbUrl) {
        sUrl = sDbUrl;
        sDriverName = getDriverString(sDbUrl); // i.e. we will need a
                                                // function to split the
                                                // driver string from the
                                                // passed URL
        /*
         * which in itself suggests we may provide a constructor overload
         * which takes the full URL and the DriverName....
         */
        setConnection();
    }

    private void setConnection() throws Exception {
        try {
            Class.forName(sDriverName);
        } catch (Exception e) {
            // connection failed.
            System.out.println("DriverName: " + sDriver
                    + " was not available");
            System.err.println(e);
            throw e;
        }
            // create a database connection
            conn = DriverManager.getConnection(sUrl);
            try {
                stmt = conn.createStatement();
            } catch (Exception e) {
                try { conn.close(); } catch (Exception ignore) {}
                conn = null;
            }
    }

    // this method should undoubtedly be public as we'll want to call this
    // to close connections externally to the class
    public void closeConnection() {
        if (stmt!=null) { try { stmt.close(); } catch (Exception ignore) {} }
        if (conn!=null) { try { conn.close(); } catch (Exception ignore) {} }
    }

    // and we will definitely want to be able to call the following two
    // functions externally since they expose the database
    // behaviour which we are trying to access
    public ResultSet executeQuery(String instruction) throws SQLException {
        return stmt.executeQuery(instruction);
    }

    public void execute(String instruction) throws SQLException {
        stmt.executeUpdate(instruction);
    }

}

Obviously this is a very pared down implementation of a putative database class, and it really isn't all that object oriented. However it does represent a considerable step forward from the initial HelloDatabase.java which we began with.

• Next page: Example base class
• Previous page: Connecting