一、后台操作流程
1.创建数据库
CREATE DATABASE wyy_music; USE wyy_music; DROP TABLE IF EXISTS `tb_music`; CREATE TABLE `tb_music` ( `music_id` INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT, -- 歌曲ID `music_name` VARCHAR(255) NOT NULL, -- 歌曲名称 `music_album_name` VARCHAR(255), -- 专辑名称 `music_album_picUrl` VARCHAR(255), -- 专辑图片路径 `music_mp3Url` VARCHAR(255), -- 歌曲播放路径 `music_artist_name` VARCHAR(255), -- 歌手名称 `sheet_id` INT(11) DEFAULT NULL -- 对应的歌单ID ) ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO tb_music VALUES ('1', '光年之外', '光年之外', 'https://imgessl.kugou.com/stdmusic/20161229/20161229233400375274.jpg', 'https://webfs.tx.kugou.com/202109061310/31fb3f36e2048b2172a70e327bbfc8e3/KGTX/CLTX001/f87095bff0de7c636c3a3b8aac702d76.mp3', 'G.E.M.邓紫棋','1'); INSERT INTO tb_music VALUES ('2', '夜空中最亮的星', '世界', 'https://imgessl.kugou.com/stdmusic/20150719/20150719010047203836.jpg', 'https://webfs.ali.kugou.com/202109061306/1b30ae27a5749debd602507b3bf1fea6/G202/M04/1B/13/aocBAF55G0-ADd0HAD2Y88Efqbw072.mp3', '逃跑计划','1'); INSERT INTO tb_music VALUES ('3', '只要平凡', '只要平凡', 'https://imgessl.kugou.com/stdmusic/20180622/20180622194005815458.jpg', 'https://webfs.ali.kugou.com/202109061309/edb2e89d90e66b9d125950dba107e9eb/KGTX/CLTX001/38aead7ed546b0736791ebb25c3a3951.mp3', '张杰/张碧晨','2');
2.在IDEA中创建maven工程并导入依赖
<dependencies> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>4.0.1</version> <scope>provided</scope> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.32</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.17</version> </dependency> <dependency> <groupId>commons-dbutils</groupId> <artifactId>commons-dbutils</artifactId> <version>1.7</version> </dependency> <dependency> <groupId>com.alibaba.fastjson2</groupId> <artifactId>fastjson2</artifactId> <version>2.0.20</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.26</version> </dependency> </dependencies>
3.创建db.properties文件，配置数据库
driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/wyy_music username=root password=1234
4.编写工具类
package com.slj.util; import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.pool.DruidDataSourceFactory; import java.io.InputStream; import java.sql.*; import java.util.Objects; import java.util.Properties; public class JdbcUtil { //声明一个DruidDataSource对象 private static DruidDataSource ds; //声明一个ThreadLocal对象 本地线程对象，线程安全，不需要加锁，提高执行效率 private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<>(); //静态代码块，在类加载时只执行一次，我们可以在这里初始化数据源对象 //整个项目中只需要一个数据源对象就可以 数据源内部有个连接池对象，我们之后获取连接可以从连接池中获取 static { try { //读取database.properties属性文件获取到输入流对象 InputStream inputStream = JdbcUtil.class.getResourceAsStream("/db.properties"); //创建Properties对象 Properties properties = new Properties(); //属性文件中的内容就读取到properties中了 properties.load(inputStream); //根据properties文件创建数据源 ds = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties); } catch (Exception e) { e.printStackTrace(); } } //获取连接的方法 getConnection public static Connection getConnection() { //从THREAD_LOCAL获取连接对象 Connection connection = THREAD_LOCAL.get(); if (Objects.isNull(connection)) { try { connection = ds.getConnection(); // 第一次获取到连接对象 connection 就放到THREAD_LOCAL,以后再取的时候里面就有值了 THREAD_LOCAL.set(connection); } catch (SQLException e) { e.printStackTrace(); } } return connection; } //关闭资源的方法 private static void close(Connection connection) { if (Objects.nonNull(connection)) { try { connection.close(); //关闭连接后 把THREAD_LOCAL中的对象清除掉,一定不能忘记 THREAD_LOCAL.remove(); } catch (SQLException e) { e.printStackTrace(); } } } }
5.编写实体类
package com.slj.entity; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; /** * 实体类 * 有了lombok依赖，直接使用注解方式 */ @Data @NoArgsConstructor @AllArgsConstructor public class Music { private Integer musicId; private String musicName; private String musicAlbumName; private String musicAlbumPicurl; private String musicMp3url; private String musicArtistName; private String sheetId; }
6.编写持久层（dao层）
package com.slj.dao; import com.slj.entity.Music; import java.util.List; public interface MusicDao { /** * 查询所有 * * @return */ List<Music> queryAll(); }
7.编写DaoImpl
package com.slj.dao.impl; import com.slj.dao.MusicDao; import com.slj.entity.Music; import com.slj.util.JdbcUtil; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanListHandler; import java.sql.Connection; import java.sql.SQLException; import java.util.List; public class MusicDaoImpl implements MusicDao { private QueryRunner queryRunner = new QueryRunner(); /** * 查询所有信息 * * @return */ @Override public List<Music> queryAll() { //1.获取连接 Connection connection = JdbcUtil.getConnection(); //2.SQL语句 String sql = "select music_id as musicId,music_name as musicName," + "music_album_name as musicAlbumName,music_album_picUrl as musicAlbumPicurl," + "music_mp3Url as musicMp3url,music_artist_name as musicArtistName," + "sheet_id as sheetId from tb_music"; //3.调用方法 try { return queryRunner.query(connection, sql, new BeanListHandler<>(Music.class)); } catch (SQLException e) { throw new RuntimeException(e); } } }
8.编写业务逻辑层（service层）
package com.slj.service; import com.slj.entity.Music; import java.util.List; public interface MusicService { /** * 查询所有歌曲 * * @return */ List<Music> findAll(); }
9.编写ServiceImpl
package com.slj.service.impl; import com.slj.dao.MusicDao; import com.slj.dao.impl.MusicDaoImpl; import com.slj.entity.Music; import com.slj.service.MusicService; import java.util.List; public class MusicServiceImpl implements MusicService { private MusicDao musicDao = new MusicDaoImpl(); //调用dao层方法 public List<Music> findAll() { return musicDao.queryAll(); } }
10.编写页面层（Controller层）
package com.slj.controller; import com.alibaba.fastjson2.JSON; import com.slj.entity.Music; import com.slj.service.MusicService; import com.slj.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("/findAllController.do") public class FindAllController extends HttpServlet { @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //调用service层处理核心业务逻辑 MusicService musicService = new MusicServiceImpl(); List<Music> all = musicService.findAll(); //将Java数据转换成Json数据 String jsonString = JSON.toJSONString(all); //返回给前端 response.getWriter().write(jsonString); } @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { this.doGet(request, response); } }
11.编写过滤器（处理请求响应字符乱码问题）
package com.slj.filter; import javax.servlet.*; import javax.servlet.annotation.WebFilter; import java.io.IOException; @WebFilter(urlPatterns = "*.do") public class EncodingFilter implements Filter { @Override public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException { /** * 设置编码 */ request.setCharacterEncoding("utf-8"); response.setCharacterEncoding("utf-8"); response.setContentType("text/html;charset=utf-8"); chain.doFilter(request, response); } @Override public void init(FilterConfig filterConfig) throws ServletException { System.out.println("init......."); } @Override public void destroy() { System.out.println("destroy......."); } }
12.查询结果（tomcat服务器）
二、介绍一个电脑自带浏览器的插件（可以进行测试哦）
原文链接：https://www.cnblogs.com/shilj/p/17372205.html
本站文章如无特殊说明，均为本站原创，如若转载，请注明出处：01-三层架构之查询数据库数据 - Python技术站
微信扫一扫 
支付宝扫一扫 