Home>

All the timeThe author is constantly looking for a more user-friendly database access method (not to say that the default method is not good,But sometimes it is not very convenient in modular design).

I was fortunate to find it in phpcodeigniterActivereord, refer to this article in detail:Extract database access class from codeigniter!!

However, C++ always uses the most primitive way,Yesterday, taking advantage of the opportunity of the project,After searching for a long time,Finally let me find two sets of C++ database access frameworks:

soci

litesql

I took both sets of code and took a look.litesql implements a complete set of automatic code generation,Powerful,But also heavysoci is relatively lightweight,However, the mapping of data structures to database tables is also implemented.I still prefer lightweight things,So I chose soci. After two days of trial,I feel very good.

The official documentation is also very detailed,So here is a simple explanation using the unit test code I wrote:

First create the library table:

create database soci;
create table `tb_test` (
 `id` int (11) not null auto_increment, `name` varchar (32) default" ", `sex` int (11) default 0, primary key (`id`), unique key `name` (` name`)
);
create database soci;
create table `tb_test` (
 `id` int (11) not null auto_increment, `name` varchar (32) default" ", `sex` int (11) default 0, primary key (`id`), unique key `name` (` name`)
);

1. Simple selection of a single record

test (soci, select_one)
{
  try
  {
    session sql (mysql, "host=localhost db=soci user=dantezhu");
    indicator ind;
    string name="dandan";
    int sex;
    sql<""select sex from tb_test where name =:name",      into (sex, ind), use (name);
    assert_eq (ind, i_ok)<<name;
  }
  catch (exception const&e)
  {
    fail ()<e.what ();
  }
}
test (soci, select_one)
{
  try
  {
    session sql (mysql, "host=localhost db=soci user=dantezhu");
    indicator ind;
    string name="dandan";
    int sex;
    sql<""select sex from tb_test where name =:name",      into (sex, ind), use (name);
    assert_eq (ind, i_ok)<<name;
  }
  catch (exception const&e)
  {
    fail ()<e.what ();
  }
}

The result of select. If successful, ind will be i_ok, and the same value sex will be assigned;

2. Simple selection of multiple records

test (soci, select_multi2)
{
  try
  {
    session sql (mysql, "db=soci user=dantezhu");
    indicator ind;
    int count;
    sql<""select count (*) from tb_test", into (count, ind);
    assert_eq (ind, i_ok)<<count;
    if (count == 0)
    {
      succeed ();
      return;
    }
    int sex=1;
    vector<string>vec_name (count);
    vector<int>vec_sex (count);
    sql<""select name, sex from tb_test where sex =:sex",      into (vec_name), into (vec_sex), use (sex);
  }
  catch (exception const&e)
  {
    fail ()<e.what ();
  }
}
test (soci, select_multi2)
{
  try
  {
    session sql (mysql, "db=soci user=dantezhu");
    indicator ind;
    int count;
    sql<""select count (*) from tb_test", into (count, ind);
    assert_eq (ind, i_ok)<<count;
    if (count == 0)
    {
      succeed ();
      return;
    }
    int sex=1;
    vector<string>vec_name (count);
    vector<int>vec_sex (count);
    sql<""select name, sex from tb_test where sex =:sex",      into (vec_name), into (vec_sex), use (sex);
  }
  catch (exception const&e)
  {
    fail ()<e.what ();
  }
}

The only difference from selecting a single record is thatThe argument to into () is a vector. It is not a good choice to use multiple vectors.The data structure-based approach will be described later.

3. Simple insert

test (soci, insert_exist)
{
  try
  {
    session sql (mysql, "db=soci user=dantezhu");
    string name="dandan";
    int sex=1;
    sql<""insert into tb_test (name, sex) values ​​(:name,:sex)",      use (name), use (sex);
  }
  catch (exception const&e)
  {
    succeed ()<e.what ();
  }
}
test (soci, insert_exist)
{
  try
  {
    session sql (mysql, "db=soci user=dantezhu");
    string name="dandan";
    int sex=1;
    sql<""insert into tb_test (name, sex) values ​​(:name,:sex)",      use (name), use (sex);
  }
  catch (exception const&e)
  {
    succeed ()<e.what ();
  }
}

