[JAVA] [Note] SQLite related

Introduction

If you want to store data inside your app, you will usually use SQLite3. The advantage I feel about SQLite 3 is that it can be used offline. If it is a normal application, you can talk on the assumption that it is connected to the Web, so There seems to be no need to store the data internally. Then you can use a web application. However, in places like us where the network environment cannot be expanded freely I am very grateful that it can be driven by internal data.

SQLiteOpenHelper It seems that you should use SQLiteOpenHelper to operate SQLite. Considering that it will be used in each activity in the app, I think it would be wise to take it out, so I wrote it like this.

SqlConnectionHelper.java

package sample.pack;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import org.json.JSONArray;
import org.json.JSONObject;

public class SqlConnectionHelper extends SQLiteOpenHelper {
    //constructor
    SqlConnectionHelper(Context context, String DB_Name, int DB_Version) {
        super(context,DB_Name,null,DB_Version);
    }

    //Required for extend
    @Override
    public void onCreate(SQLiteDatabase db) {
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }

    //insert,update,method for delete
    public void writeSql(String sql) throws RuntimeException {
        SQLiteDatabase db = getReadableDatabase();
        try {
            db.beginTransaction();
            db.execSQL(sql);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
            db.close();
        }
    }

    //method for select(Return JSONArray)
    public JSONArray readSql(String sql) throws RuntimeException {
        SQLiteDatabase db = getReadableDatabase();
        JSONArray jsonArr = new JSONArray();
        try {
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                JSONObject jsonObj = new JSONObject();
                for (String collumn : cursor.getColumnNames()) {
                    jsonObj.put(collumn, cursor.getString(cursor.getColumnIndex(collumn)));
                }
                jsonArr.put(jsonObj);
            }
            cursor.close();
        } finally {
            db.close();
            return jsonArr;
        }
    }
}

A specification that generates a dml statement on the activity side, passes it, and receives the result in JSONArray format.

Let's look at each part

    //constructor
    SqlConnectionHelper(Context context, String DB_Name, int DB_Version) {
        super(context,DB_Name,null,DB_Version);
    }

For each DB_Name and DB_Version, it is easy to pull the values set in this article. Context is passed from the calling activity using getApplicationContext ().

    //Required for extend
    @Override
    public void onCreate(SQLiteDatabase db) {
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }

If you do not write this part for the convenience of extending SQLiteOpenHelper, an error will occur. It seems to be called when the DB is created / updated, but I don't know how to use it, so this time through.

    //insert,update,method for delete
    public void writeSql(String sql) throws RuntimeException {
        SQLiteDatabase db = getReadableDatabase();
        try {
            db.beginTransaction();
            db.execSQL(sql);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
            db.close();
        }
    }

    //method for select(Return JSONArray)
    public JSONArray readSql(String sql) throws RuntimeException {
        SQLiteDatabase db = getReadableDatabase();
        JSONArray jsonArr = new JSONArray();
        try {
            Cursor cursor = db.rawQuery(sql, null);  //Inquiring here
            while (cursor.moveToNext()) {
                JSONObject jsonObj = new JSONObject();
                for (String collumn : cursor.getColumnNames()) {
                    jsonObj.put(collumn, cursor.getString(cursor.getColumnIndex(collumn)));
                }
                jsonArr.put(jsonObj);
            }
            cursor.close();
        } finally {
            db.close();
            return jsonArr;
        }
    }

I've put together the things that don't receive the results in a write, and the things that I receive in a read. I thought I'd put it all together, but ... Transaction is used on the Write side. If an error occurs and setTransactionSuccessful () is not called, rollback during endTransaction (). For read, in order to return the result with JSONArray The Cursor containing the query result is rotated around to make Cursor-> JSONObject-> JSONArray.