Simple select using SQLite java driver 3.21.0 does not return data

Question

I am facing a problem issuing a very simple select using SQLite and java on MacOS.

here is my code:

public class TestJDBC1 
{

public static Connection connect_DB() throws ClassNotFoundException {
    Class.forName("org.sqlite.JDBC");
    Connection conn = null;
    try {
        // db parameters
        String url = "jdbc:sqlite:/Users/Shared/DB_Farmaci/oracle-sample.db";
        // create a connection to the database
        conn = java.sql.DriverManager.getConnection(url);

        System.out.println("Connection a SQLite stabilita.");

    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }
    return conn;
}

public static void main( String[] args ) throws ClassNotFoundException, InterruptedException, SQLException
{
    Connection conn = connect_DB();
    try 
    {
      conn.setAutoCommit(false);
    } catch (SQLException ex) {
        Logger.getLogger(TestJDBC1.class.getName()).log(Level.SEVERE, null, ex);
    }

    try 
    {
        Statement S = null ;
        ResultSet rs = null ;
        S = conn.createStatement();

        String queryS = "select deptno, dname, loc from dept ;" ;
        rs = S.executeQuery(queryS);

        S.close();
        conn.close();

        int x = 0;
        while (rs.next()) 
        {
          x++;
          System.out.println(rs.getInt("deptno") +  "\t" + 
                             rs.getString("dname") + "\t" +
                             rs.getDouble("loc"));
        }
        if (x > 0) 
        {
            System.out.println("# rows returned => " + x);
        } else 
        {
            System.out.println("no rows returned");
        }
    } catch (SQLException se) {
        System.out.println(se);
        System.out.println("errore DB");
    }
}

As you can see it is really simple but it does not return any data.

My Environment is the following:

NetBeans 8.2
sqlite-jdbc-3.21.0.jar (current version )

Using similar code I can create tables and insert rows into the DB, so setup of the environment should be ok ( Properties=> Libraries => both on compile and run).

I also tried on Eclipse, with the same, negative result. I also tried all backward JDBC driver versions I found, same situation. I am using DB Browser for SQLite 3.10.1, and I can read the data I am looking for.

I have SQLite installed on my Mac and I can read data using command line commands.

It looks like a driver malfunction, unless I am missing something very important.

score 0 · Answer 1 · answered May 10 '18 at 14:20

you cannot access your result set after closing connections. refer to Java - Can't use ResultSet after connection close.

call your close() after you have finished using the resultSet.

    String queryS = "select deptno, dname, loc from dept ;" ;
    rs = S.executeQuery(queryS);

    //remove your close from here.

    int x = 0;
    while (rs.next()) 
    {
      x++;
      System.out.println(rs.getInt("deptno") +  "\t" + 
                         rs.getString("dname") + "\t" +
                         rs.getDouble("loc"));
    }
    if (x > 0) 
    {
        System.out.println("# rows returned => " + x);
    } else 
    {
        System.out.println("no rows returned");
    }


    //move your close here
    S.close();
    conn.close();

Mark Rotteveel · Answer 2 · 2018-05-10T14:26:38.063

You close the connection and statement before processing the result set. That is not possible (and should have lead to an SQLException when you used rs.next(), which you didn't post).

You need to change your code to something like:

try (Connection conn = connect_DB()) {
    // other actions with connection

    String queryS = "select deptno, dname, loc from dept ;" ;
    try (Statement statement = conn.createStatement();
         ResultSet rs = statement.executeQuery(queryS)) {

        int x = 0;
        while (rs.next())  {
            x++;
            // etc
        }

    } catch (SQLException se) {
        se.printStackTrace()
        System.out.println("errore DB");
    }
}

I used try-with-resources here, as it will simplify your code and prevent mistakes like this, because resources like the result set will be limited to the scope of their parent object. At the end of a try-with-resources, Java will automatically call the close() method.

I suggest you read a tutorial on JDBC and check its Javadoc to get more information on how to use the JDBC api.

Simple select using SQLite java driver 3.21.0 does not return data

2 Answers2