Заполнение данных из edittext input в spinner display с помощью sqlite

Expert Please advise with following my query. I am trying to run the query in Android studio .

As is : Spinner shows the X value as per edittext X value input using Textwatcher.

To be : Spinner should show the Y value as per X value input in Edit text.

Example: If i enter value "6" in edittext then my spinner should show the vaue "Data Structures"

<pre>package com.bar.example.myapplication;


import android.content.ContentValues;
import android.content.Context;
import android.content.res.AssetManager;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.Arrays;

class DBHelper extends SQLiteOpenHelper {

  private Context mContext;

  //TASK: DEFINE THE DATABASE VERSION AND NAME  (DATABASE CONTAINS MULTIPLE TABLES)
  static final String DATABASE_NAME = "OCC";
  private static final int DATABASE_VERSION = 1;

  //TASK: DEFINE THE FIELDS (COLUMN NAMES) FOR THE COURSES TABLE
  public static final String COURSES_TABLE = "Courses";
  public static final String COURSES_KEY_FIELD_ID = "_id";
  public static final String FIELD_ALPHA = "alpha";
  public static final String FIELD_NUMBER = "number";
  public static final String FIELD_TITLE = "title";

  //TASK: DEFINE THE FIELDS (COLUMN NAMES) FOR THE INSTRUCTORS TABLE


  //TASK: DEFINE THE FIELDS (COLUMN NAMES) FOR THE OFFERINGS TABLE
  private static final String OFFERINGS_TABLE = "Offerings";
  private static final String OFFERINGS_KEY_FIELD_ID = "crn";
  private static final String FIELD_SEMESTER_CODE = "semester_code";
  public static final String FIELD_COURSE_ID = "course_id";

