PostgreSQL Data Type | SQL/JDBC Data Type | Java Type |
bool | BIT | boolean |
bit | BIT | boolean |
int8 | BIGINT | long |
bigserial | BIGINT | long |
oid | BIGINT | long |
bytea/blob | BINARY | byte[] |
char | CHAR | String |
bpchar | CHAR | String |
number/numeric | NUMERIC | java.math.BigDecimal |
int4 | INTEGER | int |
serial | INTEGER | int |
int2 | SMALLINT | short |
smallserial | SMALLINT | short |
float4 | REAL | float |
float8 | DOUBLE | double |
money | DOUBLE | double |
name | VARCHAR | String |
text/clob | VARCHAR | String |
varchar/varchar2 | VARCHAR | String |
date | DATE | java.sql.Date |
time | TIME | java.sql.Time |
timetz | TIME | java.sql.Time |
timestamp/datetime | TIMESTAMP | java.sql.Timestamp |
timestamptz | TIMESTAMP | java.sql.Timestamp |
cardinal_number | DISTINCT | Mapping of underlying type |
character_data | DISTINCT | Mapping of underlying type |
sql_identifier | DISTINCT | Mapping of underlying type |
time_stamp | DISTINCT | Mapping of underlying type |
yes_or_no | DISTINCT | Mapping of underlying type |
xml | SQLXML | java.sql.SQLXML |
refcursor | REF_CURSOR | Undefined |
_abc | ARRAY | java.sql.array |
1 REF_CURSOR doesn’t appear in the jdbc appendices, but is mentioned in section “13.3.3.4 REF Cursor Support” of the specification, and may map to Types.REF_CURSOR.
2 _abc stands for one of many ARRAY data types available in PostgreSQL (_record to _yes_or_no).
pg数组对应对应java类型为:java.sql.Array。如下:
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(); }
https://www.iteye.com/problems/90761测试的几种方式都没通过。
参考:https://www.2ndquadrant.com/en/blog/using-java-arrays-to-insert-retrieve-update-postgresql-arrays