Получение данных из связанных таблиц в Java

Сделаем запрос, который достанет всех юзеров вместе с их городами. Для этого нужна команда LEFT JOIN:

Ее синтаксис выглядит следующим образом:

SELECT поля FROM имя_таблицы
	LEFT JOIN имя_связанной_таблицы ON условие_связи
WHERE условие_выборки

Разберем отдельные части синтаксиса этой команды.

Поля

Так как выборка идет из нескольких таблиц, выборка всех полей через * не будет работать. Следующий запрос выберет поля только из основной таблицы, но не из связанной:

SELECT *

Чтобы данные выбирались из всех таблиц, перед * указывают имя таблицы для выборки:

SELECT users.*, cities.*

Либо перечисляют нужные поля с указанием имени таблицы перед ними:

SELECT users.name, cities.name

Эти два способа имеют проблему. Если поля в таблицах имеют одинаковые имена, в результате запроса произойдет конфликт имен и останется только одно поле, а второго не будет. В Java колонку читают через getString, и второе значение с тем же именем уже не достать.

Для решения проблемы конфликтные имена переименовывают через команду AS:

SELECT users.name, cities.name AS city_name

Связь

После команды ON указывают поля из двух таблиц, по которым осуществляется связь. В этом случае это поле id из таблицы с городами и поле city_id из таблицы с юзерами:

ON cities.id = users.city_id

Запрос

В итоге запрос, который достанет юзеров вместе с их городами, будет выглядеть следующим образом:

SELECT
	users.name, cities.name AS city_name
FROM
	users
LEFT JOIN cities ON cities.id = users.city_id

В Java эту строку передают в executeQuery. Колонку после AS читают по новому имени:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Main {
	public static void main(String[] args) throws Exception {
		try (Connection connection = DriverManager.getConnection("jdbc:sqlite:getting_users.db")) {
			connection.createStatement().execute(
				"CREATE TABLE IF NOT EXISTS cities (id INTEGER PRIMARY KEY, name TEXT NOT NULL)"
			);
			connection.createStatement().execute(
				"CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city_id INTEGER)"
			);
			try (Statement statement = connection.createStatement()) {
				ResultSet result = statement.executeQuery(
					"""
					SELECT
						users.name, cities.name AS city_name
					FROM
						users
					LEFT JOIN cities ON cities.id = users.city_id
					"""
				);
				while (result.next()) {
					System.out.println(result.getString("name"));
					System.out.println(result.getString("city_name"));
				}
			}
		}
	}
}

Пусть есть таблица с товарами и таблица с их категориями. Напишите запрос, который достанет названия товаров вместе с их категориями.