Java SQL “ERROR: Relation ”Table_Name“ does not ex

问题:

I'm trying to connect netbeans to my postgresql database. The connection seems to have worked as I don't get any errors or exceptions when just connecting, methods such as getCatalog() also return the correct answers.

But when I try to run a simple SQL statement I get the error "ERROR: relation "TABLE_NAME" does not exist", where TABLE_NAME is any one of my tables which DO exist in the database. Here's my code:

    Statement stmt = con.createStatement();

    ResultSet rs;

    String query = "SELECT * FROM clients";

    rs = stmt.executeQuery(query);

I was thinking that netbeans might not be finding the tables because it's not looking in the default schema (public), is there a way of setting the schema in java?

EDIT: My connection code. The database name is Cinemax, when I leave out the statement code, I get no errors.

    String url = "jdbc:postgresql://localhost:5432/Cinemax";
    try{

    try {
        Class.forName("org.postgresql.Driver");
    } catch (ClassNotFoundException cnfe) {
        System.err.println("Couldn't find driver class:");
        cnfe.printStackTrace();
    }

    Connection con = DriverManager.getConnection( url,"postgres","desertrose147");

回答1:

I suspect you created the table using double quotes using e.g. "Clients" or some other combination of upper/lowercase characters and therefor the table name is case sensitive now.

What does the statement

 SELECT table_schema, table_name
 FROM information_schema.tables 
 WHERE lower(table_name) = 'clients'

return?

If the table name that is returned is not lowercase you have to use double quotes when referring to it, something like this:

String query = "SELECT * FROM \"Clients\"";

回答2:

You could check these possibilities:

String query = "SELECT * FROM clients";
String query = "SELECT * FROM CLIENTS";
String query = "SELECT * FROM \"clients\"";
String query = "SELECT * FROM \"CLIENTS\"";
String query = "SELECT * FROM Clients";

Maybe one of those would work.

回答3:

Besides CoolBeans' suggestion, you may also be connecting to the db as a different user who does not have permission on the relevant db or schema. Can you show the connection string?

回答4:

Funny thing is i was experiencing the same thing as i had just started on netbeans and postgressql db, and the error was fixed after noting that the issue was that my tables in postgressql had capital letters in my naming convention which me and my jdbc query statement for INSERT was failing to find the table. But after renaming my tables in the db and fixing the column names as well am good to go. Hope it helps.