工具篇|C++实现一个SQL语句填写工具

开发背景

客户端开发虽然涉及数据库的场景不是很多，但是一般使用sqlite3建立保存本地数据库进行一些内部数据存储。

在开发代码中，往往会进行一些连表查询，通过sqlite3进行查询时，写出的SQL语句非常不美观并且出错的概率非常大。因此学习并实现一个SQL语句工具，可以优雅的添加SQL语句。

简单了解SQL语句

SQL语句一般分为三类：

  • DDL类：数据定义语言(CREATE，ALTER，DROP，DECLARE)
  • DML类：数据操作语言(SELECT，DELETE，UPDATE，INSERT)
  • DCL类：数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)

网上也有分成四类，将查询单独作为一个类——DQL类，数据查询语言。

相对于客户端本地或者开发者工具使用来说，主要使用DML进行数据库的操作。本工具主要围绕CREATE、DROP、SELECT、DELETE、UPDATE、INSERT几个SQL语言关键字使用。

表级操作

表级操作是指对整个表进行操作的数据库操作。这些操作通常会影响到表的结构或内容，而不是单个行或列。表级操作可以包括创建、修改或删除表，以及对表的索引、约束和触发器进行操作。

在 SQL中，CREATE、DROP 和 ALTER 是用于创建、删除和修改数据库对象（如表、索引、视图等）的关键字。

  1. CREATE：用于创建数据库对象。常见的 CREATE 操作包括：

    • CREATE TABLE：用于创建表。
    • CREATE INDEX：用于创建索引。
    • CREATE VIEW：用于创建视图。

    例如，创建一个名为 PERSON 的表：

    1
    2
    3
    4
    CREATE TABLE PERSON (
        ID INT PRIMARY KEY,
        NAME VARCHAR(50)
    );

  2. DROP：用于删除数据库对象。常见的 DROP 操作包括：

    • DROP TABLE：用于删除表。
    • DROP INDEX：用于删除索引。
    • DROP VIEW：用于删除视图。

    例如，删除名为 PERSON 的表：

    1
    DROP TABLE PERSON;

  3. ALTER：用于修改数据库对象。常见的 ALTER 操作包括：

    • ALTER TABLE：用于修改表的结构，如添加列、修改列类型等。
    • ALTER INDEX：用于修改索引。
    • ALTER VIEW：用于修改视图。

    例如，向名为 PERSON 的表添加一个新列 AGE：

    1
    2
    ALTER TABLE PERSON
    ADD COLUMN AGE INT;

这些关键字在 SQL 中用于管理数据库对象的创建、删除和修改。通过使用这些关键字，可以对数据库进行结构性的管理和维护。

行级操作

行级操作用于对表中的单个行进行操作，包括插入、更新和删除数据。

  • 插入数据：使用INSERT INTO语句将新数据插入表中。
  • 更新数据：使用UPDATE语句更新表中的现有数据。
  • 删除数据：使用DELETE FROM语句删除表中的数据。
  • 查询数据：使用SELECT语句查找数据表中的数据。

  1. SELECT语句举例：

    1
    2
    3
    4
    5
    6
    7
    8
    -- 从名为"users"的表中选择所有列和行
    SELECT * FROM users;

    -- 从"users"表中选择特定列
    SELECT user_id, username, email FROM users WHERE user_type = 'admin';

    -- 使用聚合函数和GROUP BY子句
    SELECT country, COUNT(*) AS total_users FROM users GROUP BY country;

  2. DELETE语句举例：

    1
    2
    3
    4
    5
    -- 删除名为"products"的表中所有行
    DELETE FROM products;

    -- 删除"orders"表中满足条件的行
    DELETE FROM orders WHERE order_date < '2024-01-01';

  3. UPDATE语句举例：

    1
    2
    3
    4
    5
    -- 将"customers"表中的city列设置为'New York'，对于满足条件的行
    UPDATE customers SET city = 'New York' WHERE state = 'NY';

    -- 将"products"表中的价格增加10%
    UPDATE products SET price = price * 1.1 WHERE category = 'Electronics';

  4. INSERT语句举例：

    1
    2
    3
    4
    5
    -- 向"employees"表中插入新员工的记录
    INSERT INTO employees (emp_id, emp_name, emp_dept) VALUES (101, 'John Doe', 'IT');

    -- 向"orders"表中插入一条新订单记录
    INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES (1001, 101, '2024-05-03', 500.00);

