Android JDBC do not close connection and postgresql tells me it has too many connections (which is true after checking)

Question

Here is my Database.java. It does works "flawless" for what I want, but the problem is that it just not closing the connections with "connection.close()".

package com.example.testDB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class Database{

    private Connection connection;

    private final String host = "host";
    private final String database = "databasename"
    private final int port = 1234;
    private final String user = "user";
    private final String pass = "password";
    private String url = "jdbc:postgresql://%s:%d/%s";
    private boolean status;


    public Database() {
        this.url = String.format(this.url, this.host, this.port, this.database);
    }

    public void insert() {
        Thread thread = new Thread(new Runnable() {
            @Override
            public void run() {
                try {

                    Statement stmt;
                    String sql = "INSERT INTO TEST_TABLE (COLUMN1, COLUMN2, COLUMN3) VALUES ('TEST','TEST','TEST')";

                    Class.forName("org.postgresql.Driver");
                    connection = DriverManager.getConnection(url, user, pass);

                    status = true;
                    System.out.println("connected:" + status);

                    stmt = connection.createStatement();
                    ResultSet rs = stmt.executeQuery(sql);

                    connection.close();

                } catch (Exception e) {
                    status = false;
                    System.out.print(e.getMessage());
                    e.printStackTrace();
                }
            }
        });
        thread.start();
        try {
            thread.join();
        } catch (Exception e) {
            e.printStackTrace();
            this.status = false;
        }
    }
}

I don't know why this is happening, or if this is how I should do it in the first place, I got this class model from a tutorial on the internet and it does works, but has this problem of the connections not closing somehow. What am I doing wrong?

Please reconsider your use of [JDBC on Android](https://stackoverflow.com/questions/15853367/jdbc-vs-web-service-for-android). — CommonsWare, Oct 17 '21 at 17:23
I know it a bad pratice, but it good and simple enough for what I want to do now. — Igor Lima, Oct 17 '21 at 17:48
`connection` should be a local variable, not an instance variable. The `Class.forName()` line hasn't been needed since 2006. — user207421, Oct 17 '21 at 22:21
You need to use `execute` or `executeUpdate` instead of `executeQuery` to execute an insert statement. `executeQuery` throws an exception when it is used to execute a query that doesn't produce a result set, which is what causes your `connection.close()` to be skipped. — Mark Rotteveel, Oct 18 '21 at 06:06

score -1 · Answer 1 · edited Oct 18 '21 at 06:08

I solved it by adding "finally" in the end of the first "Try", so this should work flawlessly:

I found the answer in a 11 years old thread on stackoverflow!

Thank you very much tine2k!

package com.example.testDB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class Database{

    private Connection connection;

    private final String host = "host";
    private final String database = "databasename"
    private final int port = 1234;
    private final String user = "user";
    private final String pass = "password";
    private String url = "jdbc:postgresql://%s:%d/%s";
    private boolean status;


    public Database() {
        this.url = String.format(this.url, this.host, this.port, this.database);
    }

    public void insert() {
        Thread thread = new Thread(new Runnable() {
            @Override
            public void run() {
                try {

                    Statement stmt;
                    String sql = "INSERT INTO TEST_TABLE (COLUMN1, COLUMN2, COLUMN3) VALUES ('TEST','TEST','TEST')";

                    Class.forName("org.postgresql.Driver");
                    connection = DriverManager.getConnection(url, user, pass);

                    status = true;
                    System.out.println("connected:" + status);

                    stmt = connection.createStatement();
                    ResultSet rs = stmt.executeQuery(sql);

                } catch (Exception e) {
                    status = false;
                    System.out.print(e.getMessage());
                    e.printStackTrace();
                } finally {
                    try {

                        connection.close();

                    } catch (Exception e) {

                        System.out.println(e);
                    }

                }
            }
        });
        thread.start();
        try {
            thread.join();
        } catch (Exception e) {
            e.printStackTrace();
            this.status = false;
        }
    }
}

Do not close the connection after executing the query, it just don't work. You must do it in finally after doing everything.

I've been trying to fix this for 3 days now, but since JDBC isn't much appreciated, you won't find an actual answer for this kind of problem, so I hope anyone who has a small project like me that "do not care much" about the security of your database and just want to access it to do stuff, this is how you should do in Android.

But keep in mind that the best way is still using a web-server, do not use JDBC for actual serious stuff.

If closing in the finally block solves the problem for you, that means that statement execution fails with an exception, which is not surprising, because you're not supposed to execute an insert with `executeQuery`, you need to use `execute` or `executeUpdate`. — Mark Rotteveel, Oct 18 '21 at 06:03
Closing the connection after executing the query *does* work, *if the query worked* and didn't throw an exception. Closing it in the `finally` block *always* works, and that's why you should put it there. Your suggestion that 'JDBC isn't so much appreciated' is absurd. It is in worldwide use. And you did find a duplicate question here. — user207421, Oct 18 '21 at 06:17
Thanks guys for the help! I didn't know JDBC had others types of executes... Now it explains why I would get a warning saying "no results were found" or something like that every time. Now everything is working very well! And the fact I said "not much appreciated" is because everytime I see someone saying they are using JDBC people already tell them to not use it and instead make a web server always. Even tho I was doing it all wrong, the queries were still being executed and my db was still "working" normally and the actions were being passed and saved, why is that? — Igor Lima, Oct 18 '21 at 23:24
It wasn't a warning, it was an exception indicating an error, which you caught, printed out and then ignored. JDBC is in widespread use in **Java** on server applications. You shouldn't use it on **Android**, which isn't really Java, but the primary reasons you shouldn't use JDBC from Android are because it is relatively insecure (you need to expose your DB server to the internet, and DB credentials within your application), but also given mobile handsets, packetloss, chatty protocols, etc, doesn't perform that well compared to webservices. — Mark Rotteveel, Oct 19 '21 at 08:24
In short, JDBC on server applications (e.g. a webservice for a mobile application) is good, JDBC on Android (mobile) applications is bad. — Mark Rotteveel, Oct 19 '21 at 08:26

Android JDBC do not close connection and postgresql tells me it has too many connections (which is true after checking)

1 Answers1