Android 继承SQLiteOpenHelper自定义DBHelper存取数据与图像如下:
package com.test;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
public class DBHelper extends SQLiteOpenHelper {
private static final String DB_NAME = "test.db";
private static final int DB_VERSION = 1;
private static final String TABLE_NAME = "info";
private static final String CREATE_INFO = "create table if not exists info("
+ "id integer primary key autoincrement,name varchar(20),"
+ "time varchar(20),img BLOB)";
private SQLiteDatabase db;
DBHelper(Context c) {
//
super(c, DB_NAME, null, DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
this.db = db;
db.execSQL(CREATE_INFO);
}
public void insert(ContentValues values, String tableName) {
db = getWritableDatabase();
db.insert(tableName, null, values);
db.close();
}
// Return cursor with all columns by tableName
public Cursor query(String tableName) {
db = getWritableDatabase();
Cursor c = db.query(tableName, null, null, null, null, null, null);
return c;
}
// Return cursor by SQL string
public Cursor rawQuery(String sql, String[] args) {
db = getWritableDatabase();
Cursor c = db.rawQuery(sql, args);
return c;
}
// Execute a single SQL statement(as insert,create,delete)instead of a query
public void execSQL(String sql) {
db = getWritableDatabase();
db.execSQL(sql);
}
// Delete by id
public void del(int id) {
if (db == null)
db = getWritableDatabase();
db.delete(TABLE_NAME, "id=?", new String[] { String.valueOf(id) });
}
public void close() {
if (db != null)
db.close();
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
// Bitmap to byte[]
public byte[] bmpToByteArray(Bitmap bmp) {
// Default size is 32 bytes
ByteArrayOutputStream bos = new ByteArrayOutputStream();
try {
bmp.compress(Bitmap.CompressFormat.JPEG, 100, bos);
bos.close();
} catch (IOException e) {
e.printStackTrace();
}
return bos.toByteArray();
}
// Cursor to bitmap
Bitmap cursorToBmp(Cursor c, int columnIndex) {
byte[] data = c.getBlob(columnIndex);
try {
return BitmapFactory.decodeByteArray(data, 0, data.length);
} catch (Exception e) {
return null;
}
}
}
DBhelper调用方法:
//定义helper
private static DBHelper helper;
//创建helper
helper = new DBHelper(this);
//插入数据与图像
ContentValues values = new ContentValues();
values.put("name", "test");
values.put("img", helper.bmpToByteArray(bmp));
helper.insert(values, "info");
//访问数据与图像
Cursor c = helper.rawQuery("select * from info", null);
c.moveToLast();
String name = c.getString(c.getColumnIndex("name"));
Bitmap bmp = cursorToBmp(c, c.getColumnIndex("img"));