copy in
postgres 导入数据的时候可以使用 cpoy 命令进行数据导入, 如果使用 java 操作 copy 命令则需要使用 postgres 提供的 jdbc 驱动中的 CopyManager 来实现
封装一下
- public class PGCopyInUtils {
- /**
- * 将表中的数据导出到本地文件
- *
- * @param connection 连接
- * @param filePath 文件路径
- * @param tableOrQuery 表名 或者查询语句
- * @throws SQLException SQLException
- * @throws IOException IOException
- */
- public static void copyToFile(Connection connection, String filePath, String tableOrQuery)
- throws SQLException, IOException {
- FileOutputStream fileOutputStream = null;
- try {
- CopyManager copyManager = new CopyManager((BaseConnection) connection);
- fileOutputStream = new FileOutputStream(filePath);
- String copyOut = "COPY" + tableOrQuery + "TO STDOUT DELIMITER AS','";
- final long line = copyManager.copyOut(copyOut, fileOutputStream);
- System.out.println(line);
- } finally {
- if (fileOutputStream != null) {
- try {
- fileOutputStream.close();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- }
- }
- /**
- * 将文件中的数据导入到数据库中
- *
- * @param connection 连接
- * @param filePath 文件路径
- * @param tableName 表名
- * @throws SQLException SQLException
- * @throws IOException IOException
- * @return long 导入的行数
- */
- public static long copyFromFile(Connection connection, String filePath, String tableName)
- throws SQLException, IOException {
- FileInputStream fileInputStream = null;
- try {
- CopyManager copyManager = new CopyManager((BaseConnection) connection);
- fileInputStream = new FileInputStream(filePath);
- String copyIn = "COPY" + tableName + "FROM STDIN DELIMITER AS','";
- return copyManager.copyIn(copyIn, fileInputStream);
- } finally {
- if (fileInputStream != null) {
- try {
- fileInputStream.close();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- }
- }
- }
使用
1. connection 数据库连接
2. 文件路径
3. 表名
PGCopyInUtils.copyFromFile(connection, Constants.COPY_DEVICE_DATA_FILE_PATH, Constants.TABLE_NAME);
注意
以上文件内容的分隔符为 "," 标准输入.
来源: http://blog.csdn.net/u014087707/article/details/79334934