Working with SQLite Database in ANDROID.
Below is a straight forward example of how to deal with SQLite database in ANDROID.
Go ahead and copy and paste the following code to your java file.
The example has one database and performs functions like insert delete and listing of values in a textView.
package com.database;
import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.LinearLayout;
import android.widget.ScrollView;
import android.widget.TableLayout;
import android.widget.TableRow;
import android.widget.TextView;
import android.widget.Toast;
public class databaseactivity extends Activity {
/** Called when the activity is first created. */
private static String DBNAME = "mydb.db";
private static String TABLE = "myTable";
Button butAdd,butdel;
EditText nam,mail;
LinearLayout lay;
TableLayout layout;
TextView tev,tev1;
int f=0;
Integer ind;
ScrollView sv;
public void onCreate(Bundle savedInstanceState)
{
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
tev=(TextView)findViewById(R.id.tv);
tev1=(TextView)findViewById(R.id.tv1);
layout=(TableLayout)findViewById(R.id.layout);
lay = (LinearLayout) findViewById(R.id.linear);
butAdd = (Button) findViewById(R.id.add);
nam=(EditText)findViewById(R.id.names);
mail=(EditText)findViewById(R.id.emails);
SQLiteDatabase mydb;
mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
mydb.execSQL("create table if not exists "+TABLE+" (_id integer primary key autoincrement,name text not null,email text not null);");
mydb.close();
sv = new ScrollView(this);
setContentView(sv);
sv.addView(lay);
butAdd.setOnClickListener(new View.OnClickListener()
{
@Override
public void onClick(View v)
{
f=0;
addTask();
}
});
}
public void addTask()
{
SQLiteDatabase mydb;
mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
ContentValues newrow = new ContentValues();
String a=nam.getText().toString();
String b=mail.getText().toString();
if(a.trim().length()!=0 && b.trim().length()!=0)
{
newrow.put("name", nam.getText().toString());
newrow.put("email", mail.getText().toString());
mydb.insert(TABLE, null, newrow);
}
else
{
if(f==0)
Toast.makeText(getApplicationContext(), "Fields cannot be left blank",
Toast.LENGTH_SHORT).show();
}
nam.setText(null);
mail.setText(null);
String[] result = new String[]{"_id","name","email"};
Cursor allrows = mydb.query(TABLE, result, null, null, null, null, null);
Integer cindex = allrows.getColumnIndex("name");
Integer cindex1 = allrows.getColumnIndex("email");
Integer cindex2 = allrows.getColumnIndex("_id");
if(allrows.moveToFirst())
{
layout.removeAllViews();
do
{
TableRow row= new TableRow(this);
final TextView tv = new TextView(this);
final TextView tv1 = new TextView(this);
final Button but=new Button(this);
but.setText("Delete");
final Button butt=new Button(this);
butt.setText("Edit"); ind=Integer.parseInt(allrows.getString(cindex2)) ;
but.setId(ind);
butt.setId(ind);
nam.setId(ind);
but.setOnClickListener(new View.OnClickListener() {
public void onClick(View v)
{
f=1;
int i=but.getId();
SQLiteDatabase mydb;
mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
mydb.delete(TABLE,"_id="+i, null);
mydb.close();
Toast.makeText(getApplicationContext(), "Deleted", Toast.LENGTH_SHORT).show();
addTask();
}
});
butt.setOnClickListener(new View.OnClickListener() {
public void onClick(View v)
{
butt.setText("Save");
nam.setText(tv.getText());
mail.setText(tv1.getText());
butt.setOnClickListener(new View.OnClickListener() {
public void onClick(View v) {
f=1;
int j=but.getId();
SQLiteDatabase mydb;
mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
;
ContentValues newrow1 = new ContentValues();
String a=nam.getText().toString();
String b=mail.getText().toString();
if(a.trim().length()!=0&& b.trim().length()!=0) { newrow1.put("name", nam.getText().toString());
newrow1.put("email", mail.getText().toString());
mydb.update(TABLE,newrow1, "_id="+j, null);
Toast.makeText(getApplicationContext(), "Updated",
Toast.LENGTH_SHORT).show();
nam.setText(null);
mail.setText(null);
butt.setText("Edit");
addTask();
} else {
//if(f==0)
Toast.makeText(getApplicationContext(), "Fields cannot be left blank",
Toast.LENGTH_SHORT).show();
} mydb.close();
}
});
}
});
tv.setText(allrows.getString(cindex));
row.addView(tv);
tv1.setText(allrows.getString(cindex1));
row.addView(tv1);
row.addView(butt);
row.addView(but);
layout.addView(row);
}while(allrows.moveToNext());
}
else
{
layout.removeAllViews();
Toast.makeText(getApplicationContext(), "Table is empty", Toast.LENGTH_SHORT).show();
}
mydb.close();
}
}
In the XML file set up a TableLayout with android:stretchColumns=”0,1″
and other buttons also and proceed.
Please leave your valuable comments.
Link to this post!
