PostgreSQL 数组类型的能力，你知道怎么样利用上吗？-51CTO.COM

介绍

在 Java 和 PL/pgSQL 中，数组是开发人员经常使用的一项强大的编程特性。但是，当两者尝试相互通信时，接口可能会变得棘手。本节探讨了如何使用 java.sql.Array 接口编写简单的代码，在 PostgreSQL 中插入、检索和更新数组。

为了说明该功能，让我们设置一个简单的表，该表将国家名称以 TEXT 类型存储在一列中，将国家的一些城市列表以 TEXT ARRAY 类型存储在第二列中。

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

现在，我们将使用 JDBC 接口将数据插入到此表中，检索数据并更新数据。

插入 ARRAY

任何熟悉 Java 的人都曾使用过一种或另一种形式的数组。但是，在将这些数组存储到 PostgreSQL 中之前，需要将它们映射到 java.sql 包中提供的接口：Array。

JDBC 驱动提供了将 Java 数组映射到其相应的 PostgreSQL 数组的函数。映射是特定于数据库的，并定义在 PostgreSQL JDBC 的 org.postgresql.jdbc2.TypeInfoCache 文件中。请务必注意，映射区分大小写。例如，“INTEGER” 与 “integer” 不同。

在下面的代码中，使用 Connection 接口的 createArrayOf 函数，在插入前将 Java 中的 String 数组转换为 PostgreSQL 中的 TEXT 数组。

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)

检索 ARRAY

检索数组的过程与插入数组的过程完全相反。在下面的示例中，第一步是获取包含所需数据的 ResultSet，第二步是将 PostgreSQL 中的 TEXT 数组转换为 Java 中的 String 数组。

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

对于上面的代码，输出到标准输出窗口的是：

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

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

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

更新 ARRAY

在 PostgreSQL 中更新数组的过程与插入数组的过程非常接近。在下面的代码中，一组新的美国城市被声明为 Java 中的 String 数组，然后在更新现有行之前将其转换为 PostgreSQL 中的 TEXT 数组。

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)