android.database.sqlite.SQLiteDatabase#execSQL ( )源码实例Demo

private void createDB(final SQLiteDatabase db) {
    String sql = "CREATE TABLE " + TBL_SERVICE_NAME + " ("
            + BaseColumns._ID + " INTEGER PRIMARY KEY, "
            + COL_SERVICE_ID + " TEXT NOT NULL, "
            + COL_SERVICE_NAME + " TEXT NOT NULL"
            + ");";
    db.execSQL(sql);

    String sql2 = "CREATE TABLE " + TBL_PROFILE_NAME + " ("
            + BaseColumns._ID + " INTEGER PRIMARY KEY, "
            + COL_PROFILE_SERVICE_ID + " TEXT NOT NULL, "
            + COL_PROFILE_TYPE + " INTEGER, "
            + COL_PROFILE_PIN + " TEXT NOT NULL"
            + ");";
    db.execSQL(sql2);
}
 

@Override
public void onCreate(@NonNull final SQLiteDatabase db) {
    // create the play count table
    // WARNING: If you change the order of these columns
    // please update getColumnIndexForWeek
    StringBuilder builder = new StringBuilder();
    builder.append("CREATE TABLE IF NOT EXISTS ");
    builder.append(SongPlayCountColumns.NAME);
    builder.append("(");
    builder.append(SongPlayCountColumns.ID);
    builder.append(" INT UNIQUE,");

    for (int i = 0; i < NUM_WEEKS; i++) {
        builder.append(getColumnNameForWeek(i));
        builder.append(" INT DEFAULT 0,");
    }

    builder.append(SongPlayCountColumns.LAST_UPDATED_WEEK_INDEX);
    builder.append(" INT NOT NULL,");

    builder.append(SongPlayCountColumns.PLAY_COUNT_SCORE);
    builder.append(" REAL DEFAULT 0);");

    db.execSQL(builder.toString());
}
 

@Override
public void onCreate(SQLiteDatabase db) {
	String CREATE_TABLE="CREATE TABLE "+TABLE_NAME+" ("
	+ID+" INTEGER PRIMARY KEY, "
	+TITLE+" TEXT, "
	+FILE_LOCATION+" TEXT, "
	+THUMBNAIL+" TEXT, "
	+ORIGINAL_URL+" TEXT, "
	+SAVED_PAGE_BASE_DIRECTORY+" TEXT, "
	+TIMESTAMP+" TEXT DEFAULT CURRENT_TIMESTAMP)";
	
	db.execSQL(CREATE_TABLE);

}
 

/** Creates the underlying database table. */
public static void createTable(SQLiteDatabase db, boolean ifNotExists) {
    String constraint = ifNotExists? "IF NOT EXISTS ": "";
    db.execSQL("CREATE TABLE " + constraint + "\"STATISTICS\" (" + //
            "\"_id\" INTEGER PRIMARY KEY ," + // 0: id
            "\"SUCCEEDED\" INTEGER," + // 1: succeeded
            "\"TIME\" INTEGER," + // 2: time
            "\"USER_ID\" INTEGER NOT NULL ," + // 3: userId
            "\"CHALLENGE_ID\" INTEGER NOT NULL );"); // 4: challengeId
}
 

