From f98298e26fa4ad17772f6242828b73f7bec57f68 Mon Sep 17 00:00:00 2001 From: Barry Lind Date: Mon, 26 Nov 2001 05:57:57 +0000 Subject: [PATCH] Updates to JDBC doc: Editing pass over entire chapter Rewrote section dealing with Large Objects to also talk about bytea support Removed secion on Serialize functionality a we intend to remove it in the next release --- doc/src/sgml/jdbc.sgml | 952 +++++++++++++------------------------------------ 1 file changed, 248 insertions(+), 704 deletions(-) diff --git a/doc/src/sgml/jdbc.sgml b/doc/src/sgml/jdbc.sgml index c4c7caff2d..89e1e69f2c 100644 --- a/doc/src/sgml/jdbc.sgml +++ b/doc/src/sgml/jdbc.sgml @@ -1,5 +1,5 @@ @@ -377,9 +377,11 @@ db.close(); Any time you want to issue SQL statements to - the database, you require a Statement - instance. Once you have a Statement, you - can use the executeQuery() method to issue a + 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. illustrates this process. @@ -389,11 +391,29 @@ db.close(); Processing a Simple Query in <acronym>JDCB</acronym> - This example with issue a simple query and print out the first - column of each row. + 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"); +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 will issues the same query as before using + 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)); @@ -405,7 +425,8 @@ st.close(); - Using the <classname>Statement</classname> Interface + Using the <classname>Statement</classname> or <classname> + PreparedStatement</classname> Interface The following must be considered when using the @@ -419,7 +440,7 @@ st.close(); open the connection and use it for the connection's lifetime. But you have to remember that only one ResultSet can exist per - Statement. + Statement at a given time. @@ -439,6 +460,13 @@ st.close(); thinking of using threads, as it covers some important points. + + + + When you are done using the Statement + you should close the Statement. + + @@ -495,73 +523,141 @@ st.close(); Performing Updates - To perform an update (or any other SQL - statement that does not return a result), you simply use the - executeUpdate() method: + To change data (perform an insert, update, or delete) + you use the executeUpdate() method. + executeUpdate() is similar to the + executeQuery() used to issue a select, + however it doesn't return a ResultSet, + instead it returns the number of records affected by the insert, + update, or delete statement. + + + This example will issue a simple delete and print out the number + of rows deleted. -st.executeUpdate("CREATE TABLE basic (a int, b int)"); +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(); - - Using Large Objects + + Creating and Modifying Database Objects + + + To create, modify or drop a database object like a table or view + you use the execute() method. + execute is similar to the + executeQuery() used to issue a select, + however it doesn't return a result. + + + + This example will drop a table. + +Statement st = db.createStatement(); +ResultSet rs = st.executeQuery("DROP TABLE mytable"); +st.close(); + + + + + + Storing Binary Data + + + PostgreSQL provides two distinct way to + store binary data. Binary data can be stored in a table using + PostgreSQL's binary datatype + bytea, or by using the Large Object + feature which stores the binary data in a separate table in a special + format, and refers to from your own tables by an OID value. + + + + In order to determine which method is appropriate you + need to understand the limitations of each method. The + bytea datatype is not well suited for storing very + large amounts of binary data. While a column of type + bytea can hold upto 1Gig of binary data, it would + require a huge amount of memory (RAM) 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 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 case view + and/or modify any Large Object, even if they don't have + permissions to view/update the row containing the Large Object. + + + + 7.2 is the first release that the JDBC Driver + supports the bytea datatype. The introduction of + this functionality in 7.2 has introduced a change in behavior + as compared to previous releases. In 7.2 the methods + getBytes(), setBytes(), + getBinaryStream(), and + setBinaryStream() operate on + the bytea datatype. In 7.1 these methods operated + on the OID datatype associated with Large Objects. + It is possible to revert the driver back to the old 7.1 behavior + by setting the compatible property on + the Connection to a value of + 7.1 + - In PostgreSQL, Large - Objects (also known as BLOBs) are - used to hold data in the database that cannot be stored in a normal - SQL table. They are stored in a separate table in a special format, - and are referred to from your own tables by an OID value. + To use the bytea datatype you should simply use + the getBytes(), setBytes(), + getBinaryStream(), or + setBinaryStream() methods. + + + + To use the Large Object functionality you can use either the + LargeObject API + provided by the PostgreSQL + JDBC Driver, or by using the + getBLOB() and setBLOB() + methods. - For PostgreSQL, you must access Large + For PostgreSQL, you must access Large Objects within an SQL transaction. You would open a transaction by using the setAutoCommit() method with an input - parameter of false: - -Connection mycon; -... -mycon.setAutoCommit(false); -... // now use Large Objects - + parameter of false. - - There are two methods of using Large Objects. The first is the - standard JDBC way, and is documented here. The - other, uses PostgreSQL extensions to - the API, which presents the libpq large object - API to Java, providing even better access to - large objects than the standard. Internally, the driver uses the - extension to provide large object support. - + 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 + bytea datatypes. So it is recommended that you + use the LargeObject API + if you intend to use Large Objects. + - - In JDBC, the standard way to access Large - Objects is using the getBinaryStream() method - in ResultSet, and - setBinaryStream() method in - PreparedStatement. These methods make the - large object appear as a Java stream, allowing you to use the - java.io package, and others, to manipulate the - object. illustrates the usage of - this approach. - - - Using the <acronym>JDBC</acronym> Large Object Interface + + Binary Data Examples For example, suppose you have a table containing the file name of - an image and you have a large object containing that image: + an image and you also want to store the image in a bytea + column: -CREATE TABLE images (imgname text, imgoid oid); +CREATE TABLE images (imgname text, img bytea); @@ -570,26 +666,19 @@ CREATE TABLE images (imgname text, imgoid oid); File file = new File("myimage.gif"); FileInputStream fis = new FileInputStream(file); -PreparedStatement ps = conn.prepareStatement("INSERT INTO images VALUES (?, ?)"); +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(); - - - - The question marks must appear literally. The actual data is - substituted by the next lines. - - - - Here, setBinaryStream transfers a set number - of bytes from a stream into a Large Object, and stores the OID - into the field holding a reference to it. Notice that the - creation of the Large Object itself in the database happens - transparently. + + 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[]. @@ -598,14 +687,13 @@ fis.close(); Statement class can equally be used.) -PreparedStatement ps = con.prepareStatement("SELECT imgoid FROM images WHERE imgname=?"); +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()) { - InputStream is = rs.getBinaryStream(1); + byte[] imgBytes = rs.getBytes(1); // use the stream in some way here - is.close(); } rs.close(); } @@ -615,13 +703,88 @@ ps.close(); Here you can see how the Large Object is retrieved as an - InputStream. You will also notice that we - close the stream before processing the next row in the - result. This is part of the JDBC specification, - which states that any InputStream returned - is closed when ResultSet.next() or - ResultSet.close() is called. + byte[]. You could have used a + InputStream object instead. + + + Alternativly 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 +conn.setAutoCommit(false); +LargeObjectManager lobj = ((org.postgresql.Connection)conn).getLargeObjectAPI(); + +//create a new large object +int oid = lobj.create(LargeObjectManager.READ | LargeObjectManager.WRITE); + +//open the large object for write +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 +conn.setAutoCommit(false); +LargeObjectManager lobj = ((org.postgresql.Connection)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(); + + + @@ -1932,15 +2095,13 @@ java.lang.Object - Normally, client code would use the getAsciiStream, - getBinaryStream, or getUnicodeStream methods in ResultSet, or - setAsciiStream, setBinaryStream, or setUnicodeStream methods in - PreparedStatement to access Large Objects. + Normally, client code would use the methods in + BLOB to access large objects. - However, sometimes lower level access to Large Objects are - required, that are not supported by the JDBC + However, sometimes lower level access to Large Objects is + required, that is not supported by the JDBC specification. @@ -2208,11 +2369,9 @@ lobj = ((org.postgresql.Connection)myconn).getLargeObjectAPI(); - Normally, client code would use the getAsciiStream, - getBinaryStream, or getUnicodeStream methods in ResultSet, or - setAsciiStream, setBinaryStream, or setUnicodeStream methods in - PreparedStatement to access Large Objects. However, sometimes - lower level access to Large Objects are required, that are not + 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. @@ -2318,622 +2477,8 @@ public void unlink(int oid) throws SQLException - - - Object Serialization - - - PostgreSQL is not a normal - SQL database. It is more extensible than most - other databases, and does support object oriented features that - are unique to it. - - - - One of the consequences of this, is that you can have one table - refer to a row in another table. For example: - -test=> CREATE TABLE users (username NAME,fullname TEXT); -CREATE -test=> CREATE TABLE server (servername NAME,adminuser users); -CREATE -test=> INSERT INTO users VALUES ('peter','Peter Mount'); -INSERT 2610132 1 -test=> INSERT INTO server VALUES ('maidast',2610132::users); -INSERT 2610133 1 -test=> SELECT * FROM users; -username|fullname ---------+-------------- -peter |Peter Mount -(1 row) - -test=> SELECT * FROM server; -servername|adminuser -----------+--------- -maidast | 2610132 -(1 row) - - Okay, the above example shows that we can use a table name as a - field, and the row's oid value is stored in that field. - - - - What does this have to do with Java? - - - - In Java, you can store an object to a Stream as long as it's class - implements the java.io.Serializable interface. This process, known - as Object Serialization, can be used to store complex objects into - the database. - - - - Now, under JDBC, you would have to use a - Large Object to store them. However, you cannot perform queries on - those objects. - - - - What the org.postgresql.util.Serialize class does, is provide a - means of storing an object as a table, and to retrieve that object - from a table. In most cases, you would not need to access this - class direct, but you would use the PreparedStatement.setObject() - and ResultSet.getObject() methods. Those methods will check the - objects class name against the table's in the database. If a match - is found, it assumes that the object is a Serialized object, and - retrieves it from that table. As it does so, if the object - contains other serialized objects, then it recurses down the tree. - - - - Sound's complicated? In fact, it's simpler than what I wrote - - it's just difficult to explain. - - - - The only time you would access this class, is to use the create() - methods. These are not used by the driver, but issue one or more - CREATE TABLE statements to the database, based on a Java Object - or Class that you want to serialize. - - - - Oh, one last thing. If your object contains a line like: - -public int oid; - - then, when the object is retrieved from the table, it is set to - the oid within the table. Then, if the object is modified, and re- - serialized, the existing entry is updated. - - - - If the oid variable is not present, then when the object is - serialized, it is always inserted into the table, and any existing - entry in the table is preserved. - - - - Setting oid to 0 before serialization, will also cause the object - to be inserted. This enables an object to be duplicated in the - database. - - - -Class org.postgresql.util.Serialize - -java.lang.Object - | - +----org.postgresql.util.Serialize - - public class Serialize extends Object - - This class uses PostgreSQL's object oriented features to store Java -Objects. It does this by mapping a Java Class name to a table in the -database. Each entry in this new table then represents a Serialized -instance of this class. As each entry has an OID (Object IDentifier), -this OID can be included in another table. This is too complex to show -here, and will be documented in the main documents in more detail. - -Constructors - - public Serialize(org.postgresql.Connection c, - String type) throws SQLException - - This creates an instance that can be used to serialize -or deserialize a Java object from a PostgreSQL table. - -Methods - - public Object fetch(int oid) throws SQLException - - This fetches an object from a table, given it's OID - - Parameters: - oid - The oid of the object - - Returns: - Object relating to oid - - Throws: SQLException - on error - - public int store(Object o) throws SQLException - - This stores an object into a table, returning it's OID. - - If the object has an int called OID, and it is > 0, then -that value is used for the OID, and the table will be updated. If the -value of OID is 0, then a new row will be created, and the value of -OID will be set in the object. This enables an object's value in the -database to be updateable. If the object has no int called OID, then -the object is stored. However if the object is later retrieved, -amended and stored again, it's new state will be appended to the -table, and will not overwrite the old entries. - - Parameters: - o - Object to store (must implement Serializable) - - Returns: - oid of stored object - - Throws: SQLException - on error - - public static void create(org.postgresql.Connection con, - Object o) throws SQLException - - This method is not used by the driver, but it creates a -table, given a Serializable Java Object. It should be used before -serializing any objects. - - Parameters: - c - Connection to database - o - Object to base table on - - Throws: SQLException - on error - - public static void create(org.postgresql.Connection con, - Class c) throws SQLException - - This method is not used by the driver, but it creates a -table, given a Serializable Java Object. It should be used before -serializing any objects. - - Parameters: - c - Connection to database - o - Class to base table on - - Throws: SQLException - on error - - public static String toPostgreSQL(String name) throws SQLException - - This converts a Java Class name to a PostgreSQL table, by - replacing . with _ - - Because of this, a Class name may not have _ in the name. - - Another limitation, is that the entire class name (including - packages) cannot be longer than 31 characters (a limit -forced by PostgreSQL). - - Parameters: - name - Class name - - Returns: - PostgreSQL table name - - Throws: SQLException - on error - - public static String toClassName(String name) throws SQLException - - This converts a PostgreSQL table to a Java Class name, by - replacing _ with . - - Parameters: - name - PostgreSQL table name - - Returns: - Class name - - Throws: SQLException - on error - -Utility Classes - -The org.postgresql.util package contains classes used by the internals of -the main driver, and the other extensions. - -Class org.postgresql.util.PGmoney - -java.lang.Object - | - +----org.postgresql.util.PGobject - | - +----org.postgresql.util.PGmoney - - public class PGmoney extends PGobject implements Serializable, -Cloneable - - This implements a class that handles the PostgreSQL money type - -Variables - - public double val - - The value of the field - -Constructors - - public PGmoney(double value) - - Parameters: - value - of field - - public PGmoney(String value) throws SQLException - - Create a money. - - Parameters: - value - Definition of this money in PostgreSQL's -syntax - - public PGmoney() - - Required by the driver - -Methods - - public void setValue(String s) throws SQLException - - Parameters: - s - Definition of this money 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 moneys 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 PGmoney in the syntax expected by PostgreSQL - - Overrides: - getValue in class PGobject - - -Class org.postgresql.util.PGobject - -java.lang.Object - | - +----org.postgresql.util.PGobject - - public class PGobject extends Object implements Serializable, -Cloneable - - This class is used to describe data types that are unknown by - JDBC -Standard. - A call to org.postgresql.Connection permits a class that extends this -class to be associated with a named type. This is how the -org.postgresql.geometric package operates. - ResultSet.getObject() will return this class for any type that is -not recognized on having it's own handler. Because of this, any -PostgreSQL data type is supported. - -Constructors - - public PGobject() - - This is called by org.postgresql.Connection.getObject() to -create the object. - -Methods - - public final void setType(String type) - - This method sets the type of this object. - - It should not be extended by subclasses, hence its final - - Parameters: - type - a string describing the type of the object - - public void setValue(String value) throws SQLException - - This method sets the value of this object. It must be -overridden. - - Parameters: - value - a string representation of the value of the - object - - Throws: SQLException - thrown if value is invalid for this type - - public final String getType() - - As this cannot change during the life of the object, it's -final. - - Returns: - the type name of this object - - public String getValue() - - This must be overridden, to return the value of the object, -in the form required by PostgreSQL. - - Returns: - the value of this object - - public boolean equals(Object obj) - - This must be overridden to allow comparisons of objects - - Parameters: - obj - Object to compare with - - Returns: - true if the two objects are identical - - Overrides: - equals in class Object - - public Object clone() - - This must be overridden to allow the object to be cloned - - Overrides: - clone in class Object - - public String toString() - - This is defined here, so user code need not override it. - - Returns: - the value of this object, in the syntax expected by -PostgreSQL - - Overrides: - toString in class Object - - -Class org.postgresql.util.PGtokenizer - -java.lang.Object - | - +----org.postgresql.util.PGtokenizer - - public class PGtokenizer extends Object - - This class is used to tokenize the text output of PostgreSQL. - - We could have used StringTokenizer to do this, however, we needed -to handle nesting of '(' ')' '[' ']' '<' and '>' as these are used by -the geometric data types. - - It's mainly used by the geometric classes, but is useful in parsing -any output from custom data types output from PostgreSQL. - - See Also: - PGbox, PGcircle, PGlseg, PGpath, PGpoint, PGpolygon - -Constructors - - public PGtokenizer(String string, - char delim) - - Create a tokenizer. - - Parameters: - string - containing tokens - delim - single character to split the tokens - -Methods - - public int tokenize(String string, - char delim) - - This resets this tokenizer with a new string and/or -delimiter. - - Parameters: - string - containing tokens - delim - single character to split the tokens - - public int getSize() - - Returns: - the number of tokens available - - public String getToken(int n) - - Parameters: - n - Token number ( 0 ... getSize()-1 ) - - Returns: - The token value - - public PGtokenizer tokenizeToken(int n, - char delim) - - This returns a new tokenizer based on one of our tokens. The -geometric data types use this to process nested tokens (usually -PGpoint). - - Parameters: - n - Token number ( 0 ... getSize()-1 ) - delim - The delimiter to use - - Returns: - A new instance of PGtokenizer based on the token - - public static String remove(String s, - String l, - String t) - - This removes the lead/trailing strings from a string - - Parameters: - s - Source string - l - Leading string to remove - t - Trailing string to remove - - Returns: - String without the lead/trailing strings - - public void remove(String l, - String t) - - This removes the lead/trailing strings from all tokens - - Parameters: - l - Leading string to remove - t - Trailing string to remove - - public static String removePara(String s) - - Removes ( and ) from the beginning and end of a string - - Parameters: - s - String to remove from - - Returns: - String without the ( or ) - - public void removePara() - - Removes ( and ) from the beginning and end of all tokens - - public static String removeBox(String s) - - Removes [ and ] from the beginning and end of a string - - Parameters: - s - String to remove from - - Returns: - String without the [ or ] - - public void removeBox() - - Removes [ and ] from the beginning and end of all tokens - - public static String removeAngle(String s) - - Removes < and > from the beginning and end of a string - - Parameters: - s - String to remove from - - Returns: - String without the < or > - - public void removeAngle() - - Removes < and > from the beginning and end of all tokens - - -Class org.postgresql.util.Serialize - -This was documented earlier under Object Serialization. - -Class org.postgresql.util.UnixCrypt - -java.lang.Object - | - +----org.postgresql.util.UnixCrypt - - public class UnixCrypt extends Object - - This class provides us with the ability to encrypt passwords when -sent over the network stream - - Contains static methods to encrypt and compare passwords with Unix -encrypted passwords. - - See John Dumas's Java Crypt page for the original source. - - (Invalid URL) http://www.zeh.com/local/jfd/crypt.html - -Methods - - public static final String crypt(String salt, - String original) - - Encrypt a password given the clear-text password and a -salt. - - Parameters: - salt - A two-character string representing the salt -used - to iterate the encryption engine in lots of different - ways. If you are generating a new encryption then this - value should be randomized. - original - The password to be encrypted. - - Returns: - A string consisting of the 2-character salt followed -by - the encrypted password. - - public static final String crypt(String original) - - Encrypt a password given the clear-text password. This method -generates a random salt using the 'java.util.Random' class. - - Parameters: - original - The password to be encrypted. - - Returns: - A string consisting of the 2-character salt followed -by - the encrypted password. - - public static final boolean matches(String encryptedPassword, - String enteredPassword) - - Check that enteredPassword encrypts to encryptedPassword. - - Parameters: - encryptedPassword - The encryptedPassword. The first -two characters are assumed to be the salt. This string would be the -same as one found in a Unix /etc/passwd file. - enteredPassword - The password as entered by the user -(or otherwise acquired). - - Returns: - true if the password should be considered correct. - - - - + Using the driver in a multi-threaded or a servlet environment @@ -2947,9 +2492,8 @@ same as one found in a Unix /etc/passwd file. - PostgreSQL 6.4 brought thread safety to - the entire driver. (Standard JDBC was thread - safe in 6.3, but the Fastpath API was not.) + 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 uses the database at any time. -- 2.11.0