最近搞一个小程式,打算从原来的MySQL,转移到postgreSql阵线,aardio中对postgreSql的使用,好像没多少人在用,好在官方有一个库
所以在官方libpg原版的基础上扩展了一些方便使用的方法,实际使用,还需要进一步完善。
添加了事务函数,批量插入,简化增,删,改,查,以及构建原始SQL语句等

演示代码:
Code AardioLine:129复制
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.import
postgreSql;import
consolevar
db,err = postgreSql(- host =
"localhost"
; - port =
5432
; - dbname =
"aardioTest"
; - user =
"postgres"
; - password =
"haosql"
- );
if
(!db){- console.log(
"数据库连接失败"
); -
return
; - }
var
ok,err = db.exec("- DROP
TABLE
IF
EXISTS test_users; - CREATE
TABLE
test_users ( - id SERIAL PRIMARY KEY,
- name VARCHAR(
50
) NOT
NULL
, - age INTEGER,
- email VARCHAR(
100
), - created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
- );
- ");
if
(!ok){- console.log(
"创建表失败:"
,err); -
return
; - }
- db.tableName =
"test_users"
- console.dump(
"快速插入,返回Id"
,db.insert({ - name =
"aardio"
, - email =
"aardio@163.com"
- }))
- console.dump(
"快速更新,返回执行结果"
,db.update("test_users"
,{ - name =
"aardio"
, - age =
30
, - email =
"console@163.com"
- },{name=
"aardio"
})) - console.dump(
"快速删除,返回执行结果"
,db.delete({name="aardio"
})) - sql,v = db.buildSql(
"insert"
,"test_users"
,{ - namex =
"张三"
, - age =
30
- })
- console.dump(
"生成插入语句:"
,sql); - sql1,v1 = db.buildSql(
"update"
,"test_users"
,{ - name =
"李四季"
, - age =
30
- },
- {
- name =
"张三"
- })
- console.dump(
"生成更新语句:"
,sql1); - sql2,v2 = db.buildSql(
"delete"
,"test_users"
,,{ - name =
"张三"
, - age =
30
- })
- console.dump(
"生成删除语句:"
,sql2); - console.dump(
"执行事务失败"
) - console.dump(
""
) - db.beginTran()
- res,err = db.execParams(sql,
table
.unpack(v)) - console.dump(
"事务执行插入"
,res,err) - res,err = db.execParams(sql1,
table
.unpack(v1)) - console.dump(
"事务执行更新"
,res,err) - res,err = db.execParams(sql2,
table
.unpack(v2)) - console.dump(
"事务执行删除"
,res,err) if
(err) {- db.rollback()
- }
else
{ - db.commitTran()
- }
- sql,v = db.buildSql(
"insert"
,"test_usersx"
,{ - name =
"张三"
, - age =
30
- })
- console.dump(
"执行事务"
) try
{- db.beginTran()
-
assert
(db.execParams(sql,table
.unpack(v))) -
assert
(db.execParams(sql1,table
.unpack(v1))) -
assert
(db.execParams(sql2,table
.unpack(v2))) - db.commitTran()
- console.dump(
"事务执行成功"
) - }
catch
(e){- db.rollback()
- console.dump(
"事务执行失败"
,e) - }
var
sortedUsers,err = db.query("test_usersx"
,db.formatSqlParameters("age>10 and name=@name"
,{name="李四季"
}),{"id"
,"name"
});- console.dump(
"查询失败"
,sortedUsers,err) var
sortedUsers,err = db.query("test_users"
,db.formatSqlParameters("age>10 and name=@name"
,{name="李四季"
}),{"id"
,"name"
});- console.dump(
"查询成功"
,sortedUsers,err) var
result,err = db.query("test_users"
, "age between 18 and 30"
, "*"
, {- age =
"desc"
- },
5
, 0
, - );
- console.dump(
"复杂查询:"
,result,err) var
data = {- {name=
"张三"
,age=20
}, - {name=
"李四"
,age=21
}, - {name=
"王五"
,age=22
} - }
- console.dump(
"批量插入"
,db.batchInsert("test_users"
,data)); - console.dump(
"单条记录:"
,db.getOne("test_users"
,{name="李四"
})); - console.dump(
"记录数:"
,db.count("test_users"
,{name="李四"
})); - console.dump(
"是否存在:"
,db.exists("test_users"
,{name="王五"
})); - db.close();
- console.pause();