Postgres CopyManager and connection from Connection Pool
1. PG CopyManager的使用示例代码:
package test.simple;
//You need to include postgres jdbc jar into your project lib
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
import java.io.File;
/**
* Created with IntelliJ IDEA.
* User: leon
* Date: 13-2-5
* Time: 下午12:18
* To change this template use File | Settings | File Templates.
*/
public class TestCopy {
public static void main(String args[])throws Exception{
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://localhost:5432/postgres";
Properties props = new Properties();
props.setProperty("user", "postgres");
props.setProperty("password", "postgres");
Connection conn = DriverManager.getConnection(url, props);
CopyManager cm = new CopyManager((BaseConnection)conn);
File file = new File("a.txt");
if(!file.exists()){
file.createNewFile();
}
/*FileWriter fw = new FileWriter(file);
cm.copyOut("COPY test_delete TO STDOUT WITH DELIMITER AS '|'", fw);
fw.close();*/
FileInputStream fis = new FileInputStream(file);
cm.copyIn("COPY test_delete FROM STDIN WITH DELIMITER AS '|'", fis);
fis.close();
}
}
非常简单,将表中数据导出到txt文件中,可以自己增加一步压缩导出的数据文件,可以大大减少文件所占磁盘空间。
2. connection from Connection Pool
但遗憾的是CopyManager是PG的特有功能,使用CopyManager的话,必须从BaseConnection(就是PG的Connection)中获取。而实际项目中通常会使用到数据库连接池。
研究了一下,从PG连接池取到的connection无法强制转化成BaseConnection。
看下简单的PG 连接池代码,并将其中的一个connection转化成BaseConnection时的错误:
import java.sql.Connection;
import org.postgresql.core.BaseConnection;
import org.postgresql.ds.PGPoolingDataSource;
public class PGPool {
PGPoolingDataSource source = new PGPoolingDataSource();
public PGPool(){
source.setServerName("localhost");
source.setPortNumber(5432);
source.setDatabaseName("postgres");
source.setUser("postgres");
source.setPassword("postgres");
source.setMaxConnections(10);
}
public Connection getConn(){
try{
return source.getConnection();
}catch (Exception e){
e.printStackTrace();
}
return null;
}
public static void main(String args[]){
PGPool pool = new PGPool();
Connection con = pool.getConn();
BaseConnection bConn = (BaseConnection) con;
}
}
Exception in thread "main" java.lang.ClassCastException: $Proxy0 cannot be cast to org.postgresql.core.BaseConnection
at PGPool.main(PGPool.java:35)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
完整的代码样例如下(从连接池中获取一个连接,连进行PG的CopyManager操作):
/**
* Created with IntelliJ IDEA.
* User: leon
* Date: 13-2-12
* Time: 下午8:20
* To change this template use File | Settings | File Templates.
*/
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
//Remember, You need to include PG jdbc jar into your project build path.
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
import org.postgresql.ds.PGPoolingDataSource;
public class PGPool {
PGPoolingDataSource source = new PGPoolingDataSource();
public PGPool(){
//PG database server name
source.setServerName("localhost");
//PG db port number
source.setPortNumber(5432);
//PG database name
source.setDatabaseName("postgres");
source.setUser("postgres");
source.setPassword("postgres");
source.setMaxConnections(10);
}
public Connection getConn(){
try{
return source.getConnection();
}catch (Exception e){
e.printStackTrace();
}
return null;
}
public static void main(String args[]) throws Exception{
PGPool pool = new PGPool();
Connection con = pool.getConn();
CopyManager cm = new CopyManager((BaseConnection)con.getMetaData().getConnection());
File file = new File("a.txt");
if(!file.exists()){
file.createNewFile();
}
FileInputStream fis = new FileInputStream(file);
//According to our simple example, your should have a table named test_delete in your database
cm.copyIn("COPY test_delete FROM STDIN WITH DELIMITER AS '|'", fis);
fis.close();
}
}
还没有评论,来说两句吧...