[JAVA] Since I use it often, I make a note of DB related things on Android.

Introduction

This is a memo, so don't be afraid.

1. Inheritance, constants and DB file generation

There are four columns (horizontal axis): ID (automatically generated)` ``, `` `category, `chapter```, password```. The file name is ``` ProjectList.db``` and the table name is `` PROJECT_INFO```

SampleDatabaseHelper

public class SampleDatabaseHelper extends SQLiteOpenHelper {
    // Reference
    private static final String TAG = "SampleDatabaseHelper";
    //Table name
    private static final String TABLE_PROJECT = "PROJECT_INFO";
    //column
    public static final String ID = "_id";
    public static final String CATEGORY = "category";
    public static final String CHAPTER = "chapter";
    public static final String PASSWORD = "password";

    //All columns
    private final String[] PROJECT_COLUMNS = { CATEGORY, CHAPTER, PASSWORD };
    
    //SQlite production text
    private static final String SQL_CREATE_TABLE_PROJECT = "CREATE TABLE "
            + "`" + TABLE_PROJECT + "` ("
              + "`" + ID + "`	INTEGER PRIMARY KEY AUTOINCREMENT,"
              + "`" + CATEGORY + "`    TEXT NOT NULL,"
              + "`" + CHAPTER + "`    TEXT NOT NULL,"
              + "`" + PASSWORD + "`,"
              + "UNIQUE("
                + "`" + CATEGORY + "`,"
                + "`" + CHAPTER + "`,"
                + "`" + PASSWORD + "`"
              + ")"
            + ");";

    //File name and version number definition
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_FILE_NAME = "ProjectList.db";

    //constructor
    public SampleDatabaseHelper (Context context) {
        super(context, DATABASE_FILE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate (SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(SQL_CREATE_TABLE_PROJECT);
    }

    @Override
    public void onUpgrade (SQLiteDatabase sqLiteDatabase, int i, int i1) { }
}

[Reference] SQLite table creation command

CREATE TABLE `PROJECT_INFO`(
`_id` INTEGER PRIMARY KEY AUTOINCREMENT,
`category` TEXT NOT NULL,
`chapter` TEXT NOT NULL,
`password`,
UNIQUE(`category`, `chapter`, `password`));

The table name after CREATE TABLE. In (), separate , in the order of `column type rule` `. The rules include PRIMARY KEY, which specifies the primary key, ```AUTO INCREMENT, which increments the value by 1 when a row is added, and NOT NULL, which must not be absolutely empty. is there. Also, the value of the column enclosed in `ʻUNIQUE`` cannot be the same combination as other rows.

2. Registration method

SampleDatabaseHelper

    public boolean addProject(final String category, final String chapter, final String password) {
        try{
            SQLiteDatabase db = getReadableDatabase();
            final ContentValues values = new ContentValues(3);
            values.put(CATEGORY,category);
            values.put(CHAPTER,chapter);
            values.put(PASSWORD,password);
            long rowID = db.insert(TABLE_PROJECT,null,values);
            if (rowID >= 0) {
                //Successful registration.
                return true;
            } else {
                //Registration failed. In many cases, the cause is that the condition of UNIQUE is caught.
                return false;
            }
        } catch (Exception e) {
            return false;
        }
    }

3. Delete line

SampleDatabaseHelper

    public int deleteProject(String id){
        String selection = ID + " = ?";
        String[] selectionArgs = { id };
        try {
            SQLiteDatabase db = getWritableDatabase();
            int deletedId = db.delete(TABLE_PROJECT, selection, selectionArgs);
            return deletedId;
        } catch (Exception e) {}
    }

selection is a conditional statement, which is how to write sqlite. In this case, there is only one ? , but even if there are multiple ? , the contents of selectionArgs are applied to ? one by one.

4. Row search

SampleDatabaseHelper

    public List<String[]> getProjectColumns(String category){
        String selection = CATEGORY + " = ?";
        String[] selectionArgs = { category };
        Cursor c = null;
        try {
            SQLiteDatabase db = getReadableDatabase();
            c = db.query(TABLE_PROJECT, PROJECT_COLUMNS, selection, selectionArgs, null, null, null);
            List<String[]> list = new ArrayList<>();
            while (c.moveToNext()) {
                String[] ret = { c.getString(0), c.getString(1), c.getString(2) };
                list.add(ret);
            }
            return list;
        }catch (Exception e) {

        } finally {
            if (c != null) {
                try { c.close(); } catch (Exception ex) {}
            }
        }
        return null;
    }

Note the concept of cursors. Those that hit the search while there are many columns are marked and extracted. In that state, everything is marked, but by doing `c.moveToNext ()`, the cursor moves to only the top one of the ** extracted ones **. (If you do it one more time, ** among the extracted ones ** will go next) You can use this to make sure that the value is inside.

[Example] Search for columns by ID

SampleDatabaseHelper

    public String[] getProjectColumns(String id) {
        String selection = ID + " = ?";
        String[] selectionArgs = { id };
        Cursor c = null;
        try {
            SQLiteDatabase db = getReadableDatabase();
            c = db.query(TABLE_PROJECT,PROJECT_COLUMNS, selection, selectionArgs, null, null, null);
            if (c.moveToNext()) {
                String[] ret = {c.getString(0),c.getString(1),c.getString(2)};
                return ret;
            }
        } catch (Exception e) {
        } finally {
            if (c != null) {
                try { c.close(); } catch (Exception ex) {}
            }
        }
        return null;
    }

5. Line update

    public boolean updateColumns(String id, String newCategory, String newChapter, String newPassword){
        String selection = ID + " = ?";
        String[] selectionArgs = { id };
        try {
            SQLiteDatabase db = getReadableDatabase();
            final ContentValues updateValues = new ContentValues(3);
            updateValues.put(CATEGORY,newCategory);
            updateValues.put(CHAPTER,newChapter);
            updateValues.put(PASSWORD,newPassword);
            db.update(DATABASE_PROJECT_NAME, updateValues, selection, selectionArgs);
            return true;
        }catch (Exception e) {
            return false;
        }
        return false;
    }

Summary

It's just a memo, so if you say spelling, I'll fix it. I wrote the method specifications as a corporate standard. Although it is in Japanese. Twitter: https://twitter.com/Cyber_Hacnosuke (Please follow me.) Github: https://github.com/CyberHacnoshuke