Java JDBC Connection with Heroku

问题:

I'm trying to create a Java Web App on Heroku. When connecting the App to Heroku Database I encountered a Issue: org.postgresql.util.PSQLException: ERROR: relation "sc_user" does not exist

The connection part is:

URI dbUri = new URI(System.getenv("DATABASE_URL"));
String dbUrl = "jdbc:postgresql://" + dbUri.getHost() + dbUri.getPath();

BasicDataSource connectionPool = new BasicDataSource();

if (dbUri.getUserInfo() != null) {
    connectionPool.setUsername(dbUri.getUserInfo().split(":")[0]);
    connectionPool.setPassword(dbUri.getUserInfo().split(":")[1]);
}
connectionPool.setDriverClassName("org.postgresql.Driver");
connectionPool.setUrl(dbUrl);
connectionPool.setInitialSize(3);

Connection conn = connectionPool.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM SC_User");

I see on Heroku Dashboard that 3 connections are used from pool, but the last line throws exception saying no relation sc_user.

I created the table with add-on tool Adminium, is there something i'm missing in the commands? Thanks for your help!

回答1:

When you create a column in postgres with Upper Letter the DBMS use it between "SC_User" si in case your column is in upper Letter you have to use :

stmt.executeQuery("SELECT * FROM \"SC_User\""); 
//--------------------------------^--------^

else you have to use the correct name with lowercase

stmt.executeQuery("SELECT * FROM sc_user");
//---------------------------------^^

---

read more about that here : Cannot query Postgres database that has column names with capital letters

回答2:

Try delimiting the table name with quotations

ResultSet rs = stmt.executeQuery("SELECT * FROM \"SC_User\"");