JDBC Java 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 chapter 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. This section describes the steps you need to take before you can write or run programs that use the JDBC interface. Precompiled versions of the driver can be downloaded from the PostgreSQL JDBC web site. 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. For details, refer to the PostgreSQL installation instructions. 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. 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 . 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 -i option flag when starting postmaster. Also, the client authentication setup in the pg_hba.conf file may need to be configured. Refer to for details. The JDBC driver supports the trust, ident, password, md5, and crypt authentication methods. This section describes how to load and initialize the JDBC driver in your programs. 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. 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 -D 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 available SQLException being thrown, this is probably caused by the driver not being in the class path, or the value in the parameter not being correct. 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); To close the database connection, simply call the close() method to the Connection: db.close(); 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. 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(); 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. 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(); 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. 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. 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. 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(); PostgreSQL's JDBC driver fully supports calling PostgreSQL stored functions. 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(); 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. 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. 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. 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(); 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. This example will drop a table. Statement st = db.createStatement(); st.execute("DROP TABLE mytable"); st.close(); 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. 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(); 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. 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(); public class PGConnection These are the extra methods used to gain access to PostgreSQL's extensions. 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. Fastpath object allowing access to functions on the PostgreSQL server. 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. LargeObject object that implements the API 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. 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. FastpathFastpathArg, LargeObject public Object fastpath(int fnid, boolean resulttype, FastpathArg args[]) throws SQLException Send a function call to the PostgreSQL server. fnid - Function id resulttype - True if the result is an integer, false for other results args - FastpathArguments to pass to fast-path call 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 name - Function name resulttype - True if the result is an integer, false for other results args - FastpathArguments to pass to fast-path call null if no data, Integer if an integer result, or byte[] otherwise LargeObject public int getInteger(String name, FastpathArg args[]) throws SQLException This convenience method assumes that the return value is an Integer name - Function name args - Function arguments integer result 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. name - Function name args - Function arguments byte[] array containing result 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! 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. 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. 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. Fastpath, LargeObjectManager, LargeObject public FastpathArg(int value) Constructs an argument that consists of an integer value value - int value to set public FastpathArg(byte bytes[]) Constructs an argument that consists of an array of bytes bytes - array to store public FastpathArg(byte buf[], int off, int len) Constructs an argument that consists of part of a byte array 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 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 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. 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. LargeObjectManager 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 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 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 buf destination array off offset within array len number of bytes to write 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. 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. 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. 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 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. 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. PostgreSQL includes one implementation of ConnectionPoolDataSource for JDBC 2 and one for JDBC 3, as shown in . JDBC Implementation Class 2 org.postgresql.jdbc2.optional.ConnectionPool 3 org.postgresql.jdbc3.Jdbc3ConnectionPool 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. 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 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. JDBC Pooling Implementation Class 2 No org.postgresql.jdbc2.optional.SimpleDataSource 2 Yes org.postgresql.jdbc2.optional.PoolingDataSource 3 No org.postgresql.jdbc3.Jdbc3SimpleDataSource 3 Yes org.postgresql.jdbc3.Jdbc3PoolingDataSource 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. 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 . 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. 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) {} } } 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 . 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) {} } } 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.