publicclassJdbcCourseCrudPersister{privateConnectionconn;// PRE: conn is an open connection to a SQLite database with Courses table.publicJdbcCourseCrudPersister(Connectionconn){this.conn=conn;}// PRE: id of course is not set yet.publicvoidcreateCourse(Coursecourse)throwsSQLException{}publicCoursereadCourse(intcourseId)throwsSQLException{returnnull;}// PRE: id of course is already set by the database.publicvoidupdateCourse(Coursecourse)throwsSQLException{}publicvoiddeleteCourse(intcourseId)throwsSQLException{}}
It's good time for test-driven development. Create the following test suit (it must be created in src/test/java/persistence):
publicclassJdbcCourseCrudPersisterTest{privateJdbcCourseCrudPersisterunit;privateConnectionconn;@BeforepublicvoidsetUp()throwsSQLException{finalStringURI="jdbc:sqlite:./Test.db";conn=DriverManager.getConnection(URI);Stringsql="CREATE TABLE IF NOT EXISTS Courses(id INTEGER PRIMARY KEY, name VARCHAR(30), url VARCHAR(200));";PreparedStatementpst=conn.prepareStatement(sql);pst.execute();unit=newJdbcCourseCrudPersister(conn);}// TODO add tests!@AfterpublicvoidtearDown()throwsException{PreparedStatementpst=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.
// PRE: id of course is not set yet.publicvoidcreateCourse(Coursecourse)throwsSQLException{Stringsql="INSERT INTO Courses (name, url) VALUES (?, ?);";PreparedStatementpst=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);pst.setString(1,course.getName());pst.setString(2,course.getUrl());pst.executeUpdate();ResultSetrs=pst.getGeneratedKeys();rs.next();course.setId(rs.getInt(1));}
publicCoursereadCourse(intcourseId)throwsSQLException{Stringsql="SELECT * FROM Courses WHERE id = ?;";PreparedStatementpst=conn.prepareStatement(sql);pst.setInt(1,courseId);ResultSetrs=pst.executeQuery();Coursecourse=null;while(rs.next()){intid=rs.getInt("id");course=newCourse(rs.getString("name"),rs.getString("url"));course.setId(id);}returncourse;}
// PRE: id of course is already set by the database.publicvoidupdateCourse(Coursecourse)throwsSQLException{Stringsql="UPDATE Courses Set name = ?, url = ? WHERE id = ?;";PreparedStatementpst=conn.prepareStatement(sql);pst.setString(1,course.getName());pst.setString(2,course.getUrl());pst.setInt(3,course.getId());pst.executeUpdate();}
publicvoiddeleteCourse(intcourseId)throwsSQLException{Stringsql="DELETE FROM Courses where id = ?;";PreparedStatementpst=conn.prepareStatement(sql);pst.setInt(1,courseId);pst.executeUpdate();}