音乐网站管理系统

1、精品文档表1-1用户信息表（siteUser ）字段名称说明数据类型字段长 度是否允许空约束userID编号Nvarchar24否主键userName姓名Nvarchar28:否password密码Nvarchar28是isAdmin是否管理 员nu mber1否取值是0或1,默认0表1-2专辑表（Album）字段名称说明数据类型字段长度是否允许空约束albumID专辑编号Nvarchar24:否主键albumName专辑名称Nvarchar220否albumI ntro专辑简介Nvarchar22000是issueDate发行时间date是sin gerlD歌手编号Nvarchar24否Ia

2、n guageID语言编号Nvarchar24:否classifyID分类编号Nvarchar24否表1-3歌手表（Sin ger）字段名称说明数据类型字段长 度是否允许空约束sin gerID歌手编号Nvarchar24否主键sin gerName姓名Nvarchar220否isGroup是否组合nu mber1否取值是0或1，默认0sex性别nu mber1是检查约束（男，女）表 1-4 语言表（Language）字段名称说明数据类型字段长 度是否允许空约束Ian guageID语言编号Nvarchar24否主键Ian guageName语言名称Nvarchar220否表1-5专辑分类表（

3、albumClassify ）字段名称说明数据类型字段长 度是否允许空约束classifyID分类编号Nvarchar24否主键classifyName分类名称Nvarchar220否表1-6歌曲表（Song）字段名称说明数据类型字段长 度是否允许空约束songID歌曲编号Nvarchar24否主键son gName歌曲名称Nvarchar220否sin gerID歌手编号Nvarchar24否votes人气值nu mber1否默认值09欢迎下载son gFile歌曲文件blob否1. 创建数据库表1) 创建siteUser(用户信息表)Create table siteUser(userID

4、 Nvarchar2(4) con stra int pk_siteUser 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) con stra int pk_Album primary key, albumName Nvarchar2(20) n ot null,albumI ntro Nvarc

5、har2(2000),issueDate date,singerID Nvarchar2(4) not null,Ian guageID Nvarchar2(4) not null,classifyID Nvarchar2 not n ull);3) 创建表Sin ger (歌手表)Create table Si nger(sin gerID Nvarchar2(4) con stra int pk_S in ger primary key,sin gerName Nvarchar2(20) n ot null,isGroup number(1) default 0 check(isGroup

6、 in (0,1) sex n char(1);4) 创建Language表(语言表)Create table Lan guage(Ian guageID Nvarchar2(4) con stra int pk_La nguage primary key,Ian guageName Nvarchar2(20) not n ull);5) 创建albumClassify 表(专辑分类表)Create table albumClassify(classifyID Nvarchar2(4) con stra int pk_albumClassify primary key, classifyNam

7、e Nvarchar2(20) not n ull);6) 创建表So ng(歌曲表)Create table Song(songID Nvarchar2(4) con stra int pk_S ong primary key,son gName Nvarchar2(20) not n ull,singerID Nvarchar2(4) not null,votes nu mber(1) default 0,son gFile blob);2. 创建约束1) 创建表sin ger的检查约束sig ner_chk1:Alter table signer add constraint singe

8、r_chk1 check(sex in(男,女);2) 创建表album的外键约束 Abbum_singer_fk1:Alter table album add constraintAbbum_signer_fk1 foreign key(singerlD)refere nee sig ner(sin gerID);3) 创建表album的外键约束Abbumanguage_fk1:Alter table album addcon stra intAbbum_la nguage_fk1foreig nkey(la nguagelD) refere nces Ian guage(la nguage

9、lD);4) 创建表 album 的外键约束 Abbum_classify_fk1:Alter table album addcon stra intAbbum_classify_fk1 foreig nkey(classifylD) refere nces Ablumclassify(classifylD);5) 创建表Song的外键约束Song_Singer_fk1:Alter table Song add con stra int Song_Sin ger_fk1 foreig n key(s in gerlD) refere nces Sin ger(s in gerlD);3. 创建

10、视图1) 创建视图V_album用以显示专辑信息Create or replace view v_albumAsSelect t1.albumlD,t1.albumName,t1.albuml ntro,t1.issueDate,t4.s in gerName,t3 .Ian guageName,t2.classifyNameFrom album t1 inner jion albumclassify t2 on t1.classifylD=t2.classifylDInner jion Ian guage t3 on tl.lan guagelD=t3 .Ian guagelDlNNER J

