DAY 14

package com.NBUFE.dao;
import com.NBUFE.bean.Music;
import com.NBUFE.util.DBUtil;

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 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.close(connection,statement, resultSet);
    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;
    try {
        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("失败了");
        }

    } catch (SQLException e) {
        e.printStackTrace();
    }

}

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());
    statement.executeUpdate();
}

}