diff -cPr ../cvs-pgjdbc/build.xml ./build.xml
*** ../cvs-pgjdbc/build.xml 2004-01-19 04:40:30.000000000 +0100
--- ./build.xml 2004-01-23 20:37:53.000000000 +0100
***************
*** 335,338 ****
--- 335,347 ----
+
+
+
+
+
+
+
+
+
diff -cPr ../cvs-pgjdbc/doc/pgjdbc.xml ./doc/pgjdbc.xml
*** ../cvs-pgjdbc/doc/pgjdbc.xml 1970-01-01 01:00:00.000000000 +0100
--- ./doc/pgjdbc.xml 2004-01-23 19:31:14.000000000 +0100
***************
*** 0 ****
--- 1,3212 ----
+
+
+
+
+ ]>
+
+
+ The PostgreSQL <acronym>JDBC</acronym> Interface
+
+
+ Introduction
+
+
+ JDBC is a core API of Java 1.1 and later.
+ It provides a standard set of
+ interfaces to SQL-compliant databases.
+
+
+
+ PostgreSQL provides a type
+ 4 JDBC driver. Type 4 indicates
+ that the driver is written in Pure Java, and communicates in the
+ database system's own network protocol. Because of this, the driver
+ is platform independent; once compiled, the driver can be used on
+ any system.
+
+
+
+ This manual is not intended as a complete guide to
+ JDBC programming, but should help to get you
+ started. For more information refer to the standard
+ JDBC API documentation.
+ Also, take a look at the examples included with the source.
+
+
+
+
+ Setting up the <acronym>JDBC</acronym> Driver
+
+
+ This section describes the steps you need to take before you can
+ write or run programs that use the JDBC interface.
+
+
+
+ Getting the Driver
+
+
+ Precompiled versions of the driver can be downloaded from
+ the PostgreSQL
+ JDBC web site.
+
+
+
+ These instructions are currently inaccurate.
+
+
+
+ Alternatively you can build the driver from source, but you should
+ only need to do this if you are making changes to the source code.
+ To build the JDBC driver, you need Ant
+ 1.5 or higher and a JDK.
+ Ant is a special tool for building
+ Java-based packages. It can be downloaded from the Ant
+ web site.
+
+
+
+ If you have several Java compilers installed, it depends on the
+ Ant configuration which one gets used. Precompiled
+ Ant distributions are typically set up
+ to read a file .antrc in the current user's
+ home directory for configuration. For example, to use a different
+ JDK than the default, this may work:
+
+ JAVA_HOME=/usr/local/sun-jdk1.3
+ JAVACMD=$JAVA_HOME/bin/java
+
+
+
+
+
+ Do not try to build the driver by calling ant
+ or even javac directly. This will not work.
+ Run gmake normally as described below.
+
+
+
+
+ After installation, the driver should be found in
+ PREFIX/share/java/postgresql.jar.
+ The resulting driver will be built for the version of Java you are
+ running. If you build with a 1.1 JDK you will
+ build a version that supports the JDBC 1
+ specification, if you build with a 1.2 or 1.3
+ JDK you will build a version that supports the
+ JDBC 2 specification, and finally if you build
+ with a 1.4 JDK you will build a version that
+ supports the JDBC 3 specification.
+
+
+
+
+ Setting up the Class Path
+
+
+ class path
+
+
+
+ CLASSPATH
+
+
+
+ To use the driver, the JAR archive (named
+ postgresql.jar if you built from source, otherwise
+ it will likely be named pg&majorversion;jdbc1.jar,
+ pg&majorversion;jdbc2.jar, or
+ pg&majorversion;jdbc3.jar for the JDBC 1,
+ JDBC 2, and JDBC 3 versions respectively)
+ needs to be included in the class path, either by putting it in the
+ CLASSPATH environment variable, or by using flags on the
+ java command line.
+
+
+
+ For instance, assume we have an application that uses the
+ JDBC driver to access a database, and that
+ application is installed as
+ /usr/local/lib/myapp.jar. The
+ PostgreSQL JDBC driver installed as
+ /usr/local/pgsql/share/java/postgresql.jar. To run
+ the application, we would use:
+
+ export CLASSPATH=/usr/local/lib/myapp.jar:/usr/local/pgsql/share/java/postgresql.jar:.
+ java MyApp
+
+
+
+
+ Loading the driver from within the application is covered in
+ .
+
+
+
+
+ Preparing the Database Server for <acronym>JDBC</acronym>
+
+
+ Because Java only uses TCP/IP connections, the
+ PostgreSQL server must be configured to
+ accept TCP/IP connections. This can be done by setting
+ tcpip_socket = true in the
+ postgresql.conf file or by supplying the
+ option flag when starting
+ postmaster.
+
+
+
+ Also, the client authentication setup in the
+ pg_hba.conf file may need to be configured.
+ Refer to the PostgreSQL documentation for details. The
+ JDBC driver supports the trust,
+ ident, password, md5, and
+ crypt authentication methods.
+
+
+
+
+
+ Initializing the Driver
+
+
+ This section describes how to load and initialize the JDBC
+ driver in your programs.
+
+
+
+ Importing <acronym>JDBC</acronym>
+
+
+ Any source that uses JDBC needs to import the
+ java.sql package, using:
+
+
+ import java.sql.*;
+
+
+
+
+
+ Do not import the org.postgresql package. If
+ you do, your source will not compile, as
+ javac will get confused.
+
+
+
+
+
+ Loading the Driver
+
+
+ Before you can connect to a database, you need to load the
+ driver. There are two methods available, and it depends on your
+ code which is the best one to use.
+
+
+
+ In the first method, your code implicitly loads the driver using the
+ Class.forName() method.
+ For PostgreSQL, you would use:
+
+
+ Class.forName("org.postgresql.Driver");
+
+
+ This will load the driver, and while loading, the driver will automatically
+ register itself with JDBC.
+
+
+
+
+ The forName() method can throw a
+ ClassNotFoundException if the driver is
+ not available.
+
+
+
+
+ This is the most common method to use, but restricts your code to
+ use just PostgreSQL. If your code may
+ access another database system in the future, and you do not use
+ any PostgreSQL-specific extensions, then
+ the second method is advisable.
+
+
+
+ The second method passes the driver as a parameter to the
+ JVM as it starts, using the
+ argument. Example:
+
+ java -Djdbc.drivers=org.postgresql.Driver example.ImageViewer
+
+ In this example, the JVM will attempt to load
+ the driver as part of its initialization. Once done, the
+ ImageViewer is started.
+
+
+
+ Now, this method is the better one to use because it allows your
+ code to be used with other database packages without recompiling
+ the code. The only thing that would also change is the connection
+ URL, which is covered next.
+
+
+
+ One last thing: When your code then tries to open a
+ Connection, and you get a No
+ driver availableSQLException
+ being thrown, this is probably caused by the driver not being in
+ the class path, or the value in the parameter not being correct.
+
+
+
+
+ Connecting to the Database
+
+
+ With JDBC, a database is represented by a
+ URL (Uniform Resource Locator). With
+ PostgreSQL, this takes one of the
+ following forms:
+
+
+
+
+ jdbc:postgresql:database
+
+
+
+
+
+ jdbc:postgresql://host/database
+
+
+
+
+
+ jdbc:postgresql://host:port/database
+
+
+
+
+ The parameters have the following meanings:
+
+
+
+
+ host
+
+
+
+ The host name of the server. Defaults to localhost. To specify an IPv6 address your must enclose the host parameter with square brackets, for example:
+
+ jdbc:postgresql://[::1]:5740/accounting
+
+
+
+
+
+
+
+ port
+
+
+
+ The port number the server is listening on. Defaults to the
+ PostgreSQL standard port number (5432).
+
+
+
+
+
+
+ database
+
+
+
+ The database name.
+
+
+
+
+
+
+
+ To connect, you need to get a Connection instance from
+ JDBC. To do this,
+ you use the DriverManager.getConnection() method:
+
+
+ Connection db = DriverManager.getConnection(url, username, password);
+
+
+
+
+
+ Closing the Connection
+
+
+ To close the database connection, simply call the
+ close() method to the Connection:
+
+ db.close();
+
+
+
+
+
+
+
+ Issuing a Query and Processing the Result
+
+
+ Statement
+
+
+
+ PreparedStatement
+
+
+
+ ResultSet
+
+
+
+ Any time you want to issue SQL statements to
+ the database, you require a Statement or
+ PreparedStatement instance. Once you have
+ a Statement or
+ PreparedStatement, you can use issue a
+ query. This will return a ResultSet
+ instance, which contains the entire result (see
+ here for how to alter this behaviour).
+ illustrates this process.
+
+
+
+ Processing a Simple Query in <acronym>JDBC</acronym>
+
+
+ This example will issue a simple query and print out the first
+ column of each row using a Statement.
+
+ Statement st = db.createStatement();
+ ResultSet rs = st.executeQuery("SELECT * FROM mytable WHERE columnfoo = 500");
+ while (rs.next()) {
+ System.out.print("Column 1 returned ");
+ System.out.println(rs.getString(1));
+ }
+ rs.close();
+ st.close();
+
+
+
+
+ This example issues the same query as before but uses
+ a PreparedStatement
+ and a bind value in the query.
+
+ int foovalue = 500;
+ PreparedStatement st = db.prepareStatement("SELECT * FROM mytable WHERE columnfoo = ?");
+ st.setInt(1, foovalue);
+ ResultSet rs = st.executeQuery();
+ while (rs.next()) {
+ System.out.print("Column 1 returned ");
+ System.out.println(rs.getString(1));
+ }
+ rs.close();
+ st.close();
+
+
+
+
+
+ Getting results based on a cursor
+
+ By default the driver collects all the results for the
+ query at once. This can be inconvenient for large data sets so
+ the JDBC driver provides a means of basing
+ a ResultSet on a database cursor and
+ only fetching a small number of rows.
+
+ A small number of rows are cached on the
+ client side of the connection and when exhausted the next
+ block of rows is retrieved by repositioning the cursor.
+
+
+
+ Setting fetch size to turn cursors on and off.
+
+ Changing code to cursor mode is as simple as setting the
+ fetch size of the Statement to the
+ appropriate size. Setting the fetch size back to 0 will cause
+ all rows to be cached (the default behaviour).
+
+
+ Statement st = db.createStatement();
+ // Turn use of the cursor on.
+ st.setFetchSize(50);
+ ResultSet rs = st.executeQuery("SELECT * FROM mytable");
+ while (rs.next()) {
+ System.out.print("a row was returned.");
+ }
+ rs.close();
+ // Turn the cursor off.
+ st.setFetchSize(0);
+ ResultSet rs = st.executeQuery("SELECT * FROM mytable");
+ while (rs.next()) {
+ System.out.print("many rows were returned.");
+ }
+ rs.close();
+ // Close the statement.
+ st.close();
+
+
+
+
+
+
+ Using the <classname>Statement</classname> or <classname>PreparedStatement</classname> Interface
+
+
+ The following must be considered when using the
+ Statement or
+ PreparedStatement interface:
+
+
+
+
+ You can use a single Statement instance
+ as many times as you want. You could create one as soon as you
+ open the connection and use it for the connection's
+ lifetime. But you have to remember that only one
+ ResultSet can exist per
+ Statement or
+ PreparedStatement at a given time.
+
+
+
+
+
+ If you need to perform a query while processing a
+ ResultSet, you can simply create and
+ use another Statement.
+
+
+
+
+
+ If you are using threads, and several are using the database,
+ you must use a separate Statement for
+ each thread. Refer to if you are
+ thinking of using threads, as it covers some important points.
+
+
+
+
+
+ When you are done using the Statement
+ or PreparedStatement
+ you should close it.
+
+
+
+
+
+
+
+ Using the <classname>ResultSet</classname> Interface
+
+
+ The following must be considered when using the
+ ResultSet interface:
+
+
+
+
+ Before reading any values, you must call
+ next(). This returns true if there is a
+ result, but more importantly, it prepares the row for
+ processing.
+
+
+
+
+
+ Under the JDBC specification, you should
+ access a field only once. It is safest to stick to this rule,
+ although at the current time, the
+ PostgreSQL driver will allow you to
+ access a field as many times as you want.
+
+
+
+
+
+ You must close a ResultSet by calling
+ close() once you have finished using it.
+
+
+
+
+
+ Once you make another query with the
+ Statement used to create a
+ ResultSet, the currently open
+ ResultSet instance is closed
+ automatically.
+
+
+
+
+
+
+
+
+
+ Performing Updates
+
+
+ To change data (perform an INSERT,
+ UPDATE, or DELETE) you use
+ the executeUpdate() method. This method is
+ similar to the method executeQuery() used to
+ issue a SELECT statement, but it doesn't return
+ a ResultSet; instead it returns the number
+ of rows affected by the INSERT,
+ UPDATE, or DELETE statement.
+ illustrates the usage.
+
+
+
+ Deleting Rows in <acronym>JDBC</acronym>
+
+ This example will issue a simple DELETE
+ statement and print out the number of rows deleted.
+
+ int foovalue = 500;
+ PreparedStatement st = db.prepareStatement("DELETE FROM mytable WHERE columnfoo = ?");
+ st.setInt(1, foovalue);
+ int rowsDeleted = st.executeUpdate();
+ System.out.println(rowsDeleted + " rows deleted");
+ st.close();
+
+
+
+
+
+
+
+ Calling Stored Functions
+
+ PostgreSQL's JDBC driver fully
+ supports calling PostgreSQL stored
+ functions.
+
+
+ Calling a built in stored function
+
+ This example shows how to call
+ a PostgreSQL built in
+ function, upper, which simply converts the
+ supplied string argument to uppercase.
+
+
+ // Turn transactions off.
+ con.setAutoCommit(false);
+ // Procedure call.
+ CallableStatement upperProc = con.prepareCall("{ ? = call upper( ? ) }");
+ upperProc.registerOutParameter(1, Types.VARCHAR);
+ upperProc.setString(2, "lowercase to uppercase");
+ upperProc.execute();
+ String upperCased = upperProc.getString(1);
+ upperProc.close();
+
+
+
+
+
+ Using the <classname>CallableStatement</classname> Interface
+
+
+ All the considerations that apply
+ for Statement
+ and PreparedStatement apply
+ for CallableStatement but in addition
+ you must also consider one extra restriction:
+
+
+
+
+ You can only call a stored function from within a
+ transaction.
+
+
+
+
+
+
+ Obtaining <classname>ResultSet</classname> from a stored function
+
+ PostgreSQL's stored function
+ can return results by means of a refcursor
+ value. A refcursor.
+
+ As an extension to JDBC,
+ the PostgreSQL JDBC driver can
+ return refcursor values
+ as ResultSet values.
+
+
+ Getting <type>refcursor</type> values from a
+ function
+
+ When calling a function that returns
+ a refcursor you must cast the return type
+ of getObject to
+ a ResultSet
+
+
+ // Turn transactions off.
+ con.setAutoCommit(false);
+ // Procedure call.
+ CallableStatement proc = con.prepareCall("{ ? = call doquery ( ? ) }");
+ proc.registerOutParameter(1, Types.Other);
+ proc.setInt(2, -1);
+ proc.execute();
+ ResultSet results = (ResultSet) proc.getObject(1);
+ while (results.next()) {
+ // do something with the results...
+ }
+ results.close();
+ proc.close();
+
+
+
+ It is also possible to treat the refcursor
+ return value as a distinct type in itself. The JDBC driver
+ provides
+ the org.postgresql.PGRefCursorResultSet
+ class for this purpose.
+
+
+ Treating <type>refcursor</type> as a distinct
+ type
+
+
+ con.setAutoCommit(false);
+ CallableStatement proc = con.prepareCall("{ ? = call doquery ( ? ) }");
+ proc.registerOutParameter(1, Types.Other);
+ proc.setInt(2, 0);
+ org.postgresql.PGRefCursorResultSet refcurs
+ = (PGRefCursorResultSet) con.getObject(1);
+ String cursorName = refcurs.getRefCursor();
+ proc.close();
+
+
+
+
+
+
+
+
+ Creating and Modifying Database Objects
+
+
+ To create, modify or drop a database object like a table or view
+ you use the execute() method. This method is
+ similar to the method executeQuery(), but it
+ doesn't return a result.
+ illustrates the usage.
+
+
+
+ Dropping a Table in JDBC
+
+ This example will drop a table.
+
+ Statement st = db.createStatement();
+ st.execute("DROP TABLE mytable");
+ st.close();
+
+
+
+
+
+
+ Storing Binary Data
+
+
+ bytea
+ in JDBC
+
+
+
+ large object
+ in JDBC
+
+
+
+ PostgreSQL provides two distinct ways to
+ store binary data. Binary data can be stored in a table using
+ the data type bytea or by using the Large Object
+ feature which stores the binary data in a separate table in a special
+ format and refers to that table by storing a value of type
+ oid in your table.
+
+
+
+ In order to determine which method is appropriate you
+ need to understand the limitations of each method. The
+ bytea data type is not well suited for storing very
+ large amounts of binary data. While a column of type
+ bytea can hold up to 1 GB of binary data, it would
+ require a huge amount of memory to
+ process such a large value. The Large Object method for
+ storing binary data is better suited to storing very large values,
+ but it has its own limitations. Specifically deleting a row
+ that contains a Large Object reference does not delete the Large Object.
+ Deleting the Large Object is a separate operation that needs to
+ be performed. Large Objects also have some security
+ issues since anyone connected to the database can view
+ and/or modify any Large Object, even if they don't have
+ permissions to view/update the row containing the Large Object reference.
+
+
+
+ Version 7.2 was the first release of the JDBC driver
+ that supports the bytea data type. The introduction of
+ this functionality in 7.2 has introduced a change in behavior
+ as compared to previous releases. Since 7.2, the methods
+ getBytes(), setBytes(),
+ getBinaryStream(), and
+ setBinaryStream() operate on
+ the bytea data type. In 7.1 and earlier, these methods operated
+ on the oid data type associated with Large Objects.
+ It is possible to revert the driver back to the old 7.1 behavior
+ by setting the property compatible on
+ the Connection object to the value
+ 7.1.
+
+
+
+ To use the bytea data type you should simply use
+ the getBytes(), setBytes(),
+ getBinaryStream(), or
+ setBinaryStream() methods.
+
+
+
+ To use the Large Object functionality you can use either the
+ LargeObject class provided by the
+ PostgreSQL JDBC driver, or by
+ using the getBLOB() and
+ setBLOB() methods.
+
+
+
+
+ You must access Large Objects within an SQL
+ transaction block. You can start a transaction block by calling
+ setAutoCommit(false).
+
+
+
+
+
+ In a future release of the
+ JDBC driver, the getBLOB()
+ and setBLOB() methods may no longer
+ interact with Large Objects and will instead work on the data type
+ bytea. So it is recommended that you
+ use the LargeObject API
+ if you intend to use Large Objects.
+
+
+
+
+ contains some examples on
+ how to process binary data using the PostgreSQL
+ JDBC driver.
+
+
+
+ Processing Binary Data in <acronym>JDBC</acronym>
+
+
+ For example, suppose you have a table containing the file names of
+ images and you also want to store the image in a bytea
+ column:
+
+ CREATE TABLE images (imgname text, img bytea);
+
+
+
+
+ To insert an image, you would use:
+
+ File file = new File("myimage.gif");
+ FileInputStream fis = new FileInputStream(file);
+ PreparedStatement ps = conn.prepareStatement("INSERT INTO images VALUES (?, ?)");
+ ps.setString(1, file.getName());
+ ps.setBinaryStream(2, fis, file.length());
+ ps.executeUpdate();
+ ps.close();
+ fis.close();
+
+
+ Here, setBinaryStream() transfers a set number
+ of bytes from a stream into the column of type bytea.
+ This also could have been done using the setBytes()
+ method if the contents of the image was already in a
+ byte[].
+
+
+
+ Retrieving an image is even easier. (We use
+ PreparedStatement here, but the
+ Statement class can equally be used.)
+
+
+ PreparedStatement ps = con.prepareStatement("SELECT img FROM images WHERE imgname = ?");
+ ps.setString(1, "myimage.gif");
+ ResultSet rs = ps.executeQuery();
+ if (rs != null) {
+ while (rs.next()) {
+ byte[] imgBytes = rs.getBytes(1);
+ // use the data in some way here
+ }
+ rs.close();
+ }
+ ps.close();
+
+
+
+
+ Here the binary data was retrieved as an
+ byte[]. You could have used a
+ InputStream object instead.
+
+
+
+ Alternatively you could be storing a very large file and want to use
+ the LargeObject API to
+ store the file:
+
+ CREATE TABLE imageslo (imgname text, imgoid oid);
+
+
+
+
+ To insert an image, you would use:
+
+ // All LargeObject API calls must be within a transaction block
+ conn.setAutoCommit(false);
+
+ // Get the Large Object Manager to perform operations with
+ LargeObjectManager lobj = ((org.postgresql.PGConnection)conn).getLargeObjectAPI();
+
+ // Create a new large object
+ int oid = lobj.create(LargeObjectManager.READ | LargeObjectManager.WRITE);
+
+ // Open the large object for writing
+ LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
+
+ // Now open the file
+ File file = new File("myimage.gif");
+ FileInputStream fis = new FileInputStream(file);
+
+ // Copy the data from the file to the large object
+ byte buf[] = new byte[2048];
+ int s, tl = 0;
+ while ((s = fis.read(buf, 0, 2048)) > 0) {
+ obj.write(buf, 0, s);
+ tl += s;
+ }
+
+ // Close the large object
+ obj.close();
+
+ // Now insert the row into imageslo
+ PreparedStatement ps = conn.prepareStatement("INSERT INTO imageslo VALUES (?, ?)");
+ ps.setString(1, file.getName());
+ ps.setInt(2, oid);
+ ps.executeUpdate();
+ ps.close();
+ fis.close();
+
+
+
+
+ Retrieving the image from the Large Object:
+
+
+ // All LargeObject API calls must be within a transaction block
+ conn.setAutoCommit(false);
+
+ // Get the Large Object Manager to perform operations with
+ LargeObjectManager lobj = ((org.postgresql.PGConnection)conn).getLargeObjectAPI();
+
+ PreparedStatement ps = con.prepareStatement("SELECT imgoid FROM imageslo WHERE imgname = ?");
+ ps.setString(1, "myimage.gif");
+ ResultSet rs = ps.executeQuery();
+ if (rs != null) {
+ while (rs.next()) {
+ // Open the large object for reading
+ int oid = rs.getInt(1);
+ LargeObject obj = lobj.open(oid, LargeObjectManager.READ);
+
+ // Read the data
+ byte buf[] = new byte[obj.size()];
+ obj.read(buf, 0, obj.size());
+ // Do something with the data read here
+
+ // Close the object
+ obj.close();
+ }
+ rs.close();
+ }
+ ps.close();
+
+
+
+
+
+
+
+
+ <productname>PostgreSQL</productname> Extensions to the
+ <acronym>JDBC</acronym> <acronym>API</acronym>
+
+
+ PostgreSQL is an extensible database
+ system. You can add your own functions to the server, 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.
+
+
+
+ Accessing the Extensions
+
+
+ To access some of the extensions, you need to use some extra
+ methods in the org.postgresql.PGConnection
+ 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.PGConnection)db).getFastpathAPI();
+
+
+
+
+ Class <classname>org.postgresql.PGConnection</classname>
+
+
+ public class PGConnection
+
+
+
+ These are the extra methods used to gain access to
+ PostgreSQL's extensions.
+
+
+
+ Methods
+
+
+
+
+ public Fastpath getFastpathAPI() throws SQLException
+
+
+ This returns the fast-path 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.PGConnection)myconn).getFastpathAPI();
+
+ where myconn is an open Connection to PostgreSQL.
+
+
+
+ Returns:
+
+ Fastpath object allowing access to functions on the
+ PostgreSQL server.
+
+
+
+
+ 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.PGConnection)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.PGConnection)myconn).addDataType("mytype","my.class.name");
+ ...
+
+ where myconn is an open Connection to
+ PostgreSQL. The handling class must
+ extend org.postgresql.util.PGobject.
+
+
+
+
+
+
+
+
+ Class <classname>org.postgresql.Fastpath</classname>
+
+
+ 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 server. 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.PGConnection)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.PGConnection 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 server
+ function.
+
+
+
+ See Also:
+
+ FastpathFastpathArg, LargeObject
+
+
+
+
+ Methods
+
+
+
+
+ public Object fastpath(int fnid,
+ boolean resulttype,
+ FastpathArg args[]) throws SQLException
+
+
+ Send a function call to the PostgreSQL server.
+
+
+
+ Parameters:
+
+ fnid - Function id
+ resulttype - True if the result is an integer, false
+ for
+ other results
+ args - FastpathArguments to pass to fast-path call
+
+
+
+
+ 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 server by name.
+
+
+
+
+ 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 server. 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 fast-path call
+
+
+
+
+ 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.
+
+
+
+
+ Remember to close() the
+ ResultSet 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, a Hashtable 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 returned
+ ResultSet to the
+ addFunctions() 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 OIDs 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.
+
+
+
+
+
+
+
+
+ Class <classname>org.postgresql.fastpath.FastpathArg</classname>
+
+
+ public class FastpathArg extends Object
+
+ java.lang.Object
+ |
+ +----org.postgresql.fastpath.FastpathArg
+
+
+
+ Each fast-path 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
+
+
+
+
+ Constructors
+
+
+
+
+ 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.
+
+
+
+
+
+
+
+
+
+ Geometric Data Types
+
+
+ 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 server, 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
+
+
+ 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.
+
+
+
+ Class <classname>org.postgresql.largeobject.LargeObject</classname>
+
+
+ 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
+
+
+
+ Variables
+
+
+
+ public static final int SEEK_SET
+
+ Indicates a seek from the beginning of a file
+
+
+
+
+ public static final int SEEK_CUR
+
+ Indicates a seek from the current position
+
+
+
+
+ public static final int SEEK_END
+
+ Indicates a seek from the end of a file
+
+
+
+
+
+
+ Methods
+
+
+
+
+ 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:
+
+
+
+ buf
+
+ destination array
+
+
+
+
+ off
+
+ offset within array
+
+
+
+
+ len
+
+ 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:
+
+
+
+ buf
+
+ destination array
+
+
+
+
+ off
+
+ offset within array
+
+
+
+
+ len
+
+ number of bytes to write
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+ Class <classname>org.postgresql.largeobject.LargeObjectManager</classname>
+
+
+ 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.PGConnection. 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.PGConnection)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.
+
+
+
+ Variables
+
+
+
+ public static final int WRITE
+
+ This mode indicates we want to write to an object.
+
+
+
+
+ public static final int READ
+
+ This mode indicates we want to read an object.
+
+
+
+
+ public static final int READWRITE
+
+ This mode is the default. It indicates we want read and write access to a large object.
+
+
+
+
+
+
+ Methods
+
+
+
+
+ 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.
+
+
+
+
+
+
+
+
+
+
+
+ Using the Driver in a Multithreaded or a Servlet Environment
+
+
+ threads
+ with JDBC
+
+
+
+ A problem with many JDBC drivers is that only
+ one thread can use a Connection at any one
+ time --- otherwise a thread could send a query while another one is
+ receiving results, and this could cause severe confusion.
+
+
+
+ The PostgreSQL JDBC driver
+ is thread safe.
+ Consequently, if your application uses multiple threads then you do
+ not have to worry about complex algorithms to ensure that only one thread
+ uses the database at a time.
+
+
+
+ If a thread attempts to use the connection while another one is
+ using it, it will wait until the other thread has finished its
+ current operation. If the operation is a regular SQL
+ statement, then the operation consists of sending the statement and
+ retrieving any ResultSet (in full). If it
+ is a fast-path call (e.g., reading a block
+ from a large object) then it consists of
+ sending and retrieving the respective data.
+
+
+
+ This is fine for applications and applets but can cause a
+ performance problem with servlets. If you have several threads
+ performing queries then each but one will pause.
+ To solve this, you are advised to create a pool of connections.
+ When ever a thread needs to use the database, it asks a manager
+ class for a Connection object. The manager
+ hands a free connection to the thread and marks it as busy. If a
+ free connection is not available, it opens one. Once the thread
+ has finished using the connection, it returns it to the manager
+ which can then either close it or add it to the pool. The manager
+ would also check that the connection is still alive and remove it
+ from the pool if it is dead. The down side of a connection pool is
+ that it increases the load on the server because a new session is
+ created for each Connection object. It is
+ up to you and your applications' requirements.
+
+
+
+
+ Connection Pools and Data Sources
+
+
+ connection pool
+ in JDBC
+
+
+
+ DataSource
+
+
+
+ JDBC 2 introduced standard connection pooling features in an
+ add-on API known as the JDBC 2.0 Optional
+ Package (also known as the JDBC 2.0
+ Standard Extension). These features have since been included in
+ the core JDBC 3 API. The
+ PostgreSQL JDBC drivers
+ support these features if it has been compiled with
+ JDK 1.3.x in combination with the
+ JDBC 2.0 Optional Package
+ (JDBC 2), or with JDK 1.4 or higher
+ (JDBC 3). Most application servers include
+ the JDBC 2.0 Optional Package, but it is
+ also available separately from the Sun
+ JDBC download site.
+
+
+
+ Overview
+
+
+ The JDBC API provides a client
+ and a server interface for connection pooling. The client
+ interface is javax.sql.DataSource,
+ which is what application code will typically use to
+ acquire a pooled database connection. The server interface
+ is javax.sql.ConnectionPoolDataSource,
+ which is how most application servers will interface with
+ the PostgreSQL JDBC
+ driver.
+
+
+
+ In an application server environment, the
+ application server configuration will typically refer to
+ the PostgreSQL
+ ConnectionPoolDataSource implementation,
+ while the application component code will typically acquire a
+ DataSource implementation provided by
+ the application server (not by
+ PostgreSQL).
+
+
+
+ For an environment without an application server,
+ PostgreSQL provides two implementations
+ of DataSource which an application can use
+ directly. One implementation performs connection pooling,
+ while the other simply provides access to database connections
+ through the DataSource interface without
+ any pooling. Again, these implementations should not be used
+ in an application server environment unless the application
+ server does not support the
+ ConnectionPoolDataSource interface.
+
+
+
+
+ Application Servers: <classname>ConnectionPoolDataSource</classname>
+
+
+ PostgreSQL includes one implementation
+ of ConnectionPoolDataSource for
+ JDBC 2 and one for JDBC 3,
+ as shown in .
+
+
+
+
+
+
+ Both implementations use the same configuration scheme.
+ JDBC requires that a
+ ConnectionPoolDataSource be configured via
+ JavaBean properties, shown in ,
+ so there are get and set methods for each of these properties.
+
+
+
+ <classname>ConnectionPoolDataSource</classname> Configuration Properties
+
+
+
+
+ Property
+ Type
+ Description
+
+
+
+
+
+ serverName
+ String
+ PostgreSQL database server
+ host name
+
+
+
+ databaseName
+ String
+ PostgreSQL database name
+
+
+
+ portNumber
+ int
+
+ TCP port which the PostgreSQL
+ database server is listening on (or 0 to use the default port)
+
+
+
+
+ user
+ String
+ User used to make database connections
+
+
+
+ password
+ String
+ Password used to make database connections
+
+
+
+ defaultAutoCommit
+ boolean
+
+ Whether connections should have autocommit enabled or disabled
+ when they are supplied to the caller. The default is
+ false, to disable autocommit.
+
+
+
+
+
+
+
+ Many application servers use a properties-style syntax to
+ configure these properties, so it would not be unusual to enter
+ properties as a block of text. If the application server provides
+ a single area to enter all the properties, they might be listed
+ like this:
+
+ serverName=localhost
+ databaseName=test
+ user=testuser
+ password=testpassword
+
+ Or, if semicolons are used as separators instead of newlines, it
+ could look like this:
+
+ serverName=localhost;databaseName=test;user=testuser;password=testpassword
+
+
+
+
+
+
+ Applications: <classname>DataSource</classname>
+
+ PostgreSQL includes two
+ implementations of DataSource
+ for JDBC 2 and two for JDBC
+ 3, as shown in .
+ The pooling implementations do not actually close connections
+ when the client calls the close method, but
+ instead return the connections to a pool of available connections
+ for other clients to use. This avoids any overhead of repeatedly
+ opening and closing connections, and allows a large number of
+ clients to share a small number of database connections.
+ The pooling data-source implementation provided here is not
+ the most feature-rich in the world. Among other things,
+ connections are never closed until the pool itself is closed;
+ there is no way to shrink the pool. As well, connections
+ requested for users other than the default configured user are
+ not pooled. Many application servers
+ provide more advanced pooling features and use the
+ ConnectionPoolDataSource implementation
+ instead.
+
+
+
+
+ All the implementations use the same configuration scheme.
+ JDBC requires that a
+ DataSource be configured via JavaBean
+ properties, shown in , so there
+ are get and set methods for each of these properties.
+
+
+
+ <classname>DataSource</classname> Configuration Properties
+
+
+
+
+ Property
+ Type
+ Description
+
+
+
+
+
+ serverName
+ String
+ PostgreSQL database server
+ host name
+
+
+
+ databaseName
+ String
+ PostgreSQL database name
+
+
+
+ portNumber
+ int
+ TCP port which the
+ PostgreSQL database server is
+ listening on (or 0 to use the default port)
+
+
+
+ user
+ String
+ User used to make database connections
+
+
+
+ password
+ String
+ Password used to make database connections
+
+
+
+
+
+ The pooling implementations require some additional
+ configuration properties, which are shown in .
+
+
+ Additional Pooling <classname>DataSource</classname> Configuration Properties
+
+
+
+
+ Property
+ Type
+ Description
+
+
+
+
+
+ dataSourceName
+ String
+ Every pooling DataSource must have a
+ unique name.
+
+
+
+ initialConnections
+ int
+ The number of database connections to be created
+ when the pool is initialized.
+
+
+
+ maxConnections
+ int
+ The maximum number of open database connections to
+ allow. When more connections are requested, the caller
+ will hang until a connection is returned to the pool.
+
+
+
+
+
+ shows an example of typical application code using a
+ pooling DataSource.
+
+
+ <literal>DataSource</literal> Code Example
+
+
+ Code to initialize a pooling DataSource might look like this:
+
+ Jdbc3PoolingDataSource source = new Jdbc3PoolingDataSource();
+ source.setDataSourceName("A Data Source");
+ source.setServerName("localhost");
+ source.setDatabaseName("test");
+ source.setUser("testuser");
+ source.setPassword("testpassword");
+ source.setMaxConnections(10);
+
+ Then code to use a connection from the pool might look
+ like this. Note that it is critical that the connections
+ are eventually closed. Else the pool will leak connections and
+ will eventually lock all the clients out.
+
+ Connection con = null;
+ try {
+ con = source.getConnection();
+ // use connection
+ } catch (SQLException e) {
+ // log error
+ } finally {
+ if (con != null) {
+ try { con.close(); } catch (SQLException e) {}
+ }
+ }
+
+
+
+
+
+
+ Data Sources and <acronym>JNDI</acronym>
+
+
+ JNDI
+
+
+
+ All the ConnectionPoolDataSource and
+ DataSource implementations can be stored
+ in JNDI. In the case of the nonpooling
+ implementations, a new instance will be created every time the
+ object is retrieved from JNDI, with the
+ same settings as the instance that was stored. For the
+ pooling implementations, the same instance will be retrieved
+ as long as it is available (e.g., not a different
+ JVM retrieving the pool from
+ JNDI), or a new instance with the same
+ settings created otherwise.
+
+
+
+ In the application server environment, typically the
+ application server's DataSource instance
+ will be stored in JNDI, instead of the
+ PostgreSQL
+ ConnectionPoolDataSource implementation.
+
+
+
+ In an application environment, the application may store
+ the DataSource in JNDI
+ so that it doesn't have to make a reference to the
+ DataSource available to all application
+ components that may need to use it. An example of this is
+ shown in .
+
+
+
+ <classname>DataSource</classname> <acronym>JNDI</acronym> Code Example
+
+
+ Application code to initialize a pooling DataSource and add
+ it to JNDI might look like this:
+
+ Jdbc3PoolingDataSource source = new Jdbc3PoolingDataSource();
+ source.setDataSourceName("A Data Source");
+ source.setServerName("localhost");
+ source.setDatabaseName("test");
+ source.setUser("testuser");
+ source.setPassword("testpassword");
+ source.setMaxConnections(10);
+ new InitialContext().rebind("DataSource", source);
+
+ Then code to use a connection from the pool might look
+ like this:
+
+ Connection con = null;
+ try {
+ DataSource source = (DataSource)new InitialContext().lookup("DataSource");
+ con = source.getConnection();
+ // use connection
+ } catch (SQLException e) {
+ // log error
+ } catch (NamingException e) {
+ // DataSource wasn't found in JNDI
+ } finally {
+ if (con != null) {
+ try { con.close(); } catch (SQLException e) {}
+ }
+ }
+
+
+
+
+
+
+
+
+ Further Reading
+
+
+ If you have not yet read it, you are advised you read the
+ JDBC API Documentation
+ (supplied with Sun's JDK) and the
+ JDBC Specification. Both are available from
+ .
+
+
+
+
+ contains updated information not included in this chapter and
+ also offers precompiled drivers.
+
+
+