How to access sqlite data to ListView in fragment

Question

i'v android app where should save file.xlsx into sqlite DB, this DB should be empty on start then i choose the file.xlsx from directory to save it into sqlite and this is the code:

HomeFragment

public class HomeFragment extends Fragment {

public ListView mainListView;
public XlsxDB xlsxDB;
public Intent fileintent;

@Override
public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) {


    View parentView = inflater.inflate(R.layout.home, container, false);

    mainListView = parentView.findViewById(R.id.listveiw);
    FloatingActionButton btnImport = parentView.findViewById(R.id.btnImport);

    btnImport.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View view) {

            fileintent = new Intent(Intent.ACTION_GET_CONTENT);
            fileintent.setType("*/*");
            startActivityForResult(fileintent, 10);

        }
    });
   mainListView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
        @Override
        public void onItemClick(AdapterView<?> arg0, View arg1, int arg2, long arg3) {

            LinearLayout linearLayoutParent = (LinearLayout) arg1;
            LinearLayout lvh = (LinearLayout) linearLayoutParent.getChildAt(0);
            TextView itemCode = (TextView) lvh.getChildAt(0);
            TextView product = (TextView) lvh.getChildAt(1);

            Bundle dataBundle = new Bundle();
            dataBundle.putString("Item_Code", String.valueOf(itemCode.getText().toString()));
            dataBundle.putString("Product", String.valueOf(product.getText().toString()));
            Intent intent = new Intent(getApplicationContext(), ItemInfo.class);
            intent.putExtras(dataBundle);
            startActivity(intent);
        }
    });


    ArrayList<HashMap<String, String>> myList;
    myList = xlsxDB.getProducts();
    if (myList.size() != 0) {
        ListAdapter adapter = new SimpleAdapter(getActivity(), myList,
                R.layout.v, new String[]{Item_Code, Product, Quantity},
                new int[]{R.id.TxtProductCode, R.id.txtproductname, R.id.TxtProductQty});
        mainListView.setAdapter(adapter);
    }else{
        Toast.makeText(getContext(),"Kindly inform their is no data...",Toast.LENGTH_SHORT).show();
    }

    return parentView;
}

    public void onActivityResult(int requestCode, int resultCode, Intent data) {

    switch (requestCode) {
        case 10:
            if (resultCode == RESULT_OK) {

                FileInputStream inStream;
                XSSFWorkbook wb = null;
                try {
                    inStream = (FileInputStream) getContext().getContentResolver().openInputStream(data.getData());
                    if (inStream != null) {
                        wb = new XSSFWorkbook(inStream);
                    }
                    if (inStream != null) {
                        inStream.close();
                    }
                } catch (IOException e) {
                    Toast.makeText(getApplicationContext(), "kindly inform: " + e.getMessage(), Toast.LENGTH_SHORT).show();
                    e.printStackTrace();
                }

                XlsxDB dbAdapter = new XlsxDB(getContext());
                XSSFSheet sheet = null;
                if (wb != null) {
                    sheet = wb.getSheetAt(0);
                }

                if (sheet == null) {
                    return;
                }

                dbAdapter.open();
                dbAdapter.delete();
                dbAdapter.insertExcelToSqlite(dbAdapter, sheet);
                dbAdapter.close();

            }
            break;
    }

    ArrayList<HashMap<String, String>> myList;
    myList = xlsxDB.getProducts();
    if (myList.size() != 0) {
        ListAdapter adapter = new SimpleAdapter(getActivity(), myList,
                R.layout.v, new String[]{Item_Code, Product, Quantity},
                new int[]{R.id.TxtProductCode, R.id.txtproductname, R.id.TxtProductQty});
        mainListView.setAdapter(adapter);
    }else{
        Toast.makeText(getContext(),"Kindly inform their is no data...",Toast.LENGTH_SHORT).show();
    }
}

and this is my sqlite class:

XlsxDB

