Thread: Problem using PostgreSQL 9.0.4 with Java

On 7 Září 2011, 22:45, Arun Nadar wrote:
> Hi
>
>
> I am currently using 'PostgreSQL
> 9.0.4' database with JDBC driver 'postgresql-9.0-801.jdbc4'. In my Java
> program normal SELECT query didn't work.
> ie, 
>
> try {
>             Class.forName("org.postgresql.Driver");
>             connection =
> DriverManager.getConnection("jdbc:postgresql://localhost/Student","postgres","postgres");
>             statement = connection.createStatement();
>             String sql="SELECT Id, Name FROM Student ORDER BY Id";     //
> problem
>
>             resultSet = statement.executeQuery(sql);
>             if(resultSet.next()) {
>                 String id=resultSet.getString(1);
>                 String name=resultSet.getString(2);
>             }   
>         } catch (Exception e) {
>             e.printStackTrace();
>         } finally {
>             try {
>    
>              connection.close();
>             } catch (Exception e) {
>                 e.printStackTrace();
>             }
>         }
>
>
> I directly write a query via pgAdmin III the above SELECT query didn't
> work
> { SELECT Id, Name FROM Student ORDER BY Id; }
>
>
> but it work by putting " ",  like this  SELECT "Id", "Name" FROM "Student"
> ORDER BY "Id";
>
> in java String,  inside of double inverted commas another is does not
> possible.
>
>
> how this code is implement through java program. please kindly send me the
> solution for this.

First of all, this has nothing to do with Java - this is caused by quoting
the identifiers when creating the table. I.e. you've created the table
like this:

CREATE TABLE "Student" (
  "Id" ...
  "Name" ...
);

in that case you have to quote the identifiers every time you use them. I
generally don't recommend it, in my experience it makes the db difficult
to use and error prone.

CREATE TABLE student (
  Id ...
  Name ...
);

and then you don't need the quotes at all.

But if you really need to use the quotes (e.g. if you can't change the
schema), then you can escape them in the query string, e.g. like this:

String sql="SELECT \"Id\", \"Name\" FROM \"Student\" ORDER BY \"Id\"";

regards
Tomas

On 09/07/11 1:45 PM, Arun Nadar wrote:
>
> but it work by putting " ",  like this SELECT "Id", "Name" FROM
> "Student" ORDER BY "Id";
>
> in java String,  inside of double inverted commas another is does not
> possible.


String sql="SELECT \"Id\", \"Name\" FROM \"Student\" ORDER BY \"Id\"";

Alternately, create your table with all lower case names, and you won't
have this problem.

     alter table "Student" rename to student;
     alter table student rename column "Id" to id;
     alter table student rename column "Name" to name;



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast