5
\$\begingroup\$

Genereal Introduction

This is my first project

  • which involves a Database
  • which involves testing (especially unit testing)
  • where the quality is really important for me.

The long term aim is to create two frontends for Android and normal Java and a backend with the database. That means, the app should consist of 3 classes (GUI, Control, Database), where I kind of use the same code for both Java and Android GUIs. But that is far away, and not the topic here - although I explain that because I think its relevant for the current software design.

I plan to store the vocabulary values inside a text file and insert those during the build process, like suggested here.

The database has currently this very simple design (its simplicity allows me to understand it better..):

Table: Vocabulary

| english | german |
| ------- | ------ |
| time    | zeit   |

specific questions

  1. i don't like my return "<error>"; statements - but i don't know how to improve it.

  2. The method TestDataInsertion() does initially the same as the method TestDatabaseTable(), namely creating the database table. Additionally, both depend on the setUp() method. Of course those test Methods are automated (using maven), but horribly written - because they are not isolated at all. (according to "The art of Unit testing" by Roy Osherove). I know, that DBunit could maybe solve my problem, but I couldn't figure out how i can isolate those two methods.

  3. What else can I improve?

Control.java

package voc;

public class Control {

    private Database theDB;

    public Control() {
        theDB = new Database();
        theDB.setDBfilename("database.db");
        theDB.createNewEmptyDbFile();
        theDB.establishConnections();
        theDB.createBasicSqlTable();

        this.fillDbUp();
    }

    public void fillDbUp() {
        theDB.insertWordPairIntoTable("'time'", "'zeit'");
    }

    public static void main(String[] args) {
        new Control();
    }
}

Database.java

package voc;

import java.io.File;
import java.sql.*;

public class Database {
    private Connection conn = null;
    private Statement st = null;
    private String DBfilename  = null;

    public void setDBfilename(String pFilename)
    {
        this.DBfilename = pFilename;
    }

    public void createNewEmptyDbFile() {
        try {
            File file = new File(this.DBfilename);
            file.delete();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void establishConnections() {
        try {
            // create database connection
            conn = DriverManager.getConnection("jdbc:sqlite:" + this.DBfilename);
            st = conn.createStatement();
            st.setQueryTimeout(30);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void createBasicSqlTable() {
        try {
            String s = "create table vocabulary (english string, german string);";
            st.executeUpdate(s);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public String showTable() {
        try {
            String s = "SELECT name FROM sqlite_master WHERE type='table';";
            ResultSet rs =st.executeQuery(s);
            if (rs.next())
            {
                return rs.getString(1);
            }
            else
            {
                return "<error>";
            }

        } catch (SQLException e) {
            e.printStackTrace();
            return "<error>";
        }
    }

    /**
     * @param englishVoc: given word we want to learn
     * @param germanTranslation: translation of the given word
     */
    public void insertWordPairIntoTable(String englishVoc, String germanTranslation) {
        try {
            String s = "insert into vocabulary values("
                    + englishVoc + "," + germanTranslation + ");";
            st.executeUpdate(s);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * @param pEnglishVoc: english word we want to learn (1st column of db)
     * @return the german translation of the parameter (2nd column of db)
     */
    public String getGermanTranslation(String pEnglishVoc) {
        ResultSet rs;
        try {
            String s = "select german from vocabulary where english="
                    + pEnglishVoc + ";";
            rs = st.executeQuery(s);
            return rs.getString(1);

        } catch (SQLException e) {
            e.printStackTrace();
            return "<<error>>";
        }
    }
}

DatabaseTest.java

package voc;

import org.junit.Test;

import static org.junit.Assert.assertEquals;

public class DatabaseTest {

    private Database db = null;

    public void setUp() {
        db = null;
        db = new Database();

        String f = "/tmp/test.db";
        db.setDBfilename(f);
        db.createNewEmptyDbFile();
        db.establishConnections();
    }

    @Test
    public void TestDatabaseTable() {
        setUp();

        db.createBasicSqlTable();
        assertEquals(db.showTable(), "vocabulary");
    }

    @Test
    public void TestDataInsertion() {
        setUp();

        db.createBasicSqlTable();
        assertEquals(db.showTable(), "vocabulary");

        db.insertWordPairIntoTable("'time'", "'zeit'");
        assertEquals(db.getGermanTranslation("'time'"), "zeit");
    }
}
\$\endgroup\$
1
  • 1
    \$\begingroup\$ In the createNewEmptyDbFile method, it will not create it because it has been deleted. You will need to call file.createNewFile() \$\endgroup\$
    – JD9999
    Jun 28 '16 at 5:50
1
\$\begingroup\$

First of all, @Before annotation is running setup for you if you want a completely new database for every test run. So instead of setUp() in every method, just annotate @Setup.

For unit testing, you should reach good coverage. Writing unit tests for every possible scenario for a particular class.

Lets take for example this method:

public String getGermanTranslation(String pEnglishVoc) {
    ResultSet rs;
    try {
        String s = "select german from vocabulary where english="
                + pEnglishVoc + ";";
        rs = st.executeQuery(s);
        return rs.getString(1);

    } catch (SQLException e) {
        e.printStackTrace();
        return "<<error>>";
    }
}

Unit test should have

get_german_translation_null()
get_german_translation_empty_string()
get_german_translation_existing_value()
get_german_translation_not_existing_value()

So just go with different combinations and in this case, you might be sure you won't have problems. It's "cheaper" to fix during development.

Your design is probably ok for your first pet project, but big solid projects have specific architecture.

Your methods working with db have same repetitive manner: For example:

ResultSet rs;
try {
  String s = "select german from vocabulary where english="
                + pEnglishVoc + ";";
  rs = st.executeQuery(s);
  return rs.getString(1);

} catch (SQLException e) {
        e.printStackTrace();
        return "<<error>>";
}

If you decide to handle exceptions differently, then you will need change that in N placed rather then one. Its called DRY (Don't-Repeat-Yourself) principle. It's better to have one place like executeQuery(String query) and handle it there.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.