前言
在盒子开发中,用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);
}
- 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