Inserting a single data in database is easy, but when we have 1000's of data to be inserted it takes lot of time , because we are calling 1000's of sql queries. So in this tutorial I will show you how you can insert large data in seconds.
In this tutorial we will make a Student database, contains table Record which is supposed to store students information , table columns are-
Project hierarchy is this-
 |
| Project hierarchy |
Student.java
package com.org.model;
public class Student {
String
name;
String
subject;
int className;
int marks;
public String
getName() {
return name;
}
public void setName(String
name) {
this.name = name;
}
public String
getSubject() {
return subject;
}
public void
setSubject(String subject) {
this.subject = subject;
}
public int getClassName()
{
return className;
}
public void setClassName(int className) {
this.className = className;
}
public int getMarks() {
return marks;
}
public void setMarks(int marks) {
this.marks = marks;
}
}
|
1. Now make DatabaseConstant.java class in com.org.database package .In this class all the constants which are related to Database will be defined
DatabaseConstant.java
package
com.org.database;
class
DatabaseConstant {
static final int DB_VERSION = 1; // Database Version number
static final String DB_NAME = "Student"; // Database Name
static final String KEY_ID = "_id"; // Autoincrement field
static final String TABLE_RECORD = "Record"; // Table Name
//Table fields
static final String RECORD_NAME = "name";
static final String RECORD_SUBJECT = "subject";
static final String RECORD_CLASS = "class";
static final String RECORD_MARKS = "marks";
// Create
Table "Record" Query
static final String CREATE_TABLE_RECORD = "CREATE
TABLE " + TABLE_RECORD + " (
" + KEY_ID + " integer primary key autoincrement, " + RECORD_NAME + " text,
" + RECORD_SUBJECT + " text, " + RECORD_CLASS + "
integer," + RECORD_MARKS + " integer)";
}
|
2. Make DatabaseHelper.java class in com.org.database and extends SQLiteOpenHelper.
DatabaseHelper.java
package
com.org.database;
import
android.content.Context;
import
android.database.sqlite.SQLiteDatabase;
import
android.database.sqlite.SQLiteOpenHelper;
public class DatabaseHelper extends
SQLiteOpenHelper{
public
DatabaseHelper(Context context) {
super(context,
DatabaseConstant.DB_NAME, null, DatabaseConstant.DB_VERSION);
}
@Override
public void
onCreate(SQLiteDatabase db) {
// Executing Create Table "Record" Query
db.execSQL(DatabaseConstant.CREATE_TABLE_RECORD);
}
@Override
public void
onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO
Auto-generated method stub
}
}
|
3. Now make DatabaseAdapter.java class in com.org.database . In this class functions like opening and closing of database , query functions are written.
DatabaseAdapter.java
package
com.org.database;
import java.util.List;
import
android.content.Context;
import
android.database.Cursor;
import
android.database.DatabaseUtils;
import
android.database.SQLException;
import
android.database.sqlite.SQLiteDatabase;
import
android.text.TextUtils;
import com.org.model.Student;
public class DatabaseAdapter
{
private Context context;
private SQLiteDatabase database;
private DatabaseHelper dbHelper;
//Constructor
protected
DatabaseAdapter(Context context){
this.context = context;
}
//Open
database
protected DatabaseAdapter
open() throws SQLException{
dbHelper = new DatabaseHelper(context);
database = dbHelper.getWritableDatabase();
return this;
}
//Close
Database
protected void close() {
dbHelper.close();
}
//Insert Data
into Record Table
protected void
insertStudentRecord(List<Student> studentData){
/*We have to
make this query-
* INSERT INTO
Record (name, subject, class, marks) VALUES (name1, subject1, class1, marks1),
(name2, subject2, class2, marks2).....
*/
String
values = buildQueryValueForRecordInsert(studentData);
String
query = "INSERT INTO " + DatabaseConstant.TABLE_RECORD + "("
+ DatabaseConstant.RECORD_NAME + ", " + DatabaseConstant.RECORD_SUBJECT + ", " + DatabaseConstant.RECORD_CLASS + ", " +
DatabaseConstant.RECORD_MARKS + ") VALUES " + values;
database.execSQL(query);
}
//build values
private String
buildQueryValueForRecordInsert(List<Student> studentData){
//String array of size equals to list size
String[]
valueArray = new String[studentData.size()];
for(int i = 0; i <
studentData.size(); i++){
// Fetching model from list one by one
Student
data = studentData.get(i);
// Fetching name from model
String
name = data.getName();
// Fetching subject from model
String
subject = data.getSubject();
// Fetching className from model
String
className = "" + data.getClassName();
// Fetching marks from model
String
marks = "" + data.getMarks();
// Make an String array of name, subject, class, marks
String[]
values = new String[]{name, subject, className, marks};
// Pass the string array in buildQueryInsertValue
valueArray[i]
= buildQueryInsertValue(values);
}
return TextUtils.join(",", valueArray);
}
/**Below
function takes string array as input and return a string with "(" at
the start & ")" at the end
* with each array
element separated by a comma(",")
* So if the string array
is new String[]{"name", "subject1", "10",
"75"}
* Output will be -> ('name',
'subject1', '10', '75')
*/
private String
buildQueryInsertValue(String[] values){
for(int i = 0; i <
values.length; i++){
if(values[i] == null){
values[i]
= "";
}
values[i]
= DatabaseUtils.sqlEscapeString(values[i]);
}
return "(" + TextUtils.join(",", values) + ")";
}
//get number
of rows in Record table
protected Cursor
getRecordDataCount() {
String
query = "SELECT Count() as c FROM " +
DatabaseConstant.TABLE_RECORD;
return database.rawQuery(query,
null);
}
}
|
A. open() - This function opens the database.
B. close() - This function close the database.
C. getRecordDataCount() - returns the number of rows in Record table.
D. insertStudentRecord(List<Student> studentData) - This function takes List of Student data as parameter and insert data in Record table.
E. buildQueryInsertValue(String[] values) - This function takes array of String as parameter and return a string where each element of array is separated by comma (",") and adding "(" at the start and ")" at the end. Suppose array elements are-
values = new String[]{"name", "subject", "10", "75"};
i. Iterate it with for loop.
ii. Pass that value from DatabaseUtils.sqlEscapeString. This function handles the escape characters (as we are making the query without using ContentValues so we have to handle escape characters our-self).
iii. TextUtils.join(",", values) this function separates the String array element with comma(",") and returns a string.
B. close() - This function close the database.
C. getRecordDataCount() - returns the number of rows in Record table.
D. insertStudentRecord(List<Student> studentData) - This function takes List of Student data as parameter and insert data in Record table.
E. buildQueryInsertValue(String[] values) - This function takes array of String as parameter and return a string where each element of array is separated by comma (",") and adding "(" at the start and ")" at the end. Suppose array elements are-
values = new String[]{"name", "subject", "10", "75"};
i. Iterate it with for loop.
ii. Pass that value from DatabaseUtils.sqlEscapeString. This function handles the escape characters (as we are making the query without using ContentValues so we have to handle escape characters our-self).
iii. TextUtils.join(",", values) this function separates the String array element with comma(",") and returns a string.
iv. At last this function return string as - ('name', 'subject', '10', '75').
F. buildQueryValueForRecordInsert(List<Student> studentData) - This function takes List of Student data and return a String forming the VALUES part of the query.
i. In this function we will pass the String array to the buildQueryInsertValue which will return String in the format of - ('name', 'subject', '10', '75'). which is stored in a array Like-
new Stirng[]{('name', 'subject', '10', '75'), ('name1', 'subject1', '12', '85'),('name3', 'subject3', '8', '70')};
ii. At last that array is passed into TextUtils.join(",", values) which separates the String array element with comma(",") and returns a string. So the output of this function will be.
('name', 'subject', '10', '75'), ('name1', 'subject1', '12', '85'),('name3', 'subject3', '8', '70')
4. Now create the last class of com.org.database package DatabaseController.java. This is the only class whose functions will be called from outside this package.
In this class a recursive function is written which is responsible for inserting 1000's of data in seconds.
A. insertRecord() - This function takes the list of student data.
i. Suppose you want to insert 2,500 data of student then this list contains information of 2,500 student.
ii. dbAdapter = new DatabaseAdapter(context).open() - will open the database.
iii. dbAdapter.close() - will close the database.
B. insertRecordRecursion() - This is how this function works-
i. Check if listSize == 0 return; Means list is empty.
ii. Get the number of entries in Record Table -> countBeforeInsertion.
iii. dbAdapter.insertStudentRecord() - Inserts data in the table. This should be written in try catch because if somehow data not entered in the table it will throw a exception.
iv. Again check the number of records present in Record table -> countAfterInsertion
v. If (countAfterInsertion == countBeforeInsertion) Means data is not entered in the table.
vi. Now check if listSize == 1. If it is true means there is some problem in that data, or you are using UNIQUE constraint in any of the column in your table. So this data will be skipped and will not enter in the table.
vii. If listSize != 1 then break the list into two halves and with each half call this function again.And this will continue until listSize becomes zero.
If table column don't have UNIQUE or any other constraint then 2,500 data will be inserted in 14 attempts.
The maximum limit of sqlite to insert data is 500. This means you can only insert max 500 data at a time, So your query will be -
INSERT INTO TABLE_NAME (COLUMN1, COLUMN2) VALUES (VC11, VC21),(VC12, VC22),(VC13, VC23),(VC14, VC24)...................(VC1500, VC2500).
Also , if there is other limit then you don't have to worry because our recursion method handles it.
5. Now lets make a UI to check how it works, this will be very simple UI which will have a editText for the number of data to insert and a Button. Open activity_main.xml and write down the following code-
6. Open MainActivity.java and write down the following code.
Sometime You may see in your Logcat. This is because you are trying to insert more than 500 data at a time.
F. buildQueryValueForRecordInsert(List<Student> studentData) - This function takes List of Student data and return a String forming the VALUES part of the query.
i. In this function we will pass the String array to the buildQueryInsertValue which will return String in the format of - ('name', 'subject', '10', '75'). which is stored in a array Like-
new Stirng[]{('name', 'subject', '10', '75'), ('name1', 'subject1', '12', '85'),('name3', 'subject3', '8', '70')};
ii. At last that array is passed into TextUtils.join(",", values) which separates the String array element with comma(",") and returns a string. So the output of this function will be.
('name', 'subject', '10', '75'), ('name1', 'subject1', '12', '85'),('name3', 'subject3', '8', '70')
4. Now create the last class of com.org.database package DatabaseController.java. This is the only class whose functions will be called from outside this package.
In this class a recursive function is written which is responsible for inserting 1000's of data in seconds.
DatabaseController.java
package
com.org.database;
import
java.util.ArrayList;
import java.util.List;
import
android.content.Context;
import
android.database.Cursor;
import
com.org.model.Student;
public class
DatabaseController {
public
DatabaseController(){}
private static
DatabaseController instance = null;
public static
DatabaseController getInstance(){
if(instance == null){
instance = new
DatabaseController();
}
return instance;
}
public void
insertRecord(Context context, ArrayList<Student> studentData){
DatabaseAdapter
dbAdapter = null;
try{
dbAdapter
= new
DatabaseAdapter(context).open(); //Open
database
insertRecordRecursion(studentData,
context, dbAdapter);
dbAdapter.close(); //Close database
}catch(Exception e){
e.printStackTrace();
}
}
private void
insertRecordRecursion(List<Student> studentData, Context context,
DatabaseAdapter dbAdapter){
int dataSize =
studentData.size();
if(dataSize == 0)
return;
int
countBeforeInsertion = getRecordDataCount(context);
try{
dbAdapter.insertStudentRecord(studentData);
}catch(Exception e){
e.printStackTrace();
}
int
countAfterInsertion = getRecordDataCount(context);
if(countAfterInsertion
== countBeforeInsertion){
if(dataSize == 1)
return;
List<Student>
firstHalfData = studentData.subList(0, dataSize/2);
List<Student>
secondHalfData = studentData.subList(dataSize/2, dataSize);
insertRecordRecursion(firstHalfData,
context, dbAdapter);
insertRecordRecursion(secondHalfData,
context, dbAdapter);
}
}
public int
getRecordDataCount(Context context){
DatabaseAdapter
dbAdapter = null;
Cursor
c = null;
int count = 0;
try{
dbAdapter
= new DatabaseAdapter(context).open();
c
= dbAdapter.getRecordDataCount();
c.moveToFirst();
count
= c.getInt(c.getColumnIndex("c"));
c.close();
dbAdapter.close();
}catch(Exception e){
e.printStackTrace();
}
return count;
}
}
|
A. insertRecord() - This function takes the list of student data.
i. Suppose you want to insert 2,500 data of student then this list contains information of 2,500 student.
ii. dbAdapter = new DatabaseAdapter(context).open() - will open the database.
iii. dbAdapter.close() - will close the database.
B. insertRecordRecursion() - This is how this function works-
i. Check if listSize == 0 return; Means list is empty.
ii. Get the number of entries in Record Table -> countBeforeInsertion.
iii. dbAdapter.insertStudentRecord() - Inserts data in the table. This should be written in try catch because if somehow data not entered in the table it will throw a exception.
iv. Again check the number of records present in Record table -> countAfterInsertion
v. If (countAfterInsertion == countBeforeInsertion) Means data is not entered in the table.
vi. Now check if listSize == 1. If it is true means there is some problem in that data, or you are using UNIQUE constraint in any of the column in your table. So this data will be skipped and will not enter in the table.
vii. If listSize != 1 then break the list into two halves and with each half call this function again.And this will continue until listSize becomes zero.
 |
