Postgres with Java I can't insert data

问题:

I tried to make an insert to my postgres database with Java. I have default configuration for my local database.

I want to put some data in a table and I have some issues.

Here is the code :

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

public static void main(String[] args) {

    Connection con = null;
    PreparedStatement pst = null;

    String url = "jdbc:postgresql://localhost/postgres";
    String user = "postgres";
    String password = "thanassis";

    try {


        con = DriverManager.getConnection(url, user, password);

        String stm = "INSERT INTO TEST2(ID) VALUES(?)";
        pst = con.prepareStatement(stm);
        pst.setInt(1, 1);

        pst.executeUpdate(); 

    } catch (SQLException ex) {
        Logger lgr = Logger.getLogger(PreparedStatement.class.getName());
        lgr.log(Level.SEVERE, ex.getMessage(), ex);

    } finally {

        try {
            if (pst != null) {
                pst.close();
            }
            if (con != null) {
                con.close();
            }

        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(PreparedStatement.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }
    }
}

And here the exceptions

SEVERE: ERROR: relation "test2" does not exist
  Position: 13
org.postgresql.util.PSQLException: ERROR: relation "test2" does not exist
  Position: 13
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1834)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:510)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:386)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:332)
    at test.Test.main(Test.java:30)

回答1:

Your table is called TEST2 not test2. Apparently you created it using double quotes, which makes Postgres (and other standard-compliant DBMS) case-sensitive.

Because of that you now have to enclose the table in name in double quotes each time you refer to it.

String stm = "INSERT INTO \"TEST2\"(ID) VALUES(?)";

Most probably this is not what you intended, so just re-recreate the table without using double quotes around the identifiers:

CREATE TABLE test2
(
  ...
)

create a different table than:

CREATE TABLE "test2"
(
  ...
)

If you do not want to re-create the tables, you can rename them:

alter table "TEST2" rename to test2;

回答2:

Table test2 doesn't exist. Try to login to PostgreSQL and check this table.

You can list all existing tables in the database with command line utility

psql -d postgres
\dt

回答3:

Check for the schema in which you create the table test2. If it is one of the schemas not in the search path, then you have two options:-

• Add the schema to your search_path(comma separated list in the file postgres.conf)
• Refer to the table by prepending schema name and dot to the table name. For example if the concerned table test2 is in my_schema, then your query should be
  insert into my_schema.test2(ID) values(?).