SQL Table in Android

Introduction

SQLite is used as a database on 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 in 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 connections, 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.

<LinearLayout
	xmlns:android="http://schemas.android.com/apk/res/android"  
android:layout_width="fill_parent"  
android:layout_height="fill_parent"  
android:orientation="vertical">
	<Button  
android:id="@+id/btnCreate"  
android:layout_width="190dp"  
android:layout_height="wrap_content"  
android:text="@string/Create" />
	<Button  
android:id="@+id/btnInsert"  
android:layout_width="195dp"  
android:layout_height="wrap_content"  
android:text="@string/Insert" />
	<Button  
android:id="@+id/btnSelect"  
android:layout_width="192dp"  
android:layout_height="wrap_content"  
android:text="@string/Select"/>
	<Button  
android:id="@+id/btnDelete"  
android:layout_width="188dp"  
android:layout_height="wrap_content"  
android:text="@string/Delete" />
</LinearLayout>  

After the preceding code, our Activity will look as in the following:

Now we move to our project's Java code (source code). We use the following source code for our activity.

Now I will explain this code in a number of sections such that it becomes straightforward to understand.

Section 1

Button Insert;  
Button Create;  
Button Select;  
Button Delete;  
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 in the activity. The string dbpath includes the name of our database.

Section 2

Create=(Button)findViewById((R.id.btnCreate));  
Insert=(Button)findViewById((R.id.btnInsert));  
Select=(Button)findViewById((R.id.btnSelect));  
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

final  SQLiteDatabase  db;  
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 whether the database name already exists. If the database name doesn't exist, it will create a new one.

Section 4

Create.setOnClickListener(newView.OnClickListener() {  
  
    @Override  
    public void onClick(View arg0) {  
        // TODO Auto-generated method stub  
  
        final String CREATE_TABLE_Test = "CREATE TABLE IF NOT EXISTS tbl_Test (" + "ID INTEGER primary key AUTOINCREMENT," + "name TEXT," + "age INTEGER," + "city TEXT);";  
  
        db.execSQL(CREATE_TABLE_Test);  
        Toast.makeText(SqlCreateTableActivity.this, "table created ", Toast.LENGTH_LONG).show();  
  
    }  
});  

In the preceding code, we create an event that will be executed when we click 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 be created.

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

Section 5

Delete.setOnClickListener(newView.OnClickListener() {  
  
    @Override  
    public void onClick(View arg0) {  
        // TODO Auto-generated method stub  
  
        db.execSQL("delete from tbl_Test");  
  
        Toast.makeText(SqlCreateTableActivity.this, "data deleted ", Toast.LENGTH_LONG).show();  
    }  
});  

In the preceding code, we create an event that will be executed when we click the "Delete" button. On the raising of this event, we run 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 we will get a message for confirmation.

Section 6

Insert.setOnClickListener(newView.OnClickListener() {  
  
    @Override  
    public void onClick(View arg0) {  
        // TODO Auto-generated method stub  
        String sql = "INSERT or replace INTO tbl_Test (name,age,city) VALUES('Pankaj Kumar Choudhary',21,'Alwar')";  
        db.execSQL(sql);  
  
        sql = "INSERT or replace INTO tbl_Test (name,age,city) VALUES('Rahul ',21,'Jaipur')";  
        db.execSQL(sql);  
  
        Toast.makeText(SqlCreateTableActivity.this, "Data Inserted ", Toast.LENGTH_LONG).show();  
  
    }  
});  

In the preceding code, we create an event that will be executed when we click 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 we will get a message for confirmation.

Section 7

Select.setOnClickListener(newView.OnClickListener() {  
  
    @Override  
    public void onClick(View arg0) {  
        // TODO Auto-generated method stub  
        String name = "", city = "";  
        int ID = 0, age = 0;  
  
  
        Cursor mCursor = db.rawQuery("select * from tbl_Test", null);  
  
        if (mCursor != null) {  
            mCursor.moveToFirst();  
            do {  
                ID = mCursor.getInt(0);  
                name = mCursor.getString(1);  
                age = mCursor.getInt(2);  
                city = mCursor.getString(3);  
  
                Toast.makeText(SqlCreateTableActivity.this, ID + " " + name + " " + age + " " + city, Toast.LENGTH_SHORT).show();  
            } while (mCursor.moveToNext());  
        }  
    }  
});  

In the preceding code, we create an event that will be executed when we click the "Select" button. On raising this event, we select all the data from the table and show it using a Toast method.

Summary

This article taught us how to use SQL Table in Android with code examples and output images. I hope you enjoyed it.