Adit R Adit R - 3 months ago 23
Android Question

How to Search Sqlite databse using spinners and seekbars?

I have a set of spinners and seekbars on my main activity and want to use the user inputs from there to query a sq-lite Database and return the results in my Results Activity. Please can you help me with this.

I query the database in the DatabaseAccess.java Class

This is my activity_main.xml

<LinearLayout
android:layout_width="match_parent"
android:layout_height="match_parent"
xmlns:app="http://schemas.android.com/apk/res-auto"
android:orientation="vertical"
xmlns:android="http://schemas.android.com/apk/res/android"
android:background="#f5f5f5">
<android.support.v7.widget.Toolbar
android:id="@+id/toolbar"
android:layout_width="match_parent"
android:layout_height="?attr/actionBarSize"
android:background="@color/colorPrimary"
android:elevation="4dp"
app:titleTextColor="#FFFFFF"
android:theme="@style/ThemeOverlay.AppCompat.ActionBar"
app:popupTheme="@style/ThemeOverlay.AppCompat.Light"/>

<ScrollView
android:layout_width="match_parent"
android:layout_height="match_parent"
xmlns:app="http://schemas.android.com/apk/res-auto"
>



<android.support.v7.widget.CardView
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_margin="16dp"
android:elevation="4dp"

>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
android:padding="16dp">

<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:padding="16dp"
android:paddingTop="16dp"
android:textSize="16sp"

android:text="Hunger Level" />
<Spinner
android:id="@+id/hunger_spinner"
android:layout_width="wrap_content"
android:layout_height="wrap_content"


android:padding="16dp"

>

</Spinner>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:padding="16dp"
android:textSize="16sp"
android:text="Cuisine" />
<Spinner
android:id="@+id/cuisine_spinner"
android:layout_width="wrap_content"
android:layout_height="wrap_content"


android:padding="16dp"

>

</Spinner>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:padding="16dp"
android:textSize="16sp"
android:text="Preparation Time" />
<Spinner
android:id="@+id/time_spinner"
android:layout_width="wrap_content"
android:layout_height="wrap_content"


android:padding="16dp"

>

</Spinner>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:padding="16dp"
android:textSize="16sp"
android:text="Course" />
<Spinner
android:id="@+id/course_spinner"
android:layout_width="wrap_content"
android:layout_height="wrap_content"


android:padding="16dp"

>

</Spinner>

<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Salty"
android:padding="16dp"
android:textSize="16sp"
/>
<TextView
android:id="@+id/howSalty"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center_horizontal"
/>

<SeekBar
android:id="@+id/salty"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:thumbTint="@color/colorAccent"
android:progressTint="@color/colorAccent"
android:max="2"
android:progress="1"
/>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Sweet"
android:padding="16dp"
android:textSize="16sp"/>
<TextView
android:id="@+id/howSweet"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center_horizontal"/>
<SeekBar
android:thumbTint="@color/colorAccent"
android:progressTint="@color/colorAccent"
android:id="@+id/sweet"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:max="2"
android:progress="1"
/>


<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Spicy"
android:padding="16dp"
android:textSize="16sp"/>

<TextView
android:id="@+id/howSpicy"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center_horizontal"
/>
<SeekBar
android:thumbTint="@color/colorAccent"
android:progressTint="@color/colorAccent"
android:id="@+id/spicy"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:max="2"
android:progress="1"

/>

<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"

android:padding="16dp"
android:text="Special Requirements"

android:textSize="16sp" />

<GridLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"

android:layout_margin="16dp"
android:columnCount="2"
android:rowCount="3">



<CheckBox
android:layout_width="wrap_content"
android:buttonTint="@color/colorAccent"
android:layout_height="wrap_content"
android:text="Dairy Free"
android:textSize="16sp" />

<CheckBox
android:layout_width="wrap_content"

android:buttonTint="@color/colorAccent"
android:layout_height="wrap_content"
android:text="Nut Free"
android:textSize="16sp" />

<CheckBox
android:layout_width="wrap_content"
android:buttonTint="@color/colorAccent"
android:layout_height="wrap_content"
android:text="Egg Free"
android:textSize="16sp"
/>

<CheckBox
android:layout_width="wrap_content"
android:buttonTint="@color/colorAccent"
android:layout_height="wrap_content"
android:text="Vegetarian"
android:textSize="16sp" />
</GridLayout>
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"

android:layout_margin="16dp"
android:onClick="go"

android:text="go" />
</LinearLayout>
</android.support.v7.widget.CardView>

</ScrollView>
</LinearLayout>


This is The MainActivity.java

package com.example.rcadit.foodgenie;

