How to Store Json Data into Sqlite?

Question

Hi I am creating one app in which i want to send data from mobile to server.but problem is data is reflected on website only when server is online/Up.when server is offline i got response 400 or 502 and so data is displayed on only mobile screen it is not reflected on server.so i want to create Sqlite database so when i take reading data is stored in database and want to upload data which is the server is become online.this jon i want to insert in sqlite.i am new in android so i dont have idea how to insert this json into sqlite.please anyone help me.thanks in advanced

            JSONObject json = new JSONObject();
            json.put("siteId", "");
            if (wtConnected == true) {
                json.put("patientId", 15);
                json.put("deviceMACId", DeviceMac);
                json.put("readingType", 1003);
                json.put("deviceData", weight);
                json.put("deviceType", "WeightScale");

            } else if (bpConnected == true) {
                json.put("patientId", 5);

                json.put("deviceMACId", DeviceMac);
                json.put("readingType", 1002);
                json.put("deviceData", bpdata);
                json.put("deviceType", "BPMonitor");

            } 
            json.put("assetId", "");
            json.put("geoLocationLatitude", "");
            json.put("clientId", "");
            json.put("timeStamp", System.currentTimeMillis());
            json.put("deviceRawData", "");
            json.put("geoLocationLongitude", "");
            System.out.println("Json Is:" + json);

score 2 · Answer 1 · answered Jul 28 '16 at 13:34

My friend just do following things :

Define this class which will perform all the operation related to your database creation and manipulation and make changes in this class according to your need.

DatabaseHandler.java



 public class DatabaseHandler extends SQLiteOpenHelper {
        private static final int DATABASE_VERSION = 1;
        private static final String DATABASE_NAME = "Demo";
        private static final String TABLE_Demo = "demo";
        private static final String KEY_ID = "id";
        private static final String KEY_PATIENT_ID= "patientId";
        private static final String KEY_ReadingType= "readingType";
        private static final String KEY_DeviceMACId= "deviceMACId"; 
        private static final String KEY_DeviceData = "deviceData ";

  private static final String KEY_DeviceType= "deviceType";  
        String CREATE_DEMO_TABLE = "CREATE TABLE " + TABLE_Demo +   "("+   
        KEY_ID + " INTEGER PRIMARY KEY,"+ KEY_ReadingType+ " INTEGER ,"  
        +  KEY_DeviceMACId +  "TEXT,"+ KEY_DeviceData + " TEXT,"     
        + KEY_DeviceType +  " TEXT" + ")"; 

            public DatabaseHandler(Context context) {
                super(context, DATABASE_NAME, null, 

            }

            public void onCreate(SQLiteDatabase db) {
                db.execSQL(CREATE_DEMO_TABLE);
            }
            public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
                db.execSQL("DROP TABLE IF EXISTS " + TABLE_Demo );
                onCreate(db);
            }

            void addJson(JsonObject json) {
                SQLiteDatabase db = this.getWritableDatabase();
                ContentValues values = new ContentValues();
                values.put(KEY_PATIENT_ID,   json.getString("patientId")); 
            values.put(KEY_ReadingType,json.getString("readingType"));       
                values.put(KEY_DeviceMACId, json.getString("deviceMACId")); 
                values.put(KEY_DeviceData , json.getString("deviceData")); 
                values.put(KEY_DeviceType, json.getString("deviceType")); 
                db.insert(TABLE_Demo , null, values);
                db.close();
            }

        }

Now make a object of this class and store your json object into database like this:
```
DatabaseHandler db = new DatabaseHandler (context); 
     // context of your activity or fragment
    try
    {
    db.addJson(JsonObject json);
```
// here json is your json object that you need to save in database. } catch(Exception e) { }

Hi Dhiraj thanks for reply...i create class according to ur suggestion can plz check is it right or wrong.. — user6649633, Jul 29 '16 at 05:30

score 0 · Answer 2 · answered Jul 28 '16 at 12:37

0

You can create model object for this json. Then you can save you model object to database. Try to find some examples about SQLite database and use it in your project.

answered Jul 28 '16 at 12:37

Lalit Jadav

1,409
1
15
38

score 0 · Answer 3 · answered Jul 28 '16 at 12:38

First you try to post some json data to server.So you get response from your server is up/not.So check with your response code.If response code is not equal to 200, you insert the generated json to Sqlite database.And you frequently check connection with your server is up/not.If server is online means get your json data from SqliteDB and post it to your server

