How do you pass a spinner selection from main activity to databasehelper to run a query and then pass the results to a second activity?

Refresh

March 2019

Views

7 time

0

I am trying to make a small app that one the user selects on the spinner, the selection will be sent to the database helper to run the select query and then the select query results will be display in list view on a second activity.

If run the query without trying to get the where function to work or the variable to be passed I can see all of the results from my existing database database in listview, but I am unable to just get the results for the user selected spinner value.

Main Activity:

import android.content.Intent;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Spinner;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {
    private DatabaseHelper mydatabasehelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mydatabasehelper = new DatabaseHelper(this);
    }

    private static final String ERPSelect = "ERPS";
    public void toastMe(View view){
        // Toast myToast = Toast.makeText(this, message, duration);
        Spinner ERPSpinner = (Spinner)findViewById(R.id.ERPSpinner);
        String ERPS = ERPSpinner.getSelectedItem().toString();
        String ERPforQuery = ERPSpinner.getSelectedItem().toString();
        mydatabasehelper.getAllMyData(ERPforQuery);
        Toast myToast = Toast.makeText(this, ERPS.toString(),
                Toast.LENGTH_SHORT);
        myToast.show();
        Intent TestIntent = new Intent (this, SecondActivity.class);
        TestIntent.putExtra(ERPSelect,ERPS);
        startActivity(TestIntent);
    }


}

DatabaseHelper:

import android.database.sqlite.SQLiteOpenHelper;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.Cursor;
import android.provider.BaseColumns;


import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.InputStream;
import java.io.IOException;

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "SystemDBforApp.db";
    private static final int DATABASE_VERSION = 1;
    private static String DATABASE_PATH;
    private final Context context;
    SQLiteDatabase db;


    public final static String USER_TABLE = "Systems";
    public final static String USER_SYSTEM_COLUMN = "System";
    public final static String USER_REPORT_COLUMN = "Report";
    public final static String USER_ADDTL_COLUMN = "Additional Info";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
        DATABASE_PATH = context.getDatabasePath(DATABASE_NAME).getPath(); 
        createDb();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

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

    public void createDb() {
        boolean dbExist = checkDbExist();

        if (!dbExist) {
            //this.getReadableDatabase(); 
            copyDatabase();
        }
    }

    private boolean checkDbExist() {

        File db = new File(DATABASE_PATH);
        if (!db.exists()) {
            if(!new File(db.getParent()).exists()) {
                new File(db.getParent()).mkdirs();
            }
            return false;
        } else {
            return true;
        }

        /* Done away with unreliable method
        SQLiteDatabase sqLiteDatabase = null;
        try {
            String path = DATABASE_PATH;
            sqLiteDatabase = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READONLY);
        } catch (Exception ex) {
        }
        if (sqLiteDatabase != null) {
            sqLiteDatabase.close();
            return true;
        }
        return false;
        */
    }

    private void copyDatabase() {
        try {
            InputStream inputStream = context.getAssets().open(DATABASE_NAME);
            String outFileName = DATABASE_PATH;
            OutputStream outputStream = new FileOutputStream(outFileName);

            byte[] b = new byte[1024];
            int length;
            while ((length = inputStream.read(b)) > 0) {
                outputStream.write(b, 0, length);
            }
            outputStream.flush();
            outputStream.close();
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private SQLiteDatabase openDatabase() {
        String path = DATABASE_PATH;
        db = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READWRITE);
        return db;
    }

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


    public Cursor getAllMyData(String ERPforQuery) {
        String query2 = "SELECT * FROM " + USER_TABLE + " Where " + USER_SYSTEM_COLUMN + "=" +"'" + ERPforQuery + "'";
        SQLiteDatabase db = this.getWritableDatabase();
        String[] columns = new String[]{"*,rowid AS " + BaseColumns._ID};
        return db.rawQuery(query2,null);

    }
}

Second Activity:

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.widget.TextView;
import android.database.Cursor;
import android.content.Context;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;

public class SecondActivity extends AppCompatActivity {
    DatabaseHelper mDBHlpr;
    Cursor mCsr;
    ListView mUserList;
    SimpleCursorAdapter mSCA;
    Context mContext;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_second);
        mContext = this;
        mUserList = this.findViewById(R.id.userlist); //<<<<<<<<<< id of the ListView
        mDBHlpr = new DatabaseHelper(this); //Instantiate the database helper
        setupOrRefreshTheListView();
        ShowERP();
    }

    private static final String ERPSelect = "ERPS";

    public void ShowERP() {

        TextView headingView = (TextView)
               findViewById(R.id.textview_label);

        String ERPS = getIntent().getStringExtra(ERPSelect);
        headingView.setText("Recommended Reports: " + ERPS);
    }

    private void setupOrRefreshTheListView() {
        mCsr =  mDBHlpr.getAllMyData(); //getting an error getAllMyData
                                        //(String)
                                        //      in DatabaseHelper cannot be 
                                        //applied
                                        //    to
                                        //() 
        if (mSCA == null) {
            mSCA = new SimpleCursorAdapter(
                    this,
                    android.R.layout.simple_list_item_2,
                    mCsr,

                    new String[]{
                            DatabaseHelper.USER_REPORT_COLUMN,
                            DatabaseHelper.USER_ADDTL_COLUMN,
                    },
                    new int[]{
                            android.R.id.text1,
                            android.R.id.text2},
                    0
            );
            mUserList.setAdapter(mSCA);


        }
    }

0 answers