WEB project - Music Player

requirement analysis

1. Registration and login
Use the post request, the login path is / login, and the transmitted data is username and password. Here, json is used to organize the data. The response is returned through a set of key value pairs (msg:true). True indicates successful login and false indicates failed login
The registration path is/
2. Upload music
Use the post request, the path is / upload, the transmitted data is filename (file name) and file (. mp3), and upload the file to the server directory at the same time
3. Delete music
Use the post request, the path is / delete, the transmission data is the music id, and then obtain the user object through the Session to obtain the userid
4. Delete music in batch
Use the post request, the path is / deletemore, the transmission data is the music id array, and then obtain the user object through the Session to obtain the userid
5. Query music
Use the post request, the path is / findusic, and the transmission data is query data to perform fuzzy database query
6. Add music to your favorite list
Use the post request, the path is / lovemusic, the transmission data is music id and user id, and add information to the database
7. Find music in your favorite list
Use the post request, the path is / findmove, and the transfer data is the user id
8. Remove music from your favorite list
Use the post request, the path is / remove, the transmission data is user id and music id, and remove the corresponding data from the database

Database design

1. Music table
id music id
title music name
Singer singer name
Time upload time
url music path
userid upload user id
2. User table
id user id
Username username
Password password
Age age
gender
email mailbox
3. Favorite list
id unique index id (it's meaningless to distinguish between similar data)
userid user id
musicid music id

Entity class

Music:

public class Music {
    private int id;
    private String title;
    private String single;
    private String time;
    private String url;
    private int userid;

    public int getId() {
        return id;
    }

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

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getSingle() {
        return single;
    }

    public void setSingle(String single) {
        this.single = single;
    }

    public String getTime() {
        return time;
    }

    public void setTime(String time) {
        this.time = time;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public int getUserid() {
        return userid;
    }

    public void setUserid(int userid) {
        this.userid = userid;
    }

    @Override
    public String toString() {
        return "Music{" +
                "id=" + id +
                ", title='" + title + '\'' +
                ", single='" + single + '\'' +
                ", time='" + time + '\'' +
                ", url='" + url + '\'' +
                ", userid=" + userid +
                '}';
    }
}

User:

public class User {
    private int id;
    private String username;
    private String password;
    private int age;
    private String gender;
    private String email;

    public int getId() {
        return id;
    }

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

    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;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

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

Dao layer

UserDao:

public class UserDao {
    public User login(User loginUser) {
        User user = null;
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet ret = null;

        try {
            connection = DBUtils.getConn();
            String sql = "select * from user where username = ? and password = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,loginUser.getUsername());
            statement.setString(2,loginUser.getPassword());
            ret = statement.executeQuery();
            if (ret.next()){
                user = new User();
                user.setId(ret.getInt("id"));
                user.setUsername(ret.getString("username"));
                user.setPassword(ret.getString("password"));
                user.setEmail(ret.getString("email"));
                user.setGender(ret.getString("gender"));
                user.setAge(ret.getInt("age"));

            }else {
                System.out.println("Login failed");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.getClose(connection,statement,ret);
        }
        return user;
    }

    public void insertUser(User user){
        Connection con = null;
        PreparedStatement pre = null;

        try {
            con = DBUtils.getConn();
            String sql = "insert into user values (null,?,?,?,?,?)";
            pre = con.prepareStatement(sql);
            pre.setString(1,user.getUsername());
            pre.setString(2,user.getPassword());
            pre.setString(3,user.getEmail());
            pre.setString(4,user.getGender());
            pre.setInt(5,user.getAge());
            int ret = pre.executeUpdate();
            if (ret == 1){
                System.out.println("login was successful");
            }else {
                System.out.println("login has failed");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.getClose(con,pre,null);
        }
    }
}

MusicDao layer:

public class MusicDao {
    public int insert(Music music){
        Connection con = null;
        PreparedStatement pre = null;
        try {
            con = DBUtils.getConn();
            String sql = "insert into music values(null,?,?,now(),?,?)";
            pre = con.prepareStatement(sql);
            pre.setString(1,music.getTitle());
            pre.setString(2,music.getSinger());
            pre.setString(3,music.getUrl());
            pre.setInt(4,music.getUserid());
            int ret = pre.executeUpdate();
            if (ret == 1){
                return 1;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.getClose(con,pre,null);
        }
        return 0;
    }

    public List<Music> findMusic(){
        List<Music> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet ret = null;
        try {
            connection = DBUtils.getConn();
            String sql = "select * from music";
            statement = connection.prepareStatement(sql);
            ret = statement.executeQuery();
            while(ret.next()){
                Music music = new Music();
                music.setId(ret.getInt("id"));
                music.setTitle(ret.getString("title"));
                music.setSinger(ret.getString("singer"));
                music.setUrl(ret.getString("url"));
                music.setTime(ret.getString("time"));
                music.setUserid(ret.getInt("userid"));
                list.add(music);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.getClose(connection,statement,ret);
        }
        return list;
    }

    public Music findMusicById(int id){
        Music music = null;
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet ret = null;
        try {
            connection = DBUtils.getConn();
            String sql = "select * from music where id = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1,id);
            ret = statement.executeQuery();
            if (ret.next()){
                music = new Music();
                music.setId(ret.getInt("id"));
                music.setTitle(ret.getString("title"));
                music.setSinger(ret.getString("singer"));
                music.setUrl(ret.getString("url"));
                music.setTime(ret.getString("time"));
                music.setUserid(ret.getInt("userid"));
            }
        }catch(SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.getClose(connection,statement,ret);
        }
        return music;
    }

    public List<Music> ifMusic(String str){
        List<Music> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet ret = null;
        try {
            connection = DBUtils.getConn();
            String sql = "select * from music where title like ? ";
            statement = connection.prepareStatement(sql);
            statement.setString(1,"%" + str + "%");
            ret = statement.executeQuery();
            while (ret.next()){
                Music music = new Music();
                music.setId(ret.getInt("id"));
                music.setTitle(ret.getString("title"));
                music.setSinger(ret.getString("singer"));
                music.setUrl(ret.getString("url"));
                music.setTime(ret.getString("time"));
                music.setUserid(ret.getInt("userid"));
                list.add(music);
            }
        }catch(SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.getClose(connection,statement,ret);
        }
        return list;
    }

    public int deleteMusicById(int id) {
        Connection con = null;
        PreparedStatement pre = null;
        try {
            con = DBUtils.getConn();
            String sql = "delete from mylove where music_id = ?";
            pre = con.prepareStatement(sql);
            pre.setInt(1,id);
            int ret = pre.executeUpdate();
            sql = "delete from music where id = ?";
            pre = con.prepareStatement(sql);
            pre.setInt(1,id);
            ret = pre.executeUpdate();
            if (ret == 1){
                System.out.println("Delete succeeded");
                return ret;
            }else{
                System.out.println("Deletion failed");

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.getClose(con,pre,null);
        }
        return 0;
    }
}

LoveMusicDao:

public class LoveMusicDao {
    public boolean insertLoveMusic(int userId,int musicId){
        Connection con = null;
        PreparedStatement pre= null;
        try {
            con = DBUtils.getConn();
            String sql = "insert into mylove values(null,?,?)";
            pre = con.prepareStatement(sql);
            pre.setInt(1,userId);
            pre.setInt(2,musicId);
            int ret = pre.executeUpdate();
            if (ret == 1){
                return true;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.getClose(con,pre,null);
        }
        return false;
    }

    public List<Music> findLoveMusic(int userId){
        Connection con = null;
        PreparedStatement pre = null;
        List<Music> musics = new ArrayList<>();
        ResultSet ret = null;
        try {
            con = DBUtils.getConn();
            String sql = "select music.* from music , mylove  where mylove.music_id = music.id and mylove.user_id = ?";
            pre = con.prepareStatement(sql);
            pre.setInt(1,userId);
            ret = pre.executeQuery();
            while (ret.next()){
                Music music = new Music();
                music.setId(ret.getInt("id"));
                music.setTitle(ret.getString("title"));
                music.setSinger(ret.getString("singer"));
                music.setUrl(ret.getString("url"));
                music.setUserid(ret.getInt("userid"));
                music.setTime(ret.getString("time"));
                musics.add(music);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.getClose(con,pre,ret);
        }
        return musics;
    }
    public boolean findLoveMusicByUserIdAndMusicId(int userId,int musicId){
        Connection con = null ;
        PreparedStatement pre = null;
        ResultSet ret = null;
        try {
            con = DBUtils.getConn();
            String sql = "select * from mylove where user_id = ? and music_id = ?";
            pre = con.prepareStatement(sql);
            pre.setInt(1,userId);
            pre.setInt(2,musicId);
            ret = pre.executeQuery();
            if (ret.next()){
                return true;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.getClose(con,pre,ret);
        }
        return false;
    }


    // I like music that looks up the str keyword in the list
    public List<Music> findLoveMusicBykeyAndUserId(String str, int user_id){
        List<Music> musics = null;
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet ret = null;
        try {
            con = DBUtils.getConn();
            String sql = "select music.* from music,mulove where music.id = mulove.music_id " +
                    "and mulove.user_id = ? and music.title like ? ";
            pre = con.prepareStatement(sql);
            pre.setInt(1,user_id);
            pre.setString(2,"%"+str+"%");
            ret = pre.executeQuery();
            while (ret.next()){
                Music music = new Music();
                music.setId(ret.getInt("id"));
                music.setTitle(ret.getString("title"));
                music.setSinger(ret.getString("singer"));
                music.setUrl(ret.getString("url"));
                music.setTime(ret.getString("time"));
                music.setUserid(ret.getInt("userid"));
                musics.add(music);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.getClose(con,pre,ret);
        }

        return musics;
    }
    public int removeLoveMusic(int userId,int musicId){
        Connection con = null;
        PreparedStatement pre = null;
        try {
            con = DBUtils.getConn();
            String sql = "delete from mylove where user_id = ? and music_id = ?";
            pre = con.prepareStatement(sql);
            pre.setInt(1,userId);
            pre.setInt(2,musicId);
            int ret = pre.executeUpdate();
            if (ret==1){
                return 1;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.getClose(con,pre,null);
        }
        return 0;
    }
}

Servlet layer

LoginServlet:

@WebServlet("/login")
public class LoginServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        resp.setContentType("application/json;charset=utf-8");
        Map<String,Object> retmap = new HashMap<>();
        String username = req.getParameter("username");
        String password = req.getParameter("password");
        User loginUser = new User();
        loginUser.setUsername(username);
        loginUser.setPassword(password);
        UserDao userDao = new UserDao();
        User user = userDao.login(loginUser);
        if (user == null){
            retmap.put("msg",false);
        }else {
            req.getSession().setAttribute("user",user);
            retmap.put("msg",true);
        }
        ObjectMapper mapper = new ObjectMapper();
        mapper.writeValue(resp.getWriter(),retmap);
    }
}

RegisterServlet:

@WebServlet("/register")
public class RegisterServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        resp.setContentType("application/json;charset=utf-8");
        String username = req.getParameter("username");
        String password = req.getParameter("password");
        String email = req.getParameter("email");
        String gender = req.getParameter("gender");
        int age = Integer.parseInt(req.getParameter("age"));
        Map<String,Object> retmap = new HashMap<>();
        UserDao userDao = new UserDao();
        User user = userDao.findusername(username);
        if (user == null){
            user = new User();
            user.setUsername(username);
            user.setPassword(password);
            user.setEmail(email);
            user.setGender(gender);
            user.setAge(age);
            boolean ret = userDao.insertUser(user);
            if (ret){
                System.out.println("login was successful");
                retmap.put("msg",true);
            }else {
                System.out.println("Registration failed with wrong information");
                retmap.put("msg",false);
            }
        }else {
            System.out.println("Registration failed, user name already exists");
            retmap.put("msg",false);
        }
        ObjectMapper mapper = new ObjectMapper();
        mapper.writeValue(resp.getWriter(),retmap);
    }
}

UplodeServlet:

@MultipartConfig
@WebServlet("/uplode")
public class UplodeServlet extends HttpServlet {
    private static final String SAVEPATH = "D:\\java\\musicdemo\\src\\main\\webapp\\music\\";
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        resp.setContentType("text/html;charset = utf-8");
        User user = (User) req.getSession().getAttribute("user");
        if (user == null){
            System.out.println("No login");
        }else {
            Part part = req.getPart("filename");
            String head = part.getHeader("Content-Disposition");
            int start = head.lastIndexOf("=");
            String filename = head.substring(start+1).replace("\"","");
            part.write(SAVEPATH + filename);
            String singer = req.getParameter("singer");
            String title = filename.replace(".mp3","");
            String url = "music/"+title;
            Music music = new Music();
            music.setTitle(title);
            music.setUrl(url);
            music.setSinger(singer);
            music.setUserid(user.getId());
            MusicDao musicDao = new MusicDao();
            int ret = musicDao.insert(music);
            if (ret==1){
                resp.sendRedirect("list.html");
            }else {
                part.delete();
            }
        }
    }
}

FindMusicServlet

@WebServlet("/findmusic")
public class FindMusicServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        resp.setContentType("application/json;charset=utf-8");
        String musicName = req.getParameter("musicName");
        MusicDao musicDao = new MusicDao();
        new ObjectMapper().writeValue(resp.getWriter(), musicDao.findMusicByKey(musicName));
    }
}

DeleteMusicServlet:

@WebServlet("/delete")
public class DeleteMusicServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        resp.setContentType("application/json;charset=utf-8");
        int id = Integer.parseInt(req.getParameter("id"));
        MusicDao musicDao = new MusicDao();
        Map<String,Object> retrnMap = new HashMap<>();
        User user = (User) req.getSession().getAttribute("user");
        Music music = musicDao.findMusicById(id);
        if (user == null){
            System.out.println("Not logged in, deletion failed");
            retrnMap.put("msg",false);
        }else if (music == null) {
            System.out.println("Unable to find relevant information, deletion failed");
            retrnMap.put("msg",false);
        }else {
            if (music.getUserid() == user.getId()){
                int del = musicDao.deleteMusicById(id);
                File file = new File("D:\\java\\musicdemo\\src\\main\\webapp\\"+music.getUrl()+".mp3");
                System.out.println("Delete succeeded");
                file.delete();
                retrnMap.put("msg",true);
            }else {
                System.out.println("Failed to delete because it is not uploaded by yourself");
                retrnMap.put("msg",false);
            }
        }
        ObjectMapper mapper = new ObjectMapper();
        mapper.writeValue(resp.getWriter(),retrnMap);
    }
}

DeleteMoreServlet:

@WebServlet("/deletemore")
public class DeleteMoreServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        resp.setContentType("application/json;charset=utf-8");
        String[] ids = req.getParameterValues("id[]");
        int sum = 0;
        Map<String,Object> retmap = new HashMap<>();
        MusicDao musicDao = new MusicDao();
        User user = (User)req.getSession().getAttribute("user");
        if (user == null){
            System.out.println("Not logged in, cannot delete");
            retmap.put("msg",false);

        }else {
            Music[] musics = new Music[ids.length];
            for (int i = 0;i<ids.length;i++){
                musics[i] = musicDao.findMusicById(Integer.parseInt(ids[i]));
                if (musics[i] == null){
                    System.out.println("Delete music information is missing and cannot be deleted");
                    retmap.put("msg",false);
                    break;
                }else if (musics[i].getUserid() != user.getId()){
                    System.out.println("Music not uploaded by yourself cannot be deleted");
                    retmap.put("msg",false);
                    break;
                }else {
                    sum+=1;
                }
            }
            if (sum == ids.length){
                for (int i=0;i<musics.length;i++){
                    musicDao.deleteMusicById(musics[i].getId());
                    File file = new File("D:\\java\\musicdemo\\src\\main\\webapp\\"+musics[i].getUrl()+".mp3");
                    file.delete();
                }
                System.out.println("Delete succeeded");
                retmap.put("msg",true);
            }
        }
        ObjectMapper mapper = new ObjectMapper();
        mapper.writeValue(resp.getWriter(),retmap);
    }
}

LoveMusicServlet:

@WebServlet("/lovemusic")
public class LoveMusicServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        resp.setContentType("application/json;charset=utf-8");
        Map<String,Object> retmap = new HashMap<>();
        User user = (User) req.getSession().getAttribute("user");
        int id = Integer.parseInt(req.getParameter("id"));
        LoveMusicDao loveMusicDao = new LoveMusicDao();
        if (user == null){
            retmap.put("msg",false);
            System.out.println("Not logged in");
        }else {
            boolean ret = loveMusicDao.findLoveMusicByUserIdAndMusicId(user.getId(),id);
            if (ret){
                retmap.put("msg",false);
                System.out.println("Favorite list already exists");
            }else {
                ret = loveMusicDao.insertLoveMusic(user.getId(),id);
                if (ret){
                    retmap.put("msg",true);
                    System.out.println("Added successfully");
                }else {
                    retmap.put("msg",false);
                    System.out.println("Add failed");
                }
            }
        }
        ObjectMapper mapper = new ObjectMapper();
        mapper.writeValue(resp.getWriter(),retmap);
    }
}