  public DBHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    mContext = context;
  }

  @Override
  public void onCreate(SQLiteDatabase database) {
    String createQuery = "CREATE TABLE " + COURSES_TABLE + "(" +
      COURSES_KEY_FIELD_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
      FIELD_ALPHA + " TEXT, " +
      FIELD_NUMBER + " TEXT, " +
      FIELD_TITLE + " TEXT" + ")";
    database.execSQL(createQuery);



    createQuery = "CREATE TABLE " + OFFERINGS_TABLE + "(" +
      OFFERINGS_KEY_FIELD_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
      FIELD_SEMESTER_CODE + " INTEGER, " +
      FIELD_COURSE_ID + " INTEGER, "

      +
      "FOREIGN KEY(" + FIELD_COURSE_ID + ") REFERENCES "

      +
      COURSES_TABLE + "(" + COURSES_KEY_FIELD_ID + ")" +
      ")";
    database.execSQL(createQuery);
  }

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

    database.execSQL("DROP TABLE IF EXISTS " + OFFERINGS_TABLE);
    onCreate(database);
  }

  //********** COURSE TABLE OPERATIONS:  ADD, GETALL, EDIT, DELETE

  public void addCourse(Course course) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();

    values.put(FIELD_ALPHA, course.getAlpha());
    values.put(FIELD_NUMBER, course.getNumber());
    values.put(FIELD_TITLE, course.getTitle());

    db.insert(COURSES_TABLE, null, values);

    // CLOSE THE DATABASE CONNECTION
    db.close();
  }

  public ArrayList < Course > getAllCourses() {
    ArrayList < Course > coursesList = new ArrayList < > ();
    SQLiteDatabase database = this.getReadableDatabase();
    //Cursor cursor = database.rawQuery(queryList, null);
    Cursor cursor = database.query(
      COURSES_TABLE,
      new String[] {
        COURSES_KEY_FIELD_ID,
        FIELD_ALPHA,
        FIELD_NUMBER,
        FIELD_TITLE
      },
      null,
      null,
      null, null, null, null);

    //COLLECT EACH ROW IN THE TABLE
    if (cursor.moveToFirst()) {
      do {
        Course course =
          new Course(cursor.getInt(0),
            cursor.getString(1),
            cursor.getString(2),
            cursor.getString(3));
        coursesList.add(course);
      } while (cursor.moveToNext());
    }
    return coursesList;
  }

  public void deleteCourse(Course course) {
    SQLiteDatabase db = this.getWritableDatabase();

    // DELETE THE TABLE ROW
    db.delete(COURSES_TABLE, COURSES_KEY_FIELD_ID + " = ?",
      new String[] {
        String.valueOf(course.getId())
      });
    db.close();
  }

  public void deleteAllCourses() {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(COURSES_TABLE, null, null);
    db.close();
  }

  public void updateCourse(Course course) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();

    values.put(FIELD_ALPHA, course.getAlpha());
    values.put(FIELD_NUMBER, course.getNumber());
    values.put(FIELD_TITLE, course.getTitle());

    db.update(COURSES_TABLE, values, COURSES_KEY_FIELD_ID + " = ?",
      new String[] {
        String.valueOf(course.getId())
      });
    db.close();
  }

  public Course getCourse(int id) {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.query(
      COURSES_TABLE,
      new String[] {
        COURSES_KEY_FIELD_ID,
        FIELD_ALPHA,
        FIELD_NUMBER,
        FIELD_TITLE
      },
      COURSES_KEY_FIELD_ID + "=?",
      new String[] {
        String.valueOf(id)
      },
      null, null, null, null);

    if (cursor != null)
      cursor.moveToFirst();

    Course course = new Course(
      cursor.getInt(0),
      cursor.getString(1),
      cursor.getString(2),
      cursor.getString(3));

    db.close();
    return course;
  }




  //********** OFFERING TABLE OPERATIONS:  ADD, GETALL, EDIT, DELETE

  public void addOffering(int crn, int semesterCode, int courseId) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();

    values.put(OFFERINGS_KEY_FIELD_ID, crn);
    values.put(FIELD_SEMESTER_CODE, semesterCode);
    values.put(FIELD_COURSE_ID, courseId);


    db.insert(OFFERINGS_TABLE, null, values);

    // CLOSE THE DATABASE CONNECTION
    db.close();
  }

  public ArrayList < Offering > getAllOfferings() {
    ArrayList < Offering > offeringsList = new ArrayList < > ();
    SQLiteDatabase database = this.getReadableDatabase();
    //Cursor cursor = database.rawQuery(queryList, null);
    Cursor cursor = database.query(
      OFFERINGS_TABLE,
      new String[] {
        OFFERINGS_KEY_FIELD_ID,
        FIELD_SEMESTER_CODE,
        FIELD_COURSE_ID
      },
      null,
      null,
      null, null, null, null);

    //COLLECT EACH ROW IN THE TABLE
    if (cursor.moveToFirst()) {
      do {
        Course course = getCourse(cursor.getInt(2));
        //Instructor instructor = getInstructor(cursor.getInt(3));
        Offering offering = new Offering(cursor.getInt(0),
          cursor.getInt(1), course);

        offeringsList.add(offering);
      } while (cursor.moveToNext());
    }
    return offeringsList;
  }

  public void deleteOffering(Offering offering) {
    SQLiteDatabase db = this.getWritableDatabase();

    // DELETE THE TABLE ROW
    db.delete(OFFERINGS_TABLE, OFFERINGS_KEY_FIELD_ID + " = ?",
      new String[] {
        String.valueOf(offering.getCRN())
      });
    db.close();
  }

  public void deleteAllOfferings() {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(OFFERINGS_TABLE, null, null);
    db.close();
  }

  public void updateOffering(Offering offering) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();

    values.put(FIELD_SEMESTER_CODE, offering.getSemesterCode());
    values.put(FIELD_COURSE_ID, offering.getCourse().getId());


    db.update(OFFERINGS_TABLE, values, OFFERINGS_KEY_FIELD_ID + " = ?",
      new String[] {
        String.valueOf(offering.getCRN())
      });
    db.close();
  }

  public Offering getOffering(int crn) {
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.query(
      OFFERINGS_TABLE,
      new String[] {
        OFFERINGS_KEY_FIELD_ID,
        FIELD_SEMESTER_CODE,
        FIELD_COURSE_ID
      },
      OFFERINGS_KEY_FIELD_ID + "=?",
      new String[] {
        String.valueOf(crn)
      },
      null, null, null, null);

    if (cursor != null)
      cursor.moveToFirst();

    Course course = getCourse(cursor.getInt(2));
    //Instructor instructor = getInstructor(cursor.getInt(3));
    Offering offering = new Offering(cursor.getInt(0),
      cursor.getInt(1), course);


    db.close();
    return offering;
  }

  public Cursor getAllLabelsAsCursor() {
    String[] columns = new String[] {
      "rowid AS _id, *"
    }; // Need _id column for SimpleCursorAdapter
    return this.getWritableDatabase().query(COURSES_TABLE, columns, null, null, null, null, null);
  }



  public boolean importCoursesFromCSV(String csvFileName) {
    AssetManager manager = mContext.getAssets();
    InputStream inStream;
    try {
      inStream = manager.open(csvFileName);
    } catch (IOException e) {
      e.printStackTrace();
      return false;
    }

    BufferedReader buffer = new BufferedReader(new InputStreamReader(inStream));
    String line;
    try {
      while ((line = buffer.readLine()) != null) {
        String[] fields = line.split(",");
        if (fields.length != 4) {
          Log.d("OCC Course Finder", "Skipping Bad CSV Row: " + Arrays.toString(fields));
          continue;
        }
        int id = Integer.parseInt(fields[0].trim());
        String alpha = fields[1].trim();
        String number = fields[2].trim();
        String title = fields[3].trim();
        addCourse(new Course(id, alpha, number, title));
      }
    } catch (IOException e) {
      e.printStackTrace();
      return false;
    }
    return true;
  }



  public boolean importOfferingsFromCSV(String csvFileName) {
    AssetManager am = mContext.getAssets();
    InputStream inStream = null;
    try {
      inStream = am.open(csvFileName);
    } catch (IOException e) {
      e.printStackTrace();
    }

    BufferedReader buffer = new BufferedReader(new InputStreamReader(inStream));
    String line;
    try {
      while ((line = buffer.readLine()) != null) {
        String[] fields = line.split(",");
        if (fields.length != 4) {
          Log.d("OCC Course Finder", "Skipping Bad CSV Row: " + Arrays.toString(fields));
          continue;
        }
        int crn = Integer.parseInt(fields[0].trim());
        int semesterCode = Integer.parseInt(fields[1].trim());
        int courseId = Integer.parseInt(fields[2].trim());

        addOffering(crn, semesterCode, courseId);
      }
    } catch (IOException e) {
      e.printStackTrace();
      return false;
    }
    return true;
  }
}

