SQL query method returns null

Question

I have a class which manages my database. However, since I made all methods in the class static, the query method doesn't work anymore: it returns me null.

This is the DatabaseManager class:

public class DatabaseManager {
    public final static String DBPath = "database.db";
    private static Connection connection = null;
    private static Statement statement = null;

    /**
     * Connect to the database.
     * @return True if succeeded to connect to the database. False if not.
     */
    private static boolean connect() {
        try {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:" + DBPath);
            statement = connection.createStatement();
            return true;
        }
        catch (ClassNotFoundException | SQLException connectException) {
            connectException.printStackTrace();
            System.out.println("Unable to connect to the database.");
            return false;
        }
    }

    /**
     * Close the connection.
     */
    private static void close() {
        try {
            connection.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Execute a query inside a database.
     * @param queryStr the SQL query
     * @return ResultSet object is the result of the query (if it succeeded)
     */
    public static ResultSet query(String queryStr) {
        connect();
        ResultSet result = null;

        try {
            result = statement.executeQuery(queryStr);
        }
        catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Impossible to execute the following query : " + queryStr);
            System.out.println("It may contains error.");
        }
        finally {
            close();
        }

        return result;

    }
}

When I add this to the query method :

try {
    while(result.next()) {
        System.out.println(result.getString("columnTest"));
    }
}
catch (SQLException e) { e.printStackTrace(); }

I am getting the good results in my terminal. (The data is from the database). But when I delete it, and add it to the main method:

import DatabaseManager;

import java.sql.ResultSet;
import java.sql.SQLException;

public class Main {

    public static void main(String[] args) {
        ResultSet result;
        result = DatabaseManager.query("SELECT * FROM DataTest");

        try {
            while (result.next()) {
                System.out.println(result.getString("columnTest"));
            }
        }
        catch (SQLException e) {
            e.getStackTrace();
        }
    }
}

It prints null as though the query doesn't return anything... Why is that the case?

score 0 · Accepted Answer · answered Jan 23 '16 at 12:05

0

I think the problem come from your function "query". You are closing the connection there. So You can't print the result in the main

answered Jan 23 '16 at 12:05

kulturman

26

who told this after closing the connection we can't print the result? – SatyaTNV Jan 23 '16 at 12:08
It is said that :"JDBC doesn't bring back all the results of a query in a ResultSet". So I think after closing connection you'ill not be able to call next() and get all the results. Source : http://stackoverflow.com/questions/25493837/java-cant-use-resultset-after-connection-close – kulturman Jan 23 '16 at 12:35
Yup, you're right, without the call to the close methods, it works... But how can I close the connection directly in the methods query? – Louis Etienne Jan 23 '16 at 12:45
@Wizix You can't, the way your code handles JDBC is fundamentally flawed as it ignores the way the JDBC API should be used. The only option would be to first transfer the result to something else (a list of objects, for example). – Mark Rotteveel Jan 23 '16 at 15:15

SQL query method returns null

1 Answers1