sqlite数据库方法类使用说明-创建数据表、查询数据表、插入记录

最新推荐文章于 2024-06-17 20:46:13 发布

pioneer_t

最新推荐文章于 2024-06-17 20:46:13 发布

阅读量660

点赞数 8

文章标签：数据库 sqlite jvm

本文链接：https://blog.csdn.net/Programmer_Dong/article/details/134625828

版权

本文主要演示sqlite开源项目中的数据库方法类基础操作使用说明（包括创建数据表、插入数行、删除行、查询数据表等）。本项目旨在分享知识和经验，欢迎广大网友一起加入到开源项目中一起分享贡献知识和经验。

文章目录

命令行模式的sqlite操作程序

首先编译命令行模式的sqlite操作程序，源代码如下

#include <stdio.h>
#include <iostream>
#include <sstream>
#include "sqlite/sqlite.hpp"
#include <cstring>
#include "./Wtime.h"
#include <sys/time.h>
#include <unistd.h>

#include "./main.hpp"

typedef struct {
	bool	output;
	bool	timing;
} dbCfg_t;

/********************************
 * std::cin.getline()属于字符串流
 * 用法：char str[100];std::cin.getline(str, 100);
 * 
 * std::getline()属于std::string流
 * 用法：std::string str;std::getline(std::cin, str);
********************************/

int test_exec(int argc, char* argv[])
{
	class sql::sqlite* handle;
 	struct timeval start,end;
	dbCfg_t cfg;
	std::string sqlCmd;
	if (argc>2) {
		handle = new sql::sqlite(argv[2]);
		if (handle == nullptr) {
			std::cout << "数据库[" << argv[2] << "]打开失败！" << std::endl;
			return -1;
		}
	} else {
		std::cout << "参数错误：输入正确的参数，如：./app 6 database.db" << std::endl;
		return -1;
	}
	//memset (&cfg, 0, sizeof (TdbCfg));
	cfg.timing = true;
	cfg.output = true;
	while (1) {
		std::cout << ">";
		//std::cin >> sqlCmd;
		std::getline (std::cin, sqlCmd);
		if (sqlCmd == "help") {
			std::cout << "[帮助]" << std::endl;
			std::cout << "output disable:关闭查询输出" << std::endl;
			std::cout << "output enable:打开查询输出" << std::endl;
			std::cout << "timing disable:关闭计时器" << std::endl;
			std::cout << "timing enable:打开计数器" << std::endl;
			std::cout << "exit:退出" << std::endl;
		} else if (sqlCmd == "output disable") {
			cfg.output = false;
		} else if (sqlCmd == "output enable") {
			cfg.output = true;
		} else if (sqlCmd == "timing disable") {
			cfg.timing = false;
		} else if (sqlCmd == "timing enable") {
			cfg.timing = true;
		} else if (sqlCmd == "exit") {
			delete handle;
			return 0;
		} else {
			gettimeofday (&start, NULL);
			cfg.output?handle->exec (sqlCmd.c_str(), test_callback):handle->exec (sqlCmd.c_str());
			//sleep (1);
			gettimeofday (&end, NULL);
			if (cfg.timing) {
				std::cout << "耗时：" << (end.tv_sec-start.tv_sec)*1000 + (end.tv_usec-start.tv_usec)/1000 << " ms,";
				std::cout << (end.tv_usec-start.tv_usec)%1000 << " us" << std::endl;
			}
		}
		sqlCmd.clear ();
	}
}

编译方法

make type=1

这里我们把编译好的命令行程序重命名为cmdexec，以备后续使用

mv exec cmdexec

创建数据表

接下来是创建数据表的测试代码

#include "./samples_create_table.hpp"
#include "sqlite/sqlite.h"




int samples_create_table (int argc, char** argv)
{
	if (argc < 2) {
		printf ("请输入一个参数以指定数据库文件名！\n");
		return -1;
	}
	sql::sqlite db(argv[1]);
	db.exec ("create table if not exists employee("
		"ID INTEGER PRIMARY KEY  AUTOINCREMENT, "
		"NAME           TEXT    NOT NULL UNIQUE, "
		"AGE            INT     NOT NULL, "
		"ADDRESS        CHAR(50), "
		"SALARY         REAL"
	");");
	return 0;
}

上面的sql语句采用的是未包装的全拼命令，也可以使用接口定义好的宏定义来节省一些重复操作，代码如下：

#include "./samples_create_table.hpp"
#include "sqlite/sqlite.h"




