最近在学习数据库的内容，起先是在windows下用mysql c++ connector进行编程，之所以选用c++而不是c的api，主要是考虑到c++ connector是按照JDBC的api进行实现的，使用比较方便。废话不多说，直接上代码。代码主要是对常用操作的测试与封装，包括DDL、DQL、DML，以及文本、图像的读写方法
/*
* SQLWork.cpp
*
* Created on: Dec 29, 2016
* Author: tla001
*/ #include "SQLWork.h" SQLWork::SQLWork(string url,string user,string passwd) {
// TODO Auto-generated constructor stub
turl=url;
tuser=user;
tpasswd=passwd;
}
SQLWork::~SQLWork() {
// TODO Auto-generated destructor stub
if(res)
delete res;
if(stmt)
delete stmt;
if(con)
delete con;
} int SQLWork::doConnect(){
try{
dirver = get_driver_instance(); //连接数据库
con = dirver->connect(turl, tuser, tpasswd);
con->setClientOption("characterSetResults", "utf8");
}catch(SQLException &e){
cout << "ERROR: " << e.what();
cout << " (MySQL error code: " << e.getErrorCode();
cout << ", SQLState: " << e.getSQLState() << ")" << endl; if (e.getErrorCode() == ) {
/*
Error: 1047 SQLSTATE: 08S01 (ER_UNKNOWN_COM_ERROR)
Message: Unknown command
*/
cout << "\nYour server does not seem to support Prepared Statements at all. ";
cout << "Perhaps MYSQL < 4.1?" << endl;
} return EXIT_FAILURE;
} catch (runtime_error &e) {
cout << "ERROR: " << e.what() << endl; return EXIT_FAILURE;
} return EXIT_SUCCESS;
}
int SQLWork::changeCharacter(string chars){
try{
con->setClientOption("characterSetResults", chars);
}catch(SQLException &e){
cout << "ERROR: " << e.what();
cout << " (MySQL error code: " << e.getErrorCode();
cout << ", SQLState: " << e.getSQLState() << ")" << endl; if (e.getErrorCode() == ) {
/*
Error: 1047 SQLSTATE: 08S01 (ER_UNKNOWN_COM_ERROR)
Message: Unknown command
*/
cout << "\nYour server does not seem to support Prepared Statements at all. ";
cout << "Perhaps MYSQL < 4.1?" << endl;
} return EXIT_FAILURE;
} catch (runtime_error &e) {
cout << "ERROR: " << e.what() << endl; return EXIT_FAILURE;
} return EXIT_SUCCESS;
}
int SQLWork::chooseDatabase(string db){
try{
con->setSchema(db);
stmt = con->createStatement(); //从表中获取所有信息
}catch(SQLException &e){
cout << "ERROR: " << e.what();
cout << " (MySQL error code: " << e.getErrorCode();
cout << ", SQLState: " << e.getSQLState() << ")" << endl; if (e.getErrorCode() == ) {
/*
Error: 1047 SQLSTATE: 08S01 (ER_UNKNOWN_COM_ERROR)
Message: Unknown command
*/
cout << "\nYour server does not seem to support Prepared Statements at all. ";
cout << "Perhaps MYSQL < 4.1?" << endl;
} return EXIT_FAILURE;
} catch (runtime_error &e) {
cout << "ERROR: " << e.what() << endl; return EXIT_FAILURE;
} return EXIT_SUCCESS;
} int SQLWork::doWork(){
try{
/*
* 执行具体工作
*/
//buildTest();
//operateTable();
//operateData();
//operateDataPre();
//queryData();
queryDataPre(); }catch(SQLException &e){
cout << "ERROR: " << e.what();
cout << " (MySQL error code: " << e.getErrorCode();
cout << ", SQLState: " << e.getSQLState() << ")" << endl; if (e.getErrorCode() == ) {
/*
Error: 1047 SQLSTATE: 08S01 (ER_UNKNOWN_COM_ERROR)
Message: Unknown command
*/
cout << "\nYour server does not seem to support Prepared Statements at all. ";
cout << "Perhaps MYSQL < 4.1?" << endl;
} return EXIT_FAILURE;
} catch (runtime_error &e) {
cout << "ERROR: " << e.what() << endl; return EXIT_FAILURE;
} return EXIT_SUCCESS;
} void SQLWork::buildTest(){
res = stmt->executeQuery("SELECT * from student"); //循环遍历
while (res->next()) { //输出，id，name，age字段的信息
cout << res->getInt("id")
<< " | " << res->getString()
<< " | " << res->getInt("tage")
<< " | " << res->getString("thome")
<< endl;
} //清理
} /*
* 执行DDL语句Data Definition Language 数据定义语言
*/
void SQLWork::operateTable(){
//string sqlOp="create table contact(id int primary key auto_increment,name varchar(20),gender varchar(2))";
//string sqlOp="drop table contact";
//string sqlOp="ALTER TABLE contact ADD home VARCHAR(30)";
//string sqlOp="ALTER TABLE contact drop home";
//string sqlOp="ALTER TABLE contact MODIFY gender VARCHAR(4)";
//string sqlOp="ALTER TABLE contact CHANGE thome home VARCHAR(20);";
string sqlOp="CREATE TABLE longtest(id INT PRIMARY KEY AUTO_INCREMENT,content LONGTEXT,img LONGBLOB)"; int count=stmt->executeUpdate(sqlOp);
cout<<"count "<<count<<endl;
} /*
* DML（Data Manipulation Language 数据操控语言）
*/
void SQLWork::operateData(){
//string sqlOp="INSERT INTO contact VALUES(2,'李','男','shenyang')";
//string sqlOp="insert contact(name,gender) values('王二丫','女')";
//string sqlOp="DELETE FROM contact WHERE id>2 and id<5";
string sqlOp="UPDATE contact SET name='韩' WHERE id=2;";
//string sqlOp="";
//string sqlOp=""; int count=stmt->executeUpdate(sqlOp);
cout<<"count "<<count<<endl;
}
void SQLWork::operateDataPre(){
//预编译插入
// string sqlOp="insert contact(name,gender) values(?,?)";
// pstmt=con->prepareStatement(sqlOp);
// pstmt->setString(1,"李四");
// pstmt->setString(2,"男"); //预编译修改
// string sqlOp="UPDATE contact SET name=? WHERE id=?";
// pstmt=con->prepareStatement(sqlOp);
// pstmt->setString(1,"王二");
// pstmt->setInt(2,1); //预编译删除
// string sqlOp="DELETE FROM contact WHERE id>?";
// pstmt=con->prepareStatement(sqlOp);
// pstmt->setInt(1,4); // int count=pstmt->executeUpdate();
// cout<<"count "<<count<<endl; /***********************文本数据*****************************************/
//保存大文本数据（longtext）
// string sqlOp="insert longtest(content) values(?)";
// pstmt=con->prepareStatement(sqlOp);
// ifstream file;
// file.open("./file.txt");
// if(!file){
// cout<<"open failed"<<endl;
// exit(-1);
// }
// pstmt->setBlob(1,&file);
//
// int count=pstmt->executeUpdate();
// cout<<"count "<<count<<endl;
// file.close(); /***********************二进制数据*****************************************/
//保存二进制数据（longblob）
string sqlOp="insert longtest(img) values(?)";
pstmt=con->prepareStatement(sqlOp);
ifstream file;
file.open("./test.png",ios::binary);
if(!file){
cout<<"open failed"<<endl;
exit(-);
}
pstmt->setBlob(,&file); int count=pstmt->executeUpdate();
cout<<"count "<<count<<endl;
file.close();
}
/*
* 数据查询语句DQL
*/
void SQLWork::queryData(){
string sqlOp="SELECT * FROM contact";
//string sqlOp="";
//string sqlOp=""; res = stmt->executeQuery(sqlOp); //循环遍历
while (res->next()) {
int id=res->getInt("id");
string name=res->getString("name");
string gender=res->getString("gender");
string home=res->getString("home");
cout<<"id: "<<id<<"\t| name: "<<name<<"\t| gender: "<<gender<<"\t| home: "<<home<<endl;
}
} void SQLWork::queryDataPre(){
//预编译查询
// string sqlOp="SELECT * FROM contact";
// //string sqlOp="";
// //string sqlOp="";
// pstmt=con->prepareStatement(sqlOp);
// res = pstmt->executeQuery();
// while (res->next()) {
// int id=res->getInt("id");
// string name=res->getString("name");
// string gender=res->getString("gender");
// string home=res->getString("home");
// cout<<"id: "<<id<<"\t| name: "<<name<<"\t| gender: "<<gender<<"\t| home: "<<home<<endl;
// } /***********************文本数据*****************************************/
//查询大文件数据
// string sqlOp="SELECT * FROM longtest";
//
// pstmt=con->prepareStatement(sqlOp);
// res = pstmt->executeQuery();
// while (res->next()) {
// string content=res->getString("content");
// cout<<content<<endl;
// }
/***********************二进制数据*****************************************/
//查询二进制数据
string sqlOp="SELECT * FROM longtest"; pstmt=con->prepareStatement(sqlOp);
res = pstmt->executeQuery();
int i=;
while (res->next()) {
i++;
ostringstream iname;
iname <<"img"<<i<<".png";
istream *is=res->getBlob("img");
if(is->peek()>){
ofstream file(iname.str().c_str(),ios::binary);
char data[];
while(is->read(data,)){
file.write(data,sizeof(data));
}
file.write(data,sizeof(data));
file.close();
}
}
}
获取整个工程GitHub