记：数据库课程设计（二）

关注

原创最新推荐文章于 2026-07-13 09:17:08 发布 3.3k 阅读 AI 写同款· GEO 优化 ›

数据库课程设计mysql 以上是一个简单的数据库课程设计示例，涵盖了数据库设计、表结构、SQL查询和一些基本的操作。你可以根据自己的需求扩展这个示例，例如添加更多的实体和关系，增加更多的查询和操作等。希望这个示例能帮助你更好地理解和掌握数据库设计和MySQL的使用。数据库课程设计是一个重要的实践环节，它可以帮助你更好地理解数据库系统的设计和应用。以MySQL为例，下面是一个可能的数据库课程设计的基本步骤和要点。### 1. 选题首先，你需要确定一个具体的设计题目。这个题目应该具有一定的实际应用背景。阅读详情

接着上一篇博客写，记：数据库课程设计（一）;

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

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

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

数据库选择：SQL Sever 2005

开发工具（软件）：Sybase PowerDesigner，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内容如下：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:mvc="http://www.springframework.org/schema/context/mvc" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:tx="http://www.springframework.org/schema/tx" xmlns:util="http://www.springframework.org/schema/util"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
	http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
	http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
	http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
	http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd
	http://www.springframework.org/schema/context/mvc http://www.springframework.org/schema/context/mvc/spring-mvc-3.0.xsd">

	<!-- 配置数据源 -->
	<bean id="propertyConfigurer"
		class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="locations">
		<list>
			 <value>/WEB-INF/datasource-conf.properties</value>
		</list>
		</property>
	</bean>

	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
		destroy-method="close">
		<property name="driverClass" value="${driverClass}" />
		<property name="jdbcUrl" value="${jdbcUrl}" />
		<property name="user" value="${user}" />
		<property name="password" value="${password}" />
		<property name="initialPoolSize" value="${initialPoolSize}"></property>
		<property name="minPoolSize" value="${minPoolSize}"></property>
		<property name="maxPoolSize" value="${maxPoolSize}"></property>
		<property name="maxIdleTime" value="${maxIdleTime}"></property>
		<property name="acquireIncrement" value="${acquireIncrement}"></property>
		<property name="idleConnectionTestPeriod" value="${idleConnectionTestPeriod}"></property>
		<property name="acquireRetryAttempts" value="${acquireRetryAttempts}"></property>
		<property name="breakAfterAcquireFailure" value="${breakAfterAcquireFailure}"></property>
		<property name="maxStatements" value="${maxStatements}"></property>
		<property name="testConnectionOnCheckout" value="${testConnectionOnCheckout}"></property>
	</bean>

	<!-- 启动注解自动装配 -->
	<bean class="org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor" />

	<!-- 启动注解驱动 MVC-->
	<context:component-scan base-package="acms.controller">
		<context:include-filter type="annotation"
			expression="org.springframework.stereotype.Controller" />
	</context:component-scan>
	
	<!--基于注解映射的hibernateTemplate -->
	<bean id="sessionFactory"
		class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="annotatedClasses">
			<list>
				<value>acms.model.Apartment</value>
				<value>acms.model.ApartmentManager</value>
				<value>acms.model.ChargeRecord</value>
				<value>acms.model.Manager</value>
				<value>acms.model.Room</value>
				<value>acms.model.Student</value>
			</list>
		</property>
		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">${hibernate.dialect}</prop>
				<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
			</props>
		</property>
	</bean>
	
	<bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">
		<property name="sessionFactory" ref="sessionFactory" />
	</bean>

	<!-- JDBCTemplate -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource" />
	</bean>
</beans>

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只是为了调试起来方便

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

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

收录于