#JDBC PostgreSQL

74 messages · Page 1 of 1 (latest)

lethal plume
#

Hello everyone,

I'm struggling with my code. Every time I get the error that a specific relation (a table in a databse) does not exist. I don't know how to fix this. In pgAdmin4 the relation is obviously visible and accessible through SQL. My queries don't seem to have any errors.

I have attached the error trace as well as my code.

I'd be very thankful for any help. Thanks in Advance.

Main.java:

package de.rezeptekompass;

import java.sql.SQLException;
import java.util.List;

import de.rezeptekompass.controller.db.DBConnect;
import de.rezeptekompass.controller.db.VorratsschrankDAO;
import de.rezeptekompass.model.Vorratsschrank;
import javafx.application.Application;
import javafx.stage.Stage;

public class Main extends Application {

    public static void main(String[] args) {
        launch(args);
    }

    @Override
    public void start(Stage primaryStage) {
//        try {

            DBConnect dbConnect = new DBConnect();
            dbConnect.connectToDB("postgres", "password");


            VorratsschrankDAO vorratsschrankDAO = new VorratsschrankDAO(dbConnect);

            List<Vorratsschrank> schraenke;
            System.out.println(schraenke = vorratsschrankDAO.getAllVorratsschraenke());

//            FXMLLoader loader = new FXMLLoader(getClass().getResource("VorratsschrankDoc.fxml"));
//            VorratsschraenkeViewController controller = new VorratsschraenkeViewController(vorratsschrankDAO);
//            loader.setController(controller);
//
//
//            Scene scene = new Scene(loader.load());
//            primaryStage.setTitle("Rezepte-Kompass");
//            primaryStage.setScene(scene);
//            primaryStage.show();
//
// 
//            primaryStage.setOnCloseRequest(event -> dbConnect.closeConnection());
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
    }
}
frosty plinthBOT
#

This post has been reserved for your question.

Hey @lethal plume! Please use /close or the Close Post button above when your problem is solved. Please remember to follow the help guidelines. This post will be automatically closed after 300 minutes of inactivity.

TIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here.

frosty plinthBOT
lethal plume
#

I've attached:
Main Class in my first message,
Error Code,
VorratsschrankDAO (first message.txt),
DBConnect class (where tables are created, etc. --> second message.txt)

whole tulip
#

Have you made sure that the table exists in the correct Database?

lethal plume
#

yeah, i always remove it just to be sure that a new one gets created

whole tulip
#

In the INSERT INTO vorratsschraenke query in the addToDB method, you have the table name as "vorratsschraenke," but in the delete queries (DELETE FROM vorratsschrank), you have "vorratsschrank"

#

Check ur spelling and typos

#

Im not too familiar with SQL

lethal plume
#

Thanks, I changed it

frosty plinthBOT
# lethal plume Thanks, I changed it

If you are finished with your post, please close it.
If you are not, please ignore this message.
Note that you will not be able to send further messages here after this post have been closed but you will be able to create new posts.

lethal plume
whole tulip
#

Does it work now?

lethal plume
#

Nope, it still can't find the vorratsschraenke. The error seems to be in getAllVorratsschraenke()

#
Datenbank rezeptekompass existiert bereits.
Erfolgreich mit der Datenbank verbunden: rezeptekompass
Fehler beim Abrufen aller Vorratsschränke aus der Datenbank
org.postgresql.util.PSQLException: ERROR: relation "vorratsschraenke" does not exist
  Pozycja: 15
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2712)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2400)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:367)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134)
    at de.rezeptekompass.controller.db.VorratsschrankDAO.getAllVorratsschraenke(VorratsschrankDAO.java:94)
    at de.rezeptekompass.Main.start(Main.java:29)
    at javafx.graphics@21.0.1/com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$9(LauncherImpl.java:839)
    at javafx.graphics@21.0.1/com.sun.javafx.application.PlatformImpl.lambda$runAndWait$12(PlatformImpl.java:483)
    at javafx.graphics@21.0.1/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:456)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:400)
    at javafx.graphics@21.0.1/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:455)
    at javafx.graphics@21.0.1/com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
    at javafx.graphics@21.0.1/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at javafx.graphics@21.0.1/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:185)
    at java.base/java.lang.Thread.run(Thread.java:1623)
[]
#

ignore the javafx stuff, that's my 2nd problem I will worry about

#

The connection is passed by an argument, yet it still doesn't work

whole tulip
#

I cant really read german but try getting someone else to help you

lethal plume
#

the rest is english

whole tulip
#

but im not that familiar with sql either :C

lethal plume
#

thanks for trying anyway :>

