local FreeSwitchDB = {}
FreeSwitchDB.__index = FreeSwitchDB
function FreeSwitchDB:new()
local instance = setmetatable({}, FreeSwitchDB)
instance.conn = nil
return instance
end
— 连接数据库
function FreeSwitchDB:connect()
self.conn = freeswitch.Dbh(“core:core”)
if not self.conn:connected() then
error(“Failed to connect to database”)
end
end
— 执行SQL查询
function FreeSwitchDB:query(sql, params)
if not self.conn then
error(“Database connection not established”)
end
local result = {}
local success, err = self.conn:query(sql, params, function(row)
table.insert(result, row)
end)
if not success then
error(“Query failed: ” .. tostring(err))
end
return result
end
— 执行SQL语句（INSERT/UPDATE/DELETE）
function FreeSwitchDB:execute(sql, params)
if not self.conn then
error(“Database connection not established”)
end
local success, err = self.conn:execute(sql, params)
if not success then
error(“Execute failed: ” .. tostring(err))
end
return self.conn:affected_rows()
end
— 关闭数据库连接
function FreeSwitchDB:close()
if self.conn then
self.conn:release()
self.conn = nil
end
end
return FreeSwitchDB
———————
local FreeSwitchDB = require(“interface.FreeSwitchDB”)
local UserModel = {}
UserModel.__index = UserModel
function UserModel:new()
local instance = setmetatable({}, UserModel)
instance.db = FreeSwitchDB:new()
instance.db:connect()
return instance
end
— 创建用户
function UserModel:create(name, phone_number)
local query = “INSERT INTO user (name, phone_number) VALUES (?, ?)”
local params = {name, phone_number}
return self.db:execute(query, params)
end
— 根据ID查询用户
function UserModel:read_by_id(id)
local query = “SELECT * FROM user WHERE id = ?”
local params = {id}
return self.db:query(query, params)
end
— 根据电话号码查询用户
function UserModel:read_by_phone(phone_number)
local query = “SELECT * FROM user WHERE phone_number = ?”
local params = {phone_number}
return self.db:query(query, params)
end
— 查询用户列表
function UserModel:list(limit, offset, order_by, order_direction)
local query = “SELECT * FROM user”
local params = {}
— 添加排序
if order_by then
local direction = order_direction == “desc” and “DESC” or “ASC”
query = query .. ” ORDER BY ” .. order_by .. ” ” .. direction
end
— 添加分页
if limit then
query = query .. ” LIMIT ?”
table.insert(params, limit)
if offset then
query = query .. ” OFFSET ?”
table.insert(params, offset)
end
end
return self.db:query(query, params)
end
— 更新用户信息
function UserModel:update(id, name, phone_number)
local query = “UPDATE user SET name = ?, phone_number = ? WHERE id = ?”
local params = {name, phone_number, id}
return self.db:execute(query, params)
end
— 删除用户
function UserModel:delete(id)
local query = “DELETE FROM user WHERE id = ?”
local params = {id}
return self.db:execute(query, params)
end
— 关闭数据库连接
function UserModel:close()
self.db:close()
end
return UserModel
转载请注明：SuperIT » freeswitch db操作
