mysql数据库和前端交互,前端与数据库交互

设计要求：

前端的HTML页面可以对数据库的一个数据表进行增删改查，并将结果以一个div的形式进行输出。

前端HTML页面(index.html)：

Powered By Leisureeen

table {

border-collapse: collapse;

}

td {

border: 1px solid #448844;

}

var mode = 0;

function setVis(vis) {

if (vis == 0)

document.getElementById('name').style.visibility = "hidden";

else

document.getElementById('name').style.visibility = "visible";

}

function b_req() {

var idV = document.getElementById("id").value;

var nameV = document.getElementById("name").value;

var result = document.getElementById("res");

Request("db.do", "post", "mode=" + mode + "&id=" + idV + "&name="

+ nameV, result);

}

Please Select a Mode:

增

删

改

查

  id:  

name:

req.js文件：

function Request(url, action, json, result) {

var httpRequest = new XMLHttpRequest();

httpRequest.open(action, url, true);

httpRequest.setRequestHeader("Content-type",

"application/x-www-form-urlencoded");

httpRequest.send(json);

httpRequest.onreadystatechange = function() {

if (httpRequest.readyState == 4 && httpRequest.status == 200)

result.innerHTML = httpRequest.responseText;

};

}

web.xml文件：

xmlns="http://java.sun.com/xml/ns/javaee"

xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"

id="WebApp_ID" version="3.0">

html_db

index.html

index.htm

index.jsp

default.html

default.htm

default.jsp

database

controller.Servlet

database

/db.do

后端收发数据Java类(Servlet.java)：

package controller;

import java.io.IOException;

import java.sql.SQLException;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import math.*;

public class Servlet extends HttpServlet{

protected void doPost(HttpServletRequest req,HttpServletResponse resp) throws ServletException, IOException{

req.setCharacterEncoding("utf-8");// 这条语句竟然是我因为乱码然后自己摸索出来的，必须写上一行中文注释

String mode=req.getParameter("mode");

int modeI=Integer.parseInt(mode);

String id=req.getParameter("id");

String name=req.getParameter("name");

resp.setContentType("application/json; charset=utf-8");

if(modeI>3||modeI<0||name.length()>16)

resp.getWriter().print("意外的错误。");

else if(!(Str.isPhone(id)||modeI==3&&id.equals("")))

resp.getWriter().print("id格式错误，应为11位手机号。");

else if(modeI%2==0&&name.equals(""))

resp.getWriter().print("请输入姓名！");

else

try{

resp.getWriter().print(DB.dataIn(modeI,id,name));

}catch(ClassNotFoundException e){

// e.printStackTrace();

resp.getWriter().print("ClassNotFoundException");

}catch(SQLException e){

// e.printStackTrace();

resp.getWriter().print("SQLException");

}

}

}

后端字符串处理Java类(Str.java)：

package math;

public class Str{

public static boolean isPhone(String s){

if(s.length()!=11)

return false;

if(s.charAt(0)!='1')

return false;

for(int i=1;i<=10;i++)

if(s.charAt(i)>'9'||s.charAt(i)

return false;

return true;

}

}

后端数据库处理Java类(DB.java)：

package controller;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import com.mysql.jdbc.PreparedStatement;

public class DB{

public static String dataIn(int mode,String id,String name) throws ClassNotFoundException, SQLException{

int resN=0;

String preStr="",outS="";

Class.forName("com.mysql.jdbc.Driver");

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/html_db?characterEncoding=utf-8","root",

"123456");

if(mode==0){

preStr="insert `maintable` values('"+id+"','"+name+"')";

PreparedStatement ps=(PreparedStatement)con.prepareStatement(preStr);

resN=ps.executeUpdate();

outS+="操作成功，Inserted:"+id+"。";

ps.close();

}else if(mode==1){

preStr="delete from `maintable` where `id`='"+id+"'";

PreparedStatement ps=(PreparedStatement)con.prepareStatement(preStr);

resN=ps.executeUpdate();

outS+="操作成功，Deleted:"+id+"。";

ps.close();

}else if(mode==2){

preStr="update `maintable` set `name`='"+name+"' where `id`='"+id+"'";

PreparedStatement ps=(PreparedStatement)con.prepareStatement(preStr);

resN=ps.executeUpdate();

outS+="操作成功，Updated:"+id+"。";

ps.close();

}else{

if(!id.equals(""))

preStr="select * from `maintable` where `id`='"+id+"'";

else if(!name.equals(""))

preStr="select * from `maintable` where `name`='"+name+"'";

else

preStr="select * from `maintable`";

PreparedStatement ps=(PreparedStatement)con.prepareStatement(preStr);

ResultSet res=ps.executeQuery();

outS+="

for(resN=0;res.next();resN++)

outS+="

"+res.getString(1)+""+res.getString(2)+"";

if(resN==0)

outS+="

无";

outS+="

";

outS="查询结果(共"+resN+"条记录)：
"+outS;

res.close();

ps.close();

}

con.close();

return outS;

}

}

数据库初始化文件(html_db.sql)：

CREATE DATABASE /*!32312 IF NOT EXISTS*/`html_db` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `html_db`;

/*Table structure for table `maintable` */

DROP TABLE IF EXISTS `maintable`;

CREATE TABLE `maintable` (

`id` char(11) NOT NULL,

`name` char(16) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

标签：ps,outS,name,数据库,id,import,preStr,交互,前端

来源： https://www.cnblogs.com/leisureeen/p/12374267.html