LoginSignup
12
12

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@cattaka(Takao Sumitomo)inUbie株式会社

AndroidのSQLiteの面倒臭いを簡単にする(2)

Posted at

AndroidのSQLiteの面倒臭いを簡単にする
に引き続いて、CatHandsGendroidの簡単な使い方をご紹介します。

DB用の便利クラスの生成方法

既にモデルクラスがある場合は簡単に@DataModel(genDbFunc=true)アノテーションをモデルクラスに付加し、主キーになる項目に@DataModelAttr(primarykey=true)を追加します。そしてビルドすると[モデルクラス名]+CatHandsというクラスが生成されます。

利用可能なアノテーション

@DataModelアノテーション

データモデルのクラスに使用します。

tableNamingConventions属性

モデルクラス名からテーブル名への変換規則を指定します。

  • LOWER_CAMEL_CASE: クラス名が"TheDataModel"の場合、テーブル名は"theDataModel"となります。
  • UPPER_CAMEL_CASE: クラス名が"TheDataModel"の場合、テーブル名は"TheDataModel"となります。
  • LOWER_COMPOSITE: クラス名が"TheDataModel"の場合、テーブル名は"the_data_model"となります。
  • UPPER_COMPOSITE: クラス名が"TheDataModel"の場合、テーブル名は"THE_DATA_MODEL"となります。

fieldNamingConventions属性

フィールド名からカラム名への変換規則を指定します。
tableNamingConventionsと同じものが指定できます。

find属性

検索に指定するフィールド名と並び順を指定します。

  • ex1 : find=("userId") : find by userId.
  • ex2 : find=("") : find all.
  • ex3 : find={"team:userId-"} : find team and orderBy userId desc.
  • ex4 : find={"", "userId", "team:userId-"} : multiple indications are also allowed.

query属性

データ取得に使用するSQLを直接していします。取得する際のカラム名をAS句などを使用して必ずフィールド名に合わせてください。

  • ex1 : query={"Query1:select val1,val2 from mytable", "Query2:select max(val1) as val1, max(val2) as val2 from mytable"}

unique属性

このテーブル上でどの属性の組み合わせがユニークになるかを指定します。ただしCREATE文には反映されません。ここで指定されたものが、find属性で指定されると、そのfindXXXメソッドの戻り値は単一のものになります。

  • ex1 : unique="userId"
  • ex2 : unique={"userId", "userName,team"}

autoincrement属性

このテーブルの主キーがAUTOINCREMENTとなるか否かを設定します。
ただし、この属性がtrueの場合は主キーが必ず数値型である必要があります。

@DataModelAttrアノテーション

データモデルのクラスのフィールドに使用します。

ignore属性

指定されたフィールドを除外します。

forDb属性

SQLite関連のメソッドの対象とします。

primaryKey

指定されたフィールドを主キーとします。

version

このフィールドが追加されたSQLiteDatabaseのバージョンを指定します。ここで指定された場合、SQL_ALTER_TABLE_X_TO_Yという名前のSQL文が生成され、SQLiteOpenHelper#onUpgrade内で実行すべきALTER文が生成されます。

accessor

指定されたフィールドをどのようにDBなどに格納するかのアクセス方法を指定します。標準で対応していないデータ型を格納する際に使用します。0.4.0以降ではgenAccessor=trueを@DataModelに指定することで、そのデータクラス用のAccessorが生成することもできます。

例

@DataModelと@DataModelAttrが指定された次のようなクラスがあるときの例です。

package net.cattaka.util.cathandsgendroid.test.model;

import java.util.Date;
import java.util.List;

import net.cattaka.util.cathandsgendroid.accessor.EnumOrderAccessor;
import net.cattaka.util.cathandsgendroid.annotation.DataModel;
import net.cattaka.util.cathandsgendroid.annotation.DataModelAttrs;

@DataModel(find = {
    // ここに書いたものが検索条件として使えるようになります。
    // 複数列の組み合わせもOKです。
    // 並び順はコロン(:)以降で指定します。
    "id", "username", "team:role+,id", "team:id-", ":id", "authority:id+"
}, unique = {
    // ここで指定された項目はユニークキー扱いとなり、findで指定されたものと一致する場合、findXXXメソッドの戻り値が単一になります。
    "username"
})
public class UserModel {
    public enum Role {
        PROGRAMMER, DESIGNNER, MANAGER
    }

    public enum Authority {
        USER, ADMIN
    }

