Skip to content

Using CRUD For Course

CRUD stands for create, read, update, and delete. It refers to the common tasks you want to carry out on database.

Create the following class JdbcCourseCrudPersister in the persistence package;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
public class JdbcCourseCrudPersister {
  private Connection conn;

  // PRE: conn is an open connection to a SQLite database with Courses table.
  public JdbcCourseCrudPersister(Connection conn) {
    this.conn = conn;
  }

  // PRE: id of course is not set yet.
  public void createCourse(Course course) throws SQLException {

  }

  public Course readCourse(int courseId) throws SQLException {

    return null;
  }

  // PRE: id of course is already set by the database.
  public void updateCourse(Course course) throws SQLException {

  }

  public void deleteCourse(int courseId) throws SQLException {

  }
}

It's good time for test-driven development. Create the following test suit (it must be created in src/test/java/persistence):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
public class JdbcCourseCrudPersisterTest {

  private JdbcCourseCrudPersister unit;
  private Connection conn;

  @Before
  public void setUp() throws SQLException {
    final String URI = "jdbc:sqlite:./Test.db";
    conn = DriverManager.getConnection(URI);

    String sql = "CREATE TABLE IF NOT EXISTS Courses(id INTEGER PRIMARY KEY, name VARCHAR(30), url VARCHAR(200));";
    PreparedStatement pst = conn.prepareStatement(sql);
    pst.execute();

    unit = new JdbcCourseCrudPersister(conn);
  }

  // TODO add tests!

  @After
  public void tearDown() throws Exception {
    PreparedStatement pst = conn.prepareStatement("DROP TABLE IF EXISTS Courses;");
    pst.execute();
    conn.close();
  }
}

Note the setUp and tearDown methods will be executed before and after each test.

Create Course

Here is a test:

1
2
3
4
5
6
7
@Test
public void createCourseAssignsId() throws SQLException {
  Course course = new Course("OOSE", "jhu-oose.com");
  assertEquals(0, course.getId());
  unit.createCourse(course);
  assertNotEquals(0, course.getId());
}

And this is an implementation:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
// PRE: id of course is not set yet.
public void createCourse(Course course) throws SQLException {
  String sql = "INSERT INTO Courses (name, url) VALUES (?, ?);";
  PreparedStatement pst = conn.prepareStatement(sql,
      Statement.RETURN_GENERATED_KEYS);
  pst.setString(1, course.getName());
  pst.setString(2, course.getUrl());

  pst.executeUpdate();
  ResultSet rs = pst.getGeneratedKeys();
  rs.next();
  course.setId(rs.getInt(1));
}

Read Course

1
2
3
4
5
6
7
@Test
public void readCourseWorks() throws SQLException {
  Course c1 = new Course("OOSE", "jhu-oose.com");
  unit.createCourse(c1);
  Course c2 = unit.readCourse(c1.getId());
  assertEquals(c1, c2);
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
public Course readCourse(int courseId) throws SQLException {
  String sql = "SELECT * FROM Courses WHERE id = ?;";
  PreparedStatement pst = conn.prepareStatement(sql);
  pst.setInt(1, courseId);
  ResultSet rs = pst.executeQuery();

  Course course = null;
  while (rs.next()) {
    int id = rs.getInt("id");
    course = new Course(rs.getString("name"), rs.getString("url"));
    course.setId(id);
  }

  return course;
}

Update Course

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
@Test
public void updateCourseWorks() throws SQLException {
  Course c1 = new Course("OOSE", "jhu-oose.com");
  unit.createCourse(c1);

  c1.setUrl("www.jhu-oose.com");
  unit.updateCourse(c1);

  Course c2 = unit.readCourse(c1.getId());
  assertEquals(c1, c2);
}

1
2
3
4
5
6
7
8
9
// PRE: id of course is already set by the database.
public void updateCourse(Course course) throws SQLException {
  String sql = "UPDATE Courses Set name = ?, url = ? WHERE id = ?;";
  PreparedStatement pst = conn.prepareStatement(sql);
  pst.setString(1, course.getName());
  pst.setString(2, course.getUrl());
  pst.setInt(3, course.getId());
  pst.executeUpdate();
}

Delete Course

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
@Test
public void deleteCourseWorks() throws SQLException {
  Course c1 = new Course("OOSE", "jhu-oose.com");
  unit.createCourse(c1);

  unit.deleteCourse(c1.getId());

  Course c2 = unit.readCourse(c1.getId());
  assertNull(c2);
}

1
2
3
4
5
6
public void deleteCourse(int courseId) throws SQLException {
  String sql = "DELETE FROM Courses where id = ?;";
  PreparedStatement pst = conn.prepareStatement(sql);
  pst.setInt(1, courseId);
  pst.executeUpdate();
}

Exercise

A common "read" operation is searching for courses that match some criteria. Complete the implementation of the following method.

1
2
3
4
public List<Course> readCourses(String name) throws SQLException {
  // TODO: implement me!
  return null;
}

Here is a test for it:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
@Test
public void readCoursesWorks() throws SQLException {
  Course c1 = new Course("OOSE", "jhu-oose.com");
  Course c2 = new Course("Intro OS", "jhu-os.com");
  Course c3 = new Course("Data Structures", "jhu-ds.com");
  unit.createCourse(c1);
  unit.createCourse(c2);
  unit.createCourse(c3);

  List<Course> result = unit.readCourses("os");
  assertTrue(result.contains(c1));
  assertTrue(result.contains(c2));
  assertFalse(result.contains(c3));
}