Postgresql – How to implement a dynamic string into a (prepared) sql statement

dynamic-sqljavapostgresql

I have some spatial datasets which are available in a open source metadata catalog software GeoNetwork and in a PostGIS database. The problem is that the student who created the database used different IDs for the same datasets. So the IDs in the GeoNetwork differ from the IDs in the PostGIS database. They only have the same name.

If someone uploads a new dataset into GeoNetwork, the same dataset is uploaded into a PostGis database, too. I want to add a ID column into the database to give the field the same ID like the ID in GeoNetwork. I think I can only do this with using the filename cause they are the same in both cases.

I want to alter the table in eclipse with the following code: 

  Connection con = null;
            PreparedStatement ps = null;
            final String sqlps = "ALTER TABLE ? ADD COLUMN ?";

            String filen = filename.substring(0, filename.indexOf('.'));
            try {
                con = DriverManager.getConnection("jdbc:postgresql_postGIS://localhost:5433/geonetwork?user=postgres&password=dominik1");
                try {
                    ps = con.prepareStatement(sqlps);
                    ps.setString(1, filen);
                    ps.setString(2, "GN_ID");

                    ps.execute();

But I get an error:

Exception:org.postgresql.util.PSQLException: ERROR: syntax error at »$1«
  Position: 13

It seems that Postgres has problems with the ? in the prepared statement.
Does anyone know a solution for this problem?

I also tried using a normal statement, but this is giving me a findbug error that I try to pass a non-constant string to an execute method on an sql statement. So I really don't know how to get rid of this problem.

Best Answer

The bad news: Prepared statements do not work like that. Parameters can only be data, not code or identifiers.

The good news: you do not need parameters for a ALTER TABLE statement at all.

final String sqlps = "ALTER TABLE filen ADD COLUMN gn_id integer";

...
    ps = con.prepareStatement(sqlps);
    ps.execute();

  • Your column needs a data type. I added integer on suspicion. Possibly add a NOT NULL constraint after that.

  • Friendly advice: do not use quoted upper-case column names in Postgres if you can avoid it. Use legal, lower case, unquoted names. Makes your life easier.

Related Question