int samples_create_table (int argc, char** argv)
{
	if (argc < 2) {
		printf ("请输入一个参数以指定数据库文件名！\n");
		return -1;
	}
	sql::sqlite db(argv[1]);
	db.exec (SQLCMD_CREATE_TABLE ("employee",
		"ID INTEGER PRIMARY KEY  AUTOINCREMENT, "
		"NAME           TEXT    NOT NULL UNIQUE, "
		"AGE            INT     NOT NULL, "
		"ADDRESS        CHAR(50), "
		"SALARY         REAL"
	));
	return 0;
}

数据库语句简化宏定义

项目中定义好的宏定义在inc/sqlite/sqlCmd.h文件中，其内容如下：

#ifndef __SELDOM_SQLITE_SQLCMD_H
#define __SELDOM_SQLITE_SQLCMD_H


//SQL 语句  仅供参考

//创建表
#define SQLCMD_CREATE_TABLE(TABLE_NAME,MEMBER)		"CREATE TABLE IF NOT EXISTS " TABLE_NAME " (" MEMBER ");" 
//删除表
//#define	SQLCMD_DROP_TABLE(TABLE_NAME)		"DROP IF EXISTS TABLE " TABLE_NAME ";" 
#define	SQLCMD_DROP_TABLE(TABLE_NAME)		"DROP TABLE " TABLE_NAME ";" 
//检查表是否存在
#define SQLCMD_CHECK_TABLE(TABLE_NAME)		"SELECT COUNT(*) AS Cnt FROM sqlite_master where type='table' and name='" TABLE_NAME "';" 
//查询记录数
#define SQLCMD_RECORDCNT(TABLE_NAME) "SELECT COUNT(*) AS Cnt FROM " TABLE_NAME 
#define SQLCMD_RECORDCNTEX(TABLE_NAME,DONDITION) "SELECT COUNT(*) AS Cnt FROM " TABLE_NAME " WHERE " DONDITION 

//将表中的rowid自增序列归零，下一次插入记录的行号从当前设置的0+1开始,注意：seq的值不能小于当前最大的rowid
#define	SQLCMD_CLEAR_TABLE_ROWID(TABLE_NAME)		"UPDATE sqlite_sequence SET seq=0 WHERE name='" TABLE_NAME "';" 

//查询表字段等信息
/***************
 * 判断 pk 列是否为1，name即为主键列名
***************/
#define SQLCMD_TABLEINFO(TABLE_NAME)	"PRAGMA table_info(" TABLE_NAME ");" 


//重命名表
#define SQLCMD_RENAME_TABLE(OLD_NAME,NEW_NAME)	"ALTER TABLE " OLD_NAME " RENAME TO " NEW_NAME 
// 增加表中字段
#define SQLCMD_ADD_COLUMN(TABLE_NAME,TABLE_FIELD)	"ALTER TABLE " TABLE_NAME " ADD COLUMN " TABLE_FIELD 
// 创建索引
#define SQLCMD_CREATE_IDX(TABLE_NAME,IDX_NAME,IDX_PARAM)	"CREATE INDEX IF NOT EXISTS " IDX_NAME " ON " TABLE_NAME " (" IDX_PARAM ") ;" 
//#define SQLCMD_CREATE_IDX(TABLE_NAME,IDX_NAME,IDX_PARAM)	"CREATE INDEX " IDX_NAME " ON " TABLE_NAME " (" IDX_PARAM ") ;" 
// 查询索引是否存在
#define SQLCMD_IDX_EXIST(IDX_NAME)	"SELECT COUNT(*) AS Cnt FROM sqlite_master WHERE type='index' AND name='" IDX_NAME "'" 
// 删除索引
#define SQLCMD_DROP_IDX(IDX_NAME)	"DROP INDEX " IDX_NAME 


#endif

编译示例程序

make type=4

运行示例程序并检查创建结果
在这里插入图片描述

插入记录

数据表创建成功，接下来插入一些数据；测试代码如下：

#include "./samples_insert.hpp"
#include "sqlite/sqlite.h"

int samples_insert (int argc, char** argv)
{
	if (argc < 2) {
		printf ("请输入一个参数以指定数据库文件名！\n");
		return -1;
	}
	sql::sqlite db(argv[1]);
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(1, '张三', 18, '珠海', 2500.0);");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '李四', 21, '广州', 6800.0);");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '啊五', 32, '深圳', 16500.0);");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '小哀', 23, '珠海', 13000.0);");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '刘五', 18, '深圳', 4500.0);");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '柯南', 43, '广州', 20500.0);");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '苏周', 51, '广州', 22500.0);");
	db.exec ("INSERT INTO employee(ID,NAME,AGE,ADDRESS,SALARY) VALUES(NULL, '啊二', 21, '珠海', 12500.0);");
	return 0;
}

