Вставка записей в БД в разные таблицы

Question

Можно ли одним запросом вставить данные в несколько таблиц в PostgreSQL?

У меня есть три таблицы: users, roles и users_roles. Я так понимаю, что нужно вначале вставить данные в самую первую таблицу (users), вернуть ключ записи, и вставить остальные данные с этим ключом. И так же нужно как-то узнавать ключ role и вставить его ключ при добавлении в таблицу users_roles (в этой таблице реализовано отношение многие-ко-многим). Как это можно сделать? Можно ли как-то несколько запросов на добавление поместить в один запрос и в одном методе? (Схему БД прикладываю)

Пока что пробовала реализовать вот так, но так не вышло:

public int insert(String login, String password, String name, int age, String role, int usersid, int rolesid) {
        String sqlInsert = "INSERT INTO users (login, password, name, age) VALUES (?, ?, ?, ?)";
        try {
            Class.forName("org.postgresql.Driver");
            try (Connection conn = DriverManager.getConnection(urlDB, usernameDB, passwordDB)) {
                try (PreparedStatement preparedStatement = conn.prepareStatement(sqlInsert)) {
                    preparedStatement.setString(1, login);
                    preparedStatement.setString(2, password);
                    preparedStatement.setString(3, name);
                    preparedStatement.setInt(4, age);
                    preparedStatement.setString(5, role);
                    insertUserRole(usersid, rolesid);
                    return preparedStatement.executeUpdate();
                }
            }
        } catch (Exception ex) {
            System.out.println("Connection Failed : " + ex.getMessage());
        }
        return 0;
    }

public int insertUserRole(int usersid, int rolesid) {
        String sqlInsert = "INSERT INTO users_roles(usersid, rolesid) VALUES (?, ?)";
        try {
            Class.forName("org.postgresql.Driver");
            try (Connection conn = DriverManager.getConnection(urlDB, usernameDB, passwordDB)) {
                try (PreparedStatement preparedStatement = conn.prepareStatement(sqlInsert)) {
                    preparedStatement.setInt(1, usersid);
                    preparedStatement.setInt(2, rolesid);
                    return preparedStatement.executeUpdate();
                }
            }
        } catch (Exception ex) {
            System.out.println("Connection Failed : " + ex.getMessage());
        }
        return 0;
    }

Answer 1

Маленький пример вставки в две таблицы одним запросом.

Связанные таблицы:

CREATE TABLE main (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
                   val INT);
CREATE TABLE slave (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
                    val INT,
                    main_id INT REFERENCES main (id));

Вставим связанные записи - присвоим val значения 11 и 111 в одной паре связанных записей и 22/222 в другой паре:

WITH cte AS ( INSERT INTO main (val)
              VALUES (11), (22)
              RETURNING id, val )
INSERT INTO slave (val, main_id)
SELECT CASE val WHEN 11 THEN 111 
                WHEN 22 THEN 222
                END,
       id
FROM cte;

DEMO

В CTE выполняется вставка в основную таблицу. Возвращаем оттуда присвоенное значение id (чтобы заполнить поле связи) и val (чтобы правильно связать записи). Возвращённые значения используем для правильного связывания.

БЛОГ НА HUSL

Вставка записей в БД в разные таблицы

Ответы (1 шт):