Android Spinner get data From SQLite Database

Last Tutorial about Spinner Android Working with Spinner was having static data.In this Tutorial we will getting data from database dynamically.

So Let’s Start

1.Open Eclipse or Android Studio -> New Project->Android Application Project-> Name of Application-> Follow all instructions and complete by clicking on Finish.

2.Create Layout for MainActivity.java that will be our mainscreen

activity_main.xml

<LinearLayout 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"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    android:paddingBottom="@dimen/activity_vertical_margin"
    tools:context=".MainActivity"
    android:orientation="vertical">

    <TextView
        android:text="Username"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content" />

    <EditText
        android:id="@+id/username"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content" />

    <TextView
        android:text="Password"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content" />

    <EditText
        android:id="@+id/password"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content" />

    <Button
        android:id="@+id/adduser"
        android:text="AddUser"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content" />

    <Spinner
        android:id="@+id/selectuser"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"></Spinner>

</LinearLayout>

2.Now we will make Model class as User.java for storing user information as a User Object

User.java

package com.coderzpassion.myspinnersample;



//this is model class
public class User {

    //variables
    int id;
    String name;
    String password;


    //  Constructor with two parameters name and password
    public User(String name, String password)
    {
        this.name=name;
        this.password=password;
    }
    //Parameter constructor containing all three parameters
    public User(int id, String name, String psd)
    {
        this.id=id;
        this.name=name;
        this.password=psd;

    }
    //getting id
    public int getId() {
        return id;
    }
    //setting id
    public void setId(int id) {
        this.id = id;
    }
    //getting name
    public String getName() {
        return name;
    }
    //setting name
    public void setName(String name) {
        this.name = name;
    }

    //getting password
    public String getPassword() {
        return password;
    }

    //setting password
    public void setPassword(String password) {
        this.password = password;
    }
}

 

3.Now We will make DbHandler.java to perform all data operations that is adding users to the database and getting all users from database to display in Spinner

DbHandler.java

package com.coderzpassion.myspinnersample;

import android.content.ContentValues;
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;

/**
 * Created by apple on 23/02/16.
 */
public class DbHandler extends SQLiteOpenHelper {

    //all constants as they are static and final(Db=Database)
    //Db Version
    private  static final int Db_Version=1;
    //Db Name
    private static final String Db_Name="users";
    //table name
    private static final String Table_Name="user";
    //Creating mycontacts Columns
    private static final String User_id="id";
    private static final String User_name="name";
    private static final String User_password="password";


    //constructor here
    public DbHandler(Context context)
    {
        super(context,Db_Name,null,Db_Version);
    }

    //creating table
    @Override
    public void onCreate(SQLiteDatabase db) {
        // writing command for sqlite to create table with required columns
        String Create_Table="CREATE TABLE " + Table_Name + "(" + User_id
                 + " INTEGER PRIMARY KEY," + User_name + " TEXT," + User_password + " TEXT" + ")";
        db.execSQL(Create_Table);
    }

    //Upgrading the Db
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //Drop table if exists
        db.execSQL("DROP TABLE IF EXISTS " + Table_Name);
        //create the table again
        onCreate(db);
    }

    //Add new User by calling this method
    public void addUser(User usr)
    {
        // getting db instance for writing the user
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues cv=new ContentValues();
       // cv.put(User_id,usr.getId());
        cv.put(User_name,usr.getName());
        cv.put(User_password,usr.getPassword());

        //inserting row
        db.insert(Table_Name, null, cv);
        //close the database to avoid any leak
        db.close();
    }

   public int checkUser(User us)
   {
       int id=-1;
       SQLiteDatabase db=this.getReadableDatabase();
       Cursor cursor=db.rawQuery("SELECT id FROM user WHERE name=? AND password=?",new String[]{us.getName(),us.getPassword()});
       if(cursor.getCount()>0) {
           cursor.moveToFirst();
           id=cursor.getInt(0);
         cursor.close();
       }
       return id;
       }

    
    //getting all users from database
    public List<String> getAllUsers()
    {
        List<String> userlist=new ArrayList<>();
        //get readable database
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor cursor=db.rawQuery("SELECT name FROM user",null);

        if(cursor.moveToFirst())
        {
            do {
                userlist.add(cursor.getString(0));
            }while (cursor.moveToNext());
        }
        //close the cursor
        cursor.close();
        //close the database
        db.close();

      return userlist;
    }

}

