Why is a part of Java code not executed after a PostgreSQL query?

Question

I'm doing a program that generates random data and populates a PostgreSQL table with it. You will find a code where I try to execute three simple SQL queries. The first two queries work well but not the third. Actually, a part of my code seems not to be executed at all after the second query, as "yes" is not printed in the console.

I tried to compile my code by commenting out the 2nd query execution line and then the end of my code is executed. Any idea?

import java.sql.*;

public class Main {

    public static void main(String[] args) {

        //connection to DB
        Class.forName("org.postgresql.Driver");
        Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/Benerator","postgres","newPassword");

        //print the first two columns of table bank_card_people
        PreparedStatement stmt = con.prepareStatement("select * from public.bank_card_people");
        ResultSet res = stmt.executeQuery();
        while(res.next()){
            System.out.println(res.getString(1)+ " " + res.getString(2));}

        //add a line to the same table
        String SQL = "insert into public.bank_card_people (\"first-name\", \"last-name\", \"card-number\") VALUES ('example','example','example')";
        PreparedStatement stmt2 = con.prepareStatement(SQL);
        stmt2.executeQuery();

        // is supposed to print all the databases
        PreparedStatement stmt3 = con.prepareStatement("SELECT datname FROM pg_database WHERE datistemplate = false");
        ResultSet res2 = stmt3.executeQuery();
        System.out.println("yes");
        while(res2.next()){
            System.out.println(res2.getString(1));}

Here is the output :

User One
User Two
User Three
example example
example example
No results were returned by the query.

Here is the output when I comment out the line : stmt2.executeQuery();

User One
User Two
User Three
example example
example example
yes
postgres
Benerator

Joop Eggen · Accepted Answer · 2018-08-15 12:39:08Z

1

The INSERT statement must not be executed by executeQuery but executeUpdate.

Also Connection, Statement and ResultSet must be .close()d, which can best be done with try-with-resources, that guarantees a closing even with an exception or return.

It also helps in naming, as it introduces new blocks.

    //connection to DB
    Class.forName("org.postgresql.Driver");
    try (Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/Benerator","postgres","newPassword")) {

        //print the first two columns of table bank_card_people
        try (PreparedStatement stmt = con.prepareStatement("select * from public.bank_card_people");
                ResultSet res = stmt.executeQuery()) {
            while (res.next()) {
                System.out.println(res.getString(1)+ " " + res.getString(2));
            }
        }

        //add a line to the same table
        String sql = "insert into public.bank_card_people (\"first-name\", \"last-name\", \"card-number\") VALUES ('example','example','example')";
        try (PreparedStatement stmt2 = con.prepareStatement(sql)) {
            int updateCount = stmt2.executeUpdate();
        }

        // is supposed to print all the databases
        try (PreparedStatement stmt3 = con.prepareStatement("SELECT datname FROM pg_database WHERE datistemplate = false"));
                ResultSet res2 = stmt3.executeQuery()) {
            System.out.println("yes");
            while(res2.next()){
                System.out.println(res2.getString(1));
            }
        }
    }

answered Aug 15, 2018 at 12:39

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

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

3 Comments

rip-freedom Over a year ago

thank you for your answer, solved the problem! Do you know why it still works when I use executeQuery ?

Joop Eggen Over a year ago

The MySQL driver might be lenient; both call the basic execute().

Joop Eggen Over a year ago

I did not do MySQL for a time; not closing can interfere with the transaction, see also setAutocommit. If a single statement is closed it is comitted with autocommit.

Collectives™ on Stack Overflow

Why is a part of Java code not executed after a PostgreSQL query?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related