@Override
public void onCreate(SQLiteDatabase db) {
    String sql = "CREATE TABLE " + TABLE_NAME
            + "(" + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + COLUMN_NAME + " VARCHAR, "
            + COLUMN_GROUP + " VARCHAR, "
            + COLUMN_USER + " VARCHAR);";

    db.execSQL(sql);
}
 

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE bookmarks (" +
            "_id INTEGER PRIMARY KEY," +
            "title TEXT," +
            "url TEXT NOT NULL," +
            "visits INTEGER," +
            "date LONG," +
            "created LONG," +
            "description TEXT," +
            "bookmark INTEGER," +
            "favicon BLOB DEFAULT NULL," +
            "thumbnail BLOB DEFAULT NULL," +
            "touch_icon BLOB DEFAULT NULL," +
            "user_entered INTEGER" +
            ");");

    final CharSequence[] bookmarks = mContext.getResources()
            .getTextArray(R.array.bookmarks);
    int size = bookmarks.length;
    try {
        for (int i = 0; i < size; i = i + 2) {
            CharSequence bookmarkDestination = replaceSystemPropertyInString(mContext, bookmarks[i + 1]);
            db.execSQL("INSERT INTO bookmarks (title, url, visits, " +
                    "date, created, bookmark)" + " VALUES('" +
                    bookmarks[i] + "', '" + bookmarkDestination +
                    "', 0, 0, 0, 1);");
        }
    } catch (ArrayIndexOutOfBoundsException e) {
    }

    db.execSQL("CREATE TABLE searches (" +
            "_id INTEGER PRIMARY KEY," +
            "search TEXT," +
            "date LONG" +
            ");");
}
 

@Override
public void performUpgrade(SQLiteDatabase database) {
    // cleanup
    database.execSQL("DELETE FROM measurements WHERE lat < -90 OR lat > 90 OR lat = 0 OR lon < -180 OR lon > 180 OR lon = 0");
    String cellFilterGsm = "(cid >= 1 AND cid <= 268435455 AND lac >= 1 AND lac <= 65535 AND mnc >= 0 AND mnc <= 999 AND mcc >= 100 AND mcc <= 999)";
    String cellFilterCdma = "(cid >= 1 AND cid <= 65535 AND lac >= 1 AND lac <= 65535 AND mnc >= 0 AND mnc <= 32767 AND mcc = 2147483647)";
    String cellFilter = "NOT (" + cellFilterGsm + " OR " + cellFilterCdma + ")";
    database.execSQL("DELETE FROM measurements WHERE cell_id IN (SELECT row_id FROM cells WHERE " + cellFilter + ")");
    database.execSQL("DELETE FROM cells WHERE " + cellFilter);
    database.execSQL("DELETE FROM cells_archive WHERE " + cellFilter);
}
 

/** Creates the underlying database table. */
public static void createTable(SQLiteDatabase db, boolean ifNotExists) {
    String constraint = ifNotExists? "IF NOT EXISTS ": "";
    db.execSQL("CREATE TABLE " + constraint + "\"SETTINGS\" (" + //
            "\"_id\" INTEGER PRIMARY KEY ," + // 0: id
            "\"TIME_BOX_STAGE1\" INTEGER," + // 1: timeBoxStage1
            "\"TIME_BOX_STAGE2\" INTEGER," + // 2: timeBoxStage2
            "\"TIME_BOX_STAGE3\" INTEGER," + // 3: timeBoxStage3
            "\"TIME_BOX_STAGE4\" INTEGER," + // 4: timeBoxStage4
            "\"TIME_BOX_STAGE5\" INTEGER," + // 5: timeBoxStage5
            "\"TIME_BOX_STAGE6\" INTEGER);"); // 6: timeBoxStage6
}
 

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
    Database.setDatabase(db);
    if(oldVersion==2) insertLanguageTags();
    if(oldVersion<=3) insertCategoryTags();
    if(oldVersion<=4)db.execSQL(Queries.BookmarkTable.CREATE_TABLE);
    if(oldVersion<=5)updateGalleryWithSizes(db);
    if(oldVersion<=6)db.execSQL(Queries.DownloadTable.CREATE_TABLE);
    if(oldVersion<=7)db.execSQL(Queries.HistoryTable.CREATE_TABLE);
    if(oldVersion<=8)insertFavorite(db);
    if(oldVersion<=9)addRangeColumn(db);
}
 

public static TableDataResponse exec(SQLiteDatabase database, String sql) {
    TableDataResponse tableDataResponse = new TableDataResponse();
    tableDataResponse.isSelectQuery = false;
    try {
        database.execSQL(sql);
    } catch (Exception e) {
        e.printStackTrace();
        tableDataResponse.isSuccessful = false;
        tableDataResponse.errorMessage = e.getMessage();
        return tableDataResponse;
    }
    tableDataResponse.isSuccessful = true;
    return tableDataResponse;
}
 

