jdbc:sqlite not retreving data

Question

I am using a jdbc:sqlite to access a sqlite db and get some records with a simple select statement. There are rows in the database but the java function returns nothing.

   private static String ConnectionString = "jdbc:sqlite:D:\\My Documents\\NetBeansProjects\\IntelligentBusStop\\BusSystem.db";

private static ResultSet GetData(String command)
{
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try
    {
        Class.forName("org.sqlite.JDBC");
        connection = DriverManager.getConnection(ConnectionString);
        statement = connection.createStatement();
        resultSet = statement.executeQuery(command);
        return resultSet;
    }
    catch (Exception e)
    {
        e.printStackTrace();
        return null;
    }
    finally
    {
        try
        {
            resultSet.close();
            statement.close();
            connection.close();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }
}
 public static ArrayList<StopBS> GetStopBS()
{
    ResultSet results = GetData("Select StopNumber, Tag, Latitude, Longitude FROM StopTable ");
    ArrayList<StopBS> stops = new ArrayList<StopBS>();
    try
    {
        while (results.next())
        {
            StopBS newStop = new StopBS();
            newStop.StopNumber = results.getInt("StopNumber");
            newStop.Tag = results.getString("Tag");
            newStop.Lat = results.getFloat("Latitude");
            newStop.Long =  results.getFloat("Longitude");
            stops.add(newStop);
        }
        return stops;
    }
    catch (Exception e)
    {
        e.printStackTrace();
        return null ;
    }
}

Using the sql statement directly on the database works. An i am using Razor sql to view the db if that makes any difference.

How many times does your while loop loop? Put in a system.out.println or something — Richard H, Apr 27 '11 at 10:33

duffymo · Accepted Answer · 2011-04-27T11:24:33.697

You close your ResultSet in GetData, so by the time GetStopBS tries to load it into the data structure the cursor isn't available.

I'd rewrite this code to do it properly. You're close, but not there.

Learn the Sun coding standards. You're following .NET standards, not Java.

Your heart is in the right place when you close your statement, result set, and connection, but I disagree with where you put the code. I recommend never passing java.sql package references out of the method scope in which they were created. Create them, use them, and close them in the same method.

You should also close them in a finally block, wrapping the close in individual try/catch blocks.

jdbc:sqlite not retreving data

1 Answers1