假設原本將 MySQL 的資料庫編碼設為 big5 (也就是 my.ini 或 my.cnf 中的 [mysqld] 中有一行 default-character-set = big5)。之後因為多國語言的需要(或其他原因[1]), 想要更改為其他編碼(如 UTF-8)，這裡提供一個小技巧:

我想程式語言裡面，同時對資料庫及多國語系支源最好的，莫過於 Java 了。因此解決方式就是，找到一個 Java 所寫的 utility，利用 JDBC，執行資料庫的匯出/匯入，或是直接資料庫對拷。由於MySQL 的 JDBC driver 內建語系轉換的功能，因此若資料庫 A 與 B 的語系不同，當我們將一個資料由 A 拷至 B 時，語系轉換也就自然完成了。

幾經搜尋與測試後，終於找到一個 Eclipse 的 plug-in DB Copy，雖然這個 utility 介面相當陽春，且還在 Beta 階段，但它的確解決了我的問題。

[1] 好吧，為什麼不用 default-character-set = big5 的原因還有一個，就是當我如此設定時，我發現中文檔案上傳有問題。尤其是 PowerPoint 與 Word 之類的文件，10 個檔案可能有 9 個在上傳時無法成功 update 至 MySQL。這是我在 Tomcat+JSP+MySQL 所發生的問題，別種情況會不會，我就不知了。

Server: The hottest in the world...

• MySQL
• PostgreSQL
• Firebird
• SQLite

Client: I found that the following client applications all work with great gui design.

• DBManager Professional
• MySQL Control Center
• MySQL Administrator
• pgAdmin
• PG Explorer

與物件永續能力相關的技術

資料存取，幾乎是任何商業應用程式不可避免的一項作業。使用 JDBC 存取資料庫時，思維模式完全是資料導向的，也就是說資料是資料，物件是物件。資料與物件間的轉換工作，必須由程式設計師自行負責。而 Persistence Framework 提供一種機制，讓存取資料，就像使用 Java 物件般直接。也就是說，資料即是物件，物件即是資料。

採用 Persistence Framework 的好處包括：

• 程式設計與底層的儲存實體區隔開來。
• 採用直觀的方式存取資料--資料即是物件，物件即是資料；程式設計相對簡化。
• 通常 Persistence Framework 會內建 Connection Pooling 的機制，程式的 scalability 較佳，也可能更易於應用於分散式環境中。

目前比較著名的 Persistence Framework 包括:

• JDO: 官方標準
• Hibernate: a powerful, ultra-high performance object/relational persistence and query service for Java. Hibernate lets you develop persistent objects following common Java idiom - including association, inheritance,polymorphism, composition and the Java collections framework.
• Castor JDO: an open source data binding framework for Java[tm]. It's basically the shortest path between Java objects, XML documents and SQL tables. Castor provides Java to XML binding, Java to SQLpersistence, and then some more.
• Apache OJB: ObJectRelationalBridge (OJB) is an Object/Relational mapping tool that allows transparent persistence for Java Objects against relational databases.
• TJDO: 另一個JDO實作，看起來很完整。TriActive JDO (TJDO) is an open source implementation of Sun's JDO specification (JSR 12), designed to support transparent persistence using any JDBC-compliant database. Although the project is formally in beta release, TJDO is currently deployed and running successfully in a number of commercial installations.
• XORM: XORM is an object to relational mapping layer that provides interface-based persistence. All objects are created by XORM and are referenced in applications using only interfaces or abstract classes. ... XORM tries to be JDO (Java Data Objects) compliant where possible but is not a fully JDO compliant implementation
• Java Ultra-Lite Persistence: Looks simple but very powerful. It suit my taste!
• iBATIS SQL Maps: The SQL Maps framework will help to significantly reduce the amount of Java code that is normally needed to access a relational database. This framework maps JavaBeans to SQL statements using a very simple XML descriptor. Simplicity is the biggest advantage of SQL Maps over other frameworks and object relational mapping tools. To use SQL Maps you need only be familiar with JavaBeans, XML and SQL.

Persistence Layer

