这篇node.js操作postgresql数据库,采用的是两个文件的方式实现,一个文件相当于客户端,实现连接数据库,调用相应的函数,而另一个文件是具体实现函数的。
在你安装了node.js,postgresql数据库(不一定是本机)和相应的模块的基础上,在数据库中建表teacher。并插入几条测试数据,这儿插入的测试数据为:
[sql]
create table teacher(id varchar(10),name varchar(20),pwd varchar(10));
[sql]
insert into teacher values('1','aaa','111');
insert into teacher values('2','bbb','222');
insert into teacher values('3','ccc','333');
insert into teacher values('4','ddd','444');
1) client.js
[cpp]
var f = require('./function');
var pg = require('pg');
var conString = "tcp://postgres:postgres@localhost/my";
var client = new pg.Client(conString);
var value = ['10','fillp','abc'];
insertSQLString = 'insert into teacher values($1,$2,$3)';
selectSQLString = 'select * from teacher';
updateSQLString = "update teacher set NAME='ipone' where ID='4'";
deleteSQLString = "delete from teacher where ID='10'";
client.connect(function(error, results) {
if(error){
console.log('ClientConnectionReady Error: ' + error.message);
client.end();
return;
}
console.log('Connecting to postgres...');
console.log('Connected to postgres automatically.');
console.log('connection success...\n');
f._select(client,selectSQLString);
f._insert(client,insertSQLString,value);
f._select(client,selectSQLString);
f._delete(client,deleteSQLString);
});
2) function.js
[cpp]
<span style="font-size:14px;"><span style="font-size:12px;">function _insert(client,insertSQLString,value)
{
console.log("insert beginning");
client.query(insertSQLString, value, function(error, results)
{
if(error)
{
console.log("ClientReady Error: " + error.message),
client.end();
return;
}
console.log('Inserted: ' + results.affectedRows + ' row.'),
console.log('insert success...\n');
});
console.log("insert end\n");
}
function _select(client,selectSQLString)
{
console.log("select beginning");
client.query(selectSQLString, function selectCb(error, results, fields)
{
console.log("in select callback function");
if (error)
{
console.log('GetData Error: ' + error.message),
client.end();
return;
}
if(results.rowCount > 0)
{
var firstResult,
resultSet = '';
for(var i = 0, len = results.rowCount; i < len; i++)
{
firstResult = results.rows[i];
resultSet += 'id:' + firstResult['id'] + ' ' + 'name:' + firstResult['name'] + ' ' +
'pwd:' + firstResult['pwd'] + '\n';
}
}
console.log(resultSet);
/* 添加功能:使查询结果集返回到客户端并保证此函数的通用性. */
});
console.log("select end\n");
}
function _update(client,updateSQLString)
{
console.log("update beginning");
client.query(updateSQLString,function(error, results)
{
if(error)
{
console.log("ClientReady Error: " + error.message),
client.end();
return;
}
console.log('update success...\n');
});
console.log("update end\n");
}
function _delete(client,deleteSQLString)
{
console.log("delete beginning");
client.query(deleteSQLString, function(error, results)
{
if(error)
{
console.log("ClientReady Error: " + error.message),
client.end();
return;
}
console.log('delete success...\n');
});
console.log("delete end\n");
}
补充:web前端 , JavaScript ,