使用PreparedStatement為不同的數據庫編寫可移植的數據庫存取方法

使用PreparedStatement為不同的數據庫編寫可移植的數據庫存取方法（1）

服務端的Web application經常需要讀取后端的數據庫，一些設計良好、高效的數據庫存取方法可以大大降低代碼的可維護性，從而提升自身應用的復雜性，JDBC的PreparedStatement接口和它的setObject()方法可以幫助你實現
快速、通用的數據庫訪問方法，應用在任何數據庫服務器上。

Java has become increasingly popular as a server-side programming language for database-powered Web applications. Those applications´ business logic-handling methods must work with data from backend databases. Therefore, you need a set of well-designed database access methods. Those access methods should meet the following requirements:

They should be generic for any table schema. Users can supply the actual table schema information at runtime. Genericness lets you reuse the methods throughout the application to reduce code maintenance overhead and minimize chances for human errors. Genericness also lets you easily add new tables or change existing schema, therefore, making the application more expandable and flexible.

They should be portable across different database servers so the Web application can deploy independent of the backend. Java´s built-in, cross-operating system portability no longer suffices for Web applications.

They should be optimized for speed.

Java現已成為服務端web應用的主要編程語言，幾乎所有的商業邏輯都離不開后端數據庫的支持，因此，你需要一系列設計良好的數據庫訪問方法。這些方法需要滿足以下一些要求：

對所有的表schema具有通用性，可以在運行時動態地應用某一種形式，這種通用性降低了方法在整個應用中的維護量并最小化人為失誤。這種通用性也可以使你任意的增加新的表schema或改變現有的表schema，提高應用的擴展性

它們可以被應用于多種數據庫服務器上從而使你的web應用可以獨立于后端的數據庫，Java的跨平臺對web應用還不夠，好的應用還需要有多數據庫的適應性。還應調整他們的速度性能

In this tip, I discuss how to write generic, portable, and fast database access methods. To illustrate, I provide example code to access an imaginary database table called Article (see Article.sql), which has several fields with different types.

在本文中，我們討論如何編寫通用、可移植、高效的數據庫訪問方法，為了闡述清楚，我們用實例代碼對一個假想數據庫表Article進行操作（見Article.sql）,該表中有幾個不同類型的字段

Article.sql
# NOTE: This is a schema in MySQL syntax
#

CREATE TABLE Article (
Article.ArticleID BIGINT(20) PRIMARY KEY,
Article.Title TEXT,
Article.Text MEDIUMTEXT,
Article.WordCount INT,
Article.SubmitDate DATETIME,
Article.Rating DECIMAL(2,1)
);

What´s wrong with raw SQL statements?
Access methods can generate raw SQL statements at runtime using database table information provided by the access methods´ caller method. The caller method knows the table schema and could generate the correct SQL text strings for each Java object it wants to store in the database. The access methods then assemble those strings into raw SQL statements and escape any illegal SQL characters.

This approach is simple but not intelligent. A lot of coding and runtime overhead results from the caller producing the correct SQL text string for each Java object. The solution is also slow. Every time the access method sends a raw SQL statement, it runs the overhead of parsing, compiling, and optimizing the statement. An even bigger problem: the raw SQL-based method cannot be cross-server portable. Different database servers have slightly different SQL syntax. For example, some databases expect YYYY-MM-DD type syntax for the SQL Date field while others might expect DD,MM,YYYY syntax. Databases can also have different SQL text escape requirements. For example, MySQL server uses backslash (\) to escape illegal characters while Microsoft SQL server uses single quote (´). That means any raw SQL-based implementation must target a specific database server.

直接使用 SQL 的問題

通過調用者提供的數據庫表信息,數據庫訪問方法可以生成一般的SQL statement,調用者清楚所要操作的數據庫表信息,產生相應的SQL語句,然后數據庫訪問方法把這些語句轉化成SQL statement,并除去一些逃逸字符

