原创不易，转载请标明出处。上一篇《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],
........
);
- 实体类（entity包下）设计
- 用户实体类：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方法 .......... }
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方法 ............. }
- 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; } }
使用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); } }
看到如下效果，则说明测试通过，同时控制台打印消息。
- Dao层，对数据库的操作
涉及到的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); }
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; } }
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); }
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; } }
- 对Dao层进行测试
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")); } }
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)); } }
- 之前提到的主键是使用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); } }
推荐一个好的论坛--科帮网，里面有很多的源码、干货帖子和大神。