WEB应用程序开发---实践开发
利用的相关技术与功能
基于MySQL+JavaJDBC+Ajax实现的学生信息管理系统附带演示图片+视频,前端利用了HTML+JavaScript+Jquery技术,来实现基本系统功能(增删改查)的操作实现
实践展示
代码呈现
注册功能代码
前端界面与功能
<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html><head><metahttp-equiv="Content-Type"content="text/html; charset=utf-8"/><title>无标题文档</title><linkhref="css/style.css"rel="stylesheet"type="text/css"/></head><scriptsrc="js/jquery-1.8.3.min.js"type="text/javascript"charset="utf-8"></script><script>functioncheckAccount(account){
$.get("back/register",{account:account},function(res){if(res==1){$("#msgId").html("账号已注册");return;}elseif(res==0){$("#msgId").html("√");}else{$("#msgId").html("服务器忙!");}});}functionsubform(){
$.post("back/register",$("#forminfo").serialize(),function(res){if(res==0){alert("注册成功");
location.assign("login.html");}else{alert("服务器忙!");}});}</script><body><divclass="formbody"><divclass="formtitle"><span>基本信息</span></div><ulclass="forminfo"id="forminfo"><li><label>用户名</label><inputname="admin"type="text"class="dfinput"onblur="checkAccount(this.value)"/><br/><spanid="msgId"></span><br/></li><li><label>密码设置</label><inputname="password"type="text"class="dfinput"/></li><br/><inputvalue="登录"style="width:40%;height: 50px"type="button"onclick="subform()"></ul></div></body></html>
LoginServlet部分代码
packagecom.qn.FProgram.Servlet;importcom.qn.FProgram.Dao.RegisterDao;importjavax.servlet.ServletException;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importjava.io.IOException;importjava.io.PrintWriter;publicclassRegisterServletextendsHttpServlet{//主要用于向数据库发送信息@OverrideprotectedvoiddoPost(HttpServletRequest req,HttpServletResponse resp)throwsServletException,IOException{PrintWriter out=null;try{
out= resp.getWriter();String account1 = req.getParameter("account");//从表单中获取account的值String password1 = req.getParameter("password");RegisterDao registerDao=newRegisterDao();
registerDao.save(account1,password1);
out.println(0);}catch(Exception e){
e.printStackTrace();
out.println(1);}}//主要用于进行消息验证@OverrideprotectedvoiddoGet(HttpServletRequest req,HttpServletResponse resp)throwsServletException,IOException{PrintWriter out =null;try{
out = resp.getWriter();String account = req.getParameter("account");RegisterDao registerDao =newRegisterDao();int res = registerDao.select(account);
out.println(res);//0-未注册 非0已注册}catch(Exception e){
e.printStackTrace();
out.println(500);}}}
RegisterDao部分代码
packagecom.qn.FProgram.Dao;importjava.sql.*;publicclassRegisterDao{publicintselect(String account)throwsSQLException,ClassNotFoundException{Connection connection =null;PreparedStatement pt =null;ResultSet resultSet =null;try{Class.forName("com.mysql.jdbc.Driver");
connection =DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai","root","root");
pt = connection.prepareStatement("select count(*) from t_student where account=?");
pt.setString(1, account);
resultSet=pt.executeQuery();
resultSet.next();return resultSet.getInt(1);}finally{if(connection !=null){
connection.close();}if(pt !=null){
pt.close();}}}publicvoidsave(String account,String password)throwsClassNotFoundException,SQLException{Connection connection=null;PreparedStatement ps=null;Class.forName("com.mysql.jdbc.Driver");
connection =DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai","root","root");
ps=connection.prepareStatement("insert into t_student(account,password)values(?,?)");
ps.setString(1,account);
ps.setString(2,password);
ps.executeUpdate();if(connection !=null){
connection.close();}if(ps !=null){
ps.close();}}}
user部分代码
packagecom.qn.FProgram.Mode;importjava.util.Date;publicclassUser{privateint id;privateString account;privateString password;privateDate reg_time;publicintgetId(){return id;}publicvoidsetId(int id){this.id = id;}publicStringgetAccount(String string){return account;}publicvoidsetAccount(String account){this.account = account;}publicStringgetPassword(String string){return password;}publicvoidsetPassword(String password){this.password = password;}publicDategetReg_time(){return reg_time;}publicvoidsetReg_time(Date reg_time){this.reg_time = reg_time;}@OverridepublicStringtoString(){return"User{"+"id="+ id +", account='"+ account +'\''+", password='"+ password +'\''+", reg_time="+ reg_time +'}';}}
登录界面
Login界面代码
<!DOCTYPEhtml><html><head><metacharset="UTF-8"><title>后台管理</title><linkhref="css/login.css"rel="stylesheet"type="text/css"/></head><scriptsrc="js/jquery-1.8.3.min.js"type="text/javascript"charset="utf-8"></script><scripttype="application/javascript">functionsubform(){
$.post("back/login",$("#formId").serialize(),function(res){var obj=$.parseJSON(res)//将接收值转化为字符串if(obj.id!=null){alert("登录成功");//在前端储存用户信息
window.sessionStorage.setItem("user",res);
location.replace("main.html");}else{alert("账号或密码错误");}})}</script><body><divclass="login_box"><divclass="login_l_img"><imgsrc="images/login-img.png"/></div><divclass="login"><divclass="login_logo"><ahref="#"><imgsrc="images/login_logo.png"/></a></div><divclass="login_name"><p>后台管理系统</p></div><formmethod="post"id="formId"><inputname="account"type="text"value="账号"><inputname="password"type="password"id="password"/><inputvalue="登录"style="width:100%;"type="button"onclick="subform()"><br/><br/><divalign="center"><ahref="register.html">没有账号?点击注册</a></div></form></div><divclass="copyright">某某有限公司 版权所有©2016-2018 技术支持电话:000-00000000</div></div></body></html>
LoginServlet部分代码
packagecom.qn.FProgram.Servlet;importcom.google.gson.Gson;importcom.qn.FProgram.Dao.LoginDao;importcom.qn.FProgram.Mode.User;importjavax.servlet.ServletException;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importjavax.servlet.http.HttpSession;importjava.io.IOException;importjava.io.PrintWriter;publicclassLoginServletextendsHttpServlet{@OverrideprotectedvoiddoPost(HttpServletRequest req,HttpServletResponse resp)throwsServletException,IOException{PrintWriter out=null;//ServletContext一个应用程序对应一个ServletContext对象,储存全局信息/* ServletContext servletContext= req.getServletContext();
System.out.println(servletContext);
System.out.println(servletContext.getInitParameter("name"));//获得当前应用的初始化参数
System.out.println(servletContext.getAttribute("a"));*/try{
resp.setContentType("text/html;charset=utf-8");//响应格式设置
req.setCharacterEncoding("utf-8");//解码格式String account = req.getParameter("account");//从表单中获取account的值String password = req.getParameter("password");LoginDao loginDao=newLoginDao();User user= loginDao.checkLogin(account,password);
out=resp.getWriter();//java程序一般将数据封装到对象中,响应到客户端,而客户端是JavaScript语言,俩边对象格式不一致//为解决此问题诞生了一种轻量级解决方案:Json(JavaScript对象表现形式)是一种轻量级数据格式if(user!=null){HttpSession httpSession=req.getSession();
httpSession.setAttribute("user",user);Gson gson=newGson();String s=gson.toJson(user);System.out.println(s);
out.println(s);}else{
out.println(1);}}catch(Exception e){
e.printStackTrace();
out.println("服务器忙");}}@OverrideprotectedvoiddoGet(HttpServletRequest req,HttpServletResponse resp)throwsServletException,IOException{HttpSession session = req.getSession();User user=(User)session.getAttribute("user");
session.invalidate();//销毁浏览器}}
LoginDao部分代码
packagecom.qn.FProgram.Dao;importcom.qn.FProgram.Mode.User;importjava.sql.*;publicclassLoginDao{publicUsercheckLogin(String account,String password)throwsSQLException,ClassNotFoundException{Connection connection =null;PreparedStatement ps =null;ResultSet resultSet =null;User user =null;try{Class.forName("com.mysql.cj.jdbc.Driver");/* 创建与数据库连接的方式*/
connection =DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai","root","root");
ps = connection.prepareStatement("select id,account,password from t_student where account=?and password=?");
ps.setString(1, account);
ps.setString(2, password);
resultSet = ps.executeQuery();if(resultSet.next()){
user =newUser();
user.setId(resultSet.getInt("id"));
user.setAccount(resultSet.getString("account"));
user.setPassword(resultSet.getString("password"));}}finally{if(connection !=null){
connection.close();}if(ps !=null){
ps.close();}if(resultSet !=null){
resultSet.close();}}return user;}}
登陆成功后的主界面
<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Frameset//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-frameset.dtd"><html><head><metahttp-equiv="Content-Type"content="text/html; charset=utf-8"/><title>信息管理系统界面</title><linkhref="css/style.css"rel="stylesheet"type="text/css"/><scriptsrc="js/jquery-1.8.3.min.js"type="text/javascript"charset="utf-8"></script><styletype="text/css">*{margin: 0px;padding: 0px;}</style><scripttype="text/javascript">$(function(){var str=window.sessionStorage.getItem("user");if(str==null){
location.replace("login.html");return;}var user=$.parseJSON(str);$("#account").html(user.account);})</script></head><body><tableborder="1"cellspacing="0"cellpadding="0"width="100%"height="100%"><trstyle="background:url(images/topbg.gif) repeat-x;"><tdcolspan="2"><divclass="topleft"><imgsrc="images/logo.png"title="系统首页"/></div><divclass="topright"><ul><li><span><imgsrc="images/help.png"title="帮助"class="helpimg"/></span><ahref="#">帮助</a></li><li><ahref="#">关于</a></li><li><ahref="login.html"target="_parent">退出</a></li></ul><divclass="user"><spanid="account"></span></div></div></td></tr><tr><tdwidth="187"valign="top"height="100%"style="background:#f0f9fd;"><divclass="lefttop"><span></span>操作菜单</div><dlclass="leftmenu"><dd><divclass="title"><span><imgsrc="images/leftico01.png"/></span><ahref="">管理信息</a></div><ulclass="menuson"><li><cite></cite><ahref="student/list.html"target="rightFrame">信息管理</a></li></ul></dd></dl></td><td><iframename="rightFrame"src="student/list.html"width="100%"height="600"></iframe></td></tr></table></body></html>
增删改查功能部分代码
中间通过Student类当做传输信息的媒介,将信息集合进行传输
packagecom.qn.FProgram.Mode;importjava.util.Date;publicclassStudent{privateint id;privateint number;privateString name;privateString sex;privateString grade;privateint telephone;privateString address;privateString user;privateDate oper_time;privateint gradeId;publicintgetId(){return id;}publicvoidsetId(int id){this.id = id;}publicintgetNumber(){return number;}publicvoidsetNumber(int number){this.number = number;}publicStringgetName(){return name;}publicvoidsetName(String name){this.name = name;}publicStringgetSex(){return sex;}publicvoidsetSex(String sex){this.sex = sex;}publicStringgetGrade(){return grade;}publicvoidsetGrade(String grade){this.grade = grade;}publicintgetTelephone(){return telephone;}publicvoidsetTelephone(int telephone){this.telephone = telephone;}publicStringgetAddress(){return address;}publicvoidsetAddress(String address){this.address = address;}publicStringgetUser(){return user;}publicvoidsetUser(String user){this.user = user;}publicDategetOper_time(){return oper_time;}publicvoidsetOper_time(Date oper_time){this.oper_time = oper_time;}publicintgetGradeId(){return gradeId;}publicvoidsetGradeId(int gradeId){this.gradeId = gradeId;}@OverridepublicStringtoString(){return"Student{"+"id="+ id +", number="+ number +", name='"+ name +'\''+", sex='"+ sex +'\''+", grade='"+ grade +'\''+", telephone="+ telephone +", address='"+ address +'\''+", user='"+ user +'\''+", oper_time="+ oper_time +'}';}}
增加功能
<!DOCTYPEhtml><htmllang="en"><head><metacharset="UTF-8"><title>Title</title><linkhref="../css/style.css"rel="stylesheet"type="text/css"/><scriptsrc="../js/jquery-1.8.3.min.js"type="text/javascript"charset="utf-8"></script><script>$(function(){
$.get("../back/student",{mark:"gradelist"},function(res){if(res==201){
window.parent.location.replace("../login.html");}elseif(res==500){alert("服务器忙");}else{var str="";for(i=0;i<res.length;i++){
str+="<option value='"+res[i].id+"'>"+res[i].grade+"</option>";}$("#gradeid").append(str);}},"json");})//验证学号functioncheckNum(number){var result=true;if(number.length==0){return;}
$.ajax({
url:"../back/student",
type:"get",
data:{number:number,mark:"checkNum"},
async:false,success:function(res){if(res==1){$("#msgid").html("学号已注册,再换一个吧");
result =false;}elseif(res==0){$("#msgid").html("学号未使用,可以注册");
result =true;}else{$("#msgid").html("服务器忙");
result =false;}}})return result;}functionsave(){var result=checkNum($("#number").val());
console.log(result);if(result){
$.post("../back/student",$("#formid").serialize(),function(res){if(res==201){alert("服务器出错");}elseif(res==500){alert("保存出错");}else{alert("保存成功");
location.assign("../student/list.html");}})}}</script></head><body><formid="formid"><divclass="formbody"><divclass="formtitle"><span>基本信息</span></div><ulclass="forminfo"><li><label>学号:</label><inputtype="text"class="dfinput"name="number"id="number"onblur="checkNum(this.value)"><br/><spanid="msgid"></span></td></li><li><label>姓名:</label><inputtype="text"class="dfinput"name="name"id="nameid"/></li><li><label>性别:</label><inputtype="radio"name="sex"value="男">男
<inputtype="radio"name="sex"value="女">女
</li><li><label>年级:</label><selectid="gradeid"name="gradeid"class="dfinput"></select></li><li><label>电话:</label><inputtype="text"name="telephone"class="dfinput"></li><li><label>家庭地址:</label><inputtype="text"name="address"id="address"class="dfinput"></li><li><inputtype="button"value="保存"class="btn"onclick="save()"></li></ul></div></form></body></html>
删减功能
其中包含List.html界面,我们将利用内联框架嵌入式main.html中
<!DOCTYPEhtml><htmllang="en"><head><metahttp-equiv="Content-Type"content="text/html; charset=utf-8"/><title>无标题文档</title><linkhref="../css/style.css"rel="stylesheet"type="text/css"/><scriptsrc="../js/jquery-1.8.3.min.js"type="text/javascript"charset="utf-8"></script><script>$(function(){
$.get("../back/student",{mark:"list"},function(res){if(res==201){
window.parent.location.replace("../login.html");}elseif(res==500){alert("服务器忙");}else{var str ="";for(var i =0; i < res.length; i++){
str +="<tr align='center'>";
str +="<td>"+(i +1)+"</td>";
str +="<td>"+ res[i].name +"</td>";
str +="<td>"+ res[i].sex +"</td>";
str +="<td>"+ res[i].grade+"</td>";
str +="<td>"+res[i].telephone+"</td>";
str +="<td>"+res[i].address+"</td>";
str +="<td>"+res[i].user+"</td>";
str +="<td>"+(newDate(res[i].oper_time).toLocaleString())+"</td>";
str +="<td><a href='javaScript:void(0)' οnclick='update("+res[i].id+")'>修改</a>"+" "+"<a href='javaScript:void(0)' οnclick='del("+res[i].id+")'>删除</a></td>";
str +="</tr>";}$("#tableid").append(str);}},"json");})functionadd(){
location.assign("Add.html");}functiondel(id){if(confirm("您确定删除?")){
$.get("../back/student",{mark:"del",id:id},function(res){if(res==201){alert(登录失效);}elseif(res==200){alert("删除成功");
location.reload();}else{alert("删除失败");}})}}functionupdate(id){
location.assign("Update.html?id="+id);
console.log(id)}</script></head><body><divclass="rightinfo"><!-- 顶部按钮 --><divclass="tools"><ulclass="toolbar"><ahref="../student/Add.html"><liclass="click"><span><imgsrc="../images/t01.png"/></span>
添加
</li></a></ul></div><!--数据列表 --><tableclass="tablelist"id="tableid"><tr><th>编号<iclass="sort"><imgsrc="../images/px.gif"/></i></th><th>姓名</th><th>性别</th><th>年级</th><th>电话</th><th>地址</th><th>操作人</th><th>操作时间</th><th>操作</th></tr></table></div></body></html>
更新操作
```java
<!DOCTYPEhtml><htmllang="en"><head><metacharset="UTF-8"><title>Title</title></head><linkhref="../css/style.css"rel="stylesheet"type="text/css"/><scriptsrc="../js/jquery-1.8.3.min.js"type="text/javascript"charset="utf-8"></script><script>$(function(){//获取路径中?后的参数var id=(location.search).split("=")[1];//页面打开后,自动向servlet发送请求$(function(){
$.get("../back/student",{mark:"gradelist"},function(res){if(res==201){
window.parent.location.replace("../login.html");}elseif(res==500){alert("服务器忙");}else{var str="";for(i=0;i<res.length;i++){
str+="<option value='"+res[i].id+"'>"+res[i].grade+"</option>";}$("#gradeid").append(str);}},"json");})//根据学生id查询学生信息
$.get("../back/student",{mark:"findStudent",id:id},function(res){if(res==201){
window.parent.location.replace("../login.html");}elseif(res==500){alert("服务器忙");}else{
console.log(res);$("input[name='number']").val(res.number);$("input[name='name']").val(res.name);if(res.sex=='男'){$("input[value='男']").attr("checked",true);}else{$("input[value='女']").attr("checked",true);}$("input[name='telephone']").val(res.telephone);$("input[name='address']").val(res.address);$("input[name='id']").val(res.id);}},"json");})functionsave(){
$.post("../back/student",$("#formid",).serialize(),function(res){if(res==201){alert("服务器出错");}elseif(res==500){alert("保存出错");}else{alert("保存成功");
location.assign("../student/list.html");}})}</script><body><formid="formid"><divclass="formbody"><divclass="formtitle"><span>基本信息</span></div><inputtype="hidden"name="id"><ulclass="forminfo"><li><label>学号:</label><inputtype="text"class="dfinput"name="number"id="numid"><spanid="msgid"></span></td></li><li><label>姓名:</label><inputtype="text"class="dfinput"name="name"id="nameid"/></li><li><label>性别:</label><inputtype="radio"name="sex"value="男">男
<inputtype="radio"name="sex"value="女">女
</li><li><label>年级:</label><selectid="gradeid"name="gradeid"class="dfinput"></select></li><li><label>电话:</label><inputtype="text"name="telephone"class="dfinput"></li><li><label>家庭地址:</label><inputtype="text"name="address"id="address"class="dfinput"></li><li><inputtype="button"value="修改"class="btn"onclick="save()"></li></ul></div></form></body></html>
中间将利用StudentServlet进行与Dao层的交互
packagecom.qn.FProgram.Servlet;importcom.google.gson.Gson;importcom.qn.FProgram.Dao.StudentDao;importcom.qn.FProgram.Mode.Grade;importcom.qn.FProgram.Mode.Student;importcom.qn.FProgram.Mode.User;importjavax.servlet.ServletException;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importjava.io.*;importjava.sql.SQLException;importjava.util.List;publicclassStudentServletextendsHttpServlet{@OverrideprotectedvoiddoGet(HttpServletRequest req,HttpServletResponse resp)throwsServletException,IOException{String mark=req.getParameter("mark");if(mark.equals("list")){PrintWriter out=null;try{
resp.setContentType("text/html;charset=utf-8");
out=resp.getWriter();//查询学生信息列表StudentDao studentDao=newStudentDao();List<Student> studentList = studentDao.findStudentList();//返回学生集合
out.print(newGson().toJson(studentList));//转为字符串进行输出System.out.println(newGson().toJson(studentList));}catch(Exception e){
e.printStackTrace();
out.print(500);}}elseif(mark.equals("gradelist")){PrintWriter out=null;try{
resp.setContentType("text/html;charset=utf-8");
out= resp.getWriter();StudentDao studentDao1=newStudentDao();List<Grade>gradeList= studentDao1.findGradeList();
out.print(newGson().toJson(gradeList));}catch(Exception e){
e.printStackTrace();
out.print(500);}}elseif(mark.equals("del")){PrintWriter out=null;try{
resp.setContentType("text/html;charset=utf-8");
out= resp.getWriter();String id=req.getParameter("id");StudentDao studentDao2=newStudentDao();
studentDao2.deleteStudent(id);
out.print(200);}catch(Exception e){
e.printStackTrace();
out.print(500);}}elseif(mark.equals("checkNum")){PrintWriter out=null;try{
resp.setContentType("text/html;charset=utf-8");
out= resp.getWriter();String number=req.getParameter("number");StudentDao studentDao3=newStudentDao();int i= studentDao3.checkNum(number);
out.print(i);}catch(Exception e){
e.printStackTrace();
out.print(500);}}elseif(mark.equals("findStudent")){PrintWriter out=null;try{
resp.setContentType("text/html;charset=utf-8");
out= resp.getWriter();String id=req.getParameter("id");StudentDao studentDao4=newStudentDao();Student student=studentDao4.findStudentByID(id);
out.print(newGson().toJson(student));}catch(Exception e){
e.printStackTrace();
out.print(500);}}}@OverrideprotectedvoiddoPost(HttpServletRequest req,HttpServletResponse resp)throwsServletException,IOException{PrintWriter out =null;try{String number = req.getParameter("number");String id = req.getParameter("id");String name = req.getParameter("name");String sex = req.getParameter("sex");String address = req.getParameter("address");String telephone = req.getParameter("telephone");String gradeid = req.getParameter("gradeid");User user =(User) req.getSession().getAttribute("user");//获得登录的用户StudentDao addDao =newStudentDao();if(id ==null){
addDao.save(number, name, sex, address, telephone, gradeid, user.getId());}else{
addDao.updateStudent(id, name, sex, address, telephone, gradeid, user.getId());}}catch(Exception e){
e.printStackTrace();
out.print(500);}}}
StudentDao代码
packagecom.qn.FProgram.Dao;importcom.qn.FProgram.Mode.Grade;importcom.qn.FProgram.Mode.Student;importjava.sql.*;importjava.util.ArrayList;importjava.util.List;publicclassStudentDao{publicList<Student>findStudentList()throwsClassNotFoundException,SQLException{Connection connection =null;PreparedStatement pt =null;ResultSet resultSet =null;List<Student>list=newArrayList<>();try{Class.forName("com.mysql.jdbc.Driver");
connection =DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai","root","root");
pt = connection.prepareStatement("SELECT \n"+"s.id,\n"+"s.num,\n"+"s.name,\n"+"s.sex,\n"+"s.address,\n"+"s.phone,\n"+"g.gradename,\n"+"u.account,\n"+"s.oper_time\n"+"FROM student s LEFT JOIN grade g ON s.gradeid=g.id\n"+" LEFT JOIN USER u ON s.userid=u.id\n"+"\n");
resultSet = pt.executeQuery();while(resultSet.next()){Student student=newStudent();
student.setId(resultSet.getInt("id"));
student.setNumber(resultSet.getInt("num"));
student.setName(resultSet.getString("name"));
student.setSex(resultSet.getString("sex"));
student.setAddress(resultSet.getString("address"));
student.setTelephone(resultSet.getInt("phone"));
student.setGrade(resultSet.getString("gradename"));
student.setUser(resultSet.getString("account"));
student.setOper_time(resultSet.getTimestamp("oper_time"));
list.add(student);}}finally{if(connection !=null){
connection.close();}if(pt !=null){
pt.close();}if(resultSet !=null){
resultSet.close();}}return list;}publicList<Grade>findGradeList()throwsClassNotFoundException,SQLException{Connection connection =null;PreparedStatement pt =null;ResultSet resultSet =null;List<Grade>list=newArrayList<>();try{Class.forName("com.mysql.jdbc.Driver");
connection =DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai","root","root");
pt = connection.prepareStatement("SELECT id,gradename FROM grade");
resultSet = pt.executeQuery();while(resultSet.next()){Grade grade=newGrade();
grade.setId(resultSet.getInt("id"));
grade.setGrade(resultSet.getString("gradename"));
list.add(grade);}}finally{if(connection !=null){
connection.close();}if(pt !=null){
pt.close();}if(resultSet !=null){
resultSet.close();}}return list;}publicvoidsave(String number,String name,String sex,String address,String telephone,String gradeid,int userid)throwsClassNotFoundException,SQLException{Connection connection=null;PreparedStatement ps=null;Class.forName("com.mysql.jdbc.Driver");
connection =DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai","root","root");
ps=connection.prepareStatement("insert into student(num,name,sex,address,phone,gradeid,userid,oper_time)values(?,?,?,?,?,?,?,now())");
ps.setString(1,number);
ps.setString(2,name);
ps.setString(3,sex);
ps.setString(4,address);
ps.setString(5,telephone);
ps.setString(6,gradeid);
ps.setObject(7,userid);
ps.executeUpdate();if(connection !=null){
connection.close();}if(ps !=null){
ps.close();}}publicvoiddeleteStudent(String id)throwsClassNotFoundException,SQLException{Connection connection =null;PreparedStatement pt =null;try{Class.forName("com.mysql.jdbc.Driver");
connection =DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai","root","root");
pt = connection.prepareStatement("delete from student where id= ?");
pt.setObject(1, id);
pt.executeUpdate();}finally{if(connection !=null){
connection.close();}if(pt !=null){
pt.close();}}}publicintcheckNum(String number)throwsClassNotFoundException,SQLException{Connection connection =null;PreparedStatement pt =null;ResultSet resultSet =null;try{Class.forName("com.mysql.jdbc.Driver");
connection =DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai","root","root");
pt = connection.prepareStatement("select count(*) from student where num=?");
pt.setString(1, number);
resultSet=pt.executeQuery();
resultSet.next();return resultSet.getInt(1);}finally{if(connection !=null){
connection.close();}if(pt !=null){
pt.close();}if(resultSet!=null){
resultSet.close();}}}publicStudentfindStudentByID(String id)throwsClassNotFoundException,SQLException{Connection connection =null;PreparedStatement pt =null;ResultSet resultSet =null;Student student=newStudent();try{Class.forName("com.mysql.jdbc.Driver");
connection =DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai","root","root");
pt = connection.prepareStatement(" SELECT id,num,NAME,sex,address,phone,gradeid FROM student s WHERE id=? ");
pt.setObject(1, id);
resultSet = pt.executeQuery();while(resultSet.next()){
student.setId(resultSet.getInt("id"));
student.setNumber(resultSet.getInt("num"));
student.setName(resultSet.getString("name"));
student.setSex(resultSet.getString("sex"));
student.setAddress(resultSet.getString("address"));
student.setTelephone(resultSet.getInt("phone"));
student.setGradeId(resultSet.getInt("gradeid"));}}finally{if(connection !=null){
connection.close();}if(pt !=null){
pt.close();}if(resultSet !=null){
resultSet.close();}}return student;}publicvoidupdateStudent(String id,String name,String sex,String address,String telephone,String gradeid,int userid)throwsClassNotFoundException,SQLException{Connection connection =null;PreparedStatement pt =null;try{Class.forName("com.mysql.jdbc.Driver");
connection =DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai","root","root");
pt = connection.prepareStatement(" UPDATE student SET NAME=?,sex=?,address=?,phone=?,gradeid=?,userid=?,oper_time=NOW() WHERE id= ?");
pt.setObject(1, name);
pt.setObject(2, sex);
pt.setObject(3, address);
pt.setObject(4, telephone);
pt.setObject(5, gradeid);
pt.setObject(6, userid);
pt.setObject(7, id);
pt.executeUpdate();}finally{if(connection !=null){
connection.close();}if(pt !=null){
pt.close();}}}}
进行更新时将自动查询所在人的年级,为方便我们将搭建一个Grade类
grade类
packagecom.qn.FProgram.Mode;publicclassGrade{privateint id;privateString grade;publicintgetId(){return id;}publicvoidsetId(int id){this.id = id;}publicStringgetGrade(){return grade;}publicvoidsetGrade(String grade){this.grade = grade;}}
数据库代码
CREATETABLEUSER(
id INTPRIMARYKEYAUTO_INCREMENT,
account VARCHAR(10)UNIQUENOTNULL,
PASSWORD VARCHAR(20)NOTNULL,
reg_time DATETIME)CREATETABLE grade(
id INTPRIMARYKEYAUTO_INCREMENT,
NAME CHAR(10)NOTNULL)
ALTERTABLE student ADDCONSTRAINT fk_userid FOREIGNKEY(userid)REFERENCESUSER(id)ALTERTABLE student ADDCONSTRAINT fk_gradeid FOREIGNKEY(gradeid)REFERENCES grade(id)
总结
通过此次实践我们可以明显感觉到代码量的巨大与繁琐,在后续我们将学习新的框架部分代码对总结构进行优化还有对新功能的拓展,尽请期待,再见!
版权归原作者 ☆青鸟☆ 所有, 如有侵权,请联系我们删除。