示例#1
0
 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;
 }
示例#2
0
 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;
 }
示例#3
0
 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;
 }
示例#4
0
 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;
 }
示例#5
0
 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;
 }
示例#6
0
 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;
 }