TA的每日心情 | 奋斗 2024-3-5 11:13 |
---|
签到天数: 93 天 连续签到: 2 天 [LV.6]常住居民II
金牌会员
- 积分
- 1105
|
本帖最后由 心淡如猫 于 2022-7-12 10:34 编辑
工具类代码:
[TypeScript] 纯文本查看 复制代码 // 数据库管理
import { logger } from "./Log"
const BBMIR = 'bbmir'
/**
* 数据库操作类
*/
class Db {
/**
* 初始化数据库连接
*/
public initConnection = () => {
if (GameLib.R.dbReady) {
return
}
const host = 'localhost'
const port = 3306
const dbName = BBMIR
const user = 'root'
const pass = ''
let ret = false
let tryTimes = 10
while (!ret && tryTimes > 0) {
tryTimes--
try {
ret = GameLib.DBEngine.AddConection(BBMIR, host, port, user, pass, dbName)
} catch (err) {
logger.debug(err)
if (err.message.startsWith('已经存在相同的配置名称')) {
ret = true
break
}
}
}
logger.info(ret ? '数据库连接成功' : ' 数据库连接失败')
GameLib.R.dbReady = ret
}
/**
* 检查数据连接
*/
public checkDb = () => {
if (!GameLib.R.dbReady) {
this.initConnection()
}
if (!GameLib.R.dbReady) {
logger.error('数据库异常')
throw Error('数据库异常, 无法连接')
}
}
/**
* 校验SQL,格式化
* @param sql
* @param params
*/
public validate = (sql: string, params: any[]) => {
for (const param of params) {
if (/update|delete|exist|alter|create|drop/.test(String(param).toLowerCase())) {
logger.error('SQL中有非法参数')
throw Error('SQL中有非法参数')
}
if (typeof param == 'number') {
sql = sql.replace('?', String(param))
} else if (typeof param == 'string') {
sql = sql.replace('?', `'${param}'`)
} else if (Array.isArray(param)) {
sql = this.parseArrayParams(sql, param)
} else {
sql = sql.replace('?', `'${param}'`)
}
}
logger.debug('sql', sql)
return sql
}
/**
* 解析数组类型参数
* @param sql
* @param param
* @returns
*/
public parseArrayParams = (sql: string, param: any[]) => {
if (param.length == 0) {
// 空数组
sql = sql.replace('?', '')
} else {
// 数字数组
if (typeof param[0] == 'number') {
sql = sql.replace('?', param.join(','))
} else {
// 字符串数组
sql = sql.replace('?', param.map(x => `'${x}'`).join(','))
}
}
return sql
}
/**
* 执行SQL查询
* @param sql
* @param params
*/
public query = <T> (sql: string, ...params: any) => {
return new Promise((resolve: (rows: T[]) => void, reject: (err?: any) => void) => {
this.checkDb()
GameLib.DBEngine.QueryAsyncEx(BBMIR, this.validate(sql, params), (err, result) => {
if (err) {
reject(err)
return
}
const rows = []
while (!result.Eof()) {
this.parseRow(result, rows)
result.Next()
}
resolve(rows)
})
})
}
/**
* 查询SQL返回第一行第一列值
* @param sql
* @param params
*/
public queryScalar = <T> (sql: string, ...params: any) => {
return new Promise((resolve: (value: T) => void, reject: (err?: any) => void) => {
this.checkDb()
GameLib.DBEngine.QueryAsyncEx(BBMIR, this.validate(sql, params), (err, result) => {
if (err) {
reject(err)
return
}
const rows = []
this.parseRow(result, rows)
let value: any
if (rows.length > 0) {
const row = rows[0]
const name = Object.getOwnPropertyNames(row)[0]
value = row[name]
}
resolve(value)
})
})
}
/**
* 查询SQL返回第一行
* @param sql
* @param params
*/
public queryOne = <T> (sql: string, ...params: any) => {
return new Promise((resolve: (value: T) => void, reject: (err?: any) => void) => {
this.checkDb()
GameLib.DBEngine.QueryAsyncEx(BBMIR, this.validate(sql, params), (err, result) => {
if (err) {
reject(err)
return
}
const rows = []
this.parseRow(result, rows)
if (rows.length > 0) {
const row = rows[0]
resolve(row)
} else {
resolve(null)
}
})
})
}
/**
* 解析行
* @param ds
* @param rows
* @returns
*/
public parseRow = (ds: TDataSet, rows: any[]) => {
if (ds.IsEmpty()) {
return
}
const row = {}
for (let i = 0; i < ds.GetFieldCount(); i++) {
const field = ds.GetField(i)
row[field.GetFieldName()] = this.parseFieldValue(field)
}
rows.push(row)
}
/**
* 解析列
* @param field
* @returns
*/
public parseFieldValue = (field: TField) => {
if (field.IsInt()) {
return parseInt(field.AsString)
}
if (field.IsFloat()) {
return field.AsFloat
}
if (field.IsDateTime()) {
return field.AsDateTime
}
return field.AsString
}
/**
* 执行SQL更新、删除
* @param sql
* @param cb
*/
public execute = (sql: string, ...params: any) => {
return new Promise((resolve: (result: boolean) => void, reject: (err?: any) => void) => {
this.checkDb()
GameLib.DBEngine.ExecAsyncEx(BBMIR, this.validate(sql, params), (err, affectRow) => {
if (err) {
reject(err)
return
}
resolve(affectRow > 0)
})
})
}
/**
* 获取事务id
* @param Player
* @param key
*/
public getTranID = (Player: TPlayObject, key: string) => {
return `${Player.PlayerID}-${key}-${randomRange(0, 1000)}`
}
}
export default new Db()
使用样例:
查询列表
[TypeScript] 纯文本查看 复制代码 // 获取所有委托单
export const getAllCommissionOrders = async (moneyType: MoneyType, limit: number = 7) => {
let ORDER = 'DESC'
if (moneyType == MoneyType.YB) {
ORDER = 'ASC'
}
return await Db.query<CommissionOrder>(`
SELECT *
FROM CommissionOrder
WHERE ServerID=? AND SellMoneyType=?
ORDER BY Price ${ORDER} LIMIT ${limit}`,
GameLib.ServerID, moneyType
)
}
查询一行
[TypeScript] 纯文本查看 复制代码 // 获取当前委托单
export const getCommissionOrder = async (Player: TPlayObject) => {
return await Db.queryOne<CommissionOrder>(
`SELECT * FROM CommissionOrder WHERE ServerID=? AND PlayerID=?`,
GameLib.ServerID, Player.PlayerID
)
}
更新数据
[TypeScript] 纯文本查看 复制代码 // 保存资产
export const saveAsset = async (Asset: AssetHosting) => {
const asset = await getAsset(Asset.PlayerID, Asset.MoneyType)
// 更新
if (asset) {
await Db.execute(`
UPDATE AssetHosting SET Balance=? WHERE PlayerID=? AND ServerID=? AND MoneyType=?`,
Asset.Balance, Asset.PlayerID, Asset.ServerID, Asset.MoneyType
)
} else {
// 插入
await Db.execute(`
INSERT INTO AssetHosting VALUES (?, ?, ?, ?)`,
Asset.PlayerID, Asset.ServerID, Asset.MoneyType, Asset.Balance
)
}
}
删除数据
[TypeScript] 纯文本查看 复制代码 // 数量为0则删除
await Db.execute(`DELETE FROM CommissionOrder WHERE OrderID=?`, order.OrderID)
|
评分
-
查看全部评分
|