SQL Table in Android

SQLite is used as database in Android devices. SQL commands for SQLite are very similar to SQL commands for other database systems (such as SQL Server and Oracle). SQLite is an open-source SQL database that stores data to a text file on a device. Android comes with built-in SQLite database implementation. SQLite supports all the relational database features. To access this database, you don't need to establish any kind of connections for it like JDBC, ODBC and so on.

Now we start our article.

First we create an activity. In this activity we use 4 buttons for Create, Insert, Select and Delete commands. The XML Code for the Activity is:

  1. <LinearLayout  
  2.     xmlns:android="http://schemas.android.com/apk/res/android"  
  3. android:layout_width="fill_parent"  
  4. android:layout_height="fill_parent"  
  5. android:orientation="vertical">  
  6.     <Button  
  7. android:id="@+id/btnCreate"  
  8. android:layout_width="190dp"  
  9. android:layout_height="wrap_content"  
  10. android:text="@string/Create" />  
  11.     <Button  
  12. android:id="@+id/btnInsert"  
  13. android:layout_width="195dp"  
  14. android:layout_height="wrap_content"  
  15. android:text="@string/Insert" />  
  16.     <Button  
  17. android:id="@+id/btnSelect"  
  18. android:layout_width="192dp"  
  19. android:layout_height="wrap_content"  
  20. android:text="@string/Select"/>  
  21.     <Button  
  22. android:id="@+id/btnDelete"  
  23. android:layout_width="188dp"  
  24. android:layout_height="wrap_content"  
  25. android:text="@string/Delete" />  
  26. </LinearLayout>  
After the preceding code our Activity will look as in the following:

Activity

Now we move to the Java code (source code) of our project. We use the following source code for our activity.
  1. packagepankaj.sql.android;  
  2.   
  3.   
  4.   
  5. importdzone.sql.android.R;  
  6. importandroid.app.Activity;  
  7. importandroid.database.Cursor;  
  8. importandroid.database.sqlite.SQLiteDatabase;  
  9. importandroid.os.Bundle;  
  10. importandroid.view.View;  
  11. importandroid.widget.Button;  
  12. importandroid.widget.Toast;  
  13.   
  14. publicclassSqlCreateTableActivityextends Activity {  
  15.   
  16.   
  17.     Button Insert;  
  18.     Button Create;  
  19.     Button Select;  
  20.     Button Delete;  
  21.     final String dbpath = "temp21.db";@Override  
  22.     publicvoidonCreate(Bundle savedInstanceState) {  
  23.         super.onCreate(savedInstanceState);  
  24.         setContentView(R.layout.main);  
  25.         Create = (Button) findViewById((R.id.btnCreate));  
  26.         Insert = (Button) findViewById((R.id.btnInsert));  
  27.         Select = (Button) findViewById((R.id.btnSelect));  
  28.         Delete = (Button) findViewById((R.id.btnDelete));  
  29.         finalSQLiteDatabasedb;  
  30.         db = openOrCreateDatabase(dbpath, SQLiteDatabase.CREATE_IF_NECESSARY, null);  
  31.   
  32.         Create.setOnClickListener(newView.OnClickListener() {  
  33.   
  34.             @Override  
  35.             publicvoidonClick(View arg0) {  
  36.                 // TODO Auto-generated method stub  
  37.   
  38.                 final String CREATE_TABLE_Test = "CREATE TABLE IF NOT EXISTS tbl_Test (" + "ID INTEGER primary key AUTOINCREMENT," + "name TEXT," + "age INTEGER," + "city TEXT);";  
  39.   
  40.                 db.execSQL(CREATE_TABLE_Test);  
  41.                 Toast.makeText(SqlCreateTableActivity.this, "table created ", Toast.LENGTH_LONG).show();  
  42.   
  43.             }  
  44.         });  
  45.   
  46.   
  47.         Delete.setOnClickListener(newView.OnClickListener() {  
  48.   
  49.             @Override  
  50.             publicvoidonClick(View arg0) {  
  51.                 // TODO Auto-generated method stub  
  52.   
  53.                 db.execSQL("delete from tbl_Test");  
  54.   
  55.                 Toast.makeText(SqlCreateTableActivity.this, "data deleted ", Toast.LENGTH_LONG).show();  
  56.             }  
  57.         });  
  58.   
  59.   
  60.         Insert.setOnClickListener(newView.OnClickListener() {  
  61.   
  62.             @Override  
  63.             publicvoidonClick(View arg0) {  
  64.                 // TODO Auto-generated method stub  
  65.                 String sql = "INSERT or replace INTO tbl_Test (name,age,city) VALUES('Pankaj Kumar Choudhary',21,'Alwar')";  
  66.   
  67.                 db.execSQL(sql);  
  68.   
  69.                 sql = "INSERT or replace INTO tbl_Test (name,age,city) VALUES('Rahul ',21,'Jaipur')";  
  70.                 db.execSQL(sql);  
  71.   
  72.                 Toast.makeText(SqlCreateTableActivity.this, "Data Inserted ", Toast.LENGTH_LONG).show();  
  73.   
  74.             }  
  75.         });  
  76.   
  77.   
  78.         Select.setOnClickListener(newView.OnClickListener() {  
  79.   
  80.             @Override  
  81.             publicvoidonClick(View arg0) {  
  82.                 // TODO Auto-generated method stub  
  83.                 String name = "", city = "";  
  84.                 int ID = 0, age = 0;  
  85.   
  86.   
  87.                 Cursor mCursor = db.rawQuery("select * from tbl_Test"null);  
  88.   
  89.                 if (mCursor != null) {  
  90.                     mCursor.moveToFirst();  
  91.                     do {  
  92.                         ID = mCursor.getInt(0);  
  93.                         name = mCursor.getString(1);  
  94.                         age = mCursor.getInt(2);  
  95.                         city = mCursor.getString(3);  
  96.   
  97.                         Toast.makeText(SqlCreateTableActivity.this, ID + " " + name + " " + age + " " + city, Toast.LENGTH_SHORT).show();  
  98.                     } while (mCursor.moveToNext());  
  99.                 }  
  100.             }  
  101.         });  
  102.   
  103.   
  104.   
  105.   
  106.   
  107.     }  
  108. }  
