Java library to generate Postgres SQL Script from DB [closed]

Question

Closed. This question is seeking recommendations for software libraries, tutorials, tools, books, or other off-site resources. It does not meet Stack Overflow guidelines. It is not currently accepting answers.

We don’t allow questions seeking recommendations for software libraries, tutorials, tools, books, or other off-site resources. You can edit the question so it can be answered with facts and citations.

Closed 7 years ago.

Improve this question

Good day, everybody!

Straight to the point. Is there any java library to generate Postgres SQL Script from DB?

For example i have table job_table

   name    | job
   Andy    | pull
   Benny   | push

Then, i wanna get the String sql-script like this

String sqlInsert = 

  "INSERT INTO job_table(name, job) VALUES ('Andy', 'pull');"+
  "INSERT INTO job_table(name, job) VALUES ('Benny', 'push');";

Or something kind like that.

Anybody know?

Nb: I have wrote my own code in java to generate it manually. But i just don't like it. :)

Thanks before.

Is there a reason you can't use the pg_dump utility to back up the database directly? — Daniel Lyons
– Daniel Lyons, Commented Apr 23, 2012 at 5:19
yes, i wanna use that String for my webservice so that the client can get it and execute it directly. I know we can give the client with other format, example:json or xml, but for some reason i must give that String to my client — ilyasfals
– ilyasfals, Commented Apr 23, 2012 at 7:24
Will COPY via JDBC do (see answer) or do you strictly need INSERTs? If so, there's no easy alternative to pg_dump at the moment, you'd probably have to shell out to "pg_dump --data-only --table=sometable" and read its stdout. — Craig Ringer
– Craig Ringer, Commented Apr 25, 2012 at 22:03

Craig Ringer · Accepted Answer · 2012-04-25 22:10:06Z

Use the PgJDBC support for the COPY command to feed tab-delimited or CSV data into the database, and to extract it in the same format. You want the CopyManager, which can be obtained from a PgConnection.

The CopyManager is rather woefully under-documented at present, but it's just a wrapper around the COPY command.

Get access to the PgConnection interface by casting your java.sql.Connection to PgConnection. If the connection is wrapped by a connection pooling service you may have to unwrap it to get the real underlying connection returned by PgJDBC. Same deal if you're using JPA, you'll have to get access to the real PgJDBC connection or make a new one.

If you actually need INSERT statements, rather than an efficient way of delivering data to the database, what are your specific needs? Portability? If so, how will you handle things like the different representations of NUMERIC / DECIMAL / whatever?

If you only need to handle the "standard" data types, your hand rolled code may be the best way.

The only strictly safe way to do that is currently via pg_dump. You can invoke a pg_dump --data-only --table=thetable process and read from its stdout to do the job, though this does require (a) direct knowledge of the DB details in the app, not just a DataSource or injected entity manager from a container; (b) knowledge of the pg_dump path, and (c) the security permission to execute external processes. Unfortunately there isn't really any way around that at present. pg_dump isn't designed to be used as a library (though there's been talk of converting it) and even if it were, it'd be a C library you'd have to wrap via JNI or similar. Ick.

Collectives™ on Stack Overflow

Java library to generate Postgres SQL Script from DB [closed]

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related