public void delete(Student student) throws Exception { Connection connection = this.dataSource.getConnection(); for (Exam exam : student.getExams()) { // TODO voglio eliminare tutto this.examsRepository.delete(exam, student); } String insert = "delete from students where code = ?"; PreparedStatement statement = connection.prepareStatement(insert); statement.setInt(1, student.getCode()); statement.executeUpdate(); // release resources statement.close(); connection.close(); }
public List<Student> findByBirthDate(java.util.Date birthDate) throws Exception { List<Student> students = new LinkedList<Student>(); Connection connection = this.dataSource.getConnection(); String query = "select * from students left join exams on student_code = code where birthdate=? order by code"; PreparedStatement statement = connection.prepareStatement(query); statement.setDate(1, new java.sql.Date(birthDate.getTime())); ResultSet result = statement.executeQuery(); Student prevStudent = null; while (result.next()) { if (prevStudent == null || result.getInt("code") != prevStudent.getCode()) { long secs; Student student = new Student(); student.setCode(result.getInt("code")); student.setFirstName(result.getString("firstname")); student.setLastName(result.getString("lastname")); secs = result.getDate("birthdate").getTime(); birthDate = new java.util.Date(secs); student.setBirthDate(birthDate); prevStudent = student; students.add(student); } if (result.getString("course") != null) { Exam e = new Exam(); e.setCourse(result.getString("course")); e.setDate(new java.util.Date(result.getDate("date").getTime())); e.setScore(result.getInt("code")); prevStudent.addExam(e); } } // release resources result.close(); statement.close(); connection.close(); return students; }
public Student findByPrimaryKey(int code) throws Exception { Connection connection = this.dataSource.getConnection(); String query = "select * from students where code=?"; PreparedStatement statement = connection.prepareStatement(query); statement.setInt(1, code); ResultSet result = statement.executeQuery(); Student student = null; if (result.next()) { if (student == null) { long secs; java.util.Date birthDate; student = new Student(); student.setCode(result.getInt("code")); student.setFirstName(result.getString("firstname")); student.setLastName(result.getString("lastname")); secs = result.getDate("birthdate").getTime(); birthDate = new java.util.Date(secs); student.setBirthDate(birthDate); } if (result.getString("course") != null) { Exam e = new Exam(); e.setCourse(result.getString("course")); e.setDate(new java.util.Date(result.getDate("date").getTime())); e.setScore(result.getInt("score")); student.addExam(e); } } // release resources result.close(); statement.close(); connection.close(); return student; }
public void persist(Student student) throws SQLException { Connection connection = this.dataSource.getConnection(); PreparedStatement statement = null; try { if (findByPrimaryKey(student.getCode()) != null) { System.err.println("the student already exists"); return; } String insert = "insert into students(code, firstname, lastname, birthDate) values (?,?,?,?)"; statement = connection.prepareStatement(insert); statement.setInt(1, student.getCode()); statement.setString(2, student.getFirstName()); statement.setString(3, student.getLastName()); Date t = new Date(); t.getTime(); statement.setDate(4, new java.sql.Date(student.getBirthDate().getTime())); statement.executeUpdate(); for (Exam exam : student.getExams()) { // TODO se ci sono degli errori qui? che succede? // Come possiamo fare in modo che: o inserisco tutti gli esami e // lo studente o niente? this.examsRepository.persist(exam, student); } // TODO // Che succede se eccezione prima? } catch (Exception e) { e.printStackTrace(); } finally { statement.close(); connection.close(); } }