node + express 搭建后端服务,连接 mysql 数

作者: 韩无仙 | 来源:发表于2021-05-28 16:27 被阅读0次

建立mysql数据库

登录mysql后，创建一个新库。我的库名是geometry。
通过osm获得的poi信息切出上海杨浦区部分输出为geojson。
通过如下代码将geojson数据存入

/*
 * @Author: citrusrlia@foxmail.com
 * @FilePath: \shy-node-back\writeJsonInsert.js
 * @Date: 2021-05-24 11:21:32
 * @Description: geojson数据入库
 */
const mysql = require("mysql");
const fs = require("fs");

let result = fs.readFileSync("./statics/yangpu_poi.json"); //osm数据
let data = JSON.parse(result.toString());
let ready = data.features;

const connection = mysql.createConnection({
  host: "localhost",
  user: "************",
  password: "*************",
  database: "************",
  port: "6326",
});

connection.connect();
let AddParam = [];
for (let i = 0, l = ready.length; i < l; i++) {
  let osm_id = ready[i].properties.osm_id,
    type = ready[i].geometry.type,
    coordinateStr = ready[i].geometry.coordinates.toString(),
    code = ready[i].properties.code,
    fclass = ready[i].properties.fclass,
    name = ready[i].properties.name;
  AddParam = [osm_id, type, coordinateStr, code, fclass, name];
  connection.query(
    "INSERT INTO geometry(osm_id,type,coordinates,code,fclass,name) VALUES(?,?,?,?,?,?)",
    AddParam,
    function (error, results) {
      if (error) {
        console.log("INSERT ERROR - ", error.message);
        return;
      }
      console.log("INSERT ID:", results);
    }
  );
}

connection.end();

这里将经纬度数据转化为字符串存储，部分长度会超过255字节限制，字段类型需要改为长文本类型。

image.png

现在获得了储存了geojson数据的表。

使用express框架搭建服务

首先安装express和express脚手架

npm i express
npm i express-generator

运行建立名叫app的项目会得到如下图的目录结构

express app

image.png

先在public/javascripts中新建sqls.js 用module.exports输出各增删改查方法

/*
 * @Author: citrusrlia@foxmail.com
 * @FilePath: \app\public\javascripts\sqls.js
 * @Date: 2021-05-24 15:01:08
 * @Description: 对数据库的操作
 */
const mysql = require("mysql");
const connection = mysql.createConnection({
  host: "localhost",
  user: "*******",
  password: "***********",
  database: "*************",
  port: "6326",
});

function addItem(params, callback) {
  let sql = `INSERT INTO geometry(osm_id,type,coordinates,code,fclass,name,id) VALUES(?,?,?,?,?,?,?)`;
  let AddParam = [
    params.osm_id,
    params.type,
    params.coordinates,
    params.code,
    params.fclass,
    params.name,
    params.id,
  ];
  connection.query(sql, AddParam, function (err, result) {
    if (err) {
      console.log("Add Geometry ERROR - ", err.message);
      return;
    }
    callback(result);
  });
}

function deleteByName(name, callback) {
  let sql = `DELETE FROM geometry where name=?`;
  connection.query(sql, [name], function (err, result) {
    if (err) {
      console.log("Delete Geometry ERROR - ", err.message);
      return;
    }
    callback(result);
  });
}

function editById(params, id, callback) {
  let sql = `UPDATE geometry SET osm_id=?,type=?,coordinates=?,code=?,fclass=?,name=?,id=? where osm_id=? or id=?`;
  let UpdateParam = [
    params.osm_id,
    params.type,
    params.coordinates,
    params.code,
    params.fclass,
    params.name,
    params.id,
  ];
  connection.query(sql, [...UpdateParam, id, id], function (err, result) {
    if (err) {
      console.log("UPDATE Geometry ERROR - ", err.message);
      return;
    }
    callback(result);
  });
}

function pagination(thispage, pages, callback) {
  let sql = `SELECT * from geometry limit ${(thispage - 1) * pages},${pages}`;
  connection.query(sql, function (err, result) {
    if (err) {
      console.log("pagination ERROR - ", err.message);
      return;
    }
    callback(result);
  });
}

