nodejs restful api unity

dd.unitypackage

npm generic-pool 

https://www.npmjs.com/package/generic-pool

ORM(Object Relational Mapping) 

npm Sequelize 

https://www.npmjs.com/package/sequelize

console.log('starting test.js');
 
var express = require('express');
var bodyParser = require('body-parser');
var uglifyJS = require("uglify-js");
var fs = require('fs');
var app = express();
//app.use(express.json());
app.use(bodyParser.urlencoded({extended: true}));
app.use(bodyParser.json());
 
var mysql = require('mysql');
var pool = mysql.createPool({
    host: 'localhost',
    port: 3306,
    user: 'smilejsu',
    password: 'smilejsu',
    database: 'test',
    connectionLimit: 50,
    waitForConnections: false
});
 
app.route('/deletePerson/:personID').delete((req, res)=>{
    // console.log(req);
    // console.log(req.params);
    console.log(req.params);
    
    var personID = req.params.personID;
 
    pool.getConnection((err, con)=>{
 
        var sql = `delete from persons where personid=${personID};`;
        var query = con.query(sql, (err, rows)=>{
            if(err){
                console.log(err);
                con.release();
                throw err;
            }else{
                console.log(rows);
                con.release();
                res.send(rows);
            }
        });
    });
    
});
 
app.route('/addPerson').post((req, res)=>{
    console.log("param: " , req.param);
    console.log("params: " , req.params);
    console.log("body: ", req.body);
    console.log("field: ", req.body.myField);
    console.log(typeof req.body.myField);
    //var json = uglifyJS.minify();
    
    // fs.writeFile("person.json", req.body.myField, (err)=>{
    //     if(err)
    //         console.log(err);
    //     else
    //         console.log('saved!');
    // });
 
    var arrPersons = JSON.parse(req.body.myField);
    console.log(arrPersons);
    pool.getConnection((err, con)=>{
        var cnt = 0;
        arrPersons.forEach(element => {
            var person = element;
            console.log("===>" + person.personID, person.lastName, person.firstName, person.address, person.city);
 
            var sql = `insert into persons(personID, lastName, firstName, address, city) values(${person.personID}, '${person.lastName}', '${person.firstName}', '${person.address}', '${person.city}');`;
            console.log(sql);
            con.query(sql, (err, rows)=>{
                if(err){
                    con.release();
                    console.log(err);
                    throw err;
                }else{
                    cnt++;
                }
            });
 
            console.log(arrPersons.length);
            if(cnt>= arrPersons.length)
            {
                console.log(rows);
                con.release();
                res.send(rows);
            }
        });
    });
    
 
});
 
app.route('/addPerson').put((req, res)=>{
 
    console.log("param: " + req.param);
    console.log("params: " + req.params);
    console.log("body: " + req.body);
    var person = req.body;
 
    pool.getConnection((err, con)=>{
 
        console.log(person);    
 
        var sql = `insert into persons (personid, lastname, firstname, address, city) values('${person.personID}','${person.lastName}','${person.firstName}','${person.address}','${person.city}');`;
        
        console.log(sql);
        var query = con.query(sql, (err, rows)=>{
            if(err){
                console.log(err);
                con.release();
                throw err;
            }else{
                console.log(rows);
                con.release();
                res.send(rows);
            }
        });
    });
});
 
 
 
app.get('/getAllUsers', (req, res)=>{
    pool.getConnection((err, con)=>{
        // console.log(err, con);
 
        var query = con.query('select * from persons;', (err, rows)=>{
            if(err){
                console.log(err);
                con.release();
                throw err;
            }else{
                console.log(rows);
                con.release();
                res.send(rows);
            }
        });
        //console.log(query);
    });
});
 
app.listen(3030, ()=>{
    console.log('server started at 3030');
});
 
 

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.Networking;
using Newtonsoft.Json;
using System.Text;
using UnityEngine.UI;
 
public class Test : MonoBehaviour
{
    public Button btnSend;
    public Button btnRefresh;
    public Button btnDelete;
    public Button btnPost;
 
