java web 进行数据库的增删改查

package cn.edu.mju.project4.persist.impl;

import cn.edu.mju.project4.persist.IBandDao;
import cn.edu.mju.project4.entity.Band;
import cn.edu.mju.project4.util.Pager;
import com.sun.org.apache.bcel.internal.generic.NEW;

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 BandDao implements IBandDao {
    @Override
    public boolean insert(Band band) {
        boolean ret=false;
        try {
            Connection conn=MysqlDbUtil.getConnection();
            String sql="INSERT INTO band(name, remark, status) VALUE (?,?,?)";
            PreparedStatement pst=conn.prepareStatement(sql);
            pst.setString(1,band.getName());
            pst.setString(2,band.getRemark());
            pst.setByte(3,band.getStatus());
            int i= pst.executeUpdate();
            if (i>0)
            {
                ret=true;
            }
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return ret;
    }

    @Override
    public boolean delete(Integer id) {
        boolean ret=false;
        try {
            Connection conn=MysqlDbUtil.getConnection();
            String sql="DELETE FROM band WHERE id=?";
            PreparedStatement pst=conn.prepareStatement(sql);
            pst.setInt( 1,id);
            int i= pst.executeUpdate();
            if (i>0)
            {
                ret=true;
            }
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ret;
    }

    @Override
    public boolean update(Band band) {
           boolean ret=false;
        try {
            Connection conn=MysqlDbUtil.getConnection();
            String sql="UPDATE band SET name=?,remark=?,status=? WHERE id=?";
            PreparedStatement pst=conn.prepareStatement(sql);
            pst.setString(1, band.getName());
            pst.setString(2,band.getRemark());
            pst.setByte(3,band.getStatus());
            pst.setInt(4,band.getId());
            int i= pst.executeUpdate();
            if (i>0)
            {
                ret=true;
            }
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ret;
    }

    @Override
    public Band findById(Integer id) {
        Band band=null;
        try {
            Connection conn=MysqlDbUtil.getConnection();
            String sql="SELECT * FROM band WHERE id=?";
            PreparedStatement pst=conn.prepareStatement(sql);
            pst.setInt(1,id);
            ResultSet rs= pst.executeQuery();
            if (rs.next()){
                band=new Band();
               band.setId(rs.getInt("id"));
                band.setName( rs.getString("name"));
                band.setRemark(rs.getString("remark"));
                band.setStatus(rs.getByte("status"));
            }
            rs.close();
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return band;
    }




    @Override
    public List<Band> find(Band band) {
        List<Band> bands=new ArrayList<>();
        List<Object> params=new ArrayList<>();

        try {
            Connection conn=MysqlDbUtil.getConnection();

            StringBuilder sb= new StringBuilder("SELECT * FROM band  where 1=1 ");
            where(band,sb,params);  //添加查询条件
            PreparedStatement pst=conn.prepareStatement(sb.toString());
            //填值
            for (int i=0 ;i<params.size();i++)
            {
              pst.setObject(i+1,params.get(i));
            }


            ResultSet rs= pst.executeQuery();
            //生成查询到的对象
            while(rs.next()){

                bands.add(toBean( rs));
            }
            rs.close();
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return bands;

    }

    @Override
    public Pager paginate(int page, int pageSize, Band band) {
        Pager pager = new Pager(page, pageSize);
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        where(band,sb, params);
        String sql = "SELECT COUNT(*) AS c FROM band WHERE 1=1 " + sb.toString();
        try {
            Connection conn = MysqlDbUtil.getConnection();
            System.out.println(sql);
            PreparedStatement pstmt = conn.prepareStatement(sql);
            for(int i=0; i< params.size(); i++){
                pstmt.setObject(i+1, params.get(i));
            }
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                pager.setRows(rs.getInt("c"));
            }
            rs.close();
            pstmt.close();
            sql = "SELECT * FROM band WHERE 1=1 " + sb.toString() + " LIMIT ?,?";
            System.out.println(sql);
            pstmt = conn.prepareStatement(sql);
            //填充占位符
            int j = 1;
            for(int i=0; i< params.size(); i++){
                pstmt.setObject(j, params.get(i));
                j = j + 1;
            }
            pstmt.setInt(j, pager.getIndex());
            pstmt.setInt(j+1, pager.getPageSize());
            rs = pstmt.executeQuery();
            List<Object> bands = new ArrayList<>();
            while(rs.next()){
                bands.add(toBean(rs));
            }
            pager.setData(bands);
            rs.close();
            pstmt.close();
            conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return pager;
    }

    private Band toBean(ResultSet rs) throws SQLException {
        Band band = null;
        if(rs != null) {
            band = new Band();
            band.setId(rs.getInt("id"));
            band.setName(rs.getString("name"));
            band.setRemark(rs.getString("remark"));
            band.setStatus(rs.getByte("status"));
        }
        return band;
    }
    private void where(Band band, StringBuilder sb, List<Object> params) {
        if (band != null) {
            if (band.getName() != null && !"".equals(band.getName())) {
                sb.append("AND name LIKE ?");
                params.add("%" + band.getName() + "%");
            }
            if (band.getRemark() != null && !"".equals(band.getRemark())) {
                sb.append("AND remark LIKE ?");
                params.add("%" + band.getRemark() + "%");
            }
            if (band.getStatus() != 0) {
                sb.append("AND status =?");
                params.add(band.getStatus());
            }
        }
    }
}

//进行分页查找工具类
package cn.edu.mju.project4.util;

import java.util.List;

public class Pager {
    private int page=1;
    private  int pageSize=10;
    private  int rows;
    private int index;
    private int pages;
    private int prev;
    private int next;
    private boolean first=false;
    private boolean last=false;
    private List<Object> data;

    public Pager() {
    }

    public Pager(int page, int pageSize) {
        setPage(page);
        setPageSize(pageSize);
    }

    public int getPage() {
        return page;
    }

    public void setPage(int page) {
        if (page<1)
        {
            page=1;
        }
        this.page = page;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        if (pageSize<1)
        {
            pageSize=10;
        }
        this.pageSize = pageSize;
    }

    public int getRows() {
        return rows;
    }

    public void setRows(int rows) {
        if (rows<0)
        {
            rows=0;
        }
        this.rows = rows;
    }

    public int getIndex() {
        getPage();
        index=(page-1)*pageSize;

        return index;
    }



    public int getPages() {
        pages=rows/pageSize;
        if (pages*pageSize<rows)
        {
            pages=pages+1;
        }
        if (pages<1)
        {
            pages=1;
        }
        return pages;
    }



    public int getPrev() {
        prev=page-1;
        if (prev<1)
        {
            prev=1;
        }
        return prev;
    }



    public int getNext() {
        getPages();
        next=page+1;
        if (next>pages)
        {
            next=pages;
        }
        return next;
    }


    public boolean isFirst() {
        if (page==1)
        {
            first=true;
        }
        return first;
    }



    public boolean isLast() {
        getPages();
        if (page==pages)
        {
            last=true;
        }

        return last;
    }



    public List<Object> getData() {
        return data;
    }

    public void setData(List<Object> data) {
        this.data = data;
    }
}