❧ Nodejs操作Sqlite3数据库

1、安装sqlite3模块

npm install sqlite3 --save

2、封装常用方法(sqlite.js)

/**
 * File: sqlite.js
 */

const fs = require('fs');
const sqlite3 = require('sqlite3').verbose();

const 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:" + err.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;

3、引入封装文件使用

/// Import SqliteDB.

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

var file = "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]);

    }

}