    private string url = "http://localhost:3030";
    private UIList_Person uiListPerson;
    public GridLayoutGroup grid;
    
 
    // Start is called before the first frame update
    void Start()
    {
        this.uiListPerson = Resources.Load<GameObject>("Panel_Achievement").GetComponent<UIList_Person>();
 
        this.btnSend.onClick.AddListener(() => {
            var newPerson = new PersonInfo(2, "임", "꺽정", "덕양구", "고양시");
            var json = JsonConvert.SerializeObject(newPerson);
            StartCoroutine(this.Put(json));
        });
 
        this.btnRefresh.onClick.AddListener(() => {
            StartCoroutine(this.Get());
        });
 
        this.btnDelete.onClick.AddListener(() => {
            StartCoroutine(this.Delete("2"));
        });
 
        this.btnPost.onClick.AddListener(() => {
            StartCoroutine(this.Post());
        });
 
        StartCoroutine(this.Get());
    }
 
    // Update is called once per frame
    void Update()
    {
        
    }
 
    private IEnumerator Get()
    {
        UnityWebRequest www = UnityWebRequest.Get("http://localhost:3030/getAllUsers");
        yield return www.SendWebRequest();
 
        if (www.isNetworkError || www.isHttpError)
        {
            Debug.Log($"{www.error}");
        }
        else {
            Debug.Log($"{www.downloadHandler.text}");
 
            byte[] results = www.downloadHandler.data;
 
            Debug.Log($"{results}");
 
            var json = Encoding.UTF8.GetString(results);
 
            var arrPersons = JsonConvert.DeserializeObject<PersonInfo[]>(json);
 
            int i = 0;
 
            foreach (Transform child in this.grid.transform)
                GameObject.Destroy(child.gameObject);
                    
 
            foreach (var person in arrPersons) {
                Debug.Log($"{person.personID}, {person.firstName}, {person.lastName}, {person.address}, {person.city}");
                //arrUIListPerson[i++].Init(person.personID, person.lastName, person.firstName, person.address, person.city);
                var listItem = Instantiate(this.uiListPerson.gameObject);
                listItem.transform.SetParent(this.grid.transform);
                listItem.transform.localScale = Vector3.one;
                listItem.GetComponent<UIList_Person>().Init(person.personID, person.lastName, person.firstName, person.address, person.city);
            }
        }
    }
 
    private IEnumerator Delete(string uri)
    {
        var _uri = new System.Uri(string.Format("http://localhost:3030/deletePerson/{0}", uri));
        UnityWebRequest www = UnityWebRequest.Delete(_uri);
        //www.SetRequestHeader("Content-type", "application/json");
        yield return www.SendWebRequest();
        if (www.isNetworkError || www.isHttpError)
        {
            Debug.Log($"{www.error}");
        }
        else {
            Debug.Log("DELETE 성공!");
        }
    }
 
    private IEnumerator Put(string json)
    {
        Debug.Log(json);
 
        byte[] data = Encoding.UTF8.GetBytes(json);
 
        Debug.Log(data.Length);
 
        var path = this.url + "/addPerson";
        Debug.Log(path);
        
        UnityWebRequest www = UnityWebRequest.Put(path, data);
        www.SetRequestHeader("Content-type", "application/json");
        yield return www.SendWebRequest();
        if (www.isNetworkError || www.isHttpError)
        {
            Debug.Log($"{www.error}");
        }
        else {
            Debug.Log("PUT 성공!");
        }
    }
 
    private IEnumerator Post()
    {
        List<IMultipartFormSection> formData = new List<IMultipartFormSection>();
 
        //var newPerson = new PersonInfo(2, "임", "꺽정", "덕양구", "고양시");
        //var json = JsonConvert.SerializeObject(newPerson);
        //formData.Add(new MultipartFormDataSection("data", json));
        var ta = Resources.Load<TextAsset>("person");
 
        WWWForm form = new WWWForm();
        form.AddField("myField", ta.text);
 
        UnityWebRequest www = UnityWebRequest.Post("http://localhost:3030/addPerson", form);
        //www.SetRequestHeader("Content-Type", "application/json");
        //www.SetRequestHeader("Accept", "application/json");
        yield return www.SendWebRequest();
 
 
        if (www.isNetworkError || www.isHttpError)
        {
            Debug.Log($"{www.error}");
        }
        else {
            Debug.Log("POST성공!");
        }
    }
}
 

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
 
