《音乐网站管理系统.doc》由会员分享,可在线阅读,更多相关《音乐网站管理系统.doc(15页珍藏版)》请在三一文库上搜索。
1、表1-1用户信息表(siteUser)字段名称说明数据类型字段长度是否允许空约束userID编号Nvarchar24否主键userName姓名Nvarchar28否password密码Nvarchar28是isAdmin是否管理员number1否取值是0或1,默认0表1-2专辑表(Album)字段名称说明数据类型字段长度是否允许空约束albumID专辑编号Nvarchar24否主键albumName专辑名称Nvarchar220否albumIntro专辑简介Nvarchar22000是issueDate发行时间date是 singerID歌手编号Nvarchar24否languageID语言编
2、号Nvarchar24否classifyID分类编号Nvarchar24否表1-3歌手表(Singer)字段名称说明数据类型字段长度是否允许空约束singerID歌手编号Nvarchar24否主键singerName姓名Nvarchar220否isGroup是否组合number1否取值是0或1,默认0sex性别number1是检查约束(男,女)表1-4语言表(Language)字段名称说明数据类型字段长度是否允许空约束languageID语言编号Nvarchar24否主键languageName语言名称Nvarchar220否表1-5专辑分类表(albumClassify)字段名称说明数据类型
3、字段长度是否允许空约束classifyID分类编号Nvarchar24否主键classifyName分类名称Nvarchar220否表1-6歌曲表(Song)字段名称说明数据类型字段长度是否允许空约束songID歌曲编号Nvarchar24否主键songName歌曲名称Nvarchar220否singerID歌手编号Nvarchar24否votes人气值number1否默认值0songFile歌曲文件blob否1. 创建数据库表 1) 创建siteUser(用户信息表) Create table siteUser(userID Nvarchar2(4) constraint pk_siteUs
4、er primary key,userName Nvarchar2(20) not null, Password Nvarchar2(8), isAdmin number(1) default 0 check(isAdmin in(0,1);2)创建Album(专辑表)Create table Album(albumID Nvarchar2(4) constraint pk_Album primary key,albumName Nvarchar2(20) not null,albumIntro Nvarchar2(2000) ,issueDate date,singerID Nvarchar
5、2(4) not null,languageID Nvarchar2(4) not null,classifyID Nvarchar2(4) not null);3)创建表Singer(歌手表)Create table Singer(singerID Nvarchar2(4) constraint pk_Singer primary key,singerName Nvarchar2(20) not null, isGroup number(1) default 0 check(isGroup in(0,1) sex nchar(1);4)创建Language表(语言表)Create table
6、 Language(languageID Nvarchar2(4) constraint pk_Language primary key,languageName Nvarchar2(20) not null);5) 创建albumClassify表(专辑分类表)Create table albumClassify(classifyID Nvarchar2(4) constraint pk_albumClassify primary key,classifyName Nvarchar2(20) not null);6)创建表Song(歌曲表)Create table Song(songID N
7、varchar2(4) constraint pk_Song primary key,songName Nvarchar2(20) not null,singerID Nvarchar2(4) not null,votes number(1) default 0,song);2. 创建约束1)创建表singer的检查约束signer_chk1:Alter table signer add constraint singer_chk1 check(sex in(男,女);2) 创建表album的外键约束Abbum_singer_fk1:Alter table album add constrai
8、nt Abbum_signer_fk1 foreign key(singerID) reference signer(singerID);3) 创建表album的外键约束Abbum_language_fk1:Alter table album add constraint Abbum_language_fk1 foreign key(languageID) references language(languageID);4)创建表album的外键约束Abbum_classify_fk1:Alter table album add constraint Abbum_classify_fk1 fo
9、reign key(classifyID) references Ablumclassify(classifyID);5) 创建表Song的外键约束Song_Singer_fk1:Alter table Song add constraint Song_Singer_fk1 foreign key(singerID) references Singer(singerID);3. 创建视图1) 创建视图V_album用以显示专辑信息Create or replace view v_albumAsSelect t1.albumID,t1.albumName,t1.albumIntro,t1.iss
10、ueDate,t4.singerName,t3.languageName,t2.classifyNameFrom album t1 inner jion albumclassify t2 on t1.classifyID=t2.classifyIDInner jion language t3 on t1.languageID=t3.languageIDINNER JOIN singer t4 on t1.singerID=t4.singerID;2) 显示歌曲信息Create or replace view v_songAsSelect t1.songID,t1.songName,t2.sin
11、gerName,t1.votes,t1.song song t1 inner join singer t2 on t1.singerID=t2.singerID;4. 创建存储过程1) 向专辑表(album)中插入数据的存储过程Create or replace procedure sp_insertAlbum(AlbumID NVARCHAR2,albumName NVARCHAR2,albumIntro NVARCHAR2,isssueDate DATE,SingerID NVARCHAR2,languageID NVARCHAR2,classifyID NVARCHAR2)ASBEGIN
12、 Insert into album values(AlbumID,albumName,albumIntro,issueDate,singerID,languageID,classifyID);END sp_insertAlbum;2) 修改专辑表数据的存储过程Create or replace procedure sp_updateAlbum(fAlbumID NVARCHAR2,falbumName NVARCHAR2,falbumIntro NVARCHAR2,fisssueDate DATE,fSingerID NVARCHAR2,flanguageID NVARCHAR2,fclas
13、sifyID NVARCHAR2)ASBEGIN Update album sett AlbumID=fAlbumID,albumName=falbumName ,albumIntro=falbumIntro,issueDate=fisssueDate,singerID=fSingerID,LanguageID=flanguageID,ClassifyIDfclassifyID;END sp_updateAlbum;3)创建删除专辑表数据的存储过程Create or replace procedure sp_deleteAlbum(fAlbumID NVARCHAR2)AsBegin Dele
14、te from album where albumID=falbumID;END sp_deletefalbum;ConnectionFactory类:Package cn.edu.hbsi.db;Import java.io.IOException;Import java.io.InputStream;Import java.sql.Connection;Import java.sql.DriverManager;Import java.sql.SQLException;Import java.util.Properties;/获得数据库连接Public class ConnectionFa
15、ctory/数据库驱动类名Private static String DRIVER=” ”;/连接数据库的URLPrivate static String URL=” ”;/连接数据库的用户名Private static String USERNAME=” ”;/连接数据库的密码Private static String PASSWORD=” ”;Private ConnectionFactory Static getPropertiesInfo(); /该方法用来获得属性文件中的driver,url,username,passwordPrivate static getPropertiesI
16、nfo() Properties prop=new Properties();InputStream inStream=Thread.currentThread().getContextClassLoader().getResourseAsStream(“jdbcCon.properties”);Try /获得相应的键值对 Prop.load(inStream);Catch(IOException e) e.printStaticTrace();/根据相应的键获得对应的值DRIVER=prop.getProperty(“driver”);URL=prop.getProperty(“url”);
17、USERNAME=prop.getProperty(“username”);PASSWORD=prop.getProperty(“password”);/该方法用来加载驱动,并会的数据库的连接对象 return数据库连接对象connPublic static Connection getConnection() Connection conn=null; Try /加载驱动程序 Class.forName(DRIVER); catch(ClassNotFoundExcepyion e) e.printStaticTrace(); Try /获得数据库连接的对象 Conn=DriverManag
18、er.getConnection(URL.USERNAME,PASSWORD); Catch(SQLException e) e.printStaticTrace(); Return conn; DbClose类:Pacage cn.edu.hbsi.db;Import java.sql.Connection;Import java.sql.Result;Import java.sql.SQLExcwption;Import java.sql.Statement;Public class DbClosePublic static void close(Connection conn) If(n
19、ull!=conn) Try Conn.close(); Catch(SQLException e) e.printStaticTrace(); Public static void close(Statement stmt) If(null!=stmt) Try stmt.close(); Catch(SQLException e) e.printStaticTrace(); Public static void close(ResultSet rs) If(null!=rs) Try rs.close(); Catch(SQLException e) e.printStaticTrace(
20、); Public static void close (ResultSet rs,Statement stmt,Connection conn) Close(rs); Close(stmt,conn);Public static void close (Statement stmt,Connection conn) Close(stmt); Close(conn);核心模块的实现:1) 在Calbum类中编写insertAlbum方法,调用ORACLE数据库中的存储过程SP_INSERTALBUM实现插入数据操作:Public void insertAlbum(String ALBUMID,
21、String ALBUMNAME,String ALBUMINTRO,DATE ISSUEDATE,String SINGERID) Connection con=null; CallableStatement cs=null; ResultSet rs=null; Try Con=ConnectionFactory.getConnection(); String sql=”call SP_INSERTALBUM(?,?,?,?,?,?,?)”; cs=con.prepareCall(aql); cs.setString(1,ALBUMID); cs.setString(2,ALBUMNAME); cs.setString(3,ALBUMINTRO); cs.setString(4,ISSUEDATE); cs.setString(5,SINGERID); cs.setString(6,LANGUAGEID); cs.setString(7,CLASSIFYID); Cs.execute(); Catch(Excepyion e) e.printStaticTrace(); Finally Dbclose.close(rs,cs,conn); 15 / 15文档可自由编辑
链接地址:https://www.31doc.com/p-8856400.html