NHibernate之旅(20)：再探SchemaExport工具使用(

本节内容

       

• 引入       
  
• 实例分析
  
                 
  
  • 1.表及其约束               
    
  • 2.存储过程、视图       
    
                 
  
• 结语
  

引入

上篇我们初步探索了SchemaExport工具使用，知道如何使用SchemaExport工具和SchemaUpdate工具利用NHibernate持久化类和映射文件删除、创建、更新数据库架构，这篇具体分析如何为表字段增加一些约束？如何生成存储过程？如何生成视图？使用SchemaExport工具帮你搞定。
实例分析

1.表及其约束

众所周知，SchemaExport工具根据映射文件来生成数据库架构，在映射文件中通过Class映射可以很方便的生成数据库表。但是这篇我们看看映射的条件，所以我重新定义两个实体CategorySchema和ProductSchema，一对多关系。
Step1：两个实体持久化类编写代码如下：

1. public class CategorySchema
2. {
3. <property name="UnitsOnStock" not-null="true" >
4.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
5. </property><property name="UnitsOnStock" not-null="true" >
6.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
7. </property>public virtual Guid Id { get; set; }
8. <property name="UnitsOnStock" not-null="true" >
9.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
10. </property><property name="UnitsOnStock" not-null="true" >
11.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
12. </property>public virtual string Name { get; set; }
13. }
14. public class ProductSchema
15. {
16. <property name="UnitsOnStock" not-null="true" >
17.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
18. </property><property name="UnitsOnStock" not-null="true" >
19.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
20. </property>public virtual Guid Id { get; set; }
21. <property name="UnitsOnStock" not-null="true" >
22.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
23. </property><property name="UnitsOnStock" not-null="true" >
24.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
25. </property>public virtual string Name { get; set; }
26. <property name="UnitsOnStock" not-null="true" >
27.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
28. </property><property name="UnitsOnStock" not-null="true" >
29.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
30. </property>public virtual int UnitsOnStock { get; set; }
31. <property name="UnitsOnStock" not-null="true" >
32.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
33. </property><property name="UnitsOnStock" not-null="true" >
34.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
35. </property>public virtual CategorySchema CategorySchema { get; set; }
36. }

复制代码

Step2：为两个实体映射，使用最简方式，编写代码如下：

1. <?xml version="1.0" encoding="utf-8" ?>
2. <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
3. <property name="UnitsOnStock" not-null="true" >
4.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
5. </property><property name="UnitsOnStock" not-null="true" >
6.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
7. </property><property name="UnitsOnStock" not-null="true" >
8.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
9. </property><property name="UnitsOnStock" not-null="true" >
10.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
11. </property><property name="UnitsOnStock" not-null="true" >
12.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
13. </property><property name="UnitsOnStock" not-null="true" >
14.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
15. </property><property name="UnitsOnStock" not-null="true" >
16.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
17. </property><property name="UnitsOnStock" not-null="true" >
18.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
19. </property><property name="UnitsOnStock" not-null="true" >
20.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
21. </property> assembly="DomainModel" namespace="DomainModel">
23. <property name="UnitsOnStock" not-null="true" >
24.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
25. </property><class name="DomainModel.Entities.CategorySchema,DomainModel">
26. <property name="UnitsOnStock" not-null="true" >
27.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
28. </property><property name="UnitsOnStock" not-null="true" >
29.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
30. </property><id name="Id">
31. <property name="UnitsOnStock" not-null="true" >
32.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
33. </property><property name="UnitsOnStock" not-null="true" >
34.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
35. </property><property name="UnitsOnStock" not-null="true" >
36.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
37. </property><generator class="guid"/>
38. <property name="UnitsOnStock" not-null="true" >
39.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
40. </property><property name="UnitsOnStock" not-null="true" >
41.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
42. </property></id>
43. <property name="UnitsOnStock" not-null="true" >
44.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
45. </property><property name="UnitsOnStock" not-null="true" >
46.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
47. </property><property name="Name"/>
48. <property name="UnitsOnStock" not-null="true" >
49.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
50. </property></class>
51. <property name="UnitsOnStock" not-null="true" >
52.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
53. </property>
54. <property name="UnitsOnStock" not-null="true" >
55.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
56. </property><class name="DomainModel.Entities.ProductSchema,DomainModel">
57. <property name="UnitsOnStock" not-null="true" >
58.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
59. </property><property name="UnitsOnStock" not-null="true" >
60.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
61. </property><id name="Id">
62. <property name="UnitsOnStock" not-null="true" >
63.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
64. </property><property name="UnitsOnStock" not-null="true" >
65.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
66. </property><property name="UnitsOnStock" not-null="true" >
67.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
68. </property><generator class="guid"/>
69. <property name="UnitsOnStock" not-null="true" >
70.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
71. </property><property name="UnitsOnStock" not-null="true" >
72.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
73. </property></id>
74. <property name="UnitsOnStock" not-null="true" >
75.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
76. </property><property name="UnitsOnStock" not-null="true" >
77.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
78. </property><property name="Name"/>
79. <property name="UnitsOnStock" not-null="true" >
80.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
81. </property><property name="UnitsOnStock" not-null="true" >
82.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
83. </property><many-to-one name="CategorySchema"
84. <property name="UnitsOnStock" not-null="true" >
85.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
86. </property><property name="UnitsOnStock" not-null="true" >
87.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
88. </property><property name="UnitsOnStock" not-null="true" >
89.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
90. </property><property name="UnitsOnStock" not-null="true" >
91.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
92. </property> class="DomainModel.Entities.CategorySchema,DomainModel"/>
93. <property name="UnitsOnStock" not-null="true" >
94.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
95. </property></class>
96. <property name="UnitsOnStock" not-null="true" >
97.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
98. </property>
99. </hibernate-mapping>

复制代码

Step3：编写测试用例用于生成数据库架构：

1. [Test]
2. public void ExecuteSchemaTest()
3. {
4. <property name="UnitsOnStock" not-null="true" >
5.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
6. </property><property name="UnitsOnStock" not-null="true" >
7.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
8. </property>var export = new SchemaExport(_cfg);
9. <property name="UnitsOnStock" not-null="true" >
10.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
11. </property><property name="UnitsOnStock" not-null="true" >
12.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
13. </property>export.Execute(true, true, false, true);
14. }

复制代码

Step4：测试！NHibernate生成语句如下：

1. if exists (select 1 from sys.objects
2. <property name="UnitsOnStock" not-null="true" >
3.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
4. </property><property name="UnitsOnStock" not-null="true" >
5.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
6. </property><property name="UnitsOnStock" not-null="true" >
7.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
8. </property><property name="UnitsOnStock" not-null="true" >
9.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
10. </property><property name="UnitsOnStock" not-null="true" >
11.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
12. </property> where object_id = OBJECT_ID(N'[FK45BBFB51BC9515A6]')
13. <property name="UnitsOnStock" not-null="true" >
14.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
15. </property><property name="UnitsOnStock" not-null="true" >
16.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
17. </property><property name="UnitsOnStock" not-null="true" >
18.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
19. </property><property name="UnitsOnStock" not-null="true" >
20.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
21. </property><property name="UnitsOnStock" not-null="true" >
22.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
23. </property> AND parent_object_id = OBJECT_ID('ProductSchema'))
24. alter table ProductSchema<property name="UnitsOnStock" not-null="true" >
25.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
26. </property>drop constraint FK45BBFB51BC9515A6
28. if exists (select * from dbo.sysobjects
29. <property name="UnitsOnStock" not-null="true" >
30.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
31. </property><property name="UnitsOnStock" not-null="true" >
32.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
33. </property><property name="UnitsOnStock" not-null="true" >
34.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
35. </property><property name="UnitsOnStock" not-null="true" >
36.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
37. </property><property name="UnitsOnStock" not-null="true" >
38.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
39. </property> where id = object_id(N'ProductSchema')
40. <property name="UnitsOnStock" not-null="true" >
41.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
42. </property><property name="UnitsOnStock" not-null="true" >
43.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
44. </property><property name="UnitsOnStock" not-null="true" >
45.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
46. </property><property name="UnitsOnStock" not-null="true" >
47.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
48. </property><property name="UnitsOnStock" not-null="true" >
49.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
50. </property> and OBJECTPROPERTY(id, N'IsUserTable') = 1)
51. <property name="UnitsOnStock" not-null="true" >
52.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
53. </property> drop table ProductSchema
54. if exists (select * from dbo.sysobjects
55. <property name="UnitsOnStock" not-null="true" >
56.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
57. </property><property name="UnitsOnStock" not-null="true" >
58.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
59. </property><property name="UnitsOnStock" not-null="true" >
60.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
61. </property><property name="UnitsOnStock" not-null="true" >
62.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
63. </property><property name="UnitsOnStock" not-null="true" >
64.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
65. </property> where id = object_id(N'CategorySchema')
66. <property name="UnitsOnStock" not-null="true" >
67.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
68. </property><property name="UnitsOnStock" not-null="true" >
69.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
70. </property><property name="UnitsOnStock" not-null="true" >
71.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
72. </property><property name="UnitsOnStock" not-null="true" >
73.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
74. </property><property name="UnitsOnStock" not-null="true" >
75.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
76. </property> and OBJECTPROPERTY(id, N'IsUserTable') = 1)
77. <property name="UnitsOnStock" not-null="true" >
78.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
79. </property> drop table CategorySchema
81. create table ProductSchema (
82. <property name="UnitsOnStock" not-null="true" >
83.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
84. </property> Id UNIQUEIDENTIFIER not null,
85. <property name="UnitsOnStock" not-null="true" >
86.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
87. </property> Name NVARCHAR(255) null,
88. <property name="UnitsOnStock" not-null="true" >
89.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
90. </property> CategorySchema UNIQUEIDENTIFIER null,
91. <property name="UnitsOnStock" not-null="true" >
92.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
93. </property> primary key (Id)
94. )
95. create table CategorySchema (
96. <property name="UnitsOnStock" not-null="true" >
97.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
98. </property> Id UNIQUEIDENTIFIER not null,
99. <property name="UnitsOnStock" not-null="true" >
100.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
101. </property> Name NVARCHAR(255) null,
102. <property name="UnitsOnStock" not-null="true" >
103.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
104. </property> primary key (Id)
105. )
107. alter table ProductSchema add constraint FK45BBFB51BC9515A6
108. foreign key (CategorySchema) references CategorySchema

复制代码

仔细看看生成的语句，都按默认的值生成了表，Name列字符串类型NVARCHAR(255)，默认为null；外键默认一数字字符串等。
1.设置非空类型和长度

在映射文件中为ProductSchema实体的Name属性添加：not-null="true"表示非空类型，length="50"：列长度设置为50，代码片段如下：

1. [/code]测试，生成语句如下：
2. [code]create table ProductSchema (
3. <property name="UnitsOnStock" not-null="true" >
4.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
5. </property> Id UNIQUEIDENTIFIER not null,
6. <property name="UnitsOnStock" not-null="true" >
7.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
8. </property> <strong>Name NVARCHAR(50) not null,</strong>
9. <property name="UnitsOnStock" not-null="true" >
10.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
11. </property> CategorySchema UNIQUEIDENTIFIER null,
12. <property name="UnitsOnStock" not-null="true" >
13.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
14. </property> primary key (Id)
15. )

复制代码

2.设置外键Foreign Keys

在映射文件设置外键名称，注意有的需要两边都要设置才生效，代码片段如下：

1. [/code]生成语句如下：
2. [code]if exists (select 1 from sys.objects
3. <property name="UnitsOnStock" not-null="true" >
4.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
5. </property><property name="UnitsOnStock" not-null="true" >
6.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
7. </property><property name="UnitsOnStock" not-null="true" >
8.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
9. </property><property name="UnitsOnStock" not-null="true" >
10.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
11. </property><property name="UnitsOnStock" not-null="true" >
12.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
13. </property> where object_id = OBJECT_ID(N'[FK_Product_Category]')
14. <property name="UnitsOnStock" not-null="true" >
15.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
16. </property><property name="UnitsOnStock" not-null="true" >
17.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
18. </property><property name="UnitsOnStock" not-null="true" >
19.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
20. </property><property name="UnitsOnStock" not-null="true" >
21.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
22. </property><property name="UnitsOnStock" not-null="true" >
23.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
24. </property> AND parent_object_id = OBJECT_ID('ProductSchema'))
25. alter table ProductSchema<property name="UnitsOnStock" not-null="true" >
26.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
27. </property>drop constraint FK_Product_Category
28. ...
29. alter table ProductSchema add constraint FK_Product_Category
30. foreign key (CategorySchema) references CategorySchema

复制代码

3.设置Unique约束

我们要求Name字段唯一，添加Unique约束，代码片段如下：

1. [/code]生成语句如下：
2. [code]create table ProductSchema (
3. <property name="UnitsOnStock" not-null="true" >
4.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
5. </property> Id UNIQUEIDENTIFIER not null,
6. <property name="UnitsOnStock" not-null="true" >
7.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
8. </property> Name NVARCHAR(50) not null unique,
9. <property name="UnitsOnStock" not-null="true" >
10.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
11. </property> CategorySchema UNIQUEIDENTIFIER null,
12. <property name="UnitsOnStock" not-null="true" >
13.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
14. </property> primary key (Id)
15. )

复制代码

还有一种unique-key约束，同时为两个属性设置unique-key约束。我们为Customer持久化类的FirstName和LastName属性添加Unique约束：
编写映射，设置FirstName和LastName的Unique约束为UK_Person_Name：

1. [/code]生成语句如下：
2. [code]create table Customer(
3. <property name="UnitsOnStock" not-null="true" >
4.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
5. </property> CustomerId INT IDENTITY not null,
6. <property name="UnitsOnStock" not-null="true" >
7.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
8. </property> FirstName NVARCHAR(50) not null,
9. <property name="UnitsOnStock" not-null="true" >
10.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
11. </property> LastName NVARCHAR(50) not null,
12. <property name="UnitsOnStock" not-null="true" >
13.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
14. </property> primary key (CustomerId),
15. <property name="UnitsOnStock" not-null="true" >
16.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
17. </property> unique (FirstName, LastName)
18. )

复制代码

4.设置索引Index

1. [/code]生成语句如下：
2. [code]create table ProductSchema (Id UNIQUEIDENTIFIER...)
3. create table CategorySchema (Id UNIQUEIDENTIFIER...)
4. create index IDX_Product_Name on ProductSchema (Name)

复制代码

5.设置Check约束

我们为UnitsOnStock值设置大于等于0：

1. <property name="UnitsOnStock" not-null="true" >
2.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
3. </property>

复制代码

生成语句如下：

1. create table ProductSchema (<property name="UnitsOnStock" not-null="true" >
2.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
3. </property> Id UNIQUEIDENTIFIER not null,<property name="UnitsOnStock" not-null="true" >
4.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
5. </property> Name NVARCHAR(50) not null unique,<property name="UnitsOnStock" not-null="true" >
6.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
7. </property> UnitsOnStock INT null check( UnitsOnStock >= 0) ,<property name="UnitsOnStock" not-null="true" >
8.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
9. </property> CategorySchema UNIQUEIDENTIFIER null,<property name="UnitsOnStock" not-null="true" >
10.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
11. </property> primary key (Id))

复制代码

好了，还有很多设置大家自己探索啦！知道了这些我们在写映射的时候就注意啦！我之前映射文件属性映射为什么写的比较全呢？就是这个原因，便于生成数据库架构是自己约束的并不是默认的。
2.存储过程、视图

除了表，我们还有存储过程和视图。怎么利用SchemaExport工具生成存储过程和视图呢？在映射文件中提供了database-object元素用来创建和删除数据库对象。

1. <property name="UnitsOnStock" not-null="true" >
2.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
3. </property>创建存储过程或视图语句等数据库对象<property name="UnitsOnStock" not-null="true" >
4.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
5. </property>删除存储过程或视图语句等数据库对象

复制代码

1.存储过程

还记得我们在NHibernate之旅(17)：探索NHibernate中使用存储过程(下)中创建的三个存储过程了吗？当时我们在数据库中手写创建的啊。现在完全可以不用那种古老的方法啦。我们在映射文件中编写database-object：在创建数据库架构时创建名为entitySProcs的存储过程，在删除数据库架构时删除名为entitySProcs的存储过程。现在使用SchemaExport工具不仅仅生成了表，还生成了存储过程。还剩两个存储过程就留给大家去完成吧。

1. <property name="UnitsOnStock" not-null="true" >
2.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
3. </property><property name="UnitsOnStock" not-null="true" >
4.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
5. </property><property name="UnitsOnStock" not-null="true" >
6.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
7. </property><property name="UnitsOnStock" not-null="true" >
8.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
9. </property>CREATE PROCEDURE entitySProcs<property name="UnitsOnStock" not-null="true" >
10.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
11. </property><property name="UnitsOnStock" not-null="true" >
12.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
13. </property> AS<property name="UnitsOnStock" not-null="true" >
14.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
15. </property><property name="UnitsOnStock" not-null="true" >
16.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
17. </property> SELECT CustomerId,Version,Firstname,Lastname FROM Customer<property name="UnitsOnStock" not-null="true" >
18.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
19. </property><property name="UnitsOnStock" not-null="true" >
20.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
21. </property><property name="UnitsOnStock" not-null="true" >
22.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
23. </property><property name="UnitsOnStock" not-null="true" >
24.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
25. </property><property name="UnitsOnStock" not-null="true" >
26.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
27. </property> DROP PROCEDURE entitySProcs<property name="UnitsOnStock" not-null="true" >
28.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
29. </property><property name="UnitsOnStock" not-null="true" >
30.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
31. </property>

复制代码

2.视图

还记得我们在NHibernate之旅(14)：探索NHibernate中使用视图中创建了第一个视图了吗？那时我们也是在数据库中手动创建的，现在我们可以自动创建了！打开CustomerView.hbm.xml文件，添加database-object：在创建数据库架构时创建名为viewCustomer的视图，在删除数据库架构时删除名为viewCustomer的视图。

1. <property name="UnitsOnStock" not-null="true" >
2.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
3. </property><property name="UnitsOnStock" not-null="true" >
4.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
5. </property><property name="UnitsOnStock" not-null="true" >
6.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
7. </property>CREATE VIEW [dbo].[viewCustomer]<property name="UnitsOnStock" not-null="true" >
8.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
9. </property><property name="UnitsOnStock" not-null="true" >
10.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
11. </property>AS<property name="UnitsOnStock" not-null="true" >
12.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
13. </property><property name="UnitsOnStock" not-null="true" >
14.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
15. </property>SELECT DISTINCT c.CustomerId, c.Firstname, c.Lastname, o.OrderId, o.OrderDate<property name="UnitsOnStock" not-null="true" >
16.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
17. </property><property name="UnitsOnStock" not-null="true" >
18.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
19. </property>FROM<property name="UnitsOnStock" not-null="true" >
20.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
21. </property><property name="UnitsOnStock" not-null="true" >
22.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
23. </property><property name="UnitsOnStock" not-null="true" >
24.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
25. </property><property name="UnitsOnStock" not-null="true" >
26.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
27. </property> dbo.Customer AS c INNER JOIN<property name="UnitsOnStock" not-null="true" >
28.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
29. </property><property name="UnitsOnStock" not-null="true" >
30.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
31. </property>dbo.[Order] AS o ON c.CustomerId = o.OrderId INNER JOIN<property name="UnitsOnStock" not-null="true" >
32.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
33. </property><property name="UnitsOnStock" not-null="true" >
34.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
35. </property>dbo.OrderProduct AS op ON o.OrderId = op.[Order] INNER JOIN<property name="UnitsOnStock" not-null="true" >
36.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
37. </property><property name="UnitsOnStock" not-null="true" >
38.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
39. </property>dbo.Product AS p ON op.Product = p.ProductId<property name="UnitsOnStock" not-null="true" >
40.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
41. </property><property name="UnitsOnStock" not-null="true" >
42.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
43. </property>GROUP BY c.CustomerId, c.Firstname, c.Lastname, o.OrderId, o.OrderDate<property name="UnitsOnStock" not-null="true" >
44.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
45. </property><property name="UnitsOnStock" not-null="true" >
46.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
47. </property>drop view dbo.viewCustomer

复制代码

测试生成数据库架构，出现错误“数据库中已存在名为'viewCustomer'的对象”。这是什么原因呢？看看NHibernate生成语句：

1. create table viewCustomer (<property name="UnitsOnStock" not-null="true" >
2.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
3. </property> CustomerId INT IDENTITY NOT NULL,<property name="UnitsOnStock" not-null="true" >
4.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
5. </property> Firstname NVARCHAR(255) null,<property name="UnitsOnStock" not-null="true" >
6.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
7. </property> Lastname NVARCHAR(255) null,<property name="UnitsOnStock" not-null="true" >
8.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
9. </property> OrderId INT null,<property name="UnitsOnStock" not-null="true" >
10.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
11. </property> OrderDate DATETIME null,<property name="UnitsOnStock" not-null="true" >
12.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
13. </property> primary key (CustomerId))

复制代码

观察NHibernate生成SQL语句发现NHibernate利用Class映射自动生成了viewCustomer表，因为NHibernate见到Class映射就认为是表，它不知道这里映射的是视图，视图和表在映射文件中没有什么区别。我们修改一下这个映射文件，在database-object元素上面再添加一个database-object用于删除NHibernate生成的表。

1. <property name="UnitsOnStock" not-null="true" >
2.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
3. </property>drop table dbo.viewCustomer<property name="UnitsOnStock" not-null="true" >
4.   <column name="UnitsOnStock" check="UnitsOnStock >= 0"/>
5. </property>drop table dbo.viewCustomer

复制代码

这个database-object的意思就是在创建数据库架构时删除NHibernate自动生成的表viewCustomer，在删除数据库架构时删除表viewCustomer。为什么删除两次呢？因为我们有可能只Drop，那么NHibernate就执行database-object中的Drop。
这样在CustomerView.hbm.xml文件中就有两个database-object了，总体的意思就是在创建数据库架构时删除NHibernate自动生成的表viewCustomer并创建名为viewCustomer的视图，在删除数据库架构时删除名为viewCustomer表和名为viewCustomer的视图。
结语
好了，终于把SchemaExport工具研究透彻了，应该没有说漏别的东西，就到这里了。下面看看NHibernate对象去，什么状态啦，缓存啦。
本系列链接：NHibernate之旅系列文章导航
NHibernate Q&A

       

• 欢迎加入NHibernate中文社区，一起讨论NHibernate知识！       
  
• 请到NHibernate中文社区下载本系列相关源码。
  

下次继续分享NHibernate！

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！