node.js link database
1. Premise
-
The computer has node installed. You can use the win+R key, enter cmd, and then enter node -v to check the version;
-
Then enter npm -V to check the installed version of npm
-
After all the above, create an empty folder named projects, which creates three empty folders: JQ, HTML and API
-
In the jq folder, import your own good jq library
2. Create a new JSON file
- Use vsCode to open the folder projects,
- Select the api folder with the mouse, right-click, select open in the integration terminal, and enter: npm init (all options in it are enter until the file path appears). Check whether package has appeared under the api folder JSON file
- Then enter: NPM install express MySQL cor body parser and wait for installation. Two json files appear after installation
- In the api folder, create two JS files, http js, db. js
3. Write code
- First open http JS file, import the library we have installed
//Introduction module
const express=require('express');
const app=express();
const url=require('url')
const db=require('./db.js');
- Solve cross regional problems
// Solve cross regional problems
// Import middleware cors
const cors = require('cors');
// Call app. before routing Use (cors()) configuration Middleware
app.use(cors());
app.all("*",function(req,res,next){
res.header("Acess-Control-Allow-Origin","*");
next();
})
- Set the port; Open the api folder terminal and enter node http JS to test whether the setting is successful
// Set port
app.listen(8888,function(){
console.log('Starting 888....');
})
- We open dB JS file and connect to the database (note that the name of the database you call is the same, and the user name and password are the same as those of your own database)
// Introducing mysql Middleware
const mysql=require('mysql')
// Create connection
let connection=mysql.createConnection({
// host database hostname
host:"localhost",
// User user name
user:"root",
// Password password
password:"123456",
// Name of the connected database
database:"in37"
})
//Connect database
connection.connect();
- Because I already have a table in the database here, the next data is mainly written around this table.
- Next, throw out the student number and name
// The first parameter is the access path, which can be accessed by yourself. For convenience, here is / table name / getOne, followed by req:request request and res:response response; callback return value
module.exports.selectBySno=function(sno,sname,callback){
let sql=`select * from student where sno='${sno}' and sname='${sname}'`;
connection.query(sql,function(err,data){
callback(data);
})
}
- Return to http The method of obtaining data is written in the JS file,
//Log in to get request through student number and name to get data
// let two variables respectively request the url, obtain the student number and name, and use the ternary writing method. If the return value is undefined, it is empty, otherwise it returns the corresponding value
app.get('/student/getOne',(req,res)=>{
let sno=url.parse(req.url,true).query.sno==undefined?"":url.parse(req.url,true).query.sno;
let sname=url.parse(req.url,true).query.sname==undefined?"":url.parse(req.url,true).query.sname;
connection.query(sql,function(err,data){
callback(data);
})
})
- We need to create an HTML page to test whether the display calls the database to display successfully and create a login HTML page and index HTML page is used for jump. The condition of login here is that the student number and name entered must be consistent with the data of the table in the called database
<body>
<table>
<tr>
<td>Student number</td>
<td><input type="text" id="tSno"></td>
</tr>
<tr>
<td>full name</td>
<td><input type="text" id="tSname"></td>
</tr>
<tr>
<td colspan="2"></td>
<button id="btnLogin">Sign in</button>
</tr>
</table>
</body>
- After the body tag, we import our jq library, calling the request in the page to write with jq.
<script src="../jq/jquery-3.5.1.min.js"></script>
<script>
$(function(){
// Set click event for login button
$("#btnLogin").click(function(){
$.ajax({
url:"http://127.0.0.1:8888/student/getOne",
data:{
sno:$("#tSno").val(),
sname:$("#tSname").val(),
},
//
success:function(result){
if(result.data.length>0){
alert("Login successful");
window.location.href="./index.html";
}else{
alert("Login failed");
$("#Sno").val("");
$("#Sname").val("");
}
}
})
})
})
</script>
- Realization effect
Here, you will successfully connect to the database content and log in and jump