SQLite - 是否可以通过insert语句插入BLOB？ [英] SQLite - Is it possible to insert a BLOB via insert statement?

问题描述

我正在开发一个Android应用程序，我使用Sqlite数据库来存储一些位图。我想要一些图像将在用户安装应用程序时自动插入。

I'm developing an Android application and i'm using a Sqlite database to store some bitmaps. I want some images to be automatically inserted when the user installs the application.

我正在使用 SQLiteOpenHelper 类：

public class DatabaseHelper extends SQLiteOpenHelper {

...

DatabaseHelper(Context context, String nameOfDB, int version, String[] scriptSQLCreate,
        String scriptSQLDelete) {
    super(context, nameOfDB, null, version);

    this.scriptSQLCreate = scriptSQLCreate;
    this.scriptSQLDelete = scriptSQLDelete;
}

@Override
public void onCreate(SQLiteDatabase db) {
    int numScripts = scriptSQLCreate.length;
    for(int i = 0; i<numScripts; i++){
    Log.i(TAG,"Creating database, executing script " + i);
    db.execSQL(scriptSQLCreate[i]);
    }
}
}

...

我想传递一个常量到上面显示的scriptSQLCreate参数，如下所示：

I want to pass a constant to the scriptSQLCreate parameter shown above that would be like so:

private static final String[] SCRIPT_DATABASE_CREATE = {
   "create table memes(  id integer primary key autoincrement," + 
                     + " img blob not null," + 
                     + " name text not null unique)" ,
   "insert into memes(img,name) values(BITMAP1,'1.jpg')",
   "insert into memes(img,name) values(BITMAP2,'2.jpg')",
   "insert into memes(img,name) values(BITMAP3,'3.jpg')"}    

}

任何帮助将会非常沉重，

Any help will be much apreciated,

Thx，
Tulio Zahn

Thx, Tulio Zahn

推荐答案

如果真的，真的想你可以使用非常长的十六进制文字作为blob字面量：

If you really, really want to you can use a very long hex literal as a blob literal:

insert into memes(img, name) values(X'0102030405060708090a0b0c0d0e0f', '1.jpg')

然而，这通常是一个坏主意;而是查看参数化查询。它们将允许您使用占位符而不是实际值来编译语句，然后重复使用它多次，根据需要填充占位符：

However, this is usually a bad idea; instead, go look at parameterised queries. They will let you compile a statement once using placeholders instead of actual values, and then reuse it many times, filling in the placeholders as needed:

SQLiteStatement p = sqlite.compileStatement("insert into memes(img, name) values(?, ?)");

byte[] data = loadData("1.jpg");
p.bindBlob(1, data);
p.bindString(2, "1.jpg");
p.execute();

byte[] data = loadData("2.jpg");
p.bindBlob(1, data);
p.bindString(2, "2.jpg");
p.execute();

（警告---未测试代码。）

(Warning --- code not tested.)

一般来说，你应该使用参数化查询随处可用，因为它们是一种确定的方式，以避免SQL注入攻击，加上通常更容易和更清楚。通过将字符串粘合在一起来组装SQL查询应尽可能避免。

In general you should be using parameterised queries everywhere, as they're a sure-fire way to avoid SQL injection attacks, plus are usually easier and clearer. Assembling SQL queries by glueing strings together should be avoided at all costs.

这篇关于SQLite - 是否可以通过insert语句插入BLOB？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！