Java:
package group.msg.gby.persistence;
//import are hidden
...
public class ProtocolDAO {
private static ProtocolDAO instance;
private static ProtocolDAO instance;
/**
* Returns always the same instance of ProtocolDAO.
*
* @return an instance of ProtocolDAO.
*/
public static ProtocolDAO getInstance() {
if (null == instance) {
instance = new ProtocolDAO();
}
return instance;
}
/**
* Inserts a new protocol with protocol entries into the database.
*
* @param protocol the Protocol object to be inserted.
* @throws SQLException if a database access error occurs.
*/
public void createProtocolInDB(Protocol protocol) throws SQLException {
String sql = "INSERT INTO protocol (protocol_id, meeting_date, start_time) VALUES (?, ?, ?)";
int id = getNextValueOfSequenceForTableProtocol();
try (PreparedStatement statement = DB_Connection.getStatement().getConnection().prepareStatement(sql)) {
statement.setInt(1, id);
statement.setDate(2, getValidDate(protocol.getMeetingDate()));
statement.setTime(3, getValidTime(protocol.getStartTime()));
statement.executeUpdate();
}
String sql2 = "INSERT INTO protocolentry (protocol_entry_id, topic, agenda_item, " +
"sub_agenda_item, resubmission_date, delete_date, remark, protocol_id)" +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
for (ProtocolEntry pe : protocol.getProtocolEntries()) {
try (PreparedStatement statement = DB_Connection.getStatement().getConnection().prepareStatement(sql2)) {
statement.setInt(1, getNextValueOfSequenceForTableProtocolEntry());
statement.setString(2, pe.getTopic());
statement.setInt(3, pe.getItemNumber());
statement.setInt(4, pe.getSubItemNumber());
statement.setDate(5, getValidDate(pe.getResubmissionDate()));
statement.setDate(6, getValidDate(pe.getDeleteDate()));
statement.setString(7, pe.getRemark());
statement.setInt(8, id);
statement.executeUpdate();
}
}
}
/**
* Searches for protocols in the database and returns a List of protocols that match the search criteria.
*
* @param meetingDate the meeting date to search for.
* @param resubmissionDate is later than the resubmission date to search for.
* @param deleteDate is later than the delete date to search for.
* @param wordTopic is, in the column topic to search for.
* @param wordRemark is, in the column remark to search for.
* @return a List of Protocol objects that match the search criteria.
* @throws SQLException if a database access error occurs.
*/
public List<Protocol> searchInProtocolsInDBAsList(LocalDate meetingDate, LocalDate resubmissionDate, LocalDate deleteDate,
String wordTopic, String wordRemark) throws SQLException {
List<Protocol> myList = new ArrayList<>();
String sql = "SELECT pe.protocol_id, pe.protocol_entry_id FROM protocol p, protocolentry pe " +
"WHERE pe.protocol_id=p.protocol_id " +
"AND topic ilike ? " +
"AND remark ilike ? " +
"AND (meeting_date = COALESCE(?, meeting_date)) " +
"AND (COALESCE(resubmission_date,'9999-12-30') < COALESCE(?, resubmission_date + 1, '9999-12-31')) " +
"AND (COALESCE(delete_date, '9999-12-30') < COALESCE(?, delete_date + 1, '9999-12-31')) " +
"ORDER BY meeting_date, delete_date DESC";
try (PreparedStatement statement = DB_Connection.getStatement().getConnection().prepareStatement(sql)) {
statement.setString(1, (null != wordTopic ? ("%" + wordTopic.trim() + "%") : "%%" ));
statement.setString(2, (null != wordRemark ? ("%" + wordRemark.trim() + "%") : "%%" ));
statement.setDate(3, getValidDate(meetingDate));
statement.setDate(4, getValidDate(resubmissionDate));
statement.setDate(5, getValidDate(deleteDate));
ResultSet rs = statement.executeQuery();
while (rs.next()) {
Protocol protocol = getProtocol(rs.getInt(1), false);
ProtocolEntry protocolEntry = getProtocolEntry(rs.getInt(2));
boolean found = false;
for (Protocol p : myList) {
if (p.equals(protocol)) {
p.addEntry(protocolEntry);
found = true;
break;
}
}
if (!found) {
myList.add(protocol);
protocol.addEntry(protocolEntry);
}
}
}
return myList;
}
/**
* Retrieves all the {@link ProtocolEntry} objects associated with a particular protocol identified by the given
* {@code protocolID}, sorted by their {@code delete_date} in descending order.
*
* @param protocolID the ID of the protocol to retrieve the entries for
* @return a {@link Set} of {@link ProtocolEntry} objects associated with the protocol
* @throws SQLException if an error occurs while accessing the database
*/
public Set<ProtocolEntry> getAllProtocolEntriesOfAProtocol(int protocolID) throws SQLException {
String sql = "SELECT * FROM protocolentry WHERE protocol_id = ? ORDER BY delete_date DESC";
Set<ProtocolEntry> result = new HashSet<>();
try (PreparedStatement statement = DB_Connection.getStatement().getConnection().prepareStatement(sql)) {
statement.setInt(1, protocolID);
ResultSet rs = statement.executeQuery();
while (rs.next()) {
ProtocolEntry pe = new ProtocolEntry(
rs.getInt(1),
rs.getString(2),
rs.getInt(3),
rs.getInt(4),
getValidLocalDate(rs.getString(5)),
getValidLocalDate(rs.getString(6)),
rs.getString(7));
result.add(pe);
}
}
return result;
}
/**
* Updates the protocol entry in the database with the given ProtocolEntry object.
*
* @param protocolEntry The ProtocolEntry object containing the updated information.
* @throws SQLException If there is an error accessing the database.
*/
public void updateProtocolEntry(ProtocolEntry protocolEntry) throws SQLException {
String sql = "UPDATE protocolentry SET topic = ?, agenda_item = ?, sub_agenda_item = ?, " +
"resubmission_date = ?, delete_date = ?, remark = ? WHERE protocol_entry_id = ?";
try (PreparedStatement statement = DB_Connection.getStatement().getConnection().prepareStatement(sql)) {
statement.setString(1, protocolEntry.getTopic());
statement.setInt(2, protocolEntry.getItemNumber());
statement.setInt(3, protocolEntry.getSubItemNumber());
statement.setDate(4, getValidDate(protocolEntry.getResubmissionDate()));
statement.setDate(5, getValidDate(protocolEntry.getDeleteDate()));
statement.setString(6, protocolEntry.getRemark());
statement.setInt(7, protocolEntry.getProtocolEntryID());
statement.executeUpdate();
}
}
/**
* Deletes a protocol entry from the database with the given protocol entry ID.
*
* @param protocolEntryID the ID of the protocol entry to be deleted
* @throws SQLException if an SQL exception occurs while attempting to delete the protocol entry
*/
public void deleteProtocolEntryInDB(int protocolEntryID) throws SQLException {
String sql = "DELETE FROM protocolentry WHERE protocol_entry_id = ?";
try (PreparedStatement statement = DB_Connection.getStatement().getConnection().prepareStatement(sql)) {
statement.setInt(1, protocolEntryID);
statement.executeUpdate();
}
}
}