/**
 * {@inheritDoc}
 */
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + GeometryMetadata.TABLE_NAME);
    db.execSQL("DROP TABLE IF EXISTS " + TableMetadata.TABLE_NAME);
    db.execSQL("DROP TABLE IF EXISTS " + GeoPackageMetadata.TABLE_NAME);
    onCreate(db);
}
 

/**
 * Create the default log table.
 *
 * @param sqliteDatabase the db into which to create the table.
 * @throws IOException if something goes wrong.
 */
public static void createTables(SQLiteDatabase sqliteDatabase) throws IOException {
    StringBuilder sB = new StringBuilder();
    sB.append("CREATE TABLE ");
    sB.append(TABLE_LOG);
    sB.append(" (");
    sB.append(COLUMN_ID);
    sB.append(" INTEGER PRIMARY KEY AUTOINCREMENT, ");
    sB.append(COLUMN_DATAORA).append(" INTEGER NOT NULL, ");
    sB.append(COLUMN_LOGMSG).append(" TEXT ");
    sB.append(");");
    String CREATE_TABLE = sB.toString();

    sB = new StringBuilder();
    sB.append("CREATE INDEX " + TABLE_LOG + "_" + COLUMN_ID + " ON ");
    sB.append(TABLE_LOG);
    sB.append(" ( ");
    sB.append(COLUMN_ID);
    sB.append(" );");
    String CREATE_INDEX = sB.toString();

    sB = new StringBuilder();
    sB.append("CREATE INDEX " + TABLE_LOG + "_" + COLUMN_DATAORA + " ON ");
    sB.append(TABLE_LOG);
    sB.append(" ( ");
    sB.append(COLUMN_DATAORA);
    sB.append(" );");
    String CREATE_INDEX_DATE = sB.toString();

    sqliteDatabase.beginTransaction();
    try {
        sqliteDatabase.execSQL(CREATE_TABLE);
        sqliteDatabase.execSQL(CREATE_INDEX);
        sqliteDatabase.execSQL(CREATE_INDEX_DATE);
        sqliteDatabase.setTransactionSuccessful();
    } catch (Exception e) {
        throw new IOException(e.getLocalizedMessage());
    } finally {
        sqliteDatabase.endTransaction();
    }
}
 

public void deleteCity(String string) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.execSQL("DELETE FROM " + TABLE_CITIES + " WHERE " + KEY_CITY + " LIKE \'" + string + "\'");
}
 

/** Drops the underlying database table. */
public static void dropTable(SQLiteDatabase db, boolean ifExists) {
    String sql = "DROP TABLE " + (ifExists ? "IF EXISTS " : "") + "'"+TABLENAME+"'";
    db.execSQL(sql);
}
 

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    onCreate(db);
}
 

@Override public void execute(SQLiteDatabase database) throws SQLException {
  database.execSQL(asCompileableStatement().sql);
}
 

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

@Override
public void onCreate(SQLiteDatabase db) {
	db.execSQL("create table info (_id integer primary key autoincrement,time varchar(20),is_show_time varchar(2),user_name varchar(20),user_img varchar(50),text_msg varchar(1024),img_msg varchar(50),voice_msg varchar(50),msg_type varchar(2),is_me_msg varchar(2))");
}
 

@Override public void onCreate(SQLiteDatabase db) {
  for (Persistable.Converter<?> converter : converterMap.values()) {
    db.execSQL(converter.createStatement());
  }
}
 

/**
 * Drops the underlying database table.
 */
public static void dropTable(SQLiteDatabase db, boolean ifExists) {
    String sql = "DROP TABLE " + (ifExists ? "IF EXISTS " : "") + "\"" + TABLENAME + "\"";
    db.execSQL(sql);
}