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

    String f = "/tmp/test.db";
    db.createNewEmptyDbFile(f);
    Connection dbConn = db.establishConnection("jdbc:sqlite:", f);
    [...]
}

package voc;

import org.junit.Test;

import java.sql.Connection;
import java.util.ArrayList;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;

public class DatabaseTest {

    private Database db = null;

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

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

        String f = "/tmp/test.db";
        db.createNewEmptyDbFile(f);
        Connection dbConn = db.establishConnection("jdbc:sqlite:", f);

        db.createBasicSqlTable(dbConn);

        ArrayList<String> SQLTableNames = db.showTables(dbConn);
        assertFalse(SQLTableNames.isEmpty());

        String FirstSQLTable = SQLTableNames.get(0);
        assertEquals(FirstSQLTable, "vocabulary");
    }

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

        String f = "/tmp/test.db";
        db.createNewEmptyDbFile(f);
        Connection dbConn = db.establishConnection("jdbc:sqlite:", f);

        db.createBasicSqlTable(dbConn);

        db.insertWordPairIntoTable(dbConn,"time", "zeit");
        assertEquals(db.getGermanTranslation("time"), "zeit");
    }
}

package voc;

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

public class Database {
    private PreparedStatement pstmt = null;
    private Connection conn = null;

    public void createNewEmptyDbFile(String DBfilename) {
        try {
            File file = new File(DBfilename);

        } catch (Exception e) {
            throw new RuntimeException("Cannot create a new database file.",e);
        }
    }

    public Connection establishConnection(String databaseType, String DBfilename) {
        try {
            // create database connection
            conn = DriverManager.getConnection(databaseType + DBfilename);

            return conn;

        } catch (SQLException e) {
            throw new RuntimeException("Cannot create a connection to the SQL database.",e);
        }
    }


    public void createBasicSqlTable(Connection pConn) {
        try {
            pstmt = pConn.prepareStatement("create table vocabulary (english string, german string);");
            pstmt.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException("Cannot create a basic SQL Table.", e);
        }
    }

    /**
     * @returns the names of the SQL tables.
     */
    public ArrayList<String> showTables(Connection pConn) {
        try {
            pstmt = pConn.prepareStatement("SELECT name FROM sqlite_master WHERE type='table';");
            ResultSet rs = pstmt.executeQuery();

            pstmt.executeUpdate();

            ArrayList<String> SQLTableNames = new ArrayList<String>();
            while (rs.next())
            {
                 SQLTableNames.add(rs.getString(1));
            }

            return SQLTableNames;

        } catch (SQLException e) {
            throw new RuntimeException("SQL command to select all tables failed", e);
        }
    }

    /**
     * @param pConn: database connection object
     * @param englishVoc : given word we want to learn
     * @param germanTranslation : translation of the given word
     */
    public void insertWordPairIntoTable(Connection pConn, String englishVoc, String germanTranslation) {
        try {
            pstmt = pConn.prepareStatement("insert into vocabulary values(?,?);");
            pstmt.setString(1,englishVoc);
            pstmt.setString(2,germanTranslation);

            pstmt.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException("Cannot insert word pair into SQL Database.", e);
        }
    }

    /**
     * @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 {
            pstmt.executeQuery("select german from vocabulary where english=?;");
            pstmt.setString(1, pEnglishVoc);

            rs = pstmt.executeQuery();
            return rs.getString(1);

        } catch (SQLException e) {
            throw new RuntimeException("Cannot get german translation for given english word.", e);
        }
    }
}

package voc;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;

import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.nio.charset.Charset;
import java.sql.Connection;

import static java.nio.charset.Charset.forName;

public class Control {

    private Database theDB;

    public Control() {
        theDB = new Database();


        theDB.createNewEmptyDbFile("database.db");
        Connection dbConn = theDB.establishConnection("jdbc:sqlite:", "database.db");

        theDB.createBasicSqlTable(dbConn);

        this.insertVocabularyListIntoDB(dbConn);
    }




    public void insertVocabularyListIntoDB(Connection pConn) {
        URL url = null;

        try {
            url = new URL("https://raw.githubusercontent.com/toogley/voc-data/master/technology_and_society");
        } catch (MalformedURLException e) {
            throw new RuntimeException("URL of VocabularyList(" + url +") is malformed", e);
        }

        // CSVParser.parse(url,...) needs a Charset object
        Charset charset = forName("UTF-8");

        CSVParser parser = null;
        try {
            parser = CSVParser.parse(url, charset, CSVFormat.RFC4180);
        } catch (IOException e) {
            throw new RuntimeException("Failed to read from" + url, e);
        }

        for (CSVRecord csvRecord : parser) {
            String englishWord = csvRecord.get(0);
            String germanWord = csvRecord.get(1);

            theDB.insertWordPairIntoTable(pConn, englishWord, germanWord);
        }
    }


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