呼叫mysql

liiinuuux發表於2016-09-26
MySql
連結選項：-lmysqlcppconn

點選(此處)摺疊或開啟

  1. #include <mysql_driver.h>
  2. #include <mysql_connection.h>
  3. #include <mysql_error.h>

  5. int main()
  6. {
  7.     sql::mysql::MySQL_Driver* driver;
  8.     sql::Connection* con;

  10.     driver = sql::mysql::get_mysql_driver_instance();
  11.     con = driver->connect("tcp://127.0.0.1:1101", "root", "oracle");
  12.     if(con->isValid()) cout << "ok" << endl;
  13.     else con->reconnect();
  14.     delete con;
  15. }

  17. con->isValid() 測試連結
  18. con->reconnect() 重新連線
  19. 必須釋放con物件

獲取查詢結果

點選(此處)摺疊或開啟

  1. #include <stdio.h>
  2. #include <iostream>
  3. #include <string>
  4. #include <vector>
  5. #include <mysql_driver.h>
  6. #include <mysql_connection.h>
  7. #include <mysql_error.h>
  8. #include <cppconn/driver.h>
  9. #include <cppconn/exception.h>
  10. #include <cppconn/resultset.h>
  11. #include <cppconn/statement.h>
  12. #include <cppconn/prepared_statement.h>
  13. using namespace std;
  14. int main()
  15. {
  16.     sql::mysql::MySQL_Driver *driver;
  17.     sql::Connection* conn;
  18.     sql::Statement * stmt;
  19.     sql::ResultSet * rest;

  21.     driver = sql::mysql::get_mysql_driver_instance();
  22.     conn = driver->connect("tcp://127.0.0.1:1101", "root", "oracle");
  23.     cout << "connect ok" << endl << endl;

  25.     stmt = conn->createStatement();
  26.     stmt->execute("use test"); // 也可以先conn->setSchema(xxxx);
  27.     stmt->execute("drop table t");
  28.     stmt->execute("create table t(id int, name varchar(10))");
  29.     stmt->execute("insert into t values (1, 'xxx')");
  30.     stmt->execute("insert into t values (2, 'yyy')");
  31.     stmt->execute("insert into t values (3, 'zzz')");
  32.     stmt->execute("commit");
  33.     rest = stmt->executeQuery("select * from t");

  35.     sql::ResultSetMetaData* md = rest->getMetaData();
  36.     for(int i=0; i<md->getColumnCount(); i++) printf("%10s", md->getColumnLabel(i+1).c_str());
  37.     cout << endl;
  38.     while (rest->next()) printf("%10d%10s\n", rest->getInt(1), rest->getString(2).c_str());

  40.     delete rest;
  41.     delete stmt;
  42.     delete conn;
  43. }

  45. 每次執行查詢都要釋放其產生的resultset

使用繫結變數

點選(此處)摺疊或開啟

  1. int main()
  2. {
  3.     sql::mysql::MySQL_Driver *driver = sql::mysql::get_mysql_driver_instance();
  4.     sql::Connection* conn = driver->connect("tcp://192.168.1.201:1101", "root", "oracle");
  5.     sql::Statement * stmt = conn->createStatement();
  6.     stmt->execute("use test");
  7.     stmt->execute("drop table t");
  8.     stmt->execute("create table t(id int, name varchar(10))");
  9.     delete stmt;

  11.     char name[5];
  12.     memset(name, '0', 5);
  13.     sql::PreparedStatement* pst1 = conn->prepareStatement("insert into t values (?, ?)");
  14.     sql::PreparedStatement* pst2 = conn->prepareStatement("select * from t where id = ?");
  15.     sql::ResultSet* rest = NULL;
  16.     for(int i=0; i<100; i++)
  17.     {
  18.         sprintf(name, "%d", i);
  19.         pst1->setInt(1, i);
  20.         pst1->setString(2, string(name));
  21.         pst1->executeUpdate();
  22.     }
  23.     for(int i=0; i<100; i++)
  24.     {
  25.         pst2->setInt(1, i);
  26.         rest = pst2->executeQuery();
  27.         while (rest->next()) printf("%10d%10s\n", rest->getInt(1), rest->getString(2).c_str());
  28.         delete rest;
  29.     }
  30.     delete pst1;
  31.     delete pst2;
  32.     delete rest;
  33.     delete conn;
  34. }

異常處理

點選(此處)摺疊或開啟

  1. try {
  2.   ...
  3. } catch (sql::SQLException &e) {
  4.     cout << "# ERR: " << e.what();
  5.     cout << " (MySQL error code: " << e.getErrorCode();
  6.     cout << ", SQLState: " << e.getSQLState() << " )" << endl;
  7. }

呼叫儲存過程

點選(此處)摺疊或開啟

  1. DELIMITER //
  2. create procedure add_t (in iid int, in iname varchar(10)) begin insert into t values (iid, iname); end;
  3. create procedure get_t_name (in iid int, out oname varchar(10)) begin select max(name) into oname from t where id = iid; end;
  4. create procedure get_t_id (in iname varchar(10), out oid int) begin select max(id) into oid from t where name = iname; end;
  5. create procedure get_t_cnt (out cnt int) begin select count(*) into cnt from t; end;
  6. create procedure get_t ()
  7. begin
  8. select id from t;
  9. select name from t;
  10. end;
  11. //
  12. DELIMITER ;

呼叫

點選(此處)摺疊或開啟

  1. stmt->execute("call add_t(1, 'asd')");

獲取儲存過程的out引數

點選(此處)摺疊或開啟

  1. stmt->execute("call get_t_name(1, @name)");
  2. std::auto_ptr<sql::ResultSet> res(stmt->executeQuery("select @name as _name"));
  3. while (res->next()) cout << "name = " << res->getString("_name") << endl;

  5. stmt->execute("call get_t_id('asd', @id)");
  6. res.reset(stmt->executeQuery("select @id as _id"));
  7. while (res->next()) cout << "id = " << res->getInt("_id") << endl;

  9. stmt->execute("call get_t_cnt(@cnt)");
  10. res.reset(stmt->executeQuery("select @cnt as _cnt"));
  11. while (res->next()) cout << "count = " << res->getInt("_cnt") << endl;

獲取儲存過程輸出的結果集

點選(此處)摺疊或開啟

  1. stmt->execute("call get_t");
  2. std::auto_ptr<sql::ResultSet> res;
  3. do {
  4.   res.reset(stmt->getResultSet());
  5.   while (res->next()) cout << "id: " << res->getInt("id") << " name: " << res->getString("name") << endl;
  6. } while (stmt->getMoreResults());

一個stmt有多個結果集時用stmt->getMoreResults()檢視是否還有結果集










來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26239116/viewspace-2125604/，如需轉載，請註明出處，否則將追究法律責任。

相關文章