public class PersonInfo 
{
    public int personID;
    public string firstName;
    public string lastName;
    public string address;
    public string city;
 
    public PersonInfo(int personID, string firstName, string lastName, string address, string city)
    {
        this.personID = personID;
        this.firstName = firstName;
        this.lastName = lastName;
        this.address = address;
        this.city = city;
    }
}
 

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.UI;
 
public class UIList_Person : MonoBehaviour
{
    public Text txtPersonID;
    public Text txtLastName;
    public Text txtFirstName;
    public Text txtAddress;
    public Text txtCity;
 
    public void Init(int personID, string lastName, string firstName, string address, string city)
    {
        this.txtPersonID.text = personID.ToString();
        this.txtLastName.text = lastName;
        this.txtFirstName.text = firstName;
        this.txtAddress.text = address;
        this.txtCity.text = city;
    }
 
}
 

use test;

show tables;

desc persons;

select * from persons;

insert into persons(personID, city) values(2, '서울');

show status;

show full columns from persons;

alter table persons convert to character set utf8;

delete from persons where city='서울';

insert into persons(personid, lastname, firstname, city) values(2, '길동', '홍', '서울');

update persons set address='쌍문동', personid=1 where personid=2;

alter table persons change firstname fistname varchar(255);

alter table persons change fistname firstname varchar(255);

update persons set personid=0 where personid=1;

update persons set address='방학동' where personid=0 AND city='seoul';

show grants; #권한 보기 

grant all privileges on *.* to 'root'@'localhost' with grant option; #권한주기 

#사용자 추가 

create user 'smilejsu'@'localhost' identified by 'smilejsu';

#권한 부여 

grant all privileges on test.* to 'smilejsu'@'localhost';

show tables;

desc persons;

select * from persons;

delete from persons where personid=0;

persons.csv

IEnumerator Post(string url, string bodyJsonString)
    {
        var request = new UnityWebRequest(url, "POST");
        byte[] bodyRaw = Encoding.UTF8.GetBytes(bodyJsonString);
        request.uploadHandler = (UploadHandler) new UploadHandlerRaw(bodyRaw);
        request.downloadHandler = (DownloadHandler) new DownloadHandlerBuffer();
        request.SetRequestHeader("Content-Type", "application/json");
 
        yield return request.Send();
 
        Debug.Log("Status Code: " + request.responseCode);
    }

토큰 발급 및 검증 

console.log('Starting app.js');
const express = require('express');
const mysql = require('mysql');
const jwt = require('jsonwebtoken');
const app = express();
 
const connectDB = (callback)=>{
    var con = mysql.createConnection(
        {
            host:'192.168.0.133',
            user:'root',
            password:'root',
            database:'test'
        }
    );
    con.connect((err)=>{
        if(err){
            console.log(err);
            con.end();
            throw err;
        }else{
            console.log('DB서버 접속성공!');
            callback(con);
        }
    });
};
 
app.use(express.json());
 
app.post('/api/login', (req, res)=>{
    
    const user = req.body;
    console.log(user.id, user.userName);
 
    //DB접속
    connectDB((con)=>{
        var sql = `select * from users where id='${user.id}';`;
        con.query(sql, (err, results, fields)=>{
            if(err){
                console.log(err);
                res.json({
                    cmd: 502,
                    err: err
                });
                con.end();
            }else{
                console.log(results[0]);
                console.log(typeof(results[0]) === 'undefined');
                if(typeof(results[0]) === 'undefined'){
                    //테이블에 유저 없음 
                    res.json({
                        cmd:503
                    });
                    con.end();
                }else{
                    var user = results[0];
                    //테이블에 있다면 (등록된 유저) 토큰 발급 
                    //jwt.sign({user})
                    console.log(user);
                    jwt.sign({user}, 'apple', { expiresIn : '10m' }, (err, token)=>{
                        res.json({
                            token
                        });
                        con.end();
                    });
                }
            }
        });
    });
 
});
 
