更新日期：2024年09月11日
使用CREATE TABLE 执行深层复制
该方法使用CREATE TABLE 语句创建原始表的副本，将原始表的数据填充至副本并重
命名副本，以完成原始表的复制。
在创建新表时，可以指定表以及列属性，比如主键。
操作步骤
执行以下步骤对表customer_t 进行深层复制。
步骤1 使用CREATE TABLE 语句创建表customer_t 的副本customer_t_copy。
postgres=# CREATE TABLE customer_t_copy
( c_customer_sk
integer,
c_customer_id
char(5),
c_first_name char(6),
c_last_name char(8)
) ;
步骤2 使用INSERT INTO…SELECT 语句向副本填充原始表中的数据。
postgres=# INSERT INTO customer_t_copy (SELECT * FROM customer_t);
步骤3 删除原始表。
postgres=# DROP TABLE customer_t;
步骤4 使用ALTER TABLE 语句将副本重命名为原始表名称。
postgres=# ALTER TABLE customer_t_copy RENAME TO customer_t;
----结束
以下示例显示应用程序如何使用 UDTManager 和 UDTMetaData 类将客户端上
（数据库服务
器无法访问）的现有 Java™ 类转换为数据库服务器中的 SQL 不透明类型。
使用缺省的支持函数创建不透明类型
以下示例在使用数据库服务器中现有的 Java™ 类和缺省的支持函数创建不透明类型 Circle：
*/
import java.sql.*;
import com.gbasedbt.jdbc.IfmxUDTSQLInput;
import com.gbasedbt.jdbc.IfmxUDTSQLOutput;
GBase 8s JDBC Driver 程序员指南
南大通用数据技术股份有限公司
- 197
-
public class Circle implements SQLData
{
private static double PI = 3.14159;
double x; // x coordinate
double y; // y coordinate
double radius;
private String type = "circle";
public String getSQLTypeName() { return type; }
public void readSQL(SQLInput stream, String typeName)
throws SQLException
{
// To be able to use the DEFAULT support functions supplied
// by the server, you must cast the stream to IfmxUDTSQLInput.
// (Server requirement)
IfmxUDTSQLInput in = (IfmxUDTSQLInput) stream;
x = in.readDouble();
y = in.readDouble();
radius = in.readDouble();
}
public void writeSQL(SQLOutput stream) throws SQLException
{
// To be able to use the DEFAULT support functions supplied
// by the server, have to cast the stream to IfmxUDTSQLOutput.
// (Server requirement)
IfmxUDTSQLOutput out = (IfmxUDTSQLOutput) stream;
out.writeDouble(x);
out.writeDouble(y);
out.writeDouble(radius);
}
GBase 8s JDBC Driver 程序员指南
南大通用数据技术股份有限公司
- 198
-
public static double area(Circle c)
{
return PI * c.radius * c.radius;
}
}
不透明类型
下列 JDBC 客户端应用程序将类 Circle（在 Circle.jar 中打包）作为不透明类型安装在系统
目录中。然后，应用程序可以在 SQL 语句中使用不透明类型 Circle 作为数据类型：
import java.sql.*;
import java.lang.reflect.*;
public class PlayWithCircle
{
String dbname = "test";
String url = null;
Connection conn = null;
public static void main (String args[])
{
new PlayWithCircle(args);
}
PlayWithCircle(String args[])
{
System.out.println("----------------");
System.out.println("- Start - Demo 1");
System.out.println("----------------");
// -----------
// Getting URL
// -----------
if (args.length == 0)
{
GBase 8s JDBC Driver 程序员指南
南大通用数据技术股份有限公司
- 199
-
System.out.println("\n***ERROR: connection URL must be provided "
+
"in order to run the demo!");
return;
}
url = args[0];
// --------------
// Loading driver
// --------------
try
{
System.out.print("Loading JDBC driver...");
Class.forName("com.gbasedbt.jdbc.IfxDriver");
System.out.println("ok");
}
catch (java.lang.ClassNotFoundException e)
{
System.out.println("\n***ERROR: " + e.getMessage());
e.printStackTrace();
return;
}
// ------------------
// Getting connection
// ------------------
try
{
System.out.print("Getting connection...");
conn = DriverManager.getConnection(url);
System.out.println("ok");
}
catch (SQLException e)
{
System.out.println("URL = '" + url + "'");
System.out.println("\n***ERROR: " + e.getMessage());
e.printStackTrace();
GBase 8s JDBC Driver 程序员指南
南大通用数据技术股份有限公司
- 200
-
return;
}
System.out.println();
// -------------------
// Setup UDT meta data
// -------------------
Method areamethod = null;
try
{
Class c = Class.forName("Circle");
areamethod = c.getMethod("area", new Class[] {c});
}
catch (ClassNotFoundException e)
{
System.out.println("Cannot get Class: " + e.toString());
return;
}
catch (NoSuchMethodException e)
{
System.out.println("Cannot get Method: " + e.toString());
return;
}
UDTMetaData mdata = null;
try
{
System.out.print("Setting mdata...");
mdata = new UDTMetaData();
mdata.setSQLName("circle");
mdata.setLength(24);
mdata.setAlignment(UDTMetaData.EIGHT_BYTE);
mdata.setUDR(areamethod, "area");
mdata.setJarFileSQLName("circle_jar");
System.out.println("ok");
}
catch (SQLException e)
GBase 8s JDBC Driver 程序员指南
南大通用数据技术股份有限公司
- 201
-
{
System.out.println("\n***ERROR: " + e.getMessage());
return;
}
// -------------------------------
// Install the UDT in the database
// -------------------------------
UDTManager udtmgr = null;
try
{
udtmgr = new UDTManager(conn);
System.out.println("\ncreateJar()");
String jarfilename = udtmgr.createJar(mdata,
new String[] {"Circle.class"}); // jarfilename = circle.jar
System.out.println(" jarfilename = " + jarfilename);
System.out.println("\nsetJarTmpPath()");
udtmgr.setJarTmpPath("/tmp");
System.out.print("\ncreateUDT()...");
udtmgr.createUDT(mdata,
"/vobs/jdbc/demo/tools/udtudrmgr/" + jarfilename, "Circle", 0);
System.out.println("ok");
}
catch (SQLException e)
{
System.out.println("\n***ERROR: " + e.getMessage());
return;
}
System.out.println();
// ---------------
// Now use the UDT
// ---------------
try
GBase 8s JDBC Driver 程序员指南
南大通用数据技术股份有限公司
- 202
-
{
String s = "drop table tab";
System.out.print(s + "...");
Statement stmt = conn.createStatement();
int count = stmt.executeUpdate(s);
stmt.close();
System.out.println("ok");
}
catch ( SQLException e)
{
// -206 The specified table (%s) is not in the database.
if (e.getErrorCode() != -206)
{
System.out.println("\n***ERROR: " + e.getMessage());
return;
}
System.out.println("ok");
}
executeUpdate("create table tab (c circle)");
// test DEFAULT Input function
executeUpdate("insert into tab values ('10 10 10')");
// test DEFAULT Output function
try
{
String s = "select c::lvarchar from tab";
System.out.println(s);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(s);
if (rs.next())
{
String c = rs.getString(1);
System.out.println(" circle = '" + c + "'");
}
rs.close();
GBase 8s JDBC Driver 程序员指南
南大通用数据技术股份有限公司
- 203
-
stmt.close();
}
catch (SQLException e)
{
System.out.println("***ERROR: " + e.getMessage());
}
System.out.println();
// test DEFAULT Send function
try
{
// setup type map before using getObject() for UDT data.
java.util.Map customtypemap = conn.getTypeMap();
System.out.println("getTypeMap...ok");
if (customtypemap == null)
{
System.out.println("***ERROR: map is null!");
return;
}
customtypemap.put("circle", Class.forName("Circle"));
System.out.println("put...ok");
String s = "select c from tab";
System.out.println(s);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(s);
if (rs.next())
{
Circle c = (Circle)rs.getObject(1, customtypemap);
System.out.println(" c.x = " + c.x);
System.out.println(" c.y = " + c.y);
System.out.println(" c.radius = " + c.radius);
}
rs.close();
stmt.close();
}
catch (SQLException e)
GBase 8s JDBC Driver 程序员指南
南大通用数据技术股份有限公司
- 204
-
{
System.out.println("***ERROR: " + e.getMessage());
}
catch (ClassNotFoundException e)
{
System.out.println("***ERROR: " + e.getMessage());
}
System.out.println();
// test user's non-support UDR
try
{
String s = "select area(c) from tab";
System.out.println(s);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(s);
if (rs.next())
{
double a = rs.getDouble(1);
System.out.println(" area = " + a);
}
rs.close();
stmt.close();
}
catch (SQLException e)
{
System.out.println("***ERROR: " + e.getMessage());
}
System.out.println();
executeUpdate("drop table tab");
// ------------------
// Closing connection
// ------------------
try
{
GBase 8s JDBC Driver 程序员指南
南大通用数据技术股份有限公司
- 205
-
System.out.print("Closing connection...");
conn.close();
System.out.println("ok");
}
catch (SQLException e)
{
System.out.println("\n***ERROR: " + e.getMessage());
}
}
使用您提供的支持函数创建不透明类型
在此示例中，
客户端上的 Java™ 类 Circle2 被映射到一个名为 circle2 的 SQL 不透明类型。
circle2 不透明类型使用程序员提供的支持函数：
import java.text.*;
import com.gbasedbt.jdbc.IfmxUDTSQLInput;
import com.gbasedbt.jdbc.IfmxUDTSQLOutput;
public class Circle2 implements SQLData
{
private static double PI = 3.14159;
double x; // x coordinate
double y; // y coordinate
double radius;
private String type = "circle2";
public String getSQLTypeName() { return type; }
public void readSQL(SQLInput stream, String typeName)
throws SQLException
{
/* commented out - because the first release of the UDT/UDR Manager feature
* does not support mixing user-supplied support functions
* with server DEFAULT support functions.
* However, once the mix is supported, this code needs to be used to
* replace the existing code.
GBase 8s JDBC Driver 程序员指南
南大通用数据技术股份有限公司
- 206
-
*
// To be able to use the DEFAULT support functions (other than
// Input/Output) supplied by the server, you must cast the stream
// to IfmxUDTSQLInput.
IfmxUDTSQLInput in = (IfmxUDTSQLInput) stream;
x = in.readDouble();
y = in.readDouble();
radius = in.readDouble();
*/
x = stream.readDouble();
y = stream.readDouble();
radius = stream.readDouble();
}
public void writeSQL(SQLOutput stream) throws SQLException
{
/* commented out - because the 1st release of UDT/UDR Manager feature
* doesn't support the mixing of user support functions
* with server DEFAULT support functions.
* However, once the mix is supported, this code needs to be used to
* replace the existing code.
*
// To be able to use the DEFAULT support functions (other than
// Input/Output) supplied by the server, you must cast the stream
// to IfmxUDTSQLOutput.
IfmxUDTSQLOutput out = (IfmxUDTSQLOutput) stream;
out.writeDouble(x);
out.writeDouble(y);
out.writeDouble(radius);
*/
stream.writeDouble(x);
stream.writeDouble(y);
stream.writeDouble(radius);
GBase 8s JDBC Driver 程序员指南
南大通用数据技术股份有限公司
- 207
-
}
/**
* Input function - return the object from the String representation -
* 'x y radius'.
*/
public static Circle2 fromString(String text)
{
Number a = null;
Number b = null;
Number r = null;
try
{
ParsePosition ps = new ParsePosition(0);
a = NumberFormat.getInstance().parse(text, ps);
ps.setIndex(ps.getIndex() + 1);
b = NumberFormat.getInstance().parse(text, ps);
ps.setIndex(ps.getIndex() + 1);
r = NumberFormat.getInstance().parse(text, ps);
}
catch (Exception e)
{
System.out.println("In exception : " + e.getMessage());
}
Circle2 c = new Circle2();
c.x = a.doubleValue();
c.y = b.doubleValue();
c.radius = r.doubleValue();
return c;
}
/**
* Output function - return the string of the form 'x y radius'.
*/
GBase 8s JDBC Driver 程序员指南
南大通用数据技术股份有限公司
- 208
-
public static String makeString(Circle2 c)
{
StringBuffer sbuff = new StringBuffer();
FieldPosition fp = new FieldPosition(NumberFormat.INTEGER_FIELD);
NumberFormat.getInstance().format(c.x, sbuff, fp);
sbuff.append(" ");
NumberFormat.getInstance().format(c.y, sbuff, fp);
sbuff.append(" ");
NumberFormat.getInstance().format(c.radius, sbuff, fp);
return sbuff.toString();
}
/**
* user function - get the area of a circle.
*/
public static double area(Circle2 c)
{
return PI * c.radius * c.radius;
}
}
不透明类型
下列 JDBC 客户端应用程序将类 Circle2（在 Circle2.jar 中打包）作为不透明类型安装在系
统目录中。然后，应用程序可以在 SQL 语句中使用不透明类型 Circle2 作为数据类型：
import java.sql.*;
import java.lang.reflect.*;
public class PlayWithCircle2
GBase 8s JDBC Driver 程序员指南
南大通用数据技术股份有限公司
- 209
-
{
String dbname = "test";
String url = null;
Connection conn = null;
public static void main (String args[])
{
new PlayWithCircle2(args);
}
PlayWithCircle2(String args[])
{
// -----------
// Getting URL
// -----------
if (args.length == 0)
{
GBase 8s JDBC Driver 程序员指南
南大通用数据技术股份有限公司
- 210
-
System.out.println("\n***ERROR: connection URL must be provided " +
"in order to run the demo!");
return;
}
url = args[0];
// --------------
// Loading driver
// --------------
try
{
System.out.print("Loading JDBC driver...");
Class.forName("com.gbasedbt.jdbc.IfxDriver");
}
catch (java.lang.ClassNotFoundException e)
{
System.out.println("\n***ERROR: " + e.getMessage());
GBase 8s JDBC Driver 程序员指南
南大通用数据技术股份有限公司
- 211
-
e.printStackTrace();
return;
}
try
{
conn = DriverManager.getConnection(url);
}
catch (SQLException e)
{
System.out.println("URL = '" + url + "'");
System.out.println("\n***ERROR: " + e.getMessage());
e.printStackTrace();
return;
}
System.out.println();