import android.content.Intent;
import android.content.SharedPreferences;
import android.os.Bundle;
import android.preference.PreferenceManager;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.Toolbar;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.SeekBar;
import android.widget.Spinner;
import android.widget.TextView;


import butterknife.ButterKnife;
import butterknife.InjectView;




public class MainActivity extends AppCompatActivity implements AdapterView.OnItemSelectedListener {
private static final String TAG = "MyActivity";


public void onNothingSelected(AdapterView<?> parent) {
// Another interface callback
}

Toolbar toolbar;
@InjectView(R.id.salty)
SeekBar salty;
private String hungerValue;
private String cuisineValue;
private String prepValue;
private String courseValue;



@Override
protected void onCreate(Bundle savedInstanceState) {


super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
ButterKnife.inject(this);
initToolBar();
saltySlider();
spicySlider();
sweetSlider();


Spinner hunger_spinner = (Spinner) findViewById(R.id.hunger_spinner);
hunger_spinner.setOnItemSelectedListener(this);
ArrayAdapter<CharSequence> adapter = ArrayAdapter.createFromResource(this,
R.array.hunger_level, android.R.layout.simple_spinner_item);
adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
hunger_spinner.setAdapter(adapter);



Spinner cuisine_spinner = (Spinner) findViewById(R.id.cuisine_spinner);
cuisine_spinner.setOnItemSelectedListener(this);
ArrayAdapter<CharSequence> adapter2 = ArrayAdapter.createFromResource(this,
R.array.cuisine, android.R.layout.simple_spinner_item);
adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
cuisine_spinner.setAdapter(adapter2);

Spinner prep_spinner = (Spinner) findViewById(R.id.time_spinner);
prep_spinner.setOnItemSelectedListener(this);
ArrayAdapter<CharSequence> adapter3 = ArrayAdapter.createFromResource(this,
R.array.preparation_time, android.R.layout.simple_spinner_item);
adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
prep_spinner.setAdapter(adapter3);

Spinner course_spinner = (Spinner) findViewById(R.id.course_spinner);
course_spinner.setOnItemSelectedListener(this);
ArrayAdapter<CharSequence> adapter4 = ArrayAdapter.createFromResource(this,
R.array.course, android.R.layout.simple_spinner_item);
adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
course_spinner.setAdapter(adapter4);


}

@Override
public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
SharedPreferences prefs = PreferenceManager.getDefaultSharedPreferences(this);
Spinner spinner = (Spinner) parent;
// On selecting a spinner item
String value = parent.getItemAtPosition(position).toString();
if ("Low".equals(value) ) {
hungerValue = "1";

SharedPreferences.Editor editor = prefs.edit();
editor.putString("hungerData", hungerValue); //InputString: from the EditText
editor.apply();
}
if ("Medium".equals(value) ) {
hungerValue = "2";

SharedPreferences.Editor editor = prefs.edit();
editor.putString("hungerData", hungerValue); //InputString: from the EditText
editor.apply();
}
if ("High".equals(value) ) {
hungerValue = "3";

SharedPreferences.Editor editor = prefs.edit();
editor.putString("hungerData", hungerValue); //InputString: from the EditText
editor.apply();
}
if ("Under 10 mins".equals(value) ) {
prepValue = "< 10";

SharedPreferences.Editor editor = prefs.edit();
editor.putString("prepData", prepValue); //InputString: from the EditText
editor.apply();
}
if ("No Preparation".equals(value) ) {
prepValue = " = 0";

SharedPreferences.Editor editor = prefs.edit();
editor.putString("prepData", prepValue); //InputString: from the EditText
editor.apply();
}
if ("Under 20 mins".equals(value) ) {
prepValue = "< 20";

SharedPreferences.Editor editor = prefs.edit();
editor.putString("prepData", prepValue); //InputString: from the EditText
editor.apply();
}
if ("Under 30 mins".equals(value) ) {
prepValue = "< 30";

SharedPreferences.Editor editor = prefs.edit();
editor.putString("prepData", prepValue); //InputString: from the EditText
editor.apply();
}
if ("Over 30 mins".equals(value) ) {
prepValue = "> 30";

SharedPreferences.Editor editor = prefs.edit();
editor.putString("prepData", prepValue); //InputString: from the EditText
editor.apply();
}
if(spinner.getId() == R.id.cuisine_spinner)
{
cuisineValue = value;

SharedPreferences.Editor editor = prefs.edit();
editor.putString("cuisineData", cuisineValue); //InputString: from the EditText
editor.apply();
}

if(spinner.getId() == R.id.course_spinner)
{
courseValue = value;

SharedPreferences.Editor editor = prefs.edit();
editor.putString("courseData", courseValue); //InputString: from the EditText
editor.apply();
}

Log.d(TAG, "onItemSelected: " + value + " " + courseValue+ " "+ cuisineValue + " "+ prepValue + " "+ hungerValue);

}



