16、Android 数据库案例

Android中采用的数据库是一个轻量级的数据库SQLite，在使用这个数据库时不用再导包，使用SQLite数据库时，首先要继承SQLiteOpenHelper，SQLiteOpenHelper是一个用于数据库的创建和管理的类，子类通过实现onCrate来实现数据库的创建，通过实现onUpGrade来实现数据库版本的控制。

需求分析:
需要实现人脸检测,识别和聚类的功能.这三个模块是独立的模块,如果面对相册中大量的数据,需要将中间结果存储到数据库中.
因为纯属验证算法的准确性,这里建立的Table比较简单.
建立三个表格,分别是Pciture\Faces\Cluster表

文件结构如下:
GalleryConstrant.java中主要记录表格中列的名称,把列的名称用变量的形式存储起来,方便在其他地方引用.
GalleryDatabaseHelper.java继承SQLiteOpenHelper类,这里主要实现建表操作.
FaceDetection.java是调用实例,对数据表进行操作.
GalleryConstrant.java中主要记录表格中的列的名称
package com.lenovo.ailab.smartkit4.database;
import android.net.Uri;
/**

• Created by mikeyna on 18-7-2.
  /
  public class GalleryConstrant {
  //picture table
  protected interface PictureColumns {
  String ID = “_picture_id”;
  String PATH = “path”;
  }
  public static final class Picture implements PictureColumns {
  private Picture() {
  }
  }
  //face table
  protected interface FaceColumns {
  String ID = “_face_id”;
  String PICTURE_ID = “picture_id”;
  String RECT = “face_rect”;
  String FEATURE = “face_feature”;
  String CLUSTER_ID = “cluster_id”;
  }
  public static final class Face implements FaceColumns {
  private Face() {
  }
  }
  //cluster tabel
  protected interface ClusterColumns {
  String ID = “_cluster_id”;
  String AVG_FEATURE = “average_feature”;
  }
  public static final class Cluster implements ClusterColumns {
  private Cluster() {
  }
  }
  }
  GalleryDatabaseHelper.java中对数据库进行操作,主要是完成建表的功能,还有就是把要建的表格统一到一个Tables的类
  package com.lenovo.ailab.smartkit4.database;
  import android.content.Context;
  import android.database.sqlite.SQLiteDatabase;
  import android.database.sqlite.SQLiteOpenHelper;
  import android.util.Log;
  /*
• Created by mikeyna on 18-7-2.
  */
  public class GalleryDatabaseHelper extends SQLiteOpenHelper {
  private static final String TAG = “GalleryDatabaseHelper”;
  private static final String DATABASE_NAME = “smartGallery.db”;
  private static final int DATABASE_VERSION = 1;
  private Context mContext;
  public interface Tables
  {
  String PICTURES = “pictures”;
  String Faces = “faces”;
  String CLUSTERS = “cluster”;
  }
  public GalleryDatabaseHelper(Context context) {
  super(context, DATABASE_NAME, null, DATABASE_VERSION);
  mContext = context;
  }
  @Override
  public void onCreate(SQLiteDatabase db) {
  Log.i(TAG, “onCreate!!”);
  db.execSQL(“CREATE TABLE " + Tables.PICTURES + “(” +
  GalleryConstrant.Picture.ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
  GalleryConstrant.Picture.PATH + " VARCHAR(20));”);
  db.execSQL(“CREATE TABLE " + Tables.Faces + “(” +
  GalleryConstrant.Face.ID + " INTEGER PRIMARY KEY AUTOINCREMENT ,” +
  GalleryConstrant.Face.PICTURE_ID + " INTEGER NOT NULL ," +
  GalleryConstrant.Face.CLUSTER_ID + " INTEGER NOT NULL ," +
  GalleryConstrant.Face.FEATURE + " TEXT NOT NULL ," +
  GalleryConstrant.Face.RECT + " TEXT NOT NULL );");
  db.execSQL(“CREATE TABLE " + Tables.CLUSTERS + “(” +
  GalleryConstrant.Cluster.ID + " INTEGER PRIMARY KEY AUTOINCREMENT ,” +
  GalleryConstrant.Cluster.AVG_FEATURE + " TEXT NOT NULL );");
  }
  @Override
  public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
  }
  }

