数据表操作层代码Model

我们将数据库的操作和业务代码分离开，因此业务模块应该包含一个个可以操作数据表的对象，通过这些对象的方法来实现对数据库的增删改查操作。

user的ORM类

//与user表相对应的类
#ifndef USER_H
#define USER_H

#include <string>

using namespace std;

//user的ORM类
class User
{
public:
    User(int id = -1, string name = "", string pwd = "", string state = "offline")
    {
        this->id = id;
        this->name = name;
        this->password = pwd;
        this->state = state;
    }

    //设置id
    void setId(int id)
    {
        this->id = id;
    }
    //设置name
    void setName(string name)
    {
        this->name = name;
    }
    //设置password
    void setPwd(string pwd)
    {
        this->password = pwd;
    }
    //设置state
    void setState(string state)
    {
        this->state = state;
    }

    //获取id
    int getId()
    {
        return this->id;
    }
    //获取name
    string getName()
    {
        return this->name;
    }
    //获取password
    string getPwd()
    {
        return this->password;
    }
    //获取state
    string getState()
    {
        return this->state;
    }

protected:
    int id;          //ID号
    string name;     //姓名
    string password; //密码
    string state;    //状态
};

#endif

group的ORM类

#ifndef GROUP_H
#define GROUP_H

#include "groupuser.hpp"
#include <string>
#include <vector>

using namespace std;

//group的ORM类
class Group
{
public:
    Group(int id = -1, string name = "", string desc = "")
    {
        this->id = id;
        this->name = name;
        this->desc = desc;
    }

    void setId(int id) { this->id = id; };            //设置组id
    void setName(string name) { this->name = name; }; //设置组名
    void setDesc(string desc) { this->desc = desc; }; //设置组功能描述

    int getId() { return this->id; };                     //获取组id
    string getName() { return this->name; };              //获取组名
    string getDesc() { return this->desc; };              //获取组功能描述
    vector<GroupUser> &getUsers() { return this->users; } //获取组成员

private:
    int id;                  //组id
    string name;             //组名
    string desc;             //组功能描述
    vector<GroupUser> users; //组成员
};

#endif

群组用户的ORM类

#ifndef GROUPUSER_H
#define GROUPUSER_H

#include "user.hpp"

//群组用户的ORM类，多了一个role角色信息（创建者还是成员），从User类直接继承，复用User的其它信息
class GroupUser : public User
{
public:
    void setRole(string role) { this->role = role; } //设置角色信息
    string getRole() { return this->role; }          //获取角色信息

private:
    string role; //角色信息
};

#endif

user表的操作类

usermodel.hpp

#ifndef USERMODEL_H
#define USERMODEL_H

#include "user.hpp"

//user表的操作类
class UserModel
{
public:
    //user表的增加方法
    bool insert(User &user);
    //根据用户号码查询用户信息
    User query(int id);
    //更新用户的状态信息
    bool updateState(User user);

    //重置用户的状态信息
    void resetState();
};

#endif

usermodel.cpp

#include "usermodel.hpp"
#include "db.hpp"
#include <iostream>

using namespace std;

//user表的增加方法
bool UserModel::insert(User &user)
{
    //组装sql语句
    char sql[1024] = {0};
    sprintf(sql, "insert into user(name, password, state) values('%s', '%s', '%s')",
            user.getName().c_str(), user.getPwd().c_str(), user.getState().c_str());

    MySQL mysql;         //定义一个mysql对象
    if (mysql.connect()) //连接chat数据库
    {
        if (mysql.update(sql)) //调用数据库更新方法，把这个sql语句传进去
        {
            user.setId(mysql_insert_id(mysql.getConnection())); //拿到数据库生成的id作为用户的id号
            return true;
        }
    }

    return false;
}

//根据用户号码查询用户信息
User UserModel::query(int id)
{
    //组装sql语句
    char sql[1024] = {0};
    sprintf(sql, "select * from user where id=%d", id);

    MySQL mysql;         //创建mysql对象
    if (mysql.connect()) //连接成功
    {
        MYSQL_RES *res = mysql.query(sql); //调用mysql数据库的查询
        if (res != nullptr)
        {
            MYSQL_ROW row = mysql_fetch_row(res); //获取行
            if (row != nullptr)
            {
                User user;
                user.setId(atoi(row[0]));
                user.setName(row[1]);
                user.setPwd(row[2]);
                user.setState(row[3]);
                mysql_free_result(res);
                return user;
            }
        }
    }
    return User(); //连接失败
}