public String getHungerValue(){
return hungerValue;
}


public String getCuisineValue(){
return cuisineValue;
}


public String getPrepValue(){
return prepValue;
}


public String getCourseValue(){
return courseValue;
}


private void saltySlider() {
salty.setOnSeekBarChangeListener(new SeekBar.OnSeekBarChangeListener() {
int progressChanged = 0;

@Override
public void onProgressChanged(SeekBar seekBar, int progress, boolean fromUser) {
progressChanged = progress * 50;
}

@Override
public void onStartTrackingTouch(SeekBar seekBar) {
}

@Override
public void onStopTrackingTouch(SeekBar seekBar) {
if (progressChanged < 100 / 3) {
TextView textView = (TextView) findViewById(R.id.howSalty);
textView.setHint("Not Salty");

}
if (progressChanged > 100 / 3 & progressChanged < 2 * (100 / 3)) {
TextView textView = (TextView) findViewById(R.id.howSalty);
textView.setHint("Salty");
}
if (progressChanged > 2 * (100 / 3)) {
TextView textView = (TextView) findViewById(R.id.howSalty);
textView.setHint("Very Salty");
}
}
});
}

private void sweetSlider() {
SeekBar sweet = (SeekBar) findViewById(R.id.sweet);
sweet.setOnSeekBarChangeListener(new SeekBar.OnSeekBarChangeListener() {
int progressChanged = 0;

@Override
public void onProgressChanged(SeekBar seekBar, int progress, boolean fromUser) {
progressChanged = progress * 50;
}

@Override
public void onStartTrackingTouch(SeekBar seekBar) {
}

@Override
public void onStopTrackingTouch(SeekBar seekBar) {
if (progressChanged < 100 / 3) {
TextView textView = (TextView) findViewById(R.id.howSweet);
textView.setHint("Not Sweet");

}
if (progressChanged > 100 / 3 & progressChanged < 2 * (100 / 3)) {
TextView textView = (TextView) findViewById(R.id.howSweet);
textView.setHint("Sweet");
}
if (progressChanged > 2 * (100 / 3)) {
TextView textView = (TextView) findViewById(R.id.howSweet);
textView.setHint("Very Sweet");
}
}
});
}

private void spicySlider() {
SeekBar spicy = (SeekBar) findViewById(R.id.spicy);
spicy.setOnSeekBarChangeListener(new SeekBar.OnSeekBarChangeListener() {
int progressChanged = 0;

@Override
public void onProgressChanged(SeekBar seekBar, int progress, boolean fromUser) {
progressChanged = progress * 50;
}

@Override
public void onStartTrackingTouch(SeekBar seekBar) {
}

@Override
public void onStopTrackingTouch(SeekBar seekBar) {
if (progressChanged < 100 / 3) {
TextView textView = (TextView) findViewById(R.id.howSpicy);
textView.setHint("Not Spicy");

}
if (progressChanged > 100 / 3 & progressChanged < 2 * (100 / 3)) {
TextView textView = (TextView) findViewById(R.id.howSpicy);
textView.setHint("Spicy");
}
if (progressChanged > 2 * (100 / 3)) {
TextView textView = (TextView) findViewById(R.id.howSpicy);
textView.setHint("Very Spicy");
}
}
});
}

public void initToolBar() {

toolbar = (Toolbar) findViewById(R.id.toolbar);

toolbar.setTitle(R.string.app_name);


setSupportActionBar(toolbar);
}
public void go(View view)
{
Intent intent = new Intent(MainActivity.this, ResultActivity.class);
startActivity(intent);
}



}


DatabaseAccess.java

package com.example.rcadit.foodgenie;

/**
* Created by rcadi on 8/22/2016.
*/


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

import java.util.ArrayList;
import java.util.List;

public class DatabaseAccess {
private SQLiteOpenHelper openHelper;
private SQLiteDatabase database;
private static DatabaseAccess instance;

/**
* Private constructor to aboid object creation from outside classes.
*
* @param context
*/
private DatabaseAccess(Context context) {
this.openHelper = new DatabaseOpenHelper(context);
}

/**
* Return a singleton instance of DatabaseAccess.
*
* @param context the Context
* @return the instance of DabaseAccess
*/
public static DatabaseAccess getInstance(Context context) {
if (instance == null) {
instance = new DatabaseAccess(context);
}
return instance;
}

/**
* Open the database connection.
*/
public void open() {
this.database = openHelper.getWritableDatabase();
}

/**
* Close the database connection.
*/
public void close() {
if (database != null) {
this.database.close();
}
}

/**
* Read all quotes from the database.
*
* @return a List of quotes
*/
public List<String> getResult() {
List<String> list = new ArrayList<>();
Cursor cursor = database.rawQuery("SELECT foodName from foodDB ", null);
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
list.add(cursor.getString(0));
cursor.moveToNext();
}
cursor.close();
return list;
}
}