package com.bar.example.myapplication;


import android.database.Cursor;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;


import android.text.Editable;
import android.text.TextWatcher;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.Spinner;
import android.widget.Toast;
import android.util.Log;
import java.util.ArrayList;
import java.util.List;

public class CourseSearchActivity extends AppCompatActivity {

    private DBHelper db;
    private List<Course> allCoursesList;
    private List<Offering> allOfferingsList;
    private List<Offering> filteredOfferingsList;
    public Button reset;
    private EditText courseTitleEditText;
    private Spinner ok;
    private ListView offeringsListView;

    // private selectedInstructorName selectedInstructorName;
    private InstructorSpinnerAdapter instructorSpinnerAdapter;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_course_search);

        deleteDatabase(DBHelper.DATABASE_NAME);
        db = new DBHelper(this);
        db.importCoursesFromCSV("courses.csv");

        db.importOfferingsFromCSV("offerings.csv");
        Button reset = (Button)  findViewById(R.id.resetButton);
        allOfferingsList = db.getAllOfferings();
        filteredOfferingsList = new ArrayList<>(allOfferingsList);

        allCoursesList = db.getAllCourses();

        courseTitleEditText = (EditText) findViewById(R.id.courseTitleEditText);
        courseTitleEditText.addTextChangedListener(courseTitleTextWatcher);

        ok = (Spinner) findViewById(R.id.spinner1);


       // offeringListAdapter = new OfferingListAdapter(this, R.layout.offering_list_item, filteredOfferingsList);
// ok.setAdapter(offeringListAdapter);
       instructorSpinnerAdapter = new InstructorSpinnerAdapter(this, R.layout.offering_list_item, filteredOfferingsList);

       ArrayAdapter<String> instructorSpinnerAdapter = new ArrayAdapter<String>
               (this, android.R.layout.simple_spinner_item, getAllCourse());
        ok.setAdapter(instructorSpinnerAdapter);

       ok.setOnItemSelectedListener(instructorSpinnerListener);


    }


    private String[] getAllCourse1() {
        String[] instructorNames = new String[allCoursesList.size() + 1];
        instructorNames[0] = "[Select Course]";
        for (int i = 1; i < instructorNames.length; i++) {
            instructorNames[i] = allCoursesList.get(i - 1).getTitle();
        }
        return instructorNames;
    }



    private ArrayList<String> getAllCourse() {
        ArrayList<String> instructorNames = new ArrayList<>();
        instructorNames.add("[Select Course]");
        for (int i = 0; i < allCoursesList.size(); i++) {

            instructorNames.add(allCoursesList.get(i).getTitle());
        }
        return instructorNames;
    }

    public TextWatcher courseTitleTextWatcher = new TextWatcher() {
        @Override
        public void beforeTextChanged(CharSequence charSequence, int i, int i1, int i2) {

        }
        @Override
        public void onTextChanged(CharSequence charSequence, int i, int i1, int i2) {
            String input = charSequence.toString().toLowerCase();
            ArrayAdapter adapter = (ArrayAdapter)ok.getAdapter();
            adapter.clear();
            if (input.equals("")) {
                adapter.addAll(getAllCourse());
            } else {
                Course course;
                for (int j = 0; j < allCoursesList.size(); j++) {
                    // If the course title starts with the user input,
                    // add it to the listAdapter
                    course = allCoursesList.get(j);
                    if (course.getTitle().toLowerCase().startsWith(input)) {
                        adapter.add(course.getTitle());
                    }
                }
            }
            adapter.notifyDataSetChanged();
            if(adapter.getCount() != 0) ok.setSelection(0);
        }
        @Override
        public void afterTextChanged(Editable editable) {


        }
    };

    public AdapterView.OnItemSelectedListener instructorSpinnerListener = new AdapterView.OnItemSelectedListener() {
        @Override
        public void onItemSelected(AdapterView<?> adapterView, View view, int i, long l) {
            String selectedInstructorName = adapterView.getItemAtPosition(i).toString();
            if(selectedInstructorName.equals("[Select Instructor]")) {
                instructorSpinnerAdapter.clear();
                for (Offering offering : allOfferingsList)
                    instructorSpinnerAdapter.add(offering);


            }
else{

                instructorSpinnerAdapter.clear();

            }

        }


        @Override
        public void onNothingSelected(AdapterView<?> adapterView) {
            adapterView.setSelection(0);

           // Toast.makeText(getApplicationContext(), "Why?", Toast.LENGTH_SHORT).show();
        }
    };




}