[Java] SQLite + Singleton Pattern

UserDBStructure.java

package com.jwsoft.javaproject;

import android.provider.BaseColumns;

public class UserDBStructure implements BaseColumns {

    public static final String TABLE_NAME = "user";
    public static final String COLUMN_ID = BaseColumns._ID;
    public static final String COLUMN_NAME = "name";
    public static final String COLUMN_PHONE = "phone";
    public static final String COLUMN_AGE = "age";

    public static final String CREATE_TABLE_USER = "CREATE TABLE IF NOT EXISTS " +
            TABLE_NAME + "(" +
            COLUMN_ID + " INTEGER PRIMARY KEY NOT NULL, " +
            COLUMN_NAME + " TEXT NOT NULL, " +
            COLUMN_PHONE + " TEXT NOT NULL, " +
            COLUMN_AGE + " INTEGER NOT NULL)";

    public static final String DROP_TABLE_USER = "DROP TABLE IF EXISTS " + TABLE_NAME;

}

 

 

 

 

 

 

 

UserDBManager.java

package com.jwsoft.javaproject;

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

import androidx.annotation.Nullable;

public class UserDBManager extends SQLiteOpenHelper {

    public static final int DATABASE_VERSION = 1;
    public static UserDBManager userDBManager;

    public UserDBManager(@Nullable Context context,
                         @Nullable String name,
                         @Nullable SQLiteDatabase.CursorFactory factory,
                         int version) {
        super(context, name, factory, version);
    }

    public static UserDBManager getInstance(Context context, String fileName) {
        if (userDBManager == null) {
            userDBManager = new UserDBManager(context, fileName, null, DATABASE_VERSION);
        }
        return userDBManager;
    }

    public Long insert(ContentValues values) {
        SQLiteDatabase db = getWritableDatabase();
        return db.insert(UserDBStructure.TABLE_NAME, null, values);
    }

    public Cursor query(String[] columns,
                        String selection,
                        String[] selectionArgs,
                        String orderBy) {
        SQLiteDatabase db = getReadableDatabase();

        return db.query(UserDBStructure.TABLE_NAME,
                columns,
                selection,
                selectionArgs,
                null,
                null,
                orderBy
        );
    }

    public int delete(String whereClause, String[] whereArgs) {
        SQLiteDatabase db = getReadableDatabase();
        return db.delete(UserDBStructure.TABLE_NAME, whereClause, whereArgs);
    }

    public int update(ContentValues values, String whereClause, String[] whereArgs) {
        SQLiteDatabase db = getReadableDatabase();
        return db.update(UserDBStructure.TABLE_NAME, values, whereClause, whereArgs);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(UserDBStructure.CREATE_TABLE_USER);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(UserDBStructure.DROP_TABLE_USER);
        onCreate(db);
    }

    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        onUpgrade(db, oldVersion, newVersion);
    }

}

 

 

 

 

 

 

 

MainActivity.java

package com.jwsoft.javaproject;

import androidx.appcompat.app.AppCompatActivity;

import android.content.ContentValues;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;

public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        // insert
        ContentValues values = new ContentValues();
        values.put(UserDBStructure.COLUMN_NAME, "James Kim");
        values.put(UserDBStructure.COLUMN_PHONE, "010-1234-1234");
        values.put(UserDBStructure.COLUMN_AGE, 30);
        Long insertedID = UserDBManager.getInstance(getApplicationContext(), "James.db")
                .insert(values);

        Log.e("add id : ", Long.toString(insertedID));


        // query
        String[] colums = {
                UserDBStructure.COLUMN_ID,
                UserDBStructure.COLUMN_NAME,
                UserDBStructure.COLUMN_PHONE,
                UserDBStructure.COLUMN_AGE
        };
        String selection = UserDBStructure.COLUMN_AGE + " = ? OR " +
                UserDBStructure.COLUMN_NAME + " = ?";
        String[] selectionArgs = {"30", "James Kim"};
        String orderBy = UserDBStructure.COLUMN_ID + " ASC";
        Cursor cursor = UserDBManager.getInstance(getApplicationContext(), "James.db")
                .query(colums, selection, selectionArgs, orderBy);

        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex(UserDBStructure.COLUMN_ID));
            String name = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_NAME));
            String phone = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_PHONE));
            int age = cursor.getInt(cursor.getColumnIndex(UserDBStructure.COLUMN_AGE));

            String strQuery = id + " " + name + " " + phone + " " + age;
            Log.e("query : ", strQuery);
        }


        // delete
        String where = UserDBStructure.COLUMN_ID + " = ?";
        String[] whereArgs = {"5"};
        int deletedRows = UserDBManager.getInstance(getApplicationContext(), "James.db")
                .delete(where, whereArgs);

        Log.e("delete : ", Integer.toString(deletedRows));


        // update
        ContentValues updateValues = new ContentValues();
        updateValues.put(UserDBStructure.COLUMN_NAME, "Sonata Lee");
        String updateWhere = UserDBStructure.COLUMN_ID + " = ? OR " +
                UserDBStructure.COLUMN_ID + " = ?";
        String[] updateWhereArgs = {"3", "4"};
        int updatedRows = UserDBManager.getInstance(getApplicationContext(), "James.db")
                .update(updateValues, updateWhere, updateWhereArgs);

        Log.e("update : ", Integer.toString(updatedRows));
    }


    @Override
    protected void onDestroy() {
        UserDBManager.getInstance(getApplicationContext(), "James.db").close();
        super.onDestroy();
    }
}

 

첫 번째 실행 결과

 

두 번째 실행 결과

 

세 번째 실행 결과

 

네 번째 실행 결과

 

다섯 번 째 실행 결과

 

여섯 번 째 실행 결과