@BeforeAll & @BeforeEach

In general, the first step to write a unit test involves arranging everything we need to run the test. JUnit allows annotating certain methods with @BeforeAll and @BeforeEach. As the name suggests, these methods will be executed before all/each unit test. We can use such methods to e.g. setup a database, before testing operations of a DAO.

Update the Sql2oCourseDaoTest class as follows:

class Sql2oCourseDaoTest {
  private static Sql2o sql2o;
  private static List<Course> samples;
  private CourseDao courseDao;

  @BeforeAll
  static void connectToDatabase() throws URISyntaxException {
    // construct the sql2o object to connect to a (test) database 
  }

  @BeforeAll
  static void setSampleCourses() {
    // instantiate "samples" with sample courses
  }

  @BeforeEach
  void injectDependency() {
    // instantiate the courseDao object as a Sql2oCourseDao
  }

  @Test
  void doNothing() {
    
  }
}

Here is the implementation of connectToDatabase():

@BeforeAll
static void connectToDatabase() throws URISyntaxException {
  String databaseUrl = System.getenv("TEST_DATABASE_URL");
  URI dbUri = new URI(databaseUrl);

  String username = dbUri.getUserInfo().split(":")[0];
  String password = dbUri.getUserInfo().split(":")[1];
  String dbUrl = "jdbc:postgresql://" + dbUri.getHost() + ':'
      + dbUri.getPort() + dbUri.getPath() + "?sslmode=require";

  sql2o = new Sql2o(dbUrl, username, password);
}

Notice that I use the environment variable TEST_DATABASE_URL; in general you want don't want to run your tests on your production database (because of the risk of corrupting the data in there). You often use a test database, a fork of production database. For simplicity, you can use your (production) Heorku database here, or create another PostgresSQL database for testing. The test database can be created locally on your computer, or in the cloud using cloud providers such as Heroku, Amazon, Google, etc. In my case, I've created a database on https://www.elephantsql.com/ which I'm using for testing.

Here is the implementation of setSampleCourses():

@BeforeAll
static void setSampleCourses() {
  samples = new ArrayList<>();
  samples.add(new Course("EN.500.112", "GATEWAY COMPUTING: JAVA"));
  samples.add(new Course("EN.601.220", "INTERMEDIATE PROGRAMMING"));
  samples.add(new Course("EN.601.226", "DATA STRUCTURES"));
  samples.add(new Course("EN.601.229", "COMPUTER SYSTEM FUNDAMENTALS"));
  samples.add(new Course("EN.601.230", "AUTOMATA and COMPUTATION THEORY"));
  samples.add(new Course("EN.601.315", "Databases"));
  samples.add(new Course("EN.601.476", "Machine Learning: Data to Models"));
  samples.add(new Course("EN.601.676", "Machine Learning: Data to Models"));
}

Here is the implementation of injectDependency():

@BeforeEach
void injectDependency() {
  try (Connection conn = sql2o.open()) {
    conn.createQuery("DROP TABLE IF EXISTS courses;").executeUpdate();

    String sql = "CREATE TABLE IF NOT EXISTS courses("
        + "offeringName VARCHAR(15) NOT NULL PRIMARY KEY,"
        + "title VARCHAR(50) NOT NULL"
        + ");";
    conn.createQuery(sql).executeUpdate();

    sql = "INSERT INTO courses(offeringName, title) VALUES(:offeringName, :title);";
    for (Course course : samples) {
      conn.createQuery(sql).bind(course).executeUpdate();
    }
  }

  courseDao = new Sql2oCourseDao(sql2o);
}

Notice the injectDependency runs before each method (whereas the other two methods run once before all the tests). Therefore, we drop and (re)create the courses table (adding sample courses) before each test, starting with a clean slate!

Run the doNothing() test method. If all goes well, you should see the sample courses in your (test) database.

Caution: The process may break because connectToDatabase is not able to read the TEST_DATABASE_URL. Make sure you set this environment variable. Also, notice there are separate "Run configurations" for "tests" and "application" in IntelliJ.