音乐网站管理系统.doc

表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语言编号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）字段名称说明数据类型字段长度是否允许空约束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_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) constraint pk_Album primary key,albumName Nvarchar2(20) not null,albumIntro Nvarchar2(2000) ,issueDate date,singerID Nvarchar2(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 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 Nvarchar2(4) constraint pk_Song primary key,songName Nvarchar2(20) not null,singerID Nvarchar2(4) not null,votes number(1) default 0,songFile blob);2. 创建约束1）创建表singer的检查约束signer_chk1:Alter table signer add constraint singer_chk1 check(sex in(男,女);2) 创建表album的外键约束Abbum_singer_fk1:Alter table album add constraint 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 foreign 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.issueDate,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.singerName,t1.votes,t1.songFileFrom 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 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,fclassifyID 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 Delete 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 ConnectionFactory/数据库驱动类名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 getPropertiesInfo() 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”);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=DriverManager.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(null!=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); Clo