4. Now we are ready for MainActivity.java as we created DbHandler to store users and get all users from database.

MainActivity.java

package com.coderzpassion.myspinnersample;

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Spinner;
import android.widget.Toast;

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

public class MainActivity extends AppCompatActivity {
    //spinner
    Spinner allusers;
    //list to be set to spinner
    List<String> users=new ArrayList<>();
   // edittext for user's name and user's password
    EditText uname,upassword;
    // Button to add user to database
    Button adduser;
    ArrayAdapter<String> adapter;
    DbHandler db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        allusers=(Spinner)findViewById(R.id.selectuser);
        uname=(EditText)findViewById(R.id.username);
        upassword=(EditText)findViewById(R.id.password);
        adduser=(Button)findViewById(R.id.adduser);
        db=new DbHandler(MainActivity.this);


        //handling click of adduser button
        adduser.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                String username=uname.getText().toString();
                String password=upassword.getText().toString();
                if(username.equalsIgnoreCase("")||password.equalsIgnoreCase(""))
                {
                    Toast.makeText(MainActivity.this,"Please Enter Username and Password",Toast.LENGTH_SHORT).show();
                }
                else
                {
                    db.addUser(new User(username,password));
                    prepareData();

                    Toast.makeText(MainActivity.this,"User was Successfully added to Database",Toast.LENGTH_SHORT).show();
                }
            }
        });

        //prepare  data for spinner
        prepareData();


        //handle click of spinner item
        allusers.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
            @Override
            public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {

                // clicked item will be shown as spinner
                Toast.makeText(getApplicationContext(),""+parent.getItemAtPosition(position).toString(),Toast.LENGTH_SHORT).show();
            }

            @Override
            public void onNothingSelected(AdapterView<?> parent) {

            }
    });

    }

    public void prepareData()
    {
         users=db.getAllUsers();
        //adapter for spinner
        adapter=new ArrayAdapter<String>(MainActivity.this,android.R.layout.simple_spinner_dropdown_item,android.R.id.text1,users);
        //attach adapter to spinner
        allusers.setAdapter(adapter);
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.menu_main, menu);
        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        // Handle action bar item clicks here. The action bar will
        // automatically handle clicks on the Home/Up button, so long
        // as you specify a parent activity in AndroidManifest.xml.
        int id = item.getItemId();

        //noinspection SimplifiableIfStatement
        if (id == R.id.action_settings) {
            return true;
        }

        return super.onOptionsItemSelected(item);
    }
}

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.coderzpassion.myspinnersample" >

    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        <activity
            android:name=".MainActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

Output

Screenshot_2016-02-28-00-49-07                           Screenshot_2016-02-28-00-49-16

Android Simple Login Example Using SQLite

MainActivity.java

package com.androidruler.databasesample;

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

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

public class MainActivity extends AppCompatActivity {

EditText uname,pswd;
Button login;
DbHandler db;

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
uname=(EditText)findViewById(R.id.uname);
pswd=(EditText)findViewById(R.id.password);
login=(Button)findViewById(R.id.login);
login.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
String name=uname.getText().toString();
String password=pswd.getText().toString();

int id= checkUser(new User(name,password));
if(id==-1)
{
Toast.makeText(MainActivity.this,"User Does Not Exist",Toast.LENGTH_SHORT).show();
}
else
{
Toast.makeText(MainActivity.this,"User Exist "+name,Toast.LENGTH_SHORT).show();
}
}
});

db=new DbHandler(MainActivity.this);
//inserting dummy users
db.addUser(new User("Ankur", "Bansal"));
db.addUser(new User("Vibhor", "Tayal"));
db.addUser(new User("Jatin", "Garg"));

}

