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.