public class XlsxDB {
private static final String Tablename = "MyTable1";
public static final String id = "_id";// 0 integer
public static final String Item_Code = "itemCode";
public static final String Product = "DescTxt";
public static final String Quantity = "Qty";

private SQLiteDatabase db;
private DBHelper dbHelper;

public XlsxDB(Context context) {
    dbHelper = new DBHelper(context);
}

public void open() {
    if (null == db || !db.isOpen()) {
        try {
            db = dbHelper.getWritableDatabase();
        } catch (SQLiteException ignored) {
        }
    }
}

public void close() {
    if (db != null) {
        db.close();
    }
}

int insert(ContentValues values) {
    return (int) db.insert("MyTable1", null, values);
}

public void delete() {
    db = dbHelper.getWritableDatabase();
    db.execSQL("delete from " + Tablename);
}

private class DBHelper extends SQLiteOpenHelper {
    private static final int VERSION = 1;
    private static final String DB_NAME = "MyDB1.db";

    DBHelper(Context context) {
        super(context, DB_NAME, null, VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String create_sql = "CREATE TABLE IF NOT EXISTS "
                + Tablename + '(' + id
                + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + Item_Code + " TEXT ,"
                + Product + " TEXT ,"
                + Quantity + ", TEXT " + ')';
        db.execSQL(create_sql);


    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + Tablename);
    }

}

public static void insertExcelToSqlite(XlsxDB dbAdapter, Sheet sheet) {

    for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext(); ) {
        Row row = rit.next();

        ContentValues contentValues = new ContentValues();
        row.getCell(0, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING);
        row.getCell(1, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING);
        row.getCell(2, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING);

        contentValues.put(XlsxDB.Item_Code, row.getCell(0, Row.CREATE_NULL_AS_BLANK).getStringCellValue());
        contentValues.put(XlsxDB.Product, row.getCell(1, Row.CREATE_NULL_AS_BLANK).getStringCellValue());
        contentValues.put(XlsxDB.Quantity, row.getCell(2, Row.CREATE_NULL_AS_BLANK).getStringCellValue());

        try {
            if (dbAdapter.insert(contentValues) < 0) {
                return;
            }
        } catch (Exception ex) {
            Log.d("Exception in importing", ex.getMessage());
        }
    }
}


public ArrayList<HashMap<String, String>> getProducts() {
    ArrayList<HashMap<String, String>> proList;
    proList = new ArrayList<>();
    String selectQuery = "SELECT  * FROM " + Tablename;
    SQLiteDatabase database = dbHelper.getWritableDatabase();
    Cursor cursor = database.rawQuery(selectQuery, null);
    if (cursor.moveToFirst()) {
        do {
            HashMap<String, String> map = new HashMap<>();
            map.put(Item_Code, cursor.getString(1));
            map.put(Product, cursor.getString(2));
            map.put(Quantity, cursor.getString(3));
            proList.add(map);
        } while (cursor.moveToNext());
    }
    return proList;
    }
 }

but i get this error massage :

java.lang.NullPointerException: Attempt to invoke virtual method 'java.util.ArrayList com.example.testApp.Sqlite_DataBases.XlsxDB.getProducts()' on a null object reference

i tried to replace

ArrayList<HashMap<String, String>> myList = xlsxDB.getProducts();

with custom adapter but no more useful results

so where is the problem

MikeT · Accepted Answer · 2019-08-04T20:40:11.087

You are only declaring xlsxDB as in public XlsxDB xlsxDB;, which will assign null as the pointer to the xlsxDB objec ( as no such object exists).

You need to instantiate/construct the (an actual) object using xlsxDB = new XlsxDB(a_valid_context); so that the variable has a pointer to an actual object.

This would probably be done in the onCreateView of the fragment using xlsxDB = new XlsxDB(getActivity());

You may wish to have a read of What is a NullPointerException, and how do I fix it?

How to access sqlite data to ListView in fragment

1 Answers1