记:数据库课程设计(二)

接着上一篇博客写,记:数据库课程设计(一);

首先又对上一次的数据库结构和字段类型进行了微调:


下面介绍一下真个开发框架和我设想的基本步骤:

开发框架:利用javaEE(javaEE5)进行开发,使用Spring 框架和 Hibernate持久层框架(其中可能还是会写一些sql语句,毕竟是数据库课设嘛)

数据库选择:SQL Sever 2005

开发工具(软件):SybasePowerDesigner,SQL Server Management Studio 2008,MyEclipse 9

所用知识:Java基础,JavaEE知识,hibernate知识,数据库知识,javascript,jquery,css3,html5(前端开发打算尝试使用html5)


步骤:

1.数据库设计(已完成)

2.建立java web工程,利用hibernate反向工程生成相应的model,导入需要的lib,设计最初的包结构(model,controller,imp)和目录结构(page,css,js,imagine)

3.初步设计完成课设要求的html功能页面

4.编写jsp和后台java代码(sql语句)

5.修改调试

(6.找老师检查)

刚刚完成功了第二步,详细信息如下:

<1>.工程结构(如图):


<2>.利用powerdesigner生成sql文件:

/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2005                    */
/* Created on:     2011/12/18 11:21:08 上午                       */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('tb_apartmentmanager') and o.name = 'FK_TB_APART_REFERENCE_TB_MANAG')
alter table tb_apartmentmanager
   drop constraint FK_TB_APART_REFERENCE_TB_MANAG
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('tb_apartmentmanager') and o.name = 'FK_TB_APART_REFERENCE_TB_APART')
alter table tb_apartmentmanager
   drop constraint FK_TB_APART_REFERENCE_TB_APART
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('tb_chargerecord') and o.name = 'FK_TB_CHARG_REFERENCE_TB_ROOM')
alter table tb_chargerecord
   drop constraint FK_TB_CHARG_REFERENCE_TB_ROOM
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('tb_chargerecord') and o.name = 'FK_TB_CHARG_REFERENCE_TB_MANAG')
alter table tb_chargerecord
   drop constraint FK_TB_CHARG_REFERENCE_TB_MANAG
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('tb_room') and o.name = 'FK_TB_ROOM_REFERENCE_TB_APART')
alter table tb_room
   drop constraint FK_TB_ROOM_REFERENCE_TB_APART
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('tb_student') and o.name = 'FK_TB_STUDE_REFERENCE_TB_ROOM')
alter table tb_student
   drop constraint FK_TB_STUDE_REFERENCE_TB_ROOM
go

if exists (select 1
            from  sysobjects
           where  id = object_id('tb_apartment')
            and   type = 'U')
   drop table tb_apartment
go

if exists (select 1
            from  sysobjects
           where  id = object_id('tb_apartmentmanager')
            and   type = 'U')
   drop table tb_apartmentmanager
go

if exists (select 1
            from  sysobjects
           where  id = object_id('tb_chargerecord')
            and   type = 'U')
   drop table tb_chargerecord
go

if exists (select 1
            from  sysobjects
           where  id = object_id('tb_manager')
            and   type = 'U')
   drop table tb_manager
go

if exists (select 1
            from  sysobjects
           where  id = object_id('tb_room')
            and   type = 'U')
   drop table tb_room
go

if exists (select 1
            from  sysobjects
           where  id = object_id('tb_student')
            and   type = 'U')
   drop table tb_student
go

/*==============================================================*/
/* Table: tb_apartment                                          */
/*==============================================================*/
create table tb_apartment (
   apartmentID          numeric              identity,
   apartmentNO          nvarchar(30)         null,
   floorNums            int                  null,
   roomNums             int                  null,
   startTime            datetime             null,
   constraint PK_TB_APARTMENT primary key (apartmentID)
)
go

/*==============================================================*/
/* Table: tb_apartmentmanager                                   */
/*==============================================================*/
create table tb_apartmentmanager (
   id                   numeric              identity,
   apartmentID          numeric              null,
   managerID            numeric              null,
   constraint PK_TB_APARTMENTMANAGER primary key (id)
)
go

