Executing Stored Functions & Procedures: PostgreSQL PL/pgSQL, Functional Invocation via SELECT, & Cross-Table Key Resolution

package com.rensaputra.lil.data.entity;

import java.math.BigDecimal;
import java.util.Objects;
import java.util.UUID;

public class SimpleProduct {

    private UUID productId;
    private String name;
    private BigDecimal price;
    private UUID vendorId;
    private String vendorName;
    private String contact;
    private String phoneNumber;
    private String email;
    private String address;

    public SimpleProduct() {
    }

    public SimpleProduct(UUID productId, String name, BigDecimal price, UUID vendorId,
                         String vendorName, String contact, String phoneNumber,
                         String email, String address) {
        this.productId = productId;
        this.name = name;
        this.price = price;
        this.vendorId = vendorId;
        this.vendorName = vendorName;
        this.contact = contact;
        this.phoneNumber = phoneNumber;
        this.email = email;
        this.address = address;
    }

    public UUID getProductId() {
        return productId;
    }

    public void setProductId(UUID productId) {
        this.productId = productId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public BigDecimal getPrice() {
        return price;
    }

    public void setPrice(BigDecimal price) {
        this.price = price;
    }

    public UUID getVendorId() {
        return vendorId;
    }

    public void setVendorId(UUID vendorId) {
        this.vendorId = vendorId;
    }

    public String getVendorName() {
        return vendorName;
    }

    public void setVendorName(String vendorName) {
        this.vendorName = vendorName;
    }

    public String getContact() {
        return contact;
    }

    public void setContact(String contact) {
        this.contact = contact;
    }

    public String getPhoneNumber() {
        return phoneNumber;
    }

    public void setPhoneNumber(String phoneNumber) {
        this.phoneNumber = phoneNumber;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (!(o instanceof SimpleProduct that)) return false;
        return Objects.equals(productId, that.productId);
    }

    @Override
    public int hashCode() {
        return Objects.hash(productId);
    }

    @Override
    public String toString() {
        return "SimpleProduct{" +
                "productId=" + productId +
                ", name='" + name + '\'' +
                ", price=" + price +
                ", vendorId=" + vendorId +
                ", vendorName='" + vendorName + '\'' +
                ", contact='" + contact + '\'' +
                ", phoneNumber='" + phoneNumber + '\'' +
                ", email='" + email + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

package com.rensaputra.lil.data.dao;

import com.rensaputra.lil.data.util.DatabaseUtils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.UUID;
import java.util.logging.Logger;

public class SimpleProductDao {

    private static final Logger LOGGER = Logger.getLogger(SimpleProductDao.class.getName());

    // Functional invocation syntax: queries the stored function as a tabular source
    private static final String CREATE = "SELECT * FROM createproduct(?, ?, ?)";

    /**
     * Executes the stored database function 'createproduct' to insert a product record.
     *
     * @param name       The product name
     * @param price      The product price
     * @param vendorName The vendor name to resolve via subquery
     * @return The generated product UUID, or null on failure
     */
    public UUID createProduct(String name, BigDecimal price, String vendorName) {
        Connection connection = DatabaseUtils.getConnection();
        UUID returnVal = null;

        try {
            // Begin explicit transaction
            connection.setAutoCommit(false);

            PreparedStatement statement = connection.prepareStatement(CREATE);
            statement.setString(1, name);
            statement.setBigDecimal(2, price);
            statement.setString(3, vendorName);

            // Execute the function query
            ResultSet rs = statement.executeQuery();

            if (rs.next()) {
                // The returned column name in the ResultSet matches the function identifier
                returnVal = (UUID) rs.getObject("createproduct");
            }

            // Finalize writes to the database
            connection.commit();

            // Clean up handles
            rs.close();
            statement.close();

        } catch (SQLException e) {
            // Roll back changes if an error occurs during execution
            try {
                connection.rollback();
            } catch (SQLException ex) {
                DatabaseUtils.handleSQLException("SimpleProductDao.create.rollback", ex, LOGGER);
            }
            DatabaseUtils.handleSQLException("SimpleProductDao.create", e, LOGGER);
        }

        return returnVal;
    }
}

package com.rensaputra.lil;

import com.rensaputra.lil.data.dao.CustomerDao;
import com.rensaputra.lil.data.dao.ServiceDao;
import com.rensaputra.lil.data.dao.SimpleProductDao;
import com.rensaputra.lil.data.entity.Customer;
import com.rensaputra.lil.data.entity.Service;

import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;
import java.util.UUID;

public class App {

    public static void main(String[] args) {
        // ==========================================
        // 1. SERVICES CRUD
        // ==========================================
        ServiceDao serviceDao = new ServiceDao();
        List<Service> services = serviceDao.getAll();
        System.out.println("**** SERVICES ****\n\n*** GET ALL ***");
        services.forEach(System.out::println);

        if (!services.isEmpty()) {
            Service first = services.get(0);
            Optional<Service> serviceOpt = serviceDao.getOne(first.getServiceId());
            System.out.println("\n*** GET ONE ***\n");
            serviceOpt.ifPresent(System.out::println);
        }

        Service newService = new Service();
        newService.setName("FooBarBaz" + System.currentTimeMillis());
        newService.setPrice(new BigDecimal("4.35"));
        newService = serviceDao.create(newService);
        System.out.println("\n*** CREATE ***\n\n" + newService);

        newService.setPrice(new BigDecimal("13.45"));
        newService = serviceDao.update(newService);
        System.out.println("\n*** UPDATE ***\n\n" + newService);

        serviceDao.delete(newService.getServiceId());
        System.out.println("\n*** DELETE ***\nDelete is done.");

        // ==========================================
        // 2. CUSTOMERS CRUD
        // ==========================================
        CustomerDao customerDao = new CustomerDao();
        System.out.println("\n\n**** CUSTOMERS ****\n\n*** GET ALL ***");
        List<Customer> customers = customerDao.getAll();
        System.out.println("Retrieved: " + customers.size() + " customers");

        if (!customers.isEmpty()) {
            Optional<Customer> custOpt = customerDao.getOne(customers.get(0).getCustomerId());
            System.out.println("\n*** GET ONE ***\n");
            custOpt.ifPresent(System.out::println);
        }

        Customer newCust = new Customer();
        newCust.setFirstName("Jane");
        newCust.setLastName("Doe");
        newCust.setEmail("jane.doe." + System.currentTimeMillis() + "@example.com");
        newCust.setPhone("555-0199");
        newCust.setAddress("123 Innovation Way");
        newCust = customerDao.create(newCust);
        System.out.println("\n*** CREATE ***\n\n" + newCust);

        newCust.setAddress("456 Enterprise Blvd");
        newCust = customerDao.update(newCust);
        System.out.println("\n*** UPDATE ***\n\n" + newCust);

        customerDao.delete(newCust.getCustomerId());
        System.out.println("\n*** DELETE ***\nCustomer delete is done.");

        // ==========================================
        // 3. STORED FUNCTION: SIMPLE PRODUCT
        // ==========================================
        System.out.println("\n\n*** SIMPLE PRODUCT ***");
        SimpleProductDao spdao = new SimpleProductDao();
        UUID productId = spdao.createProduct(
                "foobarbaz" + System.currentTimeMillis(),
                new BigDecimal("45.67"),
                "Jaloo"
        );
        System.out.println("Product Created via Stored Function. ID: " + productId);
    }
}

String sql = "SELECT createproduct(?, ?, ?) AS new_id";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
    stmt.setString(1, "Widget");
    stmt.setBigDecimal(2, new BigDecimal("19.99"));
    stmt.setString(3, "NonExistentVendor");

    try (ResultSet rs = stmt.executeQuery()) {
        if (rs.next()) {
            UUID id = (UUID) rs.getObject("new_id");
            System.out.println("ID: " + id);
        }
    }
}