基于SuperView 串口屏实现 ARM linux sqlite3数据库的移植和使用方法

SuperView 串口屏 运行的操作系统是中科世为基于Linux 开发的FlyThings OS；系统提供集成GUI开发环境，方便客户快速开发应用。

 

官网链接： www.zkswe.com ;

开发者页面：developer.flythings.comhttp://doc.zkswe.com/;

淘宝页面：superview.taobao.com

这里我们讲解一下如何利用SuperView的显示模组实现sqlite数据库的操作。

新建好一个FlyThings Project 项目。

在jni代码目录中添加 db文件夹。放入从github上下载的sqlite3源码：sqlite3.c ,sqlite3.h

github 路径：https://github.com/zkswe/thirdPart/tree/master/sqlite

设计一个供应用开发使用的Database类

#ifndef JNI_DB_DATABASE_H_
#define JNI_DB_DATABASE_H_


#include <vector>
#include <string>
#include "sqlite3.h"


typedef struct{
	std::string name;
	std::string num;
	std::string age;
	std::string score;
}S_INFOS;




class Database {
public:
	Database(std::string path);
	virtual ~Database();
	void recodeResult(std::string name,std::string num,std::string age,std::string score);
	std::vector<S_INFOS> getRecodes();
	void clear();
	void saveas(std::string path);
private:
	sqlite3* pDB;
	std::vector<S_INFOS> dbs;
};


#endif /* JNI_DB_DATABASE_H_ */
	std::string name;
	std::string num;
	std::string age;
	std::string score;
}S_INFOS;




class Database {
public:
	Database(std::string path);
	virtual ~Database();
	void recodeResult(std::string name,std::string num,std::string age,std::string score);
	std::vector<S_INFOS> getRecodes();
	void clear();
	void saveas(std::string path);
private:
	sqlite3* pDB;
	std::vector<S_INFOS> dbs;
};


#endif /* JNI_DB_DATABASE_H_ */

在Database.cpp 内添加

/*
 * Database.cpp
 *
 *  Created on: 2018年5月27日
 *      Author: Kwolve
 */

#include "Database.h"
#include "utils/Log.h"
#include "uart/ProtocolData.h"
#include "uart/ProtocolParser.h"



void Database::recodeResult(std::string name,std::string num,std::string age,std::string score) {
	std::string sqltestr ="";
	sqltestr += "insert into recode(name,num,age,score) values('"+name+"','"+
			num+"','"+
			age+"','"+
			score+"');";

	char* errmsg;

	LOGD("sqlite exec :%s",sqltestr.c_str());
	if(pDB != NULL){
		int ret = sqlite3_exec(pDB,sqltestr.c_str(),0,0, &errmsg);
		if(ret != SQLITE_OK){
			LOGD("sqlite exec err:%s",errmsg);
		}
	}

}

std::vector<S_INFOS> Database::getRecodes() {
	const char* cmd = "select * from recode";
	char** result;
	int nRow;
	int nCol;
	char* errmsg;
	dbs.clear();
	dbs.empty();
	int ret = sqlite3_get_table(pDB, cmd, &result, &nRow, &nCol, &errmsg);
	if(ret != SQLITE_OK){
		LOGD("select * form recode ret:%s",errmsg);
		return dbs;
	}
	std::string strOut;
   int nIndex = nCol;
   S_INFOS info;
   for(int i=0;i<nRow;i++)
   {
	   info.name = result[nIndex];
	   ++nIndex;
	   info.num = result[nIndex];
	   ++nIndex;
	   info.age = result[nIndex];
	   ++nIndex;
	   info.score = result[nIndex];
	   ++nIndex;
	   dbs.push_back(info);
   }
   LOGD("read size %d",nRow);
   LOGD("read size %d",dbs.size());

	sqlite3_free_table(result);
	return dbs;
}

Database::Database(std::string path) {
	// TODO 自动生成的构造函数存根
	int ret = sqlite3_open(path.c_str(), &pDB);
	if(ret != SQLITE_OK){
		LOGD("open sqlite ret:%d",ret);
		pDB = NULL;
		return;
	}
	const char* cmd = "CREATE TABLE IF NOT EXISTS recode(name,num,age,score);";
	char* errmsg;
	sqlite3_exec(pDB,cmd,0,0, &errmsg);
	if(ret != SQLITE_OK){
		LOGD("create table ret:%s",errmsg);
	}
}

