实验八 JDBC操作数据库

一、实验目的

1.JDBC连接数据库。

2.对数据库的增删改查操作。

3.分页操作。

4.掌握数据库连接及数据处理技术，理解分页技术，掌握一种基本的分页技术。

二、实验内容

1、教材8.12.1节 查询成绩。

2、教材8.12.2节 管理学生成绩。

3、教材8.12.3节 小星星广告网。

三、实验步骤

1.查询成绩

① 实验代码

文件：ch8_1.jsp

 #tom{font-family:宋体;font-size:28;color:black } 输入学号或姓名查询成绩：

 

文件：ch8_2.jsp

#tom{font-family:宋体;font-size:18;color:blue }<%request.setCharacterEncoding("utf-8");String mess = request.getParameter("mess");mess = mess.trim();String submit = request.getParameter("submit");if(mess.length() == 0){response.sendRedirect("ch8_1.jsp");return;}Connection con=null;Statement sql; ResultSet rs;try{Class.forName("com.mysql.cj.jdbc.Driver"); }catch(Exception e){out.print("
"+e);}String url = "jdbc:mysql://localhost:3306/student?"+"useSSL=false&serverTimezone=CST&characterEncoding=utf-8";String user ="root";String password ="1234";out.print("");out.print("");out.print(""); try{con = DriverManager.getConnection(url,user,password);sql=con.createStatement(); String SQL = null;if(submit.contains("姓名")){SQL = "SELECT * FROM scoreReport where name like '%"+mess+"%'";}else if(submit.contains("学号")){SQL = "SELECT * FROM scoreReport where id='"+mess+"'"; }rs=sql.executeQuery(SQL);while(rs.next()) {out.print("");out.print(""); out.print("");out.print("");out.print("");out.print("") ; }out.print("
"+"学号");out.print("
"+"姓名");out.print("
"+"数学成绩");out.print("
"+"英语成绩");out.print("
"+rs.getString(1)+"
"+rs.getString(2)+"
"+rs.getInt(3)+"
"+rs.getInt(4)+"
");con.close();}catch(SQLException e) { out.print("
"+e);}%>

② 运行结果

2.管理学生成绩

① 实验代码

文件：context.xml

文件：ch8_3.jsp

 #tom{font-family:宋体;font-size:28;color:black }<%request.setCharacterEncoding("utf-8");Connection con=null;PreparedStatement pre=null; ResultSet rs;Contextcontext =new InitialContext();ContextcontextNeeded=(Context)context.lookup("java:comp/env");DataSource ds=(DataSource)contextNeeded.lookup("studentConn");try{ con = ds.getConnection();}catch(Exception exp){ }String updateSQL = "update scoreReport set name =?,mathScore=?,englishScore=? where id=?";String insertSQL ="insert into scoreReport values(?,?,?,?)";String deleteSQL ="delete from scoreReport where id =?";String querySQL = "select * from scoreReport where id =?";String mess = request.getParameter("submit");if(mess == null) mess ="";String id = request.getParameter("id");String name = request.getParameter("name");String math = request.getParameter("mathScore");String english = request.getParameter("englishScore");try{if(mess.contains("查看")){ pre = con.prepareStatement(querySQL); pre.setString(1,id); rs = pre.executeQuery(); if(rs.next()){ id = rs.getString(1); name = rs.getString(2);math = rs.getString(3); english = rs.getString(4); }}else if(mess.contains("更新")){ pre = con.prepareStatement(updateSQL); pre.setString(1,name); pre.setInt(2,Integer.parseInt(math)); pre.setInt(3,Integer.parseInt(english)); pre.setString(4,id); pre.executeUpdate(); out.print("
更新成功
");}else if(mess.contains("添加")){ pre = con.prepareStatement(insertSQL); pre.setString(1,id); pre.setString(2,name); pre.setInt(3,Integer.parseInt(math)); pre.setInt(4,Integer.parseInt(english)); pre.executeUpdate(); out.print("
添加成功
");} else if(mess.contains("删除")){ pre = con.prepareStatement(deleteSQL);pre.setString(1,id); pre.executeUpdate(); out.print("
删除成功
");}con.close();//连接放回连接池。}catch(SQLException e) {out.print("
学号不能重复"); try{ con.close();//连接放回连接池。 } catch(SQLException exp){}}%> 输入学号查看或删除（信息）：

