ActiveRecord 的 CRUD 操作基本上都是由 ActiveRecordBase 的静态方法完成。其实例(含子类型)方法,以及 ActiveRecordMediator 只是对这些静态方法的包装调用而已。当然,ActiveRecordBase 方法又是对 NHibernate Session 方法的包装。
我们用下面这个实体来简单演示一下常用方法的使用。
为了观察 NHibernate 生成的 SQL 语句,我们在配置文件中添加 "show_sql" 配置。
1. Create
Create 通过调用 NHibernate.ISession.Save() 方法完成实体类型的存储操作。方法调用完成后,我们就能立即获取 PrimaryKey ID。
SQL
2. Update
Update 通过调用 NHibernate.ISession.Update() 方法完成实体类型的更新操作。
SQL
多数时候,我们先从数据库获取实例,然后再更新。但也可以直接创建实体对象,进行赋值更新。
SQL
对比上下两次生成的 SQL 语句,会发现后一种方法更 "快" 一些。但更新的前提是必须知道所有实体属性的 "值",通常这是做不到的,所以用处不大。
3. Save
Save 方法比较有趣,它调用的是 NHibernate.ISession.SaveOrUpdate()。也就是说,如果是 "新" 实体,它会 Create,否则 Update。
SQL
4. Delete
删除实体比较简单。
SQL
哎~~~ DeleteAll 还是老样子,要是想删除 1000 万个实体数据,我强烈建议你先去泡杯茶。
SQL
DeleteAll 还支持集合删除。
SQL
5. Refresh
Refresh 的作用是从数据库刷新实体信息。
SQL
6. Find
AR 提供了多种 Find 方法用来查找实体。
(1) PrimaryKey
我们还可以用 TryFind 代替 Find。这个方法在没找到实体时,不会触发异常。
(2) Property
(3) NHibernate.Expression.ICriterion
NHibernate Criterion 是 HQL 的 "另外一种表现方式",可用来组合多种条件。常用的有 LtExpression(<)、GeExpression(>=)、EqExpression(=)、GtExpression (>)、LeExpression(<=)、LikeExpression、AndExpression、 BetweenExpression、NotExpression、NotNullExpression、NullExpression 等。还可以使用 NHibernate.Express.Order 进行排序。
注意 FindOne 和 FindFirst 的区别,如果表达式返回的记录超过 1,FindOne 会触发异常。
7. Exists
Exists 可用来判断实体类型是否已经创建了对应的数据表,还可以通过主键判断实体是否存在,或者通过 hql 语句做出复杂的判断。
8. Query
Query 和 Find 的最大区别是它对 HQL 的支持。AR 提供了三个 Query 对象:SimpleQuery、ScalarQuery、CountQuery。
SimpleQuery: 主要用于获取一组信息,如实体数组、单个属性数组。
ScalarQuery: 主要用于获取单个实体对象,或单个实体的多个属性。当返回实体记录数大于 1 时会触发异常。
CountQuery: 用于统计数据库中实体记录数量。
我们还可以使用重载方法中的 QueryLanguage 参数,直接执行 SQL 语句,不过要谨慎使用,因为这样一来,就有可能失去自由切换数据库的能力。
9. Paging
利用 CountQuery 和 ActiveRecordBase.SlicedFindAll() 我们可以实现分页查询功能。
SQL
看它生成的 SQL 语句,可见其效率不是很好。只是不知道使用 SQLite / MySQL 时,是否会使用 LIMIT 语句进行分页处理。有空研究一下……
我们用下面这个实体来简单演示一下常用方法的使用。
[ActiveRecord("Users")]
public class User : ActiveRecordBase<User>
{
private int id;
[PrimaryKey(PrimaryKeyType.Identity, Access=PropertyAccess.FieldCamelcase)]
public int Id
{
get { return id; }
}
private string name;
[Property(Unique=true, NotNull=true)]
public string Name
{
get { return name; }
set { name = value; }
}
}
public class User : ActiveRecordBase<User>
{
private int id;
[PrimaryKey(PrimaryKeyType.Identity, Access=PropertyAccess.FieldCamelcase)]
public int Id
{
get { return id; }
}
private string name;
[Property(Unique=true, NotNull=true)]
public string Name
{
get { return name; }
set { name = value; }
}
}
为了观察 NHibernate 生成的 SQL 语句,我们在配置文件中添加 "show_sql" 配置。
<?xml version="1.0" encoding="utf-8" ?>
<activerecord>
<config>
<add key="hibernate.connection.driver_class" value="NHibernate.Driver.SqlClientDriver" />
<add key="hibernate.dialect" value="NHibernate.Dialect.MsSql2000Dialect" />
<add key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider" />
<add key="hibernate.connection.connection_string" value="server=(local);uid=sa;pwd=123456;database=test" />
<add key="hibernate.show_sql" value="true" />
</config>
</activerecord>
<activerecord>
<config>
<add key="hibernate.connection.driver_class" value="NHibernate.Driver.SqlClientDriver" />
<add key="hibernate.dialect" value="NHibernate.Dialect.MsSql2000Dialect" />
<add key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider" />
<add key="hibernate.connection.connection_string" value="server=(local);uid=sa;pwd=123456;database=test" />
<add key="hibernate.show_sql" value="true" />
</config>
</activerecord>
1. Create
Create 通过调用 NHibernate.ISession.Save() 方法完成实体类型的存储操作。方法调用完成后,我们就能立即获取 PrimaryKey ID。
User user = new User();
user.Name = "User";
user.Create();
Console.WriteLine(user.Id);
user.Name = "User";
user.Create();
Console.WriteLine(user.Id);
SQL
NHibernate: INSERT INTO Users (Name) VALUES (@p0); select SCOPE_IDENTITY()
@p0 = 'User'
@p0 = 'User'
2. Update
Update 通过调用 NHibernate.ISession.Update() 方法完成实体类型的更新操作。
User user2 = User.Find(1);
user2.Name = "User2";
user2.Update();
user2.Name = "User2";
user2.Update();
SQL
NHibernate: SELECT user0_.Id as Id0_, user0_.Name as Name0_ FROM Users user0_ WHERE user0_.Id=@p0
@p0 = '1'
NHibernate: UPDATE Users SET Name = @p0 WHERE Id = @p1
@p0 = 'User2'
@p1 = '1'
@p0 = '1'
NHibernate: UPDATE Users SET Name = @p0 WHERE Id = @p1
@p0 = 'User2'
@p1 = '1'
多数时候,我们先从数据库获取实例,然后再更新。但也可以直接创建实体对象,进行赋值更新。
// 先将上面的实体改成 Id {get; set;}
User user2 = new User();
user2.Id = 1;
user2.Name = "abcd";
user2.Update();
User user2 = new User();
user2.Id = 1;
user2.Name = "abcd";
user2.Update();
SQL
NHibernate: UPDATE Users SET Name = @p0 WHERE Id = @p1
@p0 = 'abcd'
@p1 = '1'
@p0 = 'abcd'
@p1 = '1'
对比上下两次生成的 SQL 语句,会发现后一种方法更 "快" 一些。但更新的前提是必须知道所有实体属性的 "值",通常这是做不到的,所以用处不大。
3. Save
Save 方法比较有趣,它调用的是 NHibernate.ISession.SaveOrUpdate()。也就是说,如果是 "新" 实体,它会 Create,否则 Update。
User user = new User();
user.Name = "User";
user.Save();
Console.WriteLine(user.Id);
User user2 = User.Find(user.Id);
user2.Name = "User2";
user2.Save();
Console.WriteLine(User.Find(user.Id).Name);
user.Name = "User";
user.Save();
Console.WriteLine(user.Id);
User user2 = User.Find(user.Id);
user2.Name = "User2";
user2.Save();
Console.WriteLine(User.Find(user.Id).Name);
SQL
NHibernate: INSERT INTO Users (Name) VALUES (@p0); select SCOPE_IDENTITY()
@p0 = 'User'
NHibernate: SELECT user0_.Id as Id0_, user0_.Name as Name0_ FROM Users user0_ WHERE user0_.Id=@p0
@p0 = '1'
NHibernate: UPDATE Users SET Name = @p0 WHERE Id = @p1
@p0 = 'User2'
@p1 = '1'
NHibernate: SELECT user0_.Id as Id0_, user0_.Name as Name0_ FROM Users user0_ WHERE user0_.Id=@p0
@p0 = '1'
@p0 = 'User'
NHibernate: SELECT user0_.Id as Id0_, user0_.Name as Name0_ FROM Users user0_ WHERE user0_.Id=@p0
@p0 = '1'
NHibernate: UPDATE Users SET Name = @p0 WHERE Id = @p1
@p0 = 'User2'
@p1 = '1'
NHibernate: SELECT user0_.Id as Id0_, user0_.Name as Name0_ FROM Users user0_ WHERE user0_.Id=@p0
@p0 = '1'
4. Delete
删除实体比较简单。
User user = new User();
user.Name = "User";
user.Save();
user.Delete();
user.Name = "User";
user.Save();
user.Delete();
SQL
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '1'
@p0 = '1'
哎~~~ DeleteAll 还是老样子,要是想删除 1000 万个实体数据,我强烈建议你先去泡杯茶。
for (int i = 0; i < 10; i++)
{
User user = new User();
user.Name = "User" + i;
user.Save();
}
User.DeleteAll();
{
User user = new User();
user.Name = "User" + i;
user.Save();
}
User.DeleteAll();
SQL
NHibernate: select user0_.Id as Id, user0_.Name as Name from Users user0_
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '1'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '2'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '3'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '4'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '5'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '6'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '7'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '8'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '9'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '10'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '1'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '2'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '3'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '4'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '5'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '6'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '7'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '8'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '9'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '10'
DeleteAll 还支持集合删除。
List<User> users = new List<User>();
for (int i = 0; i < 10; i++)
{
User user = new User();
user.Name = "User" + i;
user.Save();
users.Add(user);
}
User.DeleteAll(new int[] { users[1].Id, users[3].Id });
for (int i = 0; i < 10; i++)
{
User user = new User();
user.Name = "User" + i;
user.Save();
users.Add(user);
}
User.DeleteAll(new int[] { users[1].Id, users[3].Id });
SQL
NHibernate: SELECT user0_.Id as Id0_, user0_.Name as Name0_ FROM Users user0_ WHERE user0_.Id=@p0
@p0 = '2'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '2'
NHibernate: SELECT user0_.Id as Id0_, user0_.Name as Name0_ FROM Users user0_ WHERE user0_.Id=@p0
@p0 = '4'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '4'
@p0 = '2'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '2'
NHibernate: SELECT user0_.Id as Id0_, user0_.Name as Name0_ FROM Users user0_ WHERE user0_.Id=@p0
@p0 = '4'
NHibernate: DELETE FROM Users WHERE Id = @p0
@p0 = '4'
5. Refresh
Refresh 的作用是从数据库刷新实体信息。
User user = new User();
user.Name = "User";
user.Save();
User user2 = User.Find(user.Id);
user2.Name = "xxxx";
user2.Save();
Console.WriteLine(user.Name);
user.Refresh();
Console.WriteLine(user.Name);
user.Name = "User";
user.Save();
User user2 = User.Find(user.Id);
user2.Name = "xxxx";
user2.Save();
Console.WriteLine(user.Name);
user.Refresh();
Console.WriteLine(user.Name);
SQL
NHibernate: SELECT user0_.Id as Id0_, user0_.Name as Name0_ FROM Users user0_ WHERE user0_.Id=@p0
@p0 = '1'
@p0 = '1'
6. Find
AR 提供了多种 Find 方法用来查找实体。
(1) PrimaryKey
for (int i = 0; i < 10; i++)
{
User user = new User();
user.Name = "User" + i;
user.Save();
}
User u = User.Find(3);
Console.WriteLine(u.Name);
{
User user = new User();
user.Name = "User" + i;
user.Save();
}
User u = User.Find(3);
Console.WriteLine(u.Name);
我们还可以用 TryFind 代替 Find。这个方法在没找到实体时,不会触发异常。
User u = User.TryFind(500);
Console.WriteLine(u == null);
Console.WriteLine(u == null);
(2) Property
for (int i = 0; i < 10; i++)
{
User user = new User();
user.Name = "User" + i;
user.Save();
}
User[] us = User.FindAllByProperty("Name", "User3");
foreach (User u in us) Console.WriteLine(u.Name);
{
User user = new User();
user.Name = "User" + i;
user.Save();
}
User[] us = User.FindAllByProperty("Name", "User3");
foreach (User u in us) Console.WriteLine(u.Name);
(3) NHibernate.Expression.ICriterion
NHibernate Criterion 是 HQL 的 "另外一种表现方式",可用来组合多种条件。常用的有 LtExpression(<)、GeExpression(>=)、EqExpression(=)、GtExpression (>)、LeExpression(<=)、LikeExpression、AndExpression、 BetweenExpression、NotExpression、NotNullExpression、NullExpression 等。还可以使用 NHibernate.Express.Order 进行排序。
for (int i = 0; i < 10; i++)
{
User user = new User();
user.Name = "User" + i;
user.Save();
}
//User[] users = User.FindAll(new Order[]{ new Order("Id", false) }, new InExpression("Id", new object[]{ 1, 3, 5 }));
//User[] users = User.FindAll(new EqExpression("Name", "User3"));
//User[] users = User.FindAll(new OrExpression(new EqExpression("Name", "User1"), new EqExpression("Name", "User3")));
//User u = User.FindFirst(new BetweenExpression("Id", 3, 6));
User u = User.FindOne(new EqExpression("Id", 3));
{
User user = new User();
user.Name = "User" + i;
user.Save();
}
//User[] users = User.FindAll(new Order[]{ new Order("Id", false) }, new InExpression("Id", new object[]{ 1, 3, 5 }));
//User[] users = User.FindAll(new EqExpression("Name", "User3"));
//User[] users = User.FindAll(new OrExpression(new EqExpression("Name", "User1"), new EqExpression("Name", "User3")));
//User u = User.FindFirst(new BetweenExpression("Id", 3, 6));
User u = User.FindOne(new EqExpression("Id", 3));
注意 FindOne 和 FindFirst 的区别,如果表达式返回的记录超过 1,FindOne 会触发异常。
7. Exists
Exists 可用来判断实体类型是否已经创建了对应的数据表,还可以通过主键判断实体是否存在,或者通过 hql 语句做出复杂的判断。
// 判断数据表是否存在
Console.WriteLine(User.Exists());
// 通过主键判断
Console.WriteLine(User.Exists<int>(3));
// HQL
Console.WriteLine(User.Exists("Name=?", "User3"));
Console.WriteLine(User.Exists("Name=? or Id=?", "User400", 4));
Console.WriteLine(User.Exists());
// 通过主键判断
Console.WriteLine(User.Exists<int>(3));
// HQL
Console.WriteLine(User.Exists("Name=?", "User3"));
Console.WriteLine(User.Exists("Name=? or Id=?", "User400", 4));
8. Query
Query 和 Find 的最大区别是它对 HQL 的支持。AR 提供了三个 Query 对象:SimpleQuery、ScalarQuery、CountQuery。
SimpleQuery: 主要用于获取一组信息,如实体数组、单个属性数组。
for (int i = 0; i < 10; i++)
{
User user = new User();
user.Name = "User" + i;
user.Save();
}
//SimpleQuery query = new SimpleQuery(typeof(User), "from User user where user.Name = ?", "User2");
//User[] users = (User[])User.ExecuteQuery(query);
//SimpleQuery query = new SimpleQuery(typeof(User), typeof(String), "select user.Name from User user where user.Name = ?", "User2");
//string[] names = (string[])User.ExecuteQuery(query);
//SimpleQuery<User> query = new SimpleQuery<User>("from User user where user.Name = ?", "User2");
//User[] users = query.Execute();
SimpleQuery<string> query = new SimpleQuery<string>(typeof(User), "select user.Name from User user where user.Name = ?", "User2");
string[] names = query.Execute();
{
User user = new User();
user.Name = "User" + i;
user.Save();
}
//SimpleQuery query = new SimpleQuery(typeof(User), "from User user where user.Name = ?", "User2");
//User[] users = (User[])User.ExecuteQuery(query);
//SimpleQuery query = new SimpleQuery(typeof(User), typeof(String), "select user.Name from User user where user.Name = ?", "User2");
//string[] names = (string[])User.ExecuteQuery(query);
//SimpleQuery<User> query = new SimpleQuery<User>("from User user where user.Name = ?", "User2");
//User[] users = query.Execute();
SimpleQuery<string> query = new SimpleQuery<string>(typeof(User), "select user.Name from User user where user.Name = ?", "User2");
string[] names = query.Execute();
ScalarQuery: 主要用于获取单个实体对象,或单个实体的多个属性。当返回实体记录数大于 1 时会触发异常。
for (int i = 0; i < 10; i++)
{
User user = new User();
user.Name = "User" + i;
user.Save();
}
//ScalarQuery query = new ScalarQuery(typeof(User), "from User user where user.Name = ?", "User2");
//User u = (User)User.ExecuteQuery(query);
//ScalarQuery query = new ScalarQuery(typeof(User), "select user.Id, user.Name from User user where user.Name = ?", "User2");
//object[] properties = (object[])User.ExecuteQuery(query);
//ScalarQuery<User> query = new ScalarQuery<User>(typeof(User), "from User user where user.Name = ?", "User2");
//User u = query.Execute();
ScalarQuery<object> query = new ScalarQuery<object>(typeof(User), "select user.Id, user.Name from User user where user.Name = ?", "User2");
object properties = query.Execute();
{
User user = new User();
user.Name = "User" + i;
user.Save();
}
//ScalarQuery query = new ScalarQuery(typeof(User), "from User user where user.Name = ?", "User2");
//User u = (User)User.ExecuteQuery(query);
//ScalarQuery query = new ScalarQuery(typeof(User), "select user.Id, user.Name from User user where user.Name = ?", "User2");
//object[] properties = (object[])User.ExecuteQuery(query);
//ScalarQuery<User> query = new ScalarQuery<User>(typeof(User), "from User user where user.Name = ?", "User2");
//User u = query.Execute();
ScalarQuery<object> query = new ScalarQuery<object>(typeof(User), "select user.Id, user.Name from User user where user.Name = ?", "User2");
object properties = query.Execute();
CountQuery: 用于统计数据库中实体记录数量。
//CountQuery query = new CountQuery(typeof(User));
//Console.WriteLine(User.ExecuteQuery(query));
CountQuery query = new CountQuery(typeof(User), "Name like ?", "User1%");
Console.WriteLine(User.ExecuteQuery(query));
//Console.WriteLine(User.ExecuteQuery(query));
CountQuery query = new CountQuery(typeof(User), "Name like ?", "User1%");
Console.WriteLine(User.ExecuteQuery(query));
我们还可以使用重载方法中的 QueryLanguage 参数,直接执行 SQL 语句,不过要谨慎使用,因为这样一来,就有可能失去自由切换数据库的能力。
9. Paging
利用 CountQuery 和 ActiveRecordBase.SlicedFindAll() 我们可以实现分页查询功能。
for (int i = 1; i <= 100; i++)
{
User user = new User();
user.Name = "User" + i.ToString().PadLeft(3, '0');
user.Save();
}
int pageIndex = 5;
int pageSize = 10;
CountQuery countQuery = new CountQuery(typeof(User), "Id < ?", 45);
int count = (int)User.ExecuteQuery(countQuery);
if (count > 0)
{
int pageCount = count / pageSize + (count % pageSize > 0 ? 1 : 0);
pageIndex = Math.Min(pageIndex, pageCount);
int first = Math.Max((pageIndex - 1) * pageSize, 0);
User[] users = User.SlicedFindAll(first, pageSize, new LtExpression("Id", 45));
foreach (User u in users) Console.WriteLine(u.Name);
}
{
User user = new User();
user.Name = "User" + i.ToString().PadLeft(3, '0');
user.Save();
}
int pageIndex = 5;
int pageSize = 10;
CountQuery countQuery = new CountQuery(typeof(User), "Id < ?", 45);
int count = (int)User.ExecuteQuery(countQuery);
if (count > 0)
{
int pageCount = count / pageSize + (count % pageSize > 0 ? 1 : 0);
pageIndex = Math.Min(pageIndex, pageCount);
int first = Math.Max((pageIndex - 1) * pageSize, 0);
User[] users = User.SlicedFindAll(first, pageSize, new LtExpression("Id", 45));
foreach (User u in users) Console.WriteLine(u.Name);
}
SQL
NHibernate: select COUNT(*) as x0_0_ from Users user0_ where (Id<@p0)
@p0 = '45'
NHibernate: SELECT top 50 this.Id as Id0_, this.Name as Name0_ FROM Users this WHERE this.Id<@p0
@p0 = '45'
@p0 = '45'
NHibernate: SELECT top 50 this.Id as Id0_, this.Name as Name0_ FROM Users this WHERE this.Id<@p0
@p0 = '45'
看它生成的 SQL 语句,可见其效率不是很好。只是不知道使用 SQLite / MySQL 时,是否会使用 LIMIT 语句进行分页处理。有空研究一下……