Java实验--基于Swing的简单的歌曲信息管理系统(二)
分类:
IT文章
•
2023-12-28 23:13:12
原创不易,转载请标明出处。上一篇《Java实验--基于Swing的简单的歌曲信息管理系统(一)》讲解了如何这个小项目的具体情况和主要功能,下面进入编码阶段。
在eclipse中新建java项目,项目的结构如下:
该项目简单,只涉及到了两种表,且没有涉及到关联查询,就只是对单表进行操作。
工具:使用的是XMAPP(LZ装了好久的mysql没有装上,各种报错,然后同学就推荐了这款集成工具)和Navicat。
步骤:
- 启动XMAPP,然后启动MySQL。如下图所示:
2. 这里没有选择用命令行来创建数据库,而是直接在可视化工具navicat中新建(若使用,点击XMAPP中的shell按钮即可),数据库名为songmanage,注意选择编码为utf8,新建两张表,一张名为song,一张名为user。具体表的字段如下:
user表(主键id,用户名name,密码password,类型role:1代表用户、2代表管理员,roderby用来排序自增):
song表(主键id,歌曲名name,语言language,类别category,歌手名singer,orderby用来排序自增):
若使用命令行建数据库:
# 创建数据库
CREATE DATABASE 数据库名;
# 创建表
CREATE TABLE 表名(
列名 数据类型 [NULL | NOT NULL],
........
);
- 用户实体类:User.java
package shiyan6.entity;
/**
* 用户实体类,其中的role用于分别是普通人员还是管理员
*
*/
public class User {
private String id;
private String name;
private String password;
private int role; // 用户身份,1表示普通用户,2表示管理员
// 无参构造器
public User() {
}
/**
* 有参构造器
* @param id,使用util包下的Common.getUUID()来产生一个8位的uuid
* @param name,用户名
* @param password,登录密码
*/
public User(String id, String name, String password,int role) {
this.id = id;
this.name = name;
this.password = password;
this.role = role;
}
// 各个属性的set、get方法
..........
}
View Code
2. 歌曲实体类:Song.java
package shiyan6.entity;
/**
* 歌曲实体类
*
*/
public class Song {
private String id; // 编号
private String name; // 歌曲名
private String language; // 语言
private String category; // 类别
private String singer; // 歌手名
/**
* 有参构造函数
* @param name
* @param language
* @param category
* @param singer
*/
public Song(String id, String name, String language, String category, String singer) {
this.id = id;
this.name = name;
this.language = language;
this.category = category;
this.singer = singer;
}
/**
* 无参构造函数
*/
public Song() {
}
// 各属性的get、set方法
.............
}
View Code
- jdbc.properties文件类容(数据库配置信息)
# 数据库的用户名
jdbc.username=root
# 数据库的密码
jdbc.password=
# 数据库驱动
jdbc.driver=com.mysql.jdbc.Driver
# url
jdbc.url=jdbc:mysql://127.0.0.1:3306/songmanage?characterEncoding=utf8
- JdbcUtil.java(获取数据连接,util包下)
这里需要注意的获取数据库配置文件时,路径的问题。
InputStream inStream = JdbcUtil.class.getResourceAsStream("/shiyan6/jdbc.properties");
Properties prop = new Properties();
prop.load(inStream);
该文件完整代码:
package shiyan6.util;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
/**
* 数据库帮助类,主要用于获取数据库连接
*
*/
public class JdbcUtil {
// 表示定义数据库的用户名
private static String USERNAME;
// 定义数据库的密码
private static String PASSWORD;
// 定义数据库的驱动信息
private static String DRIVER;
// 定义访问数据库的地址
private static String URL;
// 定义数据库的连接
private static Connection connection;
/**
* 静态加载数据库配置信息,并给相关的属性赋值
*/
static {
try {
InputStream inStream = JdbcUtil.class.getResourceAsStream("/shiyan6/jdbc.properties");
Properties prop = new Properties();
prop.load(inStream);
USERNAME = prop.getProperty("jdbc.username");
PASSWORD = prop.getProperty("jdbc.password");
DRIVER = prop.getProperty("jdbc.driver");
URL = prop.getProperty("jdbc.url");
} catch (Exception e) {
throw new RuntimeException("读取数据库配置文件异常!", e);
}
}
/**
* 获取连接
* @return
*/
public static Connection getConn() {
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
return connection;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
View Code
使用JUnit生成一个测试类,JdbcUtilTest.java,在mysql启动的状态下,看看能否获取到链接。
package shiyan6.test;
import java.sql.Connection;
import org.junit.Assert;
import org.junit.Test;
import shiyan6.util.JdbcUtil;
public class JdbcUtilTest {
@Test
public void testGetConn() {
Connection connection = JdbcUtil.getConn();
Assert.assertNotNull(connection);
System.out.println(connection);
}
}
View Code
看到如下效果,则说明测试通过,同时控制台打印消息。
涉及到的sql语句:
查找:SELECT 列名, .... FROM 表名, .... [WHERE 条件] [UNION .....] [GROUP BY .....]
[HAVING ....] [ORDER BY 排序条件];
插入:INSERT INTO 表名 [(列名, .......)] VALUES(值, ......);
更新:UPDATE 表名 SET 需修改的列=值 [WHERE .....];
删除:DELETE FROM 表名 [WHERE ......];
- 对用户的操作
userDao接口
package shiyan6.dao;
import java.util.List;
import shiyan6.entity.User;
/**
* UserDao 接口
* @author Changsheng
*
*/
public interface UserDao {
/**
* 查询所有普通用户信息
* @return
*/
List<User> findAll();
/**
* 通过用户名查看用户是否存在
* @param name
* @return
*/
int findCountByName(String name);
/**
* 通过用户名查看用户
* @param name
* @return
*/
List<User> findByName(String name);
/**
* 通过用户id查询信息
* @param id
* @return
*/
User findById(String id);
/**
* 通过登录名和密码查询用户
* @param name
* @param password
* @return
*/
User findByNameAndPass(String name, String password);
/**
* 添加用户
* @param user
* @return
*/
boolean addUser(User user);
/**
* 修改用户信息
* @param user
* @return
*/
boolean editUser(User user);
/**
* 删除用户
* @param id
* @return
*/
boolean deleteUser(String id);
}
View Code
userDaoImpl接口实现类
package shiyan6.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import shiyan6.entity.User;
import shiyan6.util.JdbcUtil;
/**
* UserDao的实现类
*
*/
public class UserDaoImpl implements UserDao {
// 获取数据库连接
private Connection connection = null;
// prestatement用来执行动态sql语句,比statement要好
private PreparedStatement pst = null;
// ResultSet 用来存放结果
private ResultSet rs = null;
@Override
public List<User> findAll() {
// sql语句
String sql = "SELECT * FROM user WHERE role = 1 ORDER BY orderby";
// 用来存储结果
List<User> users = new ArrayList<>();
try {
connection = JdbcUtil.getConn();
pst = connection.prepareStatement(sql);
rs = pst.executeQuery(); // 执行sql
// 把查询到信息给封装到User实体类中,再放到list中
while (rs.next()) {
User user = new User();
user.setId(rs.getString("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setRole(rs.getInt("role"));
users.add(user);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭连接
try {
rs.close();
pst.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return users;
}
public int findCountByName(String name) {
int count = 0;
// sql语句,?号相当于占位符
String sql = "SELECT COUNT(*) as count FROM user WHERE name = ?";
// 存放结果
try {
connection = JdbcUtil.getConn();
pst = connection.prepareStatement(sql);
pst.setString(1, name); // 填充参数
rs = pst.executeQuery();
if (rs.next()) {
count = rs.getInt("count");
System.out.println("count" + count);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭连接
try {
rs.close();
pst.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return count;
}
@Override
public User findById(String id) {
// sql语句,?号相当于占位符
String sql = "SELECT * FROM user WHERE id = ?";
// 存放结果
User user = null;
try {
connection = JdbcUtil.getConn();
pst = connection.prepareStatement(sql);
pst.setString(1, id); // 填充参数
rs = pst.executeQuery();
if (rs.next()) {
// 初始化User对象
user = new User();
user.setId(rs.getString("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setRole(rs.getInt("role"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭连接
try {
rs.close();
pst.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return user;
}
@Override
public User findByNameAndPass(String name, String password) {
// sql语句,?号相当于占位符
String sql = "SELECT * FROM user WHERE name = ? AND password = ?";
// 存放结果
User user = null;
try {
connection = JdbcUtil.getConn();
pst = connection.prepareStatement(sql);
pst.setString(1, name); // 填充参数
pst.setString(2, password);
rs = pst.executeQuery();
if (rs.next()) {
// 初始化User对象
user = new User();
user.setId(rs.getString("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setRole(rs.getInt("role"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭连接
try {
rs.close();
pst.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return user;
}
@Override
public boolean addUser(User user) {
// sql语句,?号相当于占位符
String sql = "INSERT INTO user(id, name, password, role) VALUES(?,?,?,?)";
// 是否成功
boolean flag = false;
try {
connection = JdbcUtil.getConn();
pst = connection.prepareStatement(sql);
pst.setString(1, user.getId()); // 填充参数
pst.setString(2, user.getName());
pst.setString(3, user.getPassword());
pst.setInt(4, user.getRole());
if (pst.executeUpdate() == 1) {
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭连接
try {
pst.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return flag;
}
@Override
public boolean editUser(User user) {
// sql语句,?号相当于占位符
String sql = "UPDATE user SET name=?, password=?, role=? " + "WHERE id=? ";
// 是否成功
boolean flag = false;
try {
connection = JdbcUtil.getConn();
pst = connection.prepareStatement(sql);
pst.setString(1, user.getName());
pst.setString(2, user.getPassword());
pst.setInt(3, user.getRole());
pst.setString(4, user.getId()); // 填充参数
if (pst.executeUpdate() == 1) {
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭连接
try {
pst.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return flag;
}
@Override
public boolean deleteUser(String id) {
// sql语句,?号相当于占位符
String sql = "DELETE FROM user WHERE id=?";
// 是否成功
boolean flag = false;
try {
connection = JdbcUtil.getConn();
pst = connection.prepareStatement(sql);
pst.setString(1, id);
if (pst.executeUpdate() == 1) {
flag = true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭连接
try {
pst.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return flag;
}
@Override
public List<User> findByName(String name) {
// sql语句
String sql = "SELECT * FROM user WHERE role = 1 AND name LIKE concat('%',?,'%') ORDER BY orderby";
// 用来存放结果
List<User> users = new ArrayList<>();
try {
connection = JdbcUtil.getConn();
pst = connection.prepareStatement(sql);
// 添加参数的值
pst.setString(1, name);
rs = pst.executeQuery();
while (rs.next()) {
User user = new User();
user.setId(rs.getString("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setRole(rs.getInt("role"));
users.add(user);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
pst.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return users;
}
}
View Code
2. 对歌曲的操作
SongDao 接口
package shiyan6.dao;
import java.util.List;
import shiyan6.entity.Song;
/**
* 对歌曲信息进行
*
*/
public interface SongDao {
/**
* 显示所有歌曲
* @return
*/
List<Song> findAll();
/**
* 通过id查找信息
* @return
*/
Song findById(String id);
/**
* 按条件歌曲名查询歌曲
* @param condition
* @return
*/
List<Song> findByName(String name);
/**
* 按语言查询歌曲
* @param language
* @return
*/
List<Song> findBylanguage(String language);
/**
* 根据歌手来查询歌曲
* @param singer
* @return
*/
List<Song> findBySinger(String singer);
/**
* 格局歌曲类别来查询歌曲
* @param category
* @return
*/
List<Song> findByCategory(String category);
/**
* 删除歌曲
* @param id
* @return
*/
boolean deletSong(String id);
/**
* 添加歌曲
* @param song
* @return
*/
boolean addSong(Song song);
/**
* 修改歌曲
* @param song
* @return
*/
boolean updateSong(Song song);
}
View Code
SongDaoImpl实现类
package shiyan6.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import shiyan6.entity.Song;
import shiyan6.util.JdbcUtil;
public class SongDaoImpl implements SongDao {
// 获取数据库连接
private Connection connection = null;
// prestatement用来执行动态sql语句,比statement要好
private PreparedStatement pst = null;
// ResultSet 用来存放结果
private ResultSet rs = null;
@Override
public List<Song> findAll() {
// sql语句
String sql = "SELECT * FROM song ORDER BY orderby";
// 用来存放结果
List<Song> songs = new ArrayList<>();
try {
connection = JdbcUtil.getConn();
pst = connection.prepareStatement(sql);
rs = pst.executeQuery();
while (rs.next()) {
Song song = new Song();
song.setId(rs.getString("id"));
song.setName(rs.getString("name"));
song.setLanguage(rs.getString("language"));
song.setCategory(rs.getString("category"));
song.setSinger(rs.getString("singer"));
songs.add(song);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
pst.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return songs;
}
public Song findById(String id) {
// sql语句
String sql = "SELECT * FROM song WHERE id = ? ORDER BY orderby";
// 用来存放结果
try {
connection = JdbcUtil.getConn();
pst = connection.prepareStatement(sql);
// 添加参数的值
pst.setString(1, id);
rs = pst.executeQuery();
if (rs.next()) {
Song song = new Song();
song.setId(rs.getString("id"));
song.setName(rs.getString("name"));
song.setLanguage(rs.getString("language"));
song.setCategory(rs.getString("category"));
song.setSinger(rs.getString("singer"));
return song;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
pst.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}
@Override
public List<Song> findByName(String name) {
// sql语句
String sql = "SELECT * FROM song WHERE name LIKE concat('%',?,'%') ORDER BY orderby";
// 用来存放结果
List<Song> songs = new ArrayList<>();
try {
connection = JdbcUtil.getConn();
pst = connection.prepareStatement(sql);
// 添加参数的值
pst.setString(1, name);
rs = pst.executeQuery();
while (rs.next()) {
Song song = new Song();
song.setId(rs.getString("id"));
song.setName(rs.getString("name"));
song.setLanguage(rs.getString("language"));
song.setCategory(rs.getString("category"));
song.setSinger(rs.getString("singer"));
songs.add(song);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
pst.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return songs;
}
@Override
public List<Song> findBylanguage(String language) {
// sql语句
String sql = "SELECT * FROM song WHERE language LIKE concat('%',?,'%') ORDER BY orderby";
// 用来存放结果
List<Song> songs = new ArrayList<>();
try {
connection = JdbcUtil.getConn();
pst = connection.prepareStatement(sql);
// 添加参数的值
pst.setString(1, language);
rs = pst.executeQuery();
while (rs.next()) {
Song song = new Song();
song.setId(rs.getString("id"));
song.setName(rs.getString("name"));
song.setLanguage(rs.getString("language"));
song.setCategory(rs.getString("category"));
song.setSinger(rs.getString("singer"));
songs.add(song);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
pst.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return songs;
}
@Override
public List<Song> findBySinger(String singer) {
// sql语句
String sql = "SELECT * FROM song WHERE singer LIKE concat('%',?,'%') ORDER BY orderby";
// 用来存放结果
List<Song> songs = new ArrayList<>();
try {
connection = JdbcUtil.getConn();
pst = connection.prepareStatement(sql);
// 添加参数的值
pst.setString(1, singer);
rs = pst.executeQuery();
while (rs.next()) {
Song song = new Song();
song.setId(rs.getString("id"));
song.setName(rs.getString("name"));
song.setLanguage(rs.getString("language"));
song.setCategory(rs.getString("category"));
song.setSinger(rs.getString("singer"));
songs.add(song);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
pst.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return songs;
}
@Override
public List<Song> findByCategory(String category) {
// sql语句
String sql = "SELECT * FROM song WHERE category LIKE concat('%',?,'%') ORDER BY orderby";
// 用来存放结果
List<Song> songs = new ArrayList<>();
try {
connection = JdbcUtil.getConn();
pst = connection.prepareStatement(sql);
// 添加参数的值
pst.setString(1, category);
rs = pst.executeQuery();
while (rs.next()) {
Song song = new Song();
song.setId(rs.getString("id"));
song.setName(rs.getString("name"));
song.setLanguage(rs.getString("language"));
song.setCategory(rs.getString("category"));
song.setSinger(rs.getString("singer"));
songs.add(song);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
pst.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return songs;
}
@Override
public boolean deletSong(String id) {
int flag = 0;
// sql语句
String sql = "DELETE FROM song WHERE id = ?";
try {
connection = JdbcUtil.getConn();
pst = connection.prepareStatement(sql);
// 添加参数的值
pst.setString(1, id);
flag = pst.executeUpdate();
if (flag == 1) {
return true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
pst.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return false;
}
@Override
public boolean addSong(Song song) {
int flag = 0;
// sql语句
String sql = "INSERT INTO song(id,name,language,category,singer) VALUES(?,?,?,?,?)";
try {
connection = JdbcUtil.getConn();
pst = connection.prepareStatement(sql);
pst.setString(1, song.getId());
pst.setString(2, song.getName());
pst.setString(3, song.getLanguage());
pst.setString(4, song.getCategory());
pst.setString(5, song.getSinger());
// 添加参数的值
flag = pst.executeUpdate();
if (flag == 1) {
return true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
pst.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return false;
}
@Override
public boolean updateSong(Song song) {
int flag = 0;
// sql语句
String sql = "UPDATE song SET name=?,language=?,category=?,singer=? WHERE id=?";
try {
connection = JdbcUtil.getConn();
pst = connection.prepareStatement(sql);
pst.setString(1, song.getName());
pst.setString(2, song.getLanguage());
pst.setString(3, song.getCategory());
pst.setString(4, song.getSinger());
pst.setString(5, song.getId());
// 添加参数的值
flag = pst.executeUpdate();
if (flag == 1) {
return true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
pst.close();
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return false;
}
}
View Code
UserDaoTest.java
package shiyan6.test;
import java.util.List;
import org.junit.Assert;
import org.junit.Test;
import shiyan6.dao.UserDao;
import shiyan6.dao.UserDaoImpl;
import shiyan6.entity.User;
/**
* UserDao 层测试类
*
*/
public class UserDaoTest {
private UserDao userDao = new UserDaoImpl();
@Test
public void testFindAll() {
List<User> users = userDao.findAll();
Assert.assertNotNull(users);
System.out.println(users);
}
@Test
public void testFindById() {
User user = userDao.findById("1");
Assert.assertNotNull(user);
System.out.println(user);
}
@Test
public void testFingByNameAndPass() {
User user = userDao.findByNameAndPass("admin", "123");
Assert.assertNotNull(user);
System.out.println(user);
}
@Test
public void testAddUser() {
User user = new User("3","testadd","test",2);
System.out.println(userDao.addUser(user));
}
@Test
public void testEditUser() {
User user = new User("3","testadd","testedit",2);
System.out.println(userDao.editUser(user));
}
@Test
public void testDeleteUser() {
System.out.println(userDao.deleteUser("4"));
}
}
View Code
SongDaoTest.java
package shiyan6.test;
import org.junit.Test;
import shiyan6.dao.SongDao;
import shiyan6.dao.SongDaoImpl;
import shiyan6.entity.Song;
import shiyan6.util.Common;
/**
* Dao 层测试类
*
*/
public class SongDaoTest {
private SongDao songDao = new SongDaoImpl();
@Test
public void testFindAll() {
System.out.println(songDao.findAll());
}
@Test
public void testFindByName() {
System.out.println(songDao.findByName("test"));
}
@Test
public void testFindBylanguage() {
System.out.println(songDao.findBylanguage("中"));
}
@Test
public void testFindBySinger() {
System.out.println(songDao.findBySinger("test"));
}
@Test
public void testFindByCategory() {
System.out.println(songDao.findByCategory("流行"));
}
@Test
public void testDeletSong() {
System.out.println(songDao.deletSong("213"));
}
@Test
public void testAddSong() {
Song song = new Song(Common.getUuid(), "testadd", "英文", "乡村", "test");
System.out.println(songDao.addSong(song));
}
}
View Code
- 之前提到的主键是使用UUID生成的,所以我们的util包下,还有一个Common.java类,具体代码如下:
package shiyan6.util;
import java.util.UUID;
/**
* 用于产生主键(使用uuid)
*
*/
public class Common {
public static String getUuid() {
return UUID.randomUUID().toString().replaceAll("-", "").substring(0, 8);
}
}
View Code
推荐一个好的论坛--科帮网,里面有很多的源码、干货帖子和大神。
项目源码