SQLITE TUTORIAL

Web Hosting
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();
}

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

No comments:

Post a Comment