01-三层架构之查询数据库数据 - IT评测·应用市场-qidao123.com

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');

复制代码

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

复制代码

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/wyy_music
username=root
password=1234

复制代码

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

复制代码

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

复制代码

package com.slj.dao;
import com.slj.entity.Music;
import java.util.List;
public interface MusicDao {
/**
* 查询所有
*
* @return
*/
List<Music> queryAll();
}

复制代码

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

复制代码

package com.slj.service;
import com.slj.entity.Music;
import java.util.List;
public interface MusicService {
/**
* 查询所有歌曲
*
* @return
*/
List<Music> findAll();
}

复制代码

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

复制代码

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

复制代码

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.......");
}
}

复制代码