PostgreSQL is an extensible database system. You can add your own functions to the backend, which can then be called from queries, or even add your own data types. As these are facilities unique to PostgreSQL, we support them from Java, with a set of extension API's. Some features within the core of the standard driver actually use these extensions to implement Large Objects, etc.
To access some of the extensions, you need to use some extra
methods in the org.postgresql.Connection
class. In this
case, you would need to case the return value of Driver.getConnection()
. For example:
Connection db = Driver.getConnection(url, username, password); // ... // later on Fastpath fp = ((org.postgresql.Connection)db).getFastpathAPI();
org.postgresql.Connection
public class Connection extends Object implements Connection java.lang.Object | +----org.postgresql.Connection
These are the extra methods used to gain access to
PostgreSQL's extensions.
Methods defined by java.sql.Connection
are not listed.
public Fastpath getFastpathAPI() throws SQLException
This returns the Fastpath API for the current connection. It is primarily used by the Large Object API.
The best way to use this is as follows:
import org.postgresql.fastpath.*; ... Fastpath fp = ((org.postgresql.Connection)myconn).getFastpathAPI();
where myconn
is an open
Connection
to
PostgreSQL.
Returns: Fastpath object allowing access to functions on the PostgreSQL backend.
Throws: SQLException by Fastpath when initializing for first time
public LargeObjectManager getLargeObjectAPI() throws SQLException
This returns the Large Object API for the current connection.
The best way to use this is as follows:
import org.postgresql.largeobject.*; ... LargeObjectManager lo = ((org.postgresql.Connection)myconn).getLargeObjectAPI();
where myconn
is an open
Connection
to
PostgreSQL.
Returns: LargeObject
object that implements
the API
Throws: SQLException
by LargeObject
when initializing for
first time
public void addDataType(String type, String name)
This allows client code to add a handler for one of
PostgreSQL's more
unique data types. Normally, a data type not known by
the driver is returned by ResultSet.getObject() as a PGobject
instance. This method
allows you to write a class that extends PGobject
, and tell the driver the
type name, and class name to use. The down side to
this, is that you must call this method each time a
connection is made.
The best way to use this is as follows:
... ((org.postgresql.Connection)myconn).addDataType("mytype","my.class.name"); ...
where myconn
is an open
Connection
to
PostgreSQL. The
handling class must extend org.postgresql.util.PGobject
.
org.postgresql.Fastpath
public class Fastpath extends Object java.lang.Object | +----org.postgresql.fastpath.Fastpath
Fastpath
is an
API that exists within the
libpq C interface, and
allows a client machine to execute a function on the database
backend. Most client code will not need to use this method,
but it is provided because the Large Object API uses it.
To use, you need to import the org.postgresql.fastpath
package, using the
line:
import org.postgresql.fastpath.*;
Then, in your code, you need to get a FastPath
object:
Fastpath fp = ((org.postgresql.Connection)conn).getFastpathAPI();
This will return an instance associated with the database
connection that you can use to issue commands. The casing of
Connection
to org.postgresql.Connection
is required, as
the getFastpathAPI()
is an
extension method, not part of JDBC. Once you have a Fastpath
instance, you can use the
fastpath()
methods to execute a
backend function.
See Also: FastpathFastpathArg
, LargeObject
public Object fastpath(int fnid, boolean resulttype, FastpathArg args[]) throws SQLException
Send a function call to the PostgreSQL backend.
Parameters: fnid
- Function id resulttype
- True if the result is
an integer, false for other results args
- FastpathArguments
to pass to
fastpath
Returns: null if no data, Integer if an integer result, or byte[] otherwise
public Object fastpath(String name, boolean resulttype, FastpathArg args[]) throws SQLException
Send a function call to the PostgreSQL backend by name.
Note: The mapping for the procedure name to function id needs to exist, usually to an earlier call to
addfunction()
. This is the preferred method to call, as function id's can/may change between versions of the backend. For an example of how this works, refer to org.postgresql.LargeObject
Parameters: name
- Function name resulttype
- True if the result is
an integer, false for other results args
- FastpathArguments
to pass to
fastpath
Returns: null if no data, Integer if an integer result, or byte[] otherwise
See Also: LargeObject
public int getInteger(String name, FastpathArg args[]) throws SQLException
This convenience method assumes that the return value is an Integer
Parameters: name
- Function name args
- Function arguments
Returns: integer result
Throws: SQLException
if a database-access
error occurs or no result
public byte[] getData(String name, FastpathArg args[]) throws SQLException
This convenience method assumes that the return value is binary data.
Parameters: name
- Function name args
- Function arguments
Returns: byte[] array containing result
Throws: SQLException
if a database-access
error occurs or no result
public void addFunction(String name, int fnid)
This adds a function to our look-up table. User code
should use the addFunctions
method, which is based
upon a query, rather than hard coding the oid. The oid
for a function is not guaranteed to remain static, even
on different servers of the same version.
public void addFunctions(ResultSet rs) throws SQLException
This takes a ResultSet
containing two columns.
Column 1 contains the function name, Column 2 the oid.
It reads the entire ResultSet
, loading the values into
the function table.
Important: Remember to
close()
theResultSet
after calling this!
Implementation note about function name look-ups: PostgreSQL stores the function id's and their corresponding names in the
pg_proc
table. To speed things up locally, instead of querying each function from that table when required, aHashtable
is used. Also, only the function's required are entered into this table, keeping connection times as fast as possible.The
org.postgresql.LargeObject
class performs a query upon its start-up, and passes the returnedResultSet
to theaddFunctions()
method here. Once this has been done, the Large Object API refers to the functions by name.Do not think that manually converting them to the oid's will work. OK, they will for now, but they can change during development (there was some discussion about this for V7.0), so this is implemented to prevent any unwarranted headaches in the future.
See Also: LargeObjectManager
public int getID(String name) throws SQLException
This returns the function id associated by its name
If addFunction()
or
addFunctions()
have not
been called for this name, then an SQLException
is thrown.
org.postgresql.fastpath.FastpathArg
public class FastpathArg extends Object java.lang.Object | +----org.postgresql.fastpath.FastpathArg
Each fastpath call requires an array of arguments, the number and type dependent on the function being called. This class implements methods needed to provide this capability.
For an example on how to use this, refer to the
org.postgresql.LargeObject
package.
See Also: Fastpath
, LargeObjectManager
, LargeObject
public FastpathArg(int value)
Constructs an argument that consists of an integer value
Parameters: value - int value to set
public FastpathArg(byte bytes[])
Constructs an argument that consists of an array of bytes
Parameters: bytes - array to store
public FastpathArg(byte buf[], int off, int len)
Constructs an argument that consists of part of a byte array
Parameters:
buf
source array
off
offset within array
len
length of data to include
public FastpathArg(String s)
Constructs an argument that consists of a String.
PostgreSQL has a set of data types that can store geometric features into a table. These include single points, lines, and polygons. We support these types in Java with the org.postgresql.geometric package. It contains classes that extend the org.postgresql.util.PGobject class. Refer to that class for details on how to implement your own data type handlers.
Class org.postgresql.geometric.PGbox java.lang.Object | +----org.postgresql.util.PGobject | +----org.postgresql.geometric.PGbox public class PGbox extends PGobject implements Serializable, Cloneable This represents the box data type within PostgreSQL. Variables public PGpoint point[] These are the two corner points of the box. Constructors public PGbox(double x1, double y1, double x2, double y2) Parameters: x1 - first x coordinate y1 - first y coordinate x2 - second x coordinate y2 - second y coordinate public PGbox(PGpoint p1, PGpoint p2) Parameters: p1 - first point p2 - second point public PGbox(String s) throws SQLException Parameters: s - Box definition in PostgreSQL syntax Throws: SQLException if definition is invalid public PGbox() Required constructor Methods public void setValue(String value) throws SQLException This method sets the value of this object. It should be overridden, but still called by subclasses. Parameters: value - a string representation of the value of the object Throws: SQLException thrown if value is invalid for this type Overrides: setValue in class PGobject public boolean equals(Object obj) Parameters: obj - Object to compare with Returns: true if the two boxes are identical Overrides: equals in class PGobject public Object clone() This must be overridden to allow the object to be cloned Overrides: clone in class PGobject public String getValue() Returns: the PGbox in the syntax expected by PostgreSQL Overrides: getValue in class PGobject Class org.postgresql.geometric.PGcircle java.lang.Object | +----org.postgresql.util.PGobject | +----org.postgresql.geometric.PGcircle public class PGcircle extends PGobject implements Serializable, Cloneable This represents PostgreSQL's circle data type, consisting of a point and a radius Variables public PGpoint center This is the center point double radius This is the radius Constructors public PGcircle(double x, double y, double r) Parameters: x - coordinate of center y - coordinate of center r - radius of circle public PGcircle(PGpoint c, double r) Parameters: c - PGpoint describing the circle's center r - radius of circle public PGcircle(String s) throws SQLException Parameters: s - definition of the circle in PostgreSQL's syntax. Throws: SQLException on conversion failure public PGcircle() This constructor is used by the driver. Methods public void setValue(String s) throws SQLException Parameters: s - definition of the circle in PostgreSQL's syntax. Throws: SQLException on conversion failure Overrides: setValue in class PGobject public boolean equals(Object obj) Parameters: obj - Object to compare with Returns: true if the two circles are identical Overrides: equals in class PGobject public Object clone() This must be overridden to allow the object to be cloned Overrides: clone in class PGobject public String getValue() Returns: the PGcircle in the syntax expected by PostgreSQL Overrides: getValue in class PGobject Class org.postgresql.geometric.PGline java.lang.Object | +----org.postgresql.util.PGobject | +----org.postgresql.geometric.PGline public class PGline extends PGobject implements Serializable, Cloneable This implements a line consisting of two points. Currently line is not yet implemented in the backend, but this class ensures that when it's done were ready for it. Variables public PGpoint point[] These are the two points. Constructors public PGline(double x1, double y1, double x2, double y2) Parameters: x1 - coordinate for first point y1 - coordinate for first point x2 - coordinate for second point y2 - coordinate for second point public PGline(PGpoint p1, PGpoint p2) Parameters: p1 - first point p2 - second point public PGline(String s) throws SQLException Parameters: s - definition of the line in PostgreSQL's syntax. Throws: SQLException on conversion failure public PGline() required by the driver Methods public void setValue(String s) throws SQLException Parameters: s - Definition of the line segment in PostgreSQL's syntax Throws: SQLException on conversion failure Overrides: setValue in class PGobject public boolean equals(Object obj) Parameters: obj - Object to compare with Returns: true if the two lines are identical Overrides: equals in class PGobject public Object clone() This must be overridden to allow the object to be cloned Overrides: clone in class PGobject public String getValue() Returns: the PGline in the syntax expected by PostgreSQL Overrides: getValue in class PGobject Class org.postgresql.geometric.PGlseg java.lang.Object | +----org.postgresql.util.PGobject | +----org.postgresql.geometric.PGlseg public class PGlseg extends PGobject implements Serializable, Cloneable This implements a lseg (line segment) consisting of two points Variables public PGpoint point[] These are the two points. Constructors public PGlseg(double x1, double y1, double x2, double y2) Parameters: x1 - coordinate for first point y1 - coordinate for first point x2 - coordinate for second point y2 - coordinate for second point public PGlseg(PGpoint p1, PGpoint p2) Parameters: p1 - first point p2 - second point public PGlseg(String s) throws SQLException Parameters: s - Definition of the line segment in PostgreSQL's syntax. Throws: SQLException on conversion failure public PGlseg() required by the driver Methods public void setValue(String s) throws SQLException Parameters: s - Definition of the line segment in PostgreSQL's syntax Throws: SQLException on conversion failure Overrides: setValue in class PGobject public boolean equals(Object obj) Parameters: obj - Object to compare with Returns: true if the two line segments are identical Overrides: equals in class PGobject public Object clone() This must be overridden to allow the object to be cloned Overrides: clone in class PGobject public String getValue() Returns: the PGlseg in the syntax expected by PostgreSQL Overrides: getValue in class PGobject Class org.postgresql.geometric.PGpath java.lang.Object | +----org.postgresql.util.PGobject | +----org.postgresql.geometric.PGpath public class PGpath extends PGobject implements Serializable, Cloneable This implements a path (a multiply segmented line, which may be closed) Variables public boolean open True if the path is open, false if closed public PGpoint points[] The points defining this path Constructors public PGpath(PGpoint points[], boolean open) Parameters: points - the PGpoints that define the path open - True if the path is open, false if closed public PGpath() Required by the driver public PGpath(String s) throws SQLException Parameters: s - definition of the path in PostgreSQL's syntax. Throws: SQLException on conversion failure Methods public void setValue(String s) throws SQLException Parameters: s - Definition of the path in PostgreSQL's syntax Throws: SQLException on conversion failure Overrides: setValue in class PGobject public boolean equals(Object obj) Parameters: obj - Object to compare with Returns: true if the two pathes are identical Overrides: equals in class PGobject public Object clone() This must be overridden to allow the object to be cloned Overrides: clone in class PGobject public String getValue() This returns the path in the syntax expected by PostgreSQL Overrides: getValue in class PGobject public boolean isOpen() This returns true if the path is open public boolean isClosed() This returns true if the path is closed public void closePath() Marks the path as closed public void openPath() Marks the path as open Class org.postgresql.geometric.PGpoint java.lang.Object | +----org.postgresql.util.PGobject | +----org.postgresql.geometric.PGpoint public class PGpoint extends PGobject implements Serializable, Cloneable This implements a version of java.awt.Point, except it uses double to represent the coordinates. It maps to the point data type in PostgreSQL. Variables public double x The X coordinate of the point public double y The Y coordinate of the point Constructors public PGpoint(double x, double y) Parameters: x - coordinate y - coordinate public PGpoint(String value) throws SQLException This is called mainly from the other geometric types, when a point is embedded within their definition. Parameters: value - Definition of this point in PostgreSQL's syntax public PGpoint() Required by the driver Methods public void setValue(String s) throws SQLException Parameters: s - Definition of this point in PostgreSQL's syntax Throws: SQLException on conversion failure Overrides: setValue in class PGobject public boolean equals(Object obj) Parameters: obj - Object to compare with Returns: true if the two points are identical Overrides: equals in class PGobject public Object clone() This must be overridden to allow the object to be cloned Overrides: clone in class PGobject public String getValue() Returns: the PGpoint in the syntax expected by PostgreSQL Overrides: getValue in class PGobject public void translate(int x, int y) Translate the point with the supplied amount. Parameters: x - integer amount to add on the x axis y - integer amount to add on the y axis public void translate(double x, double y) Translate the point with the supplied amount. Parameters: x - double amount to add on the x axis y - double amount to add on the y axis public void move(int x, int y) Moves the point to the supplied coordinates. Parameters: x - integer coordinate y - integer coordinate public void move(double x, double y) Moves the point to the supplied coordinates. Parameters: x - double coordinate y - double coordinate public void setLocation(int x, int y) Moves the point to the supplied coordinates. refer to java.awt.Point for description of this Parameters: x - integer coordinate y - integer coordinate See Also: Point public void setLocation(Point p) Moves the point to the supplied java.awt.Point refer to java.awt.Point for description of this Parameters: p - Point to move to See Also: Point Class org.postgresql.geometric.PGpolygon java.lang.Object | +----org.postgresql.util.PGobject | +----org.postgresql.geometric.PGpolygon public class PGpolygon extends PGobject implements Serializable, Cloneable This implements the polygon data type within PostgreSQL. Variables public PGpoint points[] The points defining the polygon Constructors public PGpolygon(PGpoint points[]) Creates a polygon using an array of PGpoints Parameters: points - the points defining the polygon public PGpolygon(String s) throws SQLException Parameters: s - definition of the polygon in PostgreSQL's syntax. Throws: SQLException on conversion failure public PGpolygon() Required by the driver Methods public void setValue(String s) throws SQLException Parameters: s - Definition of the polygon in PostgreSQL's syntax Throws: SQLException on conversion failure Overrides: setValue in class PGobject public boolean equals(Object obj) Parameters: obj - Object to compare with Returns: true if the two polygons are identical Overrides: equals in class PGobject public Object clone() This must be overridden to allow the object to be cloned Overrides: clone in class PGobject public String getValue() Returns: the PGpolygon in the syntax expected by PostgreSQL Overrides: getValue in class PGobject
Large objects are supported in the standard JDBC specification. However, that interface is limited, and the API provided by PostgreSQL allows for random access to the objects contents, as if it was a local file.
The org.postgresql.largeobject package provides to Java the
libpq C interface's large
object API. It consists of
two classes, LargeObjectManager
,
which deals with creating, opening and deleting large objects,
and LargeObject
which deals with
an individual object.
org.postgresql.largeobject.LargeObject
public class LargeObject extends Object java.lang.Object | +----org.postgresql.largeobject.LargeObject
This class implements the large object interface to PostgreSQL.
It provides the basic methods required to run the
interface, plus a pair of methods that provide InputStream
and OutputStream
classes for this object.
Normally, client code would use the methods in
BLOB
to access large
objects.
However, sometimes lower level access to Large Objects is required, that is not supported by the JDBC specification.
Refer to org.postgresql.largeobject.LargeObjectManager on how to gain access to a Large Object, or how to create one.
See Also: LargeObjectManager
Indicates a seek from the beginning of a file
Indicates a seek from the current position
Indicates a seek from the end of a file
public int getOID()
Returns the OID of this LargeObject
public void close() throws SQLException
This method closes the object. You must not call methods in this object after this is called.
public byte[] read(int len) throws SQLException
Reads some data from the object, and return as a byte[] array
public int read(byte buf[], int off, int len) throws SQLException
Reads some data from the object into an existing array
Parameters:
destination array
offset within array
number of bytes to read
public void write(byte buf[]) throws SQLException
Writes an array to the object
public void write(byte buf[], int off, int len) throws SQLException
Writes some data from an array to the object
Parameters:
destination array
offset within array
number of bytes to write
org.postgresql.largeobject.LargeObjectManager
public class LargeObjectManager extends Object java.lang.Object | +----org.postgresql.largeobject.LargeObjectManager
This class implements the large object interface to
PostgreSQL. It provides
methods that allow client code to create, open and delete
large objects from the database. When opening an object, an
instance of org.postgresql.largeobject.LargeObject
is
returned, and its methods then allow access to the
object.
This class can only be created by org.postgresql.Connection. To get access to this class, use the following segment of code:
import org.postgresql.largeobject.*; Connection conn; LargeObjectManager lobj; // ... code that opens a connection ... lobj = ((org.postgresql.Connection)myconn).getLargeObjectAPI();
Normally, client code would use the BLOB
methods to access large objects.
However, sometimes lower level access to Large Objects is
required, that is not supported by the JDBC specification.
Refer to org.postgresql.largeobject.LargeObject on how to manipulate the contents of a Large Object.
This mode indicates we want to write to an object.
This mode indicates we want to read an object.
This mode is the default. It indicates we want read and write access to a large object.
public LargeObject open(int oid) throws SQLException
This opens an existing large object, based on its OID. This method assumes that READ and WRITE access is required (the default).
public LargeObject open(int oid, int mode) throws SQLException
This opens an existing large object, based on its OID, and allows setting the access mode.
public int create() throws SQLException
This creates a large object, returning its OID. It defaults to READWRITE for the new object's attributes.
public int create(int mode) throws SQLException
This creates a large object, returning its OID, and sets the access mode.
public void delete(int oid) throws SQLException
This deletes a large object.
public void unlink(int oid) throws SQLException
This deletes a large object. It is identical to the delete method, and is supplied as the C API uses "unlink".