• JDBC: Official Standard
• SQL-J: write store procedures in java
• Torque: a persistence layer. Torque includes a generator to generate all the database resources required by your application and includes a runtime environment to run the generated classes.
• JORM: JORM(Java Object Repository Mapping) is an adaptable persistence service. It can be used to offer various personalities, such as one compliant with the CMP EJB specification (TM), another with the OMG PSS specification or another with the JDO (Java Data Objects) specification (TM).
• Jaxor: Jaxor is a code-generating OR mapping tool which takes information defined in XML relating to the relational entities to be mapped and generates classes, interfaces and finder objects which can be used from any Java application (including JFC/Swing, J2EE and command-line tools). The actual code generation is handled by Velocity templates rather than by fixed mechanisms within the tool. This flexability allows easy tailoring and modification to the formatting or even the code that gets generated.

JDBC Wrapper

• SQLExecutor
• DbUtils is a small set of classes designed to make working with JDBC easier. JDBC resource cleanup code is mundane, error prone work so these classes abstract out all of the cleanup tasks from your code leaving you with what you really wanted to do with JDBC in the first place: query and update data.Some of the advantages of using DbUtils are:
• • No possibility for resource leaks. Correct JDBC coding isn't difficult but it is time-consuming and tedious. This often leads to connection leaks that may be difficult to track down.
  • Cleaner, clearer persistence code. The amount of code needed to persist data in a database is drastically reduced. The remaining code clearly expresses your intention without being cluttered with resource cleanup.

Pooling Framework

• PoolMan: "The PoolMan library and JDBC2.0 Driver and DataSource provide a JMX-based, XML-configurable means of pooling and caching Java objects, as well as extensions for caching SQL queries and results across multiple databases."
• YAPoolMan: "YAPoolMan is an embeddable object pooling and caching library. It optimizes resource usage by managing reusable instances across multiple requests. It can be used to pool any Java Class type, and contains specialized features -- such as a JDBC DataSource and JDBC Driver implementations for JDBC resource pooling. It is designed to be embeddable, so it instantiates its underlying server lazily (that is, upon receipt of the first request) rather than requiring an explicit root configuration and startup semantics."
• ShiftOne Java Object Cache: "JOCache is a Java library that implements strict object caching."
• Proxool: Proxool is a Java connection pool. It transparently adds connection pooling to your existing JDBC driver. It's easy to configure using the JDBC API, XML, or Java property files.
• dbcp: Apache Jakarta 專案所包含的 database connection pooling 套件。
  

Lightweight Persistence Library

• Prevayler: "Prevayler is the most reliable Free Software Prevalence layer we are capable of providing for Java.Prevalence is by far the fastest, simplest and most transparent business object persistence, ACID transaction, fault-tolerance, replication and load-balancing architecture we know. "
• JDBM Project: "JDBM is a transactional persistence engine for Java. It aims to be for Java what GDBM is for other languages (C/C , Python, Perl, etc.): a fast, simple persistence engine. You can use it to store a mix of objects and BLOBs, and all updates are done in a transactionally safe manner. JDBM also provides scalable data structures, such as HTree and B Tree, to support persistence of large object collections."
• Java Indexed Serialization Package: Jisp is an embedded database engine designed for programmers who need indexed access to data. Written in Pure Java and tested for portability, Jisp provides a simple, direct solution when an enterprise-class database system is too big or expensive for the task at hand.
• MaVerick - Open Source MultiValue Database Management System: "MultiValue databases have been around for about 30 years, starting off with The Pick Operating System. You've probably heard of relational databases, well, MultiValue databases can be relational, but their method of storing data is much more flexible than the 2 dimensional table you normally associate with relational databases, i.e., you can store multiple fields within a field. On top of which, they are extremely fast in operation."

今有一 CSV (逗號分開)格式之文字檔，欲用 Update 之方式匯入資料表中，採用哪種方式較快?

可能解法:

• 1) 直接使用 SQL 之 Update 敘述，逐筆更新至 table(這裡要注意必需採用索引鍵更新)。
• 2) 以 Insert 方式逐一匯至一 tmp table, 再整批 update 至欲更新的 table

測試結果:以1000筆資料做測試，在 Pentium 4, 1.60GHz, 526 MB RAM 的機器上做測試。1的結果為 5 秒，2的結果為6秒。採用 Update 的方式獲勝!

