接上期,本次实现了我的音乐模块的展示,并且新增了添加歌曲为我喜欢、收藏歌单功能,先展示成果界面。
可以看到左边为两个模块,一个为自建歌单,一个为收藏歌单,自建歌单就是用户自己新建的歌单,收藏歌单就是收藏别人的歌单,我喜欢模块参考酷狗音乐为默认歌单,可以通过点击歌曲列表的爱心符号实现收藏。
为了实现查询歌曲列表时同时判断用户是否添加歌曲为我喜欢,这样就要涉及一个关联表,这个关联表用于存放歌曲ID与用户ID的映射关系:
usersong_table:
在SQL查询语句进行查询时,我们接上期的查询语句,只需要根据当前用户ID左连接一个usersong表即可:
<select id="findSongBySheetId" resultType="com.jhb.crash_music.pojo.Song2">
SELECT
so.song_id,
so.song_singerId,
so.song_albumId,
so.song_name,
so.song_mp3Url,
so.song_thumbsNum,
so.song_lyc,
so.song_classify,
si.singer_name AS song_singerName,
al.album_name AS song_albumName,
al.album_imgUrl AS song_albumImgUrl,
us.usersong_userId AS song_likeUserId
FROM
song_table AS so
LEFT JOIN singers_table AS si ON so.song_singerId = si.singer_id
LEFT JOIN album_table AS al ON so.song_albumId = al.album_id
LEFT JOIN usersong_table AS us ON so.song_id = us.usersong_songId AND us.usersong_userId = #{user_id}
WHERE
song_id IN ( SELECT sl.songlist_songId FROM songlist_table AS sl WHERE sl.songlist_sheetId = #{sheet_id})
</select>
其中song_likeUserId就是用来锁定当前歌曲列表是否被用户添加进我喜欢,我的音乐对应的controller语句为:
@GetMapping(path = "myMusic")
public String myMusic(Model model,@Param("sheet_userId")Integer sheet_userId,@Param("sheet_Id")Integer sheet_id,HttpServletRequest req){
HttpSession session = req.getSession();
User user1 = (User) session.getAttribute("LoginUser");
Sheet sheet = new Sheet();
List<Song2> songList = new ArrayList<>();
if(sheet_id==0){
//默认歌单:我喜欢
sheet = new Sheet(0,"我喜欢","/images/background/我喜欢.png",0,sheet_userId,"默认歌单");
songList = songService.findSongByUserAndSong(user1.getUser_id());
}else {
//自建歌单
sheet = sheetService.findSheetById(sheet_id);
songList = songService.findSongBySheetId(sheet_id,user1.getUser_id());
}
List<Sheet> mySheetList = sheetService.findSheetByUserId(user1.getUser_id());
User user = userService.findUserById(sheet.getSheet_userId());
List<Sheet> colSheetList = sheetService.findColSheetByUserId(user1.getUser_id());
model.addAttribute("mySheetList",mySheetList);
model.addAttribute("songList",songList);
model.addAttribute("colSheetList",colSheetList);
model.addAttribute("user",user);
model.addAttribute("sheet",sheet);
return "reception/index/myMusic";
}
添加与删除歌曲映射的controller语句为:
@GetMapping(path = "addUserAndSong")
public String addUserAndSong(@Param("usersong_songId")Integer usersong_songId,@Param("usersong_userId")Integer usersong_userId,
@Param("sheet_id")Integer sheet_id,@Param("status")String status){
if(!songService.addUserAndSong(usersong_songId,usersong_userId)){
System.out.println("添加歌曲与用户映射关系失败");
}
if(status.equals("info")){
return "redirect:info?page=1&sheet_id="+sheet_id;
}
if(status.equals("myMusic")){
return "redirect:myMusic?sheet_userId="+usersong_userId+"&sheet_id="+sheet_id;
}
return "redirect:info?page=1&sheet_id="+sheet_id;
}
@GetMapping(path = "delUserAndSong")
public String delUserAndSong(@Param("usersong_songId")Integer usersong_songId,@Param("usersong_userId")Integer usersong_userId,
@Param("sheet_id")Integer sheet_id,@Param("status")String status){
if(!songService.delUserAndSong(usersong_songId,usersong_userId)){
System.out.println("删除歌曲与用户映射关系失败");
}
if(status.equals("info")){
return "redirect:info?page=1&sheet_id="+sheet_id;
}
if(status.equals("myMusic")){
return "redirect:myMusic?sheet_userId="+usersong_userId+"&sheet_id="+sheet_id;
}
return "redirect:info?page=1&sheet_id="+sheet_id;
}
前端实现:
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org"
xmlns:sec="http://www.thymeleaf.org/extras/spring-security"
xmlns:shiro="http://www.pollix.at/thymeleaf/shiro">
<head>
<title>我的音乐</title>
<div th:replace="~{reception/common/common::cjbar}"/>
</head>
<body class="myBody">
<div th:replace="~{reception/common/common::topbar}"/>
<div th:replace="~{reception/common/common::topbar2}"/>
<div class="middle-width">
<div>
<div style="width: 100%;height: 5000px;border-left: 1px rgb(211,211,211) solid;border-right: 1px rgb(211,211,211) solid;">
<div style="width: 30%;float: left;height:5000px;border-right: 1px rgb(211,211,211) solid;">
<div style="width: 100%;padding: 20px;">
<p style="font-size: 20px;font-family: '黑体'">自建歌单</p>
<hr>
<div class="sheet-div" th:onclick="|sheetSel(0,${sheet.getSheet_userId()},0)|">
<table>
<tr><td><img style="width: 60px;height: 60px;" src="/images/background/我喜欢.png"></td>
<td style="font-size: 15px;padding: 5px;" th:text="我喜欢"></td>
</tr>
</table>
</div>
<div class="sheet-div" th:each="sheet,sheetStat:${mySheetList}" th:onclick="|sheetSel(${sheetStat.index+1},${sheetStat.index+1},1)|">
<input type="hidden" th:id="m+${sheetStat.index+1}" th:value="${sheet.getSheet_id()}">
<input type="hidden" th:id="mu+${sheetStat.index+1}" th:value="${sheet.getSheet_userId()}">
<table>
<tr><td rowspan="2"><img style="width: 60px;height: 60px;" th:src="@{${sheet.getSheet_imgUrl()}}"></td>
<td style="font-size: 15px;padding: 5px;" th:text="${sheet.getSheet_name()}"></td>
</tr>
<tr><td style="font-size: 12px;color: dimgray;padding: 5px;" th:text="${'播放量:'+#numbers.formatDecimal(sheet.getSheet_volume()/10000.0,1,'COMMA',1,'POINT')+'w'}"></td></tr>
</table>
</div>
</div>
<div style="width: 100%;padding: 20px;">
<p style="font-size: 20px;font-family: '黑体'">收藏歌单</p><br>
<hr>
<div class="sheet-div" th:each="sheet,sheetStat:${colSheetList}" th:onclick="|sheetSel(${sheetStat.index+1},${sheetStat.index+1},2)|">
<input type="hidden" th:id="c+${sheetStat.index+1}" th:value="${sheet.getSheet_id()}">
<input type="hidden" th:id="cu+${sheetStat.index+1}" th:value="${sheet.getSheet_userId()}">
<table>
<tr><td rowspan="2"><img style="width: 60px;height: 60px;" th:src="@{${sheet.getSheet_imgUrl()}}"></td>
<td style="font-size: 15px;padding: 5px;" th:text="${sheet.getSheet_name()}"></td>
</tr>
<tr><td style="font-size: 12px;color: dimgray;padding: 5px;" th:text="${'播放量:'+#numbers.formatDecimal(sheet.getSheet_volume()/10000.0,1,'COMMA',1,'POINT')+'w'}"></td></tr>
</table>
</div>
</div>
</div>
<div style="width: 70%;height:5000px;float: left;border-right: 1px rgb(211,211,211) solid;">
<div style="width: 100%;padding: 40px;background-color: rgb(250,250,250);border: 1px rgb(211,211,211) solid;">
<table>
<tr><td style="width: 250px;" rowspan="4"><img style="width: 200px;height: 200px;" th:src="@{${sheet.getSheet_imgUrl()}}"></td>
<td style="width: 350px;font-size: 20px;" th:text="'歌单:《'+${sheet.getSheet_name()}+'》'"></td>
</tr>
<tr><td style="color: dimgray">创建者:<a th:href="@{/user/info(user_id=${sheet.getSheet_userId()},infoStatus=1)}" th:text="${user.getUser_nickname()}"></a></td></tr>
<tr><td style="color: dimgray" rowspan="2" th:text="'简介:'+${sheet.getSheet_details()}"></td></tr>
</table>
</div>
<div style="width: 100%;padding: 40px;">
<a type="button" class="btn btn-success btn-color btn-size" href=""><i class="bi bi-caret-right"></i>播放全部</a>
<br><p style="font-size: 20px;">歌曲列表</p>
<div class="table-responsive">
<table class="table table-sm tr-hover">
<thead>
<tr>
<td style="width: 50px;"></td>
<td style="width: 100px;">封面</td>
<td style="width: 190px;">歌曲名</td>
<td style="width: 60px;">歌手</td>
<td style="width: 200px;">专辑</td>
</tr>
</thead>
<tbody>
<tr th:each="song:${songList}">
<td style="height: 70px;padding-top: 26px;font-size: 20px;padding-left: 10px;">
<a th:if="${song.getSong_likeUserId() eq session.LoginUser.getUser_id()}"
th:href="@{/sheet/delUserAndSong(usersong_songId=${song.getSong_id()},usersong_userId=${session.LoginUser.getUser_id()},sheet_id=${sheet.getSheet_id()},status='myMusic')}"style="color: red;">
<i class="bi bi-suit-heart-fill"></i>
</a>
<a th:if="${song.getSong_likeUserId() ne session.LoginUser.getUser_id()}"
th:href="@{/sheet/addUserAndSong(usersong_songId=${song.getSong_id()},usersong_userId=${session.LoginUser.getUser_id()},sheet_id=${sheet.getSheet_id()},status='myMusic')}"style="color: red">
<i class="bi bi-suit-heart"></i>
</a>
</td>
<td><img width="70px;" height="70px;" th:src="@{${song.getSong_albumImgUrl()}}"></td>
<td style="height: 70px;padding-top: 26px;font-size: 17px;" th:text="${song.getSong_name()}"></td>
<td style="height: 70px;padding-top: 26px;font-size: 17px;"><p th:text="${song.getSong_singerName()}"></p></td>
<td style="height: 70px;padding-top: 26px;font-size: 17px;"><p th:text="'《'+${song.getSong_albumName()}+'》'"></p></td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
</div>
<div class="bottom-navigation">
</div>
</body>
<script>
layui.use(['carousel', 'form'], function() {
var carousel = layui.carousel
, form = layui.form;
});
function sheetSel(sid,uid,target) {
var sheet_id;
var sheet_userId;
if(sid==0||sid=='0'){
sheet_id=0;
sheet_userId = uid;
}
else if(target==1){
sheet_id = document.getElementById('m'+sid).value;
sheet_userId = document.getElementById('mu'+uid).value;
}else {
sheet_id = document.getElementById('c'+sid).value;
sheet_userId = document.getElementById('cu'+uid).value;
}
window.location = "/sheet/myMusic?sheet_id="+sheet_id+"&sheet_userId="+sheet_userId;
}
</script>
</html>
我们来实验一下,看看点击添加我喜欢能否成功,首先点击自己的收藏歌单里,去找没有添加我喜欢的歌曲:
可以看到Style这首歌还没有添加进我喜欢,此时点击爱心:
查看我喜欢列表:
可以看到Style已经添加进我喜欢列表里,并且它的爱心是实心的,实现成功。
还实现了一个热门歌手模块:
不过还没添加啥歌手,也没有进行排版优化,会在下一期进行介绍。