Using Java arrays to insert, retrieve, and modify PostgreSQL arrays

Arrays are a powerful programming tool often used by developers in both Java and PL / PgSQL. Interfaces can potentially become more difficult, for example, when two of them try and communicate with each other. This section discusses how to 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, get, and modify data in this table.

Insert arrays

Anyone familiar with Java 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 Java arrays to be mapped to their corresponding PostgreSQL arrays. The conversion is specific for 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 interface's createArrayOf Connection function 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 startup to create a java.sql.Array object.

This code, when executed, returns the following data to 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 to convert a 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<str_cities.length; i++) { System.out.println(str_cities[i]); } System.out.println(""); } } catch (Exception e) { System.out.println(e.getMessage()); e.printStackTrace(); } 

For this code, the output to stdout is as follows:

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

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

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

Modify arrays

The process of changing arrays in PostgreSQL is pretty close to the process of inserting them. In the following code, a new set of US cities is declared as a Java string array, which is then converted to a PostgreSQL text array before inserting into an existing string.

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

After executing this code, the base looks like this:

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