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"
What I have tried:
Database
<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;
static final String DATABASE_NAME = "OCC";
private static final int DATABASE_VERSION = 1;
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";
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);
}
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);
db.close();
}
public ArrayList < Course > getAllCourses() {
ArrayList < Course > coursesList = new ArrayList < > ();
SQLiteDatabase database = this.getReadableDatabase();
Cursor cursor = database.query(
COURSES_TABLE,
new String[] {
COURSES_KEY_FIELD_ID,
FIELD_ALPHA,
FIELD_NUMBER,
FIELD_TITLE
},
null,
null,
null, null, null, null);
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();
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;
}
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);
db.close();
}
public ArrayList < Offering > getAllOfferings() {
ArrayList < Offering > offeringsList = new ArrayList < > ();
SQLiteDatabase database = this.getReadableDatabase();
Cursor cursor = database.query(
OFFERINGS_TABLE,
new String[] {
OFFERINGS_KEY_FIELD_ID,
FIELD_SEMESTER_CODE,
FIELD_COURSE_ID
},
null,
null,
null, null, null, null);
if (cursor.moveToFirst()) {
do {
Course course = getCourse(cursor.getInt(2));
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();
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));
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, *"
};
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;
}
}
MAIN ACTIVITY
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 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);
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++) {
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);
}
};
}