Java JDBC Postgres: Select with single quote

Question

I've been testing the code over and over found that the issue is the single quote. As you can see in the query below, I used the single quote in the where clause. If I remove that I get a result but if I put it back even though the are rows that satisfies the filter it does not return anything. I also tried to use a view on this. Putting the where clause in the view and selecting the view directly and not using any filter. Still it does not return anything.

  try {
    Class.forName("org.postgresql.Driver").newInstance();
    Connection Conn = DriverManager.getConnection("jdbc:postgresql://{ipaddress}/database?user=postgres&password=password");

    Statement Stmt = Conn.createStatement();
    String sqlCommand = "SELECT col1_timestamp , col2 FROM table WHERE col1_timestamp > '00:01:00' ";

    ResultSet RS = Stmt.executeQuery(sqlCommand);

    while (RS.next()) {
        data.add(RS.getInt("col1_timestamp ")
                + "=>" + RS.getString("col2"));
    }
    // Clean up after ourselves
    RS.close();
    Stmt.close();
    Conn.close();
}
catch (SQLException E) {
    System.out.println("SQLException: " + E.getMessage());
    System.out.println("SQLState:     " + E.getSQLState());
    System.out.println("VendorError:  " + E.getErrorCode());
}
catch(Exception ex)
{
    System.out.println(ex.getMessage());
}

I also already tried:

  String sqlCommand = "SELECT col1_timestamp, col2 FROM table "
                + " WHERE col2= ? AND (now() - col1_timestamp::timestamp with time zone) < interval ? ";

        PreparedStatement preparedStatement = Conn.prepareStatement(sqlCommand);
        preparedStatement.setString(1, "test");
        preparedStatement.setTime(2, new Time(new Long(-28680000))); //String(2, "00:02:00");

Still does not work, instead it throws an error on the second parameter.

It's pretty simple task really but it always returns a blank value.

Any idea?

user330315 · Accepted Answer · 2014-12-03 08:06:07Z

1

The interval literals are quite limited (in SQL in general and in Postgres) and you can't use a parameter for the unit of the interval literal

If you always have the same unit (e.g. minutes) you can do something like this:

String sqlCommand =
   "SELECT col1_timestamp, col2 FROM table " + 
   " WHERE col2= ? " + 
   " AND (now() - col1_timestamp::timestamp with time zone) < interval '1' minute * ?";

PreparedStatement preparedStatement = Conn.prepareStatement(sqlCommand);
preparedStatement.setString(1, "test");
preparedStatement.setInt(2, 5); // five minutes

If you need to query for different units (hours, minutes, days) then you either need to use the approriate number of minutes for each of them or change the SQL each time: interval '1' day * ? or interval '1' hour * ?

answered Dec 3, 2014 at 8:06

user330315

Sign up to request clarification or add additional context in comments.

10 Comments

oneofakind Over a year ago

is a single quote a concern at all? When I also tried to use a view it comes out wrong.

oneofakind Over a year ago

Tried it and it throws an error on the second parameter saying ERROR: syntax error at or near "$2"

user330315 Over a year ago

@oneofakind: not sure what you mean with the single quotes problem. The code as I have shown works for me, there must be something you are not telling us. Also your error message also doesn't seem to match the code. There is no $2 in my example

oneofakind Over a year ago

I'm sorry I did not scroll right. Tried it still returns nothing. Keep in mind that I keep on trying this on my pgAdmin and it does return rows. Printed out preparedStatement.toString(); to see the final query. I do not know what else to tell you.

user330315 Over a year ago

@GordThompson: interval '1' minute is the standard (ANSI) SQL format for an interval literal. interval '1 minute' is a Postgres specific format.

|

Collectives™ on Stack Overflow

Java JDBC Postgres: Select with single quote

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related