Insert, update, delete all have the same two problems:

a) affect_rows (number of rows of operation) has no way to return

b) the id of the operation cannot be known,Especially for the case where the primary key of the insert is self-incrementing,It is not possible to know what the value of the inserted primary key is.

Update and delete are similar to insert, so I won't go into details here.

Next is a very important feature of this framework,That is, the database table is bound to the data structure:

First we need to define a structure,And tell soci how to make the column names correspond to the fields of the data structure:

struct person
{
  int id;
  std ::string name;
  int sex;
};
namespace soci
{
  template<>struct type_conversion<person>
  {
    typedef values ​​base_type;
    static void from_base (values ​​const&v, indicator/* ind * /, person & p)
    {
      p.id=v.get<int>("id");
      p.name=v.get<std ::string>("name");
      p.sex=v.get<int>("sex");
    }
    static void to_base (const person&p, values ​​&v, indicator&ind)
    {
      v.set ("id", p.id);
      v.set ("name", p.name);
      v.set ("sex", p.sex);
      ind=i_ok;
    }
  };
}
struct person
{
  int id;
  std ::string name;
  int sex;
};
namespace soci
{
  template<>struct type_conversion<person>
  {
    typedef values ​​base_type;
    static void from_base (values ​​const&v, indicator/* ind * /, person & p)
    {
      p.id=v.get<int>("id");
      p.name=v.get<std ::string>("name");
      p.sex=v.get<int>("sex");
    }
    static void to_base (const person&p, values ​​&v, indicator&ind)
    {
      v.set ("id", p.id);
      v.set ("name", p.name);
      v.set ("sex", p.sex);
      ind=i_ok;
    }
  };
}

on

template<>struct type_conversion<person>
template<>struct type_conversion<person>

Here, the official documentation is incorrect.I checked for a long time,Just write it as above.

1. Use data structure to select

test (soci, select_obj_one)
{
  try
  {
    session sql (mysql, "db=soci user=dantezhu");
    indicator ind;
    int count;
    sql<""select count (*) from tb_test", into (count, ind);
    assert_eq (ind, i_ok)<<count;
    string name="dandan";
    person p;
    sql<""select id, name, sex from tb_test where name =:name",      into (p, ind), use (name);
    assert_eq (ind, i_ok)<<name;
    if (sql.got_data ())
    {
      cout<p.id
        <<","
        <<p.name
        <<","
        <<p.sex
        <endl;
    }
  }
  catch (exception const&e)
  {
    fail ()<e.what ();
  }
}
test (soci, select_obj_one)
{
  try
  {
    session sql (mysql, "db=soci user=dantezhu");
    indicator ind;
    int count;
    sql<""select count (*) from tb_test", into (count, ind);
    assert_eq (ind, i_ok)<<count;
    string name="dandan";
    person p;
    sql<""select id, name, sex from tb_test where name =:name",      into (p, ind), use (name);
    assert_eq (ind, i_ok)<<name;
    if (sql.got_data ())
    {
      cout<p.id
        <<","
        <<p.name
        <<","
        <<p.sex
        <endl;
    }
  }
  catch (exception const&e)
  {
    fail ()<e.what ();
  }
}

2. Use data structure for insert

test (soci, insert_obj_noexist)
{
  try
  {
    session sql (mysql, "db=soci user=dantezhu");
    person p={
      0,      "niuniu",      2
    };
    sql<""insert into tb_test (name, sex) values ​​(:name,:sex)",      use (p);
  }
  catch (exception const&e)
  {
    fail ()<e.what ();
  }
}
test (soci, insert_obj_noexist)
{
  try
  {
    session sql (mysql, "db=soci user=dantezhu");
    person p={
      0,      "niuniu",      2
    };
    sql<""insert into tb_test (name, sex) values ​​(:name,:sex)",      use (p);
  }
  catch (exception const&e)
  {
    fail ()<e.what ();
  }
}

The whole is like this ~~ The following is the download path of the code file in the text:

  • Previous Ruby metaprogramming techniques
  • Next jQuery implements tab effect code for scrolling