音乐网站管理系统

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 .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(“jdbccperties”);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 .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(); 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);核心模块的