function getByClass(fclass, callback) {
  let sql = `SELECT * FROM geometry WHERE fclass = ?`;
  connection.query(sql, [fclass], function (err, result) {
    if (err) {
      console.log("GET By class ERROR - ", err.message);
      return;
    }
    callback(result);
  });
}

function getById(id, callback) {
  let sql = `SELECT * FROM geometry WHERE id = ? or osm_id=?`;
  connection.query(sql, [id, id], function (err, result) {
    if (err) {
      console.log("GET By ID ERROR - ", err.message);
      return;
    }
    callback(result);
  });
}

function getByName(name, callback) {
  let sql = `SELECT * FROM geometry WHERE name = ?`;
  connection.query(sql, [name], function (err, result) {
    if (err) {
      console.log("GET By Name ERROR - ", err.message);
      return;
    }
    callback(result);
  });
}

function getAllFclass(callback) {
  let sql = `SELECT fclass FROM geometry`;
  connection.query(sql, function (err, result) {
    if (err) {
      console.log("GET ALL CLASS ERROR - ", err.message);
      return;
    }
    let arr = [];
    for (let i = 0, l = result.length; i < l; i++) {
      if (!arr.includes(result[i].fclass)) {
        arr.push(result[i].fclass);
      }
    }
    callback(arr);
  });
}

function getByPosition(initPoint, range, jsonUrl) {
  try {
    initPoint = eval(initPoint + "//@ sourceURL=initpoint"); //便于调试eval代码
    let result = fs.readFileSync(jsonUrl);
    let data = JSON.parse(result.toString());
    let Arr = [];
    for (let i = 0, l = data.features.length; i < l; i++) {
      let center = data.features[i].geometry.coordinates[0].reduce(
        (acc, cur, index, array) => {
          let lng = acc[0] + cur[0],
            lat = acc[1] + cur[1];
          if (index === array.length - 1) {
            return [lng / array.length, lat / array.length];
          }
          return [lng, lat];
        },
        [0, 0]
      );
      let distance = caculateLL(center, initPoint);
      if (distance < range) {
        Arr.push(data.features[i]);
      }
    }
    return Arr;
  } catch (err) {
    console.log(err);
  }
}
/**
 * @author: citrusrlia@foxmail.com
 * @description: 计算两点间距离
 * @param {p1,p2 : Arr}
 * @return {number}
 */
function caculateLL(p1, p2) {
  let lat1 = p1[0],
    lng1 = p1[1],
    lat2 = p2[0],
    lng2 = p2[1];
  var radLat1 = (lat1 * Math.PI) / 180.0;
  var radLat2 = (lat2 * Math.PI) / 180.0;
  var a = radLat1 - radLat2;
  var b = (lng1 * Math.PI) / 180.0 - (lng2 * Math.PI) / 180.0;
  var s =
    2 *
    Math.asin(
      Math.sqrt(
        Math.pow(Math.sin(a / 2), 2) +
          Math.cos(radLat1) * Math.cos(radLat2) * Math.pow(Math.sin(b / 2), 2)
      )
    );
  s = s * 6378.137;
  s = Math.round(s * 10000) / 10;
  return s;
}

function getAll(callback) {
  // connection.connect();
  let sql = "SELECT * FROM geometry";
  connection.query(sql, function (err, result) {
    if (err) {
      console.log("GETALL ERROR - ", err.message);
      return;
    }
    callback(result);
  });
  // connection.end();
}

module.exports = {
  addItem,
  deleteByName,
  editById,
  pagination,
  getByClass,
  getById,
  getByName,
  getAllFclass,
  getByPosition,
  getAll,
};

接着修改路由index.js，在请求对应地址时返回对应sql结果。node-mysql查询结果无法通过return返回，需要传入回调函数在回调中做res.send()，返回结果。views中页面使用pug编写，可以通过修改app.set("view engine","html")改成html语言或其他语言。使用require引入sqls.js时，项目未编译，如下设置不会使index.js中 ./ 路径映射到根目录下。