Sample for Sqlite Database http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ Sample to connect server http://www.androidhive.info/2014/05/android-working-with-volley-library-1/ Use volley library for server connection you can get error response for that

score 0 · Answer 4 · edited May 23 '17 at 11:54

Create a database with all the fields in your sq-lite database.
Place a field named isUpdated extra in your data-table.
Run a separate thread for checking network and updating data into remote-database.
Thread functions [Check internet availability,if internet available update data into remote database,update isUpdated status to true in your sq-lite database on your update success
Main thread will update local sq-lite database regularly even in the absence of internet with isUpdated status is false

this may be helpful to you

Check network availability using this

Updating remote database require a web service ,

 void PutRequest(String URL)
 {
    Ion.with(mContext)
            .load("PUT",URL)
            .setBodyParameter("name","foo")
            .asString();

 }

for more information about Ion

clifford:Hi Clifford thanks for reply..if u dont mind can u plz share some code for checking network and updating remote database? — user6649633, Jul 29 '16 at 05:29

score 0 · Answer 5 · answered Jul 29 '16 at 05:31

public class DataBase {
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "demo";
    private static final String TABLE_NAME = "device_data";
    private static final String KEY_ID = "id";
    private static final String KEY_PATIENT_ID = "patientId";
    private static final String KEY_ReadingType = "readingType";
    private static final String KEY_DeviceMACId = "deviceMACId";
    private static final String KEY_DeviceData = "deviceData ";
    private static final String KEY_DeviceType = "deviceType";
    private static final String KEY_TimeStamp = "timeStamp";
    private static final String KEY_AssetId = "assetId";
    private static final String KEY_GeoLocationLattitude = "geoLocationLatitude";
    private static final String KEY_GeoLocationLongitude = "geoLocationLongitude";
    private static final String KEY_ClientId = "clientId";
    private static final String KEY_DeviceRawData = "deviceRawData";

    private final Context context;
    private DataBaseHelper dbHelper;
    public SQLiteDatabase db;

    static final String DATABASE_CREATE = "create table " + TABLE_NAME + "("
            + KEY_ID + " INTEGER PRIMARY KEY," + KEY_ReadingType + " INTEGER ,"
            + KEY_DeviceMACId + "TEXT," + KEY_DeviceData + " TEXT,"
            + KEY_DeviceType + " TEXT," + KEY_TimeStamp + "TEXT," + KEY_AssetId
            + "INTEGER," + KEY_GeoLocationLattitude + "TEXT,"
            + KEY_GeoLocationLongitude + "TEXT," + KEY_ClientId + "INTEGER,"
            + KEY_DeviceRawData + "TEXT" + ")";

    public DataBase(Context _context) {
        context = _context;
        dbHelper = new DataBaseHelper(context, DATABASE_NAME, null,
                DATABASE_VERSION);
    }

    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE);
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    void addJson(JSONObject json) throws JSONException {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        try {
            values.put(KEY_PATIENT_ID, json.getString("patientId"));
            values.put(KEY_ReadingType, json.getString("readingType"));
            values.put(KEY_DeviceMACId, json.getString("deviceMACId"));
            values.put(KEY_DeviceData, json.getString("deviceData"));
            values.put(KEY_DeviceType, json.getString("deviceType"));
            values.put(KEY_TimeStamp, json.getString("deviceType"));
            values.put(KEY_AssetId, json.getString("deviceType"));
            values.put(KEY_GeoLocationLattitude, json.getString("deviceType"));
            values.put(KEY_GeoLocationLongitude, json.getString("deviceType"));
            values.put(KEY_ClientId, json.getString("deviceType"));
            values.put(KEY_DeviceRawData, json.getString("deviceType"));

        } catch (JSONException e) {
            // TODO Auto-generated catch block
            System.out.println("JsonException........");
            e.printStackTrace();
        }

        db.insert(TABLE_NAME, null, values);
        db.close();
    }

    public DataBase open() throws SQLException {
        db = dbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        db.close();
    }

    public SQLiteDatabase getDatabaseInstance() {
        return db;
    }

yes it is right just make an obejct of this class as mentioned in my answer and insert data into your database. — Dhiraj Choudhary, Jul 29 '16 at 10:28
**DATABASE_CREATE** script of your table has not **patientId** field. — faranjit, Aug 01 '16 at 07:26
yes..that was my mistake bt still having below error near "null": syntax error 08-01 12:05:45.458: E/SQLiteDatabase(4109): Error inserting 08-01 12:05:45.458: E/SQLiteDatabase(4109): android.database.sqlite.SQLiteException: near "null": syntax error (code 1): , while compiling: INSERT INTO device_data(null) VALUES (NULL) — user6649633, Aug 01 '16 at 08:53

score 0 · Answer 6 · answered Aug 01 '16 at 06:53

@Dhiraj -i got exception..
08-01 12:05:45.447: I/System.out(4109): In try....com.example.healthdeviceprofilee.DataBase@42187568
08-01 12:05:45.447: I/System.out(4109): JsonException........
08-01 12:05:45.447: W/System.err(4109): org.json.JSONException: No value for patientId
08-01 12:05:45.447: W/System.err(4109):     at org.json.JSONObject.get(JSONObject.java:355)
08-01 12:05:45.447: W/System.err(4109):     at org.json.JSONObject.getString(JSONObject.java:515)
08-01 12:05:45.447: W/System.err(4109):     at com.example.healthdeviceprofilee.DataBase.addJson(DataBase.java:59)
08-01 12:05:45.447: W/System.err(4109):     at com.example.healthdeviceprofilee.DashboardActivity$RestConnection.onPostExecute(DashboardActivity.java:895)
08-01 12:05:45.447: W/System.err(4109):     at com.example.healthdeviceprofilee.DashboardActivity$RestConnection.onPostExecute(DashboardActivity.java:1)
08-01 12:05:45.447: W/System.err(4109):     at android.os.AsyncTask.finish(AsyncTask.java:632)
08-01 12:05:45.447: W/System.err(4109):     at android.os.AsyncTask.access$600(AsyncTask.java:177)
08-01 12:05:45.447: W/System.err(4109):     at android.os.AsyncTask$InternalHandler.handleMessage(AsyncTask.java:645)
08-01 12:05:45.447: W/System.err(4109):     at android.os.Handler.dispatchMessage(Handler.java:102)
08-01 12:05:45.447: W/System.err(4109):     at android.os.Looper.loop(Looper.java:136)
08-01 12:05:45.447: W/System.err(4109):     at android.app.ActivityThread.main(ActivityThread.java:5017)
08-01 12:05:45.458: W/System.err(4109):     at java.lang.reflect.Method.invokeNative(Native Method)
08-01 12:05:45.458: W/System.err(4109):     at java.lang.reflect.Method.invoke(Method.java:515)
08-01 12:05:45.458: W/System.err(4109):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
08-01 12:05:45.458: W/System.err(4109):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
08-01 12:05:45.458: W/System.err(4109):     at dalvik.system.NativeStart.main(Native Method)
08-01 12:05:45.458: E/SQLiteLog(4109): (1) near "null": syntax error
08-01 12:05:45.458: E/SQLiteDatabase(4109): Error inserting 
08-01 12:05:45.458: E/SQLiteDatabase(4109): android.database.sqlite.SQLiteException: near "null": syntax error (code 1): , while compiling: INSERT INTO device_data(null) VALUES (NULL)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1467)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at com.example.healthdeviceprofilee.DataBase.addJson(DataBase.java:77)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at com.example.healthdeviceprofilee.DashboardActivity$RestConnection.onPostExecute(DashboardActivity.java:895)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at com.example.healthdeviceprofilee.DashboardActivity$RestConnection.onPostExecute(DashboardActivity.java:1)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at android.os.AsyncTask.finish(AsyncTask.java:632)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at android.os.AsyncTask.access$600(AsyncTask.java:177)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at android.os.AsyncTask$InternalHandler.handleMessage(AsyncTask.java:645)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at android.os.Handler.dispatchMessage(Handler.java:102)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at android.os.Looper.loop(Looper.java:136)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at android.app.ActivityThread.main(ActivityThread.java:5017)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at java.lang.reflect.Method.invokeNative(Native Method)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at java.lang.reflect.Method.invoke(Method.java:515)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:779)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:595)
08-01 12:05:45.458: E/SQLiteDatabase(4109):     at dalvik.system.NativeStart.main(Native Method)

How to Store Json Data into Sqlite?

6 Answers6