Android NDK使用sqlite3.h 查询sdcard和硬盘的数据库

前言
在盒子开发中，用sqlite查询大量数据的话会很慢，所以借助NDK用sqlite3.h来查询db数据库就非常的快捷，下面就具体操作分享下 做个笔记
1.native-lib.cpp

#include <jni.h>
#include <string>
#include "AndroidLog.h"
#include "sqlite3.h"
#define CLASSNAME_VISION_DET_RET "com/imi/myapplication/MainActivity"

extern "C"
JNIEXPORT jstring JNICALL
Java_com_imi_myapplication_MainActivity_stringFromJNI(
        JNIEnv* env,
        jobject /* this */) {
    std::string hello = "Hello from C++";
    return env->NewStringUTF(hello.c_str());
}
 int _sql_callback(void * notused, int argc, char ** argv, char **szColName)
{
       int i;  char *sresult;
       for ( i=0; i < argc; i++ )
       {
//           strcpy((char *)notused,argv[i]); //上面char类型array的，类型转换
           LOGE("pName=%s",szColName[i]);
           LOGE("argv=%s",argv[i]);
       }
        return 0;
 }
extern "C"
JNIEXPORT jstring JNICALL
Java_com_imi_myapplication_MainActivity_querydbdata(JNIEnv *env, jobject instance,
                                                    jstring dbpath_) {
    const char *dbpath = env->GetStringUTFChars(dbpath_, 0);
//    const char * sSQL3 = "select SONGNAME from songs;";
//    char *pErrMsg= 0;
//    int ret = 0;
//    //打开数据库，如果数据库不存在，会建立一个数据库
//      ret = sqlite3_open(dbpath, &db);
//      if ( ret != SQLITE_OK )
//      {
//          LOGE("open error! : %s",sqlite3_errmsg(db));
//          sqlite3_free(pErrMsg);
//      }else{
//          LOGE("open db OK!\n");
//      }
//     // 查询数据表
//      sqlite3_exec(db, sSQL3, _sql_callback, 0, &pErrMsg);
//      // 关闭数据库
//     sqlite3_close(db);
//     db = 0;
    sqlite3 * db = 0;
    /*查找名字为Sky的个数*/
    char *sql = "select count(*) from  songs;";
    sqlite3_stmt *stmt = NULL;
    sqlite3_open(dbpath, &db);
    /*将sql语句转换为sqlite3可识别的语句，返回指针到stmt*/
    int res = sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, NULL);
    if (SQLITE_OK != res || NULL == stmt) {
        LOGE("open error!");
    }
    /*执行准备好的sqlite3语句*/
    res = sqlite3_step(stmt);
    if (res != SQLITE_ROW) {
        sqlite3_finalize(stmt);
    }
    int count = sqlite3_column_int(stmt, 0);
    if (count < 0) {
        sqlite3_finalize(stmt);
    }
    LOGE("count= %d", count);
    char *string =(char*)malloc(sizeof(char)*16);
    sprintf(string,"%d",count);
    sqlite3_finalize(stmt);
    //释放数据
    env->ReleaseStringUTFChars(dbpath_, dbpath);
    return env->NewStringUTF(string);
}
extern "C"
JNIEXPORT jstring JNICALL
Java_com_imi_myapplication_MainActivity_querydbbyname(JNIEnv *env, jobject instance,
                                                      jstring dbpath_, jstring name_) {
    const char *dbpath = env->GetStringUTFChars(dbpath_, 0);
    const char *name = env->GetStringUTFChars(name_, 0);
    const char *key="%';";
    int count=0;
    sqlite3 * db = 0;
    sqlite3_open(dbpath, &db);
    /*查找name为Sky的数据*/
//    char *sql = "select * from  songs LIMIT 20 OFFSET 0;";
    char *sql = "select * from  songs where SONGNAME like '";
    char *sqlites =(char*)malloc(sizeof(sql)+sizeof(name)+ sizeof(key));
    *sqlites=0;
//    sprintf(sqlites,"%s%s%s",sql,name,key);
    strcat(sqlites,sql);
    strcat(sqlites,name);
    strcat(sqlites,key);
    LOGE("sql=%s",sqlites);
    sqlite3_stmt *stmt = NULL;
    /*将sql语句转换为sqlite3可识别的语句，返回指针到stmt*/
    int res = sqlite3_prepare_v2(db, sqlites, strlen(sqlites), &stmt, NULL);
    if (SQLITE_OK != res || NULL == stmt) {
        LOGE("open error!");
    }
     char *results=(char *)malloc(sizeof(char)*1024*500);
          *results=0;
    /*执行准备好的sqlite3语句*/
    while (SQLITE_ROW == sqlite3_step(stmt)) {
        char *songbm=(char *)sqlite3_column_text(stmt, 0);
//        char *songname=(char *)sqlite3_column_text(stmt, 1);
        char *desc =(char*)malloc(sizeof(songbm)+ sizeof(char)*10);
        *desc=0;
//        LOGE("SONGBM: %s",songbm);
//        LOGE("SONGNAME: %s",songname);
//        sprintf(desc,"%s%s%s%s%s","songbm:",songbm,"--songname:",songname,"--");
        strcat(desc,"songbm:");
        strcat(desc,songbm);
//        strcat(desc,"--songname:");
//        strcat(desc,songname);
        strcat(desc,"--");
//        LOGE("desc=%s",desc);
        strcat(results,desc);
//        LOGE("result=%s",result);
        count++;
        free(desc);
    }
    LOGE("count2=%d",count);
    sqlite3_finalize(stmt);
    env->ReleaseStringUTFChars(dbpath_, dbpath);
    env->ReleaseStringUTFChars(name_, name);
    return env->NewStringUTF(results);
}
jstring charTojstring(JNIEnv* env, const char* pat) {
    //定义java String类 strClass
    jclass strClass = (env)->FindClass("java/lang/String;");
    //获取String(byte[],String)的构造器,用于将本地byte[]数组转换为一个新String
    jmethodID ctorID = (env)->GetMethodID(strClass, "<init>", "([BLjava/lang/String;)V");
    //建立byte数组
    jbyteArray bytes = (env)->NewByteArray(strlen(pat));
    //将char* 转换为byte数组
    (env)->SetByteArrayRegion(bytes, 0, strlen(pat), (jbyte*) pat);
    // 设置String, 保存语言类型,用于byte数组转换至String时的参数
    jstring encoding = (env)->NewStringUTF("GB2312");
    //将byte数组转换为java String,并输出
    return (jstring) (env)->NewObject(strClass, ctorID, bytes, encoding);
}