编译并运行检查

linux@ubuntu:~/project/sqlite3$ ./exec test.db
linux@ubuntu:~/project/sqlite3$ ls
build  cmdexec  exec  executable  inc  lib  Makefile  mkfile  README.md  samples  src  test.db
linux@ubuntu:~/project/sqlite3$ ./cmdexec 1 test.db 
wsqlite version:V0.0.0.6
>select * from employee;
ID = 1
NAME = 张三
AGE = 18
ADDRESS = 珠海
SALARY = 2500.0

ID = 2
NAME = 李四
AGE = 21
ADDRESS = 广州
SALARY = 6800.0

ID = 3
NAME = 啊五
AGE = 32
ADDRESS = 深圳
SALARY = 16500.0

ID = 4
NAME = 小哀
AGE = 23
ADDRESS = 珠海
SALARY = 13000.0

ID = 5
NAME = 刘五
AGE = 18
ADDRESS = 深圳
SALARY = 4500.0

ID = 6
NAME = 柯南
AGE = 43
ADDRESS = 广州
SALARY = 20500.0

ID = 7
NAME = 苏周
AGE = 51
ADDRESS = 广州
SALARY = 22500.0

ID = 8
NAME = 啊二
AGE = 21
ADDRESS = 珠海
SALARY = 12500.0

耗时：0 ms,377 us
>exit
linux@ubuntu:~/project/sqlite3$

插入同名记录

插入成功，接下来是测试插入重名记录；
在这里插入图片描述
插入失败，说明name的唯一性限制正常起作用。

查询记录

接下来测试查询接口。

#include "./samples_query.hpp"
#include "sqlite/sqlite.h"


int samples_query (int argc, char** argv)
{
	if (argc < 2) {
		printf ("请输入一个参数以指定数据库文件名！\n");
		return -1;
	}
	sql::sqlite db(argv[1]);
	sql::query* query = NULL;
	query = db.query ("select * from employee;");
	if (query == NULL) {
		printf ("查询失败或未查询到数据！\n");
		return -1;
	}
	if (query->size () <= 0) {
		delete query;
		printf ("未查询到数据！\n");
		return -1;
	}
	printf ("总共查询到%d条记录，记录如下：\n", query->size ());
	query->begin ();
	do {
		printf ("ID:%ld, name:%s, age:%ld, address:%s, salary:%f\n"
		, query->get_number ("ID", 0)
		, query->get_string ("name", "")
		, query->get_number ("age", 0)
		, query->get_string ("address", "")
		, query->get_float ("salary", 0)
		);
	} while (query->next () > 0);
	delete query;
	return 0;
}

linux@ubuntu:~/project/sqlite3$ make type=4
-e g++ 	 	 samples/samples_query.cpp
-e ---------------------------[create exec]-[g++]----------------------------------
-e   build/samples/samples_query.obj
  build/samples/samples_insert.obj
  build/samples/samples_create_table.obj
  build/samples/main.obj

linux@ubuntu:~/project/sqlite3$ ./exec test.db
总共查询到8条记录，记录如下：
ID:1, name:张三, age:18, address:珠海, salary:2500.000000
ID:2, name:李四, age:21, address:广州, salary:6800.000000
ID:3, name:啊五, age:32, address:深圳, salary:16500.000000
ID:4, name:小哀, age:23, address:珠海, salary:13000.000000
ID:5, name:刘五, age:18, address:深圳, salary:4500.000000
ID:6, name:柯南, age:43, address:广州, salary:20500.000000
ID:7, name:苏周, age:51, address:广州, salary:22500.000000
ID:8, name:啊二, age:21, address:珠海, salary:12500.000000

pioneer_t

关注

8
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
sqlite数据库方法类使用说明-创建数据表、查询数据表、插入记录

/SQL 语句仅供参考//创建表# ifndef __SELDOM_SQLITE_SQLCMD_H # define __SELDOM_SQLITE_SQLCMD_H //SQL 语句仅供参考 //创建表 # define SQLCMD_CREATE_TABLE(TABLE_NAME , MEMBER) "CREATE TABLE IF NOT EXISTS " TABLE_NAME " (" MEMBER ");
复制链接

扫一扫