音乐管理系统5

这次在音乐管理系统中，加入了一个新功能：分页查询。当你点击查询时，一次只显示5条记录。还添加了上一页和下一页，模仿web网页。

首先新建了一个名为PageBean的class类

public class PageBean<T> {
    private int currentPage;
    private  int pageSize;
    private List<T> musics;

    private int totalCount;
    private int totalPage;

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public List<T> getMusics() {
        return musics;
    }

    public void setMusics(List<T> musics) {
        this.musics = musics;
    }

    public int getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(int totalCount) {
        this.totalCount = totalCount;
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }

    @Override
    public String toString() {
        return "PageBean{" +
                "currentPage=" + currentPage +
                ", pageSize=" + pageSize +
                ", musics=" + musics +
                ", totalCount=" + totalCount +
                ", totalPage=" + totalPage +
                '}';
    }
}

其次，在MusicDao中建立了查询页面的语句

 PreparedStatement statement=connection.prepareStatement("select * from music order by id limit ?,?");
        statement.setInt(1,start);
        statement.setInt(2,5);

还建立了一个查询总信息条数的方法

public int getCount() throws SQLException {
        Connection connection=DBUtil.getConnection();
        PreparedStatement statement=connection.prepareStatement("select count(*) from music");
        ResultSet resultSet=statement.executeQuery();
        int count=0;
        while (resultSet.next()){
            count=resultSet.getInt(1);
        }
        DBUtil.closeAll(resultSet,statement,connection);
        return count;
    }

MusicDao的总代码：

public class MusicDao {
    public int getCount() throws SQLException {
        Connection connection=DBUtil.getConnection();
        PreparedStatement statement=connection.prepareStatement("select count(*) from music");
        ResultSet resultSet=statement.executeQuery();
        int count=0;
        while (resultSet.next()){
            count=resultSet.getInt(1);
        }
        DBUtil.closeAll(resultSet,statement,connection);
        return count;
    }

    public List<Music> findAll(int start) throws SQLException {
        List<Music> musics=new ArrayList<>();
        Connection connection= DBUtil.getConnection();
        PreparedStatement statement=connection.prepareStatement("select * from music order by id limit ?,?");
        statement.setInt(1,start);
        statement.setInt(2,5);
        ResultSet resultSet=statement.executeQuery();
        while (resultSet.next()){
            int id=resultSet.getInt(1);
            String musicname=resultSet.getString(2);
            String author=resultSet.getString(3);
            Music music=new Music();
            music.setId(id);
            music.setMusicname(musicname);
            music.setAuthor(author);
            musics.add(music);
        }
        return musics;
    }

    public void delete(int id) throws SQLException {
        PreparedStatement statement=null;
        Connection connection=null;
        connection=DBUtil.getConnection();
        int i=0;
        statement=connection.prepareStatement("delete from music where id=?");
        statement.setInt(1,id);
        i=statement.executeUpdate();
        if (i!=0){
            System.out.println("================删除成功================");
        }else{
            System.out.println("================删除失败================");
        }
    }

    public void add(Music music) throws SQLException {
        Connection connection=DBUtil.getConnection();
        PreparedStatement statement=connection.prepareStatement
                ("insert into music (musicname,author) values (?,?)");
        statement.setString(1,music.getMusicname());
        statement.setString(2,music.getAuthor());
        int i=statement.executeUpdate();
        if (i!=0){
            System.out.println("添加成功！");
        }else{
            System.out.println("添加失败！");
        }
    }

    public void update(Music music) throws SQLException {
        Connection connection=null;
        connection=DBUtil.getConnection();
        PreparedStatement statement=connection.prepareStatement("update music set musicname=? , author=? where id=?");
        statement.setString(1,music.getMusicname());
        statement.setString(2,music.getAuthor());
        statement.setInt(3,music.getId());
        int re=statement.executeUpdate();
        DBUtil.closeAll(null,statement,connection);
        if(re!=-1){
            System.out.println("修改成功！");
        }
        else{
            System.out.println("没有修改成功！");
        }
    }
}

最后在MusicService的getMenu中补充完整的代码：

int count =musicDao.getCount();
                    pageBean.setTotalCount(count);
                    double ceil=Math.ceil(count/5.0);
                    int totalPage=(int) ceil;
                    pageBean.setTotalPage(totalPage);
                    System.out.println("请问你要查询第几页，我们一共有"+totalPage+"页");
                    int i=input.nextInt();
                    List<Music> music1 = getMusic(i, totalPage, musicDao);
                    System.out.println("下一步还要做什么：\t1.上一页 \t2.下一页 \t3.退出");
                    int i1 = input.nextInt();
                    switch (i1){
                        case 1:
                            if(i==1){
                                getMusic(1, totalPage, musicDao);
                            }else{
                                getMusic(i-1, totalPage, musicDao);
                            }

                            break;
                        case 2:
                            if(i==totalPage){
                                getMusic(totalPage,totalPage,musicDao);
                            }else {
                                getMusic(i+1,totalPage,musicDao);
                            }

                            break;
                        case 3:
                            System.exit(0);
                            break;
                    }
                    break;

private List<Music>  getMusic(int currentPage, int totalPage, MusicDao musicDao) throws SQLException {
        List<Music> musics=null;
        if(currentPage>totalPage){
            System.out.println("傻子，输错了");
        }else{
            musics = musicDao.findAll((currentPage-1)*5);
            System.out.println(musics);
        }
        return musics;
    }