C ++ 面向对象简单写sqlite3 数据库

冷不防 2022-08-20 12:16 172阅读 0赞

<pre name="code" class="cpp">#define  _CRT_SECURE_NO_WARNINGS
    #include<iostream>
    #include <conio.h>
    #include <windows.h>
    #include <cstdlib>
    #include <string>
    using namespace std;
    extern "C"
    {
         #include "sqlite3.h"
    };
    
    class stds
    {
    public:
    	stds() : sql("create table if not exists inof(id int,m_name char(255),results decimal(5,2),unique(id))"), count(0), db(0), stmt(0), erroe(0), rs(0){}
    
    	bool deleteinof(int num)
    	{
    		char temp[255] = { 0 };
    		sprintf(temp, "inof\\student%d.db",num);
    		if (init(temp))
    		{
    			memset(temp, 0, 255);
    			sprintf(temp, "delete from inof");
    			if (SQLITE_OK == sqlite3_exec(db, temp, 0,  0, &erroe))
    			{
    				sqlite3_close(db);
    				return true;
    			}
    			else
    			{
    				cout << erroe << endl;
    				sqlite3_free(erroe);
    				erroe = 0;
    			}
    		}
    		else
    			cout << "打开失败"<<endl;
    		return false;
    	}
    	bool ADDstuden(int num)
    	{
         
    		char temp[255] = { 0 };
    		sprintf(temp, "inof\\student%d.db", num);
    		if (init(temp))
    		{
    			sqlite3_close(db);
    			return true;
    		}
    		return false;
    	}
    	bool insert(int num)
    	{
    		char temp[255] = { 0 };
    		sprintf(temp, "inof\\student%d.db", num);
    		if (init(temp))
    		{
    			cout << "请输入学生的编号:(学号为唯一)" << endl;
    			cin >> st.id;
    			fflush(stdin);
    			cout << "请输入姓名:" << endl;
    			cin.getline(st.name, 128);
    			cout << "请输入成绩:" << endl;
    			cin >> st.results;
    			char temp[255] = { 0 };
    			sprintf(temp, "insert into inof(id,m_name,results)values(%d,' %s ',%f) ", st.id, st.name, st.results);
    			if (SQLITE_OK == sqlite3_exec(db, temp, 0, 0, &erroe))
    			{
    				cout << "添加学生OK" << endl;
    				sqlite3_close(db);
    				return true;
    			}
    			else
    			{
    				cout << erroe << endl;
    				sqlite3_free(erroe);
    				erroe = 0;
    				sqlite3_close(db);
    				cout << "添加学生失败或学号重复，请查看一下学生统计" << endl;
    				return false;
    			}
    			sqlite3_close(db);
    			return true;
    		}
    		return false;
    	}
    	
    	bool select_shoew(int num)
    	{
    		char temp[255] = { 0 };
    		sprintf(temp, "inof\\student%d.db", num);
    		if (init(temp))
    		{
    			while (1)
    			{
    				cout << "【1】查看统计【2】修改【3】删除单个学生【4】查看学生人数总和" << endl;
    				fflush(stdin);
    				cin >> rs;
    				switch (rs)
    				{
    				case 1:
    					memset(temp, 0, 255);
    					sprintf(temp, "select *from inof");
    					show_ALL(temp);
    					break;
    				case 2:
    					update_one(temp, 255);
    					break;
    				case 3:
    					delete_one(temp, 255);
    					break;
    				case 4:
    					sprintf(temp, "select *from inof");
    					cout << "班级人数为：" << AllSUm(temp) << endl;
    				default:
    					break;
    			     }
    				
    					sqlite3_close(db);
    					return true;
    					break;
    			
    		
    			}
    		}
    		return false;
    	}
    	~stds(){ sqlite3_close(db); }
    private:
    	int AllSUm(const char *temp)
    	{
    		if (SQLITE_OK == sqlite3_prepare_v2(db, temp, -1, &stmt,0))
    		{
    			if ((count = sqlite3_column_count(stmt)))
    			{
    				if (SQLITE_OK == sqlite3_exec(db, temp, 0, 0, &erroe))
    				{
    					sqlite3_finalize(stmt);
    					stmt = 0;
    					return count;
    				}
    				else
    				{
    					cout << erroe << endl;
    					sqlite3_free(erroe);
    					erroe = 0;
    					return 0;
    				}
    					
    			}
    			else
    			{
    				cout << "空数据" << endl;
    				sqlite3_finalize(stmt);
    				stmt = 0;
    				return 0;
    			}
    				
    
    		}
    		else
    		{
    		
    			return 0;
    		}
    	}
    	bool init(const char *temp)
    	{
    		if (SQLITE_OK == sqlite3_open(temp, &db))
    		{
    			
    			if (SQLITE_OK != sqlite3_exec(db, sql.c_str(), 0, 0, &erroe))
    			{
    				cout << "创建学生数据库失败" << endl;
    				return false;
    			}
    			return true;
    		}
    		else
    		{
    			return false;
    		}
    	}
    	void show_ALL(const char *temp)
    	{
    		if (SQLITE_OK == sqlite3_prepare_v2(db, temp, -1, &stmt, 0))
    		{
    			while (SQLITE_ROW == sqlite3_step(stmt))
    			{
    				cout << sqlite3_column_int(stmt, 0) << " " << sqlite3_column_text(stmt, 1) << " "<< sqlite3_column_double(stmt, 2) << endl;
    			}
    			sqlite3_finalize(stmt);
    			stmt = 0;
    		}
    	}
    	void update_one( char *temp, int len)
    	{
    		fflush(stdin);
    		system("cls");
    		cout << "先输入你的要输入的编号" << endl;
    		cin >> rs;
    		fflush(stdin);
    		memset(temp, 0, len);
    		cout << "请输入你的要修改的内容" << endl;
    		cout << "【1】学号【2】名字【3】成绩【4】全部" << endl;
    		switch (_getch())
    		{
    		case '1':
    			cout << "请输入修改的学号" << endl;
    			cin >> st.id;
    			sprintf(temp,"select *from inof order by id");
    			if (SQLITE_OK == sqlite3_prepare_v2(db, temp, -1, &stmt, 0))
    				{
    
    					if ((count == sqlite3_column_count(stmt)))
    					{
    						memset(temp, 0, len);
    						sprintf(temp, "update inof set id=%d where id=%d", st.id, rs);
    						if (SQLITE_OK == sqlite3_exec(db, temp, 0, 0, &erroe))
    						{
    							cout << "修改成功" << endl;
    							sqlite3_finalize(stmt);
    							stmt = 0;
    						}
    						else
    						{
    
    							cout << erroe << endl;
    							sqlite3_free(erroe);
    							erroe = 0;
    							sqlite3_finalize(stmt);
    							stmt = 0;
    						}
    							
    					}
    					else
    					{
    						cout << "空数据" << endl;
    						sqlite3_finalize(stmt);
    						stmt = 0;
    					}
    						
    			
    			}
    			else
    			{
    				sqlite3_finalize(stmt);
    				stmt = 0;
    				cout << "打开失败" << endl;
    			}
    			break;
    		case '2':
    			cout << "请输入修改的名字:" << endl;
    			cin.get(st.name, 128);
    			sprintf(temp, (char*)"update inof set m_name=' %s '  where id=%d", st.name, rs);
    			if (SQLITE_OK == sqlite3_exec(db, temp, 0, 0, &erroe))
    			{
    				cout << "修改成功" << endl;
    			}
    			else
    			{
    				cout << erroe << endl;
    				sqlite3_free(erroe);
    				erroe = 0;
    			}
    			break;
    		case '3':
    			cout << "请输入修改的成绩" << endl;
    			cin >> st.results;
    			sprintf(temp, (char*)"update inof set results=%f  where id=%d", st.results, rs);
    			if (SQLITE_OK == sqlite3_exec(db, temp, 0, 0, &erroe))
    			{
    				cout << "修改成功" << endl;
    			}
    			else
    			{
    				cout << erroe << endl;
    				sqlite3_free(erroe);
    				erroe = 0;
    			}
    			break;
    		case '4':
    			cout << "请输入修改的学号" << endl;
    			cin >> st.id;
    			fflush(stdin);
    			cout << "请输入修改的名字:" << endl;
    			cin.get(st.name, 128);
    			cout << "请输入修改的成绩" << endl;
    			cin >> st.results;
    			sprintf(temp, (char*)"update inof set id=%d, m_name =' %s ' , results =%f  where id=%d", st.id,st.name,st.results, rs);
    			if (SQLITE_OK == sqlite3_exec(db, temp, 0, 0, &erroe))
    			{
    				cout << "修改成功" << endl;
    			}
    			else
    			{
    				cout << erroe << endl;
    				sqlite3_free(erroe);
    				erroe = 0;
    			}
    			break;
    		default:
    			cout << "输入错误,返回菜单重现选择!!" << endl;
    			break;
    		}
    
    	}
    	void delete_one(char *temp,int len)
    	{
    		cout << "请输入要删除的学生编号:" << endl;
    		fflush(stdin);
    		cin >> rs;
    		memset(temp, 0, len);
    		sprintf(temp, "delete from inof where id=%d", rs);
    		if (SQLITE_OK == sqlite3_exec(db, temp, 0, 0, &erroe))
    		{
    			cout << "删除成功" << endl;
    		}
    		else
    		{
    			cout << erroe << endl;
    			sqlite3_free(erroe);
    			erroe = 0;
    		}
    	}
    	struct students
    	{
    		int id;
    		char name[255];
    		float results;
    	}st;
    	int rs;
    	sqlite3 *db;
    	sqlite3_stmt *stmt;
    	//const char *txt;
    	char *erroe;
    	string sql;
    	int count;
    };
    class re
    {
    public:
    	re() :s(0),rs(0), txt("text.db"), db(0), erroe(0), count(0), stmt(0), sql("create table if not exists inof(id int,name char(255),expit char(255),unique(id));")
    	{
    		if (system("cd inof"))
    		{
    			system("cls");
    			system("mkdir inof");
    		}
    		else
    		{
    			system("cd..");
    		}
    	}
    	void Run();
    	~re(){ sqlite3_close(db); }
    private:
    	void CreateTable_Name();
    	void Select_from_stds()
    	{
    		while (1)
    		{
    			if (!init())
    			{
    				while (1)
    				{
    
    					system("cls");
    					show_sqlite();
    					cout << "青选择查看的班级情况,请输入编号" << endl;
    					fflush(stdin);
    					cin >> rs;
    					s->select_shoew(rs);
    					cout << "是否继续【0】退出【1】继续:" << endl;
    					if ('0' == _getch())
    					{
    						sqlite3_close(db);
    						db = NULL;
    						return;
    					}
    				}
    			}
    			else
    			{
    				cout << "打开失败<<endl";
    				break;
    			}
    			
    		}
    	}
    	void update_stds()
    	{
    		if (init())
    		{
    			cout << "错误" << endl;
    		}
    		else
    		{
    			while (1)
    			{
    				system("cls");
    				fflush(stdin);
    				show_sqlite();
    				cout << "请输入你的修改数据库名字的的编号:" << endl;
    				cin >> rs;
    				char temp[255] = { 0 };
    				sprintf(temp, (char*)"select *from inof where id=%d", rs);
    				if ( SQLITE_OK==(sqlite3_prepare_v2(db, temp, -1, &stmt, 0)))
    				{
    					while (SQLITE_ROW == sqlite3_step(stmt))
    					{
    						sql = (char*)sqlite3_column_text(stmt, 1);
    						sql += ".db\n";
    					}
    					sqlite3_finalize(stmt);
    					stmt = 0;
    					fflush(stdin);
    					cout << "请输入你要修改的数据库的名字" << endl;
    					cin.getline(temp, 255);
    					{
    						string stmp = temp;
    						fflush(stdin);
    						memset(temp, 0, 255);
    						sprintf(temp, (char*)"update inof set name=' %s '  where id=%d", stmp.c_str(), rs);
    						if (SQLITE_OK == sqlite3_exec(db, temp, 0, 0, &erroe))
    						{
    							cout << "修改成功" << endl;
    						}
    						else
    						{
    							cout << "失败" << erroe;
    							sqlite3_free(erroe);
    							erroe = 0;
    						}
    					}
    					fflush(stdin);
    					cout << "是否继续【0】退出【任意】继续" << endl;
    					if ('0' == _getch())
    					{
    						break;
    					}
    
    				}
    				
    				
    			}
    			
    			
    		}
    		sqlite3_close(db);
    		db = NULL;
    		return;
    	}
    	void ALLdelete()
    	{
    		if (init())
    		{
    			cout << "失败\n";
    			return;
    		}
    		else
    		{
    			sql = "delete from inof";
    			if ((rs = sqlite3_exec(db, sql.c_str(), 0, 0, &erroe)) != SQLITE_OK)
    			{
    				cout << "删除失败,或许程序没有退出" << erroe << endl;
    				cin.get();
    				sqlite3_free(erroe);
    				erroe = 0;
    			}
    			else
    			{
    				cout << "删除成功\n";
    				if (system("rd/s/q inof"))
    				{
    					cout << "请关闭程序手动删除:" << "有误未知错误引起"<<"请手动删除"<<endl;
    					cin.get();
    					cin.get();
    				}
    				
    			}
    				
    		}
    		sqlite3_close(db);
    		db = NULL;
    		return;
    	}
    	void Insert_from_stds()
    	{
    	
    			if (!init())
    			{
    				show_sqlite();
    				cout << "请选着添加那个班级的学生,请输入编号:" << endl;
    				cin >> rs;
    				while (1)
    				{
    					system("cls"); 
    					fflush(stdin);
    					cout << "书否需要添加学生" << endl;
    					s->insert(rs);
    					cout << "是否继续添加【0】退出【任意】继续" << endl;
    					if ('0' == _getch())
    					{
    						sqlite3_close(db);
    						db = NULL;
    						return;
    					}
    				}
    				
    			}
    			else
    			{
    				cout << "打开失败"<<endl;
    				return;
    			}
    
    
    			
    		
    	}
    	void Delete_from_stds()
    	{
    		bool quit = false;
    		char ch;
    		while (1)
    		{
    			system("cls");
    			fflush(stdin);
    			cout << "【0】全部清空【任意】单选删除【2】退出" << endl;
    			ch = _getch();
    			if ('0' ==ch ||'2'==ch)
    			{
    				if ('0' == ch)
    				{
    					ALLdelete();
    					continue;
    				}
    				else
    				{
    					if (quit == true)
    					{
    						sqlite3_close(db);
    						db = NULL;
    					}
    					return;
    				}
    			}
    			
    			fflush(stdin);
    		
    
    			cout << "请输入你删除的编号:" << endl;
    			fflush(stdin);
    			cin >> rs;
    			char temp[255] = {0};
    			sprintf(temp, (char*)"delete from inof where id=%d", rs);
    			
    		
    			
    				if (s->deleteinof(rs));
    				{
    					if (init()&&quit==false)
    					{
    						
    						cout << "错误" << endl;
    
    					}
    					else
    					{
    						
    						quit = true;
    						if ((rs = sqlite3_exec(db, temp, 0, 0, &erroe)) != SQLITE_OK)
    						{
    							cout << erroe << endl;
    							sqlite3_free(erroe);
    							erroe = 0;
    						}
    						else
    						{
    							cout << "删除成功" << endl;
    
    						}
    					}
    					
    				}
    				
    			}
    		
    	
    		return;
    	}
    	void show_sqlite()
    	{
    		
    		char temp[255] = { 0 };
    		sprintf(temp,(char*)"select *from inof ");
    		stmt = 0;
    		if (SQLITE_OK == sqlite3_prepare_v2(db, (char*)temp, -1, &stmt, NULL))
    		{
    			count = sqlite3_column_count(stmt);
    			if (count != 0)
    			{
    					while (SQLITE_ROW == sqlite3_step(stmt))
    					{
    						cout << sqlite3_column_int(stmt, 0) << sqlite3_column_text(stmt, 1) << sqlite3_column_text(stmt, 2) << endl;
    					}
    				
    				sqlite3_finalize(stmt);
    				stmt = NULL;
    			}
    			else
    			{
    				cout << "数据为空,请添加数据:" << endl;
    				sqlite3_finalize(stmt);
    				stmt = 0;
    			}
    		}
    		else
    		{
    			cout << "显示冒失有误" << endl;
    		//	sqlite3_finalize(stmt);
    		//	stmt = 0;
    		}
    		
    	}
    	bool init()
    	{
    		if ((rs = sqlite3_open(txt, &db) != SQLITE_OK))
    		{
    			cout << "数据文件未在或打开失败" << sqlite3_errmsg(db) << endl;
    			return 1;
    		}
    		char temp[255] = { "create table if not exists inof(id int,name char(255),expit char(255),unique(id));" };
    		rs = sqlite3_exec(db, temp, 0, 0, &erroe);
    		if (rs != SQLITE_OK)
    		{
    			cout << "创建失败:" << erroe << endl;
    			sqlite3_free(erroe);
    			erroe = NULL;
    			return 2;
    		}
    		return 0;
    	}
    	static int callback(void *data, int count, char **agr, char **argv)
    	{
    		int i;
    		for (i = 0; i < count; i++)
    		{
    			printf("$s = %s", argv[i], agr[i] ? argv[i] : "NULL");
    		}
    		cout << endl;
    		return 0;
    	}
    	stds *s;
    	int rs;
    	sqlite3 *db;
    	sqlite3_stmt *stmt;
    	const char *txt;
    	char *erroe;
    	string sql;
    	int count;
    };
    
    void re::CreateTable_Name()
    {
    	
    	if (init())
    	{
    		cout << "文件打开失败" << endl;
    	}
    	else
    	{
    		
    		char temp[128];
    		
    		while (1)
    		{
    			cout << "书否显示数据【0】看【任意】否" << endl;
    			if ('0' == _getch())
    			{
    				show_sqlite();
    				
    			}
    			cout << "请输入班级数据的编号:" << endl;
    			cin >> rs;
    			fflush(stdin);
    			cout << "请输入创建班级数据的名称:" << endl;
    			sql.clear();
    			cin >> sql;
    			fflush(stdin);
    			memset(temp, 0, 128);
    			cout << "是否备注:【0】是【任意】否" << endl;
    			if (('0'==_getch()))
    			{
    				cout << "开始写注释:" << endl;
    				char exip[255] = { 0 };
    				cin.getline(exip, 255);
    sprintf(temp,(char*) "insert into inof(id,name,expit)values(%d,' %s ',' %s ' )", rs, (char*)sql.c_str(),exip);
    			}
    			else
    			    sprintf((char*)temp, (char*)"insert into inof(id,name,expit) values(%d,' %s ',' ')", rs, (char*)sql.c_str());
    			if ((SQLITE_OK!= sqlite3_exec(db, (const char*)temp, 0, 0, &erroe)))
    			{
    				cout << "添加失败或编号重复,请重现添加:" << erroe << endl;
    				sqlite3_free(erroe);
    				erroe = NULL;
    			}
    			else
    			{
    				cout << "插入成功" << endl;
    				s->ADDstuden(rs);
    				
    			}
    			fflush(stdin);
    			cout << "是否继续执行添加:【0】退出【任意】执行" << endl;
    			if ('0' == _getch())
    				break;
    			system("cls");
    		}
    	}
    	sqlite3_close(db);
    	db = NULL;
    	return;
    }
    void re::Run()
    {
    	int ICD;
    	bool quit = false;
    	s = new stds;
    	while (!quit)
    	{
    		cout << "【1】创建数据库【2】修改数据库【3】增加数据库【4】删除数据库【5】查看数据库【6】退出" << endl;
    		fflush(stdin);
    		cin >> ICD;
    		switch (ICD)
    		{
    		case 1:
    			CreateTable_Name();
    			break;
    		case 2:
    			update_stds();
    			break;
    		case 3:
    			Insert_from_stds();
    			break;
    		case 4:
    			Delete_from_stds();
    			break;
    		case 5:
    			Select_from_stds();
    			break;
    		case 6:
    			quit = true;
    			break;
    		default:
    			cout << "输入错误,重现输入" << endl;
    			break;
    		}
    		system("cls");
    	}
    	delete s;
    }
    
    void main()
    {
    	system("mode con cols=100 lines=20");
    	
    	re r;
    	r.Run();
    
    
    	system("pause");
    }