zelime

    标准的LISTEN, NOTIFY和UNLISTEN命令来自标准的Statement接口。为找回并处理重新得到的通知，Connection必须投射到PostgreSQL™的特殊扩展接口PGConnection，它包含的方法getNotifications()可以找回所有显著的通知。

    Note
    JDBC驱动程序的一个关键限制是，它不能找回异步的通知，且必须poll the backend 以确认是否所有的通知都已传下。

例9.2 接收通知
import java.sql.*;

public class NotificationTest {

    public static void main(String args[]) throws Exception {
        Class.forName("org.postgresql.Driver");
        String url = "jdbc:postgresql://localhost:5432/test";
    // Create two distinct connections, one for the notifier
    // and another for the listener to show the communication
    // works across connections although this example would
    // work fine with just one connection.
        Connection lConn = DriverManager.getConnection(url,"test","");
        Connection nConn = DriverManager.getConnection(url,"test","");

    // Create two threads, one to issue notifications and
    // the other to receive them.
        Listener listener = new Listener(lConn);
        Notifier notifier = new Notifier(nConn);
        listener.start();
        notifier.start();
    }
}

class Listener extends Thread {

    private Connection conn;
    private org.postgresql.PGConnection pgconn;

    Listener(Connection conn) throws SQLException {
         this.conn = conn;
         this.pgconn = (org.postgresql.PGConnection)conn;
         Statement stmt = conn.createStatement();
         stmt.execute("LISTEN mymessage");
         stmt.close();
    }

    public void run() {
         while (true) {
         try {
             // issue a dummy query to contact the backend
             // and receive any pending notifications.
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT 1");
             rs.close();
             stmt.close();

             org.postgresql.PGNotification notifications[] = pgconn.getNotifications();
             if (notifications != null) {
                for (int i=0; i<notifications.length; i++) {
                 System.out.println("Got notification: " + notifications[i].getName());
             }
         }

     // wait a while before checking again for new
     // notifications
         Thread.sleep(500);
         } catch (SQLException sqle) {
         sqle.printStackTrace();
         } catch (InterruptedException ie) {
             ie.printStackTrace();
             }
         }
    }

}

class Notifier extends Thread {

    private Connection conn;

    public Notifier(Connection conn) {
        this.conn = conn;
    }

    public void run() {
         while (true) {
             try {
                Statement stmt = conn.createStatement();
                 stmt.execute("NOTIFY mymessage");
                stmt.close();
                 Thread.sleep(2000);
             } catch (SQLException sqle) {
                sqle.printStackTrace();
             } catch (InterruptedException ie) {
                ie.printStackTrace();
             }
         }
    }

}

    org.postgresql.largeobject 包向Java提供了libpq C接口的大对象API。它是由两个类组成的，处理创建、打开、删除大对象的LargeObjectManager，和处理独立对象的LargeObject。作为API的一个使用例子，请参考 例7.1 "在JDBC中处理二进制数据"。

    org.postgresql.largeobject 包向Java提供了libpq C接口的大对象API。它是由两个类组成的，处理创建、打开、删除大对象的LargeObjectManager，和处理独立对象的LargeObject。作为API的一个使用例子，请参考 例7.1 "在JDBC中处理二进制数据"。

      PostgreSQL™有一组数据类型用来在表中存储几何学的特点，包括单独的点、线和多边形，JAVA的org.postgresql.geometric包支持这些类型。欲了解以上的类和特点的详细情况请参考第十二章 深入阅读。

  例9.1 使用JDBC的圆周数据类型
    import java.sql.*;
    import org.postgresql.geometric.PGpoint;
    import org.postgresql.geometric.PGcircle;

    public class GeometricTest {
        public static void main(String args[]) throws Exception {
            Class.forName("org.postgresql.Driver");
            String url = "jdbc:postgresql://localhost:5432/test";
            Connection conn = DriverManager.getConnection(url,"test","");
            Statement stmt = conn.createStatement();
            stmt.execute("CREATE TEMP TABLE geomtest(mycirc circle)");
            stmt.close();
            insertCircle(conn);
            retrieveCircle(conn);
            conn.close();
        }

        private static void insertCircle(Connection conn)
                throws SQLException {

            PGpoint center = new PGpoint(1, 2.5);
            double radius = 4;
            PGcircle circle = new PGcircle(center, radius);
            PreparedStatement ps = conn.prepareStatement("INSERT INTO geomtest(mycirc) VALUES (?)");
            ps.setObject(1, circle);
            ps.executeUpdate();
            ps.close();
        }
        private static void retrieveCircle(Connection conn) throws SQLException {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT mycirc, area(mycirc) FROM geomtest");
            rs.next();
            PGcircle circle = (PGcircle)rs.getObject(1);
            double area = rs.getDouble(2);
            PGpoint center = circle.center;
            double radius = circle.radius;
            System.out.println("Center (X, Y) = (" + center.x + ", " + center.y + ")");
            System.out.println("Radius = " + radius);
            System.out.println("Area = " + area);
        }
    }

    为了访问某些扩展部分，你需要使用org.postgresql.PGConnection类一些特别的方法。你必须注意Driver.getConnection()返回的值。如：

       Connection db = Driver.getConnection(url, username, password);
        // ...
        // 稍后
        Fastpath fp = ((org.postgresql.PGConnection)db).getFastpathAPI();

表8.3 支持的date/time函数溢出

    JDBC规范定义了调用语法的溢出函数：{fn function_name(arguments)}。下表列出PostgresSQL™ 驱动程序支持的函数。驱动程序支持嵌套和混合的溢出函数和溢出值。附录C JDBC规范详述了这些函数。

    下表中的某些函数已经转化，但是没有reported as supported，因为它们的主题重复或者顺序改变了。虽然对字面上的值或者数字是无害的，但是在使用prepared statements时会引起问题。例如，"{fn right(?,?)}"将被转化为 "substring(? from (length(?)+1-?))"。如你所见，转化后的SQL比转换前要求更多的参数，而驱动程序不能自动处理这个。