extern "C"
JNIEXPORT jobjectArray JNICALL
Java_com_imi_myapplication_MainActivity_querydbbykey(JNIEnv *env, jobject instance, jstring dbpath_,
                                                     jstring name_) {
    const char *dbpath = env->GetStringUTFChars(dbpath_, 0);
    const char *name = env->GetStringUTFChars(name_, 0);
    const char *key="%';";
    int count=0;
    sqlite3 * db = 0;
    sqlite3_open(dbpath, &db);
    jclass cls=env->FindClass(CLASSNAME_VISION_DET_RET);
    jobjectArray jobjectarray=env->NewObjectArray(7241,cls,NULL);
    char *sql = "select * from  songs where SONGNAME like '";
    char *sqlite =(char*)malloc(sizeof(sql)+sizeof(name)+ sizeof(key));
    sprintf(sqlite,"%s%s%s",sql,name,key);
    LOGE("sql2=%s",sqlite);
    sqlite3_stmt *stmt = NULL;
    /*将sql语句转换为sqlite3可识别的语句，返回指针到stmt*/
    int res = sqlite3_prepare_v2(db, sqlite, strlen(sqlite), &stmt, NULL);
    if (SQLITE_OK != res || NULL == stmt) {
        LOGE("open error!");
    }
//    jstring stringsongbm=NULL;
    /*执行准备好的sqlite3语句*/
    while (SQLITE_ROW == sqlite3_step(stmt)) {
        char *songbm=(char *)sqlite3_column_text(stmt, 0);
        LOGE("SONGBM: %s",songbm);
        LOGE("SONGNAME: %s",sqlite3_column_text(stmt, 1));
//        char *songbm1;
//        strcpy(songbm1,(char *)songbm);
        jstring stringsongbm=charTojstring(env,songbm);
        env->SetObjectArrayElement(jobjectarray,count,(jobject)stringsongbm);
        count++;
    }
    LOGE("count=%d",count);
    sqlite3_finalize(stmt);
//    env->DeleteLocalRef(stringsongbm);
    env->ReleaseStringUTFChars(dbpath_, dbpath);
    env->ReleaseStringUTFChars(name_, name);
    env->DeleteLocalRef(jobjectarray);
    return jobjectarray;
}extern "C"
JNIEXPORT jstring JNICALL
Java_com_imi_myapplication_MainActivity_querydbbyId(JNIEnv *env, jobject instance, jstring dbpath_,
                                                    jstring songbm_) {
    const char *dbpath = env->GetStringUTFChars(dbpath_, 0);
    const char *songbm = env->GetStringUTFChars(songbm_, 0);
    const char *key=";";
    int count=0;
    sqlite3 * db = 0;
    sqlite3_open(dbpath, &db);
    /*查找name为Sky的数据*/
//    char *sql = "select * from  songs LIMIT 20 OFFSET 0;";
    char *sql = "select * from  songs where SONGBM = ";
    char *sqlite =(char*)malloc(sizeof(sql)+sizeof(songbm)+ sizeof(key));
    sprintf(sqlite,"%s%s%s",sql,songbm,key);
    LOGE("sql3=%s",sqlite);
    sqlite3_stmt *stmt = NULL;
    /*将sql语句转换为sqlite3可识别的语句，返回指针到stmt*/
    int res = sqlite3_prepare_v2(db, sqlite, strlen(sqlite), &stmt, NULL);
    if (SQLITE_OK != res || NULL == stmt) {
        LOGE("open error!");
    }
    char *result=(char *)malloc(sizeof(char)*1024);
    *result=0;
    /*执行准备好的sqlite3语句*/
    while (SQLITE_ROW == sqlite3_step(stmt)) {
        char *songbm=(char *)sqlite3_column_text(stmt, 0);
        char *songname=(char *)sqlite3_column_text(stmt, 1);
        char *zs=(char *)sqlite3_column_text(stmt,2);
        char *singer=(char *)sqlite3_column_text(stmt, 3);
        char *songtype=(char *)sqlite3_column_text(stmt, 4);
        char *desc =(char*)malloc(sizeof(songbm)+sizeof(songname)+sizeof(zs)+sizeof(singer)+sizeof(songtype)+ sizeof(char)*100);
        *desc=0;
        strcat(desc,"songbm:");
        strcat(desc,songbm);
        strcat(desc,"|");
        strcat(desc,"songname:");
        strcat(desc,songname);
        strcat(desc,"|");
        strcat(desc,"zs:");
        strcat(desc,zs);
        strcat(desc,"|");
        strcat(desc,"singer:");
        strcat(desc,singer);
        strcat(desc,"|");
        strcat(desc,"songtype:");
        strcat(desc,songtype);
//        LOGE("desc=%s",desc);
        strcat(result,desc);
//        LOGE("result=%s",result);
        count++;
        free(desc);
        desc=NULL;
    }
    LOGE("count3=%d",count);
    sqlite3_finalize(stmt);
    env->ReleaseStringUTFChars(dbpath_, dbpath);
    env->ReleaseStringUTFChars(songbm_, songbm);
    return env->NewStringUTF(result);
}

