How is a database query performed and what result processing is done?

Via JDBC API.

1. Creating a connection (Connection): Obtain a connection to the database using a driver for the specific database and a connection URL.

// Getting a connection
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/_database_name_", "user", "password");

2. Creating a statement or prepared statement object: Create an object to send SQL queries. PreparedStatement is preferred for protection against SQL injection and better performance for repeated queries.

// Creating a statement object
Statement statement = connection.createStatement();
// Or PreparedStatement
PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM _table_ WHERE _field_ = ?");
preparedStatement.setString(1, "value");

3. Executing queries (executeQuery() or executeUpdate()): executeQuery() is used for SELECT queries returning a result set. executeUpdate() is for INSERT, UPDATE, DELETE, returning the number of affected rows.

// Executing SELECT query
ResultSet resultSet = statement.executeQuery("SELECT * FROM _table_");

// Executing UPDATE query
int rowsAffected = statement.executeUpdate("UPDATE _table_ SET _field_ = 'new_value' WHERE _field_ = 'old_value'");

4. Processing results (for SELECT): Iterate over the ResultSet object, extracting data from columns by index or name.

// Processing results
while (resultSet.next()) {
    int id = resultSet.getInt("_column_name_");
    String name = resultSet.getString("_another_column_name_");
    // Process the data
    System.out.println("ID: " + id + ", Name: " + name);
}

5. Closing resources: It is important to close all resources (ResultSet, Statement/PreparedStatement, Connection) in a finally block or use try-with-resources for automatic closing.

// Using try-with-resources
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/_database_name_", "user", "password");
     Statement statement = connection.createStatement()) {

    ResultSet resultSet = statement.executeQuery("SELECT * FROM _table_");
    while (resultSet.next()) {
        // Process data
    }
} catch (SQLException e) {
    // Handle exception
    e.printStackTrace();
}

Processing results:

• Iterate over ResultSet with while (resultSet.next()).
• Extract data from columns using get*() methods (getInt(), getString(), getDate(), etc.) by column name or index (starting from 1).
• Map processed data to Java objects (POJO).
• Handle SQLException during database operations.
• For modification queries (executeUpdate()), check the number of affected rows to confirm success.