526互联

sqlite简单使用教程

发布时间 2023-04-15 10:40:51作者: 月夜魂归

笔记软件在2023/4/15 10:30:30推送该笔记

一、安装

npm install sqlite3 -S

二、封装的包

/**
 * File: sqlite.js.
 */
 
var fs = require('fs');
var sqlite3 = require('sqlite3').verbose();
 
var DB = DB || {};
 
DB.SqliteDB = function(file){
    DB.db = new sqlite3.Database(file);
 
    DB.exist = fs.existsSync(file);
    if(!DB.exist){
        console.log("Creating db file!");
        fs.openSync(file, 'w');
    };
};
 
DB.printErrorInfo = function(err){
    console.log("Error Message:" + err.message + " ErrorNumber:" + errno);
};
 
DB.SqliteDB.prototype.createTable = function(sql){
    DB.db.serialize(function(){
        DB.db.run(sql, function(err){
            if(null != err){
                DB.printErrorInfo(err);
                return;
            }
        });
    });
};
 
/// tilesData format; [[level, column, row, content], [level, column, row, content]]
DB.SqliteDB.prototype.insertData = function(sql, objects){
    DB.db.serialize(function(){
        var stmt = DB.db.prepare(sql);
        for(var i = 0; i < objects.length; ++i){
            stmt.run(objects[i]);
        }
  
        stmt.finalize();
    });
};
 
DB.SqliteDB.prototype.queryData = function(sql, callback){
    DB.db.all(sql, function(err, rows){
        if(null != err){
            DB.printErrorInfo(err);
            return;
        }
 
        /// deal query data.
        if(callback){
            callback(rows);
        }
    });
};
 
DB.SqliteDB.prototype.executeSql = function(sql){
    DB.db.run(sql, function(err){
        if(null != err){
            DB.printErrorInfo(err);
        }
    });
};
 
DB.SqliteDB.prototype.close = function(){
    DB.db.close();
};
 
/// export SqliteDB.
exports.SqliteDB = DB.SqliteDB;

简单使用（增删改查）



const path = require('path');

/// Import SqliteDB.

var SqliteDB = require('../utils/sqlite.js').SqliteDB;



var file = path.resolve(__dirname,"../db/Gis1.db");
var sqliteDB = new SqliteDB(file);

/// create table.
var createTileTableSql = "create table if not exists tiles(level INTEGER, column INTEGER, row INTEGER, content BLOB);";
var createLabelTableSql = "create table if not exists labels(level INTEGER, longitude REAL, latitude REAL, content BLOB);";
sqliteDB.createTable(createTileTableSql);
sqliteDB.createTable(createLabelTableSql);



/// insert data.
var tileData = [[1, 10, 10], [1, 11, 11], [1, 10, 9], [1, 11, 9]];
var insertTileSql = "insert into tiles(level, column, row) values(?, ?, ?)";
sqliteDB.insertData(insertTileSql, tileData);



/// query data.
var querySql = 'select * from tiles where level = 1 and column >= 10 and column <= 11 and row >= 10 and row <=11';
sqliteDB.queryData(querySql, dataDeal);


/// update data.
var updateSql = 'update tiles set level = 2 where level = 1 and column = 10 and row = 10';
sqliteDB.executeSql(updateSql);

/// query data after update.
querySql = "select * from tiles where level = 2";
sqliteDB.queryData(querySql, dataDeal);

sqliteDB.close();

function dataDeal(objects){
    for(var i = 0; i < objects.length; ++i){
        console.log(objects[i]);
    }
}