实现过程

实现一个拼接SQL语句的类

SQLSplicing.hpp

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
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
#pragma once
#include <vector>
#include <string>
#include <memory>


class SQLSplicing {

	friend class SQLBuilder;

private:
	const std::string AND = ") AND (";
	const std::string OR = ") OR (";

	enum class SplicingType
	{
		NONE = -1,
		INSERT,
		DELETE,
		UPDATE,
		SELECT,
		CREATE,
		DROP
	}splicingType;

	enum class TableType
	{
		NONE = -1,
		TABLE,
		VIEW,
		INDEX
	}tableType;

	std::vector<std::string> sets;
	std::vector<std::string> select;
	std::vector<std::string> tables;
	std::vector<std::string> indexs;
	std::vector<std::string> views;
	std::vector<std::string> join;
	std::vector<std::string> innerJoin;
	std::vector<std::string> outerJoin;
	std::vector<std::string> leftOuterJoin;
	std::vector<std::string> rightOuterJoin;
	std::vector<std::string> where;
	std::vector<std::string> having;
	std::vector<std::string> groupBy;
	std::vector<std::string> orderBy;
	std::vector<std::string>* lastList = nullptr;
	std::vector<std::string> columns;
	std::vector<std::vector<std::string>> valuesList;//插入
	bool distinct = false;
	bool unique = false;
	std::string offset;
	std::string limit;

	void sqlClause(std::string& builder, const std::string& keyword, const std::vector<std::string>& parts,
		const std::string& open, const std::string& close, const std::string& conjunction) {

		if (parts.empty())
			return;

		if (!builder.empty()) {
			builder.append(" ");
		}

		builder.append(keyword);
		builder.append(" ");
		builder.append(open);

		std::string last = "";
		for (int i = 0; i < parts.size(); ++i) {
			std::string part = parts[i];
			if (i > 0 && part != AND && part != OR && last != AND && last != OR) {
				builder.append(conjunction);
			}

			builder.append(part);
			last = part;
		}
		builder.append(close);

	}

	void appendLimitClause(std::string& builder, const std::string& offset, const std::string& limit) {
		if (!limit.empty()) {
			builder.append(" LIMIT ").append(limit);
		}
		if (!offset.empty()) {
			builder.append(" OFFSET ").append(offset);
		}
	}

	void joins(std::string& builder) {
		sqlClause(builder, "JOIN", join, "", "", " JOIN ");
		sqlClause(builder, "INNER JOIN", innerJoin, "", "", " INNER JOIN ");
		sqlClause(builder, "OUTER JOIN", outerJoin, "", "", " OUTER JOIN ");
		sqlClause(builder, "LEFT OUTER JOIN", leftOuterJoin, "", "", " LEFT OUTER JOIN ");
		sqlClause(builder, "RIGHT OUTER JOIN", rightOuterJoin, "", "", " RIGHT OUTER JOIN ");
	}

	void deleteSQL(std::string& builder) {
		sqlClause(builder, "DELETE FROM", tables, "", "", "");
		sqlClause(builder, "WHERE", where, "(", ")", " AND ");
		appendLimitClause(builder, "", limit);
	}

	void updateSQL(std::string& builder) {
		sqlClause(builder, "UPDATE", tables, "", "", "");
		joins(builder);
		sqlClause(builder, "SET", sets, "", "", "");
		sqlClause(builder, "WHERE", where, "(", ")", " AND ");
		appendLimitClause(builder, "", limit);
	}

