一、背景 我们现在做的项目,用NHibernate实现数据访问层。 访问数据时,有的数据库表是确定的:有明确的表名、字段名。这时候按照常规的方法处理即可:建立数据库表到类的映射,使用HQL读写数据库。 但有的数据访问,所针对的数据库表是不确定的,在运行阶
一、背景
我们现在做的项目,用NHibernate实现数据访问层。
访问数据时,有的数据库表是确定的:有明确的表名、字段名。这时候按照常规的方法处理即可:建立数据库表到类的映射,使用HQL读写数据库。
但有的数据访问,所针对的数据库表是不确定的,在运行阶段确定访问哪些数据库表的哪些字段。数据库表和字段都不确定,自然没办法建议O-R映射,只好构造SQL语句了。
既然已经用了NHibernate,我们利用SQL访问数据库时,也仍然使用NHibernate。主要是想利用它管理的Session,还有对分页查询的支持:可以指定开始行,还有所需要的总行数。
就因为贪这个便宜,出问题了。
二、错误
使用SQL(而不是HQL)访问数据库,而且加上访问范围(开始行或总行数),有时候会出现奇怪的问题:有的字段,在数据库中明明有值,但就是读不出来。
例如,有一个数据库表,表的定义大致是:MyTable(Id, Name, FromPoint)。
现在需要查询其中的前10条记录,利用下面的SQL语句:
select Id, Name, FromPoint from MyTable
创建好SQL查询后,设置查询范围:
... var query = session.CreateSQLQuery(sql); query.SetFirstResult(0); query.SetMaxResults(10); ...
对于执行结果,期望的是,每条记录有三个字段。但实际上,只返回前两个字段的值,第三个字段,FromPoint的值,没有返回。
查看NHibernate的日志,所生成的SQL是:
select Id, Name from (select Id, Name, FromPoint from MyTable) where rownum<=10
这实在令人费解。
三、原因
在网上搜索,找不到原因。只好祭出最后的杀手锏:跟踪源代码。
结论是:NHibernate在解析SQL语句时有问题,导致过滤掉了不该过滤掉的列。
具体地说,在类 NHibernate.Dialect.Dialect的方法 ExtractColumnOrAliasNames 中有如下代码:
if (token.StartsWithCaseInsensitive("select")) continue; if (token.StartsWithCaseInsensitive("distinct")) continue; if (token.StartsWithCaseInsensitive(",")) continue; if (token.StartsWithCaseInsensitive("from")) break;
这段代码的本意,是不将select、distinct等SQL保留字作为列,而且一旦遇到from保留字,就意味着列结束。
问题在于,它用了StartsWith,而不是整词判断,从而误伤了以这些关键字开头的字段。而且一旦有以from开始的字段,后面的字段都被过滤掉了。
四、办法
出问题的方法 ExtractColumnOrAliasNames 是 static internal 的,没有修改机会。我们只好绕道走:自行加上rownum的过滤条件。
本来要借助NHibernate实现跨数据库,我还一直告诫小伙伴们避免使用Oracle、SQL Server等数据库管理系统特定的SQL语法来着。
不少程序员会本能地想到一个解决方法:既然是开源的,而且错误原因找到了,拿过来修改好,编译一个新版本用就OK。我对此明确表示不赞成,一方面,这会脱离NHibernate主线版本的发展,另一方面,也给组件的引用带来不便:我们正在用Spring.NET管理NHibernate,build一个自己的版本,要设置一堆元信息,想想就头大。
五、范围
就我目前所知,该BUG出现的情景如下:
使用原生SQL访问数据库;设置了FirstResult、MaxResults等查询结果范围;最早的引入版本不详,最新的版本中,从源代码上判断,此问题仍然存在;我们使用的是Oracle数据库,其它数据库管理系统不详。 
InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Dreamweaver CS6
Visual web development tools