JDBI JOIN example

 import org.jdbi.v3.core.Jdbi;

import org.jdbi.v3.core.handle.Handle;

import org.jdbi.v3.sqlobject.SqlObjectPlugin;

import java.math.BigDecimal;

import java.sql.Date;

import java.util.List;

public class Main {

    public static void main(String[] args) {

        // Create a Jdbi instance using your database connection

        Jdbi jdbi = Jdbi.create("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1");

        jdbi.installPlugin(new SqlObjectPlugin());

        // Use a handle to execute queries

        try (Handle handle = jdbi.open()) {

            // Create tables

            handle.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name VARCHAR)");

            handle.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, order_date DATE, FOREIGN KEY (user_id) REFERENCES users(id))");

            handle.execute("CREATE TABLE products (id INTEGER PRIMARY KEY, name VARCHAR, price DECIMAL)");

            handle.execute("CREATE TABLE order_details (id INTEGER PRIMARY KEY, order_id INTEGER, product_id INTEGER, quantity INTEGER, FOREIGN KEY (order_id) REFERENCES orders(id), FOREIGN KEY (product_id) REFERENCES products(id))");

            // Insert test data

            handle.execute("INSERT INTO users (id, name) VALUES (1, 'John Doe')");

            handle.execute("INSERT INTO orders (id, user_id, order_date) VALUES (1, 1, '2023-01-01')");

            handle.execute("INSERT INTO products (id, name, price) VALUES (1, 'Product A', 10.0)");

            handle.execute("INSERT INTO order_details (id, order_id, product_id, quantity) VALUES (1, 1, 1, 2)");

            // Fetch order summaries

            String sql = "SELECT u.name AS userName, o.order_date AS orderDate, p.name AS productName, od.quantity " +

                         "FROM users u " +

                         "JOIN orders o ON u.id = o.user_id " +

                         "JOIN order_details od ON o.id = od.order_id " +

                         "JOIN products p ON od.product_id = p.id";

            List<OrderSummary> summaries = handle.createQuery(sql)

                .mapToBean(OrderSummary.class)

                .list();

            for (OrderSummary summary : summaries) {

                System.out.println("User: " + summary.getUserName() +

                                   ", Order Date: " + summary.getOrderDate() +

                                   ", Product: " + summary.getProductName() +

                                   ", Quantity: " + summary.getQuantity());

            }

        }

    }

}