	void insertSQL(std::string& builder) {
		sqlClause(builder, "INSERT INTO", tables, "", "", "");
		sqlClause(builder, "", columns, "(", ")", ", ");
		for (int i = 0; i < valuesList.size(); ++i) {
			std::string keyword = i > 0 ? "," : "VALUES";
			sqlClause(builder, keyword, valuesList[i], "(", ")", ", ");
		}
	}

	void selectSQL(std::string& builder) {
		if(distinct)
			sqlClause(builder, "SELECT DISTINCT", select, "", "", ", ");
		else
			sqlClause(builder, "SELECT", select, "", "", ", ");

		sqlClause(builder, "FROM", tables, "", "", ", ");
		joins(builder);
		sqlClause(builder, "WHERE", where, "(", ")", " AND ");
		sqlClause(builder, "GROUP BY", groupBy, "", "", ", ");
		sqlClause(builder, "HAVING", having, "(", ")", " AND ");
		sqlClause(builder, "ORDER BY", groupBy, "", "", ", ");
		appendLimitClause(builder, offset, limit);
	}

	void dropSQL(std::string& builder, TableType _type) {
		switch (_type)
		{
		case TableType::TABLE:
			sqlClause(builder, "DROP TABLE", tables, "", "", ", ");
			break;
		case TableType::VIEW:
			sqlClause(builder, "DROP VIEW", tables, "", "", ", ");
			break;
		case TableType::INDEX:
			sqlClause(builder, "DROP INDEX", tables, "", "", ", ");
			break;
		default:
			break;
		}
	}

	void createSQL(std::string& builder, TableType _type) {
		switch (_type)
		{
		case SQLSplicing::TableType::TABLE:
			sqlClause(builder, "CREATE TABLE", tables, "", "", ", ");
			break;
		case SQLSplicing::TableType::VIEW:
			sqlClause(builder, "CREATE VIEW", tables, "", "", ", ");
			break;
		case SQLSplicing::TableType::INDEX:
			if(unique)
				sqlClause(builder, "CREATE UNIQUE INDEX", tables, "", "", ", ");
			else
				sqlClause(builder, "CREATE INDEX", tables, "", "", ", ");
			break;
		default:
			break;
		}
	}

	std::string sql() {
		std::string strSQL;

		switch (splicingType)
		{
		case SplicingType::INSERT:
			insertSQL(strSQL);
			break;
		case SplicingType::DELETE:
			deleteSQL(strSQL);
			break;
		case SplicingType::UPDATE:
			updateSQL(strSQL);
			break;
		case SplicingType::SELECT:
			selectSQL(strSQL);
			break;
		case SplicingType::CREATE:
			createSQL(strSQL, tableType);
			break;
		case SplicingType::DROP:
			dropSQL(strSQL, tableType);
			break;
		default:
			strSQL = "";
			break;
		}
		return strSQL;
	}

public:
	SQLSplicing()
		: splicingType(SplicingType::NONE)
		, tableType(TableType::NONE)
	{
		valuesList.resize(1);
	}

	~SQLSplicing()
	{

	}
};

实现一个填写SQL语句的类

SQLBuilder.hpp

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
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
#pragma once
#include <string>
#include "SQLSplicing.hpp"

class SQLBuilder
{
public:
	SQLBuilder() {};
	~SQLBuilder() {};

	SQLBuilder& UPDATE(const std::string& table) {
		sql.splicingType = SQLSplicing::SplicingType::UPDATE;
		sql.tables.emplace_back(table);
		return *this;
	}

	SQLBuilder& SET(const std::string& set) {
		sql.sets.emplace_back(set);
		return *this;
	}

	SQLBuilder& INSERT_INTO(const std::string& tableName) {
		sql.splicingType = SQLSplicing::SplicingType::INSERT;
		sql.tables.emplace_back(tableName);
		return *this;
	}

	SQLBuilder& VALUES(const std::string& columns, const std::string& values) {
		INTO_COLUMNS(columns);
		INTO_VALUES(values);
		return *this;
	}