    @DataModelAttrs(primaryKey = true)
    private Long id;

    private String username;

    @DataModelAttrs(version = 2)
    private String nickname;

    @DataModelAttrs(version = 2)
    private String team;

    private Role role;

    private Date createdAt;

    private List<String> tags;

    @DataModelAttrs(version = 3, accessor = EnumOrderAccessor.class)
    private Authority authority;

    @DataModelAttrs(ignore = true)
    private Object userData;

    private byte[] blob;

    private Boolean booleanData;

    private Byte byteData;

    private Character charData;

    public UserModel() {
    }

    /** Getters and Setters */
}

次のようなクラスがAPTによって自動生成されます。


package net.cattaka.util.cathandsgendroid.test.model;
import net.cattaka.util.cathandsgendroid.accessor.IAccessor;
import net.cattaka.util.cathandsgendroid.accessor.Accessors;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

/**
 * This class is auto-generated by APT; please do not edit by hand.
 */
public class UserModelCatHands {
    public static final IAccessor<Long> ACCESSOR_ID;
    public static final IAccessor<String> ACCESSOR_USERNAME;
    public static final IAccessor<String> ACCESSOR_NICKNAME;
    public static final IAccessor<String> ACCESSOR_TEAM;
    public static final IAccessor<net.cattaka.util.cathandsgendroid.test.model.UserModel.Role> ACCESSOR_ROLE;
    public static final IAccessor<java.util.Date> ACCESSOR_CREATED_AT;
    public static final IAccessor<java.util.List<String>> ACCESSOR_TAGS;
    public static final IAccessor<net.cattaka.util.cathandsgendroid.test.model.UserModel.Authority> ACCESSOR_AUTHORITY;
    public static final IAccessor<byte[]> ACCESSOR_BLOB;
    public static final IAccessor<Boolean> ACCESSOR_BOOLEAN_DATA;
    public static final IAccessor<Byte> ACCESSOR_BYTE_DATA;
    public static final IAccessor<Character> ACCESSOR_CHAR_DATA;
    static {
        ACCESSOR_ID = Accessors.LongAccessor.createAccessor(Long.class);
        ACCESSOR_USERNAME = Accessors.StringAccessor.createAccessor(String.class);
        ACCESSOR_NICKNAME = Accessors.StringAccessor.createAccessor(String.class);
        ACCESSOR_TEAM = Accessors.StringAccessor.createAccessor(String.class);
        ACCESSOR_ROLE = net.cattaka.util.cathandsgendroid.accessor.EnumNameAccessor
                .createAccessor(net.cattaka.util.cathandsgendroid.test.model.UserModel.Role.class);
        ACCESSOR_CREATED_AT = Accessors.DateAccessor.createAccessor(java.util.Date.class);
        ACCESSOR_TAGS = Accessors.createListAccessor(Accessors.StringAccessor
                .createAccessor(String.class));
        ACCESSOR_AUTHORITY = net.cattaka.util.cathandsgendroid.accessor.EnumOrderAccessor
                .createAccessor(net.cattaka.util.cathandsgendroid.test.model.UserModel.Authority.class);
        ACCESSOR_BLOB = Accessors.BlobAccessor.createAccessor(byte[].class);
        ACCESSOR_BOOLEAN_DATA = Accessors.BooleanAccessor.createAccessor(Boolean.class);
        ACCESSOR_BYTE_DATA = Accessors.ByteAccessor.createAccessor(Byte.class);
        ACCESSOR_CHAR_DATA = Accessors.CharacterAccessor.createAccessor(Character.class);
    }
    public static final String SQL_CREATE_TABLE = "CREATE TABLE userModel(id INTEGER PRIMARY KEY AUTOINCREMENT,username TEXT,nickname TEXT,team TEXT,role TEXT,createdAt INTEGER,tags BLOB,authority INTEGER,blob BLOB,booleanData INTEGER,byteData INTEGER,charData INTEGER)";
    public static final String[] SQL_ALTER_TABLE_1_TO_2 = new String[] {
            "ALTER TABLE userModel ADD COLUMN nickname TEXT",
            "ALTER TABLE userModel ADD COLUMN team TEXT",
    };
    public static final String[] SQL_ALTER_TABLE_2_TO_3 = new String[] {
        "ALTER TABLE userModel ADD COLUMN authority INTEGER",
    };
    public static void upgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) {
            for (String sql : SQL_ALTER_TABLE_1_TO_2) {
                db.execSQL(sql);
            }
        }
        if (oldVersion < 3) {
            for (String sql : SQL_ALTER_TABLE_2_TO_3) {
                db.execSQL(sql);
            }
        }
    };
    public static final String TABLE_NAME = "userModel";
    public static final String COLUMNS = "id,username,nickname,team,role,createdAt,tags,authority,blob,booleanData,byteData,charData";
    public static final String[] COLUMNS_ARRAY = new String[] {
            "id", "username", "nickname", "team", "role", "createdAt", "tags", "authority", "blob",
            "booleanData", "byteData", "charData"
    };
    public static final int COL_INDEX_ID = 0;
    public static final int COL_INDEX_USERNAME = 1;
    public static final int COL_INDEX_NICKNAME = 2;
    public static final int COL_INDEX_TEAM = 3;
    public static final int COL_INDEX_ROLE = 4;
    public static final int COL_INDEX_CREATED_AT = 5;
    public static final int COL_INDEX_TAGS = 6;
    public static final int COL_INDEX_AUTHORITY = 7;
    public static final int COL_INDEX_BLOB = 8;
    public static final int COL_INDEX_BOOLEAN_DATA = 9;
    public static final int COL_INDEX_BYTE_DATA = 10;
    public static final int COL_INDEX_CHAR_DATA = 11;
    public static final String COL_NAME_ID = "id";
    public static final String COL_NAME_USERNAME = "username";
    public static final String COL_NAME_NICKNAME = "nickname";
    public static final String COL_NAME_TEAM = "team";
    public static final String COL_NAME_ROLE = "role";
    public static final String COL_NAME_CREATED_AT = "createdAt";
    public static final String COL_NAME_TAGS = "tags";
    public static final String COL_NAME_AUTHORITY = "authority";
    public static final String COL_NAME_BLOB = "blob";
    public static final String COL_NAME_BOOLEAN_DATA = "booleanData";
    public static final String COL_NAME_BYTE_DATA = "byteData";
    public static final String COL_NAME_CHAR_DATA = "charData";

    public static void readCursorByIndex(Cursor cursor, UserModel dest) {

        dest.setId(ACCESSOR_ID.readFromCursor(cursor, 0));
        dest.setUsername(ACCESSOR_USERNAME.readFromCursor(cursor, 1));
        dest.setNickname(ACCESSOR_NICKNAME.readFromCursor(cursor, 2));
        dest.setTeam(ACCESSOR_TEAM.readFromCursor(cursor, 3));
        dest.setRole(ACCESSOR_ROLE.readFromCursor(cursor, 4));
        dest.setCreatedAt(ACCESSOR_CREATED_AT.readFromCursor(cursor, 5));
        dest.setTags(ACCESSOR_TAGS.readFromCursor(cursor, 6));
        dest.setAuthority(ACCESSOR_AUTHORITY.readFromCursor(cursor, 7));
        dest.setBlob(ACCESSOR_BLOB.readFromCursor(cursor, 8));
        dest.setBooleanData(ACCESSOR_BOOLEAN_DATA.readFromCursor(cursor, 9));
        dest.setByteData(ACCESSOR_BYTE_DATA.readFromCursor(cursor, 10));
        dest.setCharData(ACCESSOR_CHAR_DATA.readFromCursor(cursor, 11));
    }

    public static UserModel readCursorByIndex(Cursor cursor) {
        UserModel result = new UserModel();
        readCursorByIndex(cursor, result);
        return result;
    }

    public static void readCursorByName(Cursor cursor, UserModel dest) {
        int idx;

        idx = cursor.getColumnIndex("id");
        dest.setId(ACCESSOR_ID.readFromCursor(cursor, idx));
        idx = cursor.getColumnIndex("username");
        dest.setUsername(ACCESSOR_USERNAME.readFromCursor(cursor, idx));
        idx = cursor.getColumnIndex("nickname");
        dest.setNickname(ACCESSOR_NICKNAME.readFromCursor(cursor, idx));
        idx = cursor.getColumnIndex("team");
        dest.setTeam(ACCESSOR_TEAM.readFromCursor(cursor, idx));
        idx = cursor.getColumnIndex("role");
        dest.setRole(ACCESSOR_ROLE.readFromCursor(cursor, idx));
        idx = cursor.getColumnIndex("createdAt");
        dest.setCreatedAt(ACCESSOR_CREATED_AT.readFromCursor(cursor, idx));
        idx = cursor.getColumnIndex("tags");
        dest.setTags(ACCESSOR_TAGS.readFromCursor(cursor, idx));
        idx = cursor.getColumnIndex("authority");
        dest.setAuthority(ACCESSOR_AUTHORITY.readFromCursor(cursor, idx));
        idx = cursor.getColumnIndex("blob");
        dest.setBlob(ACCESSOR_BLOB.readFromCursor(cursor, idx));
        idx = cursor.getColumnIndex("booleanData");
        dest.setBooleanData(ACCESSOR_BOOLEAN_DATA.readFromCursor(cursor, idx));
        idx = cursor.getColumnIndex("byteData");
        dest.setByteData(ACCESSOR_BYTE_DATA.readFromCursor(cursor, idx));
        idx = cursor.getColumnIndex("charData");
        dest.setCharData(ACCESSOR_CHAR_DATA.readFromCursor(cursor, idx));
    }

    public static UserModel readCursorByName(Cursor cursor) {
        UserModel result = new UserModel();
        readCursorByName(cursor, result);
        return result;
    }

    public static void putToContentValues(ContentValues dest, UserModel model,
            boolean withPrimaryKey) {
        if (withPrimaryKey) {
            ACCESSOR_ID.putToContentValues(dest, "id", model.getId());
        }

        ACCESSOR_USERNAME.putToContentValues(dest, "username", model.getUsername());
        ACCESSOR_NICKNAME.putToContentValues(dest, "nickname", model.getNickname());
        ACCESSOR_TEAM.putToContentValues(dest, "team", model.getTeam());
        ACCESSOR_ROLE.putToContentValues(dest, "role", model.getRole());
        ACCESSOR_CREATED_AT.putToContentValues(dest, "createdAt", model.getCreatedAt());
        ACCESSOR_TAGS.putToContentValues(dest, "tags", model.getTags());
        ACCESSOR_AUTHORITY.putToContentValues(dest, "authority", model.getAuthority());
        ACCESSOR_BLOB.putToContentValues(dest, "blob", model.getBlob());
        ACCESSOR_BOOLEAN_DATA.putToContentValues(dest, "booleanData", model.getBooleanData());
        ACCESSOR_BYTE_DATA.putToContentValues(dest, "byteData", model.getByteData());
        ACCESSOR_CHAR_DATA.putToContentValues(dest, "charData", model.getCharData());
    }

    public static long insert(SQLiteDatabase db, UserModel model) {
        ContentValues values = new ContentValues();
        putToContentValues(values, model, false);
        long key = db.insert(TABLE_NAME, null, values);
        model.setId((long)key);
        return key;
    }

    public static int update(SQLiteDatabase db, UserModel model) {
        ContentValues values = new ContentValues();
        String whereClause = "id=?";
        String[] whereArgs = new String[] {
            ACCESSOR_ID.stringValue(model.getId())
        };
        putToContentValues(values, model, false);
        return db.update(TABLE_NAME, values, whereClause, whereArgs);
    }

    public static int delete(SQLiteDatabase db, Long key) {
        String whereClause = "id=?";
        String[] whereArgs = new String[] {
            ACCESSOR_ID.stringValue(key)
        };
        return db.delete(TABLE_NAME, whereClause, whereArgs);
    }

    public static UserModel findById(SQLiteDatabase db, Long id) {
        Cursor cursor = findCursorById(db, id);
        UserModel model = (cursor.moveToNext()) ? readCursorByIndex(cursor) : null;
        cursor.close();
        return model;
    }

    public static Cursor findCursorById(SQLiteDatabase db, Long id) {
        String selection = "id=?";
        String[] selectionArgs = new String[] {

            ACCESSOR_ID.stringValue(id),
        };
        return db.query(TABLE_NAME, COLUMNS_ARRAY, selection, selectionArgs, null, null, null);
    }

    public static UserModel findByUsername(SQLiteDatabase db, String username) {
        Cursor cursor = findCursorByUsername(db, username);
        UserModel model = (cursor.moveToNext()) ? readCursorByIndex(cursor) : null;
        cursor.close();
        return model;
    }

    public static Cursor findCursorByUsername(SQLiteDatabase db, String username) {
        String selection = "username=?";
        String[] selectionArgs = new String[] {

            ACCESSOR_USERNAME.stringValue(username),
        };
        return db.query(TABLE_NAME, COLUMNS_ARRAY, selection, selectionArgs, null, null, null);
    }

    public static java.util.List<UserModel> findByTeamOrderByRoleAscAndIdAsc(SQLiteDatabase db,
            int limit, String team) {
        Cursor cursor = findCursorByTeamOrderByRoleAscAndIdAsc(db, limit, team);
        java.util.List<UserModel> result = new java.util.ArrayList<UserModel>();
        while (cursor.moveToNext()) {
            result.add(readCursorByIndex(cursor));
        }
        cursor.close();
        return result;
    }

    public static Cursor findCursorByTeamOrderByRoleAscAndIdAsc(SQLiteDatabase db, int limit,
            String team) {
        String selection = "team=?";
        String[] selectionArgs = new String[] {

            ACCESSOR_TEAM.stringValue(team),
        };
        String limitStr = (limit > 0) ? String.valueOf(limit) : null;

        String orderBy = "role asc,id asc";
        return db.query(TABLE_NAME, COLUMNS_ARRAY, selection, selectionArgs, null, null, orderBy,
                limitStr);

    }

    public static java.util.List<UserModel> findByTeamOrderByIdDesc(SQLiteDatabase db, int limit,
            String team) {
        Cursor cursor = findCursorByTeamOrderByIdDesc(db, limit, team);
        java.util.List<UserModel> result = new java.util.ArrayList<UserModel>();
        while (cursor.moveToNext()) {
            result.add(readCursorByIndex(cursor));
        }
        cursor.close();
        return result;
    }

    public static Cursor findCursorByTeamOrderByIdDesc(SQLiteDatabase db, int limit, String team) {
        String selection = "team=?";
        String[] selectionArgs = new String[] {

            ACCESSOR_TEAM.stringValue(team),
        };
        String limitStr = (limit > 0) ? String.valueOf(limit) : null;

        String orderBy = "id desc";
        return db.query(TABLE_NAME, COLUMNS_ARRAY, selection, selectionArgs, null, null, orderBy,
                limitStr);

    }

    public static java.util.List<UserModel> findOrderByIdAsc(SQLiteDatabase db, int limit) {
        Cursor cursor = findCursorOrderByIdAsc(db, limit);
        java.util.List<UserModel> result = new java.util.ArrayList<UserModel>();
        while (cursor.moveToNext()) {
            result.add(readCursorByIndex(cursor));
        }
        cursor.close();
        return result;
    }

    public static Cursor findCursorOrderByIdAsc(SQLiteDatabase db, int limit) {
        String selection = "";
        String[] selectionArgs = new String[] {

        };
        String limitStr = (limit > 0) ? String.valueOf(limit) : null;

        String orderBy = "id asc";
        return db.query(TABLE_NAME, COLUMNS_ARRAY, selection, selectionArgs, null, null, orderBy,
                limitStr);

    }

    public static java.util.List<UserModel> findByAuthorityOrderByIdAsc(SQLiteDatabase db,
            int limit, net.cattaka.util.cathandsgendroid.test.model.UserModel.Authority authority) {
        Cursor cursor = findCursorByAuthorityOrderByIdAsc(db, limit, authority);
        java.util.List<UserModel> result = new java.util.ArrayList<UserModel>();
        while (cursor.moveToNext()) {
            result.add(readCursorByIndex(cursor));
        }
        cursor.close();
        return result;
    }

    public static Cursor findCursorByAuthorityOrderByIdAsc(SQLiteDatabase db, int limit,
            net.cattaka.util.cathandsgendroid.test.model.UserModel.Authority authority) {
        String selection = "authority=?";
        String[] selectionArgs = new String[] {

            ACCESSOR_AUTHORITY.stringValue(authority),
        };
        String limitStr = (limit > 0) ? String.valueOf(limit) : null;

        String orderBy = "id asc";
        return db.query(TABLE_NAME, COLUMNS_ARRAY, selection, selectionArgs, null, null, orderBy,
                limitStr);

    }
}

このクラスには次のような機能や属性があります。

  • SQL_CREATE_TABLE：CREATE文です。
  • upgradeメソッド：SQLiteOpenHelper#onUpgradeの引数を与えると、ALTER文でテーブル定義を更新します。
  • insert/update/delete用のメソッド
  • findXXXメソッド：DBからのフェッチ用のメソッドです。検索項目が主キーやUnique項目と一致する場合、単一の戻り値に、それ以外の場合はListで戻り値を返すようになります。
  • その他、テーブル名や列名の定数群
12
12
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
12
12

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?