以前從網路上抄下來的命名規範...

• Name your tables with singular names. Thus 'User', not 'Users'. This is related to the fact that when you create a model, you first draw an abstract model, like a NIAM/ORM model. You name your entity 'User', not 'Users' there too, so why name the table 'Users' then?
• Do not prefix table or view names.
• Do not prefix the fields with table related prefixes. Use aliases in queries if you have doubles.
• Do not use spaces.
• Do not type prefix names.
• Name your fields after the data they contain. So 'Password' is ok. UserID is also ok. 'ID' is a little too general IMHO, but can be ok. Use PascalCasing.
• Booleans should be named as IsDeleted or HasKey or IsValid, but not 'Valid', 'Deleted' because in some situations these can be ambigious.
• Use the language the application is for, so if you're writing a dutch application, store the data in Dutch fields. If the system is multi-language, use English. Picking the target language is key to make the code understandable. Keep this language choice consistent, thus do not use two or more languages but one.
• When you pick a non-english language, don't use the language's special characters for table names or field names. This can upset code that is ported.
• Do not use abbreviations for fieldnames or entity names! I've seen every silly naming scheme in the world you can think of in the last 10 years and most of them contained the most incredible abbreviations you can possibly imagine.
• Names of foriegn keys should exactly match the primary key they associate with, so "CountryId" and "AccountId"

一般講 design patterns 都是講物件導向，程式設計方面的議題。其實在架構大型商用系統，進行資料表之設計時，也有一些常用的 design pattern 可供遵循。

在 IT 領域一般講 Design Patterns 都是講物件導向、程式設計的議題方面比較多。其實 database 在設計時，也會有一些常用的樣式。例如，在設計商業應用程式時，在資料庫方面，就常會遇到底下幾種需求：

• 列舉型別(Enumenate): 姓別是男、是女；HTML 標準顏色表；婚姻狀態等
• 國際化(Translation): i18n 與 l10n 的考量。
• 系統設定(Config): 把所有的系統設定，存在某個資料表中
• 目錄結構(Category): 常用在型錄結構，內容分類等
• 可擴充內容型態(Extensible Content Type): 如何透過一致的方式，管理不同的內容型態。另外，內容如何與目錄結合，也是設計上另一個考量的重點。
• 階層式資料型態(Hierarchical Data Type)：即樹狀結構之資料。

當然，以上所舉只是部分。不過資料表之設計，萬變不離其宗，了解以上的 Database Design Pattern後，設計商業應用程式，將不在是難事。以下分別說明其設計方式。

列舉型別設計：

這裡要考量的重點是，系統的列舉型別，應如何由資料表來表示。是要一種列舉型別設計一個 table 來表示呢? 還是將所有列舉型別共用一個 table 表示。

個人的喜好是，將所有的列舉型別共用一個 table 表示。然後透過 table 裡面的一個稱為 ENUM_TYPE 的欄位來識別其型態。

當然，除了其型態外，列舉型別還包括了列舉元素名稱及列舉元素值。因此，列舉型態資料表可設計為：

ENUM_LIST
ENUM_TYPE	VARCHAR(32)
ENUM_SEQ	INT
ENUM_NAME	VARCHAR(32)
ENUM_VALUE	VARCHAR(32)

例如，假設我們的系統中有下列三種列舉列別：

1. Enum color = {RED:"#FF0000", GREEN:"#00FF00", BLUE:"#0000FF"}
2. Enum sex = {female:0, male:1, unknown:-1}
3. Enum area = {台北市, 台北縣, 基隆市, ...}

則列舉型態資料表的內容為：

 

ENUM_TYPE	ENUM_SEQ	ENUM_NAME	ENUM_VALUE
color	0	COLOR	#000000
color	1	RED	#FF0000
color	2	GREEN	#00FF00
color	3	BLUE	#0000FF
sex	0	SEX
sex	1	FEMALE	0
sex	2	MALE	1
sex	3	UNKNOWN	-1
area	0	AREA
area	1	台北市
area	2	台北縣
area	3	基隆市
area	...	...