app.get('/api/getUnits', verifyToken, (req, res)=>{
 
    //토큰 있는지 검사 완료 
 
    //토큰이 정상인지 검사 
    jwt.verify(req.token, 'apple', (err, authData)=>{
 
        console.log(authData);
 
        if(err){
            console.log(req.token);
            console.log(err);
            res.sendStatus(403);
        }else{
            console.log('정상 토큰 입니다.');
            //정상입니다.
            //DB접속 
            //DB검색 
            //데이터 가져옴 
            //응답시 데이터 전송 
            connectDB((con)=>{
                var sql = `select * from units`;
                con.query(sql, (err, results, fields)=>{
                    if(err){
                        console.log(err);
                        res.json({
                            cmd: 501,
                            err:err
                        });
                        con.end();
                        throw err;                
                    }else{
                        console.log(results);
                        var json = JSON.stringify(results);
                        console.log(json);
                        res.json({
                            cmd: 200,
                            authData: authData,
                            data: json
                        });
                    }
 
                });
            });
            // res.json({
            //     cmd:200,
            //     authData
            // });
        }
    });
 
 
    
});
 
app.post('/api/saveUnits', (req, res)=>{
    // console.log("param: " + req.param);
    // console.log("params: " + req.params);
    // console.log("body: " + req.body);
 
    //console.log("Units: " + req.body);
    var units = req.body;
    console.log(units);
    // console.log(typeof req.body);
    
    // //DB접속 
    // //DB저장 
    // //응답시 완료 되었음 전송 
    connectDB((con)=>{
 
        var str = '';
        req.body.forEach(element => {
            str += `('${element.unitName}', ${element.x},${element.y}, ${element.z}),`    
        });
 
        var val = str.substring(0, str.length - 1);
        console.log(val);
        var sql = `insert into units (unitName, posX, posY, posZ) values ${val};`;
        console.log(sql);
 
        con.query(sql, (err, results, fields)=>{
            if(err){
                console.log(err);
                res.json({
                    cmd: 500,
                    err: err
                });
                con.end();
                throw err;
            }else{
                console.log(results);
                con.end();
                res.json({
                    cmd: 200
                });
            }
 
        });
    });
 
});
 
//토큰이 있냐?
function verifyToken(req, res, next){
    console.log(req.headers);
    const bearerHeader = req.headers['authorization'];
    if( typeof(bearerHeader) !== 'undefined' ){
        //헤더에 토큰 정보가 있어 
        const bearer = bearerHeader.split(' ');
        const bearerToken = bearer[1];
        req.token = bearerToken;
        next();
    }else{
        //정보가 없어 
        console.log('토큰 없음');
        res.sendStatus(403);
    }
}
 
app.listen(3000, ()=>{
    console.log("서버가 시작 되었습니다. 포트:3000");
});

유니티 코드 

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.UI;
using Newtonsoft.Json;
using UnityEngine.Networking;
using System.Text;
 
public class user {
    public string id;
    public string userName;
    public user(string id, string userName)
    {
        this.id = id;
        this.userName = userName;
    }
}
public class TestWebRequest : MonoBehaviour
{
    public Button btn1;
    public Button btn2;
 
    // Start is called before the first frame update
    void Start()
    {
        btn1.onClick.AddListener(() => {
            var url = "http://192.168.0.133:3000/api/getUnits";
            var user = new user("hong@gmail.com", "홍길동");
            var json = JsonConvert.SerializeObject(user);
            //StartCoroutine(this.Post(url, json));
            StartCoroutine(this.Get(url));
        });    
    }
 
    IEnumerator Post(string url, string bodyJsonString)
    {
        var request = new UnityWebRequest(url, "POST");
        byte[] bodyRaw = Encoding.UTF8.GetBytes(bodyJsonString);
        request.uploadHandler = (UploadHandler)new UploadHandlerRaw(bodyRaw);
        request.downloadHandler = (DownloadHandler)new DownloadHandlerBuffer();
        request.SetRequestHeader("Content-Type", "application/json");
 
        yield return request.SendWebRequest();
 
        Debug.Log("Status Code: " + request.responseCode);
    }
 