Now I will explain this code in a number of sections such that it becomes very easy to understand.

Section 1
  1. Button Insert;  
  2. Button Create;  
  3. Button Select;  
  4. Button Delete;  
  5. final String dbpath="temp21.db";  
In the preceding code we create 4 objects of the Button class. We will use these objects to contain the references of the button that we use in the activity. The string dbpath contains the name of our database.

Section 2
  1. Create=(Button)findViewById((R.id.btnCreate));  
  2. Insert=(Button)findViewById((R.id.btnInsert));  
  3. Select=(Button)findViewById((R.id.btnSelect));  
  4. Delete=(Button)findViewById((R.id.btnDelete));  
In the preceding code we take the reference of our buttons. Create an object containing the reference of the Create Button. Insert an object containing the reference of the Insert Button. Select an object containing the reference of the Select Button. Delete an object containing the reference of the Delete Button.

Section 3
  1. final  SQLiteDatabase  db;  
  2. db = openOrCreateDatabase( dbpath , SQLiteDatabase.CREATE_IF_NECESSARY , null );  
In the preceding code we create an object of the SQLite database. The openOrCreateDatabase command will first check that if the database name already exists or not. If the database name doesn't exist then it will create a new database.

Section 4
  1. Create.setOnClickListener(newView.OnClickListener() {  
  2.   
  3.     @Override  
  4.     public void onClick(View arg0) {  
  5.         // TODO Auto-generated method stub  
  6.   
  7.         final String CREATE_TABLE_Test = "CREATE TABLE IF NOT EXISTS tbl_Test (" + "ID INTEGER primary key AUTOINCREMENT," + "name TEXT," + "age INTEGER," + "city TEXT);";  
  8.   
  9.         db.execSQL(CREATE_TABLE_Test);  
  10.         Toast.makeText(SqlCreateTableActivity.this"table created ", Toast.LENGTH_LONG).show();  
  11.   
  12.     }  
  13. });  
In the preceding code we create an event that will be executed when we click on the “Create table” button. On the raising of this event we execute a command that will create a table in the database. This command first checks whether the table already exists. If the table already exists then the table will be replaced otherwise a new table will we created.

When we click on the “Create Table” button a table will be created and for confirmation we will get a message.

SQL Create table

Section 5
  1. Delete.setOnClickListener(newView.OnClickListener() {  
  2.   
  3.     @Override  
  4.     public void onClick(View arg0) {  
  5.         // TODO Auto-generated method stub  
  6.   
  7.         db.execSQL("delete from tbl_Test");  
  8.   
  9.         Toast.makeText(SqlCreateTableActivity.this"data deleted ", Toast.LENGTH_LONG).show();  
  10.     }  
  11. });  
In the preceding code we create an event that will be executed when we click on the “Delete” button. On the raising of this event we execute a command that will delete all the data from the table.When we click on the “Delete” button all the data from the table will be deleted and for the confirmation we will get a message.

output

Section 6
  1. Insert.setOnClickListener(newView.OnClickListener() {  
  2.   
  3.     @Override  
  4.     public void onClick(View arg0) {  
  5.         // TODO Auto-generated method stub  
  6.         String sql = "INSERT or replace INTO tbl_Test (name,age,city) VALUES('Pankaj Kumar Choudhary',21,'Alwar')";  
  7.         db.execSQL(sql);  
  8.   
  9.         sql = "INSERT or replace INTO tbl_Test (name,age,city) VALUES('Rahul ',21,'Jaipur')";  
  10.         db.execSQL(sql);  
  11.   
  12.         Toast.makeText(SqlCreateTableActivity.this"Data Inserted ", Toast.LENGTH_LONG).show();  
  13.   
  14.     }  
  15. });  
In the preceding code we create an event that will be executed when we click on the “Insert” button. On the raising of this event we execute an insert command that will insert some data into the table. When we click on the “insert” button data will be inserted into the table and for confirmation we will get a message.

get a message

Section 7
  1. Select.setOnClickListener(newView.OnClickListener() {  
  2.   
  3.     @Override  
  4.     public void onClick(View arg0) {  
  5.         // TODO Auto-generated method stub  
  6.         String name = "", city = "";  
  7.         int ID = 0, age = 0;  
  8.   
  9.   
  10.         Cursor mCursor = db.rawQuery("select * from tbl_Test"null);  
  11.   
  12.         if (mCursor != null) {  
  13.             mCursor.moveToFirst();  
  14.             do {  
  15.                 ID = mCursor.getInt(0);  
  16.                 name = mCursor.getString(1);  
  17.                 age = mCursor.getInt(2);  
  18.                 city = mCursor.getString(3);  
  19.   
  20.                 Toast.makeText(SqlCreateTableActivity.this, ID + " " + name + " " + age + " " + city, Toast.LENGTH_SHORT).show();  
  21.             } while (mCursor.moveToNext());  
  22.         }  
  23.     }  
  24. });  
In the preceding code we create an event that will be executed when we click on the “Select” button. On the raising of this event we select all the data from the table and show it using a Toast method.

ouput

 

create table