public int checkUser(User user)
{
return db.checkUser(user);
}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.menu_main, menu);
return true;
}

@Override
public boolean onOptionsItemSelected(MenuItem item) {
// Handle action bar item clicks here. The action bar will
// automatically handle clicks on the Home/Up button, so long
// as you specify a parent activity in AndroidManifest.xml.
int id = item.getItemId();

//noinspection SimplifiableIfStatement
if (id == R.id.action_settings) {
return true;
}

return super.onOptionsItemSelected(item);
}
}

User.java

package com.androidruler.databasesample;


//this is model class
public class User {

 //variables
 int id;
 String name;
 String password;


 // Constructor with two parameters name and password
 public User(String name,String password)
 {
 this.name=name;
 this.password=password;
 }
 //Parameter constructor containing all three parameters
 public User(int id,String name,String psd)
 {
 this.id=id;
 this.name=name;
 this.password=psd;

 }
 //getting id
 public int getId() {
 return id;
 }
 //setting id
 public void setId(int id) {
 this.id = id;
 }
 //getting name
 public String getName() {
 return name;
 }
 //setting name
 public void setName(String name) {
 this.name = name;
 }

 //getting password
 public String getPassword() {
 return password;
 }

 //setting password
 public void setPassword(String password) {
 this.password = password;
 }
}

 DbHandler.java

package com.androidruler.databasesample;

import android.content.ContentValues;
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 DbHandler extends SQLiteOpenHelper {

 //all constants as they are static and final(Db=Database)
 //Db Version
 private static final int Db_Version=1;
 //Db Name
 private static final String Db_Name="users";
 //table name
 private static final String Table_Name="user";
 //Creating mycontacts Columns
 private static final String User_id="id";
 private static final String User_name="name";
 private static final String User_password="password";


 //constructor here
 public DbHandler(Context context)
 {
 super(context,Db_Name,null,Db_Version);
 }

 //creating table
 @Override
 public void onCreate(SQLiteDatabase db) {
 // writing command for sqlite to create table with required columns
 String Create_Table="CREATE TABLE " + Table_Name + "(" + User_id
 + " INTEGER PRIMARY KEY," + User_name + " TEXT," + User_password + " TEXT" + ")";
 db.execSQL(Create_Table);
 }

 //Upgrading the Db
 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 //Drop table if exists
 db.execSQL("DROP TABLE IF EXISTS " + Table_Name);
 //create the table again
 onCreate(db);
 }

 //Add new User by calling this method
 public void addUser(User usr)
 {
 // getting db instance for writing the user
 SQLiteDatabase db=this.getWritableDatabase();
 ContentValues cv=new ContentValues();
 // cv.put(User_id,usr.getId());
 cv.put(User_name,usr.getName());
 cv.put(User_password,usr.getPassword());

 //inserting row
 db.insert(Table_Name, null, cv);
 //close the database to avoid any leak
 db.close();
 }

 public int checkUser(User us)
 {
 int id=-1;
 SQLiteDatabase db=this.getReadableDatabase();
 Cursor cursor=db.rawQuery("SELECT id FROM user WHERE name=? AND password=?",new String[]{us.getName(),us.getPassword()});
 if(cursor.getCount()>0) {
 cursor.moveToFirst();
 id=cursor.getInt(0);
 cursor.close();
 }
 return id;
 }
}

//layout for MainActivity.java

activity_main.xml

<LinearLayout 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" android:paddingLeft="@dimen/activity_horizontal_margin"
 android:paddingRight="@dimen/activity_horizontal_margin"
 android:paddingTop="@dimen/activity_vertical_margin"
 android:paddingBottom="@dimen/activity_vertical_margin"
 tools:context=".MainActivity"
 android:orientation="vertical">

 <TextView
 android:id="@+id/totalcount"
 android:layout_margin="10dp"
 android:text="UserName"
 android:layout_width="wrap_content"
 android:layout_height="wrap_content" />
 <EditText
 android:id="@+id/uname"
 android:layout_width="fill_parent"
 android:layout_height="50dp" />

 <TextView
 android:id="@+id/psw"
 android:layout_margin="10dp"
 android:text="Password"
 android:layout_width="wrap_content"
 android:layout_height="wrap_content" />
 <EditText
 android:id="@+id/password"
 android:layout_width="fill_parent"
 android:layout_height="50dp" />


 <Button
 android:id="@+id/login"
 android:padding="20dp"
 android:text="Login"
 android:layout_width="wrap_content"
 android:layout_height="wrap_content" />