如上表所示，注意到每種 ENUM_TYPE，其 ENUM_SEQ 為 0 的記錄，其 ENUM_NAME 即代表型態名稱，ENUM_VALUE 即代表型態預設值(可不設)。這樣的設計，當我們要取得系統內所有列舉型態時，只需透過一個 SQL query:

SELECT * FROM ENUM_LIST WHERE ENUM_SEQ = 0

得到輸出為:

 

ENUM_TYPE	ENUM_SEQ	ENUM_NAME	ENUM_VALUE
color	0	COLOR	#000000
sex	0	SEX
area	0	AREA

另一個好處是，當我查詢某列舉型態的所有值時，其 ENUM_SQL 為 0 的元素，即為其型態名稱。例如：

SELECT ENUM_NAME FROM ENUM_LIST WHERE ENUM_TYPE = color

得到輸出為：

 

COLOR
RED
GREEN
BLUE

未完待續 ...

Data Processing

• JAVA-OLTP PROJECT PAGE - MOREDATA: javaoltp is a framework to help the creation of OLTP applications in Java using Swing or JSP.

  It provides high-level APIs to create forms, reports & menus, highly integrated with an SQL database.

  It includes: DataAccessObejcts, GUI Widgets, JSP/Swing Data Entry, Reports , etc.

Data Mining

Database Server Writen in Java:

• Axion: Axion is a small, fast, open source relational database system (RDBMS) supporting SQL and JDBC written in and for the Java programming language.
• hsqldb: hsqldb is a relational database engine written in Java, with a JDBC driver, supporting a rich subset of ANSI-92 SQL (BNF tree format). It offers a small (less than 160k), fast database engine which offers both in memory and disk based tables. Embedded and server modes are available. Additionally, it includes tools such as a minimal web server, in-memory query and management tools (can be run as applets) and a number of demonstration examples. (根據我測試的結果，在用 embedded 模式時，資料都是放在記憶體中，會導致 out off mamory!)
• Mckoi: Mckoi SQL Database is an SQL (Structured Query Language) Database management system written for the JavaTM platform. Mckoi SQL Database is optimized to run as a client/server database server for multiple clients, however it can also be embedded in an application as a stand-alone database. It is highly multi-threaded and features an extendable object-oriented engine.
• ozone: The Ozone Database Project is a open initiative for the creation of an open source, Java based, object-oriented database management system.

Database Client Writen in Java :

• SQuirreL SQL Client: "SQuirreL SQL Client is a graphical Java program that will allow you to view the structure of a JDBC compliant database, browse the data in tables, issue SQL commands etc."
• QueryForm Database Tool: "QueryForm is a robust Java application that provides a powerful GUI front end for JDBC-enabled databases. It creates forms on-the-fly through which you can query tables and browse the results with just a few keystrokes or mouse clicks. It also lets you insert, update and delete table rows without typing any SQL statements."
• JDBC Explorer: A Swing front end to connect with every DBMS (Data Base Manager System) who has a JDBC driver. It is written using pure Java and no third party library is used. It is:
  • Cross-platform: every O.S. with a JVM, JDK 1.4 or above
  • Cross DBMS: every data base server having a JDBC driver
  • Source code available through the Open Source license
• SQL Admin: SQL Admin is a Java client application to connect and send queries to different databases through JDBC. The main idea is to create a multiplatform and multidatabase thin client. For example you can connect to an Microsoft SQL Server from a Linux machine, or connect to a PostgreSQL/Linux server from a windows machine using the same application

An example to demonstrate how to use JDBC to get column names for a table:

 public ArrayList getColumnNames(Connection connection, String tableName) {    ArrayList v = new ArrayList();     try{      /* query table */      String cmd = "SELECT * FROM " + tableName;      //ResultSet rs = connection.createStatement().executeQuery(cmd);       PreparedStatement pstmt = connection.prepareStatement(cmd);      ResultSetMetaData meta = pstmt.getMetaData();      int colSize = meta.getColumnCount();       for(int i = 1; i <= colSize; i++){          v.add(  meta.getColumnName(i).trim()  );      }      return(v);    }    catch(Exception ex){      return(null);    } }  

Note:

1. Use PreparedStatement instead of ResultSet to avoid executeQuery

2. The begining of column index is 1