GaussDB(DWS)数据入库方式主要有三种:INSERT/COPY/FDW,三者性能INSERT最差,FDW(gds外表/obs外表)性能最好,但在很多客户场景下都是流式数据,不额外落盘,需要直接入库,这种场景下可以采用copy方式入库,Java作为编程语言,广泛应用于互联网应用中,Java访问数据库使用的是JDBC接口,支持copymanager,示例程序如下:
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
import java.io.StringReader;
public class copy_test {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e) {
System.out.println("can not find Driver");
e.printStackTrace();
}
String url = "jdbc:postgresql://10.x.x.x:8000/postgres";
Properties props = new Properties();
props.setProperty("user", "jack");
props.setProperty("password", "Gauss_234");
try {
Connection con = DriverManager.getConnection(url, props);
con.setAutoCommit(false);
CopyManager cm = null;
StringReader sr = null;
String quote = "$";
String delimter = "|";
String sql = "copy test from STDIN with (format 'CSV', delimiter '|', quote '$')";
System.out.println(sql);
cm = new CopyManager((BaseConnection) con);
StringBuffer tuples = new StringBuffer();
for (int j = 0; j < 3; j++) {
tuples.append(quote + "1" + quote + delimter + quote + "a" + quote + "\n");
}
sr = new StringReader(tuples.toString());
long rows = cm.copyIn(sql, sr);// 执行copy入库
con.commit();// 提交
con.close();
} catch (Exception se) {
se.printStackTrace();
}
}
}