database example in android
DESCRIPTION
the compete code for creating a database app in AndroidTRANSCRIPT
SQLiteExample.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
package pac.SQLite;
import android.app.Activity;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Color;
import android.os.Bundle;
import android.widget.LinearLayout;
import android.widget.TextView;
import android.widget.Toast;
public class SQLiteExample extends Activity {
LinearLayout Linear;
SQLiteDatabase mydb;
private static String DBNAME = "PERSONS.db"; // THIS IS THE SQLITE DATABASE
FILE NAME.
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
private static String TABLE = "MY_TABLE"; // THIS IS THE TABLE NAME
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
Linear = (LinearLayout)findViewById(R.id.linear);
Toast.makeText(getApplicationContext(), "Creating table.",
Toast.LENGTH_SHORT).show();
dropTable(); // DROPPING THE TABLE.
createTable();
TextView t0 = new TextView(this);
t0.setText("This tutorial covers CREATION, INSERTION, UPDATION AND
DELETION USING SQLITE DATABASES.
Creating table complete........");
Linear.addView(t0);
Toast.makeText(getApplicationContext(), "Creating table complete.",
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
Toast.LENGTH_SHORT).show();
insertIntoTable();
TextView t1 = new TextView(this);
t1.setText("Insert into table complete........");
Linear.addView(t1);
Toast.makeText(getApplicationContext(), "Insert into table complete",
Toast.LENGTH_SHORT).show();
TextView t2 = new TextView(this);
t2.setText("Showing table values............");
Linear.addView(t2);
showTableValues();
Toast.makeText(getApplicationContext(), "Showing table values",
Toast.LENGTH_SHORT).show();
updateTable();
TextView t3 = new TextView(this);
t3.setText("Updating table values............");
Linear.addView(t3);
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
Toast.makeText(getApplicationContext(), "Updating table values",
Toast.LENGTH_SHORT).show();
TextView t4 = new TextView(this);
t4.setText("Showing table values after updation..........");
Linear.addView(t4);
Toast.makeText(getApplicationContext(), "Showing table values after
updation.", Toast.LENGTH_SHORT).show();
showTableValues();
deleteValues();
TextView t5 = new TextView(this);
t5.setText("Deleting table values..........");
Linear.addView(t5);
Toast.makeText(getApplicationContext(), "Deleting table values",
Toast.LENGTH_SHORT).show();
TextView t6 = new TextView(this);
t6.setText("Showing table values after deletion.........");
Linear.addView(t6);
Toast.makeText(getApplicationContext(), "Showing table values after
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
deletion.", Toast.LENGTH_SHORT).show();
showTableValues();
setColor(t0);
setColor(t1);
setColor(t2);
setColor(t3);
setColor(t4);
setColor(t5);
setColor(t6);
}
// THIS FUNCTION SETS COLOR AND PADDING FOR THE TEXTVIEWS
public void setColor(TextView t){
t.setTextColor(Color.BLACK);
t.setPadding(20, 5, 0, 5);
t.setTextSize(1, 15);
}
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
// CREATE TABLE IF NOT EXISTS
public void createTable(){
try{
mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
mydb.execSQL("CREATE TABLE IF NOT EXISTS "+ TABLE +" (ID INTEGER
PRIMARY KEY, NAME TEXT, PLACE TEXT);");
mydb.close();
}catch(Exception e){
Toast.makeText(getApplicationContext(), "Error in creating table",
Toast.LENGTH_LONG);
}
}
// THIS FUNCTION INSERTS DATA TO THE DATABASE
public void insertIntoTable(){
try{
mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
96
97
98
99
10
0
10
1
10
2
10
3
10
4
10
5
10
6
10
7
mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE)
VALUES('CODERZHEAVEN','GREAT INDIA')");
mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE)
VALUES('ANTHONY','USA')");
mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE)
VALUES('SHUING','JAPAN')");
mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE)
VALUES('JAMES','INDIA')");
mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE)
VALUES('SOORYA','INDIA')");
mydb.execSQL("INSERT INTO " + TABLE + "(NAME, PLACE)
VALUES('MALIK','INDIA')");
mydb.close();
}catch(Exception e){
Toast.makeText(getApplicationContext(), "Error in inserting into
table", Toast.LENGTH_LONG);
}
}
// THIS FUNCTION SHOWS DATA FROM THE DATABASE
10
8
10
9
11
0
11
1
11
2
11
3
11
4
11
5
11
6
11
7
11
8
public void showTableValues(){
try{
mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
Cursor allrows = mydb.rawQuery("SELECT * FROM "+ TABLE, null);
System.out.println("COUNT : " + allrows.getCount());
Integer cindex = allrows.getColumnIndex("NAME");
Integer cindex1 = allrows.getColumnIndex("PLACE");
TextView t = new TextView(this);
t.setText("========================================");
//Linear.removeAllViews();
Linear.addView(t);
if(allrows.moveToFirst()){
do{
LinearLayout id_row = new LinearLayout(this);
11
9
12
0
12
1
12
2
12
3
12
4
12
5
12
6
12
7
12
8
12
LinearLayout name_row = new LinearLayout(this);
LinearLayout place_row= new LinearLayout(this);
final TextView id_ = new TextView(this);
final TextView name_ = new TextView(this);
final TextView place_ = new TextView(this);
final TextView sep = new TextView(this);
String ID = allrows.getString(0);
String NAME= allrows.getString(1);
String PLACE= allrows.getString(2);
id_.setTextColor(Color.RED);
id_.setPadding(20, 5, 0, 5);
name_.setTextColor(Color.RED);
name_.setPadding(20, 5, 0, 5);
9
13
0
13
1
13
2
13
3
13
4
13
5
13
6
13
7
13
8
13
9
place_.setTextColor(Color.RED);
place_.setPadding(20, 5, 0, 5);
System.out.println("NAME " + allrows.getString(cindex) + "
PLACE : "+ allrows.getString(cindex1));
System.out.println("ID : "+ ID + " || NAME " + NAME + "||
PLACE : "+ PLACE);
id_.setText("ID : " + ID);
id_row.addView(id_);
Linear.addView(id_row);
name_.setText("NAME : "+NAME);
name_row.addView(name_);
Linear.addView(name_row);
place_.setText("PLACE : " + PLACE);
place_row.addView(place_);
Linear.addView(place_row);
14
0
14
1
14
2
14
3
14
4
14
5
14
6
14
7
14
8
14
9
15
0
sep.setText("-----------------------------------------------
----------------");
Linear.addView(sep);
}
while(allrows.moveToNext());
}
mydb.close();
}catch(Exception e){
Toast.makeText(getApplicationContext(), "Error encountered.",
Toast.LENGTH_LONG);
}
}
// THIS FUNCTION UPDATES THE DATABASE ACCORDING TO THE CONDITION
public void updateTable(){
try{
mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
mydb.execSQL("UPDATE " + TABLE + " SET NAME = 'MAX' WHERE PLACE =
15
1
15
2
15
3
15
4
15
5
15
6
15
7
15
8
15
9
16
0
16
1
'USA'");
mydb.close();
}catch(Exception e){
Toast.makeText(getApplicationContext(), "Error encountered",
Toast.LENGTH_LONG);
}
}
// THIS FUNCTION DELETES VALUES FROM THE DATABASE ACCORDING TO THE CONDITION
public void deleteValues(){
try{
mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
mydb.execSQL("DELETE FROM " + TABLE + " WHERE PLACE = 'USA'");
mydb.close();
}catch(Exception e){
Toast.makeText(getApplicationContext(), "Error encountered while
deleting.", Toast.LENGTH_LONG);
}
16
2
16
3
16
4
16
5
16
6
16
7
16
8
16
9
17
0
17
1
17
}
// THIS FUNTION DROPS A TABLE
public void dropTable(){
try{
mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
mydb.execSQL("DROP TABLE " + TABLE);
mydb.close();
}catch(Exception e){
Toast.makeText(getApplicationContext(), "Error encountered while
dropping.", Toast.LENGTH_LONG);
}
}
}
2
17
3
17
4
17
5
17
6
17
7
17
8
17
9
18
0
18
1
18
2
18
3
18
4
18
5
18
6
18
7
18
8
18
9
19
0
19
1
19
2
Now the main.xml file
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical"
android:layout_width="fill_parent"
android:layout_height="fill_parent">
<ScrollView
android:id="@+id/ScrollView01"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:background="@drawable/android">
<LinearLayout
android:id="@+id/linear"
android:orientation="vertical"
android:layout_below="@+id/add_record"
android:layout_width="wrap_content"
16
17
18
19
android:layout_height="fill_parent">
</LinearLayout>
</ScrollView>
</LinearLayout>
The mainfest.xml file.
?
1
2
3
4
5
6
7
8
9
10
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="pac.SQLite"
android:versionCode="1"
android:versionName="1.0">
<application android:icon="@drawable/icon" android:label="@string/app_name">
<activity android:name=".SQLiteExample"
android:label="SQLite Example Demo">
<intent-filter>
<action android:name="android.intent.action.MAIN" />
11
12
13
14
15
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
</manifest