//更新用户的状态信息
bool UserModel::updateState(User user)
{
    char sql[1024] = {0};
    sprintf(sql, "update user set state ='%s' where id=%d", user.getState().c_str(), user.getId());

    MySQL mysql;
    if (mysql.connect())
    {
        if (mysql.update(sql))
        {
            return true;
        }
    }
    return false;
}

void UserModel::resetState()
{
    //组装sql语句
    char sql[1024] = "update user set state='offline' where state='online'";

    //更新用户状态
    MySQL mysql;         //定义mysql对象
    if (mysql.connect()) //连接成功
    {
        mysql.update(sql); //更新
    }
}

friend表的操作类

friendmodel.hpp

#ifndef FRIENDMODEL_H
#define FRIENDMODEL_H

#include "user.hpp"
#include <vector>

using namespace std;

//friend表的操作类
class FriendModel
{
public:
    //添加好友关系
    void insert(int userid, int friendid);

    //返回用户好友列表
    vector<User> query(int userid);
};

#endif

friendmodel.cpp

#include "friendmodel.hpp"
#include "db.hpp"

//添加好友关系
void FriendModel::insert(int userid, int friendid)
{
    //组装sql语句
    char sql[1024] = {0};
    sprintf(sql, "insert into friend values(%d,%d)", userid, friendid);

    //更新friend表
    MySQL mysql;
    if (mysql.connect())
    {
        mysql.update(sql);
    }
}

//返回用户好友列表
vector<User> FriendModel::query(int userid)
{
    //组装sql语句
    char sql[1024] = {0};
    sprintf(sql, "select a.id,a.name,a.state from user a inner join friend b on b.friendid=a.id where b.userid=%d", userid);

    //通过联合查询，查询用户的好友列表
    vector<User> vec;
    MySQL mysql;
    if (mysql.connect())
    {
        MYSQL_RES *res = mysql.query(sql);
        if (res != nullptr)
        {
            MYSQL_ROW row;
            while ((row = mysql_fetch_row(res)) != nullptr)
            {
                User user;
                user.setId(atoi(row[0]));
                user.setName(row[1]);
                user.setState(row[2]);
                vec.push_back(user);
            }
            mysql_free_result(res);
            return vec;
        }
    }
    return vec;
}

offlinemessage表的操作类

offlinemessagemodel.hpp

#ifndef OFFLINEMESSAGEMODEL_H
#define OFFLINEMESSAGEMODEL_H

#include <string>
#include <vector>

using namespace std;

//offlinemessage表的操作类
class OfflineMsgModel
{
public:
    //插入用户的离线消息
    void insert(int userid, string msg);
    //删除用户的离线消息
    void remove(int userid);
    //查询用户的离线消息
    vector<string> query(int userid);
};

#endif

offlinemessagemodel.cpp

#include "offlinemessagemodel.hpp"
#include "db.hpp"

//插入用户的离线消息
void OfflineMsgModel::insert(int userid, string msg)
{
    //组装mysql语句
    char sql[1024] = {0};
    sprintf(sql, "insert into offlinemessage values(%d,'%s')", userid, msg.c_str());

    //向offlinemessage表插入msg
    MySQL mysql;         //定义mysql对象
    if (mysql.connect()) //连接成功
    {
        mysql.update(sql); //更新
    }
}

//删除用户的离线消息
void OfflineMsgModel::remove(int userid)
{
    //组装mysql语句
    char sql[1024] = {0};
    sprintf(sql, "delete from offlinemessage where userid=%d", userid);

    //删除offlinemessage表删中id==userid的行
    MySQL mysql;         //定义mysql对象
    if (mysql.connect()) //连接成功
    {
        mysql.update(sql); //更新
    }
}
//查询用户的离线消息
vector<string> OfflineMsgModel::query(int userid)
{
    //组装mysql语句
    char sql[1024] = {0};
    sprintf(sql, "select message from offlinemessage where userid=%d", userid);

    //查询用户的离线消息
    vector<string> vec;  //存储用户的离线消息
    MySQL mysql;         //定义mysql对象
    if (mysql.connect()) //连接成功
    {
        MYSQL_RES *res = mysql.query(sql);
        if (res != nullptr)
        {
            MYSQL_ROW row;
            while ((row = mysql_fetch_row(res)) != nullptr)
            {
                vec.push_back(row[0]);
            }
            mysql_free_result(res);
            return vec;
        }
    }
}