	SQLBuilder& INTO_COLUMNS(const std::string& columns) {
		sql.columns.emplace_back(columns);
		return *this;
	}

	SQLBuilder& INTO_VALUES(const std::string& values) {
		sql.valuesList.back().emplace_back(values);
		return *this;
	}

	SQLBuilder& SELECT(const std::string& columns) {
		sql.splicingType = SQLSplicing::SplicingType::SELECT;
		sql.select.emplace_back(columns);
		return *this;
	}

	SQLBuilder& SELECT_DISTINCT(const std::string& columns) {
		sql.distinct = true;
		return SELECT(columns);
	}

	SQLBuilder& DELETE_FROM(const std::string& table) {
		sql.splicingType = SQLSplicing::SplicingType::DELETE;
		sql.tables.emplace_back(table);
		return *this;
	}

	SQLBuilder& FROM(const std::string& table) {
		sql.tables.emplace_back(table);
		return *this;
	}

	SQLBuilder& JOIN(const std::string& join) {
		sql.join.emplace_back(join);
		return *this;
	}

	SQLBuilder& INNER_JOIN(const std::string& join) {
		sql.innerJoin.emplace_back(join);
		return *this;
	}

	SQLBuilder& OUTER_JOIN(const std::string& join) {
		sql.outerJoin.emplace_back(join);
		return *this;
	}

	SQLBuilder& LEFT_OUTER_JOIN(const std::string& join) {
		sql.leftOuterJoin.emplace_back(join);
		return *this;
	}

	SQLBuilder& RIGHT_OUTER_JOIN(const std::string& join) {
		sql.rightOuterJoin.emplace_back(join);
		return *this;
	}

	SQLBuilder& WHERE(const std::string& conditions) {
		sql.where.emplace_back(conditions);
		sql.lastList = &sql.where;
		return *this;
	}

	SQLBuilder& AND() {
		sql.lastList->emplace_back(sql.AND);
		return *this;
	}

	SQLBuilder& OR() {
		sql.lastList->emplace_back(sql.OR);
		return *this;
	}

	SQLBuilder& GROUP_BY(const std::string& columns) {
		sql.groupBy.emplace_back(columns);
		return *this;
	}

	SQLBuilder& HAVING(const std::string& conditions) {
		sql.having.emplace_back(conditions);
		sql.lastList = &sql.having;
		return *this;
	}

	SQLBuilder& ORDER_BY(const std::string& columns) {
		sql.orderBy.emplace_back(columns);
		return *this;
	}

	SQLBuilder& LIMIT(const std::string& variable, const std::string& offset) {
		sql.limit = variable;
		sql.offset = offset;
		return *this;
	}

	SQLBuilder& LIMIT(const std::string& variable) {
		sql.limit = variable;
		sql.offset = std::to_string(0);
		return *this;
	}

	SQLBuilder& DROP_TABLE(const std::string& table) {
		sql.splicingType = SQLSplicing::SplicingType::DROP;
		sql.tableType = SQLSplicing::TableType::TABLE;
		sql.tables.emplace_back(table);
		return *this;
	}

	SQLBuilder& DROP_INDEX(const std::string& table) {
		sql.splicingType = SQLSplicing::SplicingType::DROP;
		sql.tableType = SQLSplicing::TableType::INDEX;
		sql.tables.emplace_back(table);
		return *this;
	}

	SQLBuilder& DROP_VIEW(const std::string& table) {
		sql.splicingType = SQLSplicing::SplicingType::DROP;
		sql.tableType = SQLSplicing::TableType::VIEW;
		sql.tables.emplace_back(table);
		return *this;
	}

	SQLBuilder& CREATE_TABLE(const std::string& table) {
		sql.splicingType = SQLSplicing::SplicingType::CREATE;
		sql.tableType = SQLSplicing::TableType::TABLE;
		sql.tables.emplace_back(table);
		return *this;
	}

	SQLBuilder& CREATE_INDEX(const std::string& table) {
		sql.splicingType = SQLSplicing::SplicingType::CREATE;
		sql.tableType = SQLSplicing::TableType::INDEX;
		sql.tables.emplace_back(table);
		return *this;
	}

