= Advanced Application Development =

== Transactions ==
=== Multi-Operation Transaction Documentation ===
In this application, transactional integrity guarantees that changes to entity profiles do not leave the schema with orphaned relational data. A prime example occurs when creating or updating a profile within ProfessorDAOImpl.java.

Because a professor's university affiliation is stored across separate boundaries (Professor table and the affiliated reference bridge table), modifying a professor requires writing to multiple tables sequentially. If the primary details save successfully but the university bridge mapping breaks, the database would fall out of sync. To eliminate this risk, the application explicitly controls boundaries via connection.setAutoCommit(false), manually invoking a group .commit() only when all steps conclude, or a .rollback() if a query throws an error.

Transactional Storage Implementation (ProfessorDAOImpl.java):
{{{
#!java
@Override
public void save(Professor professor) throws SQLException {
final String INSERT_PROF = "INSERT INTO Professor (name, surname, age, facultyid) VALUES (?, ?, ?, ?)";
final String INSERT_AFFILIATION = "INSERT INTO affiliated (university_id, professor_id) VALUES (?, ?)";

try (Connection conn = JDBCUtils.getConnection()) {
    // Step 1: Initialize the manual transaction boundary
    conn.setAutoCommit(false);
    
    try (PreparedStatement ps = conn.prepareStatement(INSERT_PROF, Statement.RETURN_GENERATED_KEYS)) {
        ps.setString(1, professor.getName());
        ps.setString(2, professor.getSurname());
        ps.setInt(3, professor.getAge());
        ps.setLong(4, professor.getFacultyid());
        ps.executeUpdate();

        // Fetch the primary key generated by the database
        ResultSet rs = ps.getGeneratedKeys();
        if (rs.next()) {
            long newProfId = rs.getLong(1);

            // Step 2: Conditional execution dependent on primary table completion
            if (professor.getUniversity() != null && professor.getUniversity().getId() != null) {
                try (PreparedStatement psAff = conn.prepareStatement(INSERT_AFFILIATION)) {
                    psAff.setLong(1, professor.getUniversity().getId());
                    psAff.setLong(2, newProfId);
                    psAff.executeUpdate();
                }
            }
        }
        // Step 3: Atomic save across both tables simultaneously
        conn.commit();
    } catch (SQLException e) {
        // Step 4: Full structural rollback if any single operation fails
        conn.rollback();
        throw e;
    }
}
}
}}}

Transactional Modification Implementation (ProfessorDAOImpl.java):
{{{
#!java
@Override
public void update(Professor professor) throws SQLException {
final String UPDATE_PROF = "UPDATE Professor SET name = ?, surname = ?, age = ?, facultyid = ? WHERE id = ?";
final String DELETE_AFFILIATION = "DELETE FROM affiliated WHERE professor_id = ?";
final String INSERT_AFFILIATION = "INSERT INTO affiliated (university_id, professor_id) VALUES (?, ?)";

try (Connection conn = JDBCUtils.getConnection()) {
    // Clear auto-commit to prevent intermediate modifications from tracking
    conn.setAutoCommit(false);
    try {
        // Operation 1: Update core attributes
        try (PreparedStatement ps = conn.prepareStatement(UPDATE_PROF)) {
            ps.setString(1, professor.getName());
            ps.setString(2, professor.getSurname());
            ps.setInt(3, professor.getAge());
            ps.setLong(4, professor.getFacultyid());
            ps.setLong(5, professor.getId());
            ps.executeUpdate();
        }

        // Operation 2: Flush out old relational mappings
        try (PreparedStatement psDel = conn.prepareStatement(DELETE_AFFILIATION)) {
            psDel.setLong(1, professor.getId());
            psDel.executeUpdate();
        }

        // Operation 3: Re-insert active relational state mappings
        if (professor.getUniversity() != null && professor.getUniversity().getId() != null) {
            try (PreparedStatement psIns = conn.prepareStatement(INSERT_AFFILIATION)) {
                psIns.setLong(1, professor.getUniversity().getId());
                psIns.setLong(2, professor.getId());
                psIns.executeUpdate();
            }
        }
        // Finalize unit of work safely
        conn.commit();
    } catch (SQLException e) {
        // Roll back to the original database state if any query faults
        conn.rollback();
        throw e;
    }
}
}
}}}

== Database Connection Pooling ==
=== Connection Setup Architecture ===
To handle database operations systematically, connection requests are routed through a singular provider method using the JDBCUtils.getConnection() structural pattern. Rather than instantiating hardcoded configuration attributes inside discrete controllers, the persistence layers pull a ready connection whenever a data action is initiated.

Resource safety is ensured at the runtime level via Java's try-with-resources declaration structures. Because the code instantiates Connection objects directly within try blocks, the underlying connection context is explicitly guaranteed to drop closed the microsecond a query cycle finishes, allowing the database driver to process active connection lifecycles without bleeding socket leakage.