11、OlN sin ger t4 on tl.s in gerlD=t4.s in gerlD;2) 显示歌曲信息Create or replace view v_songAsSelect tl.s on glD,t1.s on gName,t2.s in gerName,t1.votes,t1.s on gFileFrom song t1 inner join sin ger t2 on tl.s in gerlD=t2.s in gerlD;4. 创建存储过程1)向专辑表(album)中插入数据的存储过程Create or replace procedure sp_in sertAlbum(A

12、lbumlD NVARCHAR2,albumName NVARCHAR2,albuml ntro NVARCHAR2,isssueDate DATE,Si ngerlD NVARCHAR2,Ian guagelD NVARCHAR2,classifylD NVARCHAR2)ASBEGINIn sert into album values(AlbumlD,albumName,albu mln tro,issueDate,si ngerlD,la nguagelD,classifylD )；END spn sertAlbum;2) 修改专辑表数据的存储过程Create or replace pr

13、ocedure sp_updateAlbum(fAlbumID NVARCHAR2, falbumName NVARCHAR2, falbuml ntro NVARCHAR2, fisssueDate DATE, fSi ngerlD NVARCHAR2, flan guagelD NVARCHAR2, fclassifylD NVARCHAR2) ASBEGINUpdate album settAlbumID=fAlbumID, albumName=falbumName , albuml ntro=falbuml ntro, issueDate=fisssueDate, sin gerID=

14、fS in gerlD,Lan guageID=fla nguagelD,ClassifylDfclassifylD;END sp_updateAlbum;3) 创建删除专辑表数据的存储过程Create or replace procedure sp_deleteAlbum(fAlbumID NVARCHAR2)AsBeg inDelete from album where albumID=falbumID;END sp_deletefalbum;ConnectionFactory 类：Package cn. edu.hbsi.db;Import java.i o.IO Excepti on;

15、Import java.i o.ln putStream;Import java.sql.Co nn ectio n;Import java.sql.DriverMa nager;Import java.sql.SQLExceptio n;Import java.util.Properties;/获得数据库连接Public class ConnectionF actory/数据库驱动类名Private static Stri ng DRIVER= ”；/连接数据库的URLPrivate static String URL= ” ”；/连接数据库的用户名Private static String

16、 USERNAME= ” ”；/连接数据库的密码Private static String PASSWORD= ” ”；Private Conn ecti onF actoryStatic getProperties lnfo();/该方法用来获得属性文件中的 driver,url,username,passwordPrivate static getPropertiesI nfo()Properties prop=new Properties();In putStreamin Stream=Thread.curre ntThread().getCo ntextClassLoader(). g

17、etResourseAsStream( jdbcC on .properties”)；Try/获得相应的键值对Prop .lo ad(i nStream);Catch(IOException e)e.pri ntStaticTrace();/根据相应的键获得对应的值DRIVER=prop.getProperty( driver ”)； URL=prop.getProperty( url ”)； USERNAME=prop.getProperty( Username”)； PASSWORD=prop.getProperty( password ”)；数据库连接对象/该方法用来加载驱动，并会的数据

18、库的连接对象returnconnPublic static Connection getCo nn ecti on()Connection conn=n ull;Try / 加载驱动程序Class.forName(DRIVER);catch(ClassNotF oun dExcepyi on e)e.pri ntStaticTrace();Try/获得数据库连接的对象Conn=DriverMa nager.getCo nn ectio n(URL.USERNAME,PASSWORD); Catch(SQLExceptio n e)e.pri ntStaticTrace();Retur n co

19、nn;DbClose 类：Pacage cn. edu.hbsi.db;Import java.sql.Co nn ecti on;Import java.sql.Result;Import java.sql.SQLExcwpti on;Import java.sql.Stateme nt;Public class DbClosePublic static void close(Co nn ecti on conn)lf(nu ll!=co nn) TryConn. close();Catch(SQLException e)e.pri ntStaticTrace();Public static

20、 void close(Stateme nt stmt)If(null!=stmt)Trystmt.close();Catch(SQLException e)e.pri ntStaticTrace();Public static void close(ResultSet rs)If(n ull!=rs)Tryrs.close();Catch(SQLException e)e.pri ntStaticTrace();Public static void close (ResultSet rs,Statement stmt,Connection conn )Close(rs);Close(stmt,c onn);Public static void close (Statement stmt,Connection conn )Close(stmt);Close(c onn);核心模块的实现：1)在 Calbum类中编