Using Java Arrays to Insert, Retrieve, and Modify PostgreSQL Arrays

Arrays are a powerful programming tool often used by developers, both in Java and PL / PgSQL. Interfaces can potentially become more complex, for example, when two of them try and communicate with each other. This section discusses how you can write simple code that uses the java.sql.Array interface to insert, extract, and update arrays in PostgreSQL.

To demonstrate this functionality, let's create a simple table that stores the names of countries in one column as text, and a list of some cities belonging to this country in the second column as a text array.

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

Now we will use the JDBC interface to add, receive and modify data in this table.

Insert Arrays

Anyone familiar with Java has used arrays in one form or another. Before these arrays will be stored in PostgreSQL, they must be converted to the interface provided in the java.sql package ... Array.

The JDBC driver provides functions that allow you to cast Java arrays to their corresponding PostgreSQL arrays. The conversion is specific to each specific database and is defined in the PostgreSQL org.postgresql.jdbc2.TypeInfoCache file. In addition, it is important to note that the conversion is case sensitive. For example, “INTEGER” is not the same as “integer”.

In the following code, the createArrayOf Connection function of the interface is used to convert lowercase Java arrays to PostgreSQL text arrays before insertion.

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();
}

Please note that the data type defined in Connection.createArrayOf must be a PostgreSQL data type, not java.sql.Types. The JDBC driver looks at the data type at launch time to create a java.sql.Array object.

This code, when executed, returns the following data in the city_example table:

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

Getting Arrays

The process of getting arrays is completely the opposite of the process of inserting them. In the following example, the first step is to get a ResultSet with the necessary data, and the second is the conversion of the PostgreSQL text array to a Java string array.

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

Для этого кода, вывод в 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)