</LinearLayout>

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
 package="com.androidruler.databasesample" >

 <application
 android:allowBackup="true"
 android:icon="@mipmap/ic_launcher"
 android:label="@string/app_name"
 android:theme="@style/AppTheme" >
 <activity
 android:name=".MainActivity"
 android:label="@string/app_name" >
 <intent-filter>
 <action android:name="android.intent.action.MAIN" />

 <category android:name="android.intent.category.LAUNCHER" />
 </intent-filter>
 </activity>
 </application>

</manifest>

 

Android SQLite Database Example

MainActivity.java

MainActivity.java

package com.androidruler.databasesample;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.TextView;
import java.util.ArrayList;
import java.util.List;

public class MainActivity extends AppCompatActivity {

ListView allcontacts;
TextView totalcontacts;
ArrayList<String> allusers=new ArrayList<>();
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
allcontacts=(ListView)findViewById(R.id.contactlist);
totalcontacts=(TextView)findViewById(R.id.totalcount);

DbHandler db=new DbHandler(MainActivity.this);
//inserting contacts
db.addContact(new Contact("Ankur","Bansal","123456789"));
db.addContact(new Contact("Vibhor","Tayal","267755557"));
db.addContact(new Contact("Jatin","Garg","123456789"));

//getting total number of contacts
int total=db.getTotalContacts();
totalcontacts.setText("Total Contacts "+total);

//Reading all contacts and adding them to list
List<Contact> contacts=db.getAllContacts();
for (Contact ct: contacts)
{
allusers.add(ct.getFname()+" "+ct.getLname());
}

//setting the list to listview
ArrayAdapter<String> adapter=new ArrayAdapter<String>(this,android.R.layout.simple_list_item_1,android.R.id.text1,allusers);
allcontacts.setAdapter(adapter);
}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.menu_main, menu);
return true;
}

@Override
public boolean onOptionsItemSelected(MenuItem item) {
// Handle action bar item clicks here. The action bar will
// automatically handle clicks on the Home/Up button, so long
// as you specify a parent activity in AndroidManifest.xml.
int id = item.getItemId();

//noinspection SimplifiableIfStatement
if (id == R.id.action_settings) {
return true;
}

return super.onOptionsItemSelected(item);
}
}

Contact.java

package com.androidruler.databasesample;

//this is model class
public class Contact {

//variables
int id;
String fname;
String lname;
String contactnumber;

//simple empty Constructor
public Contact()
{

}
//Parameter constructor containing all three parameters
public Contact(int id,String fname,String lname,String contactnumber)
{
this.id=id;
this.fname=fname;
this.lname=lname;
this.contactnumber=contactnumber;
}

//Parameter constructor containing two parameters
public Contact(String fname,String lname,String contactnumber)
{
this.fname=fname;
this.lname=lname;
this.contactnumber=contactnumber;

}
//getting contactnumber
public String getContactnumber() {
return contactnumber;
}
//setting contactnumber
public void setContactnumber(String contactnumber) {
this.contactnumber = contactnumber;
}
//getting lname
public String getLname() {
return lname;

}
//setting lname
public void setLname(String lname) {
this.lname = lname;
}
//getting fname
public String getFname() {
return fname;
}
//setting fname
public void setFname(String fname) {
this.fname = fname;
}
//getting id
public int getId() {
return id;
}
//setting id
public void setId(int id) {
this.id = id;
}

}

DbHandler.java

