前端与数据库交互
设计要求：
前端的HTML页面可以对数据库的一个数据表进行增删改查，并将结果以一个div的形式进行输出。
前端HTML页面（index.html）：
<!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width,minimum-scale=1.0,maximum-scale=1.0,user-scalable=no" /> <title>Powered By Leisureeen</title> <style> table { border-collapse: collapse; } td { border: 1px solid #448844; } </style> <script src="js/req.js"></script> <script type="text/javascript"> 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); } </script> </head> <body bgcolor="CCDDFF"> <div align="center" style="line-height: 30px;"> Please Select a Mode: <br> <input type="radio" name="ra" onclick="mode=0;setVis(1)" checked="checked"> 增 <input type="radio" name="ra" onclick="mode=1;setVis(0)"> 删 <input type="radio" name="ra" onclick="mode=2;setVis(1)"> 改 <input type="radio" name="ra" onclick="mode=3;setVis(1)"> 查 <br> id: <input type="text" id="id" maxlength="11" value="18876543210"> <br> name: <input type="text" id="name" maxlength="16" value="王炸"> <br> <input type="submit" value="Submit" onclick="b_req()"> <br> <div id="res" style="color: red"></div> </div> </body> </html>
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文件：
<?xml version="1.0" encoding="UTF-8"?> <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 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"> <display-name>html_db</display-name> <welcome-file-list> <welcome-file>index.html</welcome-file> <welcome-file>index.htm</welcome-file> <welcome-file>index.jsp</welcome-file> <welcome-file>default.html</welcome-file> <welcome-file>default.htm</welcome-file> <welcome-file>default.jsp</welcome-file> </welcome-file-list> <servlet> <servlet-name>database</servlet-name> <servlet-class>controller.Servlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>database</servlet-name> <url-pattern>/db.do</url-pattern> </servlet-mapping> </web-app>
后端收发数据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)<'0') 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+="<table>"; for(resN=0;res.next();resN++) outS+="<tr><td>"+res.getString(1)+"</td><td>"+res.getString(2)+"</td></tr>"; if(resN==0) outS+="<tr><td>无</td></tr>"; outS+="</table>"; outS="查询结果（共"+resN+"条记录）：<br>"+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;
posted on 2020-02-27 20:44 Leisureeen 阅读(3573) 评论(0) 编辑 收藏 举报