=== Application Usage Examples ===
Below are standard methods highlighting how active database loops borrow a temporary connection resource context and immediately hand it back upon block termination.

Aggregation Query Implementation Example (findAverageAgeByFaculty):
{{{
#!java
@Override
public Map<String, Double> findAverageAgeByFaculty() {
String query = "SELECT f.name as faculty_name, AVG(p.age) as average_age " +
"FROM professor p JOIN faculty f ON f.id = p.facultyid " +
"GROUP BY f.name";
Map<String, Double> result = new HashMap<>();

// Establishing resource visibility block boundary
try (Connection connection = JDBCUtils.getConnection();
     PreparedStatement stmt = connection.prepareStatement(query)) {
    ResultSet rs = stmt.executeQuery();
    while (rs.next()) {
        result.put(rs.getString("faculty_name"), rs.getDouble("average_age"));
    }
} catch (SQLException e) {
    e.printStackTrace();
} // Connection implicitly closes right here, ensuring safe lifecycle recovery
return result;
}
}}}

Relational Joining Fetch Implementation Example (findStudentsByUniversityId):
{{{
#!java
@Override
public List findStudentsByUniversityId(int universityId) {
String query = "SELECT s.id, s.name, s.surname, s.location, s.studentindex, s.facultyid " +
"FROM student s " +
"JOIN faculty f ON s.facultyid = f.id " +
"JOIN university u ON f.university_id = u.id " +
"WHERE u.id = ?";
List students = new ArrayList<>();

// Requesting single connection endpoint from JDBCUtils factory
try (Connection conn = JDBCUtils.getConnection();
     PreparedStatement stmt = conn.prepareStatement(query)) {
    stmt.setInt(1, universityId);
    ResultSet rs = stmt.executeQuery();
    while (rs.next()) {
        students.add(new Student(
                rs.getLong("id"), rs.getString("name"), rs.getString("surname"),
                rs.getString("location"), rs.getInt("studentindex"), rs.getLong("facultyid")
        ));
    }
} catch (SQLException e) {
    throw new RuntimeException(e);
} // Lifecycle terminates cleanly, preventing connection exhaustion
return students;
}
}}}

= Advanced Application Development AI Usage =

Name of AI service/solution that was used: Gemini 3 Flash

URL: https://gemini.google.com/

Type of service/subscription: Free Tier

Final result: I reviewed my completed Java application prototype with the AI assistant to analyze how my data layer handles multi-table consistency and resource lifecycles. This allowed me to formally document my existing transaction blocks and connection patterns to fulfill the Phase P8 submission requirements without altering my working codebase.

Diagram:

Results in details / description:

Transaction Architecture Analysis: I had already built multi-table synchronization blocks into my ProfessorDAOImpl and FacultyDAOImpl classes to prevent orphan rows (such as managing the affiliated bridge table during updates, and handling professor dependencies during faculty deletion). I used the AI to review these sections to confirm they perfectly matched the criteria for formal multi-operation database transaction boundaries.

Resource Management Breakdown: I used the AI to help me articulate how Java’s try-with-resources blocks interact with my custom JDBCUtils.getConnection() handler, ensuring that active connection sockets are safely closed and processed without any database resource leaks.

Trac Syntax Verification: Because our course wiki utilizes specific formatting blocks ({{{ #!java ... }}}), I used the AI to convert my existing code snippets into perfectly structured markup so they render cleanly on the platform.

Entire AI usage log:

User: I have already finished writing my ProfessorDAOImpl.java and FacultyDAOImpl.java classes using plain JDBC. Can you look at my code and help me find the specific sections where I used transactions so I can document them for Phase P8?

AI: Analyzed the provided classes and pointed out that the save() and update() methods in ProfessorDAOImpl and the delete() method in FacultyDAOImpl already implement complete manual transaction workflows. They explicitly turn off auto-commit, update multiple tables sequentially to preserve foreign key constraints, and use clean commit() and rollback() blocks.

User: Perfect, I wrote those to stop the database from breaking. Now, how should I explain the connection infrastructure part since I am using my own JDBCUtils class instead of a third-party pooling library?

AI: Recommended highlighting the centralized design pattern of your JDBCUtils factory. It suggested explaining how your use of modern Java try-with-resources blocks automatically closes connection lifecycles the moment a data query loop terminates, preventing connection exhaustion.

User: Can you format these specific code snippets from my files into clean Trac Wiki markup blocks so I can copy-paste them directly onto my AdvancedApplicationDevelopment page?

AI: Structured the requested code examples into the proper wiki markup format, ensuring the Java syntax highlighting blocks and explanatory documentation comments were clean and readable.