将sql查询的输出写入文件

发布于 2024-12-10 04:24:42 字数 812 浏览 0 评论 0原文

我有一个 SQL 查询：

SELECT 'DROP TABLE ' || tablename
  FROM pg_catalog.pg_tables where tablename like 'r_395%';

其输出是这样的：

?column?      
--------------------
 DROP TABLE r_395_0
 DROP TABLE r_395_1
 DROP TABLE r_395_2
 DROP TABLE r_395_3
 DROP TABLE r_395_4
 DROP TABLE r_395_5
 DROP TABLE r_395_6
 DROP TABLE r_395_7
 DROP TABLE r_395_8
 DROP TABLE r_395_9
(10 rows)

我正在使用实体管理器来执行此查询：

StringBuffer query = new StringBuffer();

query.append("SELECT 'DROP TABLE ' || tablename
                FROM pg_catalog.pg_tables where tablename like 'r_395%'");            
entityManager.createNativeQuery(query.toString()).executeUpdate();

我想将此输出写入文件。如果它只是一个文本文件，我会使用文件写入器和缓冲写入器。但现在它是一个 SQL 生成的文件，我有点困惑。有没有办法在Java中实现这一点？

原文

I have an SQL query:

SELECT 'DROP TABLE ' || tablename
  FROM pg_catalog.pg_tables where tablename like 'r_395%';

whose output is this:

?column?      
--------------------
 DROP TABLE r_395_0
 DROP TABLE r_395_1
 DROP TABLE r_395_2
 DROP TABLE r_395_3
 DROP TABLE r_395_4
 DROP TABLE r_395_5
 DROP TABLE r_395_6
 DROP TABLE r_395_7
 DROP TABLE r_395_8
 DROP TABLE r_395_9
(10 rows)

I am using entity manager to execute this query:

StringBuffer query = new StringBuffer();

query.append("SELECT 'DROP TABLE ' || tablename
                FROM pg_catalog.pg_tables where tablename like 'r_395%'");            
entityManager.createNativeQuery(query.toString()).executeUpdate();

I want to write this output to a file. If it was a mere a text file I would use filewriter and buffered writer. But now it is an SQL generated file, I am a little bit confused. Is there a way to achieve this in Java?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

锦上情书 2024-12-17 04:24:42

如果您想在本地（在数据库的计算机上）写入文件，还有非常快速且简单的COPY 对于这样的情况：

COPY $SELECT 'DROP TABLE ' || tablename
         FROM pg_catalog.pg_tables
        WHERE tablename ~~ 'r_395%'$ TO /path/to/myfile;

我使用美元引用，以避免转义单引号。

If you want to write a file locally (on the machine of the database) there is also the very fast and simple COPY for a case like this:

COPY $SELECT 'DROP TABLE ' || tablename
         FROM pg_catalog.pg_tables
        WHERE tablename ~~ 'r_395%'$ TO /path/to/myfile;

I use dollar-quoting here, to avoid escaping the single quotes.

回复收藏 0 原文

橙幽之幻 2024-12-17 04:24:42

与使用 SQL COPY 非常相似的解决方案（请参阅 Erwin Brandstetter 的答案）是使用 psql 元命令 \copy。此处的区别在于您不需要对数据库进行本地访问。 SQL COPY 的输出只能写入数据库框上系统用户 postgres 具有写访问权限的位置。使用 psql \copy 您可以将输出写入客户端上的某个位置。

用psql连接数据库并执行命令

psql -h host -p port -U user database
\copy (SELECT 'DROP TABLE ' || tablename FROM pg_catalog.pg_tables WHERE tablename ~~ 'r_395%') to '/path/to/myfile'

或者将命令直接传递给psql，带-c参数：

psql -h host -p port -U user -c "\copy (SELECT 'DROP TABLE ' || tablename FROM pg_catalog.pg_tables WHERE tablename ~~ 'r_395%') to '/path/to/myfile'" database

HTH

A very similar solution to using SQL COPY (see the answer of Erwin Brandstetter) is to use the psql meta command \copy. The difference here is that you do not need local access on the database box. The output of SQL COPY can only be written to a location on the database box where the system user postgres has write access. With psql \copy you can write the output to a location on the client.

Connect to the database with psql and execute the command

psql -h host -p port -U user database
\copy (SELECT 'DROP TABLE ' || tablename FROM pg_catalog.pg_tables WHERE tablename ~~ 'r_395%') to '/path/to/myfile'

Or pass the command directly to psql with the -c parameter:

psql -h host -p port -U user -c "\copy (SELECT 'DROP TABLE ' || tablename FROM pg_catalog.pg_tables WHERE tablename ~~ 'r_395%') to '/path/to/myfile'" database

HTH

回复收藏 0 原文

~没有更多了~