這種方法簡單,但不聰明.對每一次數據庫操作都要產生一個SQL語句，而且這種方法也很慢，每一次訪問，都必須重復的進行SQL語句的解析、編譯、調整.最大的問題還在于,SQL語句在不同的數據庫中是不兼容的。比如一些數據庫中的日期類型是YYYY-MM-DD，而另一些可能是DD,MM,YYYY.在逃逸符（escape characters）上各數據庫也不盡相同，MySQL使用反斜杠(\)而Microsoft SQL server使用單引號(´).這意味著以純SQL為實現的訪問是面向特定的數據庫的


To overcome the problems of the above raw SQL approach, you can use JDBC´s (Java Database Connectivity) PreparedStatement interface.

PreparedStatement for cross-server portability
A PreparedStatement can take a parameterized SQL template and compile it for maximum efficiency. When you need to run a query/update using this template, you only need to reset the parameter values using PreparedStatement.setXXXX() method, where XXXX is the parameter´s Java type. For example, setLong(1, articleID) resets the first parameter in the template to long type value articleID. JDBC´s PreparedStatement automatically generates the complete SQL statement for execution.

要克服如上所述的問題,你可以使用JDBC中的PreparedStatement接口

PreparedStatement使可移植成為可能
PreparedStatement 采用預編譯SQL模板來提高效能.當你用它來運行查詢/更新,你只需要為不同的參數執行reparedStatement.setXXXX()方法,就可以適應不同的查詢,這里XXXX是參數的類型.例如,setLong(1, articleID)可以重置SQL中的第一個long型的值articleID。JDBC的PreparedStatement會自動更新SQL statement來執行查詢

Because the JDBC driver generates the SQL statement according to the particular database server´s specifications, you don´t need a manual escape and the JDBC driver takes care of the database-specific SQL syntax. The application is portable across all database servers that have JDBC drivers. Also, the template´s precompilation greatly improves efficiency.

However, to use PreparedStatement´s setXXXX() methods, you must know the database table field´s type at compile time. That does not meet our "generic" requirement. "Java Tip 82: Build Data-Type-Independent JDBC Applications" gives a good discussion on how to convert generic type data from external sources to unknown SQL field types at runtime using the table metadata. But for Web applications, the situation is less complicated. The application knows the table schema at runtime. The Web application can also use the appropriate Java object type for data in each table field. For example, if a field is SQL Date type, the corresponding data in the Java application is probably already a java.sql.Date type object rather than a String containing the time information. This lets you use a simple solution to handle the runtime types.

由于JDBC driver可以按特定的數據庫規范來生成SQL statement，所以你不需要手工添加逃逸符，應用程序可以在已有JDBC driver的數據庫中移植，模板的預編譯可以使效率提高

但是在使用PreparedStatement的setXXXX()方法時，你必須知道表字段的類型，這并不符合一般性需要，在文章 Build Data-Type-Independent JDBC Applications中有關于使用表的標簽數據把外部未知的字段類型轉化成一般類型的討論。對于web應用來說，解決的方法相對簡單些：在運行時，程序獲知表的結構，使用相應的Java數據類型對應于每個字段。例如，如果有個字段是SQL 的Date類型，那應使用Java中的java.sql.Date類型而不是包含日期的String,這是一種簡單的處理方法

setObject() method for generic types
This solution uses the PreparedStatement.setObject() method instead of setXXXX() methods. Method setObject() uses reflection to figure out a Java object´s type at runtime before converting it to an appropriate SQL type. The method converts Java to SQL types using a standard JDBC map. If no match is found on the map, the method throws an exception.

Unfortunately, you cannot use Java primitive types with the setObject() method. Instead, you must use the corresponding object wrapper types. For example, if you want to set long type variable articleID into the template´s first parameter, you need to use setObject(1, (new Long(articleID)). You can retrieve the query result data fields as Java objects from ResultSet, using the ResultSet.getObject() method.

（未完待續）

原文轉自：http://www.anti-gravitydesign.com