app.use(express.static(path.join(__dirname, "public")));

var express = require("express");
var router = express.Router();
var sqls = require("../public/javascripts/sqls");
var url = require("url")

/* 展示views中地址 */
router.get("/", function (req, res, next) {
  res.render("index", { title: "Hello,简书的朋友!" });
});

router.get("/getByClass", function (req, res) {
  /**
   * @description: 根据类别查询对应条目
   * @param {类别:fclass}
   * @return {Array}
   */
  let params = url.parse(req.url, true).query;
  sqls.getByClass(params.fclass, (searchResult) => {
    res.send(searchResult);
  });
});

router.get("/getById", function (req, res) {
  /**
   * @author: citrusrlia@foxmail.com
   * @description: 根据osm_id查找对应条目
   * @param {String:osm_id}
   * @return {Object}
   */
  let params = url.parse(req.url, true).query;
  sqls.getById(params.id, (searchResult) => {
    res.send(searchResult);
  });
});

router.get("/getByName", function (req, res) {
  /**
   * @author: citrusrlia@foxmail.com
   * @description: 根据名称查找对应条目
   * @param {String:name}
   * @return {Arr}
   */
  let params = url.parse(req.url, true).query;
  sqls.getByName(params.name, (result) => {
    res.send(result);
  });
});

router.get("/pagination", function (req, res) {
  /**
   * @description: 分页查询对应条目
   * @param {查询页标page,每页条目数perpage}
   * @return {Array}
   */
  let params = url.parse(req.url, true).query;
  sqls.pagination(params.page, params.perpage, (searchResult) => {
    res.send(searchResult);
  });
});

router.post("/addItem", function (req, res) {
  /**
   * @description: 添加条目
   * @param {addData:Object}
   * @return {nothing}
   */
  let params = url.parse(req.url, true).query;
  sqls.addItem(params, (searchResult) => {
    if (searchResult) res.send("添加成功");
  });
});

router.post("/delectByName", function (req, res) {
  /**
   * @description: 删除条目
   * @param {条目名称:name}
   * @return {nothing}
   */
  let params = url.parse(req.url, true).query;
  sqls.delectByName(params.name, (searchResult) => {
    if (searchResult) res.send("删除成功");
  });
});

router.post("/editById", function (req, res) {
  /**
   * @description: 修改条目
   * @param {params:Object,条目名称:searchId}
   * @return {Array}
   */
  let params = url.parse(req.url, true).query;
  sqls.editById(params, params.searchId, (searchResult) => {
    if (searchResult) res.send("修改成功");
  });
});

router.get("/getAllFclass", function (req, res) {
  /**
   * @author: citrusrlia@foxmail.com
   * @description:获取所有fclass
   * @param {nothing}
   * @return {Arr}
   */
  let params = url.parse(req.url, true).query;
  sqls.getAllFclass((searchResult) => {
    res.send(searchResult);
  });
});

router.get("/getByPosition", function (req, res) {
  /**
   * @author: citrusrlia@foxmail.com
   * @description:获取点附近features
   * @param {initPoint:[lng,lat] ,range:number}
   * @return {Arr}
   */
  let params = url.parse(req.url, true).query;
  sqls.getByPosition(params.initPoint, params.range, (searchResult) => {
    res.send(searchResult);
  });
});

router.get("/getAll", function (req, res) {
  /**
   * @author: citrusrlia@foxmail.com
   * @description: 获取所有features
   * @param {}
   * @return {Arr}
   */
  let params = url.parse(req.url, true).query;
  sqls.getAll((searchResult) => {
    res.send(searchResult);
  });
});
module.exports = router;

调用

image.png

访问对应端口，成功拿到数据库中数据。

总结

由于本人系gis前端工程师，第一次自己做后端服务，文中不足之处希望在看本文的朋友多多留言指正，万分感谢！

node + express 搭建后端服务,连接 mysql 数

建立mysql数据库

使用express框架搭建服务

调用

总结

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读