	SQLBuilder& CREATE_UNIQUE_INDEX(const std::string& table) {
		sql.unique = true;
		return CREATE_INDEX(table);
	}

	SQLBuilder& CREATE_VIEW(const std::string& table) {
		sql.splicingType = SQLSplicing::SplicingType::CREATE;
		sql.tableType = SQLSplicing::TableType::VIEW;
		sql.tables.emplace_back(table);
		return *this;
	}

	/// <summary>
	/// INSERT插入多行时，需要调用添加行
	/// </summary>
	/// <returns>SQLBuilder</returns>
	SQLBuilder& ADD_ROW() {
		sql.valuesList.resize(sql.valuesList.size() + 1);
		return *this;
	}

	std::string toString() {
		return sql.sql();
	}

private:
	SQLSplicing sql;
};

测试输出

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
#include <iostream>
#include "SQLBuilder.hpp"

int main()
{
    auto sql1 = SQLBuilder()
        .SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME")
        .SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON")
        .FROM("PERSON P")
        .FROM("ACCOUNT A")
        .INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID")
        .INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID")
        .WHERE("P.ID = A.ID")
        .WHERE("P.FIRST_NAME like 'a*s'")
        .OR()
        .WHERE("P.LAST_NAME like '*s'")
        .GROUP_BY("P.ID")
        .HAVING("P.LAST_NAME like 'ak*as'")
        .OR()
        .HAVING("P.FIRST_NAME like '_ak'")
        .ORDER_BY("P.ID")
        .ORDER_BY("P.FULL_NAME")
        .LIMIT("10", "10")
        .toString();

    auto sql2 = SQLBuilder()
        .INSERT_INTO("PERSON")
        .VALUES("ID, FIRST_NAME", "1314, TOM")
        .VALUES("LAST_NAME", "JERRY")
        .toString();

    auto sql3 = SQLBuilder()
        .INSERT_INTO("TABLE_A")
        .INTO_COLUMNS("a,b,c,d")
        .INTO_VALUES("1,2,3,4")
        .ADD_ROW()
        .INTO_VALUES("5,6")
        .INTO_VALUES("7,8")
        .toString();

    auto sql4 = SQLBuilder()
        .DELETE_FROM("PERSON")
        .WHERE("id = 520")
        .LIMIT("20")
        .toString();

    auto sql5 = SQLBuilder()
        .UPDATE("PERSON")
        .SET("name=?")
        .WHERE("id=10")
        .toString();

    auto sql6 = SQLBuilder()
        .DROP_VIEW("PERSON")
        .toString();

    auto sql7 = SQLBuilder()
        .CREATE_UNIQUE_INDEX("idx_employee_name ON EMPLOYEE (EMPLOYEE_NAME)")
        .toString();

	system("pause");
	return 0;
}

输出如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME, P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON FROM PERSON P, ACCOUNT A INNER JOIN DEPARTMENT D on D.ID = P.DEPARTMENT_ID INNER JOIN COMPANY C on D.COMPANY_ID = C.ID WHERE (P.ID = A.ID AND P.FIRST_NAME like 'a*s') OR (P.LAST_NAME like '*s') GROUP BY P.ID HAVING (P.LAST_NAME like 'ak*as') OR (P.FIRST_NAME like '_ak') ORDER BY P.ID LIMIT 10 OFFSET 10

INSERT INTO PERSON  (ID, FIRST_NAME, LAST_NAME) VALUES (1314, TOM, JERRY)

INSERT INTO TABLE_A  (a,b,c,d) VALUES (1,2,3,4) , (5,6, 7,8)

DELETE FROM PERSON WHERE (id = 520) LIMIT 20

UPDATE PERSON SET name=? WHERE (id=10)

DROP VIEW PERSON

CREATE UNIQUE INDEX idx_employee_name ON EMPLOYEE (EMPLOYEE_NAME)