Storing JSON data also called as complete JSON object that is coming from server into SQLite database is very complex functionality because there are so much code we need to prepare for both server side and android application side. In this tutorial we would going to create an android application that parse the JSON data and store sync that JSON into SQLite database. Each time when new data arrives it will delete all the SQLite table previous data and refill all the database table with newly parsed server values. So here is the complete step by step tutorial for Android Store PHP MySQL JSON Parsing data into SQLite Database.
Project File Description :
List of Activity in this project:
- MainActivity.java
- ShowDataActivity.java
List of Java files in this project:
- HttpServiceClass.java
- ListAdapter.java
- SQLiteHelper.java
List of layout files in this project:
- activity_main.xml
- activity_show_data.xml
- items.xml
List of PHP files in this project:
- DatabaseConfig.php
- SubjectFullForm.php
Contents in this project for Sync Store PHP MySQL JSON Parsing data SQLite db:
- Watch live demo video.
- Create database including table on your server.
- Create & Run PHP Script to convert MySQL db data to JSON data.
- Start a new android application development project.
- Add org.apache.http.legacy library in your project.
- Add internet permission in your project.
- Start Coding.
1. Watch Live Demo Video :
2. Create database including table on your server :
3. Create & Run PHP Script to convert MySQL db data to JSON data :
Upload the below code with your server configuration on your server.
Code for DatabaseConfig.php file.
<?php //Define your host here. $HostName = "localhost"; //Define your database username here. $HostUser = "id632449_androidjson"; //Define your database password here. $HostPass = "kdfjdfdskljomew9ry3873"; //Define your database name here. $DatabaseName = "id632449_androidjson"; ?>
Code for SubjectFullForm.php file.
<?php include 'DatabaseConfig.php'; // Create connection $conn = new mysqli($HostName, $HostUser, $HostPass, $DatabaseName); if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "SELECT * FROM SubjectFullFormTable"; $result = $conn->query($sql); if ($result->num_rows >0) { while($row[] = $result->fetch_assoc()) { $tem = $row; $json = json_encode($tem); } } else { echo "No Results Found."; } echo $json; $conn->close(); ?>
Screenshot of JSON after run above PHP Script :
4. Start a new android application development project.
5. Add org.apache.http.legacy library in your project :
1. Open Your Project’s build.gradle(Module:app) file .
2. Add useLibrary ‘org.apache.http.legacy’ in android scope .
6. Add internet permission in your project :
Open your Project’s AndroidManifest.xml file and add internet permission inside it.
<uses-permission android:name="android.permission.INTERNET" />
7. Start Coding Store PHP MySQL JSON Parsing data in SQLite :-
Code for MainActivity.java file.
package com.androidjson.jsonstoreinsqlite_androidjsoncom; import android.app.ProgressDialog; import android.content.Context; import android.content.Intent; import android.database.sqlite.SQLiteDatabase; import android.os.AsyncTask; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.view.View; import android.widget.Button; import android.widget.Toast; import org.json.JSONArray; import org.json.JSONException; import org.json.JSONObject; public class MainActivity extends AppCompatActivity { SQLiteDatabase sqLiteDatabase; Button SaveButtonInSQLite, ShowSQLiteDataInListView; String HttpJSonURL = "https://androidjsonblog.000webhostapp.com/SubjectFullForm.php"; ProgressDialog progressDialog; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); SaveButtonInSQLite = (Button)findViewById(R.id.button); ShowSQLiteDataInListView = (Button)findViewById(R.id.button2); SaveButtonInSQLite.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { SQLiteDataBaseBuild(); SQLiteTableBuild(); DeletePreviousData(); new StoreJSonDataInToSQLiteClass(MainActivity.this).execute(); } }); ShowSQLiteDataInListView.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { Intent intent = new Intent(MainActivity.this, ShowDataActivity.class); startActivity(intent); } }); } private class StoreJSonDataInToSQLiteClass extends AsyncTask<Void, Void, Void> { public Context context; String FinalJSonResult; public StoreJSonDataInToSQLiteClass(Context context) { this.context = context; } @Override protected void onPreExecute() { super.onPreExecute(); progressDialog = new ProgressDialog(MainActivity.this); progressDialog.setTitle("LOADING"); progressDialog.setMessage("Please Wait"); progressDialog.show(); } @Override protected Void doInBackground(Void... arg0) { HttpServiceClass httpServiceClass = new HttpServiceClass(HttpJSonURL); try { httpServiceClass.ExecutePostRequest(); if (httpServiceClass.getResponseCode() == 200) { FinalJSonResult = httpServiceClass.getResponse(); if (FinalJSonResult != null) { JSONArray jsonArray = null; try { jsonArray = new JSONArray(FinalJSonResult); JSONObject jsonObject; for (int i = 0; i < jsonArray.length(); i++) { jsonObject = jsonArray.getJSONObject(i); String tempSubjectName = jsonObject.getString("SubjectName"); String tempSubjectFullForm = jsonObject.getString("SubjectFullForm"); String SQLiteDataBaseQueryHolder = "INSERT INTO "+SQLiteHelper.TABLE_NAME+" (subjectName,subjectFullForm) VALUES('"+tempSubjectName+"', '"+tempSubjectFullForm+"');"; sqLiteDatabase.execSQL(SQLiteDataBaseQueryHolder); } } catch (JSONException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } else { Toast.makeText(context, httpServiceClass.getErrorMessage(), Toast.LENGTH_SHORT).show(); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } @Override protected void onPostExecute(Void result) { sqLiteDatabase.close(); progressDialog.dismiss(); Toast.makeText(MainActivity.this,"Load Done", Toast.LENGTH_LONG).show(); } } public void SQLiteDataBaseBuild(){ sqLiteDatabase = openOrCreateDatabase(SQLiteHelper.DATABASE_NAME, Context.MODE_PRIVATE, null); } public void SQLiteTableBuild(){ sqLiteDatabase.execSQL("CREATE TABLE IF NOT EXISTS "+SQLiteHelper.TABLE_NAME+"("+SQLiteHelper.Table_Column_ID+" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "+SQLiteHelper.Table_Column_1_Subject_Name+" VARCHAR, "+SQLiteHelper.Table_Column_2_SubjectFullForm+" VARCHAR);"); } public void DeletePreviousData(){ sqLiteDatabase.execSQL("DELETE FROM "+SQLiteHelper.TABLE_NAME+""); } }
Code for ShowDataActivity.java file.
package com.androidjson.jsonstoreinsqlite_androidjsoncom; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.view.View; import android.widget.AdapterView; import android.widget.ListView; import android.widget.Toast; import java.util.ArrayList; public class ShowDataActivity extends AppCompatActivity { SQLiteHelper sqLiteHelper; SQLiteDatabase sqLiteDatabase; Cursor cursor; ListAdapter listAdapter ; ListView LISTVIEW; ArrayList<String> ID_Array; ArrayList<String> Subject_NAME_Array; ArrayList<String> Subject_FullForm_Array; ArrayList<String> ListViewClickItemArray = new ArrayList<String>(); @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_show_data); LISTVIEW = (ListView) findViewById(R.id.listView1); ID_Array = new ArrayList<String>(); Subject_NAME_Array = new ArrayList<String>(); Subject_FullForm_Array = new ArrayList<String>(); sqLiteHelper = new SQLiteHelper(this); LISTVIEW.setOnItemClickListener(new AdapterView.OnItemClickListener() { @Override public void onItemClick(AdapterView<?> parent, View view, int position, long id) { // TODO Auto-generated method stub Toast.makeText(ShowDataActivity.this, ListViewClickItemArray.get(position).toString(), Toast.LENGTH_LONG).show(); } }); } @Override protected void onResume() { ShowSQLiteDBdata() ; super.onResume(); } private void ShowSQLiteDBdata() { sqLiteDatabase = sqLiteHelper.getWritableDatabase(); cursor = sqLiteDatabase.rawQuery("SELECT * FROM "+SQLiteHelper.TABLE_NAME+"", null); ID_Array.clear(); Subject_NAME_Array.clear(); Subject_FullForm_Array.clear(); if (cursor.moveToFirst()) { do { ID_Array.add(cursor.getString(cursor.getColumnIndex(SQLiteHelper.Table_Column_ID))); //Inserting Column Name into Array to Use at ListView Click Listener Method. ListViewClickItemArray.add(cursor.getString(cursor.getColumnIndex(SQLiteHelper.Table_Column_1_Subject_Name))); Subject_NAME_Array.add(cursor.getString(cursor.getColumnIndex(SQLiteHelper.Table_Column_1_Subject_Name))); Subject_FullForm_Array.add(cursor.getString(cursor.getColumnIndex(SQLiteHelper.Table_Column_2_SubjectFullForm))); } while (cursor.moveToNext()); } listAdapter = new ListAdapter(ShowDataActivity.this, ID_Array, Subject_NAME_Array, Subject_FullForm_Array ); LISTVIEW.setAdapter(listAdapter); cursor.close(); } }
Code for HttpServiceClass.java file.
package com.androidjson.jsonstoreinsqlite_androidjsoncom; import org.apache.http.HttpEntity; import org.apache.http.HttpResponse; import org.apache.http.NameValuePair; import org.apache.http.client.ClientProtocolException; import org.apache.http.client.HttpClient; import org.apache.http.client.entity.UrlEncodedFormEntity; import org.apache.http.client.methods.HttpGet; import org.apache.http.client.methods.HttpPost; import org.apache.http.client.methods.HttpUriRequest; import org.apache.http.impl.client.DefaultHttpClient; import org.apache.http.message.BasicNameValuePair; import org.apache.http.params.BasicHttpParams; import org.apache.http.params.HttpConnectionParams; import org.apache.http.params.HttpParams; import org.apache.http.protocol.HTTP; import java.io.BufferedReader; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.net.URLEncoder; import java.util.ArrayList; /** * Created by Juned on 1/30/2017. */ public class HttpServiceClass { private ArrayList<NameValuePair> params; private ArrayList<NameValuePair> headers; private String url; private int responseCode; private String message; private String response; public String getResponse() { return response; } public String getErrorMessage() { return message; } public int getResponseCode() { return responseCode; } public HttpServiceClass(String url) { this.url = url; params = new ArrayList<NameValuePair>(); headers = new ArrayList<NameValuePair>(); } public void AddParam(String name, String value) { params.add(new BasicNameValuePair(name, value)); } public void AddHeader(String name, String value) { headers.add(new BasicNameValuePair(name, value)); } public void ExecuteGetRequest() throws Exception { String combinedParams = ""; if (!params.isEmpty()) { combinedParams += "?"; for (NameValuePair p : params) { String paramString = p.getName() + "=" + URLEncoder.encode(p.getValue(), "UTF-8"); if (combinedParams.length() > 1) { combinedParams += "&" + paramString; } else { combinedParams += paramString; } } } HttpGet request = new HttpGet(url + combinedParams); for (NameValuePair h : headers) { request.addHeader(h.getName(), h.getValue()); } executeRequest(request, url); } public void ExecutePostRequest() throws Exception { HttpPost request = new HttpPost(url); for (NameValuePair h : headers) { request.addHeader(h.getName(), h.getValue()); } if (!params.isEmpty()) { request.setEntity(new UrlEncodedFormEntity(params, HTTP.UTF_8)); } executeRequest(request, url); } private void executeRequest(HttpUriRequest request, String url) { HttpParams httpParameters = new BasicHttpParams(); int timeoutConnection = 10000; HttpConnectionParams.setConnectionTimeout(httpParameters, timeoutConnection); int timeoutSocket = 10000; HttpConnectionParams.setSoTimeout(httpParameters, timeoutSocket); HttpClient client = new DefaultHttpClient(httpParameters); HttpResponse httpResponse; try { httpResponse = client.execute(request); responseCode = httpResponse.getStatusLine().getStatusCode(); message = httpResponse.getStatusLine().getReasonPhrase(); HttpEntity entity = httpResponse.getEntity(); if (entity != null) { InputStream instream = entity.getContent(); response = convertStreamToString(instream); instream.close(); } } catch (ClientProtocolException e) { client.getConnectionManager().shutdown(); e.printStackTrace(); } catch (IOException e) { client.getConnectionManager().shutdown(); e.printStackTrace(); } } private String convertStreamToString(InputStream is) { BufferedReader reader = new BufferedReader(new InputStreamReader(is)); StringBuilder sb = new StringBuilder(); String line = null; try { while ((line = reader.readLine()) != null) { sb.append(line + "\n"); } } catch (IOException e) { e.printStackTrace(); } finally { try { is.close(); } catch (IOException e) { e.printStackTrace(); } } return sb.toString(); } }
Code for ListAdapter.java file.
package com.androidjson.jsonstoreinsqlite_androidjsoncom; /** * Created by Juned on 1/23/2017. */ import android.content.Context; import android.view.LayoutInflater; import android.view.View; import android.view.ViewGroup; import android.widget.BaseAdapter; import android.widget.TextView; import java.util.ArrayList; public class ListAdapter extends BaseAdapter { Context context; ArrayList<String> ID; ArrayList<String> S_Name; ArrayList<String> S_Full_Form; public ListAdapter( Context context2, ArrayList<String> id, ArrayList<String> sub_name, ArrayList<String> Sub_full ) { this.context = context2; this.ID = id; this.S_Name = sub_name; this.S_Full_Form = Sub_full; } public int getCount() { // TODO Auto-generated method stub return ID.size(); } public Object getItem(int position) { // TODO Auto-generated method stub return null; } public long getItemId(int position) { // TODO Auto-generated method stub return 0; } public View getView(int position, View child, ViewGroup parent) { Holder holder; LayoutInflater layoutInflater; if (child == null) { layoutInflater = (LayoutInflater) context.getSystemService(Context.LAYOUT_INFLATER_SERVICE); child = layoutInflater.inflate(R.layout.items, null); holder = new Holder(); holder.Subject_TextView = (TextView) child.findViewById(R.id.textViewSubject); holder.SubjectFullFormTextView = (TextView) child.findViewById(R.id.textViewSubjectFullForm); child.setTag(holder); } else { holder = (Holder) child.getTag(); } holder.Subject_TextView.setText(S_Name.get(position)); holder.SubjectFullFormTextView.setText(S_Full_Form.get(position)); return child; } public class Holder { TextView Subject_TextView; TextView SubjectFullFormTextView; } }
Code for SQLiteHelper.java file.
package com.androidjson.jsonstoreinsqlite_androidjsoncom; /** * Created by Juned on 1/23/2017. */ import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class SQLiteHelper extends SQLiteOpenHelper { static String DATABASE_NAME="SubjectDataBase"; public static final String TABLE_NAME="SubjectTable"; public static final String Table_Column_ID="id"; public static final String Table_Column_1_Subject_Name="subjectName"; public static final String Table_Column_2_SubjectFullForm="subjectFullForm"; public SQLiteHelper(Context context) { super(context, DATABASE_NAME, null, 1); } @Override public void onCreate(SQLiteDatabase database) { String CREATE_TABLE="CREATE TABLE IF NOT EXISTS "+TABLE_NAME+" ("+Table_Column_ID+" INTEGER PRIMARY KEY, "+Table_Column_1_Subject_Name+" VARCHAR, "+Table_Column_2_SubjectFullForm+" VARCHAR)"; database.execSQL(CREATE_TABLE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME); onCreate(db); } }
Code for activity_main.xml layout file.
<?xml version="1.0" encoding="utf-8"?> <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:id="@+id/activity_main" android:layout_width="match_parent" android:layout_height="match_parent" android:paddingBottom="@dimen/activity_vertical_margin" android:paddingLeft="@dimen/activity_horizontal_margin" android:paddingRight="@dimen/activity_horizontal_margin" android:paddingTop="@dimen/activity_vertical_margin" tools:context="com.androidjson.jsonstoreinsqlite_androidjsoncom.MainActivity"> <Button android:text="STORE JSON PARSING DATA INTO SQLITE DB" android:layout_width="fill_parent" android:layout_height="wrap_content" android:id="@+id/button" android:layout_alignParentTop="true" android:layout_alignParentLeft="true" android:layout_alignParentStart="true" android:layout_marginTop="177dp" /> <Button android:text="SHOW LISTVIEW AFTER STORING JSON DATA INTO SQLITE DATABASE" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_below="@+id/button" android:layout_centerHorizontal="true" android:layout_marginTop="13dp" android:id="@+id/button2" /> </RelativeLayout>
Code for activity_show_data.xml file.
<?xml version="1.0" encoding="utf-8"?> <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:id="@+id/activity_show_data" android:layout_width="match_parent" android:layout_height="match_parent" android:paddingBottom="@dimen/activity_vertical_margin" android:paddingLeft="@dimen/activity_horizontal_margin" android:paddingRight="@dimen/activity_horizontal_margin" android:paddingTop="@dimen/activity_vertical_margin" tools:context="com.androidjson.jsonstoreinsqlite_androidjsoncom.ShowDataActivity"> <ListView android:id="@+id/listView1" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_alignParentTop="true" android:layout_centerHorizontal="true" > </ListView> </RelativeLayout>
Code for items.xml layout file.
<?xml version="1.0" encoding="utf-8"?> <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent"> <TextView android:id="@+id/textViewSubject" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Name" android:textAppearance="?android:attr/textAppearanceMedium" android:textColor="#050505" /> <TextView android:id="@+id/textViewSubjectFullForm" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="phone number" android:textAppearance="?android:attr/textAppearanceMedium" android:textColor="#050505" android:layout_below="@+id/textViewSubject" android:layout_alignParentLeft="true" android:layout_alignParentStart="true" /> </RelativeLayout>
Code for AndroidManifest.xml file.
<?xml version="1.0" encoding="utf-8"?> <manifest xmlns:android="http://schemas.android.com/apk/res/android" package="com.androidjson.jsonstoreinsqlite_androidjsoncom"> <uses-permission android:name="android.permission.INTERNET" /> <application android:allowBackup="true" android:icon="@mipmap/ic_launcher" android:label="@string/app_name" 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> <activity android:name=".ShowDataActivity"></activity> </application> </manifest>
Screenshots:
is the list view retrieving data from sqlite after the data is stored?