how to properly execute a `SET` query in java using jdbc driver

Question

I am able to make a connection to my database but unable to execute a SET query properly. From looking at the documentation, I should be using execute not executeQuery or executeUpdate. I have a working connection and this is the rest :

 try (Statement st = connection.createStatement()) {
        boolean result = st.execute("SET search_path TO '712275-8S8DH-74DASS'");
        ResultSet queryResult = st.executeQuery("SELECT MAX(customer.last_updated) from customer");
        while (queryResult.next()) {
          String lastUpdated = queryResult.getString("last_updated");
        }
 } catch (SQLException ex) {
        LOGGER.info(ex.getMessage());
 }

I get these exceptions: The column name last_updated was not found in this ResultSet. and No data from query which makes me think the search_path hasn't been set properly because when I make a direct connection using psql, I am able to get results by running

postgres=> SET search_path TO '712275-8S8DH-74DASS';
SET
postgres=> SELECT MAX(customer.last_updated) from customer;
[expected result]

I somehow think the [expected result] does not show the column name as last_updated — Scary Wombat
– Scary Wombat, Commented Jun 7, 2021 at 7:18
You should be using getObject(..., LocalDateTime.class) to begin with - don't retrieve timestamps as strings — user330315
– user330315, Commented Jun 7, 2021 at 7:43

Davide D'Alto · Accepted Answer · 2021-06-07 07:08:19Z

1

Your query doesn't return the result in a column called last_updated. That's because you are doing an aggregate operation. I think the column returned by the query is called max.

This might work:

String lastUpdated = queryResult.getString("max");

Or you can use an alias:

ResultSet queryResult = st.executeQuery("SELECT MAX(customer.last_updated) as last_updated from customer");

answered Jun 7, 2021 at 7:08

Davide D'Alto

8,4212 gold badges20 silver badges34 bronze badges

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

1 Comment

Scary Wombat Over a year ago

or queryResult.getString(1);

Collectives™ on Stack Overflow

how to properly execute a `SET` query in java using jdbc driver

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related