更新（或添加）<input type="text" name="id" value = size=9 />学号的信息：
姓名:<input type="text" name="name" value =''size=11/>
数学成绩:<input type="text" name="mathScore" value =''size=7 />
英语成绩:<input type="text" name="englishScore" value =''size=7/>
 

② 运行结果

3.小星星广告网

① 实验代码

文件：context2.xml

文件：head.jsp

 #jerry{font-family:隶书;font-size:58;color:blue; } #tom{font-family:楷体;font-size:33;color:blue; }
小星星广告网
  注册 登录 发布广告 浏览广告 主页

文件：index.jsp

#ok{font-family:楷体;font-size:50;color:green }欢迎注册，发布广告。

文件：web.xml

 registerServlethandle.data.HandleRegisterregisterServlet/registerServletloginServlethandle.data.HandleLoginloginServlet/loginServletupFilehandle.data.UpFileupFile/upFile

文件：register.jsp

 #ok{font-family:宋体;font-size:26;color:black;}用户名由字母、数字、下划线构成，*注释的项必须填写。
*用户名称:
*用户密码:
*重复密码:
注册反馈： 

文件：Register.java

package save.data;public class Register{Stringlogname="",backNews="请输入注册信息"; public void setLogname(String logname){ this.logname=logname;}public String getLogname(){ return logname;}public void setBackNews(String backNews){ this.backNews=backNews;}public String getBackNews(){ return backNews;}}

文件：Encrypt.java

package handle.data;public class Encrypt {static String encrypt(String sourceString,String password) {char [] p= password.toCharArray(); int n = p.length; char [] c = sourceString.toCharArray(); int m = c.length;for(int k=0;k<m;k++){int mima=c[k]+p[k%n];c[k]=(char)mima;} return new String(c);}}

文件：HandleRegister.java

