音乐管理系统

创建dao，model，util三个包

在util包下创建DBUtil类，实现jdbc的连接和关闭资源

package com.zhongruan.util;

import java.sql.*;

public class DBUtil {
    public Connection getConnection(){
        Connection connection=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://localhost/zjgm?user=root&password=123456");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }
    public void getClose(ResultSet resultSet, Statement statement,Connection connection){
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

在model创建Music和User的实体类来存放音乐和用户的信息
Music

package com.zhongruan.model;

public class Music {
    private int id;
    private String name;
    private String author;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    @Override
    public String toString() {
        return "Music{" +
                "id=" + id +
                ", 歌名='" + name + '\'' +
                ", 作者='" + author + '\'' +
                '}';
    }
}

User

package com.zhongruan.model;

public class User {
    private String username;
    private String password;
    private int type;

    public int getType() {
        return type;
    }

    public void setType(int type) {
        this.type = type;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", type=" + type +
                '}';
    }
}

在dao中创建musicDao和userDao来对数据库的交互
musicDao

package com.zhongruan.dao;

import com.zhongruan.model.Music;
import com.zhongruan.util.DBUtil;
import com.zhongruan.exception.LoginException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class MusicDao {
    public List<Music> findMusic(){
        List<Music> musicList=new ArrayList<>();
        DBUtil dbUtil=new DBUtil();
        ResultSet resultSet =null;
        Connection connection =null;
        PreparedStatement statement =null;
        try{
            connection = dbUtil.getConnection();
            String sql="select * from music";
            statement=connection.prepareStatement(sql);
            resultSet=statement.executeQuery();
            while (resultSet.next()){
                Music music=new Music();
                music.setId(resultSet.getInt(1));
                music.setName(resultSet.getString(2));
                music.setAuthor(resultSet.getString(3));
                musicList.add(music);
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }
        finally {
            dbUtil.getClose(resultSet,statement,connection);
        }
        return musicList;
    }

    public List<Music> inseterMusic(String name,String author){
        List<Music> musicList=new ArrayList<>();
        DBUtil dbUtil=new DBUtil();
        ResultSet resultSet =null;
        Connection connection =null;
        PreparedStatement statement =null;
        try{
            connection = dbUtil.getConnection();
            String sql="insert into music (name,author) value (?,?)";
            statement=connection.prepareStatement(sql);
            statement.setString(1,name);
            statement.setString(2,author);
            statement.executeUpdate();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        finally {
            dbUtil.getClose(resultSet,statement,connection);
        }
        return musicList;
    }

    public List<Music> deleteMusic(int id){
        List<Music> musicList=new ArrayList<>();
        DBUtil dbUtil=new DBUtil();
        ResultSet resultSet =null;
        Connection connection =null;
        PreparedStatement statement =null;
        try{
            connection = dbUtil.getConnection();
            String sql="delete from music where id= ?";
            statement=connection.prepareStatement(sql);
            statement.setInt(1,id);
            statement.executeUpdate();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        finally {
            dbUtil.getClose(resultSet,statement,connection);
        }
        return musicList;
    }

    public List<Music> updateMusic(String name,String author,int id){
        List<Music> musicList=new ArrayList<>();
        DBUtil dbUtil=new DBUtil();
        ResultSet resultSet =null;
        Connection connection =null;
        PreparedStatement statement =null;
        try{
            connection = dbUtil.getConnection();
            String sql="update music set name=?,author=? where id=?";
            statement=connection.prepareStatement(sql);
            statement.setString(1,name);
            statement.setString(2,author);
            statement.setInt(3,id);
            statement.executeUpdate();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        finally {
            dbUtil.getClose(resultSet,statement,connection);
        }
        return musicList;
    }

    public void Login() throws LoginException{

    }
}

userDao

package com.zhongruan.dao;

import com.zhongruan.model.User;
import com.zhongruan.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDao {
    public User findUser(String username) throws SQLException {
        DBUtil dbUtil=new DBUtil();
        User user=null;
        Connection connection = dbUtil.getConnection();
        String sql="select * from tb_user where username=?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,username);
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()){
            user=new User();
            user.setUsername(resultSet.getString(2));
            user.setPassword(resultSet.getString(3));
            user.setType(resultSet.getInt(4));
        }
        dbUtil.getClose(resultSet,statement,connection);
        return user;
    }

    public void charu(String name,String password) throws SQLException {
        DBUtil dbUtil=new DBUtil();
        Connection connection = dbUtil.getConnection();
        String sql="insert into tb_user (username,password) values (?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,name);
        statement.setString(2,password);
        statement.executeUpdate();
        dbUtil.getClose(null,statement,connection);
    }
}

在创建一个View类来实现功能

package com.zhongruan;

import com.zhongruan.dao.MusicDao;
import com.zhongruan.dao.UserDao;
import com.zhongruan.model.Music;
import com.zhongruan.model.User;
import com.zhongruan.exception.LoginException;

import java.sql.SQLException;
import java.util.List;
import java.util.Scanner;

public class View {
    public static void main(String[] args) throws LoginException, SQLException {
        View.Login();
    }

    public static void musicSystem(User user) throws LoginException, SQLException {
        MusicDao musicDao=new MusicDao();
        while (true){
            System.out.println("1查看音乐，2添加音乐，3删除音乐，4修改音乐,5切换账号，6退出登录");
            Scanner a=new Scanner(System.in);
            int num=a.nextInt();
            if (num==1){
                List<Music> music = musicDao.findMusic();
                System.out.println(music);
            }
            else if(num==2){
                if (user.getType()!=0){
                    System.out.println("权限不够");
                }
                else {
                    Scanner input=new Scanner(System.in);
                    System.out.println("请输入添加音乐名：");
                    String name=input.next();
                    System.out.println("请输入作者：");
                    String author=input.next();
                    musicDao.inseterMusic(name,author);
                    List<Music> music = musicDao.findMusic();
                    System.out.println(music);
                }

            }
            else if(num==3) {
                if (user.getType() != 0) {
                    System.out.println("权限不够");
                } else {
                    Scanner input = new Scanner(System.in);
                    System.out.println("请输入你要删除的音乐的id：");
                    int id = input.nextInt();
                    musicDao.deleteMusic(id);
                    List<Music> music = musicDao.findMusic();
                    System.out.println(music);
                }
            }
            else if(num==4) {
                if (user.getType() != 0) {
                    System.out.println("权限不够");
                } else {
                    Scanner input = new Scanner(System.in);
                    System.out.println("请输入要修改音乐的id：");
                    int id = input.nextInt();
                    System.out.println("请输入新的音乐名：");
                    String name = input.next();
                    System.out.println("请输入新的作者名");
                    String author = input.next();
                    musicDao.updateMusic(name, author, id);
                    List<Music> music = musicDao.findMusic();
                    System.out.println(music);
                }
            }
            else if(num==5) {
                View.Login();
            }
            else {
                break;
            }
        }
    }
    public static void Login() throws LoginException, SQLException {
        boolean flag=true;
        while (flag){
            Scanner input=new Scanner(System.in);
            System.out.println("请输入用户名：");
            String u=input.next();
            System.out.println("请输入密码：");
            String p=input.next();
            UserDao userDao=new UserDao();
            User username=userDao.findUser(u);
            if (username==null){
                System.out.println("该用户尚未注册，请注册");
                System.out.println("请输入注册名：");
                String name=input.next();
                System.out.println("请输入密码：");
                String password=input.next();
                userDao.charu(name,password);
            }
            else if(username.getPassword().equals(p)){
                System.out.println("登录成功");
                musicSystem(username);
                flag=false;
            }else {
                throw new LoginException("登录错误");
            }
        }
    }
}