/** SQLWork.cpp
*
* Created on: Dec 29, 2016
* Author: tla001*/#include"SQLWork.h"SQLWork::SQLWork(string url,string user,stringpasswd) {//TODO Auto-generated constructor stub
turl=url;
tuser=user;
tpasswd=passwd;
}
SQLWork::~SQLWork() {//TODO Auto-generated destructor stub
if(res)deleteres;if(stmt)deletestmt;if(con)deletecon;
}intSQLWork::doConnect(){try{
dirver= get_driver_instance(); //连接数据库
con = dirver->connect(turl, tuser, tpasswd);
con->setClientOption("characterSetResults", "utf8");
}catch(SQLException &e){
cout<< "ERROR:" <
cout<< "(MySQL error code:" <
cout<< ", SQLState:" << e.getSQLState() << ")" <
Message: Unknown command*/cout<< "\nYour server does not seem to support Prepared Statements at all.";
cout<< "Perhaps MYSQL < 4.1?" <
}returnEXIT_FAILURE;
}catch (runtime_error &e) {
cout<< "ERROR:" << e.what() <
}returnEXIT_SUCCESS;
}int SQLWork::changeCharacter(stringchars){try{
con->setClientOption("characterSetResults", chars);
}catch(SQLException &e){
cout<< "ERROR:" <
cout<< "(MySQL error code:" <
cout<< ", SQLState:" << e.getSQLState() << ")" <
Message: Unknown command*/cout<< "\nYour server does not seem to support Prepared Statements at all.";
cout<< "Perhaps MYSQL < 4.1?" <
}returnEXIT_FAILURE;
}catch (runtime_error &e) {
cout<< "ERROR:" << e.what() <
}returnEXIT_SUCCESS;
}int SQLWork::chooseDatabase(stringdb){try{
con->setSchema(db);
stmt= con->createStatement(); //从表中获取所有信息
}catch(SQLException &e){
cout<< "ERROR:" <
cout<< "(MySQL error code:" <
cout<< ", SQLState:" << e.getSQLState() << ")" <
Message: Unknown command*/cout<< "\nYour server does not seem to support Prepared Statements at all.";
cout<< "Perhaps MYSQL < 4.1?" <
}returnEXIT_FAILURE;
}catch (runtime_error &e) {
cout<< "ERROR:" << e.what() <
}returnEXIT_SUCCESS;
}intSQLWork::doWork(){try{/** 执行具体工作*/
//buildTest();//operateTable();//operateData();//operateDataPre();//queryData();
queryDataPre();
}catch(SQLException &e){
cout<< "ERROR:" <
cout<< "(MySQL error code:" <
cout<< ", SQLState:" << e.getSQLState() << ")" <
Message: Unknown command*/cout<< "\nYour server does not seem to support Prepared Statements at all.";
cout<< "Perhaps MYSQL < 4.1?" <
}returnEXIT_FAILURE;
}catch (runtime_error &e) {
cout<< "ERROR:" << e.what() <
}returnEXIT_SUCCESS;
}voidSQLWork::buildTest(){
res= stmt->executeQuery("SELECT * from student"); //循环遍历
while (res->next()) { //输出,id,name,age字段的信息
cout << res->getInt("id")<< "|" << res->getString(2)<< "|" << res->getInt("tage")<< "|" << res->getString("thome")<
}//清理
}/** 执行DDL语句Data Definition Language 数据定义语言*/
voidSQLWork::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<
}/** DML(Data Manipulation Language 数据操控语言)*/
voidSQLWork::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<
}voidSQLWork::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<
/***********************文本数据*****************************************/
//保存大文本数据(longtext)//string sqlOp="insert longtest(content) values(?)";//pstmt=con->prepareStatement(sqlOp);//ifstream file;//file.open("./file.txt");//if(!file){//cout<setBlob(1,&file);//
//int count=pstmt->executeUpdate();//cout<
/***********************二进制数据*****************************************/
//保存二进制数据(longblob)
string sqlOp="insert longtest(img) values(?)";
pstmt=con->prepareStatement(sqlOp);
ifstream file;
file.open("./test.png",ios::binary);if(!file){
cout<
exit(-1);
}
pstmt->setBlob(1,&file);int count=pstmt->executeUpdate();
cout<
file.close();
}/** 数据查询语句DQL*/
voidSQLWork::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<
}
}voidSQLWork::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<
/***********************文本数据*****************************************/
//查询大文件数据//string sqlOp="SELECT * FROM longtest";//
//pstmt=con->prepareStatement(sqlOp);//res = pstmt->executeQuery();//while (res->next()) {//string content=res->getString("content");//cout<
//查询二进制数据
string sqlOp="SELECT * FROM longtest";
pstmt=con->prepareStatement(sqlOp);
res= pstmt->executeQuery();int i=0;while (res->next()) {
i++;
ostringstream iname;
iname<
istream*is=res->getBlob("img");if(is->peek()>0){
ofstream file(iname.str().c_str(),ios::binary);char data[1024];while(is->read(data,1024)){
file.write(data,sizeof(data));
}
file.write(data,sizeof(data));
file.close();
}
}
}