FindLoveServlet:

@WebServlet("/findlove")
public class FindLoveServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        resp.setContentType("application/json;charset=utf-8");
        User user = (User)req.getSession().getAttribute("user");
        LoveMusicDao loveMusicDao = new LoveMusicDao();
        List<Music> musics = new ArrayList<>();
        String str = req.getParameter("str");
        if (user == null){
            System.out.println("Not logged in");
        }else {
            musics = loveMusicDao.findLoveMusicBykeyAndUserId(str,user.getId());
        }
        ObjectMapper mapper = new ObjectMapper();
        mapper.writeValue(resp.getWriter(),musics);
    }
}

RemoveLoveServlet:

@WebServlet("/removelove")
public class RemoveLoveServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        resp.setContentType("application/json;charset=utf-8");
        User user = (User) req.getSession().getAttribute("user");
        int id = Integer.parseInt(req.getParameter("music_id"));
        Map<String,Object> retmap = new HashMap<>();
        LoveMusicDao loveMusicDao = new LoveMusicDao();
        if (user == null){
            retmap.put("msg",false);
            System.out.println("Not logged in");
        }else {
            int ret = loveMusicDao.removeLoveMusic(user.getId(),id);
            if (ret == 1){
                retmap.put("msg",true);
                System.out.println("Delete succeeded");
            }else {
                retmap.put("msg",false);
                System.out.println("Deletion failed");
            }
        }
        ObjectMapper mapper = new ObjectMapper();
        mapper.writeValue(resp.getWriter(),retmap);
    }
}