/*==============================================================*/
/* Table: tb_chargerecord                                       */
/*==============================================================*/
create table tb_chargerecord (
   recordID             int                  not null,
   roomID               int                  null,
   managerID            numeric              null,
   time                 datetime             null,
   type                 nvarchar(30)         null,
   money                int                  null,
   constraint PK_TB_CHARGERECORD primary key (recordID)
)
go

/*==============================================================*/
/* Table: tb_manager                                            */
/*==============================================================*/
create table tb_manager (
   managerID            numeric              identity,
   userName             nvarchar(30)         null,
   password             nvarchar(50)         null,
   name                 nvarchar(30)         null,
   number               int                  null,
   isSuperManager       bit                  null,
   constraint PK_TB_MANAGER primary key (managerID)
)
go

/*==============================================================*/
/* Table: tb_room                                               */
/*==============================================================*/
create table tb_room (
   roomID               int                  not null,
   roomNO               nvarchar(20)         null,
   apartmentID          numeric              null,
   holdNums             int                  null,
   expenses             int                  null,
   phone                nvarchar(20)         null,
   constraint PK_TB_ROOM primary key (roomID)
)
go

/*==============================================================*/
/* Table: tb_student                                            */
/*==============================================================*/
create table tb_student (
   studentID            numeric              identity,
   roomID               int                  null,
   name                 nvarchar(30)         null,
   sex                  nvarchar(10)         null,
   nation               nvarchar(20)         null,
   major                nvarchar(20)         null,
   class                nvarchar(20)         null,
   phone                nvarchar(20)         null,
   constraint PK_TB_STUDENT primary key (studentID)
)
go

alter table tb_apartmentmanager
   add constraint FK_TB_APART_REFERENCE_TB_MANAG foreign key (managerID)
      references tb_manager (managerID)
go

alter table tb_apartmentmanager
   add constraint FK_TB_APART_REFERENCE_TB_APART foreign key (apartmentID)
      references tb_apartment (apartmentID)
go

alter table tb_chargerecord
   add constraint FK_TB_CHARG_REFERENCE_TB_ROOM foreign key (roomID)
      references tb_room (roomID)
go

alter table tb_chargerecord
   add constraint FK_TB_CHARG_REFERENCE_TB_MANAG foreign key (managerID)
      references tb_manager (managerID)
go

alter table tb_room
   add constraint FK_TB_ROOM_REFERENCE_TB_APART foreign key (apartmentID)
      references tb_apartment (apartmentID)
go

alter table tb_student
   add constraint FK_TB_STUDE_REFERENCE_TB_ROOM foreign key (roomID)
      references tb_room (roomID)
go

<3>.利用SQL Server Management Studio执行sql文件:


<4>.利用Myeclipse hibernate反向工程生成相应model:

首先进入Myeclipse Database Explorer,

建立相应的数据库连接:


生成相应model:


<5>.Spring和数据源的相关配置:

(跟mysql,oracle的区别只是把hibernate的sql方言改一下就好了)

把生成的model的相关信息注册到sessionFactory

applicationContext.xml内容如下:




	
	
		
		
			 /WEB-INF/datasource-conf.properties
		
		
	

	
		
		
		
		
		
		
		
		
		
		
		
		
		
		
	

	
	

	
	
		
	
	
	
	
		
		
			
				acms.model.Apartment
				acms.model.ApartmentManager
				acms.model.ChargeRecord
				acms.model.Manager
				acms.model.Room
				acms.model.Student
			
		
		
			
				${hibernate.dialect}
				${hibernate.show_sql}
			
		
	
	
	
		
	

	
	
		
	

datasource-conf.properties内容如下:

driverClass=com.mysql.jdbc.Driver
jdbcUrl=jdbc\:sqlserver\://localhost\:1433;databaseName\=zys_test

testConnectionOnCheckout=false

hibernate.dialect=org.hibernate.dialect.SQLServerDialect

hibernate.show_sql=true//true只是为了调试起来方便

以后我还会更新博客记录我的课设过程,最后完成之后会上传全部的源代码和大家交流。

好了,今天就写到这里,刚吃完了饭,小睡一会儿~~

你可能感兴趣的