// 导入 mysql2
const mysql = require('mysql2');
class Database {
constructor(config) {
this.pool = mysql.createPool({
host: '192.168.102.252',
user: 'root',
password: '8141b9fdc40e751d',
database: 'szxmxyyos'
}).promise();
this.reset();
}
// 重置查询状态
reset() {
this.queryType = 'select'; // 默认查询类型
this.tableName = '';
this.fields = '*';
this.conditions = '';
this.data = {};
this.limitValue = '';
this.orderBy = '';
this.queryParams = [];
}
// 设置表名
table(tableName) {
this.tableName = tableName;
return this;
}
// 指定查询字段
field(fields = '*', include = true) {
if (fields === '*') {
this.fields = '*';
return this;
}
let fieldArray = [];
// 处理不同类型的输入
if (typeof fields === 'string') {
// 处理字符串输入
fieldArray = fields.split(',').map(f => f.trim());
} else if (Array.isArray(fields)) {
// 处理数组输入
fieldArray = fields;
} else if (typeof fields === 'object') {
// 处理 JSON 对象输入
fieldArray = Object.keys(fields);
}
// 如果是单个字段的情况，确保它是数组
if (typeof fieldArray === 'string') {
fieldArray = [fieldArray];
}
// 包含模式（include = true，默认）
if (include) {
this.fields = fieldArray.join(', ');
this.excludeFields = null; // 清除可能存在的排除字段
} else {
// 排除模式
this.excludeFields = fieldArray;
this.fields = null; // 清除可能存在的包含字段
}
return this;
}
// 获取表的所有字段
async getAllTableFields() {
const [columns] = await this.pool.query(
`SHOW COLUMNS FROM ${this.tableName}`
);
return columns.map(col => col.Field);
}
// WHERE 条件
where(conditions, params = []) {
// 如果是字符串形式，保持原有的处理方式
if (typeof conditions === 'string') {
this.conditions = `WHERE ${conditions}`;
this.queryParams = params;
return this;
}
// 处理 JSON 对象形式
if (typeof conditions === 'object') {
const whereConditions = [];
this.queryParams = [];
for (let [key, value] of Object.entries(conditions)) {
// 处理不同类型的值
if (value === null) {
whereConditions.push(`${key} IS NULL`);
} else if (Array.isArray(value)) {
// 处理 IN 查询
const placeholders = value.map(() => '?').join(',');
whereConditions.push(`${key} IN (${placeholders})`);
this.queryParams.push(...value);
} else if (typeof value === 'object') {
// 处理操作符 {'>': 100, '<': 200} 这样的形式
for (let [operator, operatorValue] of Object.entries(value)) {
let sqlOperator = operator;
switch (operator.toLowerCase()) {
case 'gt': sqlOperator = '>'; break;
case 'gte': sqlOperator = '>='; break;
case 'lt': sqlOperator = '<'; break;
case 'lte': sqlOperator = '<='; break;
case 'neq': sqlOperator = '!='; break;
case 'like': sqlOperator = 'LIKE'; break;
}
whereConditions.push(`${key} ${sqlOperator} ?`);
this.queryParams.push(operatorValue);
}
} else {
// 普通等值查询
whereConditions.push(`${key} = ?`);
this.queryParams.push(value);
}
}
if (whereConditions.length > 0) {
this.conditions = `WHERE ${whereConditions.join(' AND ')}`;
}
}
return this;
}
// 设置数据 (用于 INSERT 和 UPDATE)
data(data) {
this.data = data;
return this;
}
// 限制返回记录数
limit(limit) {
this.limitValue = `LIMIT ${limit}`;
return this;
}
// 排序
order(order) {
this.orderBy = `ORDER BY ${order}`;
return this;
}
// 执行 SELECT 查询
select() {
this.queryType = 'select';
return this.execute();
}
// 执行 INSERT 操作
insert() {
this.queryType = 'insert';
return this.execute();
}
// 执行 UPDATE 操作
update() {
this.queryType = 'update';
return this.execute();
}
// 执行 DELETE 操作
delete() {
this.queryType = 'delete';
return this.execute();
}
// 构建并执行查询
async execute() {
// 如果有需要排除的字段，先处理
if (this.excludeFields) {
const [columns] = await this.pool.query(
`SHOW COLUMNS FROM ${this.tableName}`
);
const allFields = columns.map(col => col.Field);
this.fields = allFields
.filter(field => !this.excludeFields.includes(field))
.join(', ');
}
let query = '';
switch (this.queryType) {
case 'select':
query = `SELECT ${this.fields} FROM ${this.tableName} ${this.conditions} ${this.orderBy} ${this.limitValue}`.trim();
break;
case 'insert':
const fields = Object.keys(this.data).join(', ');
const placeholders = Object.keys(this.data).map(() => '?').join(', ');
query = `INSERT INTO ${this.tableName} (${fields}) VALUES (${placeholders})`;
this.queryParams = Object.values(this.data);
break;
case 'update':
const setClause = Object.keys(this.data)
.map((key) => `${key} = ?`)
.join(', ');
query = `UPDATE ${this.tableName} SET ${setClause} ${this.conditions}`.trim();
this.queryParams = Object.values(this.data).concat(this.queryParams);
break;
case 'delete':
query = `DELETE FROM ${this.tableName} ${this.conditions}`.trim();
break;
default:
throw new Error('Invalid query type');
}
try {
const [rows] = await this.pool.query(query, this.queryParams);
this.reset(); // 查询后重置状态
return rows;
} catch (error) {
this.reset(); // 查询出错后也重置状态
throw error;
}
}
// 查询单条记录
async find(id = null) {
if (id !== null) {
// 如果提供了 id，自动添加 where 条件
this.where('id = ?', [id]);
}
// 自动添加 LIMIT 1
this.limit(1);
const rows = await this.select();
return rows[0] || null; // 返回第一条记录或 null
}
}
module.exports = Database;
调用示例
// 导入 Express const express = require('express'); const Database = require('./database'); // 创建一个 Express 应用 const app = express();
const db = new Database();
// 设置一个路由，处理 GET 请求
app.get('/', async (req, res) => {
// 1. 基本等值查询
const user1 = await db
.table('bk_consult')
.where({
id: 3,
consult_name: '张三'
})
.find();
// 2. NULL 值查询
const user2 = await db
.table('bk_consult')
.where({
consult_name: null
})
.select();
// 3. IN 查询
const user3 = await db
.table('bk_consult')
.where({
id: [1, 2, 3] // WHERE id IN (1,2,3)
})
.select();
// 4. 比较运算符
const user4 = await db
.table('bk_consult')
.where({
id: {
gt: 10, // 大于
lt: 20 // 小于
},
consult_name: {
like: '%张%' // LIKE 查询
}
})
.select();
// 5. 仍然支持原来的字符串方式
const user5 = await db
.table('bk_consult')
.where('id = ? AND consult_name = ?', [3, '张三'])
.find();
res.json({
user1,
user2,
user3,
user4,
user5
});
});
// 启动服务，监听端口
const PORT = 3000;
app.listen(PORT, () => {
console.log(Server is running on http://localhost:${PORT});
});