For the impatients, try the demo here: http://kripken.github.io/sql.js/GUI/
sql.js is a port of SQLite to JavaScript, by compiling the SQLite C code with Emscripten. It uses a virtual database file stored in memory, and thus does’nt persist the changes made to the database. However, it allows you to import any existing sqlite file, and to export the created database as a javascript typed array.
There is no C bindings or node-gyp compilation here, sql.js is a simple javascript file, that can be used like any traditional javascript library. If you are building a native application in javascript (using Electron for instance), or are working in node.js, you will likely prefer to use a native binding of SQLite to javascript.
functiontest(){var fs =require("fs");//Create the databasevar db =newSQL.Database();// NOTE: You can also use new sql.Database(data) where// data is an Uint8Array representing an SQLite database file// Execute some sql
sqlstr ="CREATE TABLE hello (a int, b char);";
sqlstr +="INSERT INTO hello VALUES (0, 'hello');"
sqlstr +="INSERT INTO hello VALUES (1, 'world');"
db.run(sqlstr);// Run the query without returning anythingvar res = db.exec("SELECT * FROM hello");// Prepare an sql statementvar stmt = db.prepare("SELECT * FROM hello WHERE a=:aval AND b=:bval");// Bind values to the parameters and fetch the results of the queryvar result = stmt.getAsObject({':aval':1,':bval':'world'});alert(result);// Will print {a:1, b:'world'}// Bind other values
stmt.bind([0,'hello']);while(stmt.step())alert(stmt.get());// Will print [0, 'hello']// free the memory used by the statement
stmt.free();// You can not use your statement anymore once it has been freed.// But not freeing your statements causes memory leaks. You don't want that.// Export the database to an Uint8Array containing the SQLite database filevar binaryArray = db.export();var buffer =newBuffer(binaryArray);
fs.writeFileSync("filename.sqlite", buffer);}
也可以使用自己新建的sqlite数据库文件
导入方式：
var fs =require('fs');var filebuffer=fs.readFileSync('test.sqlite');// Load the dbvar db=newSQL.Database(filebuffer);// Exportthe database to an Uint8Array containing the SQLite databasefile
varbinaryArray = db.export();var buffer =newBuffer(binaryArray);
fs.writeFileSync("test.sqlite", buffer);
<!DOCTYPE html><html><head><metahttp-equiv="Content-type"content="text/html; charset=UTF-8"/><scripttype="text/javascript"src="sql.js"></script></head><body><buttonname="test"type="button"value="val"onclick="save()">Button Text</button><inputid="uploadInput"type="file"onchange="getfile()"></body><script>functionsave(){//Create the databasevar db =newwindow.SQL.Database();
db.run("CREATE TABLE test (列1 int, 列2 char);");
db.run("INSERT INTO test VALUES (0,'hello');");
db.run("INSERT INTO test VALUES (1,'world');");
db.run("INSERT INTO test VALUES (55,'34534545');");
db.run("INSERT INTO test VALUES (166,'woteggrgrld');");//save to local filevar data = db.export();var arraybuff = data.buffer;var blob =newBlob([arraybuff]);var url = window.URL.createObjectURL(blob);var a = document.createElement("a");
a.href = url;
a.download ='test.db';
a.click();
window.URL.revokeObjectURL(url);}functiongetfile(){var files = document.getElementById("uploadInput").files;//document.getElementById("uploadInput").value;var file = files[0];var fr =newFileReader();
fr.onload=function(){var Uints =newUint8Array(fr.result);var db =newwindow.SQL.Database(Uints);var res = db.exec("SELECT 列2 FROM test");for(i=0;i<res[0].values.length;i++){//query values
console.log(res[0].values[i]+' ');}}
fr.readAsArrayBuffer(file);}</script></html>
####1.4几点说明
1.15-20行对数据库的操作是在内存中进行的，保存到本地的话需要执行21行后的代码。
2.上面代码保存到本地不会覆盖原数据库。
3.查询的时候res是一个保存了多行对象的数组。在代码文档里写到
We have the following table, named test :
id age name
1 1 Ling
2 18 Paul
3 3 Markus
We query it like that:
var db = new SQL.Database();
var res = db.exec("SELECT id FROM test; SELECT age,name FROM test;");
/*sqlite数据库操作类 by sdxjwkq01*/
this.Db={
tableName:"",//表
whereReg:"",//where条件
orderReg:"",//排序条件
pageReg:"",//分页
dbUrl:"DRIVER=SQLite3 ODBC Driver;Database=Db/database.db",//数据库地址
//取得表
table:function(tableName){
this.tableName=tableName;
return this;
},
//取得where
where:function(whereReg){
this.whereReg=whereReg;
return this;
},
//排序
order:function(orderReg){
this.orderReg=orderReg;
return this;
},
//分页
page:function(pageReg){
this.pageReg=pageReg;
return this;
},
//添加
add:function(json){
var sql="insert into "+this.tableName+"(";
var fields=[];
var values=[];
for(var item in json){
fields.push(item);
values.push("'"+json[item]+"'");
}
sql+=fields.join(",");
sql+=") values("+values.join(",")+")";
var con = new ActiveXObject("ADODB.Connection");
con.ConnectionString =this.dbUrl;
con.Open();
con.Execute(sql);
con.Close();
},
//删除
del:function(id){
var con = new ActiveXObject("ADODB.Connection");
con.ConnectionString = this.dbUrl;
con.Open();
if(typeof id=="object"){
con.Execute("delete from "+this.tableName+" where id in ("+id.join(",")+")");
}else{
con.Execute("delete from "+this.tableName+" where id="+id);
}
con.Close();
},
//修改
upd:function(json){
var sql="update "+this.tableName+" set ";
var data=[];
for(var item in json){
data.push(item+"="+json[item]);
}
sql+=data.join(",");
if(this.whereReg.length>0){
sql+=" where "+this.whereReg;
}
var con = new ActiveXObject("ADODB.Connection");
con.ConnectionString =this.dbUrl;
con.Open();
var re=con.Execute(sql);
con.Close();
},
//查询
sel:function(){
var con = new ActiveXObject("ADODB.Connection");
con.ConnectionString =this.dbUrl;
con.Open();
var sql="";
sql+="select * from "+this.tableName;
if(this.whereReg.length>0){
sql+=" where "+this.whereReg;
}
if(this.orderReg.length>0){
sql+=" order by "+this.orderReg;
}
if(this.pageReg.length>0){
var limit=this.pageReg.split(",");
sql+=" limit "+limit[0]+" offset "+limit[1];
}
var result=con.Execute(sql);
var resultArray=[];
var h=0;
while(!result.eof){
if(h==0){
//试探指针位置
for(i=0;;i++){
try{
eval("var temp=result("+i+")");
}catch(e){
var fieldLength=i;
break;
}
}
h++;
}
var temp=[];
for(i=0;i<fieldLength;i++){
eval("temp.push(''+result("+i+"))");
}
resultArray.push(temp);
result.movenext();
}
con.Close();
return resultArray;
},
//直接执行
execute:function(sql){
var con = new ActiveXObject("ADODB.Connection");
con.ConnectionString =this.dbUrl;
con.Open();
var result=con.Execute(sql);
var resultArray=[];
var h=0;
while(!result.eof){
if(h==0){
//试探指针位置
for(i=0;;i++){
try{
eval("var temp=result("+i+")");
}catch(e){
var fieldLength=i;
break;
}
}
h++;
}
var temp=[];
for(i=0;i<fieldLength;i++){
eval("temp.push(''+result("+i+"))");
}
resultArray.push(temp);
result.movenext();
}
con.Close();
return resultArray;
}
}
發表於 2023-3-28 09:16:00
