Database course Design (ii) __ Database

Then on a blog to write, remember: Database curriculum design (i);

First, the previous database structure and field types were fine-tuned:

Here's a look at the entire development framework and the basic steps I've envisioned:

Development framework: Using Java EE (javaEE5) for development, using the spring framework and the Hibernate Persistence layer framework (which might still write some SQL statements, after all, database classes)

Database selection: SQL Sever 2005

Development tools (software): Sybase powerdesigner,sql Server Management Studio 2008,myeclipse 9

Knowledge used: Java basics, EE knowledge, hibernate knowledge, Database knowledge, JAVASCRIPT,JQUERY,CSS3,HTML5 (front-end development intends to try to use HTML5)

Steps:

1. Database design (completed)

2. Build Java Web Engineering, use Hibernate reverse engineering to generate the corresponding model, import the needed lib, design the original package structure (MODEL,CONTROLLER,IMP) and directory structure (page,css,js,imagine)

3. The initial design completes the class to request the HTML function page

4. Write JSP and background Java code (SQL statement)

5. Modify and Debug

(6. Find a teacher to check)

Just finished the second step, detailed information as follows:

<1> engineering structure (as pictured):

<2>. Generate SQL Files using PowerDesigner:

/*==============================================================*//* DBMS Name:microsoft SQL Server 2005 * * * Created ON:2011/12/18 11:21:08 a.m./*/*========================================= =====================*/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 tab  Le tb_apartmentmanager drop constraint fk_tb_apart_reference_tb_manag go to 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_apa RT Go if exists (select 1-sys.sysreferences R join Sys.sysobjects o on (o.id = R.constid and O.type = ' F ') whe Re 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-sys.sysreferences R join Sys.sysobjects o on (o.id = R.constid and O.ty PE = ' F ') where R.fkeyid = object_id (' Tb_chargerecord ') and o.name = ' fk_tb_charg_reference_tb_manag ') ALTER TABLE Tb_c Hargerecord 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_ref Erence_tb_apart ') ALTER TABLE tb_room drop constraint Fk_tb_room_reference_tb_apart go to 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_a
            Partment 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 (Sele Ct 1 from sysobjects where id = object_id (' Tb_manager ') and type = ' U ') drop tab
            Le Tb_manager go to 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 = objec t_id (' tb_student ') and type = ' U ') drop table tb_student go/*========================================== ====================*/
/* table:tb_apartment * * */*====================================================== ========*/CREATE TABLE Tb_apartment (Apartmentid numeric identity, Apartmentno Nvar char (a) null, floornums int NULL, roomnums int nul L, 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 numeri c 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 (a) null, Money int NULL, Constrai NT Pk_tb_chargerecord primary KEY (RecordID)) Go/*==============================================================*// * Table:tb_manager * * */*====================================================== ========*/CREATE TABLE Tb_manager (ManagerID numeric identity, UserName Nvarch 
   AR (a) null, password nvarchar (m) null, name nvarchar () NULL, Number inT null, Issupermanager bit NULL, constraint Pk_tb_manager primary KEY (Manag                                               Erid)) Go/*==============================================================*//* table:tb_room * */*==============================================================*/CREATE TABLE Tb_room (Roomi          D int not NULL, Roomno nvarchar (a) null, Apartmentid                  numeric null, holdnums int NULL, expenses int NULL, phone nvarchar () NULL, constraint Pk_tb_room primary key (Roomid)) Go/*==========
====================================================*//* Table:tb_student * /*==============================================================*/CREATE TABLE tb_student (StudentID numer IC Identity, Roomid int NULL, name nvarchar (a) null, sex         nvarchar (a) null, Nation nvarchar (a) null, major nvarchar (20) NULL, class nvarchar () null, phone nvarchar null, cons Traint Pk_tb_student primary KEY (StudentID)) Go ALTER TABLE Tb_apartmentmanager add constraint _tb_manag foreign KEY (ManagerID) references Tb_manager (ManagerID) Go ALTER TABLE Tb_apartmentmanager Add const  Raint 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_apartme NT (APARTMENTID) go ALTER TABLE tb_student add constraint fk_tb_stude_reference_tb_room foreign key (ROOMID) ref
 Erences tb_room (Roomid) go

<3&gt. Use SQL Server Management Studio to execute SQL files:

<4>. Use myeclipse Hibernate reverse engineering to generate the corresponding model:

First into MyEclipse Database Explorer,

Establish the appropriate database connection:

Generate the corresponding model:

<5>. Related configurations for spring and data sources:

(The difference with mysql,oracle is just to change the hibernate's SQL dialect.)

Register the generated model information with the Sessionfactory

Applicationcontext.xml contents are as follows:

<?xml version= "1.0" encoding= "UTF-8"?> <beans "xmlns=" 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 "> <!--Configure Data source--> <bean id=" Propertyconfigurer "class=" Org.springframework.beans.factory.config.PropertyPlaceholderConfigurer "> < Property Name= "Locations" > <list> <value>/WEB-INF/datasource-conf.properties</value> </lis t> </property> </bean> <bean id= "DataSource" class= "Com.mchange.v2.c3p0.ComboPooledDataSource" de stroy-method= "Close" > <property name= "driverclass" value= "${driverclass}"/> <property name= "JdbcUrl" Val Ue= "${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> <!--start annotation automatic assembly--> <bean class="
	Org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor "/> <!--boot annotation driver 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" based on annotation mapping Org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean "> <property name=" DataSource " ref= "DataSource"/> <property name= "annotatedclasses" > <list> <value>acms.model.apartment&lt ;/value> <value>acms.model.ApartmentManager</value> <value>acms.model.chargerecord</ value> <value>acms.model.Manager</value> <value>acms.model.Room</value> &LT;VALUE&G t;acms.model.student</value> </list> </property> <property name= "Hibernateproperties" > & lt;props> <prop key= "Hibernate.dialect" >${hibernate.dialect}</prop> <prop key= "hibernate.show_sq L ">${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.springframewor" K.jdbc.core.jdbctemplate "> <property name=" dataSource "ref=" DataSource "/> </bean> </beans>

Datasource-conf.properties contents are as follows:

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 just for easy debugging.

Later I will update the blog to record my course setup process, the final completion will upload all the source code and exchange.

Well, today is written here, just finished dinner, take a nap ~ ~