// 在eclipse里分别创建歌手类,专辑类和歌曲类
// 歌手类
/**
*
*/
package com.yidu.model;
import java.sql.Date;
/**
- 描述:音乐类
- @author zhouyuxian
- @date 2019年8月23日 下午4:02:02
- @version 1.0
*/
public class Singer {
private Integer sgID;
private String sgName;
private Date sgDate;
private String sgArea;
public Integer getSgId() {
return sgID;
}
public void setSgId(Integer sgId) {
this.sgID = sgId;
}
public String getSgName() {
return sgName;
}
public void setSgName(String sgName) {
this.sgName = sgName;
}
public Date getSgDate() {
return sgDate;
}
public void setSgDate(Date sgDate) {
this.sgDate = sgDate;
}
public String getSgArea() {
return sgArea;
}
public void setSgArea(String sgArea) {
this.sgArea = sgArea;
}
}
// 专辑类
/**
*
*/
package com.yidu.model;
/**
- 描述:专辑类
- @author zhouyuxian
- @date 2019年8月23日 下午4:05:43
- @version 1.0
*/
public class Album {
private Integer alID;
private String alTitle;
private Integer sgID;
private Integer alMoney;
private String alDate;
private String alCompany;
private Integer alRanking;
public Integer getAlID() {
return alID;
}
public void setAlID(Integer alID) {
this.alID = alID;
}
public String getAlTitle() {
return alTitle;
}
public void setAlTitle(String alTitle) {
this.alTitle = alTitle;
}
public Integer getSgID() {
return sgID;
}
public void setSgID(Integer sgID) {
this.sgID = sgID;
}
public Integer getAlMoney() {
return alMoney;
}
public void setAlMoney(Integer alMoney) {
this.alMoney = alMoney;
}
public String getAlDate() {
return alDate;
}
public void setAlDate(String alDate) {
this.alDate = alDate;
}
public String getAlCompany() {
return alCompany;
}
public void setAlCompany(String alCompany) {
this.alCompany = alCompany;
}
public Integer getAlRanking() {
return alRanking;
}
public void setAlRanking(Integer alRanking) {
this.alRanking = alRanking;
}
}
// 歌曲类
/**
*
*/
package com.yidu.model;
/**
- 描述:曲目类
- @author zhouyuxian
- @date 2019年8月23日 下午4:11:45
- @version 1.0
*/
public class Tracks {
private Integer tkID;
private Integer alID;
private Integer tkSort;
private String tkName;
public Integer getTkID() {
return tkID;
}
public void setTkID(Integer tkID) {
this.tkID = tkID;
}
public Integer getAlID() {
return alID;
}
public void setAlID(Integer alID) {
this.alID = alID;
}
public Integer getTkSort() {
return tkSort;
}
public void setTkSort(Integer tkSort) {
this.tkSort = tkSort;
}
public String getTkName() {
return tkName;
}
public void setTkName(String tkName) {
this.tkName = tkName;
}
}
//分别创建歌手Dao,专辑Dao和歌曲Dao
// 歌手Dao
/**
*
*/
package com.yidu.dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.yidu.model.Singer;
import com.yidu.utils.JDBCTools;
/**
-
描述:歌手Dao
-
@author zhouyuxian
-
@date 2019年8月23日 下午4:14:34
-
@version 1.0
*/
public class SingerDao {/**
- 添加歌手
- @param singer
- @return
*/
public int insert(Singer singer){
int rows = 0;
Connection con = null;
PreparedStatement pstm = null;
try {
con = JDBCTools.getCon();
String sql = “insert into singer values(null,?,?,?)”;
pstm = con.prepareStatement(sql);
pstm.setString(1, singer.getSgName());
pstm.setDate(2, singer.getSgDate());
pstm.setString(3, singer.getSgArea());
rows = pstm.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCTools.close(con, pstm);
}
return rows;
}
/**
- 根据ID删除歌手
- @param singerID
- @return
*/
public int delete(int singerID){
int rows = 0;
Connection con = null;
PreparedStatement pstm = null;
try {
con = JDBCTools.getCon();
String sql = “delete from singer where s_id=?”;
pstm = con.prepareStatement(sql);
pstm.setInt(1, singerID);
rows = pstm.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCTools.close(con, pstm);
}
return rows;
}
/**
-
修改歌手信息
-
@param singer
-
@return
*/
public int update(Singer singer){
int rows = 0;
Connection con = null;
PreparedStatement pstm = null;
try {
con = JDBCTools.getCon();
String sql = “update singer set s_name=?,s_birthday=?,s_area=? where s_id=?”;
pstm = con.prepareStatement(sql);
pstm.setString(1, singer.getSgName());
pstm.setDate(2, singer.getSgDate());
pstm.setString(3, singer.getSgArea());
pstm.setInt(4, singer.getSgId());
rows = pstm.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCTools.close(con, pstm);
}return rows;
}
/**
- 查询全部歌手
- @return
*/
public List selectAll(){
List list = new ArrayList();
Connection con = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
con = JDBCTools.getCon();
String sql = “select * from singer”;
pstm = con.prepareStatement(sql);
rs = pstm.executeQuery();
while(rs.next()){
Singer singer = new Singer();
singer.setSgId(rs.getInt(“s_id”));
singer.setSgName(rs.getString(“s_name”));
singer.setSgDate(rs.getDate(“s_birthday”));
singer.setSgArea(rs.getString(“s_area”));
list.add(singer);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCTools.close(con, pstm, rs);
}
return list;
}
/**
- 根据ID查询
- @param singerID
- @return
*/
public Singer selectID(int singerID){
Connection con = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
con = JDBCTools.getCon();
String sql = “select * from singer where s_name=?”;
pstm = con.prepareStatement(sql);
pstm.setInt(1, singerID);
rs = pstm.executeQuery();
while(rs.next()){
Singer singer = new Singer();
singer.setSgId(rs.getInt(“s_id”));
singer.setSgName(rs.getString(“s_name”));
singer.setSgDate(rs.getDate(“s_date”));
singer.setSgArea(rs.getString(“s_area”));
return singer;
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCTools.close(con, pstm, rs);
}
return null;
}
}
// 专辑Dao
/**
*
*/
package com.yidu.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 com.yidu.model.Album;
import com.yidu.utils.JDBCTools;
/**
-
描述:专辑Dao
-
@author zhouyuxian
-
@date 2019年8月23日 下午4:57:40
-
@version 1.0
*/
public class AlbumDao {/**
-
添加专辑
-
@param album
-
@return
*/
public int insert(Album album){
int rows = 0;
Connection con = null;
PreparedStatement pstm = null;
try {
con = JDBCTools.getCon();
String sql = “insert into album values(null,?,?,?,?,?,?)”;
pstm = con.prepareStatement(sql);
pstm.setString(1, album.getAlTitle());
pstm.setInt(2, album.getSgID());
pstm.setInt(3, album.getAlMoney());
pstm.setString(4, album.getAlDate());
pstm.setString(5, album.getAlCompany());
pstm.setInt(6, album.getAlRanking());
rows = pstm.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCTools.close(con, pstm);
}return rows;
}
/**
-
根据ID删除专辑
-
@param alID
-
@return
*/
public int delete(int alID){
int rows = 0;
Connection con = null;
PreparedStatement pstm = null;
try {
con = JDBCTools.getCon();
String sql = “delete form album where a_id=?”;
pstm = con.prepareStatement(sql);
pstm.setInt(1, alID);
rows = pstm.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCTools.close(con, pstm);
}return rows;
}
/**
- 修改信息
- @param album
- @return
*/
public int update(Album album){
int rows = 0;
Connection con = null;
PreparedStatement pstm = null;
try {
con = JDBCTools.getCon();
String sql = “update album set a_title=?,s_id=?,a_money,a_company,a_ranking”
+ “where a_id=?”;
pstm = con.prepareStatement(sql);
pstm.setString(1, album.getAlTitle());
pstm.setInt(2, album.getSgID());
pstm.setInt(3, album.getAlMoney());
pstm.setString(5, album.getAlCompany());
pstm.setInt(6, album.getAlRanking());
pstm.setInt(7, album.getAlID());
rows = pstm.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCTools.close(con, pstm);
}
return rows;
}
/**
- 查询所有专辑
- @return
*/
public List selectAll(){
List list = new ArrayList();
Connection con = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
con = JDBCTools.getCon();
String sql = “select * from album”;
pstm = con.prepareStatement(sql);
rs = pstm.executeQuery();
while(rs.next()){
Album al = new Album();
al.setAlID(rs.getInt(“a_id”));
al.setAlTitle(rs.getString(“a_title”));
al.setSgID(rs.getInt(“s_id”));
al.setAlMoney(rs.getInt(“a_money”));
al.setAlDate(rs.getString(“a_date”));
al.setAlCompany(rs.getString(“a_company”));
al.setAlRanking(rs.getInt(“a_ranking”));
list.add(al);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCTools.close(con, pstm, rs);
}
return list;
}
/**
- 根据ID查询
- @param albumID
- @return
*/
public Album selectAlID(int albumID){
Connection con = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
con = JDBCTools.getCon();
String sql = “select * from album where a_id=?”;
pstm = con.prepareStatement(sql);
pstm.setInt(1, albumID);
rs = pstm.executeQuery();
while(rs.next()){
Album al = new Album();
al.setAlID(rs.getInt(“a_id”));
al.setAlTitle(rs.getString(“a_title”));
al.setSgID(rs.getInt(“s_id”));
al.setAlMoney(rs.getInt(“a_money”));
al.setAlDate(rs.getString(“a_date”));
al.setAlCompany(rs.getString(“a_company”));
al.setAlRanking(rs.getInt(“a_ranking”));
return al;
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCTools.close(con, pstm, rs);
}
return null;
}
}
-
// 歌曲Dao
/**
*
*/
package com.yidu.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 com.yidu.model.Tracks;
import com.yidu.utils.JDBCTools;
/**
-
描述:曲目Dao
-
@author zhouyuxian
-
@date 2019年8月23日 下午5:44:30
-
@version 1.0
/
public class TracksDao {
/*-
添加曲目
-
@param album
-
@return
*/
public int insert(Tracks tracks){
int rows = 0;
Connection con = null;
PreparedStatement pstm = null;
try {
con = JDBCTools.getCon();
String sql = “insert into album values(null,?,?,?)”;
pstm = con.prepareStatement(sql);
pstm.setInt(1, tracks.getAlID());
pstm.setInt(2, tracks.getTkSort());
pstm.setString(3, tracks.getTkName());
rows = pstm.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCTools.close(con, pstm);
}return rows;
}
/**
-
根据ID删除专辑
-
@param alID
-
@return
*/
public int delete(int tkID){
int rows = 0;
Connection con = null;
PreparedStatement pstm = null;
try {
con = JDBCTools.getCon();
String sql = “delete form tracks where t_id=?”;
pstm = con.prepareStatement(sql);
pstm.setInt(1, tkID);
rows = pstm.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCTools.close(con, pstm);
}return rows;
}
/**
- 修改信息
- @param album
- @return
*/
public int update(Tracks tracks){
int rows = 0;
Connection con = null;
PreparedStatement pstm = null;
try {
con = JDBCTools.getCon();
String sql = “update tracks set t_id=?,a_id=?,t_sort=?,t_name=?”;
pstm = con.prepareStatement(sql);
pstm.setInt(1, tracks.getTkID());
pstm.setInt(2, tracks.getAlID());
pstm.setInt(3, tracks.getTkSort());
pstm.setString(4, tracks.getTkName());
rows = pstm.executeUpdate();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCTools.close(con, pstm);
}
return rows;
}
/**
- 查询所有专辑
- @return
*/
public List selectAll(){
List list = new ArrayList();
Connection con = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
con = JDBCTools.getCon();
String sql = “select * from tracks”;
pstm = con.prepareStatement(sql);
rs = pstm.executeQuery();
while(rs.next()){
Tracks tk = new Tracks();
tk.setTkID(rs.getInt(“t_id”));
tk.setAlID(rs.getInt(“a_id”));
tk.setTkSort(rs.getInt(“t_sort”));
tk.setTkName(rs.getString(“t_name”));
list.add(tk);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCTools.close(con, pstm, rs);
}
return list;
}
/**
- 根据ID查询
- @param albumID
- @return
*/
public Tracks selectName(String tkName){
Connection con = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
con = JDBCTools.getCon();
String sql = “select * from album where t_name=?”;
pstm = con.prepareStatement(sql);
pstm.setString(1, tkName);
rs = pstm.executeQuery();
while(rs.next()){
Tracks tk = new Tracks();
tk.setTkID(rs.getInt(“t_id”));
tk.setAlID(rs.getInt(“a_id”));
tk.setTkSort(rs.getInt(“t_sort”));
tk.setTkName(rs.getString(“t_name”));
return tk;
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCTools.close(con, pstm, rs);
}
return null;
}
}
-