Database::~Database() {
	// TODO 自动生成的析构函数存根
	sqlite3_free(pDB);
}

void Database::clear() {
	const char* cmd = "DELETE FROM recode";
	char* errmsg;
	int ret = sqlite3_exec(pDB,cmd,0,0, &errmsg);
	if(ret != SQLITE_OK){
		LOGD("create DELETE ret:%s",errmsg);
	}
}

void Database::saveas(std::string path) {
}

在Logic/mainLogic.cc 内添加数据库操作代码

• 定义数据库

#include "db/Database.h"

Database dbs("/mnt/extsd/test.db");

• 保存时操作

static bool onButtonClick_ButtonSave(ZKButton *pButton) {
    //LOGD(" ButtonClick ButtonSave !!!\n");
	dbs.recodeResult(mEdittextNamePtr->getText(),
			mEdittextNoPtr->getText(),
			mEdittextAgePtr->getText(),
			mEdittextScorePtr->getText());
    return true;
}

数据库读取以及列表显示

• 新建一个data.ftu 的ui文件，用于展示数据库内容

• 代码中添加列表显示的部分

#include "db/Database.h"

extern Database dbs;
static std::vector<S_INFOS> pVector;
static int nRows;
static int nSelectRow;
const char* titileTab[]={
		"姓名","学号","年龄","分数"
};
/**
 * 注册定时器
 * 在此数组中添加即可
 */
static S_ACTIVITY_TIMEER REGISTER_ACTIVITY_TIMER_TAB[] = {
	//{0,  6000}, //定时器id=0, 时间间隔6秒
	//{1,  1000},
};

static void onUI_init(){
    //Tips :添加 UI初始化的显示代码到这里,如:mText1->setText("123");
	pVector = dbs.getRecodes();
	nRows = pVector.size()+1;
}

static void onUI_quit() {

}


static void onProtocolDataUpdate(const SProtocolData &data) {
    // 串口数据回调接口
}

static bool onUI_Timer(int id){
    //Tips:添加定时器响应的代码到这里,但是需要在本文件的 REGISTER_ACTIVITY_TIMER_TAB 数组中 注册
    //id 是定时器设置时候的标签,这里不要写耗时的操作，否则影响UI刷新,ruturn:[true] 继续运行定时器;[false] 停止运行当前定时器
    return true;
}


static bool ondataActivityTouchEvent(const MotionEvent &ev) {
    // 返回false触摸事件将继续传递到控件上，返回true表示该触摸事件在此被拦截了，不再传递到控件上
    return false;
}
static int getListItemCount_Listview1(const ZKListView *pListView) {
    //LOGD(" getListItemCount_ Listview1  !!!\n");
    return nRows*4;
}

static void obtainListItemData_Listview1(ZKListView *pListView,ZKListView::ZKListItem *pListItem, int index) {
    //LOGD(" obtainListItemData_ Listview1  !!!\n");
	if(index < 4){
		pListItem->setText(titileTab[index]);
	}else{
		int rindex = index-4;
		int row = rindex/4;
		S_INFOS info = pVector.at(row);
		int id = rindex%4;
		switch(id){
		case 0:
			pListItem->setText(info.name);
			break;
		case 1:
			pListItem->setText(info.num);
			break;
		case 2:
			pListItem->setText(info.age);
			break;
		case 3:
			pListItem->setText(info.score);
			break;
		}
	}
}

static void onListItemClick_Listview1(ZKListView *pListView, int index, int id) {
    //LOGD(" onListItemClick_ Listview1  !!!\n");
	nSelectRow = index/4 - 1;
	S_INFOS info = pVector.at(nSelectRow);
	mEdittextNamePtr->setText(info.name);
	mEdittextNoPtr->setText(info.num);
	mEdittextAgePtr->setText(info.age);
	mEdittextSCorePtr->setText(info.score);
	mWindowTipPtr->showWnd();
}

源码下载地址请加入QQ群：371608200

官网链接： www.zkswe.com ;

开发者页面：doc.zkswe.com;

淘宝页面：shop434237749.taobao.com