01-三层架构之查询数据库数据

2023-05-08 09:24 由是七七呀77 发表于 #后端开发

一、后台操作流程

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服务器）

二、介绍一个电脑自带浏览器的插件（可以进行测试哦）