The use of Java arrays to insert, retrieve and modify PostgreSQL arrays

Arrays are a powerful programming tool commonly used by developers in Java and PL / PgSQL. Interfaces can potentially become more difficult for example, when two of them try and communicate with each other. This section describes how you can write simple code that uses 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 of the cities belonging to that country in the second column as a text array.

the

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

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

the

Inserting arrays

Anyone who is familiar with Java, have used arrays in some form or other. Before these arrays will be stored in PostgreSQL, they must be converted to the interface provided in the java package.sql package ... Array.

The JDBC driver provides functions that allow you to bring Java arrays to their corresponding PostgreSQL arrays. The transformation is specific for each database and are defined in PostgreSQL'om 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 function createArrayOf Connection interface used to convert a lowercase Java arrays into PostgreSQL arrays text before pasting.

the

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 APIs
*/
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 specified in the Connection.createArrayOf must be a PostgreSQL data type, not java.sql.Types. The JDBC driver looks at the data type at the time of launch for java.sql.The Array object.

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

the

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

the

Receiving arrays

The process of obtaining arrays is a completely reverse process of the insertion process. In the following example, the first step is getting a ResultSet with the given data, and the second to convert PostgreSQL text array in Java string array.

the

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 in stdout is the following:

the

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

the

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

the

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

the

Modifying arrays

The process of changing arrays in PostgreSQL are pretty close to the process of insertion. In the code below, a new set of cities is declared as a Java string array, which is then converted to PostgreSQL text array before you insert it into an existing line.

the

try {

String[] usa = {"New York", "Chicago", "San Francisco", "Miami", "Seattle"};
Array arrayUSA = conn.createArrayOf("text", usa);

String sql = "UPDATE cities SET city_example = ? 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();
}

the

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

Article based on information from habrahabr.ru