Introducing the PSQL JDBC Driver
An Overview of PSQL support for JDBC Development
The following topics introduce you to the PSQL JDBC interface:
For instructions and details on this PSQL feature, see
Programming with the PSQL JDBC 2 Driver and
JDBC API Reference.
PSQL JDBC Support
JDBC is a standard API that Java programmers can use to develop database and Internet applications using Java. It consists of interfaces to develop SQL based database applications in the Java programming language. The JDBC interfaces are included as part of the Java Developer Kit.
JDBC is the counterpart of ODBC in Java and is heavily influenced by ODBC and relational databases.
Detailed information on the JDBC API is available at Oracle’s website.
JDBC Requirements
The PSQL JDBC driver works in conjunction with PSQL. You can use the Server, Vx Server, or Workgroup engines.
JDBC Features
The following is a summary of features of the PSQL JDBC driver:
•100% Java certified
•JDBC 4 compliant, type 4 driver that is also compatible with applications that use JDBC 2 drivers
•Supports thread safe operation
•Supports transactions isolation levels supported by the PSQL engine, for example READ_COMMITTED, serializable
•Performs result set caching to reduce network access
•Supports binary data through the longvarbinary data type (2 GB limit)
•Supports long char data through the longvarchar and nlongvarchar data types (2 GB limit)
•Supports stored procedures with parameters
•Encrypts connection strings to provide security
•Support for code page filtering when reading from the database by specified the code page using a connection string parameter
•Support for result set cursors CONCUR_UPDATABLE, TYPE_SCROLL_INSENSITIVE, and TYPE_SCROLL_SENSITIVE
•Supports the DataSource interface to register PSQL databases in JNDI, shielding your applications from specific driver features for PSQL
•Supports the ParameterMetaData interface
PSQL JDBC Data Types
The numerical identifiers used to specify PSQL JDBC data types in some cases differ from the JDBC standard identifiers. The following table gives the full list.
Data Type | Identifier |
BFLOAT4 | 7 |
BFLOAT8 | 8 |
BIGIDENTITY | -5 |
BIGINT | -5 |
BINARY | -2 |
BIT | -7 |
CHAR | 1 |
CURRENCY | 3 |
DATE | 9 |
DATETIME | 11 |
DECIMAL | 3 |
DOUBLE | 8 |
IDENTITY | 4 |
INTEGER | 4 |
LONGVARBINARY | -4 |
LONGVARCHAR | -1 |
NCHAR | -8 |
NLONGVARCHAR | -10 |
NUMERIC | 2 |
NVARCHAR | -9 |
REAL | 7 |
SMALLIDENTITY | 5 |
SMALLINT | 5 |
TIME | 10 |
TIMESTAMP | 11 |
TINYINT | -6 |
UBIGINT | -5 |
UINTEGER | 4 |
USMALLINT | 5 |
UTINYINT | -6 |
VARCHAR | 12 |
PSQL JDBC Driver Limitations
Unsupported APIs
The JDBC driver does not support the following JDBC interfaces:
•Array
•Blob
•Clob
•NClob
•Ref
•RowId
•SQLXML
•Struct
•SQLData
•SQLInput
•SQLOutput
•URL
These are not supported due to the fact the PSQL engine does not currently support the underlying SQL 3 data types.
Driver Limitations
•You cannot use long data in “out” parameters
•The smallest actual fetch size is two rows
•You cannot have an updateable result set with a join
•You cannot have an updateable result set with a “group by”
•The JDBC driver will not store data in UnicodeBig or UnicodeLittle formats
•The only Holdability is HOLD_CURSORS_OVER_COMMIT
•Pooled statements are not supported
•Named parameters are not supported