package handle.data;import save.data.Register;import java.sql.*;import java.io.*;import javax.servlet.*;import javax.servlet.http.*;import javax.sql.DataSource;import javax.naming.Context;import javax.naming.InitialContext;import javax.naming.NamingException;public class HandleRegister extends HttpServlet { public void init(ServletConfig config) throws ServletException { super.init(config); } publicvoidservice(HttpServletRequest request, HttpServletResponse response)throws ServletException,IOException {request.setCharacterEncoding("utf-8");Connection con =null; PreparedStatement sql=null; Register userBean=new Register();request.setAttribute("userBean",userBean);String logname=request.getParameter("logname").trim();String password=request.getParameter("password").trim();String again_password=request.getParameter("again_password").trim();if(logname==null) logname="";if(password==null) password="";if(!password.equals(again_password)) {userBean.setBackNews("两次密码不同，注册失败，"); RequestDispatcher dispatcher=request.getRequestDispatcher("register.jsp"); dispatcher.forward(request, response); return;}boolean isLD=true;for(int i=0;i0&&password.length()>0&&isLD;String backNews="";try{ Contextcontext = new InitialContext(); ContextcontextNeeded =(Context)context.lookup("java:comp/env"); DataSource ds= (DataSource)contextNeeded.lookup("adverConn"); con= ds.getConnection(); String insertCondition="INSERT INTO user VALUES (?,?)"; sql=con.prepareStatement(insertCondition); if(boo){ sql.setString(1,logname); password =Encrypt.encrypt(password,"javajsp"); sql.setString(2,password); int m=sql.executeUpdate(); if(m!=0){backNews="注册成功";userBean.setBackNews(backNews);userBean.setLogname(logname); } } else { backNews="信息填写不完整或名字中有非法字符"; userBean.setBackNews(backNews); } con.close();}catch(SQLException exp){ backNews="该会员名已被使用，请您更换名字"+exp; userBean.setBackNews(backNews); }catch(NamingException exp){ backNews="没有设置连接池"+exp; userBean.setBackNews(backNews); }finally{try{ con.close();}catch(Exception ee){}} RequestDispatcher dispatcher= request.getRequestDispatcher("register.jsp");dispatcher.forward(request, response); }}

文件：login.jsp

#tom{font-family:宋体;font-size:30;color:black; }登录用户:
输入密码:

登录反馈信息:
登录名称:

文件：Login.java

package save.data;import java.util.*;public class Login {String logname="", backNews="未登录";public void setLogname(String logname){ this.logname = logname;}public String getLogname(){ return logname;}public void setBackNews(String s) { backNews = s;} public String getBackNews(){ return backNews;}}

文件：HandleLogin.java

package handle.data;import save.data.*;import java.sql.*;import java.io.*;import javax.servlet.*;import javax.servlet.http.*;import javax.sql.DataSource;import javax.naming.Context;import javax.naming.InitialContext;import javax.naming.NamingException;public class HandleLogin extends HttpServlet{ public void init(ServletConfig config) throws ServletException{super.init(config); } public void service(HttpServletRequest request, HttpServletResponse response)throws ServletException,IOException{request.setCharacterEncoding("utf-8");Connection con =null; Statement sql; String logname=request.getParameter("logname").trim(),password=request.getParameter("password").trim();password = Encrypt.encrypt(password,"javajsp");boolean boo=(logname.length()>0)&&(password.length()>0);try{Contextcontext = new InitialContext(); ContextcontextNeeded=(Context)context.lookup("java:comp/env"); DataSource ds= (DataSource)contextNeeded.lookup("adverConn");con= ds.getConnection();//使用连接池中的连接。 String condition="select * from user where logname = '"+ logname+"' and password ='"+password+"'"; sql=con.createStatement(); if(boo){ResultSet rs=sql.executeQuery(condition);boolean m=rs.next();if(m==true){ //调用登录成功的方法:success(request,response,logname,password); RequestDispatcher dispatcher=request.getRequestDispatcher("login.jsp");dispatcher.forward(request,response);}else{String backNews="您输入的用户名不存在，或密码不般配";//调用登录失败的方法:fail(request,response,logname,backNews); } } else{String backNews="请输入用户名和密码";fail(request,response,logname,backNews); } con.close();}catch(SQLException exp){String backNews=""+exp;fail(request,response,logname,backNews);}catch(NamingException exp){String backNews="没有设置连接池"+exp;fail(request,response,logname,backNews); }finally{try{con.close();}catch(Exception ee){}}} public void success(HttpServletRequest request, HttpServletResponse response, String logname,String password) {Login loginBean=null;HttpSession session=request.getSession(true);try{loginBean=(Login)session.getAttribute("loginBean");if(loginBean==null){ loginBean=new Login(); session.setAttribute("loginBean",loginBean); loginBean=(Login)session.getAttribute("loginBean");}String name =loginBean.getLogname();if(name.equals(logname)) { loginBean.setBackNews(logname+"已经登录了"); loginBean.setLogname(logname);}else {//数据模型存储新的登录用户:loginBean.setBackNews(logname+"登录成功");loginBean.setLogname(logname);}}catch(Exception ee){loginBean=new Login();session.setAttribute("loginBean",loginBean);loginBean.setBackNews(""+ee);loginBean.setLogname(logname);} } public void fail(HttpServletRequest request,HttpServletResponse response,String logname,String backNews) {response.setContentType("text/html;charset=utf-8");try {PrintWriter out=response.getWriter();out.println("");out.println("
"+logname+"登录反馈结果
"+backNews+"
") ;out.println("返回登录页面或主页
");out.println("登录页面");out.println("
主页");out.println("");}catch(IOException exp){}}}

文件：publishAdver.jsp

 #tom{font-family:宋体;font-size:26;color:black;}选择要上传的文件：

 

文件：UpFile.java

package handle.data;import save.data.Login;import java.io.*;import javax.servlet.*;import javax.servlet.http.*;public class UpFile extends HttpServlet{ public void init(ServletConfig config) throws ServletException{super.init(config); } public void service(HttpServletRequest request, HttpServletResponse response) throws ServletException,IOException{String backMess ="";request.setCharacterEncoding("utf-8");Login loginBean=null;HttpSession session=request.getSession(true);String fileName=null;try{loginBean = (Login)session.getAttribute("loginBean"); if(loginBean==null){ response.sendRedirect("login.jsp"); return; } else { boolean b =loginBean.getLogname()==null|| loginBean.getLogname().length()==0; if(b){response.sendRedirect("login.jsp");return; } }}catch(Exception exp){ response.sendRedirect("login.jsp"); return;}try{String tempFileName=(String)session.getId();String webDir = request.getContextPath();webDir = webDir.substring(1); File f= new File(""); String path = f.getAbsolutePath();int index = path.indexOf("bin");String tomcatDir = path.substring(0,index);File dir=new File(tomcatDir+"/webapps/"+webDir+"/image");dir.mkdir();//建立目录。File fileTemp=new File(dir,tempFileName);RandomAccessFile randomWrite = new RandomAccessFile(fileTemp,"rw");InputStream in=request.getInputStream();byte b[]=new byte[10000];int n;while( (n=in.read(b))!=-1){ randomWrite.write(b,0,n);}randomWrite.close();in.close();RandomAccessFile randomRead=new RandomAccessFile(fileTemp,"r");int second=1;String secondLine=null;while(second<=2) { secondLine=randomRead.readLine();second++;}int position=secondLine.lastIndexOf("=");fileName=secondLine.substring(position+2,secondLine.length()-1);randomRead.seek(0); longforthEndPosition=0;int forth=1;while((n=randomRead.readByte())!=-1&&(forth=0)&&(j<=6)) { mark--;randomRead.seek(mark);n=randomRead.readByte();if(n=='\n'){ endPosition=randomRead.getFilePointer(); j++;}}randomRead.seek(forthEndPosition);long startPoint=randomRead.getFilePointer();while(startPoint<endPosition-1){n=randomRead.readByte();randomWrite.write(n); startPoint=randomRead.getFilePointer();}randomWrite.close();randomRead.close();backMess ="上传成功"; fileTemp.delete();} catch(Exception ee) {backMess ="没有选择文件或上传失败";} response.setContentType("text/html;charset=utf-8"); try {PrintWriter out=response.getWriter();out.println("");out.println("
"+loginBean.getLogname()+":"+backMess+"
") ;out.println("
返回主页");out.println("
主页");out.println(""); } catch(IOException exp){} } }

文件：browseAdver.jsp

 #tom{font-family:宋体;font-size:26;color:black;}<% Contextcontext = new InitialContext();ContextcontextNeeded = (Context)context.lookup("java:comp/env");DataSourceds = (DataSource)contextNeeded.lookup("adverConn");Connection con =null;Statement sql; ResultSet rs;try{ con = ds.getConnection(); sql=con.createStatement();String SQL = "SELECT logname FROM user";//SQL语句。 rs=sql.executeQuery(SQL); while(rs.next()) { String logname = rs.getString(1); out.print("
浏览"+logname+"发布的广告");} con.close() ;}catch(SQLException e) {out.print("
"+e);}finally{try{con.close();}catch(Exception ee){} } %>

文件：showAdver.jsp

 #textStyle{font-family:宋体;font-size:16;color:blue}<jsp:setPropertyname="play" property="logname" value=""/><jsp:setPropertyname="play" property="webDir" value=""/>  
的广告：
<image src = image/ width =300 height =200>
 &logname=">下一张&logname=">上一张

文件：Play.java

package save.data;import java.io.*; import java.util.regex.Pattern;import java.util.regex.Matcher;public class Play {public String logname;String pictureName[]; public String showImage;public String webDir="D:\apache-tomcat-8.5.87\webapps\ch8_1\WEB-INF";//web服务目录的名字。public String tomcatDir;int index = 0;public Play() {File f= new File(""); String path = f.getAbsolutePath();int index = path.indexOf("bin");tomcatDir = path.substring(0,index);}public void setLogname(String s){ showImage=""; logname = s;}public void setWebDir(String s) {webDir = s;File dirImage = new File(tomcatDir+"/webapps/"+webDir+"/image"); pictureName = dirImage.list(new FileStartName(logname));}public String getShowImage() { try { showImage = pictureName[index]; return showImage;} catch(Exception exp){ return "flower.jpg"; }} public void setIndex(int i) { index = i;if(index>=pictureName.length)index = 0; if(index < 0)index = pictureName.length-1;}public int getIndex() {returnindex ; } }class FileStartName implements FilenameFilter {String logname=null;Pattern pattern;Matcher matcher; FileStartName(String logname) { this.logname = logname; pattern = Pattern.compile(logname); }publicboolean accept(File dir,String name) { matcher = pattern.matcher(name);if(matcher.find())return true; elsereturn false;}}

② 运行结果

四、实验总结

本次实验应用了MySQL数据库与jsp页面的链接，前两个小实验比较简单，最后一个实验涉及到的页面较多，比较复杂，完成了一整套的小系统。将jsp页面、数据库和Java综合到了一起。