Mecido
Reputation: 29
Second Spinner result is based on First Spinner selection from Sqlite(Magic Code)
I have got one table with two column (id,name). Toast should show the "name" details from sqlite while choosing the id from spinner.
Ex: Sqlite table 1,899,Chris and 2,890,David.
Whenever i select value 899 from spinner then Toast should display Chris and if i select spinner 890 then Toast should display David. Suggestion needed.
Code: SpinnerEx4Activity.Java
package com.bar.example.androidspinnerexample;
import java.util.ArrayList;
import java.util.List;
import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Spinner;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.Toast;
import android.support.v7.app.AppCompatActivity;
import android.database.sqlite.SQLiteDatabase;
import android.widget.Button;
import android.widget.CheckBox;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.SimpleCursorAdapter;
import java.util.ArrayList;
import android.content.Context;
import android.database.Cursor;
import android.content.Intent;
import java.util.HashMap;
import java.util.List;
import android.view.View.OnClickListener;
import android.util.Log;
import android.widget.TextView;
import android.widget.AdapterView;
import android.widget.ListView;
import android.widget.EditText;
import java.util.LinkedList;
import android.view.inputmethod.InputMethodManager;
public class SpinnerEx4Activity extends Activity implements
AdapterView.OnItemSelectedListener {
Spinner s1,s2;
Button btnAdd;
EditText inputLabel;
DatabaseHandler dbhndlr; //<<<<< Single instance for Database handler
Cursor spinner1csr;
Cursor spinner2csr; //<<<<< Cursor for spinner (close in onDestroy)
@Override
protected void onCreate(Bundle savedInstanceState) {
// TODO Auto-generated method stub
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_spinner_ex4);
s1 = (Spinner)findViewById(R.id.spinner1);
s2 = (Spinner)findViewById(R.id.spinner2);
btnAdd = (Button) findViewById(R.id.btn_add);
s1.setOnItemSelectedListener(this);
dbhndlr = new DatabaseHandler(this); //<<<< Instantiate Databasehandler
//loadSpinnerData(); //<<<< commented out
altLoadSpinnerData();
altLoadSpinnerData1();//<<<< Load via cursor
btnAdd.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View arg0) {
String label = inputLabel.getText().toString();
if (label.trim().length() > 0) {
// database handler commeneted out, use dbhndlr instance instead
// inserting new label into database
dbhndlr.insertLabel(label);
// making input filed text to blank
inputLabel.setText("");
// Hiding the keyboard
InputMethodManager imm = (InputMethodManager) getSystemService(Context.INPUT_METHOD_SERVICE);
imm.hideSoftInputFromWindow(inputLabel.getWindowToken(), 0);
// loading spinner with newly added data
//loadSpinnerData(); //<<<< commeneted out
altLoadSpinnerData();
altLoadSpinnerData1();
} else {
Toast.makeText(getApplicationContext(), "Please enter label name",
Toast.LENGTH_SHORT).show();
}
}
});
}
// New method to utilise Cursor adapter
private void altLoadSpinnerData() {
spinner1csr = dbhndlr.getAllLabelsAsCursor();
SimpleCursorAdapter sca = new SimpleCursorAdapter(this,
android.R.layout.simple_list_item_1, // Layout to show 2 items
spinner1csr, // Cursor
new String[]{DatabaseHandler.KEY_NAME},
new int[]{android.R.id.text1},// Views into which data is shown
0
);
s1.setAdapter(sca);
}
private void altLoadSpinnerData1 () {
// get the cursor
spinner2csr = dbhndlr.getAllLabelsAsCursor();
// Instantiaie Simple Cursor Adapter
SimpleCursorAdapter sca = new SimpleCursorAdapter(this,
android.R.layout.simple_list_item_2, // Layout to show 2 items
spinner2csr, // Cursor
new String[]{DatabaseHandler.KEY_ID}, // Source data
new int[]{android.R.id.text2}, // Views into which data is shown
0
);
s2.setAdapter(sca);
s2.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
@Override
public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
Toast.makeText(parent.getContext(),
"You Selected: " + id + " " +
spinner2csr.getString(
spinner2csr.getColumnIndex(DatabaseHandler.KEY_ID)),
Toast.LENGTH_SHORT
).show();
}
@Override
public void onNothingSelected(AdapterView<?> parent) {
// TODO Auto-generated method stub
}
});
}}
And Databasehandler.java
package com.bar.example.androidspinnerexample;
import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DatabaseHandler extends SQLiteOpenHelper {
// Database Version
private static final int DATABASE_VERSION = 1;
// Database Name
private static final String DATABASE_NAME = "spinnerExample";
// Labels table name
private static final String TABLE_LABELS = "labels";
// Labels Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
public DatabaseHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
// Category table create query
String CREATE_CATEGORIES_TABLE = "CREATE TABLE " + TABLE_LABELS + "("
+ KEY_ID + " TEXT," + KEY_NAME + " TEXT)";
db.execSQL(CREATE_CATEGORIES_TABLE);
}
// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_LABELS);
// Create tables again
onCreate(db);
}
/**
* Inserting new lable into lables table
* */
public void insertLabel(String label){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, label);
// Inserting Row
db.insert(TABLE_LABELS, null, values);
db.close(); // Closing database connection
}
/**
* Getting all labels
* returns list of labels
* */
public List<String> getAllLabels(){
List<String> labels = new ArrayList<String>();
// Select All Query
String selectQuery = "SELECT * FROM " + TABLE_LABELS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
labels.add(cursor.getString(0));
} while (cursor.moveToNext());
}
// closing connection
cursor.close();
db.close();
// returning lables
return labels;
}
}
And activity_spinner_ex4.xml is
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:orientation="vertical" >
<!-- Label -->
<TextView
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:padding="8dip"
android:text="@string/lblAcc" />
<!-- Spinner Dropdown -->
<Spinner
android:id="@+id/spinner1"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:layout_marginLeft="8dip"
android:layout_marginRight="8dip"
android:layout_marginTop="10dip"
/>
<!-- Select Label -->
<TextView
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:padding="8dip"
android:text="@string/lblSubAcc" />
<!-- Spinner Dropdown -->
<Spinner
android:id="@+id/spinner2"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:layout_marginTop="10dip"
android:layout_marginLeft="8dip"
android:layout_marginRight="8dip"
/>
<EditText android:id="@+id/input_label"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:layout_marginLeft="8dip"
android:layout_marginRight="8dip"/>
<!-- Add Button -->
<Button android:id="@+id/btn_add"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Add Label"
android:layout_marginLeft="8dip"
android:layout_marginTop="8dip"/>
</LinearLayout>
Inserting the spinner and checkbox value into another table. i am able to insert all data. but all checkbox text is saving instead of selected one.could you assist.
main activity.
btnAdd.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View arg0) {
//String label = inputLabel.getText().toString();
String SaveString="No";
DatabaseHandler db = new DatabaseHandler(getApplicationContext());
String message1= ((Cursor) s1.getSelectedItem()).getString(2);
String message2= ((Cursor) s2.getSelectedItem()).getString(1);
String message = inputLabel.getText().toString();
// String message1 = s1.getSelectedItem().toString();
// String message2 = s2.getSelectedItem().toString();
String message10 = s3.getSelectedItem().toString();
String message4 = ck1.getText().toString();
String message5 = ck2.getText().toString();
String message6 = ck3.getText().toString();
String message7 = ck4.getText().toString();
String message9 = ck6.getText().toString();
String message3 = ck7.getText().toString();
String message8 = ck8.getText().toString();
db.insertLabel(message1,message2,message5,message6,message7,message9,message3,message4,message8,message10);
if (ck1.isChecked())
{ SaveString="Yes";
}
else
{ SaveString="No";
}
if (ck2.isChecked())
{ SaveString="Yes";
}
else
{ SaveString="No";
}
if (message.trim().length() > 0) {
// database handler commeneted out, use dbhndlr instance instead
// inserting new label into database
// making input filed text to blank
inputLabel.setText("");
// Hiding the keyboard
InputMethodManager imm = (InputMethodManager) getSystemService(Context.INPUT_METHOD_SERVICE);
imm.hideSoftInputFromWindow(inputLabel.getWindowToken(), 0);
// loading spinner with newly added data
spinner1csr = dbhndlr.getAllLabelsAsCursor();
spinner2csr = dbhndlr.getByRowid(spinner1_selected);
sca.swapCursor(spinner1csr);
sca2.swapCursor(spinner2csr);
} else {
Toast.makeText(getApplicationContext(), "Please enter label name",
Toast.LENGTH_SHORT).show();
}
}
});
}Database .
public void insertLabel(String message1, String message2,String message3,String message4,String message5,String message6,String message7,String message8,String message9,String message10){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_1, message1);
values.put(KEY_2, message2);
values.put(KEY_10,message10);
values.put(KEY_3,message3);
values.put(KEY_4,message4);
values.put(KEY_5,message5);
values.put(KEY_6,message6);
values.put(KEY_7,message7);
values.put(KEY_9,message9);
values.put(KEY_8,message8);
// Inserting Row
db.insert(TABLE_LABELS2, null, values);
db.close(); // Closing database connection
}Upvotes: 0
Views: 1184
Answers (3)
MikeT
Reputation: 56953
Alternative/Improved SpinnerEx4Activity.Java
public class SpinnerEx4Activity extends Activity {
Spinner s1,s2;
Button btnAdd;
EditText inputLabel;
DatabaseHandler dbhndlr;
Cursor spinner1csr, spinner2csr;
SimpleCursorAdapter sca, sca2;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
s1 = (Spinner)findViewById(R.id.spinner1);
s2 = (Spinner)findViewById(R.id.spinner2);
btnAdd = (Button) findViewById(R.id.btn_add);
inputLabel = (EditText) findViewById(R.id.input_label);
dbhndlr = new DatabaseHandler(this);
// If no data in database then load data for testing purposes only
if (DatabaseUtils.queryNumEntries(
dbhndlr.getWritableDatabase(),
DatabaseHandler.TABLE_LABELS) < 1)
{
dbhndlr.insertlabel("899","Chris");
dbhndlr.insertlabel("890","David");
}
// Get Cursors for Spinners
spinner1csr = dbhndlr.getAllLabelsAsCursor();
spinner2csr = dbhndlr.getAllLabelsAsCursor();
//Setup Adapter for Spinner 1
sca = new SimpleCursorAdapter(this,
android.R.layout.simple_list_item_1,spinner1csr,
new String[]{DatabaseHandler.KEY_ID},
new int[]{android.R.id.text1},
0
);
//Steup Adapter for Spinner2
sca2 = new SimpleCursorAdapter(this,
android.R.layout.simple_list_item_1,
spinner2csr,
new String[]{DatabaseHandler.KEY_NAME},
new int[]{android.R.id.text1},
0
);
// Set the Adapters to the Spinners
s1.setAdapter(sca);
s2.setAdapter(sca2);
// Set Spinner1 OnSelectedItemListener
s1.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
@Override
public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
Toast.makeText(parent.getContext(),
"You Selected: " + id + " - " +
spinner1csr.getString(
spinner1csr.getColumnIndex(DatabaseHandler.KEY_NAME)),
Toast.LENGTH_SHORT).show();
}
@Override
public void onNothingSelected(AdapterView<?> parent) {
}
});
// Set Spinner2 OnSelectedItemListener
s2.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
@Override
public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
Toast.makeText(parent.getContext(),
"You Selected: " + id + " " +
spinner2csr.getString(
spinner2csr.getColumnIndex(DatabaseHandler.KEY_ID)),
Toast.LENGTH_SHORT).show();
}
@Override
public void onNothingSelected(AdapterView<?> parent) {
}
});
btnAdd.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View arg0) {
String label = inputLabel.getText().toString();
if (label.trim().length() > 0) {
// database handler commeneted out, use dbhndlr instance instead
// inserting new label into database
dbhndlr.insertLabel(label);
// making input filed text to blank
inputLabel.setText("");
// Hiding the keyboard
InputMethodManager imm = (InputMethodManager) getSystemService(Context.INPUT_METHOD_SERVICE);
imm.hideSoftInputFromWindow(inputLabel.getWindowToken(), 0);
// loading spinner with newly added data
//loadSpinnerData(); //<<<< commeneted out
spinner1csr = dbhndlr.getAllLabelsAsCursor();
spinner2csr = dbhndlr.getAllLabelsAsCursor();
sca.swapCursor(spinner1csr);
sca2.swapCursor(spinner2csr);
//altLoadSpinnerData();
//altLoadSpinner2Data();
} else {
Toast.makeText(getApplicationContext(), "Please enter label name",
Toast.LENGTH_SHORT).show();
}
}
});
}
}
Notes
- No longer implements AdapterView.OnItemSelectedListener
- No longer creates new Instance of adapters reuses the existing ones
- Due to above uses adapter.swapCursor(new_cursor) to keep spinners in sync with data entered.
- 1st Spinner will display blank lines, 2nd will result in Toast showing id followed bu null.
- Both of the above are because when entering a new label, no ID (not rowid) is assigned. Due to adding only using 'dbhndlr.insertLabel(label);' thus the KEY_ID column is null. I have no idea how you assign ID's to Labels(names). However, notice how I created an alternative insertlabel method that takes ID and NAME/LABEL parameters.
Amended as per comment
here i am talking now about select spinner s1 value(ex:123) then spinner s2 should different value(Mike Trae) as per table.
i.e. spinners should exclude the selected value in the other spinner (spinners are inter-dependant)
- Note! I changed to display names in both spinners as id's can be null and confusing (see previous notes)
SpinnerEx4Activity
public class SpinnerEx4Activity extends Activity {
Spinner s1,s2;
Button btnAdd;
EditText inputLabel;
DatabaseHandler dbhndlr;
Cursor spinner1csr, spinner2csr;
SimpleCursorAdapter sca, sca2;
long spinner1_selected = 0, spinner2_selected = 0;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
s1 = (Spinner)findViewById(R.id.spinner1);
s2 = (Spinner)findViewById(R.id.spinner2);
btnAdd = (Button) findViewById(R.id.btn_add);
inputLabel = (EditText) findViewById(R.id.input_label);
dbhndlr = new DatabaseHandler(this);
// If no data in database then load data for testing purposes only
if (DatabaseUtils.queryNumEntries(
dbhndlr.getWritableDatabase(),
DatabaseHandler.TABLE_LABELS) < 1)
{
dbhndlr.insertlabel("899","Chris");
dbhndlr.insertlabel("890","David");
}
// Get Cursors for Spinners
spinner1csr = dbhndlr.getAllLabelsExceptedSelected(spinner2_selected);
//Setup Adapter for Spinner 1
sca = new SimpleCursorAdapter(this,
android.R.layout.simple_list_item_1,spinner1csr,
new String[]{DatabaseHandler.KEY_NAME},
new int[]{android.R.id.text1},
0
);
// Set the Adapters to the Spinners
s1.setAdapter(sca);
// Set Spinner1 OnSelectedItemListener
s1.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
@Override
public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
Toast.makeText(parent.getContext(),
"You Selected: " + id + " - " +
spinner1csr.getString(
spinner1csr.getColumnIndex(DatabaseHandler.KEY_NAME)) +
" - " + spinner1csr.getString(spinner1csr.getColumnIndex(DatabaseHandler.KEY_ID))
,
Toast.LENGTH_SHORT).show();
spinner1_selected = id;
spinner2csr = dbhndlr.getAllLabelsExceptedSelected(spinner1_selected);
sca2.swapCursor(spinner2csr);
}
@Override
public void onNothingSelected(AdapterView<?> parent) {
}
});
//Steup Adapter for Spinner2
spinner2csr = dbhndlr.getAllLabelsExceptedSelected(spinner1_selected);
sca2 = new SimpleCursorAdapter(this,
android.R.layout.simple_list_item_1,
spinner2csr,
new String[]{DatabaseHandler.KEY_NAME},
new int[]{android.R.id.text1},
0
);
s2.setAdapter(sca2);
// Set Spinner2 OnSelectedItemListener
s2.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
@Override
public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
Toast.makeText(parent.getContext(),
"You Selected: " + id + " - " +
spinner2csr.getString(
spinner2csr.getColumnIndex(DatabaseHandler.KEY_NAME)) +
" - " + spinner2csr.getString(spinner2csr.getColumnIndex(DatabaseHandler.KEY_ID)),
Toast.LENGTH_SHORT).show();
spinner2_selected = id;
spinner1csr = dbhndlr.getAllLabelsExceptedSelected(spinner2_selected);
sca.swapCursor(spinner1csr);
}
@Override
public void onNothingSelected(AdapterView<?> parent) {
}
});
btnAdd.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View arg0) {
String label = inputLabel.getText().toString();
if (label.trim().length() > 0) {
// database handler commeneted out, use dbhndlr instance instead
// inserting new label into database
dbhndlr.insertLabel(label);
// making input filed text to blank
inputLabel.setText("");
// Hiding the keyboard
InputMethodManager imm = (InputMethodManager) getSystemService(Context.INPUT_METHOD_SERVICE);
imm.hideSoftInputFromWindow(inputLabel.getWindowToken(), 0);
// loading spinner with newly added data
//loadSpinnerData(); //<<<< commeneted out
spinner1csr = dbhndlr.getAllLabelsExceptedSelected(spinner2_selected);
spinner2csr = dbhndlr.getAllLabelsExceptedSelected(spinner1_selected);
sca.swapCursor(spinner1csr);
sca2.swapCursor(spinner2csr);
//altLoadSpinnerData();
//altLoadSpinner2Data();
} else {
Toast.makeText(getApplicationContext(), "Please enter label name",
Toast.LENGTH_SHORT).show();
}
}
});
}
@Override
public void onDestroy() {
spinner1csr.close();
spinner2csr.close();
super.onDestroy();
}
}
DatabaseHandler
public class DatabaseHandler extends SQLiteOpenHelper {
// Database Version
private static final int DATABASE_VERSION = 1;
// Database Name
public static final String DATABASE_NAME = "spinnerExample";
// Labels table name
public static final String TABLE_LABELS = "labels"; //<<<< Made public
// Labels Table Columns names
public static final String KEY_ID = "id"; //<<<< Made public
public static final String KEY_NAME = "name"; //<<<< made public
public DatabaseHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
// Category table create query
String CREATE_CATEGORIES_TABLE = "CREATE TABLE " + TABLE_LABELS + "("
+ KEY_ID + " TEXT," + KEY_NAME + " TEXT)";
db.execSQL(CREATE_CATEGORIES_TABLE);
}
// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_LABELS);
// Create tables again
onCreate(db);
}
/**
* Inserting new lable into lables table
* */
public void insertLabel(String label){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, label);
// Inserting Row
db.insert(TABLE_LABELS, null, values);
db.close(); // Closing database connection
}
// Added for adding new data
public void insertlabel(String id, String label) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put(KEY_ID,id);
cv.put(KEY_NAME,label);
db.insert(TABLE_LABELS,null,cv);
db.close();
}
// Added to get Cursor for Simple CursorAdapter
public Cursor getAllLabelsAsCursor() {
String[] columns = new String[]{"rowid AS _id, *"}; // Need _id column for SimpleCursorAdapter
return this.getWritableDatabase().query(TABLE_LABELS,columns,null,null,null,null,null);
}
public Cursor getAllLabelsExceptedSelected(long selected) {
String[] columns = new String[]{"rowid AS _id, *"};
String whereclause = "rowid <> ?";
String[] whereargs = new String[]{String.valueOf(selected)};
return this.getWritableDatabase().query(TABLE_LABELS,
columns,
whereclause,
whereargs,
null,
null,
null
);
}
}
Amendments after comments :-
But i want it one-way only Spinner1 selection first column(Database) should show in spinner 2(Second Column)database.
No need from spinner 2 to spinner.
1.Also note that my table having 1000 datas*(rows)*.
ex: Database:
{ dbhndlr.insertlabel("9001234","Chris") dbhndlr.insertlabel("9001235","Cdedd"); dbhndlr.insertlabel("9003457","Dcdtt"); dbhndlr.insertlabel("9001231","Chrdis"); dbhndlr.insertlabel("9003451","Ddavid");}ex: If spinner 1 select 9001231 then spinner 2 should show Chrdis and if spinner 1 selected 9001234 then spinner2 should show Chris.
1) Added new method getByRowid to Databasehandler.java :-
public class DatabaseHandler extends SQLiteOpenHelper {
// Database Version
public static final int DATABASE_VERSION = 1;
// Database Name
public static final String DATABASE_NAME = "spinnerExample";
// Labels table name
public static final String TABLE_LABELS = "labels"; //<<<< Made public
// Labels Table Columns names
public static final String KEY_ID = "id"; //<<<< Made public
public static final String KEY_NAME = "name"; //<<<< made public
public DatabaseHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
// Category table create query
String CREATE_CATEGORIES_TABLE = "CREATE TABLE " + TABLE_LABELS + "("
+ KEY_ID + " TEXT," + KEY_NAME + " TEXT)";
db.execSQL(CREATE_CATEGORIES_TABLE);
}
// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_LABELS);
// Create tables again
onCreate(db);
}
/**
* Inserting new lable into lables table
* */
public void insertLabel(String label){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, label);
// Inserting Row
db.insert(TABLE_LABELS, null, values);
db.close(); // Closing database connection
}
// Added for adding new data
public void insertlabel(String id, String label) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put(KEY_ID,id);
cv.put(KEY_NAME,label);
db.insert(TABLE_LABELS,null,cv);
db.close();
}
// Added to get Cursor for Simple CursorAdapter
public Cursor getAllLabelsAsCursor() {
String[] columns = new String[]{"rowid AS _id, *"}; // Need _id column for SimpleCursorAdapter
return this.getWritableDatabase().query(TABLE_LABELS,columns,null,null,null,null,null);
}
public Cursor getAllLabelsExceptedSelected(long selected) {
String[] columns = new String[]{"rowid AS _id, *"};
String whereclause = "rowid <> ?";
String[] whereargs = new String[]{String.valueOf(selected)};
return this.getWritableDatabase().query(TABLE_LABELS,
columns,
whereclause,
whereargs,
null,
null,
null
);
}
public Cursor getByRowid(long id) {
String[] columns = new String[]{"rowid AS _id, *"};
return this.getWritableDatabase().query(
TABLE_LABELS,
columns,
"rowid=?",
new String[]{String.valueOf(id)},
null,null,null
);
}
}
The Amended SpinnerEx4Acitivity.java :-
public class MainActivity extends Activity {
Spinner s1,s2;
Button btnAdd;
EditText inputLabel;
DatabaseHandler dbhndlr;
Cursor spinner1csr, spinner2csr;
SimpleCursorAdapter sca, sca2;
long spinner1_selected = 0;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
s1 = (Spinner)findViewById(R.id.spinner1);
s2 = (Spinner)findViewById(R.id.spinner2);
btnAdd = (Button) findViewById(R.id.btn_add);
inputLabel = (EditText) findViewById(R.id.input_label);
dbhndlr = new DatabaseHandler(this);
// If no data in database then load data for testing purposes only
if (DatabaseUtils.queryNumEntries(
dbhndlr.getWritableDatabase(),
DatabaseHandler.TABLE_LABELS) < 1)
{
dbhndlr.insertlabel("899","Chris");
dbhndlr.insertlabel("890","David");
}
// Get Cursors for Spinners
spinner1csr = dbhndlr.getAllLabelsAsCursor();
//Setup Adapter for Spinner 1
sca = new SimpleCursorAdapter(this,
android.R.layout.simple_list_item_1,spinner1csr,
new String[]{DatabaseHandler.KEY_ID},
new int[]{android.R.id.text1},
0
);
// Set the Adapters to the Spinners
s1.setAdapter(sca);
// Set Spinner1 OnSelectedItemListener
s1.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
@Override
public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
Toast.makeText(parent.getContext(),
"You Selected: " + id + " - " +
spinner1csr.getString(
spinner1csr.getColumnIndex(DatabaseHandler.KEY_NAME)) +
" - " + spinner1csr.getString(spinner1csr.getColumnIndex(DatabaseHandler.KEY_ID))
,
Toast.LENGTH_SHORT).show();
spinner1_selected = id;
spinner2csr = dbhndlr.getByRowid(spinner1_selected);
sca2.swapCursor(spinner2csr);
}
@Override
public void onNothingSelected(AdapterView<?> parent) {
}
});
//Steup Adapter for Spinner2
spinner2csr = dbhndlr.getByRowid(spinner1_selected);
sca2 = new SimpleCursorAdapter(this,
android.R.layout.simple_list_item_1,
spinner2csr,
new String[]{DatabaseHandler.KEY_NAME},
new int[]{android.R.id.text1},
0
);
s2.setAdapter(sca2);
// Set Spinner2 OnSelectedItemListener
/* Not needed
s2.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
@Override
public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
Toast.makeText(parent.getContext(),
"You Selected: " + id + " - " +
spinner2csr.getString(
spinner2csr.getColumnIndex(DatabaseHandler.KEY_NAME)) +
" - " + spinner2csr.getString(spinner2csr.getColumnIndex(DatabaseHandler.KEY_ID)),
Toast.LENGTH_SHORT).show();
spinner2_selected = id;
spinner1csr = dbhndlr.getAllLabelsExceptedSelected(spinner2_selected);
sca.swapCursor(spinner1csr);
}
@Override
public void onNothingSelected(AdapterView<?> parent) {
}
});
*/
btnAdd.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View arg0) {
String label = inputLabel.getText().toString();
if (label.trim().length() > 0) {
// database handler commeneted out, use dbhndlr instance instead
// inserting new label into database
dbhndlr.insertLabel(label);
// making input filed text to blank
inputLabel.setText("");
// Hiding the keyboard
InputMethodManager imm = (InputMethodManager) getSystemService(Context.INPUT_METHOD_SERVICE);
imm.hideSoftInputFromWindow(inputLabel.getWindowToken(), 0);
// loading spinner with newly added data
spinner1csr = dbhndlr.getAllLabelsAsCursor();
spinner2csr = dbhndlr.getByRowid(spinner1_selected);
sca.swapCursor(spinner1csr);
sca2.swapCursor(spinner2csr);
} else {
Toast.makeText(getApplicationContext(), "Please enter label name",
Toast.LENGTH_SHORT).show();
}
}
});
}
@Override
public void onDestroy() {
spinner1csr.close();
spinner2csr.close();
super.onDestroy();
}
}
Upvotes: 1
MikeT
Reputation: 56953
There are a few ways in which this could be achieved.
The easiest to implement would be to add another method (e.g. getNameById) to Databasehandler.java that will return the name from the labels table and to then call this within the Spinner's overridden onItemSelected method.
public String getNameById(String id) {
String rv = "No Name Found";
SQLiteDatabase db = this.getReadableDatabase();
Cursor csr = db.query(TABLE_LABELS,
null,
KEY_ID + "=?",
new String[]{id},
null,null,null,null
);
if (csr.moveToFirst()) {
rv = csr.getString(csr.getColumnIndex(KEY_NAME));
}
csr.close();
db.close();
return rv;
}
- This assumes that id will be unqiue
- Using
getColumnIndex(column_name)is more flexible and less likely to result in errors than using hard coded column offsets.
The Overridden onItemSelected method in SpinnerEx4Activity.Java could then be :-
@Override
public void onItemSelected(AdapterView<?> parent, View view, int arg2, long id) {
// On selecting a spinner item
DatabaseHandler db = new DatabaseHandler(parent.getContext());
String label = parent.getItemAtPosition(arg2).toString();
// Showing selected spinner item
Toast.makeText(parent.getContext(), "You selected: " + id + " " + db.getNameById(label),Toast.LENGTH_LONG).show();
}
Alternative Approach - using a Cursor Adapter
An alternative approach would be to use a CursorAdapter (SimpleCursorAdapter), which would then allow direct access to all columns.
1) This would require a method in DatabaseHandler.java that returns a cursor. Noting that a column named _id must exist. As the table is not defined with the WITHOUT ROWID keywords then a column named rowid exists (not normally visible). So we can use rowid AS _id to get this. The following could be added to DatabaseHandler.java (note other changes made so see complete DatabaseHandler.java below.)
// Added to get Cursor for Simple CursorAdapter
public Cursor getAllLabelsAsCursor() {
String[] columns = new String[]{"rowid AS _id, *"}; // Need _id column for SimpleCursorAdapter
return this.getWritableDatabase().query(TABLE_LABELS,columns,null,null,null,null,null);
}
2) A call, in the Spinner's Activity, to the the new method getAllLabelsAsCursor to retrieve the cursor. (see below)
3) A replacement for the loadSpinner method that utilises a SimpleCursorAdapter.
In addition to the above a few changes have also been made (see comments with //<<<<).
The full working code is :-
Databasehandler.java
public class DatabaseHandler extends SQLiteOpenHelper {
// Database Version
private static final int DATABASE_VERSION = 1;
// Database Name
private static final String DATABASE_NAME = "spinnerExample";
// Labels table name
public static final String TABLE_LABELS = "labels"; //<<<< Made public
// Labels Table Columns names
public static final String KEY_ID = "id"; //<<<< Made public
public static final String KEY_NAME = "name"; //<<<< made public
public DatabaseHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
// Category table create query
String CREATE_CATEGORIES_TABLE = "CREATE TABLE " + TABLE_LABELS + "("
+ KEY_ID + " TEXT," + KEY_NAME + " TEXT)";
db.execSQL(CREATE_CATEGORIES_TABLE);
}
// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_LABELS);
// Create tables again
onCreate(db);
}
/**
* Inserting new lable into lables table
* */
public void insertLabel(String label){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_NAME, label);
// Inserting Row
db.insert(TABLE_LABELS, null, values);
db.close(); // Closing database connection
}
//<<<< Added for adding new data for testing
public void insertlabel(String id, String label) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put(KEY_ID,id);
cv.put(KEY_NAME,label);
db.insert(TABLE_LABELS,null,cv);
db.close();
}
/**
* Getting all labels
* returns list of labels
* */
public List<String> getAllLabels(){
List<String> labels = new ArrayList<String>();
// Select All Query
String selectQuery = "SELECT * FROM " + TABLE_LABELS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
labels.add(cursor.getString(0));
} while (cursor.moveToNext());
}
// closing connection
cursor.close();
db.close();
// returning lables
return labels;
}
// Added to get Cursor for Simple CursorAdapter
public Cursor getAllLabelsAsCursor() {
String[] columns = new String[]{"rowid AS _id, *"}; // Need _id column for SimpleCursorAdapter
return this.getWritableDatabase().query(TABLE_LABELS,columns,null,null,null,null,null);
}
}
SpinnerEx4Activity.Java
public class SpinnerEx4Activity extends Activity implements
AdapterView.OnItemSelectedListener {
Spinner s1,s2;
Button btnAdd;
EditText inputLabel;
DatabaseHandler dbhndlr; //<<<<< Single instance for Database handler
Cursor spinner1csr; //<<<<< Cursor for spinner (close in onDestroy)
@Override
protected void onCreate(Bundle savedInstanceState) {
// TODO Auto-generated method stub
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
s1 = (Spinner)findViewById(R.id.spinner1);
s2 = (Spinner)findViewById(R.id.spinner2);
btnAdd = (Button) findViewById(R.id.btn_add);
inputLabel = (EditText) findViewById(R.id.input_label);
s1.setOnItemSelectedListener(this);
dbhndlr = new DatabaseHandler(this); //<<<< Instantiate Databasehandler
//loadSpinnerData(); //<<<< commented out
// If no data in database then load data
if (DatabaseUtils.queryNumEntries(dbhndlr.getWritableDatabase(),DatabaseHandler.TABLE_LABELS) < 1) {
dbhndlr.insertlabel("899","Chris");
dbhndlr.insertlabel("890","David");
}
altLoadSpinnerData(); //<<<< Load via cursor
btnAdd.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View arg0) {
String label = inputLabel.getText().toString();
if (label.trim().length() > 0) {
// database handler commeneted out, use dbhndlr instance instead
// inserting new label into database
dbhndlr.insertLabel(label);
// making input filed text to blank
inputLabel.setText("");
// Hiding the keyboard
InputMethodManager imm = (InputMethodManager) getSystemService(Context.INPUT_METHOD_SERVICE);
imm.hideSoftInputFromWindow(inputLabel.getWindowToken(), 0);
// loading spinner with newly added data
//loadSpinnerData(); //<<<< commeneted out
altLoadSpinnerData();
} else {
Toast.makeText(getApplicationContext(), "Please enter label name",
Toast.LENGTH_SHORT).show();
}
}
});
}
// New method to utilise Cursor adapter
private void altLoadSpinnerData() {
// get the cursor
spinner1csr = dbhndlr.getAllLabelsAsCursor();
// Instantiaie Simple Cursor Adapter
SimpleCursorAdapter sca = new SimpleCursorAdapter(this,
android.R.layout.simple_list_item_2, // Layout to show 2 items
spinner1csr, // Cursor
new String[]{DatabaseHandler.KEY_ID,DatabaseHandler.KEY_NAME}, // Source data
new int[]{android.R.id.text1,android.R.id.text2}, // Views into which data is shown
0
);
//
//sca.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
s1.setAdapter(sca);
}
private void loadSpinnerData() {
// Spinner Drop down elements
List<String> lables = dbhndlr.getAllLabels();
// Creating adapter for spinner
ArrayAdapter<String> dataAdapter = new ArrayAdapter<String>(this,
android.R.layout.simple_spinner_item, lables);
// Drop down layout style - list view with radio button
dataAdapter
.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
// attaching data adapter to spinner
s1.setAdapter(dataAdapter);
}
@Override
public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
// Note id will be the same as position for ArrayAdapter but for CursorAdapter
// will be the _id column (in this case rowid)
// On selecting a spinner item
//String label = parent.getItemAtPosition(arg2).toString(); //<<<< no need
// Showing selected spinner item
Toast.makeText(parent.getContext(),
"You selected: " + id +
" " +
spinner1csr.getString(
spinner1csr.getColumnIndex(
DatabaseHandler.KEY_NAME)
) ,
Toast.LENGTH_LONG).show();
}
@Override
public void onNothingSelected(AdapterView<?> arg0) {
// TODO Auto-generated method stub
}
}
Notes
- The above includes a check for no data and loads the two rows mentioned above.
- Making the table and column names public allows them to be accessed (much better to use column names rather than offsets for cursor get???? methods).
- Code has been changed to utilise a single instance of the DatabaseHandler, replacing the creation of multiple instances of
DatabaseHandler db = new DatabaseHandler(getApplicationContext()); - The Cursor should be closed, override the
onDestroymethod. However, you don't need to close the Database itself although doing so has been left in. - The Spinner shows two items in both the selected view and the drop down view (commented out the setDropdownViewresource, you may wish to experiment, perhaps provide your own layout(s)).
Example Screen Shot(s) :-
- Note labels (LabelAcc and LabelSubACc) are as such due to me overriding the String resources with text, rather than guess the values of the String resources.
Edit re comment
Spinner1 only working.spinner 2 no reaction.
1) Add new method (could be in the one method but probably best to keep them separate. If in the one method then 3 isn't required) :-
// New method to utilise Cursor adapter for 2nd Spinner
private void altLoadSpinner2Data() {
// get the cursor
spinner2csr = dbhndlr.getAllLabelsAsCursor();
// Instantiaie Simple Cursor Adapter
SimpleCursorAdapter sca = new SimpleCursorAdapter(this,
android.R.layout.simple_list_item_1, // Layout to show 2 items
spinner2csr, // Cursor
new String[]{DatabaseHandler.KEY_NAME}, // Source data
new int[]{android.R.id.text1}, // Views into which data is shown
0
);
//
//sca.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
s2.setAdapter(sca);
s2.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
@Override
public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
Toast.makeText(parent.getContext(),
"You Selected: " + id + " " +
spinner2csr.getString(
spinner2csr.getColumnIndex(DatabaseHandler.KEY_ID)),
Toast.LENGTH_SHORT
).show();
}
@Override
public void onNothingSelected(AdapterView<?> parent) {
}
});
}
- Note uses 2nd Cursor so each can have their own position
- Includes it's own slightly different (show's ID seelected)
OnSelectedItemListener
2) Change Cursor declaration to be for 2 cursor so it is :-
Cursor spinner1csr, spinner2csr; //<<<<< Cursor for spinner (close in onDestroy)
3) Add call to load 2nd spinner, after/before 1st as per :-
altLoadSpinner2Data();
- Note this 2nd spinner won't be any real use as it just replicates the first just different way of displaying the row.
- Note you have to be careful with spinner's the OnSelectedItemListener will be called, by default, when started. e.g. How to keep onItemSelected from firing off on a newly instantiated Spinner? Start the App, do nothing and watch Toasts
Upvotes: 0
pcampana
Reputation: 2681
You should include one method to get just one Label.
public String getLabelsById(int id){
String result = null;
// Select Query
String selectQuery = "SELECT * FROM " + TABLE_LABELS + " WHERE " + "id" +" = ?";
SQLiteDatabase db = this.getReadableDatabase();
cursor = db.rawQuery(selectQuery, new String[] {String.valueOf(id)});
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
result = (cursor.getString(0));
}
// closing connection
cursor.close();
db.close();
// returning lables
return labels;
}
You should create a class to store a user, with two atributes: name and number. And then you should modify this function with something like:
User user = new User();
And then, insted of return a string:
user.setName(cursor.getString(1));
user.setNumber(cursor.getString(2));
Upvotes: 0
Related Questions
- How to filter second spinner values from database on the basis of selection made in the first spinner?
- Android Spinner: Populate second and thrid spinner based on first spinner filter
- storing selected values of spinner in sqlite
- How to avoid fetching double data from sqlite in a spinner?
- Second spinner not loading based on previous spinner from sqlite
- Saving Spinner selection into SQLite database
- Issues in Spinner setselection(position) with Sqlite Database android
- Spinner populated from SQLite DataBase with empty selection
- Android: value of one spinner depends on value of other spinner
- Two spinners in android app