public int insertCourse(Course course) {
PreparedStatement insertStatement, selectStatement;
Connection conn = ConnectionUtil.getOracleConnection();
int responseCode = 0;
try {
String selectSql = "SELECT COUNT(*) AS TOTAL FROM COURSE WHERE COURSECODE = ?";
selectStatement = conn.prepareStatement(selectSql);
selectStatement.setString(1, course.getCode());
ResultSet rs = selectStatement.executeQuery();
int rowCount = -1;
while (rs.next()) rowCount = rs.getInt("TOTAL");
if (conn != null && rowCount == 0) {
String sql =
"INSERT INTO COURSE (COURSEID, TITLE, COURSECODE, MAXAPPLICANTS) VALUES (COURSEID_SEQ.NEXTVAL, ?, ?, ?)";
insertStatement = conn.prepareStatement(sql);
insertStatement.setString(1, course.getTitle());
insertStatement.setString(2, course.getCode());
insertStatement.setInt(3, course.getMaxApplicants());
responseCode = insertStatement.executeUpdate();
}
} catch (SQLException e) {
e.printStackTrace();
}
return responseCode;
}
public CourseInstance getCourseInstance(int instanceId) {
CourseInstance courseInstance = null;
PreparedStatement selectStatement;
try {
String query =
"SELECT * FROM COURSE, COURSEINSTANCE WHERE INSTANCEID = ? AND COURSE.COURSEID = COURSEINSTANCE.COURSEID";
selectStatement = ConnectionUtil.getOracleConnection().prepareStatement(query);
selectStatement.setMaxRows(1);
selectStatement.setInt(1, instanceId);
ResultSet rs = selectStatement.executeQuery();
List<CourseInstance> instances = resultSetToList(rs);
if (instances.size() > 0) courseInstance = instances.stream().findFirst().get();
} catch (SQLException e) {
e.printStackTrace();
}
return courseInstance;
}
public List<CourseInstance> getCourseByStartAndEndDate(Date start, Date end) {
List<CourseInstance> instances = new ArrayList<>();
PreparedStatement statement;
try {
String query =
"SELECT * "
+ "FROM COURSE, COURSEINSTANCE "
+ "WHERE COURSE.COURSEID = COURSEINSTANCE.COURSEID AND "
+ "COURSEINSTANCE.STARTDATE BETWEEN TO_DATE(?, 'dd-MM-yyyy') AND TO_DATE(?, 'dd-MM-yyyy')";
statement = ConnectionUtil.getOracleConnection().prepareStatement(query);
statement.setString(1, DateUtil.toString(start));
statement.setString(2, DateUtil.toString(end));
ResultSet rs = statement.executeQuery();
instances = resultSetToList(rs);
} catch (SQLException e) {
e.printStackTrace();
}
return instances;
}
public List<CourseInstance> getCourseInstancesByStudentId(int studentid) {
List<CourseInstance> instances = new ArrayList<>();
PreparedStatement statement;
try {
String query =
"SELECT * "
+ "FROM COURSE, COURSEINSTANCE, ENROLLMENT "
+ "WHERE COURSE.COURSEID = COURSEINSTANCE.COURSEID AND"
+ " COURSEINSTANCE.INSTANCEID = ENROLLMENT.COURSEINSTANCEID AND"
+ " ENROLLMENT.STUDENTID = ?";
statement = ConnectionUtil.getOracleConnection().prepareStatement(query);
statement.setInt(1, studentid);
ResultSet rs = statement.executeQuery();
instances = resultSetToList(rs);
} catch (SQLException e) {
e.printStackTrace();
}
return instances;
}
public Course getCourseByCode(String code) {
Course course = null;
PreparedStatement selectStatement;
try {
String query = "SELECT * FROM COURSE WHERE COURSECODE = ?";
selectStatement = ConnectionUtil.getOracleConnection().prepareStatement(query);
selectStatement.setMaxRows(1);
selectStatement.setString(1, code);
ResultSet rs = selectStatement.executeQuery();
while (rs.next())
course =
CourseBuilder.createCourseBuilder()
.setId(rs.getInt("COURSEID"))
.setCode(rs.getString("COURSECODE"))
.setTitle(rs.getString("TITLE"))
.buildCourse();
} catch (SQLException e) {
e.printStackTrace();
}
return course;
}
public int insertCourseInstance(CourseInstance instance) {
PreparedStatement insertStatement;
Connection conn = ConnectionUtil.getOracleConnection();
int responseCode = -1;
try {
String insertSql =
"INSERT INTO COURSEINSTANCE (INSTANCEID, COURSEID, DURANCE, STARTDATE, PRICE) VALUES (INSTANCEID_SEQ.NEXTVAL, ?, ?, TO_DATE(?, 'dd-MM-yyyy'), ?)";
if (conn != null) {
insertCourse(instance.getCourse());
Course course = getCourseByCode(instance.getCourse().getCode());
insertStatement = conn.prepareStatement(insertSql);
insertStatement.setInt(1, course.getId());
insertStatement.setInt(2, instance.getDurance());
insertStatement.setString(3, DateUtil.toString(instance.getStartDate()));
insertStatement.setDouble(4, instance.getPrice());
responseCode = insertStatement.executeUpdate();
}
} catch (SQLException e) {
e.printStackTrace();
}
return responseCode;
}