package com.androidruler.databasesample;
import android.content.ContentValues;
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 DbHandler extends SQLiteOpenHelper {

//all constants as they are static and final(Db=Database)
//Db Version
private static final int Db_Version=1;
//Db Name
private static final String Db_Name="contactscontainer";
//table name
private static final String Table_Name="mycontacts";
//Creating mycontacts Columns
private static final String Contact_id="id";
private static final String Contact_fname="fname";
private static final String Contact_lname="lname";
private static final String Contact_number="contactnumber";

//constructor here
public DbHandler(Context context)
{
super(context,Db_Name,null,Db_Version);
}

//creating table
@Override
public void onCreate(SQLiteDatabase db) {
// writing command for sqlite to create table with required columns
String Create_Table="CREATE TABLE " + Table_Name + "(" + Contact_id
+ " INTEGER PRIMARY KEY," + Contact_fname + " TEXT," + Contact_lname +
" TEXT," + Contact_number + " TEXT" + ")";
db.execSQL(Create_Table);
}

//Upgrading the Db
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//Drop table if exists
db.execSQL("DROP TABLE IF EXISTS " + Table_Name);
//create the table again
onCreate(db);
}

//Add new Contact by calling this method
public void addContact(Contact contact)
{
// getting db instance for writing the contact
SQLiteDatabase db=this.getWritableDatabase();
ContentValues cv=new ContentValues();
cv.put(Contact_fname,contact.getFname());
cv.put(Contact_lname,contact.getLname());
cv.put(Contact_number,contact.getContactnumber());

//inserting row
db.insert(Table_Name, null, cv);
//close the database to avoid any leak
db.close();
}

//getting contact according to contact id
public Contact getContact(int id)
{
// getting db instance for reading the contact
SQLiteDatabase db=this.getReadableDatabase();
//writing query for getting contact according to id
Cursor cursor=db.query(Table_Name,new String[]{Contact_id,Contact_fname,Contact_lname,Contact_number},Contact_id + "=?" ,
new String[] { String.valueOf(id)},null,null,null,null);
if(cursor!=null)
cursor.moveToFirst();

Contact contact=new Contact(Integer.parseInt(cursor.getString(0)),cursor.getString(1),cursor.getString(2),
cursor.getString(3));
return contact;
}

//this will all the contact from the table
public List<Contact> getAllContacts()
{
List<Contact> contactList=new ArrayList<Contact>();
//query to select all contacts from table
String selectAllContact="SELECT * FROM " + Table_Name;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectAllContact, null);

// move cursor to first and use loop to add all the contacts to list
if(cursor.moveToFirst())
{
do {
Contact contact =new Contact();
contact.setId(Integer.parseInt(cursor.getString(0)));
contact.setFname(cursor.getString(1));
contact.setLname(cursor.getString(2));
contact.setContactnumber(cursor.getString(3));
//add contact to list
contactList.add(contact);
} while (cursor.moveToNext());
}

return contactList;
}

//get total contacts in the table
public int getTotalContacts()
{
String totalcontacts= "SELECT * FROM " + Table_Name;
SQLiteDatabase db=this.getReadableDatabase();
Cursor cursor=db.rawQuery(totalcontacts,null);

//getting total number of rows from cursor
return cursor.getCount();
}

// Updating contact
public int updateContact(Contact contact) {
SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();
values.put(Contact_fname, contact.getFname());
values.put(Contact_lname, contact.getLname());

// updating row with updated contact values
return db.update(Table_Name, values, Contact_id + " = ?",
new String[] { String.valueOf(contact.getId()) });
}

// Deleting contact
public void deleteContact(Contact contact) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(Table_Name, Contact_id + " = ?",
new String[] { String.valueOf(contact.getId()) });
db.close();
}
}

//layout for MainActivity.java

activity_main.xml

<RelativeLayout 
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=".MainActivity">
<ListView
android:id="@+id/contactlist"
android:layout_width="fill_parent"
android:layout_height="wrap_content"></ListView>

<TextView
android:layout_below="@+id/contactlist"
android:layout_margin="10dp"
android:id="@+id/totalcount"
android:text="@string/hello_world"
android:layout_width="wrap_content"
android:layout_height="wrap_content" />

</RelativeLayout>