    private IEnumerator Get(string url)
    {
        UnityWebRequest www = UnityWebRequest.Get(url);
 
        www.SetRequestHeader("authorization", "Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VyIjp7ImlkIjoiaG9uZ0BnbWFpbC5jb20iLCJ1c2VyTmFtZSI6Iu2Zjeq4uOuPmSJ9LCJpYXQiOjE1NDY1MTgzMDMsImV4cCI6MTU0NjUxODkwM30.C2TFKu3INTMzDL4ovNdO7UyV7hqxTzWeO63mRJ5fVdc");
 
        yield return www.SendWebRequest();
 
        if (www.isNetworkError || www.isHttpError)
        {
            Debug.Log($"{www.error}");
        }
        else
        {
            Debug.Log("aaaa");
            Debug.Log($"{www.downloadHandler.text}");
        }
    }
 
 
    // Update is called once per frame
    void Update()
    {
        
    }
}
 

postman 다운로드 

구글에서 postman 검색 

https://www.getpostman.com/

MysqlWorkbench 설치 

MariaDB다운로드 및 설치 

체크항목은 모두 체크 

비밀번호 root 

nodemon app.js

POST

http://localhost:3000/api/saveUnits

Body/Raw/JSON(application/json)

npm i mysql --save

http://192.168.0.133:3000/api/getUnits

nodejs_restful_api_unity

cmd 

ipconfig

('Warrior', 1.25,2.36, 0.32),('Warrior', 1.25,2.36, 0.32),('Warrior', 1.25,2.36, 0.32),('Warrior', 1.25,2.36, 0.32)

insert into units (unitName, posX, posY, posZ) values ('Warrior', 1.25,2.36, 0.32),('Warrior', 1.25,2.36, 0.32),('Warrior', 1.25,2.36, 0.32),('Warrior', 1.25,2.36, 0.32);

www.SetRequestHeader("Content-Type", "application/json");

List<IMultipartFormSection> formData = new List<IMultipartFormSection>();

        formData.Add( new MultipartFormFileSection("my file data", "myfile.txt") );

        UnityWebRequest www = UnityWebRequest.Post("http://www.my-server.com/myform", formData);

        yield return www.SendWebRequest();

 

        if(www.isNetworkError || www.isHttpError) {

            Debug.Log(www.error);

        }

        else {

            Debug.Log("Form upload complete!");

        }

IEnumerator Post(string url, string bodyJsonString)

    {

        var request = new UnityWebRequest("http://192.168.0.133:3000/api/saveUnits", "POST");

        byte[] bodyRaw = Encoding.UTF8.GetBytes(bodyJsonString);

        request.uploadHandler = (UploadHandler) new UploadHandlerRaw(bodyRaw);

        request.downloadHandler = (DownloadHandler) new DownloadHandlerBuffer();

        request.SetRequestHeader("Content-Type", "application/json");

 

        yield return request.Send();

 

        Debug.Log("Status Code: " + request.responseCode);

    }

npm i jsonwebtoken --save

eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VyIjp7ImlkIjoiaG9uZ0BnbWFpbC5jb20iLCJ1c2VyTmFtZSI6Iu2Zjeq4uOuPmSJ9LCJpYXQiOjE1NDY1MTUxNTUsImV4cCI6MTU0NjUxNTc1NX0.-TvcXEhjQocB4iSnHBG7gGDzofosXCAk_uo-VpWIoVU

jwt.io

Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VyIjp7ImlkIjoiaG9uZ0BnbWFpbC5jb20iLCJ1c2VyTmFtZSI6Iu2Zjeq4uOuPmSJ9LCJpYXQiOjE1NDY1MTUxNTUsImV4cCI6MTU0NjUxNTc1NX0.abz-vKX7Jq5drXM0qR0K5WrjIHx7Dcf55rB2Mo9w9o8

구글 

가입 

구글아이디, 기기아이디 (DB : User)

토큰 발급 : 2주 Auth 테이블 (아이디, 토큰) 

API를 마음껏 사용가능 

재접속 

구글아이디로 User테이블 검색 

회원가입 되어있다면 토큰발급 2주   

https://jwt.io/

https://www.npmjs.com/package/jsonwebtoken

use test;
 
create table units(
    unitName varchar(255) not null,
    posX float not null,
    posY float not null, 
    posZ float not null
);
 
create table users(
    id varchar(255) not null,
    userName varchar(255) not null,
    primary key(id)
);
 
insert into users (id, userName) values ('hong@gmail.com', '홍길동');
 
alter table users convert to character set utf8;
 
select * from users;