写好上面这两个文件,就可以在我们的应用中使用数据库了:

/**

• Created by mikeyna on 18-7-3.
  */
  public class FaceDetection {
  private static String TAG = “FaceDetection”;
  private ArrayList mPicturePathList = new ArrayList<>();
  private Context mContext;
  GalleryDatabaseHelper mDBHelper;
  public FaceDetection(Context context)
  {
  mContext = context;
  mDBHelper = new GalleryDatabaseHelper(context);
  }
  public void init(String folderDir)
  {
  String labelfile = “”;
  String conf = “/sdcard/tfmodel/caffemodel/facedetector/deploy.prototxt”;
  String model = “/sdcard/tfmodel/caffemodel/facedetector/res10_300x300_ssd_iter_140000_fp16.caffemodel”;
  SmartNative.faceDetectorInit(model, conf, labelfile);
  getPicturePaths(folderDir);
  StorePictureInfo2DB();
  }
  public void close()
  {
  SmartNative.faceDetectorClose();
  }
  //获取相片的名称
  private ArrayList getPicturePaths(String dir)
  {
  mPicturePathList.clear();
  mPicturePathList = Files.searchFiles(dir, “.jpg”, false, mPicturePathList);
  return mPicturePathList;
  }
  //store picture information to database
  private void StorePictureInfo2DB()
  {
  SQLiteDatabase db = mDBHelper.getReadableDatabase();
  for(String imagePath : mPicturePathList)
  {
  Cursor cursor = db.query(GalleryDatabaseHelper.Tables.PICTURES,
  null,
  GalleryConstrant.Picture.PATH + " =?", new String[]{imagePath},
  null, null, null);
  if(cursor != null) {
  if(cursor.moveToNext())
  continue;
  }
  ContentValues values = new ContentValues();
  values.put(GalleryConstrant.Picture.PATH, imagePath);
  db.insert(GalleryDatabaseHelper.Tables.PICTURES, null, values);
  }
  }
  //face detection and store result to database
  public void faceDetectAndStoreResult(){
  SQLiteDatabase db = mDBHelper.getReadableDatabase();
  Cursor cursor = db.query(GalleryDatabaseHelper.Tables.PICTURES,
  new String[]{GalleryConstrant.Picture.ID, GalleryConstrant.Picture.PATH},
  null, null, null, null,
  null, null);
  if (cursor == null) {
  Log.i(TAG, “cursor is null”);
  return ;
  }
  try {
  while(cursor.moveToNext()) {
  int imageId = cursor.getInt(0);
  String imagePath = cursor.getString(1);
  ArrayList labelInfoList = DetectFace(imagePath);
  if(labelInfoList != null)
  {
  ContentValues values = new ContentValues();
  //删除原始记录 db.delete(GalleryDatabaseHelper.Tables.Faces,
  GalleryConstrant.Face.PICTURE_ID + “=?”,
  new String[]{String.valueOf(imageId)});
  //将现有的人脸检测结果插入数据库
  for(LabelInfo labelInfo : labelInfoList)
  {
  Gson g = new Gson();
  String jsonString = g.toJson(labelInfo.boundingBox);
  //add label information to database values.put(GalleryConstrant.Face.PICTURE_ID, imageId); values.put(GalleryConstrant.Face.CLUSTER_ID, -1);
  values.put(GalleryConstrant.Face.RECT, jsonString); values.put(GalleryConstrant.Face.FEATURE, “”); db.insert(GalleryDatabaseHelper.Tables.Faces, null, values);
  }
  }
  }
  } catch (Exception e) {
  e.printStackTrace();
  } finally {
  cursor.close();
  }
  }
  private ArrayList DetectFace(String imagePath)
  {
  if(imagePath.compareTo("") == 0) return null;
  ArrayList labelInfos = SmartNative.faceDetect(imagePath);
  return labelInfos;
  }
  }