1.准备环境
2.1 开发工具
IDEA2019 微信开发者工具 PostMan SQLYog
2.2 开发环境
Java+mysql+JavaScript+json
2. 简介
前后端分离,在微信开发者工具中写页面以及跳转,在数据库中写入音乐类型 专辑名称 演唱者 以及音乐的url ,通过Java 将数据库中的数据获取出来封装成 json 通过url进行传值,在前端进行解析展示出来
3.代码
sql:创建数据库 表 插入数据
/*
SQLyog Ultimate v13.1.1 (64 bit)
MySQL - 5.7.29 : Database - wyy_music
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`wyy_music` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `wyy_music`;
/*Table structure for table `tb_music` */
DROP TABLE IF EXISTS `tb_music`;
CREATE TABLE `tb_music` (
`music_id` int(11) NOT NULL AUTO_INCREMENT,
`music_name` varchar(255) NOT NULL,
`music_album_name` varchar(255) NOT NULL,
`music_album_picUrl` varchar(255) NOT NULL,
`music_mp3Url` varchar(255) NOT NULL,
`music_artist_name` varchar(255) NOT NULL,
`sheet_id` int(11) DEFAULT NULL,
PRIMARY KEY (`music_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*Data for the table `tb_music` */
insert into `tb_music`(`music_id`,`music_name`,`music_album_name`,`music_album_picUrl`,`music_mp3Url`,`music_artist_name`,`sheet_id`) values
(1,'光年之外','光年之外','https://imgessl.kugou.com/stdmusic/20161229/20161229233400375274.jpg','https://webfs.ali.kugou.com/202109271010/183cab94955d96aee0120d1d4677540d/KGTX/CLTX001/f87095bff0de7c636c3a3b8aac702d76.mp3','G.E.M.邓紫棋',1),
(2,'夜空中最亮的星','世界','https://imgessl.kugou.com/stdmusic/20150719/20150719010047203836.jpg','https://webfs.tx.kugou.com/202109271011/8ac45da301d093ccdd58111ad91371eb/G202/M04/1B/13/aocBAF55G0-ADd0HAD2Y88Efqbw072.mp3','逃跑计划',1),
(3,'只要平凡','只要平凡','https://y.qq.com/music/photo_new/T002R300x300M000000K7srf1rZtOX.jpg?max_age=2592000','https://webfs.ali.kugou.com/202109271009/ac5e4c1b2e0e14cc1fc06d6ef8bbd286/KGTX/CLTX001/38aead7ed546b0736791ebb25c3a3951.mp3','张杰/张碧晨',2),
(4,'你要跳舞吗','生命因你而火热','https://imgessl.kugou.com/stdmusic/20160407/20160407002744966139.jpg','https://webfs.ali.kugou.com/202109271014/fd2f5cee5791b2ed79bd65490c5af5a7/KGTX/CLTX001/58ffa0221ed9397e7ad9b889cdbe1a4a.mp3','新裤子乐队',2);
/*Table structure for table `tb_sheet` */
DROP TABLE IF EXISTS `tb_sheet`;
CREATE TABLE `tb_sheet` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sheet_name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*Data for the table `tb_sheet` */
insert into `tb_sheet`(`id`,`sheet_name`) values
(1,'热歌榜'),
(2,'新歌榜'),
(3,'原创榜'),
(6,'抖音榜');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
文件结构
我是通过druid连接的数据库
druid.properties配置文件
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/wyy_music?useSSL=false
username=root
password=1234
initialSize=5
maxActive=20
maxWait=2000
JdbcUtil
package com.java.util;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.dbutils.QueryRunner;
import javax.sql.DataSource;
import java.io.InputStream;
import java.util.Properties;
//使用DBUtils的工具类
public class JdbcUtil {
//声明DataSource对象
private static DataSource dataSource;
//静态代码块
static {
try {
//读取db.properties文件
InputStream resourceAsStream = JdbcUtil.class.getResourceAsStream("/druid.properties");
//创建Properties对象
Properties properties = new Properties();
//加载流对象
properties.load(resourceAsStream);
//创建数据源对象
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取DButils中核心类对象QueryRunner对象
public static QueryRunner getQueryRunner(){
return new QueryRunner(dataSource);//此时已经连接上数据库了
}
}
实体类层 pojo
Music类
package com.java.pojo;
import java.io.Serializable;
//实体类
public class Music implements Serializable {
private Integer musicId;
private String musicName;
private String musicAlbumName;
private String musicAlbumPicurl;
private String musicMp3url;
private String musicArtistName;
private String sheetId;
public Integer getMusicId() {
return musicId;
}
public void setMusicId(Integer musicId) {
this.musicId = musicId;
}
public String getMusicName() {
return musicName;
}
public void setMusicName(String musicName) {
this.musicName = musicName;
}
public String getMusicAlbumName() {
return musicAlbumName;
}
public void setMusicAlbumName(String musicAlbumName) {
this.musicAlbumName = musicAlbumName;
}
public String getMusicAlbumPicurl() {
return musicAlbumPicurl;
}
public void setMusicAlbumPicurl(String musicAlbumPicurl) {
this.musicAlbumPicurl = musicAlbumPicurl;
}
public String getMusicMp3url() {
return musicMp3url;
}
public void setMusicMp3url(String musicMp3url) {
this.musicMp3url = musicMp3url;
}
public String getMusicArtistName() {
return musicArtistName;
}
public void setMusicArtistName(String musicArtistName) {
this.musicArtistName = musicArtistName;
}
public String getSheetId() {
return sheetId;
}
public void setSheetId(String sheetId) {
this.sheetId = sheetId;
}
@Override
public String toString() {
return "Music{" +
"musicId=" + musicId +
", musicName='" + musicName + '\'' +
", musicAlbumName='" + musicAlbumName + '\'' +
", musicAlbumPicurl='" + musicAlbumPicurl + '\'' +
", musicMp3url='" + musicMp3url + '\'' +
", musicArtistName='" + musicArtistName + '\'' +
", sheetId='" + sheetId + '\'' +
'}';
}
}
sheet类
package com.java.pojo;
public class Sheet {
private Integer id;
private String sheetName;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
@Override
public String toString() {
return "Sheet{" +
"id=" + id +
", sheetName='" + sheetName + '\'' +
'}';
}
}
dao层
package com.java.dao;
import com.java.pojo.Music;
import java.util.List;
public interface MusicDao {
public List<Music> findAll();
public Music findById(String musicId);
public List<Music> findByMusicName(String musicName);
}
package com.java.dao;
import com.java.pojo.Sheet;
import java.util.List;
public interface SheetDao {
public List<Sheet> getSheet();
public void insertSheet(String sheetName);
}
dao层实现类
package com.java.dao.impl;
import com.java.dao.MusicDao;
import com.java.pojo.Music;
import com.java.util.JdbcUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;
import java.util.List;
public class MusicDaoImpl implements MusicDao {
@Override
public List<Music> findAll() {
String sql = "select music_id musicId,music_name musicName,music_album_name musicAlbumName," +
"music_album_picUrl musicAlbumPicurl,music_mp3Url musicMp3url," +
"music_artist_name musicArtistName,sheet_id sheetId from tb_music";
QueryRunner queryRunner = JdbcUtil.getQueryRunner();
List<Music> musicList = null;
try {
musicList = queryRunner.query(sql, new BeanListHandler<Music>(Music.class));
} catch (SQLException e) {
e.printStackTrace();
}
return musicList;
}
@Override
public Music findById(String musicId) {
String sql = "select music_id musicId,music_name musicName,music_album_name musicAlbumName," +
"music_album_picUrl musicAlbumPicurl,music_mp3Url musicMp3url," +
"music_artist_name musicArtistName,sheet_id sheetId from tb_music where music_id = ?";
QueryRunner queryRunner = JdbcUtil.getQueryRunner();
Music music = null;
try {
music = queryRunner.query(sql, new BeanHandler<Music>(Music.class), musicId);
} catch (SQLException e) {
e.printStackTrace();
}
return music;
}
@Override
public List<Music> findByMusicName(String musicName) {
String sql = "select music_id musicId,music_name musicName,music_album_name musicAlbumName," +
"music_album_picUrl musicAlbumPicurl,music_mp3Url musicMp3url," +
"music_artist_name musicArtistName,sheet_id sheetId from tb_music where music_name like ?";
QueryRunner queryRunner = JdbcUtil.getQueryRunner();
List<Music> musicList = null;
try {
musicList = queryRunner.query(sql, new BeanListHandler<Music>(Music.class), "%" + musicName + "%");
} catch (SQLException e) {
e.printStackTrace();
}
return musicList;
}
}
package com.java.dao.impl;
import com.java.dao.SheetDao;
import com.java.pojo.Sheet;
import com.java.util.JdbcUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;
import java.util.List;
public class SheetDaoImpl implements SheetDao {
@Override
public List<Sheet> getSheet() {
String sql = "select id,sheet_name sheetName from tb_sheet";
QueryRunner queryRunner = JdbcUtil.getQueryRunner();
List<Sheet> query = null;
try {
query = queryRunner.query(sql, new BeanListHandler<Sheet>(Sheet.class));
} catch (SQLException e) {
e.printStackTrace();
}
return query;
}
@Override
public void insertSheet(String sheetName) {
String sql = "insert into tb_sheet(sheet_name) values (?)";
QueryRunner queryRunner = JdbcUtil.getQueryRunner();
try {
int update = queryRunner.update(sql,sheetName);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
service层
package com.java.service;
import com.java.pojo.Music;
import java.util.List;
public interface MusicService {
public List<Music> findAll();
public Music findById(String musicId);
public List<Music> findByMusicName(String musicName);
public Music nextSong(String musicId);
public Music lastSong(String musicId);
}
package com.java.service;
import com.java.pojo.Sheet;
import java.util.List;
public interface SheetService {
public List<Sheet> getSheet();
public void addSheet(String sheetName);
}
package com.java.service.impl;
import com.java.dao.MusicDao;
import com.java.dao.impl.MusicDaoImpl;
import com.java.pojo.Music;
import com.java.service.MusicService;
import java.util.List;
public class MusicServiceImpl implements MusicService {
@Override
public List<Music> findAll() {
MusicDao musicDao = new MusicDaoImpl();
List<Music> musicList = musicDao.findAll();
return musicList;
}
@Override
public Music findById(String musicId) {
MusicDao musicDao = new MusicDaoImpl();
Music music = musicDao.findById(musicId);
return music;
}
@Override
public List<Music> findByMusicName(String musicName) {
MusicDao musicDao = new MusicDaoImpl();
List<Music> musicNameList = musicDao.findByMusicName(musicName);
return musicNameList;
}
@Override
public Music nextSong(String musicId) {
MusicDao musicDao = new MusicDaoImpl();
List<Music> musicList = musicDao.findAll();
if (musicList.size()>0){
int currentIndex = 0;
for (int i = 0; i < musicList.size(); i++) {
if (musicList.get(i).getMusicId().equals(Integer.parseInt(musicId))){
currentIndex = i;
break;
}
}
int nextSongIndex = currentIndex == musicList.size() -1 ? 0 : currentIndex+1;
return musicList.get(nextSongIndex);
}else {
throw new RuntimeException("歌曲列表不存在");
}
}
@Override
public Music lastSong(String musicId) {
MusicDao musicDao = new MusicDaoImpl();
List<Music> musicList = musicDao.findAll();
if (musicList.size()>0){
int currentIndex = 0;
for (int i = 0; i < musicList.size(); i++) {
if (musicList.get(i).getMusicId().equals(Integer.parseInt(musicId))){
currentIndex = i;
break;
}
}
int lastSongIndex = currentIndex == 0 ? musicList.size()-1 : currentIndex-1;
return musicList.get(lastSongIndex);
}else {
throw new RuntimeException("歌曲列表不存在");
}
}
}
package com.java.service.impl;
import com.java.dao.SheetDao;
import com.java.dao.impl.SheetDaoImpl;
import com.java.pojo.Sheet;
import com.java.service.SheetService;
import java.util.List;
public class SheetServiceImpl implements SheetService {
@Override
public List<Sheet> getSheet() {
SheetDao sheetDao = new SheetDaoImpl();
List<Sheet> sheet = sheetDao.getSheet();
return sheet;
}
@Override
public void addSheet(String sheetName) {
SheetDao sheetDao = new SheetDaoImpl();
sheetDao.insertSheet(sheetName);
}
}
web层
package com.java.web;
import com.java.service.SheetService;
import com.java.service.impl.SheetServiceImpl;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet("/addSheetServlet")
public class AddSheetServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
SheetService sheetService = new SheetServiceImpl();
String sheetName = request.getParameter("sheetName");
sheetService.addSheet(sheetName);
}
}
package com.java.web;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.java.dao.impl.MusicDaoImpl;
import com.java.pojo.Music;
import com.java.service.MusicService;
import com.java.service.impl.MusicServiceImpl;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
@WebServlet("/musicFindAllServlet")
public class MusicFindAllServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//设置字符集
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
//
MusicService musicService = new MusicServiceImpl();
List<Music> musicList = musicService.findAll();
ObjectMapper objectMapper = new ObjectMapper();
String jsonMusic = objectMapper.writeValueAsString(musicList);
response.getWriter().write(jsonMusic);
}
}
package com.java.web;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.java.pojo.Music;
import com.java.service.MusicService;
import com.java.service.impl.MusicServiceImpl;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet("/musicFindByIdServlet")
public class MusicFindByIdServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
//
MusicService musicService = new MusicServiceImpl();
String musicId = request.getParameter("musicId");
Music music = musicService.findById(musicId);
ObjectMapper objectMapper = new ObjectMapper();
String s = objectMapper.writeValueAsString(music);
response.getWriter().write(s);
}
}
package com.java.web;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.java.pojo.Music;
import com.java.service.MusicService;
import com.java.service.impl.MusicServiceImpl;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
@WebServlet("/musicFindByMusicNameServlet")
public class MusicFindByMusicNameServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
MusicService musicService = new MusicServiceImpl();
String musicName = request.getParameter("musicName");
List<Music> musicList = musicService.findByMusicName(musicName);
ObjectMapper objectMapper = new ObjectMapper();
String s = objectMapper.writeValueAsString(musicList);
response.getWriter().write(s);
}
}
package com.java.web;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.java.pojo.Music;
import com.java.service.MusicService;
import com.java.service.impl.MusicServiceImpl;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet("/musicLastSongServlet")
public class MusicLastSongServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
String musicId = request.getParameter("musicId");
MusicService musicService = new MusicServiceImpl();
Music music = musicService.lastSong(musicId);
ObjectMapper objectMapper = new ObjectMapper();
String s = objectMapper.writeValueAsString(music);
response.getWriter().write(s);
}
}
package com.java.web;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.java.pojo.Music;
import com.java.service.MusicService;
import com.java.service.impl.MusicServiceImpl;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet("/musicNextSongServlet")
public class MusicNextSongServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
String musicId = request.getParameter("musicId");
MusicService musicService = new MusicServiceImpl();
Music music = musicService.nextSong(musicId);
ObjectMapper objectMapper = new ObjectMapper();
String s = objectMapper.writeValueAsString(music);
response.getWriter().write(s);
}
}
package com.java.web;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.java.pojo.Sheet;
import com.java.service.SheetService;
import com.java.service.impl.SheetServiceImpl;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
@WebServlet("/sheetServlet")
public class SheetServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
SheetService sheetService = new SheetServiceImpl();
List<Sheet> sheet = sheetService.getSheet();
String s = new ObjectMapper().writeValueAsString(sheet);
response.getWriter().write(s);
}
}
接下来就是前端页面
在微信开发者工具中的文件结构是这样
前端的代码比较复杂这里就不过多整理了 如果有小伙伴想要源码可以加一下交流群
下面是效果展示