Использование Java массивов для вставки, получения и изменения PostgreSQL массивов

Массивы являются мощным средством программирования, часто используемом разработчиками, как в Java так и PL / PgSQL. Интерфейсы потенциально могут стать сложнее, например, когда два из них пытаются и общаются друг другом. В этом разделе рассматривается, как можно написать простой код, который использует java.sql.Array интерфейс для вставки, извлечения, и обновления массивов в PostgreSQL.

Чтобы продемонстрировать этот функционал, давайте создадим простую таблицу, которая хранит названия стран в одной колонке в виде текста, и список каких-то городов, принадлежащих этой стране во второй колонке в виде текстового массива.

CREATE TABLE city_example (
country TEXT, 
cities TEXT[]
);

Теперь мы будем использовать JDBC интерфейс для добавления, получения и изменения данных в этой таблице.

Вставка массивов

Любой, кто знаком с Java, использовал массивы в том, или ином виде. Перед тем, как эти массивы будут храниться в PostgreSQL, они должны быть преобразованы в интерфейс, предоставленный в пакете java.sql package … Array.

JDBC драйвер предоставляет функции, которые позволяют приводить Java массивы к соответствующим им PostgreSQL массивам. Преобразование специфично для каждой конкретной базы данных и определено в PostgreSQL'ом org.postgresql.jdbc2.TypeInfoCache файле. Кроме того, важно отметить, что преобразование чувствительно к регистру. К примеру, “INTEGER” — это не то же самое, что “integer”.

В нижеследующем коде, функция createArrayOf Connection интерфейса использована для конвертации строчных Java массивов в текстовые PostgreSQL массивы перед вставкой.

try {
 
 String[] usa = {"New York", "Chicago", "San Francisco"};
 String[] canada = {"Montreal", "Toronto", "Vancouver"};
 String[] uk = {"London", "Birmingham", "Oxford"};

 /*
 Convert String[] to java.sql.Array using JDBC API
 */
 Array arrayUSA = conn.createArrayOf("text", usa);
 Array arrayCanada = conn.createArrayOf("text", canada);
 Array arrayUK = conn.createArrayOf("text", uk);
 String sql = "INSERT INTO city_example VALUES (?, ?)";
 PreparedStatement pstmt = conn.prepareStatement(sql);
 
 pstmt.setString(1, "USA");
 pstmt.setArray(2, arrayUSA);
 pstmt.executeUpdate();
 
 pstmt.setString(1, "Canada");
 pstmt.setArray(2, arrayCanada);
 pstmt.executeUpdate();
 pstmt.setString(1, "UK");
 pstmt.setArray(2, arrayUK);
 pstmt.executeUpdate();
 
 conn.commit();
} catch (Exception e) {
 
 System.out.println(e.getMessage());
 e.printStackTrace();
}

Пожалуйста обратите внимание, что тип данных, определенный в Connection.createArrayOf, должен быть типом данных PostgreSQL, не java.sql.Types. JDBC драйвер смотрит на тип данных в момент запуска для создания java.sql.Array объекта.

Этот код, при выполнении, возвращает следующие данные в таблицу city_example:

select * from city_example ; 
 country | cities 
---------+--------------------------------------
 USA     | {"New York",Chicago,"San Francisco"}
 Canada  | {Montreal,Toronto,Vancouver}
 UK      | {London,Birmingham,Oxford}
(3 rows)

Получение массивов

Процесс получения массивов — это полностью обратный процессу их вставки процесс. В нижеследующем примере, первый шаг — это получение ResultSet с необходимыми данными, а второй — преобразование PostgreSQL текстового массива в строчный массив Java.

try { 
 
 String sql = "SELECT * FROM city_example";
 PreparedStatement ps = conn.prepareStatement(sql);
 ResultSet rs = ps.executeQuery();
 
 while(rs.next()) {
 
 System.out.println("Country: " + rs.getString(1));
 System.out.println("---------------");
 
 Array cities = rs.getArray(2);
 String[] str_cities = (String[])cities.getArray();
 
 for (int i=0; i<str_cities.length; i++) {
 System.out.println(str_cities[i]);
 }
System.out.println("");
}
 
} catch (Exception e) {
 
 System.out.println(e.getMessage());
 e.printStackTrace();
}

Для этого кода, вывод в stdout следующий:

Country: USA
---------------
New York
Chicago
San Francisco

Country: Canada
---------------
Montreal
Toronto
Vancouver

Country: UK
---------------
London
Birmingham
Oxford

Изменение массивов

Процесс изменения массивов в PostgreSQL довольно близок к процессу их вставки. В нижеследующем коде, новый набор городов США объявлен как Java строчный массив, который потом преобразуется в PostgreSQL текстовый массив перед вставкой в существующую строку.

try {
 
 String[] usa = {"New York", "Chicago", "San Francisco", "Miami", "Seattle"};
 Array arrayUSA = conn.createArrayOf("text", usa);
 
 String sql = "UPDATE city_example SET cities = ? WHERE country = 'USA'";
 PreparedStatement pstmt = conn.prepareStatement(sql);
 
 pstmt.setArray(1, arrayUSA);
 pstmt.executeUpdate();
 
 conn.commit();
 
} catch (Exception e) {
 
 System.out.println(e.getMessage());
 e.printStackTrace();
}

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

select * from city_example ;
 country | cities 
---------+----------------------------------------------------
 Canada  | {Montreal,Toronto,Vancouver}
 UK      | {London,Birmingham,Oxford}
 USA     | {"New York",Chicago,"San Francisco",Miami,Seattle}
(3 rows)