SQLITE TUTORIAL

In this tutorial, I will show how to use SQLITE in Android. See code below.

DatabaseModel.java

package com.client.databases;

public class DatabaseModel {

private String m_fullname, m_username, m_password;
private int m_id;

//constructor
public DatabaseModel(String fullname, String username, String password){
this.m_fullname = fullname;
this.m_username = username;
this.m_password = password;
}

public DatabaseModel(String username){
this.m_username = username;

}

public DatabaseModel(int id, String fullname, String username, String password){
this.m_id = id;
this.m_fullname = fullname;
this.m_username = username;
this.m_password = password;
}

public DatabaseModel(int id){
this.m_id = id;
}

public DatabaseModel(){

}

//setters
public void setName(String fullname){
this.m_fullname = fullname;
}

public void setUsername(String username){
this.m_username = username;
}

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

public void setId(int id){
this.m_id = id;
}

//getters

public String getName(){
return this.m_fullname;
}

public String getUsername(){
return this.m_username;
}

public String getPassword(){
return this.m_password;
}

public int getId(){
return this.m_id;
}

}

DatabaseHandler.java

package com.client.databases;

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 {

 

    // All Static variables

    // Database Version

    private static final int DATABASE_VERSION = 1;

 

    // Database Name

    private static final String DATABASE_NAME = "Project";

 

    // Contacts table name

    private static final String TABLE_USERS = "Project_Users";

 

    // Contacts Table Columns names

    private static final String KEY_ID = "id";

    private static final String KEY_NAME = "fullname";

    private static final String KEY_UNAME = "username";

    private static final String KEY_PASS = "userpass";

 

    public DatabaseHandler(Context context) {

        super(context, DATABASE_NAME, null, DATABASE_VERSION);

    }

 

 // Creating Tables

    @Override

    public void onCreate(SQLiteDatabase db) {

        String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_USERS + "("

                + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," 

        + KEY_NAME + " TEXT,"

                + KEY_UNAME + " TEXT,"

                + KEY_PASS + " TEXT"

        + ")";

        db.execSQL(CREATE_CONTACTS_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_USERS);

 

        // Create tables again

        onCreate(db);

    }

    

    //add users to database

    public void addUsers(DatabaseModel model){

    SQLiteDatabase db = this.getReadableDatabase();

   

    ContentValues values = new ContentValues();

    values.put(KEY_NAME, model.getName());

    values.put(KEY_UNAME, model.getUsername());

    values.put(KEY_PASS, model.getPassword());

   

    db.insert(TABLE_USERS, null, values);

    db.close();

    }

    

    //query only one data

    DatabaseModel getDatabaseModel(int id){

    SQLiteDatabase db = this.getReadableDatabase();

   

    Cursor cursor = db.query(TABLE_USERS, new String[]{ KEY_ID,  KEY_NAME,  KEY_UNAME,  KEY_PASS}, KEY_ID + "=?", new String[] { String.valueOf(id)}, null, null, null, null);

    if(cursor !=null)

    cursor.moveToFirst();

   

    DatabaseModel model = new DatabaseModel(Integer.parseInt(cursor.getString(0)),

                cursor.getString(1), cursor.getString(2), cursor.getString(3));

   

    return model;

   

    }

    

    //query only one data

    public String queryOne(String username){

    //DatabaseModel getDatabaseModel(String username){

    SQLiteDatabase db = this.getReadableDatabase();

   

    Cursor cursor = db.query(TABLE_USERS, new String[]{ KEY_ID,  KEY_NAME,  KEY_UNAME,  KEY_PASS}, KEY_UNAME + "=?", new String[] { username}, null, null, null, null);

    if(cursor !=null)

    cursor.moveToFirst();

   

    String ind = cursor.getString(0);

   

    return ind;

   

    }

    

    //query all data from database

    public List<DatabaseModel> getAll(){

    List<DatabaseModel> userlist = new ArrayList<DatabaseModel>();

    String query = "SELECT * FROM " + TABLE_USERS;

   

    SQLiteDatabase db = this.getWritableDatabase();

    Cursor cursor = db.rawQuery(query, null);

   

    if(cursor.moveToFirst()){

    do{

    DatabaseModel model = new DatabaseModel();

    model.setId(Integer.parseInt(cursor.getString(0)));

    model.setName(cursor.getString(1));

    model.setUsername(cursor.getString(2));

    model.setPassword(cursor.getString(3));

    userlist.add(model);

    }while (cursor.moveToNext());

    }

    return userlist;

    }

    

    //update one data from database

    public int updateContact(DatabaseModel model){

    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();

    values.put(KEY_NAME, model.getName());

    values.put(KEY_UNAME, model.getUsername());

    values.put(KEY_PASS, model.getPassword());

   

    return db.update(TABLE_USERS, values, KEY_ID + " = ?", new String[] { String.valueOf(model.getId())});

    }

    

    //delete data from database

    public void deleteUsers(DatabaseModel model){

    SQLiteDatabase db = this.getWritableDatabase();

    db.delete(TABLE_USERS, KEY_ID + " = ?",

    new String[] { String.valueOf(model.getId())});

    db.close();

    }

}

Just call this codes below during you need it in your page.

Add:

DatabaseHandler db = new DatabaseHandler(this);

db.addUsers(new DatabaseModel(et_name.getText().toString(), et_email.getText().toString(), et_password.getText().toString()));

Delete:

DatabaseHandler db = new DatabaseHandler(this);

db.deleteUsers(new DatabaseModel(Integer.parseInt(et_email.getText().toString())));

 

Update:

DatabaseHandler db = new DatabaseHandler(this);

//db.updateContact(new DatabaseModel(Integer.parseInt(et_name.getText().toString()), et_name.getText().toString(), et_email.getText().toString(), et_password.getText().toString()));

Select:

DatabaseHandler db = new DatabaseHandler(this);

        lv_list = (ListView)findViewById(R.id.lv_list);

        

        List<DatabaseModel> model = db.getAll();

        final ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();

        for(DatabaseModel md : model){

       

        HashMap<String, String> map = new HashMap<String, String>();

        map.put("id", Integer.toString(md.getId()));

        map.put("fullname", md.getName());

        map.put("username", md.getUsername());

        map.put("userpass", md.getPassword());

        list.add(map);

        }

        

        ListAdapter adapter = new SimpleAdapter(getApplicationContext(), list, R.layout.list_users, new String[]{"id", "fullname", "username", "userpass"}, new int[]{R.id.tv_id, R.id.tv_name, R.id.tv_username, R.id.tv_password});

        lv_list.setAdapter(adapter);

        

        lv_list.setOnItemClickListener(new OnItemClickListener() {

@Override

public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,

long arg3) {

// TODO Auto-generated method stub

try{

TextView id=(TextView)arg0.getChildAt(arg2-lv_list.getFirstVisiblePosition()).findViewById(R.id.tv_id);

Toast.makeText(getApplicationContext(), id.getText().toString(), Toast.LENGTH_SHORT).show();

}catch(Exception e){

e.printStackTrace();

}

}

});

Check single data

try{

DatabaseHandler db = new DatabaseHandler(this);

if(db.queryOne(et_email.getText().toString()) != null){

Intent x = new Intent(context, DashboardMenu.class);

SavePreferences("activity", "0");

startActivity(x);

overridePendingTransition(R.anim.slide_from_left, R.anim.hold);

finish();

login_indicator = 1;

}else{

Toast.makeText(getApplicationContext(), "Not Empty", Toast.LENGTH_SHORT).show();

}

}catch(Exception e){

e.printStackTrace();

}

Anyway, I just refer this tutorial from this link. That's it, enjoy happy coding.