mysql+jsp+servlet 模糊查询+删除+更新+分页+添加

文件介绍：

1. com.lhy.bean Javabean
2. com.lhy.dao
3. com.lhy.servlet 

数据库的封装：这个部分我也是放在MovieDao.Java文件里面的,之后连接的话,直接调用就好啦~

    private PreparedStatement ps;//这个地方,你可以放在全局。之后做sql语句的预加载,就不用再创建咯
	public static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/movies?useSSL=true&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC";
			String username = "root";
			String password = "luo123456789";
			conn = DriverManager.getConnection(url, username, password);
		} 
		catch (ClassNotFoundException e) 
		{
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

模糊查询代码:

public ArrayList<Movie> FindMoviesByLikeName(String  name){
	ArrayList<Movie> list = new ArrayList<Movie>();
	try {
		Connection conn = MovieDao.getConnection();
		String sql = "SELECT * FROM  movie1 where name like ?";
		ps = conn.prepareStatement(sql);
		ps.setString(1, "%"+name+"%");
        ResultSet rs = ps.executeQuery();
       
		while (rs.next()) {
			Movie movie1 = new Movie(rs.getString("name") , rs.getString("director") ,
					rs.getString("tostar") , rs.getInt("score") , rs.getFloat("ticket"), rs.getString("film"));
			list.add(movie1);}
		    rs.close();
		    ps.close();
		    conn.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return list;
}

删除操作代码：

	public void  DeleteMoviesByName(String name) {
    	try {
			Connection conn = MovieDao.getConnection();
			String sql = "delete from movie1 where name=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.executeUpdate();
			ps.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		} }

更新操作代码：

1. 对于这个更新操作,我是通过FindAllMovieServlet发送请求给我建立了一个的update.jsp更新界面,这个更新界面先进行回显
2. 如何在设置一个按钮,修改完成之后,再返回到LookMovies.jsp。所以我们需要在MovieDao中写两个方法

     查询所有：作回显用

public ArrayList<Movie> findAll() {
		ArrayList<Movie> list = new ArrayList<Movie>();
		try {
			Connection conn = getConnection();
    			java.sql.Statement statement = conn.createStatement();
    			String sql = "SELECT * FROM movie1";
    			ResultSet rs = statement.executeQuery(sql);
    			
    			while (rs.next()) {
    			Movie movie = new Movie(rs.getString("name") , rs.getString("director") ,
    					rs.getString("tostar") , rs.getInt("score") , rs.getFloat("ticket"), rs.getString("film"));
    			list.add(movie);
    			}
    			rs.close();
    			statement.close();
    			conn.close();
    			} catch (SQLException e) {
    			e.printStackTrace();
    			}
    	return list;	
	}

    更新操作：

public void UpdateMovies(String name, String director, String tostar, int score, Float ticket, String film){
	try {
		Connection conn = MovieDao.getConnection();
		String sql = "Update movie1 set director=?, tostar=?, score=?, ticket=?, film=? where name=?";
		ps = conn.prepareStatement(sql);
		ps.setString(6,name);
		ps.setString(1,director);
		ps.setString(2,tostar);
		ps.setInt(3, score);
		ps.setFloat(4, ticket);
		ps.setString(5, film);
		ps.executeUpdate();
		ps.close();
		conn.close();
	} catch (Exception e) {
		e.printStackTrace();
	} 
}

  分页操作：

思维流程：

1. 分页操作,我们肯定需要知道一共有多少数据吧,那么就需要建立一个方法去查询所有数据
2. 如何你需要确定你想一页显示多少数据吧,我是用PAGE_SIZE变量储存
3. 如何你有了总数和每一页的,是不是就可以去求一共有多少页？这个地方才是分页的关键之处,算法有很多,那么我是采用的最简单一种。欢迎补充！嘻嘻
4. 有这么多数据,根据什么显示嘞？是不是要对数据的排序做一个规定。

第一步： 

	public int findCount() {
		int count = 0;
		Connection conn = getConnection();
		String sql = "select count(*) from movie1";
		try {
			java.sql.Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				count = rs.getInt(1);
			}
			rs.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

第二步：

public List<Movie> find(int page) {
		List<Movie> list = new ArrayList<Movie>();
		Connection conn = getConnection();
		String sql = "select * from movie1 order by name desc limit ?,?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, (page - 1) * Movie.PAGE_SIZE);
			ps.setInt(2, Movie.PAGE_SIZE);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Movie s = new Movie();
				s.setName(rs.getString("name"));
				s.setDirector(rs.getString("director"));
				s.setTostar(rs.getString("tostar"));
				s.setScore(rs.getInt("score"));
				s.setTicket(rs.getFloat("ticket"));
				s.setFilm(rs.getString("film"));
				list.add(s);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

第三步：

package com.lhy.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 com.lhy.bean.Movie;
import com.lhy.dao.MovieDao;

@WebServlet("/FindMovieBypageServlet")
public class FindMovieBypageServlet extends HttpServlet {
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		int currPage = 1;
		if(request.getParameter("page")!=null) {
			currPage = Integer.parseInt(request.getParameter("page"));
		}
		MovieDao movieDao = new MovieDao();
		List<Movie>list = movieDao.find(currPage);
		request.setAttribute("list", list);
		int pages;
		int count = movieDao.findCount();
		if(count%Movie.PAGE_SIZE == 0) {
			pages = count/Movie.PAGE_SIZE;
		}else {
			pages = count/Movie.PAGE_SIZE+1;
		}
		StringBuffer sb = new StringBuffer();
		for(int i=1;i<=pages;i++) {
			if(i == currPage) {
				sb.append("["+i+"]");
			}else {
				sb.append("<a href='FindMovieBypageServlet?page="+i+"'>"+i+"</a>");
			}
			sb.append("  ");
		}
		request.setAttribute("bar", sb.toString());
		request.getRequestDispatcher("LookMovies.jsp").forward(request, response);
	}
}

添加操作：

public void AddMovies(String name, String director, String tostar, int score, Float ticket, String film) {
		try {
			Connection conn = MovieDao.getConnection();
			// 添加图书信息的SQL语句
			String sql1 = "INSERT INTO movie1 ( name, director, tostar, score, ticket, film )VALUES(?,?,?,?,?,?) ";
			// 获取PreparedStatement
			ps = conn.prepareStatement(sql1);
			ps.setString(1, name);
			ps.setString(2, director);
			ps.setString(3, tostar);
			ps.setInt(4, score);
			ps.setFloat(5, ticket);
			ps.setString(6, film);
			ps.executeUpdate();
			ps.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

部分界面展示：

                                                                  整体架构：

                                                                   主界面:

更新界面：