allgroup表和groupuser表的操作类

groupmodel.hpp

#ifndef GROUPMODEL_H
#define GROUPMODEL_H

#include "group.hpp"
#include <string>
#include <vector>

using namespace std;

//allgroup表和groupuser表的操作类
//allgroup表记录所有的群组
//groupuser记录用户和群组的关系
class GroupModel
{
public:
    //创建群组
    bool createGroup(Group &group);
    //加入群组
    void addGroup(int userid, int groupid, string role);
    //查询用户所在群组信息  在客户端呈现
    vector<Group> queryGroups(int userid);
    //根据指定的groupid查询群组用户id列表，除userid自己，主要用户给群组其它成员群发消息
    vector<int> queryGroupUsers(int userid, int groupid);
};

#endif

groupmodel.cpp

#include "groupmodel.hpp"
#include "db.hpp"

//创建群组
bool GroupModel::createGroup(Group &group)
{
    //组装sql语句
    char sql[1024] = {0};
    sprintf(sql, "insert into allgroup(groupname,groupdesc) values('%s','%s')", group.getName().c_str(), group.getDesc().c_str());

    //向allgroup表中插入群组信息
    MySQL mysql;
    if (mysql.connect())
    {
        if (mysql.update(sql))
        {
            group.setId(mysql_insert_id(mysql.getConnection()));
            return true;
        }
    }

    return false;
}

//加入群组
void GroupModel::addGroup(int userid, int groupid, string role)
{
    //组装sql语句
    char sql[1024] = {0};
    sprintf(sql, "insert into groupuser values(%d,%d,'%s')", groupid, userid, role.c_str());

    //在groupuser表中插入信息
    MySQL mysql;
    if (mysql.connect())
    {
        mysql.update(sql);
    }
}

//查询用户所在群组信息 ，在客户端呈现
vector<Group> GroupModel::queryGroups(int userid)
{
    //组装sql语句
    char sql[1024] = {0};
    sprintf(sql, "select a.id,a.groupname,a.groupdesc from allgroup a inner join groupuser b on a.id=b.groupid where b.userid=%d", userid);

    //存储用户所在的群组信息
    vector<Group> groupVec;

    //存储联合查询之后的群组信息
    MySQL mysql;
    if (mysql.connect())
    {
        MYSQL_RES *res = mysql.query(sql);
        if (res != nullptr)
        {
            MYSQL_ROW row;
            while ((row = mysql_fetch_row(res)) != nullptr)
            {
                Group group;
                group.setId(atoi(row[0]));
                group.setName(row[1]);
                group.setDesc(row[2]);
                groupVec.push_back(group);
            }
            mysql_free_result(res);
        }
    }
    for (Group &group : groupVec)
    {
        sprintf(sql, "select a.id ,a.name,a.state,b.grouprole from user a inner join groupuser b on b.userid=a.id where b.groupid=%d", group.getId());

        MYSQL_RES *res = mysql.query(sql);
        if (res != nullptr)
        {
            MYSQL_ROW row;
            while ((row = mysql_fetch_row(res)) != nullptr)
            {
                GroupUser user;
                user.setId(atoi(row[0]));
                user.setName(row[1]);
                user.setState(row[2]);
                user.setRole(row[3]);
                group.getUsers().push_back(user);
            }
            mysql_free_result(res);
        }
    }
    return groupVec; //这个东西存着用户的所有群组和所有群组里的用户信息
}

//根据指定的groupid查询群组用户id列表，除userid自己，主要用户给群组其它成员群发消息
vector<int> GroupModel::queryGroupUsers(int userid, int groupid)
{
    //组装sql语句
    char sql[1024] = {0};
    sprintf(sql, "select userid from groupuser where groupid=%d and userid !=%d", groupid, userid);

    vector<int> idVec;
    MySQL mysql;
    if (mysql.connect())
    {
        MYSQL_RES *res = mysql.query(sql);
        if (res != nullptr)
        {
            MYSQL_ROW row;
            while ((row = mysql_fetch_row(res)) != nullptr)
            {
                idVec.push_back(atoi(row[0]));
            }
            mysql_free_result(res);
        }
    }
    return idVec;
}