[Java] SQLite

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_NAME = "name";
    public static final String COLUMN_PHONE = "phone";
    public static final String COLUMN_AGE = "age";
    public static final String COLUMN_GENDER = "gender";
    public static final String CREATE_TABLE_USER = "CREATE TABLE IF NOT EXISTS " +
            TABLE_NAME + "(" +
            BaseColumns._ID + " INTEGER PRIMARY KEY NOT NULL, " +
            COLUMN_NAME + " TEXT NOT NULL, " +
            COLUMN_PHONE + " TEXT NOT NULL, " +
            COLUMN_AGE + " TEXT NOT NULL, " +
            COLUMN_GENDER + " TEXT 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 {

    public static final int DATABASE_VERSION = 1;
    DBHelper dbHelper;

    public UserDBManager(Context context, String fileName) {
        dbHelper = new DBHelper(context, fileName, null, DATABASE_VERSION);
    }

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

    public Cursor query(String[] columns,
                        String selection,
                        String[] selectionArgs,
                        String orderBy) {

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

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

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


    class DBHelper extends SQLiteOpenHelper {

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

        @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.provider.BaseColumns;
import android.util.Log;

public class MainActivity extends AppCompatActivity {

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

        UserDBManager userDBManager = new UserDBManager(getApplicationContext(), "MyDB.db");

        // insert
        ContentValues values = new ContentValues();
        values.put(UserDBStructure.COLUMN_NAME, "James Kim");
        values.put(UserDBStructure.COLUMN_PHONE, "010-1234-5678");
        values.put(UserDBStructure.COLUMN_AGE, "30");
        values.put(UserDBStructure.COLUMN_GENDER, "male");
        Long resultInsert = userDBManager.insert(values);
        Log.e("insert : ", resultInsert.toString());


        // query
        String[] columns = {
                BaseColumns._ID,
                UserDBStructure.COLUMN_NAME,
                UserDBStructure.COLUMN_PHONE,
                UserDBStructure.COLUMN_AGE,
                UserDBStructure.COLUMN_GENDER
        };
        String selection = UserDBStructure.COLUMN_AGE + " = ?";
        String[] selectionArgs = {"30"};
        String orderBy = BaseColumns._ID + " ASC";
        Cursor cursor = userDBManager.query(columns, selection, selectionArgs, orderBy);

        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex(BaseColumns._ID));
            String name = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_NAME));
            String phone = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_PHONE));
            String age = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_AGE));
            String gender = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_GENDER));
            Log.e("query : ", id + " " + name + " " + phone + " " + age + " " + gender);
        }


        // delete
        String whereClause = UserDBStructure.COLUMN_NAME + " = ? AND " + BaseColumns._ID + " = ?";
        String[] whereArgs = {"James Kim", "5"};
        int resultDelete = userDBManager.delete(whereClause, whereArgs);
        Log.e("delete : ", Integer.toString(resultDelete));


        // update
        values = new ContentValues();
        values.put(UserDBStructure.COLUMN_NAME, "Jason Park");
        String whereClauseUpdate = BaseColumns._ID + " = ? OR " + BaseColumns._ID + " = ?";
        String[] whereArgsUpdate = {"2", "3"};
        int resultUpdate = userDBManager.update(values, whereClauseUpdate, whereArgsUpdate);
        Log.e("update : ", Integer.toString(resultUpdate));

    }
}

첫 번째 실행 결과

 

두 번째 실행 결과

 

세 번째 실행 결과

 

네 번째 실행 결과

 

다섯 번째 실행 결과

 

여섯 번째 실행 결과

 

 

 

 

 

 

 

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.provider.BaseColumns;
import android.util.Log;

public class MainActivity extends AppCompatActivity {

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

        UserDBManager userDBManager = new UserDBManager(getApplicationContext(), "MyDB.db");

        // insert
        ContentValues values = new ContentValues();
        values.put(UserDBStructure.COLUMN_NAME, "James Kim");
        values.put(UserDBStructure.COLUMN_PHONE, "010-1234-5678");
        values.put(UserDBStructure.COLUMN_AGE, "30");
        values.put(UserDBStructure.COLUMN_GENDER, "male");
        Long resultInsert = userDBManager.insert(values);
        Log.e("insert : ", resultInsert.toString());


        // query
        String[] columns = {
                BaseColumns._ID,
                UserDBStructure.COLUMN_NAME,
                UserDBStructure.COLUMN_PHONE,
                UserDBStructure.COLUMN_AGE,
                UserDBStructure.COLUMN_GENDER
        };
        String selection = UserDBStructure.COLUMN_AGE + " = ?";
        String[] selectionArgs = {"30"};
        String orderBy = BaseColumns._ID + " ASC";
        Cursor cursor = userDBManager.query(columns, selection, selectionArgs, orderBy);

        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex(BaseColumns._ID));
            String name = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_NAME));
            String phone = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_PHONE));
            String age = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_AGE));
            String gender = cursor.getString(cursor.getColumnIndex(UserDBStructure.COLUMN_GENDER));
            Log.e("query : ", id + " " + name + " " + phone + " " + age + " " + gender);
        }


        // delete
        String whereClause = UserDBStructure.COLUMN_NAME + " = ? AND " + BaseColumns._ID + " = ?";
        String[] whereArgs = {"James Kim", "5"};
        int resultDelete = userDBManager.delete(null, null);
        Log.e("delete : ", Integer.toString(resultDelete));


        // update
        values = new ContentValues();
        values.put(UserDBStructure.COLUMN_NAME, "Jason Park");
        String whereClauseUpdate = BaseColumns._ID + " = ? OR " + BaseColumns._ID + " = ?";
        String[] whereArgsUpdate = {"2", "3"};
        int resultUpdate = userDBManager.update(values, whereClauseUpdate, whereArgsUpdate);
        Log.e("update : ", Integer.toString(resultUpdate));

    }
}

int resultDelete = userDBManager.delete(whereClause, whereArgs); 에서

int resultDelete = userDBManager.delete(null, null); 로 수정

 

수정 후 첫 번째 실행 결과

 

수정 후 두 번째 실행 결과