冲刺作业：个人作业---爬取
dao
package dao;
import dbutil.DBUtil;
import work.Content;
import work.paqu;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class Dao {
private paqu pa=new paqu();
public List<Content> addpaqu() throws Exception {
List<Content> contents = pa.parseJD();
Connection conn = DBUtil.getConn();
PreparedStatement pstmt = null;
int a=0;
int i=0;
boolean f = false;
try {
while(i!=250){
String sql = "insert into paqu(id,title,author,booktitle,month,year) value(?,?,?,?,?,?)";
pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
pstmt.setInt(1, contents.get(i).getId());
pstmt.setString(2, String.valueOf(contents.get(i).getTitle()));
pstmt.setString(3, String.valueOf(contents.get(i).getAuthor()));
pstmt.setString(4, String.valueOf(contents.get(i).getBooktitle()));
pstmt.setString(5, String.valueOf(contents.get(i).getMonth()));
pstmt.setString(6, String.valueOf(contents.get(i).getYear()));
a=pstmt.executeUpdate();
i++;
}
}
catch(SQLException e) {
e.printStackTrace();
}
finally {
DBUtil.close(pstmt, conn);
}
return contents;
}
public static boolean delete(String id) {
Connection con=DBUtil.getConn();
PreparedStatement pstmt=null;
boolean f=false;
int a=0;
try {
String sql="delete from paqu where id=?";
pstmt=con.prepareStatement(sql);
pstmt.setString(1,id);
a=pstmt.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(pstmt, con);
}
if(a>0) {
f=true;
}
return f;
}
public List<Content> liulanall() {
List<Content> contents=new ArrayList<>();
Connection con=DBUtil.getConn();
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
String sql="select * from paqu ";
pstmt=con.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()) {
int id=rs.getInt("id");
String title=rs.getString("title");
String author=rs.getString("author");
String booktitle=rs.getString("booktitle");
String month=rs.getString("month");
String year=rs.getString("year");
Content conts=new Content(id,title,author,booktitle,month,year);
contents.add(conts);
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(rs,pstmt, con);
}
return contents;
}
public List<Content> searchTitle(String title){
List<Content> contents = new ArrayList<Content>();
String sql="select * from paqu where title like ?";
ResultSet rs = null;
PreparedStatement pstmt=null;
try{
Connection conn = DBUtil.getConn();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,"%"+title+"%");
rs=pstmt.executeQuery();
while(rs.next()){
Content cont=new Content(rs.getInt(1),
rs.getString(2),rs.getString(3),
rs.getString(4),rs.getString(5),rs.getString(6));
contents.add(cont);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return contents;
}
public List<Content> searchAuthor(String name) {
List<Content> contents = new ArrayList<Content>();
String sql="select * from paqu where author like ?";
ResultSet rs = null;
PreparedStatement pstmt=null;
try{
Connection conn = DBUtil.getConn();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,"%"+name+"%");
rs=pstmt.executeQuery();
while(rs.next()){
Content cont=new Content(rs.getInt(1),
rs.getString(2),rs.getString(3),
rs.getString(4),rs.getString(5),rs.getString(6));
contents.add(cont);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return contents;
}
public boolean update(Content c,String old_title){//修改信息
boolean f=false;
int a=0;
String sql = "update paqu set id='"+c.getId()+"',title='"+c.getTitle()+"',author='"+c.getAuthor()+"',booktitle='"+c.getBooktitle()+"',month='"+c.getMonth()+"',year='"+c.getYear()+"'where title='"+old_title+"'";
try {
Connection conn=DBUtil.getConn();
PreparedStatement pstmt=conn.prepareStatement(sql);
a=pstmt.executeUpdate();
System.out.println(a);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(a>0) f=true;
return f;
}
public Content selectinfor(String title) {//查询信息
String sql="select * from paqu where title=?";
Content c=new Content();
try {
ResultSet rs=null;
Connection conn=DBUtil.getConn();
PreparedStatement pstmt=conn.prepareStatement(sql);
pstmt.setString(1,title);
rs=pstmt.executeQuery();
while(rs.next()) {
c=new Content(rs.getInt(1),
rs.getString(2),rs.getString(3),
rs.getString(4),rs.getString(5),rs.getString(6));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return c ;
}
public static boolean add(Content contents) {
Connection con=DBUtil.getConn();
PreparedStatement pstmt=null;
boolean f=false;
int a=0;
try {
String sql="insert into paqu(id,title,author,booktitle,month,year) value(?,?,?,?,?,?)";
pstmt=con.prepareStatement(sql);
pstmt.setInt(1,contents.getId());
pstmt.setString(2,contents.getTitle());
pstmt.setString(3,contents.getAuthor());
pstmt.setString(4,contents.getBooktitle());
pstmt.setString(5,contents.getMonth());
pstmt.setString(6,contents.getYear());
a=pstmt.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(pstmt, con);
}
if(a>0) {
f=true;
}
return f;
}
}
dbutil
package dbutil;
import java.sql.*;
/**
* 数据库连接工具
* @author Hu
*
*/
public class DBUtil {
public static String url = "jdbc:mysql://localhost:3306/course?useUnicode=true&characterEncoding=GB18030&useSSL=false&serverTimezone=GMT&allowPublicKeyRetrieval=true";
public static String user = "root";
public static String password = "123456";
public static Connection getConn () {
Connection conn = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");//加载驱动
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void close (PreparedStatement preparedState, Connection conn) {
if (preparedState != null) {
try {
preparedState.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close (ResultSet rs, PreparedStatement preparedState, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (preparedState != null) {
try {
preparedState.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 关闭连接
* @param state
* @param conn
*/
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();
}
}
}
public static void main(String[] args) throws SQLException {
Connection conn = getConn();
PreparedStatement preparedStatement = null;
ResultSet rs = null;
String sql ="select * from xuanke";
preparedStatement = conn.prepareStatement(sql);
rs = preparedStatement.executeQuery();
if(rs.next()){
System.out.println("数据库为空");
}
else{
System.out.println("数据库不为空");
}
}
}
servlet
DeleteServlet
package servlet;
import dao.Dao;
import lombok.SneakyThrows;
import work.Content;
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 java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.List;
@WebServlet("/DeleteServlet")
public class DeleteServlet extends HttpServlet {
@SneakyThrows
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, UnsupportedEncodingException {
request.setCharacterEncoding("utf-8");
Dao dao=new Dao();
String method = request.getParameter("method");
if(Dao.delete(method)) {
response.sendRedirect("LiulanallServlet?method=liulanall");
}else{
response.getWriter().println("删除失败");
}
}
@SneakyThrows
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
}
LiulanallServlet
package servlet;
import java.io.IOException;
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 dao.Dao;
import work.Content;
@WebServlet("/LiulanallServlet")
public class LiulanallServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Dao dao=new Dao();
request.setCharacterEncoding("utf-8");
String method = request.getParameter("method");
if("liulanall".equals(method)) {
List<Content> contents=dao.liulanall();
request.setAttribute("contents",contents);
request.getRequestDispatcher("index.jsp").forward(request, response);
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
SelectServlet
package servlet;
import dao.Dao;
import lombok.SneakyThrows;
import work.Content;
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 java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.List;
@WebServlet("/SelectServlet")
public class SelectServlet extends HttpServlet {
@SneakyThrows
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, UnsupportedEncodingException {
request.setCharacterEncoding("utf-8");
Dao dao=new Dao();
String method = request.getParameter("method");
String keyword= request.getParameter("keyword");
String name = request.getParameter("name");
System.out.println(name);
if("search".equals(method)) {
if(keyword!=null&&keyword!="") {
System.out.println(keyword);
List<Content> contents = dao.searchTitle(keyword);
request.setAttribute("contents", contents);
request.getRequestDispatcher("index.jsp").forward(request, response);
}else if(name!=null&&name!=""){
// System.out.println(name);
List<Content> contents = dao.searchAuthor(name);
request.setAttribute("contents", contents);
request.getRequestDispatcher("index.jsp").forward(request, response);
}
}
}
@SneakyThrows
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
}
Servlet
package servlet;
import dao.Dao;
import lombok.SneakyThrows;
import work.Content;
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 java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.List;
@WebServlet("/Servlet")
public class Servlet extends HttpServlet {
@SneakyThrows
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, UnsupportedEncodingException {
request.setCharacterEncoding("utf-8");
Dao dao=new Dao();
String method = request.getParameter("method");
if("index".equals(method)) {
List<Content> contents=dao.addpaqu();
request.setAttribute("contents",contents);
request.getRequestDispatcher("index.jsp").forward(request, response);
}else if("add".equals(method)){
String id=request.getParameter("id");
String title=request.getParameter("title");
String author=request.getParameter("author");
String booktitle=request.getParameter("booktitle");
String month=request.getParameter("month");
String year=request.getParameter("year");
Content contents=new Content(Integer.parseInt(id),title,author,booktitle,month,year);
if(Dao.add(contents)) {
System.out.println("添加成功");
response.sendRedirect("LiulanallServlet?method=liulanall");
}
}
}
@SneakyThrows
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
}
UpdateServlet
package servlet;
import dao.Dao;
import lombok.SneakyThrows;
import work.Content;
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 java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.List;
@WebServlet("/UpdateServlet")
public class UpdateServlet extends HttpServlet {
@SneakyThrows
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, UnsupportedEncodingException {
request.setCharacterEncoding("utf-8");
Dao dao=new Dao();
String method = request.getParameter("method");
if("update".equals(method))
{
String id=request.getParameter("id");
String title=request.getParameter("title");
String author=request.getParameter("author");
String booktitle=request.getParameter("booktitle");
String month = request.getParameter("month");
String year = request.getParameter("year");
String old_title=request.getParameter("old_title");
Content c=new Content(Integer.parseInt(id),title,author,booktitle,month,year);
dao.update(c,old_title);
request.setAttribute("message", "添加成功");
response.sendRedirect("LiulanallServlet?method=liulanall");
}else{
String title=method;
Content c=new Content();
c.setTitle(title);
c= dao.selectinfor(title);
request.setAttribute("c", c);
request.getRequestDispatcher("update.jsp").forward(request, response);
}
}
@SneakyThrows
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
}
work
Content
package work;
public class Content {
private int id;
private String title;
private String author;
private String booktitle;
private String month;
private String year;
public Content(){}
public Content(int id, String title, String author, String booktitle, String month, String year) {
this.id = id;
this.title = title;
this.author = author;
this.booktitle = booktitle;
this.month = month;
this.year = year;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public String getBooktitle() {
return booktitle;
}
public void setBooktitle(String booktitle) {
this.booktitle = booktitle;
}
public String getMonth() {
return month;
}
public void setMonth(String month) {
this.month = month;
}
public String getYear() {
return year;
}
public void setYear(String year) {
this.year = year;
}
}paqu
package work;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import java.io.IOException;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;
public class paqu {
public List<Content> parseJD() throws Exception {
String url = "https://openaccess.thecvf.com/CVPR2020?day=2020-06-18";
Document document = Jsoup.parse(new URL(url), 30000);
Elements elements = document.getElementsByClass("bibref");
List<Content> goodList = new ArrayList<>();
int x=1,id=0;
for (Element el : elements) {
//String img = el.getElementsByTag("img").eq(0).attr("data-lazy-img");
// String author = el.getElementsByClass("bibref").eq(0).text();
String text = el.text();
System.out.println(text);
String[] arr = text.split("=");
String author="",title="",booktitle="",month="",year="";
id=x;
for(int i=0;i<arr.length;i++){
if(arr[i].charAt(0)=='@') {
continue;
}
else {
switch (i) {
case 1: author = arr[i].substring(2, arr[i].indexOf("}"));break;
case 2: title = arr[i].substring(2, arr[i].indexOf("}"));break;
case 3: booktitle = arr[i].substring(2, arr[i].indexOf("}"));break;
case 4: month = arr[i].substring(2, arr[i].indexOf("}"));break;
case 5: year = arr[i].substring(2, arr[i].indexOf("}"));break;
}
}
}
Content content = new Content();
content.setId(id);
content.setAuthor(author);
content.setTitle(title);
content.setBooktitle(booktitle);
content.setMonth(month);
content.setYear(year);
x++;
//content.setAuthor(author);
//content.setImg(img);
goodList.add(content);
}
return goodList;
}
}
webapp前端
add.jsp
<%--
Created by IntelliJ IDEA.
User: DELL
Date: 2022/5/15
Time: 9:00
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<form action="Servlet?method=add" method="post">
<table>
<tr>
<td>
请准备一个ID号：<input type="number" name="id" id="id">
</td>
</tr>
<tr>
<td>
请输入标题：<input type="text" name="title" id="title">
</td>
</tr>
<tr>
<td>
请输入作者姓名：<input type="text" name="author" id="author">
</td>
</tr>
<tr>
<td>
请输入书签：<input type="text" name="booktitle" id="booktitle">
</td>
</tr>
<tr>
<td>
请输入月份：<input type="text" name="month" id="month">
</td>
</tr>
<tr>
<td>
请输入年份：<input type="text" name="year" id="year">
</td>
</tr>
<tr>
<td>
<input type="submit" name="submit" value="提交">
</td>
</tr>
</table>
</form>
</body>
</html>
index.jsp
<%--
Created by IntelliJ IDEA.
User: DELL
Date: 2022/5/15
Time: 9:00
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<form action="Servlet?method=add" method="post">
<table>
<tr>
<td>
请准备一个ID号：<input type="number" name="id" id="id">
</td>
</tr>
<tr>
<td>
请输入标题：<input type="text" name="title" id="title">
</td>
</tr>
<tr>
<td>
请输入作者姓名：<input type="text" name="author" id="author">
</td>
</tr>
<tr>
<td>
请输入书签：<input type="text" name="booktitle" id="booktitle">
</td>
</tr>
<tr>
<td>
请输入月份：<input type="text" name="month" id="month">
</td>
</tr>
<tr>
<td>
请输入年份：<input type="text" name="year" id="year">
</td>
</tr>
<tr>
<td>
<input type="submit" name="submit" value="提交">
</td>
</tr>
</table>
</form>
</body>
</html>
update.jsp
<%@ page import="work.Content" %>
<%@ page import="java.util.List" %><%--
Created by IntelliJ IDEA.
User: DELL
Date: 2022/5/15
Time: 3:39
To change this template use File | Settings | File Templates.
--%>
<%@ 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</title>
</head>
<body>
<%
Content c=(Content) request.getAttribute("c");
%>
<form action="UpdateServlet?method=update" method="post">
<input type="hidden" name="old_title" value="<%=c.getTitle()%>"/>
序号:<input type="text" name="id" id="id" value="<%=c.getId()%>"><br>
标题:<input type="text" name="title" id="title" value="<%=c.getTitle()%>"><br>
作者：<input type="text" name="author" id="author" value="<%=c.getAuthor()%>"><br>
书签:<input type="text" name="booktitle" id="booktitle" value="<%=c.getBooktitle()%>"><br>
月份:<input type="text" name="month" id="month" value="<%=c.getMonth()%>"><br>
年:<input type="text" name="year" id="year" value="<%=c.getYear()%>"><br>
<input type="submit" value="修改">
</form>
</body>
</html>
浙公网安备 33010602011771号