ANDROID SQLITE DATABASE

ANDROID SQLITE DATABASE

________________________________________________

SQL databases :

It is used to Store data in tables of rows and columns. The intersection of a row and column is

called as a field and contains data. The fields are references to other fields or other tables

Fields contain data, references to other fields, or references to other tables. Rows in the

database tables are identified by unique IDs. The database  Columns are identified by the

names and are unique per table.

An SQLite database is a good storage solution when you have structured data means

students data or Employer’s data etc. that you need to store and access, search, and make

change as and when required. If you want to  represent your data with  rows and columns,

consider a SQLite database.

 

Cursor

When we query the database by using SQLiteDatabase it always returns  the results as

a Cursor in a table format that resembles that of a SQL database. If  the data as an array of rows.

A cursor is a pointer into one row of that structured( table data) data. The Cursor class has

methods that are used to moving the cursor through the data structure, and methods to get the

data from the fields in each row etc.

Some common operations on cursor are:

getCount() returns the number of rows in the database to the cursor.

getColumnNames() returns a string array holding the names of all of the columns.

getPosition() returns the current position of the cursor in the row.

To use SQL database, we have to implement SQLiteOpenHelper class to return the cursor object

to the calling activity or adapter, Here adapter class acts as a mediator between AdapterView and

Database.

ContentValues :

To  store data, an instance of ContentValues are used. it stores data as key-value pairs, where

the key represents the name of the column and the value is the value for the cell. One instance of

ContentValues represents one row of a table.

The insert() method for the database requires that the values to fill a row are passed as an

instance of ContentValues.

To insert data into the database we are passing the values for rows as a instance of

ContentValues. Below sample code describes how to insert data into database using

ContentValues and insert() method.

ContentValues values = new ContentValues();

// Insert one row. Use a loop to insert multiple rows.

values.put(KEY_WORD, "Android");

values.put(KEY_DEFINITION, "Mobile operating system.");

 

db.insert(WORD_LIST_TABLE, null, values);

 

Creating an SQLite database:

To create a database for your Android app, you need to do the following.

Create a data model.

Subclass  SQLIteOpenHelper. Use constants for table names and database creation query

Implement onCreate to create the SQLiteDatabase with tables for your data. To create

SQLIteDatabase in our application we have to impement it inside onCreate() methods

create onUpgrade() : to make data base changes means creating new user or deleting the user data

etc.

Creating the query(),

insert(): this method is used to insert a row into the database.

delete(): delete method used to delete the database and database table.

update() :update method is used to update the data base means example:adding/deleting user data.

count() : this method is used to get number of rows in the database.

methods in SQLiteOpenHelper.

In your MainActivity, create an instance of SQLiteOpenHelper.

Call methods of SQLiteOpenHelper to work with your database

 

Data model :

create a class that represents your data with getters and setters methods as shown below example code:

public class WordItem {

private int mId;

private String mWord;

private String mDefinition;

// Getters and setters and more

}

Subclass SQLiteOpenHelper :

Any open helper you create must extend SQLiteOpenHelper. As given below example code.

public class WordListOpenHelper extends SQLiteOpenHelper {

public WordListOpenHelper(Context context) {

super(context, DATABASE_NAME, null, DATABASE_VERSION);

Log.d(TAG, "Construct WordListOpenHelper");

}

}

 

Define constants for table names :

it is customary to declare your table, column, and row names as constants. This makes your code a lot more readable, makes it developer  friendly to change names easily.

 private static final int DATABASE_VERSION = 1;
  // has to be 1 first time or app will crash
  private static final String WORD_LIST_TABLE = "word_entries";
  private static final String DATABASE_NAME = "wordlist";

  // Column names...
  public static final String KEY_ID = "_id";
  public static final String KEY_WORD = "word";

  // ... and a string array of columns.
  private static final String[] COLUMNS = {KEY_ID, KEY_WORD};

query for creating database :

The below example sample code used for creating database.

private static final String WORD_LIST_TABLE_CREATE =
            "CREATE TABLE " + WORD_LIST_TABLE + " (" +
             KEY_ID + " INTEGER PRIMARY KEY, " +
             // will auto-increment if no value passed
             KEY_WORD + " TEXT );";

Implement onCreate() and create the database:

