mysql connectorc++ 基础教程（一）_mysql connector c++

一：介绍

默认已经装好了 connector c++,未装好请转mysql connector c++ 安装
默认已初步了解sql语法
默认写给N多天后的自己，比较基础
内容是从官网上学习的，简化了步骤—官方教程
源码基本复制就能跑
特别提醒：编译时，需要加上 -lmysqlcppconn

二：增删改查

#include <cppconn/driver.h>
#include <cppconn/connection.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/resultset.h>
#include <cppconn/metadata.h>
#include <cppconn/resultset_metadata.h>
#include <cppconn/exception.h>
#include <cppconn/warning.h>

#define DBHOST "tcp://127.0.0.1:3306"
#define USER "root"
#define PASSWORD "123456"
#define DATABASE "sys"

using namespace std;
using namespace sql;


int main()
{
     try
    {
        //连接数据库
        Driver *driver = get_driver_instance();
        Connection *conn = driver->connect(DBHOST, USER, PASSWORD);

        Statement *stm;


        if(!conn->isValid()){
            cout<<"数据库连接无效"<<endl;
            return 0;
        }else
            cout<<"数据库连接成功"<<endl;
        //创建 test 表，添加数据
        stm = conn->createStatement();
        stm->execute("use " DATABASE);
        stm->execute("DROP TABLE IF EXISTS test");
        stm->execute("CREATE TABLE test(id INT,lable CHAR(1))");
        stm->execute("INSERT INTO test(id,lable) VALUES(6,'A')");
        stm->execute("INSERT INTO test(id,lable) VALUES(3,'A')");
        stm->execute("INSERT INTO test(id,lable) VALUES(2,'A')");

        //升序查询
        ResultSet *rss;
        rss = stm->executeQuery("SELECT id,lable FROM test ORDER BY id ASC");
        while (rss->next())
        {
            /* code */

            int id = rss->getInt(1);
            string lable = rss->getString("lable");

            cout << "id:" << id << ","
                 << "lable:" << lable << endl;
        }


        //删除
        stm->execute("DELETE FROM test WHERE id=3");

        //改
        stm->execute("UPDATE test SET lable='B' WHERE id=2");

        delete stm;
        delete conn;
        delete rss;
    
    }
    catch (const SQLException &sqle)
    {
        cout << "# ERR: SQLException in " << __FILE__;
        cout << "(" << __FUNCTION__ << ") on line "<< __LINE__ << endl;
        std::cerr << "sql errcode:" << sqle.getErrorCode() << ",state:" << sqle.getSQLState() << ",what:" << sqle.what() << endl;
    }

    return 0;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

三：预编译语句（PreparedStatement ）

使用条件（个人理解）：同样的语句在多次执行时（只有变量不同，其余都相同，且将要执行多次）

#include <cppconn/driver.h>
#include <cppconn/connection.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/resultset.h>
#include <cppconn/metadata.h>
#include <cppconn/resultset_metadata.h>
#include <cppconn/exception.h>
#include <cppconn/warning.h>

#define DBHOST "tcp://127.0.0.1:3306"
#define USER "root"
#define PASSWORD "123456"
#define DATABASE "sys"

using namespace std;
using namespace sql;


int main()
{
    try
    {
        /* code */
        Driver *driver = get_driver_instance();
        Connection *conn = driver->connect(DBHOST, USER, PASSWORD);
        Statement *stm;
        
        ResultSet *rss;

        stm = conn->createStatement();
        stm->execute("use " DATABASE);
        stm->execute("DROP TABLE IF EXISTS test");
        stm->execute("CREATE TABLE test(id INT,lable CHAR(1))");

        //预编译语句
        PreparedStatement *pstm;
        pstm = conn->prepareStatement("INSERT INTO test(id,lable) VALUES(?,?)");
        pstm->setInt(1, 3);
        pstm->setString(2, "c");
        pstm->execute();
        pstm->setInt(1, 4);
        pstm->setString(2, "d");
        pstm->execute();
        pstm->setInt(1, 1);
        pstm->setString(2, "b");
        pstm->execute();

        rss = stm->executeQuery("SELECT id,lable FROM test ORDER BY id ASC");

        while (rss->next())
        {
            /* code */

            int id = rss->getInt(1);
            string lable = rss->getString("lable");

            cout << "id:" << id << ","
                 << "lable:" << lable << endl;
        }

        delete stm;
        delete conn;
        delete rss;
        delete pstm;
    }
    catch (const SQLException &sqle)
    {
        cout << "# ERR: SQLException in " << __FILE__;
        cout << "(" << __FUNCTION__ << ") on line "<< __LINE__ << endl;
        std::cerr << "sql errcode:" << sqle.getErrorCode() << ",state:" << sqle.getSQLState() << ",what:" << sqle.what() << endl;
    }

    return 0;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

四：过程函数

因为是学的官方教程，固依赖了官方的数据库，下载地址

下面代码分为有返回值和没有返回值的函数，都有注释，结果都有打印，执行完代码后可以看到数据库增加了许多函数

源码

#include <stdlib.h>
#include <iostream>
#include <sstream>
#include <stdexcept>
/* uncomment for applications that use vectors */
/*#include <vector>*/

#include "mysql_connection.h"

#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>

#define EXAMPLE_HOST "tcp://127.0.0.1:3306"
#define EXAMPLE_USER "root"
#define EXAMPLE_PASS "123456"
#define EXAMPLE_DB "world"

using namespace std;
//没有返回值的语句
const string procedure_noresult = "CREATE PROCEDURE add_country (IN country_code CHAR(3),IN country_name CHAR(52),IN continent_name CHAR(30))\n"
                                  "BEGIN\n"
                                  "INSERT INTO country(Code, Name, Continent)\n"
                                  "VALUES (country_code, country_name, continent_name);\n"
                                  "END;";
//有返回值的语句
const string procedure_result = "CREATE PROCEDURE get_pop (IN country_name CHAR(52),OUT country_pop BIGINT)\n"
                                "BEGIN\n"
                                "SELECT Population INTO country_pop FROM country\n"
                                "WHERE Name = country_name;\n"
                                "END;";
//有返回值的语句
const string procedure_result1 = "CREATE PROCEDURE get_pop_continent (IN continent_name CHAR(30),OUT continent_pop BIGINT)\n"
                                 "BEGIN\n"
                                 "SELECT SUM(Population) INTO continent_pop FROM country\n"
                                 "WHERE Continent = continent_name;\n"
                                 "END;\n";
//有返回值的语句
const string procedure_result2 = "CREATE PROCEDURE get_pop_world (OUT world_pop BIGINT)\n"
                                 "BEGIN\n"
                                 "SELECT SUM(Population) INTO world_pop FROM country;\n"
                                 "END;\n";
//有返回值的语句
const string procedure_result3 = "CREATE PROCEDURE get_data ()\n"
                                 "BEGIN\n"
                                 "SELECT Code, Name, Population, Continent FROM country\n"
                                 "WHERE Continent = 'Oceania' AND Population < 10000;\n"
                                 "SELECT Code, Name, Population, Continent FROM country\n"
                                 "WHERE Continent = 'Europe' AND Population < 10000;\n"
                                 "SELECT Code, Name, Population, Continent FROM country\n"
                                 "WHERE Continent = 'North America' AND Population < 10000;\n"
                                 "END;";
void testNoResult();
void testResult();
void testResult1();

int main(int argc, const char **argv)
{

  cout << "Connector/C++ tutorial framework..." << endl;
  cout << endl;



  try
  {

    /* INSERT TUTORIAL CODE HERE! */
    //testNoResult();
    //testResult();
    testResult1();
  }
  catch (sql::SQLException &e)
  {
    /*
      MySQL Connector/C++ throws three different exceptions:

      - sql::MethodNotImplementedException (derived from sql::SQLException)
      - sql::InvalidArgumentException (derived from sql::SQLException)
      - sql::SQLException (derived from std::runtime_error)
    */
    cout << "# ERR: SQLException in " << __FILE__;
    cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
    /* what() (derived from std::runtime_error) fetches error message */
    cout << "# ERR: " << e.what();
    cout << " (MySQL error code: " << e.getErrorCode();
    cout << ", SQLState: " << e.getSQLState() << " )" << endl;

    return EXIT_FAILURE;
  }

  cout << "Done." << endl;
  return EXIT_SUCCESS;
}

void testResult()
{

  sql::Driver *driver = get_driver_instance();
  std::auto_ptr<sql::Connection> con(driver->connect(EXAMPLE_HOST, EXAMPLE_USER, EXAMPLE_PASS));
  con->setSchema(EXAMPLE_DB);
  std::auto_ptr<sql::Statement> stmt(con->createStatement());

  stmt->execute("DROP PROCEDURE IF EXISTS get_pop");
  stmt->execute(procedure_result);
  stmt->execute("DROP PROCEDURE IF EXISTS get_pop_continent");
  stmt->execute(procedure_result1);
  stmt->execute("DROP PROCEDURE IF EXISTS get_pop_world");
  stmt->execute(procedure_result2);


  stmt->execute("CALL get_pop('Uganda', @pop)");
  std::auto_ptr<sql::ResultSet> res(stmt->executeQuery("SELECT @pop AS _reply"));
  while (res->next())
    cout << "Population of Uganda: " << res->getString("_reply") << endl;

  stmt->execute("CALL get_pop_continent('Asia', @pop)");

  res.reset(stmt->executeQuery("SELECT @pop AS _reply"));
  while (res->next())
    cout << "Population of Asia: " << res->getString("_reply") << endl;

  stmt->execute("CALL get_pop_world(@pop)");

  res.reset(stmt->executeQuery("SELECT @pop AS _reply"));
  while (res->next())
    cout << "Population of World: " << res->getString("_reply") << endl;
}

void testNoResult()
{

  sql::Driver *driver = get_driver_instance();
  std::auto_ptr<sql::Connection> con(driver->connect(EXAMPLE_HOST, EXAMPLE_USER, EXAMPLE_PASS));
  con->setSchema(EXAMPLE_DB);
  std::auto_ptr<sql::Statement> stmt(con->createStatement());

  // We need not check the return value explicitly. If it indicates
  // an error, Connector/C++ generates an exception.

  stmt->execute("DROP PROCEDURE IF EXISTS add_country");
  stmt->execute(procedure_noresult);
  stmt->execute("CALL add_country('ATL', 'Atlantis', 'North America')");
}

void testResult1()
{

  sql::Driver *driver = get_driver_instance();
  std::auto_ptr<sql::Connection> con(driver->connect(EXAMPLE_HOST, EXAMPLE_USER, EXAMPLE_PASS));
  con->setSchema(EXAMPLE_DB);
  std::auto_ptr<sql::Statement> stmt(con->createStatement());

  stmt->execute("DROP PROCEDURE IF EXISTS get_data");
  stmt->execute(procedure_result3);
  stmt->execute("CALL get_data()");
  std::auto_ptr<sql::ResultSet> res;
  do
  {
    res.reset(stmt->getResultSet());
    while (res->next())
    {
      cout << "Name: " << res->getString("Name")
           << " Population: " << res->getInt("Population")
           << endl;
    }
  } while (stmt->getMoreResults());
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170