supplement

About JSON, JSON is a common way to organize data. It comes from JS. It is a key value pair style data format that stores objects in strings.
JSON usage example:

public class Main {
    public static void main1(String[] args) throws JsonProcessingException {
        Person person = new Person(1,"lit","lit");
        ObjectMapper objectMapper = new ObjectMapper();
        String str =objectMapper.writeValueAsString(person);
        System.out.println(str);
    }
}

In this way, converting an object into a string is called serialization, and converting a string into an object is called deserialization
If this attribute is defined in the Servlet class, it means that the data type returned to the front-end page is json instead of htmlresp.setContentType("application/json;charset=utf-8");, If the following code is written in the Servlet, new ObjectMapper().writeValue(resp.getWriter(), musicDao.findMusicByKey(musicName)); It means that the data passed in to the front end is the return value findMusicByKey(musicName) of this method

postman is a software that simulates sending http requests. It can simulate the browser (client) to send a request you want to send to the server

When obtaining the file name, if you use postman to send, the content will be encrypted. If you need to decrypt, you can use the following methods: (not required if you use a browser)

//The encrypted content is generally as follows:% E8%AE%B8%E5%B7%8D%20-%20%E6%95%85%E4%B9%A1
//At this time, if you use encode for encryption, you can use decode for decryption
	int index = filename.indexOf("%");
	filename = filename.substring(index);
	filename = URLDecoder.decode(filename,"utf-8");

Tags: node.js Database Flask

Posted on Tue, 21 Sep 2021 17:37:44 -0400 by farzal