From ee8d39a8524140d984dd1048665500fe581037cd Mon Sep 17 00:00:00 2001 From: "Thomas G. Lockhart" Date: Mon, 4 Oct 1999 15:16:35 +0000 Subject: [PATCH] Add mention of transactions and large objects. Still need the code updated for LO examples. --- doc/src/sgml/jdbc.sgml | 983 +++++++++++++++++++++-------------------- 1 file changed, 514 insertions(+), 469 deletions(-) diff --git a/doc/src/sgml/jdbc.sgml b/doc/src/sgml/jdbc.sgml index 5339ec1f07..364fe90c11 100644 --- a/doc/src/sgml/jdbc.sgml +++ b/doc/src/sgml/jdbc.sgml @@ -1,384 +1,385 @@ - -JDBC Interface - - - -Author - -Written by Peter T. Mount, the -author of the JDBC driver. - - - - - -JDBC is a core API of Java 1.1 and later. -It provides a standard set of -interfaces to SQL-compliant databases. - - -Postgres provides -a type 4 JDBC Driver. Type 4 indicates that the driver -is written in Pure Java, and communicates in the database's own network -protocol. Because of this, the driver is platform independent. Once compiled, -the driver can be used on any platform. - - - -Building the <acronym>JDBC</acronym> Interface - - -Compiling the Driver - - -The driver's source is located in the src/interfaces/jdbc - directory of the -source tree. To compile simply change directory to that directory, and type: - - + + JDBC Interface + + + + Author + + Written by Peter T. Mount, the + author of the JDBC driver. + + + + + + JDBC is a core API of Java 1.1 and later. + It provides a standard set of + interfaces to SQL-compliant databases. + + + + Postgres provides + a type 4 JDBC Driver. + Type 4 indicates that the driver + is written in Pure Java, and communicates in the database's own network + protocol. Because of this, the driver is platform independent. Once compiled, + the driver can be used on any platform. + + + + Building the <acronym>JDBC</acronym> Interface + + + Compiling the Driver + + + The driver's source is located in the src/interfaces/jdbc + directory of the + source tree. To compile simply change directory to that directory, and type: + + % make - - - - -Upon completion, you will find the archive postgresql.jar - in the current -directory. This is the JDBC driver. - - - -You must use make, -not javac, -as the driver uses some dynamic -loading techniques for performance reasons, -and javac cannot cope. -The Makefile will generate the jar archive. - - - - - - -Installing the Driver - - -To use the driver, the jar archive postgresql.jar needs to be included in -the CLASSPATH. - - -Example: - - -I have an application that uses the JDBC driver to access a large database -containing astronomical objects. I have the application and the jdbc driver -installed in the /usr/local/lib directory, and the java jdk installed in /usr/local/jdk1.1.6. - - - -To run the application, I would use: - - -export CLASSPATH = \ - /usr/local/lib/finder.jar:/usr/local/lib/postgresql.jar:. + + + + + Upon completion, you will find the archive postgresql.jar + in the current + directory. This is the JDBC driver. + + + + You must use make, + not javac, + as the driver uses some dynamic + loading techniques for performance reasons, + and javac cannot cope. + The Makefile will generate the jar archive. + + + + + + + Installing the Driver + + + To use the driver, the jar archive postgresql.jar needs to be included in + the CLASSPATH. + + + + Example + + + I have an application that uses the JDBC driver to access a large database + containing astronomical objects. I have the application and the jdbc driver + installed in the /usr/local/lib directory, and the java jdk installed in /usr/local/jdk1.1.6. + + + + To run the application, I would use: + + +export CLASSPATH = /usr/local/lib/finder.jar:/usr/local/lib/postgresql.jar:. java uk.org.retep.finder.Main - - -Loading the driver is covered later on in this chapter. - - - - - -Preparing the Database for <acronym>JDBC</acronym> - - -Because Java can only use TCP/IP connections, the Postgres postmaster -must be running with the -i flag. - - -Also, the pg_hba.conf file must be configured. It's located in the PGDATA -directory. In a default installation, this file permits access only by UNIX -domain sockets. For the JDBC driver to connect to the same localhost, you need -to add something like: - - + + + + + Loading the driver is covered later on in this chapter. + + + + + + + Preparing the Database for <acronym>JDBC</acronym> + + + Because Java can only use TCP/IP connections, the Postgres postmaster + must be running with the -i flag. + + + + Also, the pg_hba.conf file must be configured. It's located in the PGDATA + directory. In a default installation, this file permits access only by Unix + domain sockets. For the JDBC driver to connect to the same localhost, you need + to add something like: + + host all 127.0.0.1 255.255.255.255 password - - -Here access to all databases are possible from the local machine -with JDBC. - - - -The JDBC Driver supports trust, ident, -password and crypt authentication methods. - - - - -Using the Driver - - -This section 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. The basic -example is used here. - - - - -Importing <acronym>JDBC</acronym> - - -Any source that uses JDBC -needs to import the java.sql package, using: - - + + + Here access to all databases are possible from the local machine + with JDBC. + + + + The JDBC Driver supports trust, ident, + password and crypt authentication methods. + + + + + Using the Driver + + + This section 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. The basic + example is used here. + + + + + Importing <acronym>JDBC</acronym> + + + Any source that uses JDBC + needs to import the java.sql package, using: + + import java.sql.*; - - - - -Do not import the 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 to the best one to use. - - - -In the first method, your code implicitly loads the driver using the -Class.forName() method. For Postgres, you would use: - - + + + + + Do not import the 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 to the best one to use. + + + + In the first method, your code implicitly loads the driver using the + Class.forName() method. + For Postgres, you would use: + + Class.forName("postgresql.Driver"); - - -This will load the driver, and while loading, the driver will automatically -register itself with JDBC. - - - -Note: The forName() method - can throw a ClassNotFoundException, so you will -need to catch it if the driver is not available. - - - -This is the most common method to use, but restricts your code to use just -Postgres. -If your code may access another database in the future, and you -don't use our 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: - - + + + This will load the driver, and while loading, the driver will automatically + register itself with JDBC. + + + + Note: The forName() method + can throw a ClassNotFoundException, so you will + need to catch it if the driver is not available. + + + + This is the most common method to use, but restricts your code to use just + Postgres. + If your code may access another database in the future, and you + don't use our 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=postgresql.Driver example.ImageViewer - - - - -In this example, the JVM will attempt to load the driver as part of it's -initialisation. 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 databases, without recompiling the code. The only thing -that would also change is the 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 classpath, 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 Postgres, this takes one of the following -forms: - - - - -jdbc:postgresql:database - - - - - -jdbc:postgresql://host/database - - - - - -jdbc:postgresql://host:port/database - - - - - -where: - - - - -host - - - -The hostname of the server. Defaults to "localhost". - - - - - - -port - - - -The port number the server is listening on. Defaults to the Postgres -standard port number (5432). - - - - - - -database - - - -The database name. - - - - - - - - -To connect, you need to get a Connection instance from -JDBC. To do this, -you would use the DriverManager.getConnection() method: - - + + + In this example, the JVM will attempt to load the driver as part of it's + initialisation. 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 databases, without recompiling the code. The only thing + that would also change is the 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 classpath, 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 Postgres, this takes one of the following + forms: + + + + + jdbc:postgresql:database + + + + + + jdbc:postgresql://>hos>/database + + + + + + jdbc:postgresql://>hos>">poe>/database + + + + + where: + + + + + host + + + + The hostname of the server. Defaults to "localhost". + + + + + + + port + + + + The port number the server is listening on. Defaults to the Postgres + standard port number (5432). + + + + + + + database + + + + The database name. + + + + + + + + To connect, you need to get a Connection instance from + JDBC. To do this, + you would use the DriverManager.getConnection() method: + + Connection db = DriverManager.getConnection(url,user,pwd); - - - - -Issuing a Query and Processing the Result - - -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 query. This will return a ResultSet instance, which contains -the entire result. - - - -Using the Statement Interface - - -The following must be considered when using the Statement interface: - - - - -You can use a Statement instance as many times as you want. You could -create one as soon as you open the connection, and use it for the connections -lifetime. You have to remember that only one ResultSet can exist per Statement. - - - - - -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 the sections covering -Threads and Servlets later in this document if you are thinking of using them, -as it covers some important points. - - - - - - - -Using the ResultSet 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 spec, you should access a field only once. It's safest -to stick to this rule, although at the current time, the Postgres 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 with it. - - - - - -Once you request another query with the Statement used to create a -ResultSet, the currently open instance is closed. - - - - - - -An example is as follows: - - + + + + + + Issuing a Query and Processing the Result + + + 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 query. This will return a ResultSet instance, which contains + the entire result. + + + + Using the Statement Interface + + + The following must be considered when using the Statement interface: + + + + + You can use a Statement instance as many times as you want. You could + create one as soon as you open the connection, and use it for the connections + lifetime. You have to remember that only one ResultSet can exist per Statement. + + + + + + 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 the sections covering + Threads and Servlets later in this document if you are thinking of using them, + as it covers some important points. + + + + + + + + Using the ResultSet 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 spec, you should access a + field only once. It's safest to stick to this rule, although + at the current time, the Postgres 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 with it. + + + + + + Once you request another query with the Statement used to create a + ResultSet, the currently open instance is closed. + + + + + + + An example is as follows: + + Statement st = db.createStatement(); ResultSet rs = st.executeQuery("select * from mytable"); while(rs.next()) { @@ -387,74 +388,97 @@ while(rs.next()) { } rs.close(); st.close(); - - - - + + + + - -Performing Updates + + Performing Updates - -To perform an update (or any other SQL statement that does not return a -result), you simply use the executeUpdate() method: + + To perform an update (or any other SQL statement that does not return a + result), you simply use the executeUpdate() method: - + st.executeUpdate("create table basic (a int2, b int2)"); - - - + + + - -Closing the Connection + + Closing the Connection - -To close the database connection, simply call the close() method to the Connection: + + To close the database connection, simply call the close() method to the Connection: - + db.close(); - - - - - -Using Large Objects - - -In Postgres, -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 as a -Table/Index pair, and are refered to from your own tables, by an OID value. - - - -Now, there are you methods of using Large Objects. The first is the -standard JDBC way, and is documented here. The other, uses our own extension -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 JDBC, the standard way to access them 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. - - - -For example, suppose -you have a table containing the file name of an image, and a large object -containing that image: - - + + + + + + Using Large Objects + + + In Postgres, + 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 as a + Table/Index pair, and are referred to from your own tables by an OID value. + + + + + + For Postgres, you must access large + objects within an SQL transaction. Although this has always been + true in principle, it was not strictly enforced until the + release of v6.5. 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 + + + + + + + Now, there are two methods of using Large Objects. The first is the + standard JDBC way, and is documented here. The + other, uses our own extension + 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 JDBC, the standard way to access them 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. + + + + For example, suppose + you have a table containing the file name of an image, and a large object + containing that image: + + create table images (imgname name,imgoid oid); - - + + - -To insert an image, you would use: + + 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 (?,?)"); @@ -463,20 +487,20 @@ ps.setBinaryStream(2,fis,file.length()); ps.executeUpdate(); ps.close(); fis.close(); - - + + - -Now in this example, 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. - + + Now in this example, 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. + - -Retrieving an image is even easier (I'm using PreparedStatement here, but -Statement can equally be used): + + Retrieving an image is even easier (I'm using PreparedStatement here, but + Statement can equally be used): - + PreparedStatement ps = con.prepareStatement("select oid from images where name=?"); ps.setString(1,"myimage.gif"); ResultSet rs = ps.executeQuery(); @@ -489,31 +513,33 @@ if(rs!=null) { rs.close(); } ps.close(); - - - - -Now here you can see where the Large Object is retrieved as an InputStream. -You'll 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. - - - - -<application>Postgres</application> Extensions to the <acronym>JDBC</acronym> <acronym>API</acronym> - - -Postgres is an extensible database system. -You can add your own functions -to the backend, which can then be called from queries, or even add your own -data types. - - -Now, as these are facilities unique to us, 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. + + + + + Now here you can see where the Large Object is retrieved as an InputStream. + You'll 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. + + + + + <application>Postgres</application> Extensions to the + <acronym>JDBC</acronym> <acronym>API</acronym> + + + Postgres is an extensible database system. + You can add your own functions + to the backend, which can then be called from queries, or even add your own + data types. + + + + Now, as these are facilities unique to us, 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. -- 2.40.0