Java and Oracle 26ai Web Applications - Exercise

Retrieve a product and its image reference with JDBC

Objective: Complete a server-side Java method that retrieves published product data from Oracle AI Database 26ai, and explain how a web application presents the data and image.

Background and overview

Provided assumptions

• Use Java 17 or later syntax with a compatible runtime and Oracle JDBC driver. A configured, pooled DataSource is supplied to the method. Pool creation, credentials, TLS, and deployment are outside this task.
• The application account can query PRODUCTS. PRODUCT_ID is a positive NUMBER(18,0) primary key.
• PRODUCT_NAME, CURRENCY_CODE, and PUBLICATION_STATUS are non-null character columns. PRICE is a non-null NUMBER(12,2); STOCK_QUANTITY is a non-null NUMBER(9,0).
• IMAGE_MEDIA_ID is a nullable NUMBER(18,0) reference to media metadata, and IMAGE_ALT_TEXT is nullable character data. Media approval and delivery are checked separately.
• Only rows with PUBLICATION_STATUS = 'PUBLISHED' may be returned by this public lookup.

Instructions

1. Complete the Java method (6 points). Reject a nonpositive product ID, bind the ID using a PreparedStatement, retrieve only the requested published product, map all seven result fields, and return Optional.empty() when no row matches. Use BigDecimal for price, preserve a null media ID, and close JDBC resources with try-with-resources.
2. Explain the request path (2 points). Explain why database access belongs in the server application and why binding the product ID is preferable to concatenating request text into SQL. Describe how the controller should handle an invalid ID and a product that is missing or unpublished.
3. Describe product and image presentation (2 points). Identify useful information to display beside the image. Explain how the browser retrieves the approved image and what happens when no image is available.

Starter code

Copy this class and replace the TODO method body. No download is required. You do not need to implement a servlet, REST framework, JSON serializer, or image-delivery endpoint.

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Optional;
import javax.sql.DataSource;

public final class ProductRepository {
    public record Product(long productId, String name,
                          BigDecimal price, String currencyCode,
                          int stockQuantity, Long imageMediaId,
                          String imageAltText) {}
    public static Optional<Product> findPublishedProduct(
            DataSource dataSource, long productId) throws SQLException {
        // TODO: validate productId, query one published product using a bind,
        // map its values, handle a nullable image ID, and close resources.
        throw new UnsupportedOperationException("Complete this method");
    }
}

Exercise scoring

This exercise is worth 10 points. After submission, compare your work with the reference solution and rubric. The website displays your response; it does not compile Java, execute SQL, or calculate a score.

Submitting your exercise