网页通过node.js http server管理sqlite数据库内容
2015年12月26日：
//查询数据库内容
function test() {
var data = {};
//设置表名称
data.tableName = 'eqpt_info';
//设置返回的列；如果不设置的话会默认返回所有列
data.tableRows = 'seq, eqpt_code, eqpt_name, eqpt_type, eqpt_address, create_time';
//设置查询条件中的使用=（相等）查询的的部分
data.equal = {};
//相当于查询 where eqpt_type = '0a0003ahup'
data.equal.eqpt_type = '0a0003ahup';
//设置查询条件中的使用like（条件）查询的的部分
data.like = {};
//相当于查询 where eqpt_code like '%12%'
data.like.eqpt_code = '12';
data.like.eqpt_name = '700';
data = JSON.stringify(data);
//javascript对象格式化为字符串
// data = '{"tableName":"eqpt_info","like":{"eqpt_code":"12","eqpt_name":"700"},"equal":{"eqpt_type":"0a0003ahup"}}';
$.ajax({
url:"http://10.168.1.156:8816/getTableList",
// async:true,
data:data,
type:"POST",
dataType:'text',
success:function(data) {
var data = JSON.parse(data);
console.log(data[0]);
return;
},
error:function(XMLHttpReq, textStatus, error){
alert(XMLHttpReq + ',' + textStatus + ',' + error);
}
});
}
使用post命令向中间件的http服务器发送字符串数据： '{"tableName":"eqpt_info","tableRows":"seq, eqpt_code, eqpt_name, eqpt_type, eqpt_address, create_time","like":{"eqpt_code":"12","eqpt_name":"700"},"equal":{"eqpt_type":"0a0003ahup"}}'
后台执行的sql语句是：
select seq, eqpt_code, eqpt_name, eqpt_type, eqpt_address, create_time from eqpt_info where 1=1 and eqpt_type = '0a0003ahup' and eqpt_code like '%12%' and eqpt_name like '%700%'
返回的数据是字符串（这里只是其中三条的演示，具体返回内容由传入参数和数据库内容确定）：
'[{"seq":5421,"eqpt_code":"000ec7000112","eqpt_name":"000ec7000112","eqpt_type":"0a0003ahup","eqpt_address":null,"create_time":"2015-07-24 10:03:31"},{"seq":5429,"eqpt_code":"000ec7000120","eqpt_name":"000ec7000120","eqpt_type":"0a0003ahup","eqpt_address":null,"create_time":"2015-07-24 09:55:23"},{"seq":5430,"eqpt_code":"000ec7000121","eqpt_name":"000ec7000121","eqpt_type":"0a0003ahup","eqpt_address":null,"create_time":"2015-07-24 09:56:11"}]'
可以使用javascript的JONS.parse(str)函数将其解析为对象，也可以使用其它语言的类似函数自编程序解析此字符串，其中每一行数据将被解析为一个对象，例如第一行数据：
data[0] = {
"seq":5421,
"eqpt_code":"000ec7000112",
"eqpt_name":"000ec7000112",
"eqpt_type":"0a0003ahup",
"eqpt_address":null,
"create_time":"2015-07-24 10:03:31"
};
function getTableList(res, data) {
var data = JSON.parse(data);
// var data = {
// tableName: 'eqpt_info',
// tableRows: 'seq, eqpt_code, eqpt_name, eqpt_type, eqpt_address, create_time',
// like: { eqpt_code: '12', eqpt_name: '700' },
// equal: { eqpt_type: '0a0003ahup' }
// };
console.log(data);
// console.log(data.tableName);
// console.log(data.like.eqpt_code);
//组合表和字段
var returnRows = '';
if(data.tableRows) {
returnRows = data.tableRows;
} else {
returnRows = table[data.tableName];
}
var sql = 'select ' + returnRows + ' from ' + data.tableName;
//组合查询条件
if(data.like || data.equal) {
sql = sql + ' where 1=1 ';
if(data.equal) {
for(k in data.equal) {
sql = sql + ' and ' + k + ' = \'' + data.equal[k] + '\'';
}
}
if(data.like) {
for(k in data.like) {
sql = sql + ' and ' + k + ' like \'%' + data.like[k] + '%\'';
}
}
};
console.log(sql);
sql = sql + ' LIMIT 3';
// sql = "select seq, eqpt_code, eqpt_name, eqpt_type, eqpt_address, create_time from eqpt_info where 1=1 and eqpt_type = '0a0003ahup' and eqpt_code like '%12%' and eqpt_name like '%700%'";
db.all(sql, function(err, rows) {
if(!err) {
if(rows) {
rows = JSON.stringify(rows);
console.log(rows);
// rows = '[{"seq":5421,"eqpt_code":"000ec7000112","eqpt_name":"000ec7000112","eqpt_type":"0a0003ahup","eqpt_address":null,"create_time":"2015-07-24 10:03:31"},{"seq":5429,"eqpt_code":"000ec7000120","eqpt_name":"000ec7000120","eqpt_type":"0a0003ahup","eqpt_address":null,"create_time":"2015-07-24 09:55:23"},{"seq":5430,"eqpt_code":"000ec7000121","eqpt_name":"000ec7000121","eqpt_type":"0a0003ahup","eqpt_address":null,"create_time":"2015-07-24 09:56:11"}]';
res.write(rows);
} else {
res.write('没有查询到符合的数据');
};
res.end();
} else {
console.log(err);
res.end(err);
};
});
// res.end('123');
}
