Home PostgreSQL Using Java arrays to insert, fetch and modify PostgreSQL arrays

Using Java arrays to insert, fetch and modify PostgreSQL arrays

by admin

Arrays are a powerful programming tool, used frequently 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 looks at how you can write simple code that uses the java.sql.Array interface to insert, retrieve, and update arrays in PostgreSQL.
To demonstrate this functionality, let’s create a simple table that stores country names in one column as text, and a list of some cities belonging to that 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.

Inserting arrays

Anyone familiar with Java has used arrays in one form or another. Before these arrays can 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 convert Java arrays to their corresponding PostgreSQL arrays. The conversion is database-specific and 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 code below, the createArrayOf Connection interface function is used to convert string 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 to create the 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 reverse of the process of inserting them. In the example below, the first step is getting a ResultSet with the necessary data, and the second step is converting a PostgreSQL text array into 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 YorkChicagoSan Francisco

Country: Canada---------------MontrealTorontoVancouver

Country: UK---------------LondonBirminghamOxford

Changing arrays

The process of changing arrays in PostgreSQL is quite similar to the process of inserting them. In the code below, a new set of U.S. cities is declared as a Java string array, which is then converted to a PostgreSQL text array before being inserted 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)

You may also like