nodejs mysql sqllite_封装nodejs操作sqlite数据库的模块

// import node modules package

const fs = require('fs');

const path = require('path')

const sqlite3 = require("sqlite3").verbose();

//import own package

const propath = require('../dir/handle');

// database file

const file = "linkeye.db";

//define error information

const createAccountTableErr = 'errone';

const createSendTableErr = 'errtwo';

//define success information

const createAccountTableSucc = 'succone';

const createSendTableSucc = 'succtwo'

var initDataBase={};

/*

* method:This funbction used to get the linkeye.db directory

* params:no

* return:linkeye.db directory

*/

function getDbFilePath(flag) {

var dir = propath.handlePath();

var dbDir = path.resolve(dir, '../../../');

var linkFileDir = dbDir + flag + file;

return linkFileDir;

}

/*

* method:This funbction used to check the linkeye.db file is exist or not

* params:no

* return:no

*/

initDataBase.checkCreateLinkeyeDb = function (){

var db;

var linkDirFile = getDbFilePath("/linkdata/");

var exists = fs.existsSync(linkDirFile);

if(!exists) {

db = new sqlite3.Database(linkDirFile);

console.log('create linkeye.db success');

}else {

db = new sqlite3.Database(linkDirFile);

console.log('linkeye.db is already exist');

}

return db;

}

/*

* method:This funbction used to create account table

* params:tableName

* params:db

* return:

*/

initDataBase.createAccountTable = function(tableName, db) {

if (!tableName || !db ){

console.log("table name or db is null")

return createAccountTableErr;

}else {

db.run("CREATE TABLE IF NOT EXISTS "+ tableName + " (" +

"account_id TEXT PRIMARY KEY NOT NULL," +

"account_name TEXT ," +

"account_passwd TEXT ," +

"account_address TEXT " +

") ");

console.log("create account table success");

}

return createAccountTableSucc;

}

/*

* method:This funbction used to create send table

* params:tableName

* params:db

* return:

*/

initDataBase.createSendTable = function(tableName, db) {

if (!tableName || !db){

console.log("table name or db is null");

return createSendTableErr;

}else {

db.run("CREATE TABLE IF NOT EXISTS "+ tableName + " (" +

"send_id TEXT PRIMARY KEY NOT NULL," +

"account_id TEXT ," +

"account_addr_from TEXT ," +

"account_addr_to TEXT ," +

"send_balance TEXT ," +

"service_charge TEXT ," +

"comment TEXT ," +

"send_status TEXT " +

") ");

console.log("create send table success");

}

return createSendTableSucc;

}

module.exports = initDataBase;

/*

* This Package used to execute sql operation

*/

const init = require('./init');

// error code

const execSqlErr = 'sqlerrone'

// init database handle

var sql = {};

/*

* Method:This function is used to execute sql sentence

* params: sql-sql sentence

* params: db-database handle

* return: fail to return error code

*/

sql.executeNoQuerySql = function (sql, db) {

if (!sql) {

console.log("sql sentence is null")

return execSqlErr;

}else {

db.run(sql)

}

}

/*

* Method:This function is used to execute sql sentence

* params: sql-sql sentence

* params: db-database handle

* return: success to return result set; fail to print error

*/

sql.executeQuerySql = function(sql, db) {

if (!sql) {

console.log("sql sentence is null")

}else {

db.all(sql, function(err, res) {

if(!err) {

var ret = JSON.stringify(res)

console.log(ret)

} else {

console.log(err);

}

});

}

}

module.exports = sql;

const init = require('./init');

const sql = require('./sqlite');

var db = init.checkCreateLinkeyeDb();

var sqlone = "INSERT OR REPLACE INTO account(" +

"account_id, account_name, account_passwd, account_address" +

") VALUES ('3','3','3','3')";

var err = sql.executeNoQuerySql(sqlone, db);

var sqltwo = "select * from account";

var ret = sql.executeQuerySql(sqltwo, db);

关注问我技术公众号，加小问，拉您入技术交流群：