whole tulip
#

srry bout that

lethal plume
#

even rezeptekompass.vorratsschraenke doesn't work

#

Funny enough, if I remove the database i don't get the error, yet there is no data. When I add some data, I get the error.

alpine anvil
#

ran into this the other day
you need to provide the schema

#

on the entity you do

@Entity
@Table(name = "table name", schema = "schema name"...)
#

on the many to many relationships you have to do it as well

lethal plume
#

so it will suffice if they all have one same schema, yeah?

alpine anvil
#

you have to do it on each entity. I'm not really sure how the schemas work in postgre cuz I've read you can have like 5 schemas and use them at the same time

#

I'm coming from mssql and mysql where you have 1 active schema and that's all (you can have other ones but only the 1 can be active)

lethal plume
#

as i recall, if you don't provide any schema, they all go to the public schema. But either way, public.NAME didn't work either

#

Im gonna try yours and let you know

alpine anvil
#

yeah it does this

#

well maybe your tables aren't in the public schema

#

(I put mine on v1)

#

also make sure your case is correct cuz both hibernate and postgre change it cuz they are stupid

alpine anvil
#

for example if you do SELECT * FROM TableName it would get converted into SELECT * FROM tablename

#

that's by postgre

lethal plume
#

Oh yeah, i avoided that by simply using all lower case

#

thanks

frosty plinthBOT
# lethal plume thanks

If you are finished with your post, please close it.
If you are not, please ignore this message.
Note that you will not be able to send further messages here after this post have been closed but you will be able to create new posts.

alpine anvil
#

hibernate has the same thing in the name of strategies which basically tell it how to modify the query

lethal plume
#

now it doesn't see the new schema boohoo

alpine anvil
#

show me your pgadmin schemas

lethal plume
#

oh it actually didn't create one, wait

alpine anvil
#

oh wait that's plain jdbc?

#

no. spring jdbc?

lethal plume
#

Okay I moved:

String createSchemaQuery = "CREATE SCHEMA IF NOT EXISTS rkompass";
                statement.executeUpdate(createSchemaQuery);

to the setupTables method.

Now the problem:

lethal plume
#

the project isn't that big

alpine anvil
#

the one that's throwing the error

lethal plume
#

I guess, here

statement.executeUpdate(createRecipeTableQuery);
#

But it doesn't make sense

#

Okay, i repaired that error

I added this after schema

String setSchemaQuery = "SET search_path TO rkompass";
statement.execute(setSchemaQuery);

and removed every rkompass. from the table names. The error is now:

#
Datenbank rezeptekompass erfolgreich erstellt.
Tabellen erfolgreich erstellt.
Erfolgreich mit der Datenbank verbunden: rezeptekompass
Fehler beim Hinzufügen des Vorratsschranks in die Datenbank
org.postgresql.util.PSQLException: ERROR: relation "vorratsschraenke" does not exist
  Pozycja: 13
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2712)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2400)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:367)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134)
    at de.rezeptekompass.controller.db.VorratsschrankDAO.addToDB(VorratsschrankDAO.java:32)
    at de.rezeptekompass.Main.start(Main.java:32)
    at javafx.graphics@21.0.1/com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$9(LauncherImpl.java:839)
    at javafx.graphics@21.0.1/com.sun.javafx.application.PlatformImpl.lambda$runAndWait$12(PlatformImpl.java:483)
    at javafx.graphics@21.0.1/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:456)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:400)
    at javafx.graphics@21.0.1/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:455)
    at javafx.graphics@21.0.1/com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
    at javafx.graphics@21.0.1/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at javafx.graphics@21.0.1/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:185)
    at java.base/java.lang.Thread.run(Thread.java:1623)
#

and all tables are under the correct schema now

#

but still, it can't see the damn vorratsschraenke XD

#

Okay got it

#

I changed

String sqlQuery2 = "INSERT INTO vorratsschraenke (standort) VALUES (?) RETURNING id";

to

String sqlQuery2 = "INSERT INTO rkompass.vorratsschraenke (standort) VALUES (?) RETURNING id";
#

now it works 🙂

alpine anvil
#

yeah. Everytime you write a query and need to refer to a table (that isn't in the public schema) you have to give it the schema

lethal plume
#

interestingly enough, I only had to change it there... not in other tables (lebensmittel) is also used

I also need to test if it still works after the setupTables is omitted (when the DB is already created, and that's the case rn)

alpine anvil
#

did you run queries against them?

#

I think it happens only when you try to use the table

lethal plume
#

Okay, after second run it works too (without removing the db), very nice :>

Thanks for all the help

frosty plinthBOT