2. MainActivity.java

import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.widget.TextView;
import java.util.List;

public class MainActivity extends Activity {
    private TextView result_txt;
    // Used to load the 'native-lib' library on application startup.
    static {
        System.loadLibrary("native-lib");
    }

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        // Example of a call to a native method
        TextView tv = (TextView) findViewById(R.id.sample_text);
        result_txt=(TextView)findViewById(R.id.result_text);
        tv.setText(querydbdata("/mnt/sata/KTV_VOD/db/vod_foreign.db"));
        long currentTime=System.currentTimeMillis();
        tv.setText("共有数据:"+querydbdata("/mnt/sdcard/Others/vod_foreign.db"));
        long nowTime=System.currentTimeMillis();
        long usetimes=System.currentTimeMillis()-currentTime;
        long currentTime1=System.currentTimeMillis();
        String result=querydbbyname("/mnt/sdcard/Others/vod_foreign.db","我");
        long nowTime2=System.currentTimeMillis();
        long usetimes2=System.currentTimeMillis()-currentTime1;
        Log.e("MainActivity","usetimes2:"+usetimes2+"---currentTime1:"+currentTime1+"---nowTime2:"+nowTime2);
        String [] songbm=result.split("--");
        Log.e("MainActivity","songbm is size"+songbm.length+"最后一个:"+songbm[songbm.length-1]);
        result_txt.setText("查询结果总数为:"+songbm.length+"  最后一个:"+songbm[songbm.length-1]+"  第一个:"+songbm[0]);
        String [] songbms=querydbbykey("/mnt/sdcard/Others/vod_foreign.db","我");
        Log.e("MainActivity","size:"+songbms.length);
        String songname=querydbbyId("/mnt/sdcard/Others/vod_foreign.db","10000004");
        Log.e("MainActivity",songname);
    }

    /**
     * A native method that is implemented by the 'native-lib' native library,
     * which is packaged with this application.
     */
    public native String stringFromJNI();
    public native String querydbdata(String dbpath);
    public native String querydbbyname(String dbpath,String name);
    public native String[] querydbbykey(String dbpath,String name);
    public native String querydbbyId(String dbpath,String songbm);
}

3.AndroidLog.h

//
// Created by ywl on 2017-11-12.
//
#pragma once
#ifndef WLPLAYER_ANDROIDLOG_H
#define WLPLAYER_ANDROIDLOG_H

#include <android/log.h>

#define LOG_SHOW true
#define  LOG_TAG    "ywl5320"
#define LOGD(...) __android_log_print(ANDROID_LOG_DEBUG,"ywl5320",##__VA_ARGS__)
#define LOGE(...)  __android_log_print(ANDROID_LOG_ERROR, LOG_TAG, __VA_ARGS__)

#endif //WLPLAYER_ANDROIDLOG_H

4.sqlite3.h 和 libsqlite.so下载路径