sqldelight使用

1、使用AndroidStudio 安装SQLDelight插件，这样写出来的.sq文件会高亮关键字

2、在main目录新建一个sqldelight文件，在里面新建相应的model包目录，然后建立一个mode的.sq文件，系统会自动生成相应文件名的 ***Model.java文件

3、主要来看下生成的Model文件：

以网上的一个GithubUserModel为例：

GithubUser.sq

import org.threeten.bp.ZonedDateTime;

CREATE TABLE GithubUsers (
  id INTEGER PRIMARY KEY,
  login TEXT NOT NULL,
  avatar_url TEXT NOT NULL,
  type TEXT NOT NULL,
  created_at TEXT AS ZonedDateTime NULL
);

delete_all:
DELETE FROM GithubUsers WHERE 1;

get_all:
SELECT * FROM GithubUsers;

相应的GithubUserModel.java文件

package com.github.piasy.gh.model.users;

import android.content.ContentValues;
import android.database.Cursor;
import android.support.annotation.NonNull;
import android.support.annotation.Nullable;
import com.squareup.sqldelight.ColumnAdapter;
import com.squareup.sqldelight.RowMapper;
import java.lang.Long;
import java.lang.Override;
import java.lang.String;
import org.threeten.bp.ZonedDateTime;

public interface GithubUserModel {
  String TABLE_NAME = "GithubUsers";

  String ID = "id";

  String LOGIN = "login";

  String AVATAR_URL = "avatar_url";

  String TYPE = "type";

  String CREATED_AT = "created_at";

  String CREATE_TABLE = ""
      + "CREATE TABLE GithubUsers (\n"
      + "  id INTEGER PRIMARY KEY,\n"
      + "  login TEXT NOT NULL,\n"
      + "  avatar_url TEXT NOT NULL,\n"
      + "  type TEXT NOT NULL,\n"
      + "  created_at TEXT NULL\n"
      + ")";

  String DELETE_ALL = ""
      + "DELETE FROM GithubUsers WHERE 1";

  String GET_ALL = ""
      + "SELECT * FROM GithubUsers";

  @Nullable
  Long id();

  @NonNull
  String login();

  @NonNull
  String avatar_url();

  @NonNull
  String type();

  @Nullable
  ZonedDateTime created_at();

  interface Creator<T extends GithubUserModel> {
    T create(@Nullable Long id, @NonNull String login, @NonNull String avatar_url, @NonNull String type, @Nullable ZonedDateTime created_at);
  }

  final class Mapper<T extends GithubUserModel> implements RowMapper<T> {
    private final Factory<T> githubUserModelFactory;

    public Mapper(Factory<T> githubUserModelFactory) {
      this.githubUserModelFactory = githubUserModelFactory;
    }

    @Override
    public T map(@NonNull Cursor cursor) {
      return githubUserModelFactory.creator.create(
          cursor.isNull(0) ? null : cursor.getLong(0),
          cursor.getString(1),
          cursor.getString(2),
          cursor.getString(3),
          cursor.isNull(4) ? null : githubUserModelFactory.created_atAdapter.map(cursor, 4)
      );
    }
  }

  final class Marshal {
    protected final ContentValues contentValues = new ContentValues();

    private final ColumnAdapter<ZonedDateTime> created_atAdapter;

    Marshal(@Nullable GithubUserModel copy, ColumnAdapter<ZonedDateTime> created_atAdapter) {
      this.created_atAdapter = created_atAdapter;
      if (copy != null) {
        this.id(copy.id());
        this.login(copy.login());
        this.avatar_url(copy.avatar_url());
        this.type(copy.type());
        this.created_at(copy.created_at());
      }
    }

    public ContentValues asContentValues() {
      return contentValues;
    }

    public Marshal id(Long id) {
      contentValues.put(ID, id);
      return this;
    }

    public Marshal login(String login) {
      contentValues.put(LOGIN, login);
      return this;
    }

    public Marshal avatar_url(String avatar_url) {
      contentValues.put(AVATAR_URL, avatar_url);
      return this;
    }

    public Marshal type(String type) {
      contentValues.put(TYPE, type);
      return this;
    }

    public Marshal created_at(ZonedDateTime created_at) {
      created_atAdapter.marshal(contentValues, CREATED_AT, created_at);
      return this;
    }
  }

  final class Factory<T extends GithubUserModel> {
    public final Creator<T> creator;

    public final ColumnAdapter<ZonedDateTime> created_atAdapter;

    public Factory(Creator<T> creator, ColumnAdapter<ZonedDateTime> created_atAdapter) {
      this.creator = creator;
      this.created_atAdapter = created_atAdapter;
    }

    public Marshal marshal() {
      return new Marshal(null, created_atAdapter);
    }

    public Marshal marshal(GithubUserModel copy) {
      return new Marshal(copy, created_atAdapter);
    }

    public Mapper<T> get_allMapper() {
      return new Mapper<T>(this);
    }
  }
}

生成文件主要包括以下几部分：

1、从.sq文件对应来的原始信息：

  如字段，表名、创建表 ，获取表内容等

2、定义一个接口Creator,里面定义了一个方法create用来创建我们的Model类型 （这里用的是泛型T）

3、定义了一个内部类Mapper，主要用来把一个Model对应的Cursor转化为Model(map方法),具体是调用了Factory里面的creator的creato方法，这个creator就是上面的Creator类型

4、接下来的是Marshal类，用来把一个Model对象转化为可以进行插入数据库的ContentValues对象，具体是在其构造方法里面调用各字段的方法put到contentValues里面()

5、最后一个是Factory，里面包含一个实现了Creator对象的creator，如果有列值需要转换的话还要包括相应的ColumnAdapter，它的构造函数传入Creator和ColumnAdapter这些参数，marshal方法放回Mode的Marshal(通过它的asContentValues可以获取对应的ContentValues)