java爬虫测试+增删改查
这个实验是从网站爬取数据，然后添加进数据库，然后实现增删改查
主界面代码：
<%@page import="java.util.*" %> <%@page import="bean.hero" %> <%@page import="javax.servlet.*" %> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <title>总览</title> </head> <link rel="stylesheet" type="text/css" href="resource/layui/css/layui.css"> <style> .curtab{ font-weight: bold; border-right:#b2c9d3 1px solid; background: #99ffff; } </style> <script src="jquery-1.11.0.js"></script> <script type="text/jscript"> //显示tab（tabHeadId：tab头中当前的超链接；tabContentId要显示的层ID） function showTab(tabHeadId,tabContentId) { //tab层 var tabDiv = document.getElementById("tabDiv"); //将tab层中所有的内容层设为不可见 //遍历tab层下的所有子节点 var taContents = tabDiv.childNodes; for(i=0; i<taContents.length; i++) { //将所有内容层都设为不可见 if(taContents[i].id!=null && taContents[i].id != 'tabsHead') { taContents[i].style.display = 'none'; } } //将要显示的层设为可见 document.getElementById(tabContentId).style.display = 'block'; //遍历tab头中所有的超链接 var tabHeads = document.getElementById('tabsHead').getElementsByTagName('a'); for(i=0; i<tabHeads.length; i++) { //将超链接的样式设为未选的tab头样式 tabHeads[i].className='tabs'; } //将当前超链接的样式设为已选tab头样式 document.getElementById(tabHeadId).className='curtab'; document.getElementById(tabHeadId).blur(); } </script> <body> <div id="tabDiv" style="width:100%" > <div id="tabsHead" style="padding:20px,0,20px,0;"> <ul class="layui-nav" lay-filter=""> <li class="layui-nav-item"><a id="tabs1" href="javascript:showTab('tabs1','tabContent1')">浏览英雄</a></li> <li class="layui-nav-item"><a id="tabs2" href="javascript:showTab('tabs2','tabContent2')">查找英雄</a></li> <li class="layui-nav-item"><a id="tabs3" href="javascript:showTab('tabs3','tabContent3')">删除英雄</a></li> </ul> </div> <div id="tabContent1" style="display:block;"> <c:if test="${hero!=null}"> <table border="1"> <tr> <td class="kuang1">英雄名字</td> <td class="kuang2">英雄技能</td> <td class="kuang3" style="text-align:center">英雄技能介绍</td> </tr> <c:forEach var="i" items="${hero}"> <tr> <td>${i.name}</td> <td>${i.skill}</td> <td>${i.desc}</td> </tr> </c:forEach> </table> </c:if> <form class="layui-form" action="./servlet?method=paqu" method="post"> <input type="submit" class="layui-btn layui-btn-radius" value="爬取数据" style="float:right" > </form> <form class="layui-form" action="./servlet?method=liulan" method="post"> <input type="submit" class="layui-btn layui-btn-radius" value="浏览" style="float:right" > </form> </div> <div id="tabContent2" style="display:none"> <form class="layui-form" action="servlet?method=select" method="post" > <input type="text" class="layui-input" name="name" placeholder="请输入英雄名称"> <input type="text" class="layui-input" name="jineng" placeholder="请输入技能名称"> <input type="submit" class="layui-btn layui-btn-radius" value="查询"> </form> <c:if test="${heroselect!=null}"> <table border = "1"> <tr> <td class="kuang1">英雄名字</td> <td class="kuang2">英雄技能</td> <td class="kuang3" style="text-align:center">英雄技能介绍</td> </tr> <c:forEach var="i1" items="${heroselect }"> <tr> <td>${i1.name }</td> <td>${i1.skill }</td> <td>${i1.desc }</td> </tr> </c:forEach> </table> </c:if> </div> <div id="tabContent3" style="display:none"> <form class="layui-form" action="servlet?method=shanchu" method="post"> <input type="text" class="layui-input" name="heroname" placeholder="英雄名称"> <input type="submit" class="layui-btn layui-btn-radius" value="删除"> </form> </div> </div> </body> </html>
类代码：
package bean; public class hero { private String name; private String skill; private String desc; public hero(String name, String skill, String desc) { super(); this.name = name; this.skill = skill; this.desc = desc; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSkill() { return skill; } public void setSkill(String skill) { this.skill = skill; } public String getDesc() { return desc; } public void setDesc(String desc) { this.desc = desc; } }
数据库连接代码：
package DBUtil; import java.sql.*; public class dbutil { private static String url="jdbc:mysql://localhost:3306/db?serverTimezone=UTC"; private static String user="root"; private static String pastword="123456"; private static String jdbcName="com.mysql.cj.jdbc.Driver"; private Connection con=null; public static Connection getConnection() { Connection con=null; try { Class.forName(jdbcName); con=DriverManager.getConnection(url, user, pastword); } catch (Exception e) { e.printStackTrace(); } return con; } public static void close(Connection con) { if(con!=null) try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static void close(Statement state, Connection conn) { if(state!=null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn!=null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close(ResultSet rs, Statement state, Connection conn) { if(rs!=null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(state!=null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn!=null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
服务器代码：
package servlet; import java.io.IOException; import java.util.ArrayList; import java.util.List; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import bean.hero; import dao.heroView; import dao.herodelect; import dao.paQuAdd; import dao.heroSelect; /** * Servlet implementation class servlet */ @WebServlet("/servlet") public class servlet extends HttpServlet { private static final long serialVersionUID = 1L; public servlet() { super(); // TODO Auto-generated constructor stub } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("UTF-8"); String method=request.getParameter("method"); if(method.equals("liulan")) { liulan(request,response); }else if(method.equals("select")) { select(request,response); }else if(method.equals("shanchu")) { shanchu(request,response); }else if(method.equals("paqu")) { paQuAdd.getHero(); String msg = "Success!"; response.getWriter().write("<script language=javascript>alert('" +msg+ "');window.location='wangzheInterface.jsp'</script>"); } } //浏览英雄信息 public void liulan(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("UTF-8"); List hero=heroView.heroview(); request.setAttribute("hero",hero); request.getRequestDispatcher("wangzheInterface.jsp").forward(request,response); } //查询英雄信息 public void select(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("UTF-8"); String name = null; String jineng = null; name = request.getParameter("name"); jineng = request.getParameter("jineng"); ArrayList<hero> heroselect=heroSelect.select(name,jineng); request.setAttribute("heroselect",heroselect); request.getRequestDispatcher("wangzheInterface.jsp").forward(request,response); } //删除英雄信息 public void shanchu(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("UTF-8"); String heroname = request.getParameter("heroname"); int i = herodelect.heroDelect(heroname); if (i > 0) { String msg = "Delect Success!"; response.getWriter().write("<script language=javascript>alert('" +msg+ "');window.location='wangzheInterface.jsp'</script>"); }else { String msg = "Delect Fail!"; response.getWriter().write("<script language=javascript>alert('" +msg+ "');window.location='wangzheInterface.jsp'</script>"); } } protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub doGet(request, response); } }
爬取+增代码：
package dao; import java.io.*; import java.net.MalformedURLException; import java.net.URL; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import org.jsoup.Jsoup; import org.jsoup.nodes.Document; import org.jsoup.nodes.Element; import org.jsoup.select.Elements; import DBUtil.dbutil; public class paQuAdd { //爬取王者荣耀数据 public static void getHero(){ String url="https://pvp.qq.com/web201605/herolist.shtml"; String heroName = "英雄"; String skill = "技能"; String desc = "详情"; Connection connection=dbutil.getConnection(); String sql="insert into wangzhe(name,skill,desc1) values(?,?,?)"; PreparedStatement preparedStatement = null; //加载对应网址上的Html代码 Jsoup.connect()获取连接 try { Document document= Jsoup.connect(url) .userAgent("Mozilla") .maxBodySize(0) .timeout(500000) .get(); // 获取显示图片的ul标签 Elements selectUL=document.select("div").select("[class=herolist clearfix]"); Elements selectLi=selectUL.select("li"); for (Element element : selectLi) { //详情页地址 String heroURL = element.select("a").attr("href"); // System.out.println(heroURL); //获取英雄名称(标签中的文本内容) heroName = element.select("a").text(); // System.out.println(heroName); String detailUrl="https://pvp.qq.com/web201605/"+heroURL;//详情页 // 获取详情页的html Document document2= Jsoup.connect(detailUrl).get(); skill = document2.select("p").select("[class=skill-name]").select("b").first().text(); desc = document2.select("p").select("[class=skill-desc]").first().text(); preparedStatement = null; preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, heroName); preparedStatement.setString(2, skill); preparedStatement.setString(3, desc); preparedStatement.execute(); } } catch (IOException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { //关闭资源 dbutil.close(preparedStatement,connection); } } }
删除代码：
package dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import DBUtil.dbutil; public class herodelect { public static int heroDelect(String name) { int i = 0; Connection connection = dbutil.getConnection(); PreparedStatement preparedStatement = null; String sql = "delete from wangzhe where name='"+name+"'"; System.out.println(sql); try { preparedStatement = connection.prepareStatement(sql); i = preparedStatement.executeUpdate(sql); System.out.println(i); }catch(SQLException e) { e.printStackTrace(); }finally { //关闭资源 dbutil.close(preparedStatement,connection); } return i; } }
查找代码：
package dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import DBUtil.dbutil; import bean.hero; public class heroSelect { public static ArrayList<hero> select(String name,String skill) { ArrayList<hero> list = new ArrayList<hero>(); Connection connection=dbutil.getConnection(); PreparedStatement preparedStatement = null; ResultSet rs=null; String sql1="select * from wangzhe where name like '%"+name+"%' "; String sql2="select * from wangzhe where skill like '%"+skill+"%'"; String sql3="select * from wangzhe where skill like '%"+skill+"%' or name like '%"+name+"%'"; try { if(!name.equals("") && skill.equals("")) { preparedStatement = connection.prepareStatement(sql1); rs=preparedStatement.executeQuery(); while (rs.next()) { String name1=rs.getString("name"); String skill1=rs.getString("skill"); String desc1=rs.getString("desc1"); hero hero=new hero(name1,skill1,desc1); list.add(hero); } }else if(!skill.equals("") && name.equals("")) { preparedStatement = connection.prepareStatement(sql2); rs=preparedStatement.executeQuery(); while (rs.next()) { String name2=rs.getString("name"); String skill2=rs.getString("skill"); String desc2=rs.getString("desc1"); hero hero=new hero(name2,skill2,desc2); list.add(hero); } }else if(!skill.equals("") &&!name.equals("")) { preparedStatement = connection.prepareStatement(sql3); rs=preparedStatement.executeQuery(); while (rs.next()) { String name3=rs.getString("name"); String skill3=rs.getString("skill"); String desc3=rs.getString("desc1"); hero hero=new hero(name3,skill3,desc3); list.add(hero); } } }catch(SQLException e) { e.printStackTrace(); }finally { dbutil.close(rs, preparedStatement, connection); } return list; } }
查找全部代码：
package dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import DBUtil.dbutil; import bean.hero; public class heroView { public static List heroview() { List list = new ArrayList(); Connection connection=dbutil.getConnection(); PreparedStatement preparedStatement = null; ResultSet rs=null; String sql="select * from wangzhe "; try { int i = 0; preparedStatement = connection.prepareStatement(sql); rs=preparedStatement.executeQuery(); while (rs.next()) { String name=rs.getString("name"); String skill=rs.getString("skill"); String desc=rs.getString("desc1"); hero hero=new hero(name,skill,desc); list.add(hero); } }catch(SQLException e) { e.printStackTrace(); }finally { dbutil.close(rs, preparedStatement, connection); } return list; } }
运行截图：
