Android - Get ResultSet from SQLite Database, then use for OpenCSV

Question

Good day. I have an application that uses a database that contains five database tables, what I want to do is to be able to export two of those tables as CSV files and email them. Upon doing initial research, I saw that OpenCSV Library was a popular choice for exporting SQLite tables to CSV. However, upon further research, I saw that the export works by giving a ResultSet object to the writeAll(ResultSet rSet, Boolean includeHeaders) function, however, it wasn't explicitly shown on how the ResultSet is obtained. I tried to look for guides on how to use a ResultSet in a DatabaseHelper class but I came up empty.

Can anyone teach me how to implement a function in my DatabaseHelper class that would return the ResultSet of the query result? Here is my DatabaseHelper class:

public class OrderListDBAdapter {

    private DatabaseHelper mDBHelper;
    private SQLiteDatabase mDB;
    private final Context context;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, "itemList.db", null, 1);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        }
    }


    public OrderListDBAdapter(Context context){
        this.context = context;
    }

    public OrderListDBAdapter open() throws SQLException{
        this.mDBHelper = new DatabaseHelper(this.context);
        //this.mDB = this.mDBHelper.getWritableDatabase(DBAdapter.key);
        this.mDB = this.mDBHelper.getWritableDatabase();
        return this;    
    }

    public void close(){
        this.mDBHelper.close();
    }

    //TODO - crud queries
    public long insertOrder(OrderListClass order){
        Log.d("Angelo", "Hi, insert Order entered");
        ContentValues values = new ContentValues();

        values.put("orderDate", order.getOrderDate());
        values.put("customer", order.getCustomer());
        values.put("agent", order.getAgent());
        values.put("itemName", order.getItemName());
        values.put("price", order.getPrice());
        values.put("discount", order.getDiscount());
        values.put("quantity", order.getQuantity());
        values.put("requestedDiscount", order.getRequestedDiscount());

        return this.mDB.insert("OrderList", null, values);
    }

    public boolean deleteOrder(String productname) {
        Log.d("Angelo", "Hi, delete Order entered");
        return mDB.delete("OrderList", "itemName =" + productname, null) > 0;
    }

    public int updateOrder(OrderListClass order) {
        Log.d("Angelo", "Hi, update Order entered");

        ContentValues values = new ContentValues();
        values.put("orderDate", order.getOrderDate());
        values.put("customer", order.getCustomer());
        values.put("agent", order.getAgent());
        values.put("itemName", order.getItemName());
        values.put("price", order.getPrice());
        values.put("discount", order.getDiscount());
        values.put("quantity", order.getQuantity());
        values.put("requestedDiscount", order.getRequestedDiscount());

        // updating row
        return mDB.update("OrderList", values, "itemName" + " = ?", new String[] { order.getItemName() });
    }

    public ArrayList<OrderSummaryListViewItem> getAllOrders(String date, String customerName){
        ArrayList<OrderSummaryListViewItem> orders = new ArrayList<OrderSummaryListViewItem>();

        String query = "SELECT itemName, price, discount, quantity, requestedDiscount FROM OrderList WHERE orderDate = '" 
                + date + "' AND customer = '" + customerName + "'";

        Cursor cursor = mDB.rawQuery(query, null);
        if (cursor.moveToFirst()) {
            do {

                String quantity = cursor.getString( cursor.getColumnIndex("quantity") );
                String itemName = cursor.getString( cursor.getColumnIndex("itemName") );
                String discount = cursor.getString( cursor.getColumnIndex("discount") );
                String price = cursor.getString( cursor.getColumnIndex("price") );
                String discRequest = cursor.getString( cursor.getColumnIndex("requestedDiscount") );

                OrderSummaryListViewItem entry = new OrderSummaryListViewItem(quantity, itemName, discount, price, discRequest);

                orders.add(entry);
            } while (cursor.moveToNext());
        }

        cursor.close(); 

        return orders;
    }


}

I have a feeling that it's similar to executing a query, however, instead of getting a Cursor, I should get a ResultSet and return that ResultSet to my Activity.

I am also open to alternative ways to export a sqlite db to a csv file.

Answer 1

I'm at exactly the same point now.

I think java.sql.ResultSet and android.database.sqlite.SQLiteDatabase are just incompatible.

• Connection, Statement, and ResultSet come from Java (package java.sql).

• SQLiteDatabase comes from Android and is different. Instead of a ResultSet it returns a Cursor.

You could try to not use the Android classes but the Java classes for database access, but I'm not sure if that's a good idea.

I use Cursor and CSVWriter#writeNext(String[]) instead. This is my implementation.