JDBC capitalizing the table in my postgres query

问题:

I am running the following code

/**
 * @param args
 */
public static void main(String[] args) throws SQLException {
    System.out.println("starting");

    org.postgresql.Driver driver = new org.postgresql.Driver();
    DriverManager.registerDriver(driver);

    Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/epcfe/", "postgres", "aap123!");

    Statement st = con.createStatement();
    st.executeQuery("select * from epcfeschema.PRODUCT");

    System.out.println("done");
}

I keep getting Exception in thread "main" org.postgresql.util.PSQLException: ERROR: relation "epcfeschema.product" does not exist

If I create a table with the lowercase name product this works fine but I need it to work for tables with all caps. How do I keep JDBC from lowercasing my table?

回答1:

If it's a hibernate issue, try this:

@Entity
@Table(name = "\"PRODUCT\"")
public class Product { // ...

Or better yet, make your life easy: log on to postgres and rename the table!

ALTER TABLE "PRODUCT" rename to product;

(Of course, other code may depend on the cap name...)

回答2:

You most probably created your tables using double quotes, e.g:

create table "PRODUCTS" (
  ...
)

This makes the table name case-senstive (as per the SQL Standard), and thus you need to use double quotes each time you acces the table.

select * from epcfeschema."PRODUCT"

and therefor you must use:

executeQuery("select * from epcfeschema.\"PRODUCT\"");

in your Java code (as ChssPly76 has also shown).

I would strongly recommend to re-create the tables without double quotes to make them case-insensitive. Then you never need them and you never have to worry about writing them im UPPER, lower or MixedCase:

When using

create table PRODUCTS (
  ...
)

all of the following statements will work:

select * from Products;
select * from PRODUCTS;
select * from products;

So you can write all of your table names in upppercase if you want.

回答3:

Quote your table names:

st.executeQuery("select * from epcfeschema.\"PRODUCT\"");

回答4:

Postgres (and any other reasonable database) table names are case insensitive (see here). Your problem is somewhere else - permissions, maybe?