| Recursion |
The maximum limit of sqlite to insert data is 500. This means you can only insert max 500 data at a time, So your query will be -
INSERT INTO TABLE_NAME (COLUMN1, COLUMN2) VALUES (VC11, VC21),(VC12, VC22),(VC13, VC23),(VC14, VC24)...................(VC1500, VC2500).
Also , if there is other limit then you don't have to worry because our recursion method handles it.
5. Now lets make a UI to check how it works, this will be very simple UI which will have a editText for the number of data to insert and a Button. Open activity_main.xml and write down the following code-
activity_main.xml
<?xml version="1.0"
encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical" >
<EditText
android:id="@+id/textNumber"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:inputType="number"
/>
<Button
android:id="@+id/btnFetch"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center_horizontal"
android:text="Fetch And Store" />
<TextView
android:id="@+id/textViewLog"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textSize="15dp"
/>
</LinearLayout>
|
6. Open MainActivity.java and write down the following code.
MainActivity.java
package
com.tutorialsface.databasedemo;
import
java.util.ArrayList;
import
java.util.Random;
import
com.org.database.DatabaseController;
import com.org.model.Student;
import
android.app.Activity;
import
android.os.Bundle;
import
android.util.Log;
import
android.view.View;
import
android.view.View.OnClickListener;
import
android.widget.Button;
import
android.widget.EditText;
import
android.widget.TextView;
public class MainActivity extends Activity{
private Button btnFetch;
private EditText textNumber;
private TextView textViewLog;
@Override
protected void onCreate(Bundle
savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
init();
}
private void init() {
btnFetch = (Button)
findViewById(R.id.btnFetch);
textNumber = (EditText)
findViewById(R.id.textNumber);
textViewLog = (TextView)
findViewById(R.id.textViewLog);
btnFetch.setOnClickListener(new
OnClickListener() {
@Override
public void onClick(View v)
{
textViewLog.setText("");
try{
getData();
}catch(Exception e){}
}
});
}
void getData(){
String
TAG = "getData";
DatabaseController
dbController = DatabaseController.getInstance();
long t1 = System.currentTimeMillis();
ArrayList<Student>
studentData = makeData();
long t2 = System.currentTimeMillis();
textViewLog.setText("Number
of Data to insert: " + studentData.size());
Log.d(TAG, TAG + "Time dataSize " + studentData.size());
Log.d(TAG, TAG + "Time Fetching " + ((float)(t2-t1)/1000) +
"
sec");
textViewLog.setText(textViewLog.getText().toString()
+ "\nData
Fetching Time: " + ((float)(t2-t1)/1000) +
"
sec");
textViewLog.setText(textViewLog.getText().toString()
+ "\nNumber
of Records before Insertion:" +
dbController.getRecordDataCount(getApplicationContext()));
long t3 = System.currentTimeMillis();
dbController.insertRecord(getApplicationContext(),
studentData);
long t4 = System.currentTimeMillis();
Log.d(TAG, TAG + "Time Insertion " + ((float)(t4-t3)/1000) +
"
sec");
textViewLog.setText(textViewLog.getText().toString()
+ "\nInsertion
Time: " + ((float)(t4-t3)/1000) +
"
sec");
textViewLog.setText(textViewLog.getText().toString()
+ "\nNumber
of Records after Insertion:" +
dbController.getRecordDataCount(getApplicationContext()));
Log.d(TAG, TAG + "Number of Records" +
dbController.getRecordDataCount(getApplicationContext()));
}
private
ArrayList<Student> makeData() {
Random
random = new Random();
String[]
subjects = new String[]{"Physics", "Chemistry", "Maths", "Biology", "English"};
int numberOfData =
Integer.parseInt(textNumber.getText().toString());
ArrayList<Student>
studentData = new ArrayList<Student>();
for(int i = 0; i <
numberOfData; i++){
int randomNameMarks
= random.nextInt(101);
int randomSubject =
random.nextInt(subjects.length);
int randomClass =
random.nextInt(13);
Student
data = new Student();
data.setName("Student" +
randomNameMarks);
data.setSubject(subjects[randomSubject]);
data.setClassName(randomClass);
data.setMarks(randomNameMarks);
studentData.add(data);
}
return studentData;
}
}
|
- makeData() function prepares a List of the data on the basis of size provided in the editText.
- Now run the project and enter the number and press the button.
Sometime You may see in your Logcat. This is because you are trying to insert more than 500 data at a time.
Do you have car and bike parking places data base with images.....
ReplyDelete