#include<iostream>
#include<string>
#include<cstring>
#include<stdlib.h>
#include<conio.h>
#include<fstream>
#include<Windows.h>
#include"sqlite3.h"
#pragma comment(lib, "sqlite3.lib")
using namespace std;
class Employee//定义职工的基类
{
protected:
int id;//工号
string name;//姓名
string sex;//性别
string degree;//等级
Employee* next;
public:
virtual int getid();//返回id
virtual void setid(int id);//给id赋值
virtual string getname();//返回name
virtual void setname(string name);//给name赋值
virtual string getsex();//返回性别
virtual void setsex(string sex);//给性别赋值
virtual string getdegree();//返回职位
virtual void setdegree(string degree);
Employee*& getnext();//返回next
};
class Link:public Employee
{
public:
Employee* head = new Employee;
void creatlink();
void add();//增加函数
void Delete();//删除函数
void change();//改函数
void find();//查询函数
void menu();//菜单
void show();//浏览函数
void payoff();//查询工资
void sort();//按照工号
};
class Sqlite3
{
private:
sqlite3* db;
int ret;
public:
bool DeleteData(string sql);//删除
bool UpdateData(string sql);//更新
void creattable();//创建表
void Open();//打开数据库
};
int Employee::getid()
{
return id;
}
void Employee::setid(int id)
{
this->id = id;
}
string Employee::getname()
{
return name;
}
void Employee::setname(string name)
{
this->name = name;
}
string Employee::getsex()
{
return sex;
}
void Employee::setsex(string sex)
{
this->sex = sex;
}
string Employee::getdegree()
{
return degree;
}
void Employee::setdegree(string degree)
{
this->degree = degree;
}
Employee*& Employee::getnext()
{
return next;
}
void Link::add()
{
while (1)
{
//增加数据
cout << "工号:";
cin >> id;
cout << "姓名:"; cin >> name;
cout << "性别:";
while (1)
{
cin >> sex;
if (sex != "男" && sex != "女")
{
cout << "输入错误,请输入男或女:" << endl;
}
else
{
break;
}
}
cout << "职位:(1.普通职工 2.销售人员 3. 经理)"<<endl;
while (1)
{
cin >> degree;
if (degree != "普通职工" && degree != "销售人员"&°ree!="经理")
{
cout << "输入错误,请输入普通职工 或销售人员 或经理" << endl;
}
else
{
break;
}
}
//1.打开数据库
sqlite3* ppdb = NULL;
int ret = sqlite3_open("mydb.db", &ppdb);
if (ret != SQLITE_OK)
{
cout << "打开失败!" << endl;
return;
}
char* error = NULL;
string sql;
char buffer[500];
sprintf_s(buffer, "insert into biao values(%d,'%s','%s','%s');", id, name.c_str(), sex.c_str(), degree.c_str());//把信息写入到数据库内
sql = buffer;
ret = sqlite3_exec(ppdb, sql.c_str(), NULL, NULL, &error);
if (ret != SQLITE_OK)
{
cout << "该职工已存在!" << endl;
}
else
{
Employee* p = head;
while (p->getnext() != NULL)
{
p = p->getnext();
}
p->getnext() = new Employee;
p = p->getnext();
p->setid(id);
p->setname(name);
p->setsex(sex);
p->setdegree(degree);
cout << "添加成功!" << endl;
}
sqlite3_free(error);
cout << "继续添加请按(1),退出请按(0)" << endl;
int b;
while (1)
{
cin >> b;
if (b != 1 && b != 0)
{
cout << "输入错误请重新输入:";
}
else if (b == 1)
{
break;
}
else if (b == 0)
{
return;
}
}
}
}
void Link::Delete()
{
Employee* p = head;
int a;
string sql;
char buffer[500];
Sqlite3 sq;
sqlite3* db=NULL;
if (sqlite3_open("mydb.db", &db) != SQLITE_OK)
{
cout << "文件打开失败!\n";
system("pause");
exit(0);
}
cout << "请输入要删除的职工工号:";
cin >> a;
int flag = 1;
while (p->getnext() != NULL)
{
if (p->getnext()->getid() == a)
{
p->getnext() = p->getnext()->getnext();
flag = 0;
sprintf_s(buffer, "delete from biao where id= %d", a);
sql = buffer;
bool x=sq.DeleteData(sql);
if (x == true)
{
cout << "删除成功!" << endl;
}
if (x == false)
{
cout << "删除失败!" << endl;
}
break;
}
p = p->getnext();
}
if (flag == 1)
{
cout << "未找到该职工删除失败\n";
return;
}
}
void Link::change()
{
Employee* q;
q = head;
int a;
sqlite3* db;
Sqlite3 sq;
cout << "请输入要改动的职工工号:";
cin >> a;
char buffer[500];
string sql;
q = q->getnext();
while (q != NULL)
{
if (q->getid() == a)
{
cout << "1.职工姓名" << endl;
cout << "2.职工性别" << endl;
cout << "3.职工等级" << endl;
cout << "请选择要改动的数据:" << endl;
int b;
cin >> b;
switch (b)
{
case 1:
{
cout << "请输入新的姓名" << endl;
cin >> name;
q->setname(name);
sprintf_s(buffer, "update biao set name='%s' where id=%d", name.c_str(), a);
sql = buffer;
bool x=sq.UpdateData(sql);
if (x == true)
{
cout << "修改成功!" << endl;
}
if (x == false)
{
cout << "修改失败!" << endl;
}
return;
}
break;
case 2:
{
cout << "请输入新的性别" << endl;
cin >> sex;
q->setsex(sex);
sprintf_s(buffer, "update biao set sex='%s' where id=%d", sex.c_str(), a);
sql = buffer;
sq.UpdateData(sql);
return;
}
break;
case 3:
{
cout << "请输入新的等级" << endl;
cout << "普通职工 或者 销售人员 或者 经理" << endl;
cin >> degree;
q->setdegree(degree);
sprintf_s(buffer, "update biao set degree='%s' where id=%d", degree.c_str(), a);
sql = buffer;
sq.UpdateData(sql);
return;
}
}
}
q = q->getnext();
}
}
void Link::find()
{
Employee* q= head;
q = head->getnext();
cout << "1.按工号查询 2.按姓名查询" << endl;
cout << "请输入要查询的方式:";
int a;
cin >> a;
if (a == 1)
{
cout << "请输入要查询的工号:";
int b;
cin >> b;
while (q != NULL)
{
if (q->getid() == b)
{
cout << "工号:" << q->getid() << " 姓名:" << q->getname() << " 性别:" << q->getsex() << " 职位:" << q->getdegree() << endl;
return;
}
q = q->getnext();
}
}
if (a == 2)
{
cout << "请输入要查询的姓名:";
int b;
cin >> b;
while (q != NULL)
{
if (q->getid() == b)
{
cout << "工号:" << q->getid() << " 姓名:" << q->getname() << " 性别:" << q->getsex() << " 职位:" << q->getdegree() << endl;
return;
}
q = q->getnext();
}
}
cout << "查无此人!" << endl;
}
void Link::show()
{
Employee* p = head;
while (p->getnext()!=NULL)
{
p = p->getnext();
cout << "工号:" << p->getid() << "\t姓名:" << p->getname() << "\t性别:" << p->getsex() << "\t职位:" << p->getdegree()<<endl;
}
}
void Link::payoff()
{
Employee* q;
q = head->getnext();
int a;
cout << "请输入要查询的职工工号:";
cin >> a;
while (q != NULL)
{
if (q->getid() == a)
{
cout << "职工工号:" << q->getid() << "\t职工姓名:" << q->getname() << "\t职工性别:" << q->getsex() << "\t职工等级:" << q->getdegree() << endl;
cout << "请输入工作的月数:";
int b;
cin >> b;
if (q->getdegree() == "普通职工")
{
cout << "工资为:" << 2000 * b << "元" << endl;
}
else if (q->getdegree() == "销售人员")
{
cout << "工资为:" << 3000 * b << "元" << endl;
}
else
{
cout << "工资为:" << 4000 * b << "元" << endl;
}
return;
}
q = q->getnext();
}
cout << "查无此人!" << endl;
}
void Link::creatlink()//读取数据库里的信息到链表
{
sqlite3* ppdb = NULL;
int ret = sqlite3_open("mydb.db", &ppdb);
if (ret != SQLITE_OK)
{
cout << "打开失败!" << endl;
return;
}
char* error;//保存错误
char** resultp; //二级指针取地址
int nRow; //定义一行
int nCol; //定义一列
int i;
int j;
char sql[256] = "select * from biao;";
int rec = sqlite3_get_table(ppdb, sql, &resultp, &nRow, &nCol, &error);
i = nCol;
Employee* p = head;
for (j = 0; j < nRow; j++)
{
int id = atoi(resultp[i++]);
string name = resultp[i++];
string sex = resultp[i++];
string degree =(resultp[i++]);
p ->getnext()= new Employee;
p = p->getnext();
p->setid(id);
p->setname(name);
p->setsex(sex);
p->setdegree(degree);
}
}
void Link::sort()
{
Employee* f, * p, * x, * y;
f = NULL;
while (f != head->getnext()->getnext())//外层判断少一次循环
{
for (p = head; p->getnext()->getnext() != f; p = p->getnext())
{
if (p->getnext()->getid() > p->getnext()->getnext()->getid())
{
x = p->getnext();//x和y首先指向这两个要交换的节点
y = p->getnext()->getnext();
p->getnext() = y;//交换三部曲
x->getnext() = y->getnext();
y->getnext() = x;
}
}
f = p->getnext();//把 f 往前移
}
p = head;
while (p->getnext() != NULL)
{
p = p->getnext();
cout << "职工工号:" << p->getid() << "\t职工姓名:" << p->getname() << "\t职工性别:" << p->getsex() << "\t职工等级:" << p->getdegree() << endl;
}
}
void Link::menu()
{
int a;
while (1)
{
cout << "\n";
cout << " 欢迎使用公司职工信息管理软件\n" << endl;
cout << " ----------------------------" << endl;
cout << " -------1.录入职工信息-------" << endl;
cout << " -------2.修改职工信息-------" << endl;
cout << " -------3.删除职工信息-------" << endl;
cout << " -------4.查询职工信息-------" << endl;
cout << " -------5.浏览职工信息-------" << endl;
cout << " -------6.查询工资信息-------" << endl;
cout << " -------7.按照工号排序-------" << endl;
cout << " ---------8.退出系统---------" << endl;
cout << " ----------------------------" << endl;
cout << "\n\n请选择:";
while (1)
{
cin >> a;
if (a < 1 || a > 8)
{
cout << "输入错误请重新输入" << endl;
}
else
{
break;
}
}
switch (a)
{
case 1:
add();
system("pause");
system("cls");
break;
case 2:
change();
system("pause");
system("cls");
break;
case 3:
Delete();
system("pause");
system("cls");
break;
case 4:
find();
system("pause");
system("cls");
break;
case 5:
show();
system("pause");
system("cls");
break;
case 6:
payoff();
system("pause");
system("cls");
break;
case 7:
sort();
system("pause");
system("cls");
break;
case 8:
{
int b = 3;
cout << " 秒后关闭程序";
while (--b >= 1)
{
printf("\r%d", b);
printf("\b");
Sleep(1000);
}
cout << "感谢您的使用!再见\n";
exit(0);
break;
}
default:
cout<<"输入无效!";
system("pause");
system("cls");
break;
}
}
}
bool Sqlite3::DeleteData(string sql)
{
sqlite3* db = NULL;
if (sqlite3_open("mydb.db", &db) != SQLITE_OK)
{
cout << "文件打开失败!\n";
system("pause");
exit(0);
}
int ret = sqlite3_exec(db, sql.c_str(), NULL, NULL, NULL);
if (ret != SQLITE_OK)
{
return false;
}
else
{
return true;
}
}
bool Sqlite3::UpdateData(string sql)
{
if (sqlite3_open("mydb.db", &db) != SQLITE_OK)
{
cout << "文件打开失败!\n";
system("pause");
exit(0);
}
int ret = sqlite3_exec(db, sql.c_str(), NULL, NULL, NULL);
if (ret != SQLITE_OK)
{
return false;
}
else
{
return true;
}
}
void Sqlite3::Open()
{
if (sqlite3_open("mydb.db", &db) != SQLITE_OK)
{
cout << "文件打开失败!\n";
system("pause");
exit(0);
}
}
void Sqlite3::creattable()
{
const char* sql = "create table if not exists biao(id int unique,name char[20],sex char [10],degree char[20]) ";
char* error = NULL;//保存错误
ret = sqlite3_exec(db, sql, NULL, NULL, &error);
if (ret != SQLITE_OK)
{
cout << error << endl;
}
sqlite3_free(error);
}
int main()
{
Sqlite3 sq;
sq.Open();
sq.creattable();
Link test;
test.creatlink();
test.menu();
return 0;
}
C++实现sqlite3数据库增删改查和信息读取
最新推荐文章于 2023-10-25 18:30:46 发布