The onCreate method is only called if there is no database. Create your tables in the method, and optionally add initial data.

@Override
public void onCreate(SQLiteDatabase db) { // Creates new database
   db.execSQL(WORD_LIST_TABLE_CREATE); // Create the tables
   fillDatabaseWithData(db); // Add initial data
   // Cannot initialize mWritableDB and mReadableDB here, because
   // this creates an infinite loop of on Create()
   // being repeatedly called.
}

Implement onUpgrade() :

This is a required method. If you want to modify database table, delete, update extra are done by this onUpgrade(). If you want to delate table and the recreate them also.

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // SAVE USER DATA FIRST!!!
        Log.w(WordListOpenHelper.class.getName(),
                "Upgrading database from version " + oldVersion + " to "
                        + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS " + WORD_LIST_TABLE);
        onCreate(db);
}

query() :

 

The  query method implemented in OpenHelper class returns any data types that user interface needs

The database provides two methods for sending queries:

SQLiteDatabase.rawQuery()

SQLiteDatabase.query().

 

SQLiteDatabase.rawQuery()

The open helper query method can construct an SQL query and then send it as a rawQuery to the database which returns a cursor. Means your data is supplied by your app, and under your full control, you can use rawQuery().

rawQuery(String sql, String[] selectionArgs)

 

The first parameter to db.rawquery() is an SQLite query string.

The second parameter contains the arguments.

cursor = mReadableDB.rawQuery(queryString, selectionArgs);

 

SQLiteDatabase.query()

If you are processing user-supplied data, even after validation, it is more secure to construct a query and use a version of the SQLiteDatabase.query() method for the database.

Cursor query (boolean distinct,  String table, String[] columns, String selection,
                String[] selectionArgs, String groupBy, String having,  
                String orderBy,String limit)

Here is a sample  basic example code:

String[] columns = new String[]{KEY_WORD};
String where =  KEY_WORD + " LIKE ?";
searchString = "%" + searchString + "%";
String[] whereArgs = new String[]{searchString};
cursor = mReadableDB.query(WORD_LIST_TABLE, columns, where, whereArgs, null, null, null);

 

Example of complete open helper query()

public WordItem query(int position) {
   String query = "SELECT  * FROM " + WORD_LIST_TABLE +
           " ORDER BY " + KEY_WORD + " ASC " +
           "LIMIT " + position + ",1";

   Cursor cursor = null;
   WordItem entry = new WordItem();

   try {
       if (mReadableDB == null) {mReadableDB = getReadableDatabase();}
       cursor = mReadableDB.rawQuery(query, null);
       cursor.moveToFirst();
       entry.setId(cursor.getInt(cursor.getColumnIndex(KEY_ID)));
       entry.setWord(cursor.getString(cursor.getColumnIndex(KEY_WORD)));
   } catch (Exception e) {
       Log.d(TAG, "EXCEPTION! " + e);
   } finally {
       // Must close cursor and db now that we are done with it.
       cursor.close();
       return entry;
   }
}

 

__________________________________________________

The following example given below used to create Students database tabls using SQLIte Database and store Students name and Students callege name into the Database

 

 

AndroidManifest.xml : 

The manifest file is creted by android and it has name of the package and activity name here it is MainActivity.

 

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.example.iflutter.sqlitedatabasedemo">

    <application
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:roundIcon="@mipmap/ic_launcher_round"
        android:supportsRtl="true"
        android:theme="@style/AppTheme">
        <activity android:name=".MainActivity">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

 

activity_main.xml :

