Postgres: syntax, identifiers, instructions [duplicate]

Question

I am trying to understand whether there is an instruction, like the MySQL's USE, to choose a database, once created from Java.

I would like at first to access the dbms via the url jdbc:postgresql://localhost:5432/ (without database), so I would insert an instruction to create the db, and so another instruction to use/choose it.

I am building a function that is supposed to connect to dbms, create a database, use it, and finally delete it (as I need temporary data: I tried H2 In Memory but it didn't work in my specific application).

I have been able to realize such a behavior with MySQL, but unluckily I cannot use MySQL; my code for this was:

manager.setJDBCDriver("com.mysql.jdbc.Driver");
manager.setJDBCUsername("root");
manager.setJDBCPassword("mypass");
manager.setJDBCUrl("jdbc:mysql://localhost/");
manager.createConnection();
manager.executeUpdate("CREATE DATABASE " + database);
manager.executeUpdate("USE " + database);

Where I will put the database deletion query in a module calling this latter code.

I would like to do so even with PostgreSQL.

Mark Rotteveel · Accepted Answer · 2017-10-24 08:40:23Z

0

You shouldn't use use when using JDBC, instead you should be using setCatalog and setSchema (which you need to use depends on the database, driver and type of object).

For MySQL the equivalent of use is setCatalog, see Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J:

Always use the Connection.setCatalog() method to specify the desired database in JDBC applications, rather than the USE database statement.

Unfortunately, the PostgreSQL JDBC driver doesn't support switching to another database. You will need to disconnect and create a new connection.

answered Oct 24, 2017 at 8:40

Mark Rotteveel

110k241 gold badges160 silver badges233 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tommaso Over a year ago

hello! commands never seen before, now i will have a look.. thanks

tommaso Over a year ago

by the way, maybe i don't have to switch, but to create and destroy, in each of my application execution, the same db: after the execution of 'manager.executeUpdate("CREATE DATABASE " + database)', what is the command to do as you have said, by connecting to the newly created db and immediately disconnecting when no more utilizable? sorry if i don't get the point, i am not so good with english

Collectives™ on Stack Overflow

Postgres: syntax, identifiers, instructions [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related