增删查

标题java日记

day9
建立一个音乐管理系统，主要学习增删改查的内容

创建一个DBUtil

public static Connection getConnection() {

    Connection connection=null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
       connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/nbcj?useSSL=true&characterEncoding=utf-8&user=root&password=123456");

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


    return connection;
}

public static void closeAll(ResultSet resultSet,PreparedStatement preparedStatement,Connection connection){
    if(resultSet !=null){
        try {
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (preparedStatement != null) {

        try {
            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    if(connection!=null){
        try{
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

查

    public List<Music> findall() throws SQLException {
    List<Music> musics = new ArrayList<>();
    Connection connection = DBUtil.getConnection();
    PreparedStatement statement = connection.prepareStatement("SELECT *FROM music");
    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;
}

case 1:
List musics=musicDao.findall();
System.out.println(" ");

删
public void delete(int id) throws SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
connection = DBUtil.getConnection();
int i = 0;
try {
PreparedStatement 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();
}

}

case 2:
System.out.println(“请输入要删除歌曲的id”);
int id=input.nextInt();
musicDao.delete(id);

增
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();

case 3:
System.out.println(“请输入要添加的音乐名称：”);
String n=input.next();
System.out.println(“请输入作者：”);
String au=input.next();
Music music=new Music();
music.setMusicname(n);
music.setAuthor(au);
musicDao.add(music);
break;