使用Node-RED在MySQL中創建Web API

 使用Node-RED在MySQL中創建Web API

源自於 https://qiita.com/fujitah/items/fd552e80c9c846971981

[{"id":"ccf1292d.664ef8","type":"tab","label":"流程9","disabled":false,"info":""},{"id":"aff9c1df.d0b4d","type":"mysql","z":"ccf1292d.664ef8","mydb":"ddab27b6.2c6808","name":"WebAPI","x":640,"y":220,"wires":[["130ba992.aaaff6"]]},{"id":"d363a342.8f94b","type":"function","z":"ccf1292d.664ef8","name":"Generate SQL","func":"name = msg.payload.name;\nmsg.topic = \"insert into users(name) values('\" + name + \"');\";\nconsole.log(msg.topic);\nreturn msg;","outputs":1,"noerr":0,"x":384.5,"y":135,"wires":[["aff9c1df.d0b4d","8b6bae9.511555"]]},{"id":"8b6bae9.511555","type":"debug","z":"ccf1292d.664ef8","name":"","active":true,"console":"false","complete":"topic","x":660,"y":380,"wires":[]},{"id":"130ba992.aaaff6","type":"http response","z":"ccf1292d.664ef8","name":"","x":770,"y":220,"wires":[]},{"id":"49e5b8a5.676648","type":"debug","z":"ccf1292d.664ef8","name":"","active":true,"console":"false","complete":"payload","x":390,"y":320,"wires":[]},{"id":"1fe2248a.f84f8b","type":"http in","z":"ccf1292d.664ef8","name":"","url":"/users/create","method":"get","upload":false,"swaggerDoc":"","x":108.5,"y":130,"wires":[["d363a342.8f94b","49e5b8a5.676648"]]},{"id":"b41a87c4.864748","type":"http in","z":"ccf1292d.664ef8","name":"","url":"/users/list","method":"get","swaggerDoc":"","x":90,"y":220,"wires":[["88f0cf89.c74ee","49e5b8a5.676648"]]},{"id":"6982a985.8b3b88","type":"http in","z":"ccf1292d.664ef8","name":"","url":"/users/update","method":"get","swaggerDoc":"","x":110,"y":300,"wires":[["bc8aec23.83cae","49e5b8a5.676648"]]},{"id":"25da5ff1.5e646","type":"http in","z":"ccf1292d.664ef8","name":"","url":"/users/delete","method":"get","swaggerDoc":"","x":110,"y":380,"wires":[["b5623f02.7aab3","49e5b8a5.676648"]]},{"id":"88f0cf89.c74ee","type":"function","z":"ccf1292d.664ef8","name":"Generate SQL","func":"msg.topic = \"SELECT * FROM users;\"\nconsole.log(msg.topic);\nreturn msg;","outputs":1,"noerr":0,"x":389.5,"y":199,"wires":[["aff9c1df.d0b4d","8b6bae9.511555"]]},{"id":"bc8aec23.83cae","type":"function","z":"ccf1292d.664ef8","name":"Generate SQL","func":"id = msg.payload.userid;\nname = msg.payload.name;\nmsg.topic = \"UPDATE users SET name='\" + name + \"' WHERE id=\" + id + \";\";\nconsole.log(msg.topic);\nreturn msg;","outputs":1,"noerr":0,"x":391.5,"y":261,"wires":[["aff9c1df.d0b4d","8b6bae9.511555"]]},{"id":"b5623f02.7aab3","type":"function","z":"ccf1292d.664ef8","name":"Generate SQL","func":"id = msg.payload.userid;\nmsg.topic = \"DELETE FROM users WHERE id=\" + id + \";\";\nconsole.log(msg.topic);\nreturn msg;","outputs":1,"noerr":0,"x":380,"y":380,"wires":[["aff9c1df.d0b4d","8b6bae9.511555"]]},{"id":"2665825f.f445be","type":"comment","z":"ccf1292d.664ef8","name":"create?name=hoge","info":"","x":111,"y":84,"wires":[]},{"id":"bac4e253.cb682","type":"comment","z":"ccf1292d.664ef8","name":"list","info":"","x":72.5,"y":173,"wires":[]},{"id":"465fedb6.b72794","type":"comment","z":"ccf1292d.664ef8","name":"update?userid=63&name=hoge","info":"","x":150,"y":260,"wires":[]},{"id":"11bb37db.2ed188","type":"comment","z":"ccf1292d.664ef8","name":"delete?userid=30","info":"","x":100,"y":340,"wires":[]},{"id":"5db4b0.722cab5","type":"comment","z":"ccf1292d.664ef8","name":"MySQL DB","info":"```MySQL\nmysql> CREATE DATABASE nodered;\nmysql> CREATE TABLE nodered.users(id INT AUTO_INCREMENT, name TEXT, PRIMARY KEY (id));\n```","x":640,"y":180,"wires":[]},{"id":"ff521fdd.69d69","type":"comment","z":"ccf1292d.664ef8","name":"Tutorial of MySQL: https://qiita.com/fujitah/items/fd552e80c9c846971981","info":"","x":280,"y":40,"wires":[]},{"id":"ddab27b6.2c6808","type":"MySQLdatabase","z":"","name":"nodered","host":"127.0.0.1","port":"3306","db":"nodered","tz":""}]

Create

name = msg.payload.name;
msg.topic = "insert into users(name) values('" + name + "');";
console.log(msg.topic);
return msg;

List

msg.topic = "SELECT * FROM users;"
console.log(msg.topic);
return msg;

Update

id = msg.payload.userid;
name = msg.payload.name;
msg.topic = "UPDATE users SET name='" + name + "' WHERE id=" + id + ";";
console.log(msg.topic);
return msg;

Delete

id = msg.payload.userid;
msg.topic = "DELETE FROM users WHERE id=" + id + ";";
console.log(msg.topic);
return msg;