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.