Android Database Example

Database Class


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

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class DatabaseHelper {

 private static final String DATABASE_NAME = "firstdatabase.db";
 private static final int DATABASE_VERSION = 1;
 private static final String TABLE_NAME = "mytable";

 private Context context;
 private SQLiteDatabase sqlDb;
 private SQLiteStatement insertStatement;
 private static final String INSERT = "insert into " + TABLE_NAME
   + "(name) values (?)";

 public DatabaseHelper(Context context) {
  this.context = context;
  OpenHelper openHelper = new OpenHelper(this.context);
  this.sqlDb = openHelper.getWritableDatabase();
  this.insertStatement = this.sqlDb.compileStatement(INSERT);
 }

 public long insert(String name) {
  this.insertStatement.bindString(1, name);
  return this.insertStatement.executeInsert();
 }

 public void deleteAll() {
  this.sqlDb.delete(TABLE_NAME, null, null);
 }

 public List<String> listAll() {
  List<String> list = new ArrayList<String>();
  Cursor cursor = this.sqlDb.query(TABLE_NAME, new String[] { "name" },
    null, null, null, null, "name desc");
  if (cursor.moveToFirst()) {
   do {
    list.add(cursor.getString(0));
   } while (cursor.moveToNext());
  }
  if (cursor != null && !cursor.isClosed()) {
   cursor.close();
  }

  return list;
 }

 private static class OpenHelper extends SQLiteOpenHelper {

  OpenHelper(Context context) {
   super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }

  public void onCreate(SQLiteDatabase db) {
   db.execSQL("CREATE TABLE " + TABLE_NAME + ""
     + "(id INTEGER PRIMARY KEY, name TEXT)");
  }

  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
   Log.w("Example",
     "Upgrading database, this will drop tables and recreate.");
   db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
   onCreate(db);
  }
 }

}

Your Activity

import java.util.List;

import com.may.and.database.DatabaseHelper;

import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.widget.TextView;

public class DatabaseActivity extends Activity {
 
 private TextView output;
 
 private DatabaseHelper dbh;
 
 
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        
        output = (TextView) findViewById(R.id.text_view_output);
        
        dbh = new DatabaseHelper(this);
        
        dbh.deleteAll();
        dbh.insert("Fsd");
        dbh.insert("Fsd");
        dbh.insert("Fsd");
        dbh.insert("Fsd");
        dbh.insert("Fsd");
        dbh.insert("Fsd");
        
        List<String> list = dbh.listAll();
        StringBuilder sb = new StringBuilder();
        sb.append("Database Values are: \n");
        for (String name :list){
         sb.append(name + "\n");
        }
        
        Log.d("Database", "Size is :"+ list.size());
        
        output.setText(sb.toString());
        
    }
}