#include"MYSQL_library.h"
#include<iomanip>
//进行初始化，设置编码字符,连接操作
My_library::My_library() {
ip = "127.0.0.1"; username = "root"; password = "4567"; dbname = "itcase"; port = 3306;
conn = mysql_init(nullptr); //初始化
res = nullptr;
mysql_options(conn, MYSQL_SET_CHARSET_NAME, "gbk"); //设置编码
if (!mysql_real_connect(conn,ip.c_str(),username.c_str(),password.c_str(),dbname.c_str(),port,nullptr,0)) //连接
{
cout << "连接出错:" << mysql_error(conn) << endl;
return;
}
else
{
cout << "连接成功！" << endl;
}
}
void My_library::watch_book()
{
if (mysql_query(conn, "select * from library")) {
cout << "查询出错:" << mysql_error(conn) << endl;
return;
}
cout << "查询成功：" << endl;
res = mysql_store_result(conn); //获取结果集
int cols = mysql_num_fields(res); // 计算结果集中，列的个数
while (row = mysql_fetch_row(res)) {
for (int i = 0; i < cols; ++i) {
cout << left << setw(18) << row[i];
}
cout << endl;
}
//释放结果集合mysql句柄
mysql_free_result(res);
return;
}
bool My_library::borrow_book() {
string name;
cout << "Please input the book name : " << endl;
cin >> name;
//检查书籍是否存在
string sql = "select surplus_num from library where name = '" + name + "'";
if (mysql_query(conn, sql.c_str())) {
cerr << "Error: " << mysql_error(conn) << endl;
return false;
}
res = mysql_store_result(conn);
row = mysql_fetch_row(res); //前结果集中的一行数据作为一个字符串数组返回
char ch[1][1] = { '0' };
if (mysql_num_rows(res) <= 0) {
cerr << "Error: the book does not exist!" << endl;
mysql_free_result(res);
return false;
}
else if (*row[0] == *ch[0]) {
cout << "书本已无剩余！" << endl;
return false;
}
// 获取数量并且在原来基础上减1
//row为无效时不可以输出
//cout << row[0] << endl;
int temp = atoi(row[0]) - 1;
//cout << temp << endl;
sql = "update library set surplus_num =" + to_string(temp) + " where name ='" + name+"'";
if (mysql_query(conn, sql.c_str())) {
cout << mysql_error(conn) << endl;
return false;
}
printf("借书成功: %s\n", name.c_str());
return true;
}
bool My_library::return_book()
{
int id;
cout << "请输入还书的id号:" << endl;
cin >> id;
char sql[256] = { 0 };
sprintf(sql, "select surplus_num from library where number = %d", id);
if (mysql_query(conn, sql)) {
cout<<"id号出错:" << mysql_error(conn) << endl;
return false;
}
//没有这本书
res = mysql_store_result(conn);
if (mysql_num_rows(res) <= 0) {
cerr << "Error: the book does not exist!" << endl;
mysql_free_result(res);
return false;
}
// 获取数量并且在原来基础上加1
row = mysql_fetch_row(res);
//cout << row[0] << endl;
int temp = atoi(row[0]) + 1;
//cout << temp << endl;
//string sql2 = "update library set surplus_num = " + temp + " where number=" + id;
sprintf(sql, "update library set surplus_num = %d where number=%d",temp, id);
if (mysql_query(conn, sql)) {
cout << mysql_error(conn) << endl;
return false;
}
return true;
}
void My_library::public_interface(int pw)
{
del_book();
}
bool My_library::add_book()
{
int number, surplus_num;
string name, author, versions;
char sql[256];
cout << "Please input number, name, author, versions, surplus_num:" << endl;
cin >> number >> name >> author >> versions >> surplus_num;
sprintf(sql, "insert into library(number, name, author, versions, surplus_num) values(%d,'%s','%s','%s',%d)", number, name.c_str(), author.c_str(), versions.c_str(), surplus_num);
if (mysql_query(conn, sql)) {
cout << "添加书籍失败:" << mysql_error(conn) << endl;
return false;
}
return true;
}
bool My_library::del_book() {
string id;
cout << "请输入要删除的书id号:" << endl;
cin >> id;
string sql = "delete from library where number = " + id;
if (mysql_query(conn, sql.c_str())) {
cout << "删除书籍失败:" << mysql_error(conn) << endl;
return false;
}
return true;
}
//释放内存连接
My_library::~My_library() {
if (conn != nullptr) {
mysql_close(conn);
}
}