The activity_main.xml layout file was created for user input value. it has two input value one is enter students name and enter college name. these two input values has uniq ID’s to identify them. And it also have two Buttons one is doSave and other is load these Buttons has two uniq Identifiers to identify them.

 

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout  xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">

    <TextView
        android:id="@+id/textView"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentTop="true"
        android:layout_centerHorizontal="true"
        android:layout_marginTop="51dp"
        android:text="SQLiteDatabase"
        android:textSize="30sp" />

    <EditText
        android:id="@+id/edit1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_alignParentStart="true"
        android:layout_alignParentTop="true"
        android:layout_marginStart="30dp"
        android:layout_marginTop="140dp"
        android:ems="10"
        android:hint="Enter Student Name"
        android:inputType="textPersonName" />

    <EditText
        android:id="@+id/edit2"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_alignParentStart="true"
        android:layout_alignParentTop="true"
        android:layout_marginStart="15dp"
        android:layout_marginTop="219dp"
        android:ems="10"
        android:hint="Enter Your College Name"
        android:inputType="textPersonName" />

    <Button
        android:id="@+id/button"
        android:layout_width="wrap_content"
        android:onClick="doSave"
        android:layout_height="wrap_content"
        android:layout_alignParentStart="true"
        android:layout_below="@+id/edit2"
        android:text="SAVE" />

    <Button
        android:id="@+id/button2"
        android:layout_width="wrap_content"
        android:onClick="doLoad"
        android:layout_height="wrap_content"
        android:layout_alignEnd="@+id/textView"
        android:layout_alignParentBottom="true"
        android:layout_marginBottom="191dp"
        android:text="LOAD" />
</RelativeLayout>

 

MainActivity.java:

In this MainActivity class code. we are loading the activity_main.xml layout file and we are getting the user input value. And passing  them to the inser() method to load into the student database table by using databse instance inside doSave() method of the Activity class .

We are getting all the data from database stored in student table inside doLoad() method by executing  myData.getAll(); code inside the MainActivity

package com.example.shyamkumar.sqlitedatabasedemo;

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.ListView;

public class MainActivity extends AppCompatActivity {
EditText name,college;
MyCoreDatabase myData;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        name = (EditText)findViewById(R.id.edit1);
        college = (EditText)findViewById(R.id.edit2);
        myData = new MyCoreDatabase(this);

    }

    public void doSave(View view) {
        // through mydata object data is saved into data base table
        myData.insertData(name.getText().toString(),college.getText().toString());
    }myData.getAll();

    public void doLoad(View view) {
        
    }
}

 

MyCoreDatabase.java :

MyCoreDatabase class extends SQLiteOpenHelper class. Inside MyCoreDatabase class we are creating the database name and database table name. Using onCreate() method we are creating the database table name.Using onUpgrade() method we are updating the database database. Using insert() method we are iserting the user entered data in layout file that was passed fromMainActivity to this class. And here this class stores the user entered data into the database table.

Using getAll() method we are getting the data from the database table and passig it to the MainActivity from there it is dispayed to the user by executing the layout file  load UI interface. or when we click on load it loads all the data from the students table to the user.

 

package com.example.shyamkumar.sqlitedatabasedemo;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;
import android.widget.ListView;

public class MyCoreDatabase extends SQLiteOpenHelper {

    static final private String DB_NAME = "Education";
    static final private String DB_TABLE = "students";
    static final private int DB_VER = 1;
    Context ctx;
    SQLiteDatabase myDb;
    public MyCoreDatabase(Context ct){
        super(ct,DB_NAME,null,DB_VER);
        ctx = ct;
    }



    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase){
        sqLiteDatabase.execSQL("create table "+DB_TABLE+" (_id integer primary key autoincrement,stu_name text,college_name text);");
        Log.i("Database","Table created");

    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {

        sqLiteDatabase.execSQL("drop table if exist"+DB_TABLE);
        onCreate(sqLiteDatabase);


    }
    public void insertData(String s1,String s2){

        myDb = getWritableDatabase();
        myDb.execSQL("insert into "+DB_TABLE+" (stu_name,college_name) values('"+s1+"','"+s2+"');");
        Toast.makeText(ctx,"data saved successfully",Toast.LENGTH_SHORT).show();


    }
    public void getAll(){

        myDb = getReadableDatabase();
       // Cursor cr = myDb.rawQuery("Select * from "+DB_NAME,null);
        Cursor cr = myDb.rawQuery("select * from "+DB_TABLE,null);
        StringBuilder str = new StringBuilder();
        while(cr.moveToNext()){
            String s1 = cr.getString(1);
            String s2 = cr.getString(2);
            str.append(s1+"     "+s2+" \n");
        }
        Toast.makeText(ctx,str.toString(),Toast.LENGTH_LONG).show();
    }


}
Congratulations You Have Learned About SQLite Database Using In Android Studio.
1 Response

Leave a Reply

Categories

Myles [CPS] IN
Shein WW