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:

<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 the Java code (source code) of our project. We use the following source code for our activity.

packagepankaj.sql.android;



importdzone.sql.android.R;
importandroid.app.Activity;
importandroid.database.Cursor;
importandroid.database.sqlite.SQLiteDatabase;
importandroid.os.Bundle;
importandroid.view.View;
importandroid.widget.Button;
importandroid.widget.Toast;

publicclassSqlCreateTableActivityextends Activity {


    Button Insert;
    Button Create;
    Button Select;
    Button Delete;
    final String dbpath = "temp21.db";@Override
    publicvoidonCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        Create = (Button) findViewById((R.id.btnCreate));
        Insert = (Button) findViewById((R.id.btnInsert));
        Select = (Button) findViewById((R.id.btnSelect));
        Delete = (Button) findViewById((R.id.btnDelete));
        finalSQLiteDatabasedb;
        db = openOrCreateDatabase(dbpath, SQLiteDatabase.CREATE_IF_NECESSARY, null);

        Create.setOnClickListener(newView.OnClickListener() {

            @Override
            publicvoidonClick(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();

            }
        });


        Delete.setOnClickListener(newView.OnClickListener() {

            @Override
            publicvoidonClick(View arg0) {
                // TODO Auto-generated method stub

                db.execSQL("delete from tbl_Test");

                Toast.makeText(SqlCreateTableActivity.this, "data deleted ", Toast.LENGTH_LONG).show();
            }
        });


        Insert.setOnClickListener(newView.OnClickListener() {

            @Override
            publicvoidonClick(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();

            }
        });


        Select.setOnClickListener(newView.OnClickListener() {

            @Override
            publicvoidonClick(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());
                }
            }
        });





    }
}

Now I will explain this code in a number of sections such that it becomes very easy 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 that we use in the activity. The string dbpath contains 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 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

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 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.



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 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.



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 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.



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 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.