PostgreSQL?扩展到JDBCAPI极品教程

几何数据类型

PostgreSQL?有一组数据类型可以存储到表的几何特性。这些包括单点、线和多边形。我们支持这些类型在Java org.postgresql。几何包。请参考提供的细节类的Javadoc和特性在第十二章提到,进一步阅读。

例9.1。使用JDBC数据类型

进口java.sql。*;

进口org.postgresql.geometric。PGpoint;进口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);
}

}

///////////////////////////////////////////////////////////

听/通知

听并通知提供一个简单形式的信号或进程间通信机制的集合过程访问相同的PostgreSQL?数据库。有关通知参考主服务器文档的更多信息。本节只处理JDBC特定方面的通知。

标准听、通知和UNLISTEN命令通过标准的发布声明接口。检索和处理检索通知连接必须将PostgreSQL?PGConnection特定的扩展接口。从那里getNotifications()方法可以用于检索任何杰出的通知。

请注意

JDBC驱动程序的主要限制是它不能接收异步通知,必须调查后端来检查是否有通知了。

例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 = conn.unwrap(org.postgresql.PGConnection.class);
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 a="" again="" before="" catch="" checking="" for="" got="" interruptedexception="" new="" notification:="" notifications="" pre="" sqlexception="" wait="" while="">
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();
}
}
}
}

/////////////////////////////////////////////////

 

服务器准备好的语句

PostgreSQL?服务器允许客户机编译sql语句将被重用,避免解析的开销和规划每个执行的语句。这个功能可以在SQL级通过准备和执行从server version 7.3开始,并在协议级别从server version 7.4开始,但作为Java开发人员,我们真的只是想使用标准的PreparedStatement接口。

请注意

以前版本的驱动程序使用的准备和执行来实现server-prepared语句。这是支持所有服务器版本从7.3开始,但生产application-visible查询结果的变化,如丢失的结果集元数据和行更新计数。当前驱动程序使用V3协议一级等价物,避免这些查询结果的变化,但V3协议才可以开始与服务器7.4版。启用server-prepared语句将没有影响当连接到一个7.3服务器或者显式地使用V2协议连接到7.4服务器。

有很多方法使服务器端准备好的语句根据应用程序的需要。一般的方法是设置一个阈值PreparedStatement。内部计数器跟踪语句被执行的次数,当达到阈值,它将开始使用服务器端准备好的语句。

请注意

服务器端准备好的语句由服务器只计划一次。这避免了重新规划查询每次的成本,但也意味着规划者不能利用特定的参数值用于特定查询的执行。你应该谨慎使全球服务器端预处理语句的使用。

Example 9.3. Using server side prepared statements

import java.sql.*;

public class ServerSidePreparedStatement{

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","");
PreparedStatement pstmt = conn.prepareStatement("SELECT ?");
// cast to the pg extension interface org.postgresql.PGStatement pgstmt = pstmt.unwrap(org.postgresql.PGStatement.class);
// on the third execution start using server side statements pgstmt.setPrepareThreshold(3);
for (int i=1;
i<=5;
i++) {
pstmt.setInt(1,i);
boolean usingServerPrepare = pgstmt.isUseServerPrepare();
ResultSet rs = pstmt.executeQuery();
rs.next();
System.out.println("Execution: "+i+", Used server side: " + usingServerPrepare + ", Result: "+rs.getInt(1));
rs.close();
}
pstmt.close();
conn.close();
}

}

Which produces the expected result of using server side prepared statements uponthe third execution.

Execution: 1, Used server side: false, Result: 1
Execution: 2, Used server side: false, Result: 2
Execution: 3, Used server side: true, Result: 3
Execution: 4, Used server side: true, Result: 4
Execution: 5, Used server side: true, Result: 5

The example shown above requires the programmer to use PostgreSQL? specific codein a supposedly portable API which is not ideal. Also it sets the threshold onlyfor that particular statement which is some extra typing if we wanted to use thatthreshold for every statement. Let's take a look at the other ways to set thethreshold to enable server side prepared statements. There is already a hierarchyin place above a PreparedStatement, the Connection it was created from, andabove that the source of the connection be it a Datasource or a URL. The serverside prepared statement threshold can be set at any of these levels such thatthe value will be the default for all of it's children.

// pg extension interfaces
org.postgresql.PGConnection pgconn;
org.postgresql.PGStatement pgstmt;

// set a prepared statement threshold for connections created from this url
String url = "jdbc:postgresql://localhost:5432/test?prepareThreshold=3";

// see that the connection has picked up the correct threshold from the url
Connection conn = DriverManager.getConnection(url,"test","");
pgconn = conn.unwrap(org.postgresql.PGConnection.class);
System.out.println(pgconn.getPrepareThreshold()); // Should be 3

// see that the statement has picked up the correct threshold from the connection
PreparedStatement pstmt = conn.prepareStatement("SELECT ?");
pgstmt = pstmt.unwrap(org.postgresql.PGStatement.class);
System.out.println(pgstmt.getPrepareThreshold()); // Should be 3

// change the connection's threshold and ensure that new statements pick it up
pgconn.setPrepareThreshold(5);
PreparedStatement pstmt = conn.prepareStatement("SELECT ?");
pgstmt = pstmt.unwrap(org.postgresql.PGStatement.class);
System.out.println(pgstmt.getPrepareThreshold()); // Should be 5