Database Helper

package com.example.rcadit.foodgenie;

/**
* Created by rcadi on 8/22/2016.
*/

import android.content.Context;

import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

public class DatabaseOpenHelper extends SQLiteAssetHelper {
private static final String DATABASE_NAME = "foodfile.sqlite";
private static final int DATABASE_VERSION = 1;

public DatabaseOpenHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
}


ResultsActivity.java

package com.example.rcadit.foodgenie;

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.support.v7.widget.Toolbar;
import android.widget.ArrayAdapter;
import android.widget.ListView;

import com.example.rcadit.foodgenie.R;

import java.util.List;

import static com.example.rcadit.foodgenie.R.id.toolbar;

public class ResultActivity extends AppCompatActivity {
Toolbar toolbar;






private ListView listView;

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_result);
initToolBar();
this.listView = (ListView) findViewById(R.id.listView);
DatabaseAccess databaseAccess = DatabaseAccess.getInstance(this);
databaseAccess.open();
List<String> results = databaseAccess.getResult();
databaseAccess.close();

ArrayAdapter<String> adapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, results);
this.listView.setAdapter(adapter);
}
public void initToolBar() {

toolbar = (Toolbar) findViewById(R.id.toolbar);

toolbar.setTitle(R.string.app_name);


setSupportActionBar(toolbar);
}
}


And Finally the activity_results

<?xml version="1.0" encoding="utf-8"?>


<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
android:orientation="vertical"
android:layout_width="match_parent"
android:layout_height="match_parent">
<android.support.v7.widget.Toolbar
android:id="@+id/toolbar"
android:layout_width="match_parent"
android:layout_height="?attr/actionBarSize"
android:background="@color/colorPrimary"
android:elevation="4dp"
app:titleTextColor="#FFFFFF"
android:theme="@style/ThemeOverlay.AppCompat.ActionBar"
app:popupTheme="@style/ThemeOverlay.AppCompat.Light"/>
<FrameLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"

tools:context="com.example.rcadit.foodgenie.ResultActivity">

<android.support.v7.widget.CardView
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_margin="16dp"
android:elevation="4dp"

>

<ListView
android:id="@+id/listView"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_gravity="center" />
</android.support.v7.widget.CardView>

</FrameLayout>

</LinearLayout>


Thanks

Answer

I'm guessing you are having trouble passing the data to the new activity and then querying for that data?

The easiest way would be to add Extras to the intent and then pull them out in the new activity:

public void go(View view)
{
    Intent intent = new Intent(MainActivity.this, ResultActivity.class);
    intent.putExtra(ResultsActivity.HungerValueTag, hungerValue);
    intent.putExtra(ResultsActivity.CuisineValueTag, cuisineValue);
    intent.putExtra(ResultsActivity.PrepValueTag, prepValue);
    intent.putExtra(ResultsActivity.CourseValueTag, CourseValue);

    startActivity(intent);
}

In the next activity, pull the values out of the Intent Extras and then pass the values to the getResult() method.

ResultsActivity.java:

 public static final String HungerValueTag = "HungerValue";
 // Add the same for the other three..

    @Override
    protected void onCreate(Bundle savedInstanceState) {

        ...

        String hunger = getIntent().getStringValue(HungerValueTag);
        String cuisine = getIntent().getStringValue(CuisineValueTag);
        // get the other two


        List<String> results = databaseAccess.getResult(hunger, cuisine, prep, course);

        ...
    }

I have no idea what your column names or types are, so I'm guessing here. You will also need to make sure the sql is valid, possibly adding single quotes around the stings.

DatabaseAccess.java

 public List<String> getResult(String hunger, String cuisine, String prep, String course) {
    List<String> list = new ArrayList<>();
    String query = String.format("SELECT foodName from foodDB where hunger = %s and cuisine = %s and prep = %s and course = %s", hunger, cuisine, prep, course);

    Cursor cursor = database.rawQuery(query, null);
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
        list.add(cursor.getString(0));
        cursor.moveToNext();
    }
    cursor.close();
    return list;
}

I don't generally build SQL like this anymore, I always use an ORM, dealing with DB with objects.